EMP_STAFF = "Y""AS"STAFFPK,FK1 EMP IDITSTAFFPK,FK1 EMP_IDSTAFF_LEVELSTAFF TYPE"IT"ACADSUPPORTPK,FK1 EMP_IDCurrent DataHistoric DataIT_STAFF_GROUPSTAFF_TYPEEMPLOYEEPK EMP_IDIT_STAFF_GROUPEMP_LNAMEEMP FNAMEEMP INITIALEMP_HIREDATEEMP STAFFEMP ADMINEMP PROFEMP_PROF="Y"TECHSUPPORTPK,FK1 EMP_IDPROFESSORPK EMP IDPK,FK1 EMP IDH+EMP_ADMIN "Y"EMP_JOB_HISTADMINPK,FK1 EMP_IDPROF SPECIALTYPROF_RANK"TS"EMPLOYEEEMP_LNAMEEMP_FNAMEhasEMP INITIALEMP_HIREDATEEMP STAFFEMP_ADMINEMP PROFOPKADMIN_POSITIONOPK,FK1PK,FK2PKWhere Employee and Professor table part is extended as shown belowHois dean ofHOJOB_HISTPK,FK1 EMP IDPK,FK2 SCHOOL CODEJH_DATE ASSIGNTAKENJH_DATE_ENDEDJH EMP_SALARYEMP_PROF "Y"EMP IDTRN_IDTAKEN DATEPK,FK1 EMP IDTAKEN_TYPETAKEN_PASSPKPROFESSORSCHOOLSCHOOL_CODESCHOOL_NAMEFK1 EMP IDPROF_SPECIALTYPROF_RANKSCHOOL JOB_HISThasTRAININGHPK TRN_IDTRN_CATEGORY 1. Write a SQL query to list first name, last name and rank of only professors whose rank isAssistant, Associate or full professor (where each rank is coded as ATP, AAP, and FPrespectively).2. Write a MySQL query to show the number of people in each of the professor rank category(which includes adjunct, instructor, visiting professor, assistant professor, associate professorand full professor) and sort the result by the number of people in descending order.3. Write a MySQL query to list the professor rank where the total number of people the rank ismore than 10 people.4. Write a MySQL query to find names of the IT staff who completed the training (training ID = 5) andthe date they passed the training (assuming the date field in the table: TAKEN is of the Date type).5. Write a MySQL query to find names of those who are both a full professor AND an IT staff whocompleted at least 2 trainings and the dates they passed the training.6. Write a MySQL query to find names of those who are either a full professor OR an IT staff whocompleted at least 2 trainings or both.7. Write a MySQL query to find those professors who have not served as a School Dean.8.Write a MySQL query to list all employees by Last name, First name and their EMP admin,EMP staff and EMP Prof status ordered by Last name in alphabetical order. Last name and thethree status fields (administrator, staff and professor status) must be displayed all in uppercaseletter; first name is displayed with the first letter in uppercase and the rest all in lower case letters.

According to Bartleby Guidelines we need to answer only one question so I have answered first…

Answered: 1. Write a SQL query to list first…

1. Write a SQL query to list first name, last name and rank of only professors whose rank is Assistant, Associate or full professor (where each rank is coded as ATP, AAP, and FP respectively).

Programming with Microsoft Visual Basic 2017

8th Edition

ISBN:9781337102124

Author:Diane Zak

Publisher:Diane Zak

Chapter11: Sql Server Databases

Section: Chapter Questions

Problem 6E

See similar textbooks

Similar questions

Customers ld Username Firstname Sumame Email PostalAddress Orders Id Customorld Created Status Orderitems Orderid StyleRequested SizeRequested Stockid Shirts Stockld Style Size StoreLocation Count Which of the following statements are correct for this entity rel ionship diagram? a. There is a many to many relationship between Orderitems and Orders Ob. There is a many to one relationship between Customers and Orders c. Orderltems is a bridge table Od. There is a many to one relationship between Orderitems and Shirts □e. Orders is a bridge table Of. There is a many to many relationship between Orderltems and Shirts
Make use of Windows Presentation Foundation (WPF) to develop a Stock Management System thatwill manage the items being stocked in a warehouse. The application has 3 modules named: Addproduct, List Product, and Print Bar Code.Add Product:This module is used for adding new products to the system. It will require some basic details likeProduct category, Product Name, cost price, selling price, quantity and bar code. The details willbe saved into a SQL database named StockSystemdb. One other interesting feature of this systemis the alert system.You can set a particular quantity for each item. Now a notification or alert will be given to the userif a particular item’s quantity gets below the set quantity. This will help the user in gettingnotifications of the items getting low in stock.List Product:This module lists all the items present in the database of the Stock Management System. It will haveoptions to edit the details of each item or delete a particular item from the list.Print Bar…
Having this information. StudentsAttribute Name Data TypeStudentID char(11)FirstName varchar(20)LastName varchar(20)Gender char(1)DateofBirth date CoursesAttribute Name Data TypeCourseCode varchar(6)CourseName varchar(70)Level char(2)Credits int RegistrationAttribute Name Data TypeStudentID char(11)CourseCode varchar(6)Grade decimal(2, 1) Write SQL code to: 1. How many courses are there on each level? 2. What is the average grade of courses that have been taken by the student with student ID 861103-2438? 3. Which students (studentID only) have the highest grade for the course ‘CS052’? 4. Find the courses (course codes only) that have been taken by both the student 861103-2438 and the student 123456-0980.
Having this information. StudentsAttribute Name Data TypeStudentID char(11)FirstName varchar(20)LastName varchar(20)Gender char(1)DateofBirth date CoursesAttribute Name Data TypeCourseCode varchar(6)CourseName varchar(70)Level char(2)Credits int RegistrationAttribute Name Data TypeStudentID char(11)CourseCode varchar(6)Grade decimal(2, 1) Write SQL code to: 1. What are the student ID, the names, and birthday of the female students who were born before the last day of 1992? 2. How many students are registered in the course “PHY210”? 3. How many courses are offered by the department of Computer Science (i.e. course codes CS***)? 4. Which course(s) (couseCode and courseName) of level G1 give 5 credits?
A USE statement __________ None of these Identifies the database within which the query is to be executed Identifies the SET theory All of these Identifies Mr. Peabody’s rules
In Senior Project Website, the user requests the website to search for previous senior projects, the website displays the search web form, the user inputs the keywords to be used for the search process, the user submits the search criteria to the website, and the website requests a remote database to search for senior projects that fulfill the requested criteria (keywords). The remote database returns back a Boolean value (indicating if the search is successful or not) and a list of found senior projects (the list is empty if not successful), If the search is successful, the website displays the list of received senior projects, else, the website displays error dialog box indicating that no projects were found. Q2. For Senior Project Website System described above, students are required to conduct the following task: a) Design the Sequence Diagram of Senior Project Website System described above.
Create table EMP and DEPT with the below mentioned structureStructure for EMP tableEmpIDNUMBER(4)DeptIDVARCHAR2(10)EmpNameCHAR(10)JobCHAR(10)HireDate DATESalary NUMBER(7, 2)Commission NUMBER (7, 2)Structure for DEPT tableDeptId VARCHAR2(10)Deptname VARCHAR2(20)No_of_Faculties NUMBER(2)In table EMP : EmpID should be the Primary Key and DeptID should be the foreign key.In table DEPT : DeptId should be the primary key. a) Find out the number of employees having “manager” as job.b) Display only the jobs with maximum salary greater than or equal to 3000c) Find all those employees whose job does not start with ‘M’. Find the names of the employees who are Managers and their date of joining is after “02-Jan-2006”.
Using the following entity information, the primary key of the student who lives in Eastpoint, FL is, O Start Page A MOTerm Exam L Relaional, 1 Untited 1 CIS25SOISTUDENT VARCHARZ S EYTED VARCHARZ 130 BYTE VARDAA2 (3 BYTD SLAST SIRST SMI SADORESS S CITY SSTATE CB2550LFACULTY NUMEER I VARCHARZ 0 YTE VARCHARZ GO BYTE CHARYT CHAR LEYTE VARDARZ 125 YTD VARCHARZ 20 BYTE FAST EARST MI FPHONE VARCHARZ 10 BYTE RANK FUPER CHAR 2YTE VARCHARZ 0 YTD VARCHAR2 (10 BYTE CHAR 2 BYTE DATE NUMSER 4 NUMBER INTERVAL YEAR TO MONTH VARCHARZ OBYT NUMBER NUMBER FACULTYFJD JD SPHONE SCLASS 5.006 SUPN FACULTYFJDFK FJD TIME DROLLID STUDENTSID RD STUDENT F ID K F D STUDENT SID PK 6JD Seriat Outut Query Result SOL I AlI Ros Fetched: 5in 0.74 seconds rDrLASTLIRST M HONE LRANK rSUPERFMIN 1 Marx 2 Zhulin Mark 3 Lang ley ColinA 4 Broun JonnelD 5 Sealy Janes L Teresa 4075921695 Associate 6338 4073875682 Full (null) 1121 4075928719 Assistant 9871 4 4078101155 Full (null) 8297 4079817153 Associate 1 Script Output Query…
Assume that a database has a table named Stock, with the following columns:Column Name TypeTrading_Symbol nchar(10)Company_Name nchar(25)Num_Shares intPurchase_Price moneySelling_Price money Write a Select statement that returns the Trading_Symbol column and the Num_Shares column only from the rows where Selling_Price is greater than Purchase_Price and Num_Shares is greater than 100. The results should be sorted by the Num_Shares column in ascending order.
Write Mysql code The database has three tables for tracking horse-riding lessons: Horse with columns: ID - primary key RegisteredName Breed Height BirthDate Student with columns: ID - primary key FirstName LastName Street City State Zip Phone EmailAddress LessonSchedule with columns: HorseID - partial primary key, foreign key references Horse(ID) StudentID - foreign key references Student(ID) LessonDateTime - partial primary key Write a SELECT statement to create a lesson schedule with the lesson date/time, horse ID, and the student's first and last names. Order the results in ascending order by lesson date/time, then by horse ID. Unassigned lesson times (student ID is NULL) should not appear in the schedule. Hint: Perform a join on the Student and LessonSchedule tables, matching the student IDs.
A real Estate Company has in its poššession a long list of landlords and the rent cost of their two bedroom apartments, as shown on table 1. There is the need for an Application program that will allow a user at the company to load the said list into a computer program, i.e. the list of landlords and the cost of rent. The app must also allow users to search for apartments they can afford. Apart from the list in the table, the App should be able to take as input the maximum amount a tenant is willing to spend on a two-bedroom apartment and then display a list of landlords whose apartments can be rented with that amount. Table 1: Landlords and Cost of rent Landlord Ama Dua Ali Dulon John Togbe Abena Nyame Kofi Duku Cost of rent (GHC) 800.00 950.00 2,500.00 600.00 2,300.00
Type the MATLAB command(s) to create two structures called p4_user1 and p4_user2. P4_user1 contains the following three fields and the corresponding values: name=Jonny Stone age=23 ID=123456