Question

CREATE TABLE DEPT ( DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY, DNAME VARCHAR2(14), LOC VARCHAR2(13) ); CREATE TABLE EMP ( EMPNO NUMBER (2) CONSTRAINT PK_EMP PRIMARY KEY, ENAME VARCHAR2(10), JOB VARCHAR2(9), HIREDATE DATE, MGR NUMBER(4), DEPTNO NUMBER(2), CONSTRAINT FK_MGR FOREIGN KEY (MGR) REFERENCES EMP(EMPNO) CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO) ); CREATE TABLE PRI ( PRJNO NUMBER (2) CONSTRAINT PK_PRJ PRIMARY KEY, PNAME VARCHAR2(15)); CREATE TABLE WORK_ON ( EMPNO NUMBER (2) || PRJNO NUMBER(2), STARTDATE DATE, ENDDATE DATE, LOC VARCHAR2(15), CONSTRAINT PK WORK ON PRIMARY KEY (EMPNO,PRJNO, STARTDATE), CONSTRAINT FK_EMPNO FOREIGN KEY (EMPNO) REFERENCES EMP(EMPNO). CONSTRAINT FK PRJNO FOREIGN KEY (PRINO) REFERENCES PRJ(PRJNO) );

Write SQL Queries Given the following tables

1. (7pts) Retrieve the names of employees and their project name. If the employee never worked on a project, show the names only the name, not the project name.

2. (7pts) Retrieve the names of employees who have worked on the same project at a different location.

3. (7pts) Retrieve the names of employees who have worked on more than two different projects.

4. (7pts) Retrieve the names of employees who manage more than two employees.

Answer 1

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

-- Retrieve the names of employees and their project name. If the employee never worked on a project, show the names only the name, not the project name.

SELECT ENAME, PNAME

FROM EMP LEFT OUTER JOIN WORK_ON ON EMP.EMPNO = WORK_ON.EMPNO INNER JOIN PRJ

ON WORK_ON.PRJNO = PRJ.PRJNO;

-- Retrieve the names of employees who have worked on the same project at a different location.

SELECT ENAME

FROM EMP, WORK_ON AS W1, WORK_ON AS W2

WHERE EMP.EMPNO = W1.EMPNO AND EMP.EMPNO = W2.EMPNO AND W1.PRJNO = W2.PRJNO AND W1.LOC<>W2.LOC;

-- Retrieve the names of employees who have worked on more than two different projects.

SELECT ENAME

FROM EMP, WORK_ON

WHERE EMP.EMPNO = WORK_ON.EMPNO

GROUP BY EMP.EMPNO, ENAME

HAVING COUNT(DISTINCT PRJNO)>2;

-- Retrieve the names of employees who manage more than two employees.

SELECT M.ENAME

FROM EMP AS E, EMP AS M

WHERE E.MGR = M.EMPNO

GROUP BY E.MGR, M.ENAME

HAVING COUNT(*)>2;