Write the following queries in SQL on the relational database schema for COMPANY database given in page 4.   Find the total number of dependents of each employee who has 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

 Write the following queries in SQL on the relational database schema for COMPANY database given in page 4.

 

Find the total number of dependents of each employee who has dependents.

EMPLOYEE
Fname
Minit
Lname
Ssn
Bdate
Address
Sex
Salary
Super_ssn
Dno
AAAA
DEPARTMENT
Dname
Dnumber
Mgr_ssn Mgr_start_date
AAA
DEPT LOCATIONS
Dnumber
Dlocation
PROJECT
Pname
Pnumber
Plocation
Dnum
WORKS_ON
Essn
Pno
Hours
DEPENDENT
Referential integrity constraints displayed
on the COMPANY relational database
Essn
Dependent_name
Sex
Bdate
Relationship
schema.
One possible database state for the COMPANY relational database schema.
EMPLOYEE
Fname
Minit
Lname
Ssn
Bdate
Address
Sex
Salary
Super_ssn
Dno
John
B
Smith
123456789
1965-01-09 731 Fondren, Houston, TX M
30000 333445555
888665555
1955-12-08638 Voss, Houston, TX
1968-01-19 3321 Castle, Spring, TX
1941-06-20291 Berry, Bellaire, TX
Franklin
T
Wong
333445555
M
40000
Alicia
J
Zelaya
999887777
F
25000
987654321
4
Jennifer
Wallace
987654321
F
43000 888665555
4
Ramesh
K
Narayan 666884444 1962-09-15 975 Fire Oak, Humble, TX M
38000 333445555
Joyce
A
English
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
Borg
888665555 1937-11-10 | 450 Stone, Houston, TX
M
55000 NULL
1
DEPARTMENT
DEPT_LOCATIONS
Dname
Dnumber
Mgr_ssn
Mgr_start_date
Dnumber
Dlocation
Research
333445555
1988-05-22
1
Houston
Administration
4
987654321
1995-01-01
4
Stafford
Headquarters
888665555
1981-06-19
Bellaire
Sugarland
Houston
WORKS_ON
PROJECT
Essn
Pno
Hours
Pname
Pnumber
Plocation
Dnum
123456789
1
32.5
ProductX
1
Bellaire
123456789
2
7.5
ProductY
Sugarland
5
666884444
3
40.0
ProductZ
3
Houston
5
453453453
1
20.0
Computerization
10
Stafford
4
453453453
2
20.0
Reorganization
20
Houston
1
333445555
10.0
Newbenefits
30
Stafford
4
333445555
3
10.0
333445555
10
10.0
DEPENDENT
333445555
20
10.0
Essn
Dependent_name
Sex
Bdate
Relationship
999887777
30
30.0
333445555
Alice
F
1986-04-05
Daughter
999887777
10
10.0
333445555
Theodore
M
1983-10-25
Son
987987987
10
35.0
1958-05-03
Spouse
333445555
Joy
F
987987987
30
5.0
987654321
Abner
1942-02-28
Spouse
987654321
30
20.0
123456789
Michael
M
1988-01-04
Son
987654321
20
15.0
123456789
Alice
F
1988-12-30
Daughter
888665555
20
NULL
123456789
Elizabeth
F
1967-05-05
Spouse
Transcribed Image Text:EMPLOYEE Fname Minit Lname Ssn Bdate Address Sex Salary Super_ssn Dno AAAA DEPARTMENT Dname Dnumber Mgr_ssn Mgr_start_date AAA DEPT LOCATIONS Dnumber Dlocation PROJECT Pname Pnumber Plocation Dnum WORKS_ON Essn Pno Hours DEPENDENT Referential integrity constraints displayed on the COMPANY relational database Essn Dependent_name Sex Bdate Relationship schema. One possible database state for the COMPANY relational database schema. EMPLOYEE Fname Minit Lname Ssn Bdate Address Sex Salary Super_ssn Dno John B Smith 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 333445555 888665555 1955-12-08638 Voss, Houston, TX 1968-01-19 3321 Castle, Spring, TX 1941-06-20291 Berry, Bellaire, TX Franklin T Wong 333445555 M 40000 Alicia J Zelaya 999887777 F 25000 987654321 4 Jennifer Wallace 987654321 F 43000 888665555 4 Ramesh K Narayan 666884444 1962-09-15 975 Fire Oak, Humble, TX M 38000 333445555 Joyce A English 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 Borg 888665555 1937-11-10 | 450 Stone, Houston, TX M 55000 NULL 1 DEPARTMENT DEPT_LOCATIONS Dname Dnumber Mgr_ssn Mgr_start_date Dnumber Dlocation Research 333445555 1988-05-22 1 Houston Administration 4 987654321 1995-01-01 4 Stafford Headquarters 888665555 1981-06-19 Bellaire Sugarland Houston WORKS_ON PROJECT Essn Pno Hours Pname Pnumber Plocation Dnum 123456789 1 32.5 ProductX 1 Bellaire 123456789 2 7.5 ProductY Sugarland 5 666884444 3 40.0 ProductZ 3 Houston 5 453453453 1 20.0 Computerization 10 Stafford 4 453453453 2 20.0 Reorganization 20 Houston 1 333445555 10.0 Newbenefits 30 Stafford 4 333445555 3 10.0 333445555 10 10.0 DEPENDENT 333445555 20 10.0 Essn Dependent_name Sex Bdate Relationship 999887777 30 30.0 333445555 Alice F 1986-04-05 Daughter 999887777 10 10.0 333445555 Theodore M 1983-10-25 Son 987987987 10 35.0 1958-05-03 Spouse 333445555 Joy F 987987987 30 5.0 987654321 Abner 1942-02-28 Spouse 987654321 30 20.0 123456789 Michael M 1988-01-04 Son 987654321 20 15.0 123456789 Alice F 1988-12-30 Daughter 888665555 20 NULL 123456789 Elizabeth 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
Complex Datatypes
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
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