Please answer the SQL questions according to the information below thanks Use the operator and or statement asked Otherwise, you will have zero (0) point for the question Your answer document MUST show SQL statement for each question ( No query results required ) 1 List the employee SSN,first and lastname,birthdate,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 Department Dept No Name Mgr SSN Mgr Start Date 1 Headquarters 999666666 2005 06 19 3 Admin and Records 999555555 2013 01 01 7 Production 999444444 1998 05 22 Employee EMP SSN FName LName Address City State Zip DOB Salary Park ing Space Gender Dep t No Super SSN 999111111 Bock Douglas 2 Mont Verd Dr St Louis MO 63121 1955 09 01 30000 542 M 7 999444444 999222222 Amin Hyder S Seaside Apt B Marina CA 93941 1969 03 29 25000 422 M 3 999555555 999333333 Joshi Dinesh 10 Oak St Collinsville IL 66234 1972 09 15 38000 332 M 7 999444444 999444444 Zhu Waiman 303 Lindber gh St Louis MO 63121 1975 12 08 43000 32 M 7 999666666 999555555 Joyner Suzanne 202 Burns Farm Marina CA 93941 1971 06 20 43000 3 F 3 999666666 999666666 Bordoloi Bijoy South Main 12 Edwardsvill e IL 62025 1967 11 10 55000 1 M 1 999887777 Markis Marcia High St 14 Monterey CA 93940 1978 07 19 25000 402 F 3 999555555 999888888 Prescott Sherri Overton Way 4 Edwardsvill e IL 62025 1972 07 31 25000 296 F 7 999444444 Assignment EMP SSN Pro Number Work Hours 999111111 1 31 4 999111111 2 8 5 999222222 10 34 5 999222222 30 5 1 999333333 3 42 1 999444444 1 999444444 10 10 1 999444444 2 12 2 999444444 20 11 8 999444444 3 10 5 999555555 20 14 8 999555555 30 19 2 999666666 20 999887777 10 10 2 999887777 30 30 8 999888888 1 21 0 999888888 2 22 0 Project Pro Number Pro Name Location Dept No 1 Order Entry St Louis 7 10 Inventory Marina 3 2 Payroll Collinsville 7 20 Personnel Edwardsville 1 3 Receivables Edwardsville 7 30 Pay Benefits Marina 3 Dependent EMP SSN Dep Name Gender Date of Birth Relationship 999111111 Deanna F 1978 12 31 DAUGHTER 999111111 Jeffery M 1978 01 01 SON 999111111 Mary Ellen F 1957 05 05 SPOUSE 999444444 Andrew M 1998 10 25 SON 999444444 Jo Ellen F 1996 04 05 DAUGHTER 999444444 Susan F 1975 05 03 SPOUSE 999555555 Allen M 1968 02 29 SPOUSE CREATE TABLE department ( dpt no CHAR(2) NOT NULL PRIMARY KEY, name CHAR(20), mgr ssn CHAR(9), mgr start date DATE ) CREATE TABLE project ( pro number CHAR(2) PRIMARY KEY, pro name CHAR(25), location CHAR(25), dpt no CHAR(2) ) CREATE TABLE employee ( emp ssn CHAR(9) PRIMARY KEY, last name CHAR(25), first name CHAR(25), middle name CHAR(25), address CHAR(50), city CHAR(25), state CHAR(2), zip CHAR(9), date of birth DATE, salary decimal(7,2), parking space CHAR(4), gender CHAR(1), dpt no CHAR(2), super ssn CHAR(9) ) CREATE TABLE assignment ( emp ssn CHAR(9) NOT NULL, pro number CHAR(2) NOT NULL , work hours decimal(5,1), PRIMARY KEY (emp ssn, pro number ) ) CREATE TABLE dependent ( emp ssn CHAR(9) NOT NULL, dep name CHAR(50) NOT NULL , gender CHAR(1), date of birth DATE, relationship CHAR(10), PRIMARY KEY (emp ssn, dep name) ) INSERT INTO department VALUES ( '7', 'Production', '999666666', '2005 06 19') INSERT INTO department VALUES ( '3', 'Admin and Records', '999555555', '2013 01 01') INSERT INTO department VALUES ( '1', 'Headquarters', '999444444', '1998 05 22') INSERT INTO project VALUES ( 1, 'Order Entry', 'St Louis', 7 ) INSERT INTO project VALUES ( 2, 'Payroll', 'Collinsville', 7 ) INSERT INTO project VALUES ( 3, 'Receivables', 'Edwardsville', 7 ) INSERT INTO project VALUES ( 10, 'Inventory', 'Marina', 3 ) INSERT INTO project VALUES ( 20, 'Personnel', 'Edwardsville', 1 ) INSERT INTO project VALUES ( 30, 'Pay Benefits', 'Marina', 3 ) INSERT INTO employee VALUES( '999666666', 'Bordoloi', 'Bijoy', NULL, 'South Main 12', 'Edwardsville', 'IL', 62025, '1967 11 10', 55000, 1, 'M', 1, NULL ) INSERT INTO employee VALUES( '999555555', 'Joyner', 'Suzanne', 'A', '202 Burns Farm', 'Marina', 'CA', 93941, '1971 06 20', 43000, 3, 'F', 3, '999666666' ) INSERT INTO employee VALUES( '999444444', 'Zhu', 'Waiman', 'Z', '303 Lindbergh', 'St Louis', 'MO', 63121, '1975 12 08', 43000, 32, 'M', 7, '999666666' ) INSERT INTO employee VALUES( '999887777', 'Markis', 'Marcia', 'M', 'High St 14', 'Monterey', 'CA', 93940, '1978 07 19', 25000, 402, 'F', 3, '999555555' ) INSERT INTO employee VALUES( '999222222', 'Amin', 'Hyder', NULL, 'S Seaside Apt B', 'Marina', 'CA', 93941, '1969 03 29', 25000, 422, 'M', 3, '999555555' ) INSERT INTO employee VALUES( '999111111', 'Bock', 'Douglas', 'B', ' 2 Mont Verd Dr ', 'St Louis', 'MO', 63121, '1955 09 01', 30000, 542, 'M', 7, '999444444' ) INSERT INTO employee VALUES( '999333333', 'Joshi', 'Dinesh', NULL, ' 10 Oak St ', 'Collinsville', 'IL', 66234,'1972 09 15', 38000, 332, 'M', 7, '999444444' ) INSERT INTO employee VALUES( '999888888', 'Prescott', 'Sherri', 'C', 'Overton Way 4', 'Edwardsville', 'IL', 62025, '1972 07 31', 25000, 296, 'F', 7, '999444444' ) INSERT INTO assignment VALUES ( '999111111', 1, 31 4) INSERT INTO assignment VALUES ( '999111111', 2, 8 5) INSERT INTO assignment VALUES ( '999333333', 3, 42 1) INSERT INTO assignment VALUES ( '999888888', 1, 21 0) INSERT INTO assignment VALUES ( '999888888', 2, 22 0) INSERT INTO assignment VALUES ( '999444444', 2, 12 2) INSERT INTO assignment VALUES ( '999444444', 3, 10 5) INSERT INTO assignment VALUES ( '999444444', 1, NULL) INSERT INTO assignment VALUES ( '999444444', 10, 10 1) INSERT INTO assignment VALUES ( '999444444', 20, 11 8) INSERT INTO assignment VALUES ( '999887777', 30, 30 8) INSERT INTO assignment VALUES ( '999887777', 10, 10 2) INSERT INTO assignment VALUES ( '999222222', 10, 34 5) INSERT INTO assignment VALUES ( '999222222', 30, 5 1) INSERT INTO assignment VALUES ( '999555555', 30, 19 2) INSERT INTO assignment VALUES ( '999555555', 20, 14 8) INSERT INTO assignment VALUES ( '999666666', 20, NULL) INSERT INTO dependent VALUES ( '999444444', 'Jo Ellen', 'F', '1996 04 05', 'DAUGHTER') INSERT INTO dependent VALUES ( '999444444', 'Andrew', 'M', '1998 10 25', 'SON') INSERT INTO dependent VALUES ( '999444444', 'Susan', 'F', '1975 05 03', 'SPOUSE') INSERT INTO dependent VALUES ( '999555555', 'Allen', 'M', '1968 02 29', 'SPOUSE') INSERT INTO dependent VALUES ( '999111111', 'Jeffery', 'M', '1978 01 01', 'SON') INSERT INTO dependent VALUES ( '999111111', 'Deanna', 'F', '1978 12 31', 'DAUGHTER') INSERT INTO dependent VALUES ( '999111111', 'Mary Ellen', 'F', '1957 05 05', 'SPOUSE')

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 22, 2024

Please answer the SQL questions according to the information below thanks Use the operator and/or statement asked. Otherwise, you will have zero (0) point for

Please answer the SQL questions according to the information below thanks

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

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

required).

1.List the employee SSN,first and lastname,birthdate,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 BETWEENoperator. Do NOT use AND or OR operator.

15. Find the name for each employee whose department is Admin and Records. Use the INoperator 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.

Department

Dept_No	Name	Mgr_SSN	Mgr_Start_Date
1	Headquarters	999666666	2005-06-19
3	Admin and Records	999555555	2013-01-01
7	Production	999444444	1998-05-22

Employee

EMP_SSN	FName	LName	Address	City	State	Zip	DOB	Salary	Park- ing Space	Gender	Dep t No	Super_SSN
999111111	Bock	Douglas	#2 Mont Verd Dr.	St. Louis	MO	63121	1955- 09-01	30000	542	M	7	999444444
999222222	Amin	Hyder	S. Seaside Apt. B	Marina	CA	93941	1969- 03-29	25000	422	M	3	999555555
999333333	Joshi	Dinesh	#10 Oak St.	Collinsville	IL	66234	1972- 09-15	38000	332	M	7	999444444
999444444	Zhu	Waiman	303 Lindber gh	St. Louis	MO	63121	1975- 12-08	43000	32	M	7	999666666
999555555	Joyner	Suzanne	202 Burns Farm	Marina	CA	93941	1971- 06-20	43000	3	F	3	999666666

999666666	Bordoloi	Bijoy	South Main #12	Edwardsvill e	IL	62025	1967- 11-10	55000	1	M	1
999887777	Markis	Marcia	High St. #14	Monterey	CA	93940	1978- 07-19	25000	402	F	3	999555555
999888888	Prescott	Sherri	Overton Way #4	Edwardsvill e	IL	62025	1972- 07-31	25000	296	F	7	999444444

Assignment

EMP_SSN	Pro_Number	Work_Hours
999111111	1	31.4
999111111	2	8.5
999222222	10	34.5
999222222	30	5.1
999333333	3	42.1
999444444	1
999444444	10	10.1
999444444	2	12.2
999444444	20	11.8
999444444	3	10.5
999555555	20	14.8
999555555	30	19.2
999666666	20
999887777	10	10.2
999887777	30	30.8
999888888	1	21.0
999888888	2	22.0

Project

Pro_Number	Pro_Name	Location	Dept_No
1	Order Entry	St. Louis	7
10	Inventory	Marina	3
2	Payroll	Collinsville	7
20	Personnel	Edwardsville	1
3	Receivables	Edwardsville	7
30	Pay Benefits	Marina	3

Dependent

EMP_SSN	Dep_Name	Gender	Date_of_Birth	Relationship
999111111	Deanna	F	1978-12-31	DAUGHTER
999111111	Jeffery	M	1978-01-01	SON
999111111	Mary Ellen	F	1957-05-05	SPOUSE
999444444	Andrew	M	1998-10-25	SON
999444444	Jo Ellen	F	1996-04-05	DAUGHTER
999444444	Susan	F	1975-05-03	SPOUSE
999555555	Allen	M	1968-02-29	SPOUSE

CREATE TABLE department ( dpt_no CHAR(2) NOT NULL PRIMARY KEY, name CHAR(20), mgr_ssn CHAR(9), mgr_start_date DATE ); CREATE TABLE project ( pro_number CHAR(2) PRIMARY KEY, pro_name CHAR(25), location CHAR(25), dpt_no CHAR(2) ); CREATE TABLE employee ( emp_ssn CHAR(9) PRIMARY KEY, last_name CHAR(25), first_name CHAR(25), middle_name CHAR(25), address CHAR(50), city CHAR(25), state CHAR(2), zip CHAR(9), date_of_birth DATE, salary decimal(7,2), parking_space CHAR(4), gender CHAR(1), dpt_no CHAR(2), super_ssn CHAR(9) ) ; CREATE TABLE assignment ( emp_ssn CHAR(9) NOT NULL, pro_number CHAR(2) NOT NULL , work_hours decimal(5,1), PRIMARY KEY (emp_ssn, pro_number ) ) ; CREATE TABLE dependent ( emp_ssn CHAR(9) NOT NULL, dep_name CHAR(50) NOT NULL , gender CHAR(1), date_of_birth DATE, relationship CHAR(10), PRIMARY KEY (emp_ssn, dep_name) ) ; INSERT INTO department VALUES ( '7', 'Production', '999666666', '2005-06-19'); INSERT INTO department VALUES ( '3', 'Admin and Records', '999555555', '2013-01-01'); INSERT INTO department VALUES ( '1', 'Headquarters', '999444444', '1998-05-22'); INSERT INTO project VALUES ( 1, 'Order Entry', 'St. Louis', 7 ); INSERT INTO project VALUES ( 2, 'Payroll', 'Collinsville', 7 ); INSERT INTO project VALUES ( 3, 'Receivables', 'Edwardsville', 7 ); INSERT INTO project VALUES ( 10, 'Inventory', 'Marina', 3 ); INSERT INTO project VALUES ( 20, 'Personnel', 'Edwardsville', 1 ); INSERT INTO project VALUES ( 30, 'Pay Benefits', 'Marina', 3 ); INSERT INTO employee VALUES( '999666666', 'Bordoloi', 'Bijoy', NULL, 'South Main #12', 'Edwardsville', 'IL', 62025, '1967-11-10', 55000, 1, 'M', 1, NULL ); INSERT INTO employee VALUES( '999555555', 'Joyner', 'Suzanne', 'A', '202 Burns Farm', 'Marina', 'CA', 93941, '1971-06-20', 43000, 3, 'F', 3, '999666666' ); INSERT INTO employee VALUES( '999444444', 'Zhu', 'Waiman', 'Z', '303 Lindbergh', 'St. Louis', 'MO', 63121, '1975-12-08', 43000, 32, 'M', 7, '999666666' ); INSERT INTO employee VALUES( '999887777', 'Markis', 'Marcia', 'M', 'High St. #14', 'Monterey', 'CA', 93940, '1978-07-19', 25000, 402, 'F', 3, '999555555' ); INSERT INTO employee VALUES( '999222222', 'Amin', 'Hyder', NULL, 'S. Seaside Apt. B', 'Marina', 'CA', 93941, '1969-03-29', 25000, 422, 'M', 3, '999555555' ); INSERT INTO employee VALUES( '999111111', 'Bock', 'Douglas', 'B', '#2 Mont Verd Dr.', 'St. Louis', 'MO', 63121, '1955-09-01', 30000, 542, 'M', 7, '999444444' ); INSERT INTO employee VALUES( '999333333', 'Joshi', 'Dinesh', NULL, '#10 Oak St.', 'Collinsville', 'IL', 66234,'1972-09-15', 38000, 332, 'M', 7, '999444444' ); INSERT INTO employee VALUES( '999888888', 'Prescott', 'Sherri', 'C', 'Overton Way #4', 'Edwardsville', 'IL', 62025, '1972-07-31', 25000, 296, 'F', 7, '999444444' ); INSERT INTO assignment VALUES ( '999111111', 1, 31.4); INSERT INTO assignment VALUES ( '999111111', 2, 8.5); INSERT INTO assignment VALUES ( '999333333', 3, 42.1); INSERT INTO assignment VALUES ( '999888888', 1, 21.0); INSERT INTO assignment VALUES ( '999888888', 2, 22.0); INSERT INTO assignment VALUES ( '999444444', 2, 12.2); INSERT INTO assignment VALUES ( '999444444', 3, 10.5); INSERT INTO assignment VALUES ( '999444444', 1, NULL); INSERT INTO assignment VALUES ( '999444444', 10, 10.1); INSERT INTO assignment VALUES ( '999444444', 20, 11.8); INSERT INTO assignment VALUES ( '999887777', 30, 30.8); INSERT INTO assignment VALUES ( '999887777', 10, 10.2); INSERT INTO assignment VALUES ( '999222222', 10, 34.5); INSERT INTO assignment VALUES ( '999222222', 30, 5.1); INSERT INTO assignment VALUES ( '999555555', 30, 19.2); INSERT INTO assignment VALUES ( '999555555', 20, 14.8); INSERT INTO assignment VALUES ( '999666666', 20, NULL); INSERT INTO dependent VALUES ( '999444444', 'Jo Ellen', 'F', '1996-04-05', 'DAUGHTER'); INSERT INTO dependent VALUES ( '999444444', 'Andrew', 'M', '1998-10-25', 'SON'); INSERT INTO dependent VALUES ( '999444444', 'Susan', 'F', '1975-05-03', 'SPOUSE'); INSERT INTO dependent VALUES ( '999555555', 'Allen', 'M', '1968-02-29', 'SPOUSE'); INSERT INTO dependent VALUES ( '999111111', 'Jeffery', 'M', '1978-01-01', 'SON'); INSERT INTO dependent VALUES ( '999111111', 'Deanna', 'F', '1978-12-31', 'DAUGHTER'); INSERT INTO dependent VALUES ( '999111111', 'Mary Ellen', 'F', '1957-05-05', 'SPOUSE');