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
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
Related questions
Concept explainers
SQL
SQL stands for Structured Query Language, is a form of communication that uses queries structured in a specific format to store, manage & retrieve data from a relational database.
Queries
A query is a type of computer programming language that is used to retrieve data from a database. Databases are useful in a variety of ways. They enable the retrieval of records or parts of records, as well as the performance of various calculations prior to displaying the results. A search query is one type of query that many people perform several times per day. A search query is executed every time you use a search engine to find something. When you press the Enter key, the keywords are sent to the search engine, where they are processed by an algorithm that retrieves related results from the search index. Your query's results are displayed on a search engine results page, or SER.
Question
100%
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
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
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
This is a popular solution!
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
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
by Bartleby ExpertKnowledge Booster
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.Recommended textbooks for you
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)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
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)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education