Guidelines: ⦁ All problems must be solved using SQL code. The SQL code and the output of your query should be cut and pasted into your submitted file (via Canvas). ⦁ Output Tables must be a screensh

Guidelines:

⦁ All problems must be solved using SQL code. The SQL code and the output of your query should be cut and pasted into your submitted file (via Canvas).  

⦁ Output Tables must be a screenshot taken from the DBMS output page.

⦁ You must display the whole output for each problem. Points will be deducted for partial displays.

⦁ Do not paste outputs from multiple queries into a single result. This does not apply to problems 1,3, and 4

⦁ All decimals should be rounded to two decimal places. I do not want to see a number that looks like 55.6783457828. It should be 55.68.

⦁ Check your work carefully. I am not very sympathetic to errors that would easily turn up if checked.

⦁ You may assume the file will not change in size (unless stated).

⦁ Certainly, if you have questions, you are welcome to call me (908-418-6078) or send an email. More specifically, this is not a math test, so if you do not know how to do a calculation, I will provide that information.

⦁ Submit one document only, unzipped.

⦁ Handwritten problems will not be accepted unless permission is granted by ME.

⦁ Check the Announcements page during the week for any of my comments and corrections regarding the assignment

Submission Requirements:

⦁ This assignment is due Monday December 6, 2021 at 6pm. IT WILL NOT BE ACCEPTED LATE .

⦁ It must be submitted via Canvas.

⦁ Submit one document only, unzipped.

⦁ Handwritten problems will not be accepted unless permission is granted by ME.  

⦁ All work must be your own. The only person you may discuss the assignment with is me (Professor Forman)

⦁ You may NOT discuss problems with any other student.

⦁ You may NOT get answers from sites such as Chegg or Homework Hero or any other online site.

⦁ Anything not mentioned, that constitutes “not doing your own work” will be considered cheating.

⦁ Violation of these requirements will result in a grade of 0.

Problem 1 (10 points):

Add the following two records to the Employee table and the new department record to the department table. Cut and paste the code and display the new tables.

Employee Table Attribute Person 1 Person 2

Fname Quick Ba

Minit D B

Lname McGraw Louie

Ssn 456-12-8888 312-12-7777

Bdate 1961-04-07 1971-03-14

Address 100 Horse, Newark, De 200 Goat, Newark, NJ

Sex M M

Salary 40000 33000

Super_ssn 888-66-5555 456-12-8888

Dno 9 9

Department Table Attribute New Department

Dname Cartoons

Dnumber 9

Mgr_ssn 456-12-8888

Mgr_start_date 2021-11-18

Problem 2 (10 points):

Using the data from problem 1, list all employees who have an address shown that is not in TX (Texas) and their manager. Display Fname, Lname, Address, SSN and dname and dnumber. Order by SSN in descending order

Problem 3 (10 points):

The record added in Problem 1  for McGraw was entered with an error. The salary should have been 45000. The rest of the information was correct. Correct the record and display just that record. You do not have to rerun problem 1 or 2, but you do need to show the SQL code used to make the correction.  

Problem 4 (5 points):

Delete the records for Mr. McGraw and Mr. Louie. Then delete the new department record that was created.  

Show the DBMS statement that confirms that the record was deleted. Display the Employee and Department tables after the record has been deleted. I need to see some representation from the DBMS that shows the record was deleted.  

Problem 5 (15 points)

Each employee gets an insurance policy with the following death benefits:

Spouse gets 20% of employee’s Salary

Daughter gets 10% of employee’s Salary

Son gets no death benefit.  

Display each employee and their dependent’s death benefit. Do not show dependent who gets a death benefit of zero. The attributes to display and the required column headings are shown below. Your column headings should be exactly as shown in the Display As column. Order output by employee SSN followed by dependent first name.

Attribute Display As

Employee SSN  Employee SSN

Employee Fname EE First Name

Employee Lname EE Last Name

Employee Salary EE Salary

Dependent Name Dep First Name

Dependent Bonus Percentage Pct

Dependent Bonus Amount Dep Bonus Amt

Relationship Rel

Problem 6 (15 Points)

Assuming that each employee works 52 weeks per year and that the salary shown in the Employee table is an annual salary, compute each employee’s weekly wage. Display SSN, Last Name, Salary, Weekly Wage exactly as shown in this sentence for females whose weekly salary is less than $750/week, Order by salary followed by SSN.  

Problem 7 (15 points)

Display the name of the department, the department number, and the number of people for those departments that have fewer than2 people.

Problem 8 (10 points)

For each project in the Works_on table, display the Project Name, Project Number, the Number of people working on the project and the average hours worked. Arrange your Count so that nulls are NOT counted. Order by pnumber, descending order. Column headings should be Proj Name, Proj Number, Workers, Avg. Hours; EXACTLY as stated.

Problem 9 (5 points)

Dump the following tables. Use the format Select * from table name.

Employee

Department

Works_On

Project

Dependent

This is not a trick question.