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
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
Related questions
Question
Specify the following queries on the
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
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
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
Knowledge 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