Hi, I'm needing the SQL statements for the following questions. The SQL script provided can be directly copied into a DBMS. I have the first one question. I just don't understand how to do averages and while accessing different tables. If this could be answered in the next 2 hours, that would be amazing. Thank so much.

SQL Script

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');

Questions

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

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.