Homework Help is Here – Start Your Trial Now!
Engineering
Computer Science
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).

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).

Database System Concepts
Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
See similar textbooks
icon
Related questions
icon
Concept explainers
Question
100%
EMP_STAFF = "Y" "AS" STAFF PK,FK1 EMP ID ITSTAFF PK,FK1 EMP_ID STAFF_LEVEL STAFF TYPE "IT" ACADSUPPORT PK,FK1 EMP_ID Current Data Historic Data IT_STAFF_GROUP STAFF_TYPE EMPLOYEE PK EMP_ID IT_STAFF_GROUP EMP_LNAME EMP FNAME EMP INITIAL EMP_HIREDATE EMP STAFF EMP ADMIN EMP PROF EMP_PROF="Y" TECHSUPPORT PK,FK1 EMP_ID PROFESSOR PK EMP ID PK,FK1 EMP ID H+ EMP_ADMIN "Y" EMP_JOB_HIST ADMIN PK,FK1 EMP_ID PROF SPECIALTY PROF_RANK "TS" EMPLOYEE EMP_LNAME EMP_FNAME has EMP INITIAL EMP_HIREDATE EMP STAFF EMP_ADMIN EMP PROF OPK ADMIN_POSITION OPK,FK1 PK,FK2 PK Where Employee and Professor table part is extended as shown below Hois dean of HO JOB_HIST PK,FK1 EMP ID PK,FK2 SCHOOL CODE JH_DATE ASSIGN TAKEN JH_DATE_ENDED JH EMP_SALARY EMP_PROF "Y" EMP ID TRN_ID TAKEN DATE PK,FK1 EMP ID TAKEN_TYPE TAKEN_PASS PK PROFESSOR SCHOOL SCHOOL_CODE SCHOOL_NAME FK1 EMP ID PROF_SPECIALTY PROF_RANK SCHOOL JOB_HIST has TRAINING HPK TRN_ID TRN_CATEGORY
Transcribed Image Text:EMP_STAFF = "Y" "AS" STAFF PK,FK1 EMP ID ITSTAFF PK,FK1 EMP_ID STAFF_LEVEL STAFF TYPE "IT" ACADSUPPORT PK,FK1 EMP_ID Current Data Historic Data IT_STAFF_GROUP STAFF_TYPE EMPLOYEE PK EMP_ID IT_STAFF_GROUP EMP_LNAME EMP FNAME EMP INITIAL EMP_HIREDATE EMP STAFF EMP ADMIN EMP PROF EMP_PROF="Y" TECHSUPPORT PK,FK1 EMP_ID PROFESSOR PK EMP ID PK,FK1 EMP ID H+ EMP_ADMIN "Y" EMP_JOB_HIST ADMIN PK,FK1 EMP_ID PROF SPECIALTY PROF_RANK "TS" EMPLOYEE EMP_LNAME EMP_FNAME has EMP INITIAL EMP_HIREDATE EMP STAFF EMP_ADMIN EMP PROF OPK ADMIN_POSITION OPK,FK1 PK,FK2 PK Where Employee and Professor table part is extended as shown below Hois dean of HO JOB_HIST PK,FK1 EMP ID PK,FK2 SCHOOL CODE JH_DATE ASSIGN TAKEN JH_DATE_ENDED JH EMP_SALARY EMP_PROF "Y" EMP ID TRN_ID TAKEN DATE PK,FK1 EMP ID TAKEN_TYPE TAKEN_PASS PK PROFESSOR SCHOOL SCHOOL_CODE SCHOOL_NAME FK1 EMP ID PROF_SPECIALTY PROF_RANK SCHOOL JOB_HIST has TRAINING HPK TRN_ID TRN_CATEGORY
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). 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 professor and 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 is more than 10 people. 4. Write a MySQL query to find names of the IT staff who completed the training (training ID = 5) and the 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 who completed 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 who completed 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 the three status fields (administrator, staff and professor status) must be displayed all in uppercase letter; first name is displayed with the first letter in uppercase and the rest all in lower case letters.
Transcribed Image Text: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). 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 professor and 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 is more than 10 people. 4. Write a MySQL query to find names of the IT staff who completed the training (training ID = 5) and the 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 who completed 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 who completed 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 the three status fields (administrator, staff and professor status) must be displayed all in uppercase letter; first name is displayed with the first letter in uppercase and the rest all in lower case letters.
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 2 steps

SEE SOLUTIONCheck out a sample Q&A here
Blurred answer
Follow-up Questions
Read through expert solutions to related follow-up questions below.
Follow-up Question

Can you Help me with Home work questions 2-8 please.

Solution
Bartleby Expert
SEE SOLUTION
Knowledge Booster
Query Syntax
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.
Similar questions
  • SEE MORE QUESTIONS
Recommended textbooks for you
Database System Concepts
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education
SEE MORE TEXTBOOKS