DEPARTMENT TABLE: DNAME DNUMBER MGR_SSN MGR_START_DATE RESEARCH 5 333445555 1988

Question

DEPARTMENT TABLE:

DNAME DNUMBER MGR_SSN MGR_START_DATE

RESEARCH 5 333445555 1988-05-22

ADMINISTRATION 4 987654321 1995-01-01

HEADQUARTERS 1 888665555 1981-06-19

DEPT_LOCATIONS TABLE:

DNUMBER DLOCATION

1 HOUSTON

4 STRAFFORD

5 BELLAIRE

5 SUGARLAND

5 HOUSTON

FOR EACH DEPARTMENT, LIST ITS DEPARTMENT NUMBER, NAME, AND THE AVERAGE SALARY OF ITS EMPLOYEES?

RETRIEVE THE NAMES OF DEPARTMENTS THAT LOCATE IN HOUSTON?

RETRIEVE THE NAMES OF EMPLOYEES WHO WORK MORE THAN 30 HOURS ON A PROJECT, LIST HIS/HER NAME ALONG WITH THE CORRESPONDING PROJECT NAME AND HOURS?

Explanation / Answer

1

SELECT DNUMBER,DNAME,AVG(SALARY) FROM DEPARTMENT , EMPLOYEE WHERE DEPARTMENT.DNUMBER = EMPLOYEE.DNO GROUP BY DEPARTMENT.DNUMBER;

2

SELECT DNAME FROM DEPARTMENT, DEPT_LOCATIONS WHERE DEPARTMENT.DNUMBER = DEPT_LOCATIONS.DNUMBER AND DLOCATION = 'HOUSTON';

3.

SELECT FNAME,MINIT,LNAME, PNAME,HOURS FROM EMPLOYEE INNER JOIN WORKS_ON ON EMPLOYEE.SSN = WORKS_ON.ESSN INNER JOIN PROJECT ON WORKS_ON.PNO = PROJECT.PNUMBER WHERE HOURS > 30;

Do ask if any doubt. Please upvote.

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

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