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

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