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.

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)