Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Please answer the SQL questions 11-20 according to the information below thanks and make it look like the example image below Use the operator and/or

Please answer the SQL questions 11-20 according to the information below thanks and make it look like the example image below

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). 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. UseBETWEENoperator. Do NOT use AND or OR operator.15. Find the name for each employee whose department is Admin and Records. Use the INoperator andsubquery 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.

image text in transcribed

image text in transcribedimage text in transcribedimage text in transcribed

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');
Question 1: SQL Script: /*1.List the employee SSN,first and lastname,birthdate,and salary of each employee/ select emp_ssn, first_name, last_name, date_of_birth , Salary from employee; Screen in SQL Server Management Studio (SSMS) EmpProjectDB.sql- NAGESH-PC.Demo (NAGESH-PCNagesh (52)) icsoft SQL Server Man...- File Edit View Query Project Debug Tools Window Help New Query Dema EmpProjectDB.sq. SH-PCNagesh (52))" X /*1. List the employee ssn, first and lastname , birthdate,and salary of each employee" select emp ssnfirst_name, last_name, date_of birth Salary from employee; 100 % RestsMessages emp ssn ftmet name date_of birh Salany 2 999222222 HyderAmn 3 999333333 Dinesh Joshi 4 999444444 Waiman Zu 1955-09-01 30000.00 196903-29 25000.00 1972-09-15 39000.00 1975-12-08 43000.00 5 999555555 Suzanne Joyner 1971-6-20 43000.00 6 999666666 Bjoy Bordoloi 1967-11-10 55000.0 Markis Prescott 972-07-31 25000.00 999887777 Marcia 978-07-19 25000.00 8 999888888 Shem

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Entity Alignment Concepts Recent Advances And Novel Approaches

Authors: Xiang Zhao ,Weixin Zeng ,Jiuyang Tang

1st Edition

9819942527, 978-9819942527

More Books

Students also viewed these Databases questions