CSC 381: Database and File Systems, Fall 2007
SQL Exercises
For each query below, write an SQL select statement. Assume that the following tables have been created
(this database is available in the folder I:\10708_CSC381\public\CompanyDB
; the code to create it
is here):
DEPARTMENT
DNAME | DNUMBER | MGRSSN | MGRSTARTDATE |
Research |
5 |
333445555 |
1988-05-22 |
Administration |
4 |
987654321 |
1995-01-01 |
Headquarters |
1 |
888665555 |
1981-06-19 |
EMPLOYEE
FNAME | LNAME | SSN | BDATE | ADDRESS | SEX | SALARY | SUPERSSN | DNO |
John |
Smith |
123456789 |
1965-01-09 |
731 Fondren, Houston TX |
M |
30000 |
333445555 |
5 |
Franklin |
Wong |
333445555 |
1965-12-08 |
638 Voss, Houston TX |
M |
40000 |
888665555 |
5 |
Alicia |
Zelaya |
999887777 |
1968-01-19 |
3321 Castle, Spring TX |
F |
25000 |
987654321 |
4 |
Jennifer |
Wallace |
987654321 |
1941-06-20 |
291 Berry, Bellaire TX |
F |
43000 |
888665555 |
4 |
Ramesh |
Narayan |
666884444 |
1962-09-15 |
975 Fire Oak, Humble TX |
M |
38000 |
333445555 |
5 |
Joyce |
English |
453453453 |
1972-07-31 |
5631 Rice, Houston TX |
F |
25000 |
333445555 |
5 |
Ahmad |
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 |
PROJECT
PNAME | PNUMBER | PLOCATION | DNUM |
ProductX |
1 |
Bellaire |
5 |
ProductY |
2 |
Sugarland |
5 |
ProductZ |
3 |
Houston |
5 |
Computerization |
10 |
Stafford |
4 |
Reorganization |
20 |
Houston |
1 |
Newbenefits |
30 |
Stafford |
4 |
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 |
333445555 |
2 |
10.0 |
333445555 |
3 |
10.0 |
333445555 |
10 |
10.0 |
333445555 |
20 |
10.0 |
999887777 |
30 |
30.0 |
999887777 |
10 |
10.0 |
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 |
Alice |
F |
1986-04-04 |
Daughter |
333445555 |
Theodore |
M |
1983-10-25 |
Son |
333445555 |
Joy |
F |
1958-05-03 |
Spouse |
987654321 |
Abner |
M |
1942-02-28 |
Spouse |
123456789 |
Michael |
M |
1988-01-04 |
Son |
123456789 |
Alice |
F |
1988-12-30 |
Daughter |
123456789 |
Elizabeth |
F |
1967-05-05 |
Spouse |
DEPT_LOCATIONS
DNUMBER | DLOCATION |
1 |
Houston |
4 |
Stafford |
5 |
Bellaire |
5 |
Houston |
5 |
Sugarland |
- Select fname, minit, lname and dname of employees and the department each
works in.
- Produce a list of employees, fname, lname, salary, in order of increasing
salary.
- Produce a list of employees, fname, lname, salary, in order of decreasing
salary.
- Retrieve the names of all employees in department 5 who work more than
10 hours on the "ProductX" project.
- Retrieve the names of all employees who work on the "ProductX" project
less than 20 hours.
- List the names of all employees who have a dependent with the same first
name as themselves.
- Find the names of all employees who are directly supervised by Franklin
Wong (You are NOT to use Franklin's SSN in this query).
- For each project, list the project name and the total hours per week (by
all employees) spent on that project.
- List the names of employees who work on every project.
- List the names of all employees who do not work on any project.
- For each department, retrieve the department name and the average salary
of all employees working in the department.
- Find the names and addresses of all employees who work on at least one
project located in Houston but whose department has no location in Houston (This
is a tricky one).
- List the names of all department managers who have no dependents.
DePauw University,
Computer Science Department,
Fall 2007
Maintained by Brian Howard
(bhoward@depauw.edu
).
Last updated