Question

Please provide SQL code for following question, and please make sure your answer is LEGIBLE. Thank you.

Retrieve a list of employees and the projects they are working on, ordered by department, and within each department, ordered alphabetically by last name, first name

Answer 1

SAMPLE TABLES ASSUMED FOR GIVEN QUERY with FIELDS

EMPLOYEE (FNAME, MINIT, LNAME, SSN, BDATE, ADDRESS, SEX, SALARY, #SUPERSSN, #DNO)

DEPARTMENT (DNAME, DNUMBER, #MGRSSN, MGRSTARTDATE)

PROJECT (PNAME, PNUMBER, PLOCATION, #DNUM)

WORKS_ON (#ESSN, #PNO, HOURS)

SQL Query to Retrieve a list of employees and the projects they are working on, ordered by department and, within each department, ordered alphabetically by last name, first name

SELECT                  DNAME, LNAME, FNAME, PNAME

FROM                    DEPARTMENT, EMPLOYEE,WORKS_ON, PROJECT

WHERE                 DNUMBER = DNO AND SSN = ESSN AND PNO = PNUMBER

ORDER BY            DNAME DESC, LNAME ASC, FNAME ASC;