Retrieve the average salary of all employees who works for 'Administration' department    2. For each department, list the department name and the total number of project controlled by the department.    3. Retrieve the names of employees who work on every project controlled by department 4    4. Retrieve the names of department managers who do not work on any project.    5. For each employee, retrieve employee's name, and the total hours working in projects    6. List the last names of female employees who have no dependents

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
Question

Specify the following queries on the database schema shown in Figure above using queries in SQL

 

 1. Retrieve the average salary of all employees who works for 'Administration' department

 

 2. For each department, list the department name and the total number of project controlled by the department. 

 

3. Retrieve the names of employees who work on every project controlled by department 4 

 

4. Retrieve the names of department managers who do not work on any project. 

 

5. For each employee, retrieve employee's name, and the total hours working in projects 

 

6. List the last names of female employees who have no dependents

EMPLOYEE
FNAME
MINIT
LNAME
SSN
BDATE
ADDRESS
SEX
SALARY
SUPERSSN DNO
John
Smith
123456789
1965-01-09
731 Fondren, Houston, TX
30000
333445555
Frankin
Wong
Zelaya
Wallace
333445555
1955-12-08
1968-07-19
638 Voss, Houston, TX
40000
888665555
3321 Castle, Spring. TX
291 Bery, Belaire, TX
975 Fire Oak, Humble, TX
Alicia
J
999887777
F
25000
987654321
Jennifer
987654321
1941-06-20
F
43000
888665555
Narayan
English
Ramesh
K
666884444
1962-09-15
M
38000
333445555
Joyce
A
453453453
1972-07-31
5631 Rice, Houston, TX
F
25000
333445555
5
Ahmad
V
Jabbar
987987987
1969-03-29
980 Dallas, Houston, TX
M
25000
987654321
4
James
E
Borg
888665555
1937-11-10
450 Stone, Houston, TX
M
55000
null
1
DEPT LOCATIONS
DNUMBER
DLOCATION
Houston
Stafford
DEPARTMENT
DNAME
DNUMBER
MGRSSN
MGRSTARTDATE
Bellaire
Research
333445555
1988-05-22
5
Sugarland
Administration
Headquarters
987654321
888665555
1995-01-01
5
Houston
1981-06-19
WORKS ON
ESSN
PNO
HOURS
123456789
1
32.5
123456789
2
7.5
666884444
3
40.0
453453453
1
20.0
453453453
2
20.0
PROJECT
PNAME
PNUMBER
PLOCATION
DNUM
333445555
2
10.0
Productx
ProductY
Productz
Computerization
Reorganization
Newbenefits
Belaire
333445555
3
10.0
Sugarland
333445555
10
10.0
3
Houston
333445555
20
10.0
10
Stafford
Houston
Stafford
4
999887777
30
30.0
20
999887777
10
10.0
30
4
987987987
10
35.0
987987987
30
5.0
987654321
30
20.0
987654321
20
15.0
888665555
20
null
DEPENDENT
ESSN
DEPENDENT NAME
SEX
BDATE
RELATIONSHIP
333445555
333445555
Alice
F
1986-04-05
1983-10-25
1958-05-03
DAUGHTER
Theodore
Joy
M
SON
SPOUSE
333445555
F
987654321
Abner
M
1942-02-28
SPOUSE
Michael
1988-01-04
123456789
123456789
123456789
M
SON
Alice
1988-12-30
DAUGHTER
Eizabeth
F
1967-05-05
SPOUSE
Transcribed Image Text:EMPLOYEE FNAME MINIT LNAME SSN BDATE ADDRESS SEX SALARY SUPERSSN DNO John Smith 123456789 1965-01-09 731 Fondren, Houston, TX 30000 333445555 Frankin Wong Zelaya Wallace 333445555 1955-12-08 1968-07-19 638 Voss, Houston, TX 40000 888665555 3321 Castle, Spring. TX 291 Bery, Belaire, TX 975 Fire Oak, Humble, TX Alicia J 999887777 F 25000 987654321 Jennifer 987654321 1941-06-20 F 43000 888665555 Narayan English Ramesh K 666884444 1962-09-15 M 38000 333445555 Joyce A 453453453 1972-07-31 5631 Rice, Houston, TX F 25000 333445555 5 Ahmad V Jabbar 987987987 1969-03-29 980 Dallas, Houston, TX M 25000 987654321 4 James E Borg 888665555 1937-11-10 450 Stone, Houston, TX M 55000 null 1 DEPT LOCATIONS DNUMBER DLOCATION Houston Stafford DEPARTMENT DNAME DNUMBER MGRSSN MGRSTARTDATE Bellaire Research 333445555 1988-05-22 5 Sugarland Administration Headquarters 987654321 888665555 1995-01-01 5 Houston 1981-06-19 WORKS ON ESSN PNO HOURS 123456789 1 32.5 123456789 2 7.5 666884444 3 40.0 453453453 1 20.0 453453453 2 20.0 PROJECT PNAME PNUMBER PLOCATION DNUM 333445555 2 10.0 Productx ProductY Productz Computerization Reorganization Newbenefits Belaire 333445555 3 10.0 Sugarland 333445555 10 10.0 3 Houston 333445555 20 10.0 10 Stafford Houston Stafford 4 999887777 30 30.0 20 999887777 10 10.0 30 4 987987987 10 35.0 987987987 30 5.0 987654321 30 20.0 987654321 20 15.0 888665555 20 null DEPENDENT ESSN DEPENDENT NAME SEX BDATE RELATIONSHIP 333445555 333445555 Alice F 1986-04-05 1983-10-25 1958-05-03 DAUGHTER Theodore Joy M SON SPOUSE 333445555 F 987654321 Abner M 1942-02-28 SPOUSE Michael 1988-01-04 123456789 123456789 123456789 M SON Alice 1988-12-30 DAUGHTER Eizabeth F 1967-05-05 SPOUSE
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 2 steps

Blurred answer
Knowledge Booster
Data Modeling Concepts
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
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