SQL assignment

use SQL SERVER 2008 relational DBMS.

This assignment involves implementation and querying of a relational database called the Company using SQL SERVER.

EMPLOYEE
FNAME	MINIT	LNAME	SSN	BDATE	ADDRESS	SEX	SALARY	SUPERSSN	DNO
DEPARTMENT
DNAME	DNUMBER	MGRSSN	MGRSTRARTDATE
DEPT_LOCATIONS
DNUMBER	DLOCATION
PROJECT
PNAME	PNUMBER	PLOCATION	DNUM
WORKS_ON
ESSN	PNO	HOURS
DEPENDENT
DEP#	DEPENDENTNAME	SEX	BDATE	RELATIONSHIP	ES SN

Legend:

Department # is designated Dnumber, Dno, or Dnum

Employee ID is designated as SSN, Superssn, or Essn

Project # is designated as Pnumber, or Pno

After setting up all six relations ,execute SQL commands to perform the following queries:

1. What is the total salary of all female and male employees combined? The total column should be labeled Total Salary.

2. Which employees worked on two or more projects? Report must include employees’ first and last name.

3. List the first and last names of employees who have a dependent with the same first name as themselves.

4. Retrieve the first and last names of employees in department 5 who work 7 hours or more per week on the 'Product Y' project. The report must include a department name column.

5. List the first and last name and SSN of employees who have no dependents.

6. Find the first and last names of employees who are NOT directly supervised by 'Franklin Wong'.

7. For each project, list the project name and the total hours per week (by all employees) on that project. The report must have the project name and the total hrs. The total hours column should read total hrs worked and the project name column should read project name.

8. Retrieve the first and last names and SSN of employees who do not work on any project.

9. For each department, retrieve the department name and the average salary of employees working in that department. The salary column should be labeled Emp Avg Salary and dept column as Department.

Use Notepad to write your SQL code and then copy and paste it into SQL query window.

WARNING: you are not allowed to use information in your SQL code that was not given in the problem. A good example is using someone’s SSN when only their name is given to you in the problem. Every time this instruction is violated, even when the program returns the right results, you will loose 50% of the points. In the real world it is unrealistic to search through millions of records to find someone’s SSN. In some instances this information may be protected and off limits to you.

EMPLOYEE

FNAME	MINIT	LNAME	SSN	BDATE	ADDRESS	SEX	SALARY	SUPERSSN	DNO
John	B	Smith	123456789	09-JAN-55	731 Fondren, Houston, TX	M	30000	333445555	5
Franklin	T	Wong	333445555	08-DEC-45	638 Voss, Houston, TX	M	40000	888665555	5
Alicia	J	Zelaya	999887777	19-JUL-58	3321 Castle, Spring, TX	F	25000	987654321	4
Jennifer	S	Wallace	987654321	20-JUL-31	291 Berry, Bellaire, TX	F	43000	888665555	4
Ramesh	K	Narayan	666884444	15-SEP-52	975 Fire Oak, Humble, TX	M	38000	333445555	5
Joyce	A	English	453453453	31-JUL-62	5631 Rice, Houston, TX	F	25000	333445555	5
Ahmad	V	Jabbar	987987987	29-MAR-59	980 Dallas, Houston, TX	M	25000	987654321	4
James	E	Borg	888665555	10-NOV-27	450 Stone, Houston, TX	M	55000	NULL	1

DEPARTMENT

DNAME	DNUMBER	MGRSSN	MGRSTARTDATE
Research	5	333445555	22-MAY-78
Administration	4	987654321	01-JAN-85
Headquarters	1	888665555	19-JUN-71

DEPT_LOCATIONS

DNUMBER	DLOCATION
1	Houston
4	Stafford
5	Bellaire
5	Sugarland
5	Houston

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
453453453	1	20
453453453	2	20
333445555	1	10
333445555	2	10
333445555	3	10
333445555	10	10
333445555	20	10
333445555	30	10
999887777	10	10
999887777	30	30
987987987	10	35
987987987	30	5
987654321	20	15
987654321	30	20
888665555	20

DEPENDENT

DEPT#	DEPENDENTNAME	SEX	BDATE	RELATIONSHIP	ESSN
1	John	M	10-JAN-87	SON	123456789
2	Sarah	F	01-FEB-92	DAUGHTER	123456789
3	Robert	M	11-MAR-25	FATHER	333445555
4	Diane	F	04-APR-91	DAUGHTER	999887777
5	Jennifer	F	23-MAY-90	DAUGHTER	987654321
6	Sidhah	M	01-JAN-86	SON	666884444
7	Maboob	M	31-DEC-85	SON	453453453
8	Lisa	F	12-JUN-77	DAUGHTER	987987987