Submission:

Your answer document MUST show SQL statement for each question (No query results

required).

Copy and paste SQL statements Microsoft Word or PDF file and submit the Word or PDF file

Put question #, too.

Note:

Except for Question #18, when you join tables, you may use old-style join which is covered in

Chapter 7.

You can use any database software (MySQL, Oracle, SQL Server, etc.) to do this homework. I do

not recommend any single-user DBMS (e.g., MS Access) because they are NOT 100% compliant

to SQL standards.

Answer the following questions.

Use the operator and/or statement asked. Otherwise, you will have zero (0) point for the

question.

1. List the employee SSN, first and last name, birth date, and salary of each employee.

2. For each project, list the project name and its average hours worked, but consider only those

projects for which the average work hours is more than 15.

3. List the employee SSN and number of hours an employee has worked on a project.

4. Employees get a 10% increase in their salary. List employee SSN, first and last name, the old salary,

and the new salary of each employee.

5. List the name of each employee who does not live in Illinois. Use conditional restriction IL in

WHERE statement, not CA and MO.

6. List the name of employees whose address contains the word "Oak You have to use ADDRESS

column in WHERE statement.

7. List all employees who earn more than $40,000, and are either in department 3 or are male. List

each employee first and last name, salary, gender, and department number.

8. List all employees who earn exactly $43,000, $50,000, or $55,000. List each employee first and

last name, and salary. Use IN operator. Do not use OR operator.

9. For each project, list project number and total hours worked per project. Label the two columns

Project Number and Total Hours, respectively. Sort the output by Total Hours from largest to

smallest.

10.List the employee first and last name who doesnt work on any project.

11.For each employee, list employee first and last name, the department the employee belongs to,

hours worked on projects, project name.

12.List the number of male dependents.

13.You want to track the type of relationship within the Dependent table. List the dependent

relationship without duplicating output rows.

14.List employee first and last name whose salary is between $35,000 and$50,000. Use BETWEEN

operator. Do NOT use AND or OR operator.

15.Find the name for each employee whose department is Admin and Records. Use the IN

operator and subquery in your formulation.

16. Repeat Question #15, but this time use the EXISTS operator in your formulation.

17. Find the employee SSN, and employees first and last name, and salary for each

employee whose salary is more than $40,000 or who is in project number 1. Use

UNION set operator.

18. List the employee SSN, employees first and last name, his/her dependent name, and

relationship. Be sure all employees are included in the result, regardless of whether

he/she has any dependents or not. You have to use outer join.

19.List the department name, employee SSN, and employees first and last name, and

salary of each employee who is in production department and his/her salary is at least

$35,000.

20. List employee SSN, and employees first and last name, and salary, who are in

project number 30.

CREATE TABLE department ( dpt_no name mgr ssn mgr start_date CHAR (2) NOT NULL PRIMARY KEY, CHAR (20) CHAR (9), DATE CREATE TABLE project ( pro_number pro_name location dpt_no CHAR (2) PRIMARY CHAR (25), CHAR (25), KEY, CHAR (2) CREATE TABLE employee ( CHAR (9) PRIMARY KEY, CHAR (25), CHAR (25) CHAR (25) CHAR (50), CHAR (25) CHAR (2), CHAR (9) DATE, decimal(7,2), CHAR (4) CHAR (1), CHAR (2), CHAR (9) emp s5n last name first name middle_name address city state date_of_birth salary parking space gender dpt_no super ssn CREATE TABLE assignment ( emp ssn pro_number work_hours PRIMARY KEY (emp ssn, pro_number) CHAR (9) NOT NULL, CHAR (2) NOT NULL , decimal(5,1), CREATE TABLE dependent( emp ssn dep name gender date_of_birth relationship CHAR(9) NOT NULL, CHAR (50) NOT NULL , CHAR (1), DATE, CHAR (10) PRIMARY KEY (emp ssn, dep_name)