Question

ules for submission:

1. All problems must be solved using SQL code. The SQL code and the output of your query should be cut and pasted into your MS Word file.

a. Output Tables must be screenshot from DBMS

b. Do not create a MS Word (or other application) table and paste the results into it.

c. With the exception of Problems 4, 5 and 6, all output should be the result of a single step. Do not paste outputs from multiple steps into a single result.

Problem 7 (20 points):

Assume that each employee earns 25.75 per hour and works 52 weeks per year. Compute their annual earnings per project. Round results to two decimal places. For each employee list his full name (Bruce Forman as a single field, not the space), project name, project number, location, department name, hours, and earnings (two decimal places, comma for thousands: 12345 should be 12,345.00). Order by Department Name, Project Name, Last Name.

Chapter 5 The Relational Data Model and Relational Database Constraints 162 Figure 5.6 One possible database state for the COMPANY relational database schema EMPLOYEE Fame Mint Lname San Bdate Se Salary Supern Dno 30000 333445555 Address 123456789 1965-01-09 731 Fondren, Houston, TX M 333445588 1955-12-08 e3e Voen, Houston, TX 990a87777 1968O1-19 3321 Castle, Spring TX se7e54321 1941-06-20 291 Berry. Belaire, TX e06884444 1962-09-15 975 Fire Oak, Humble, TX 1972-07-31 5631 Rice, Houston, TX 1909-03-29 980 Dalas, Houston, TX Smith John Franklin Wong M4000088sesss66 Alicia Zelaya 25000 087654321 Jennifer Walace 43000 esse65555 Ramesh Narayan M38000 333445555 25000 333445556 M 25000 0e7654321 M55000 NULL 453453453 Joyce English Jabbar 907987907 Ahmad James Borg 88866555s 1937-11-10 450 Stone, Houston, TX DEPARTMENT DEPT LOCATIONS Dnumber Drame Mgr n 333445655 Mar start date 1908-06-22 Drumber Diocation Resnarch Houston Administration 907664321 1995-01-01 Stafford 8esesss5 Headquarters 1981-06-19 Belaire Sugarland Houston WORKS OM PROJECT Esan Pno Hours Prame Priumber Plocation Drum 123456700 32.5 ProductX Bellaire 123456709 7.5 ProducrY Sugarland 606684444 40.0 Productz Houston 453453453 20.0 Computerization Reorganication Newbenefts 10 Stallord 453453453 20.0 Houston 20 333445555 10.0 30 Stafford 333445556 10.0 333446555 10 10.0 DEPENDENT 33344555 20 10.0 Ess Depandent name Relationship Sex Bdate 00087777 30 30.0 333445555 Alice 1900-04-05 Daughter 09ge07777 10 100 Theodore 333445556 1983-10-25 Son 10 987087007 35.0 333445555 Joy 1958-05-03 Spouse Spouse 9877987 30 987654321 Abnar 1942-02-20 M. 987054321 30 20.0 123456789 Michael 1988-01-04 Son 7654321 20 15.0 123456789 Alice 1988-12-30 Daughter 880665ss5 20 NULL Elcabeth 123456789 1907-06-05 Spouse 2.

Answer 1

If you have any errors/doubts, please give me comment...

SELECT CONCAT(Fname,' ', Lname) AS empName, Pname, Pnumber, Plocation, Dname, Hours, FORMAT(Hours*52*25.75, 2) AS earnings

FROM EMPLOYEE, WORKS_ON, PROJECT, DEPARTMENT

WHERE Ssn = Essn AND Pno = Pnumber AND Dnum = Dnumber

ORDER BY Dname, Pname, Lname;