(1) Retrieve the names (fname, lname) of employees in department 1 who work less

Question

(1) Retrieve the names (fname, lname) of employees in department 1 who work less than 11 hours per week on the 'River' project. (“River” is the project name.)

(2) List the name (fname, lname) of the direct supervisor of 'John Smith'.

(3)List the name (fname, lname) of the manager of department 1.

(4) List the name (fname,lname) of male dependents, if the dependents depend on an employee who works for the ‘EEE’ department. (‘EEE’ is department name)

EMPLOYEE FNAMEMINIT LNAME SSNBDATE ADDRESS SEX SALARY SUPERSSNDNO DEPARTMENT DNAME DNUMBER MGRSSN DEPT LOCATIONS DLOCATION PROJECT PNAME PNUMBER PLOCATION DNUM WORKS_ON ESSN PNOHOURS DEPENDENT ESSN DEPENDENT NAME SEXBDATE RELATIONSHIP

Explanation / Answer

1)

Query to Retrieve the names (fname, lname) of employees in department 1 who work less than 11 hours per week on the 'River' project. (“River” is the project name.)

SELECT FNAME,LNAME FROM EMPLOYEE E,DEPARTMENT D, PROJECT P,WORKS_ON W WHERE E.DNO=D.DNUMBER AND DNO=1 AND D.DNUMBER=P.DNUM AND P.PNAME='River' AND P.PNUMBER=P.PNO AND P.HOURS <11;

2)

Query to List the name (fname, lname) of the direct supervisor of 'John Smith'.

SELECT E.FNAME,E.LNAME FROM EMPLOYEE E, EMPLOYEE EM WHERE EM.FNAME='John' AND EM.LNAME='Smith' AND EM.SSN=E.SSN;

3)

Query to List the name (fname, lname) of the manager of department 1.

SELECT FNAME,LNAME FROM EMPLOYEE E, DEPARTMENT D WHERE E.DNO=D.DNO AND D.MGRSSN=E.SSN AND D.DNO=1;

4)

Query to List the name (fname,lname) of male dependents, if the dependents depend on an employee who works for the ‘EEE’ department. (‘EEE’ is department name).

SELECT FNAME,LNAME FROM EMPLOYEE E,DEPENDENT D,DEPARTMENT DP WHERE E.SSN=D.ESSN AND D.SEX='Male' AND E.DNO=DP.DNUMBER AND DP.NAME='EEE';

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.