JOIN AND SUBQUERY

DataBase Management Systems
(M.Sc. Sem I)
(Joins and Subquery)


1.SELECT E.FIRST_NAME AS "EMP_NAME",D.DEPARTMENT_NAME,
D.DEPARTMENT_ID AS "DEPARTMENT_NO."
FROM EMPLOYEES E,DEPARTMENTS D
WHERE E.DEPARTMENT_ID=D.DEPARTMENT_ID
OUTPUT:
EMP_NAME DEPARTMENT_NAME DEPARTMENT_NO.
Steven Executive 90
Neena Executive 90
Lex Executive 90
Alexander IT 60
Bruce IT 60
David IT 60
Valli IT 60
Diana IT 60
Nancy Finance 100
Daniel Finance 100


2. SELECT FIRST_NAME AS "EMPLOYEE_NAME",DEPARTMENT_NAME,
LOCATION_ID AS "LOCATION"
FROM EMPLOYEES,DEPARTMENTS D WHERE D.MANAGER_ID BETWEEN 100 AND 200
OUTPUT:
EMPLOYEE_NAME DEPARTMENT_NAME LOCATION
Ellen Administration 1700
Sundar Administration 1700
Mozhe Administration 1700
David Administration 1700
Hermann Administration 1700
Shelli Administration 1700
Amit Administration 1700
Elizabeth Administration 1700
Sarah Administration 1700
David Administration 1700

3.SELECT E.FIRST_NAME,E1.FIRST_NAME,E.DEPARTMENT_ID,E1.DEPARTMENT_ID
FROM EMPLOYEES E,EMPLOYEES E1 WHERE E.DEPARTMENT_ID!=E1.DEPARTMENT_ID
OUTPUT:
FIRST_NAME FIRST_NAME DEPARTMENT_ID DEPARTMENT_ID
Steven Alexander 90 60
Steven Bruce 90 60
Steven David 90 60
Steven Valli 90 60
Steven Diana 90 60
Steven Nancy 90 100
Steven Daniel 90 100
Steven John 90 100
Steven Ismael 90 100
Steven Jose Manuel 90 100

4.SELECT FIRST_NAME FROM EMPLOYEES WHERE
HIRE_DATE>(SELECT HIRE_DATE FROM EMPLOYEES WHERE FIRST_NAME='LEX')

6.SELECT COUNT(FIRST_NAME),DEPARTMENT_NAME FROM EMPLOYEES E,DEPARTMENTS D
WHERE E.DEPARTMENT_ID=D.DEPARTMENT_ID;

7.SELECT D.DEPARTMENT_NAME,E.FIRST_NAME AS "EMPLOYEE_NAME" FROM
DEPARTMENTS D,EMPLOYEES E
WHERE D.DEPARTMENT_ID=E.DEPARTMENT_ID;
OUTPUT:
DEPARTMENT_NAME EMPLOYEE_NAME
Executive Steven
Executive Neena
Executive Lex
IT Alexander
IT Bruce
IT David
IT Valli
IT Diana
Finance Nancy
Finance Daniel


8.SELECT D.DEPARTMENT_NAME,E.MANAGER_NAME FROM
DEPARTMENTS D,EMPLOYEES E WHERE D.DEPARTMENT_ID=E.DEPARTMENT_ID;

9.SELECT FIRST_NAME,JOB_ID
FROM EMPLOYEES WHERE SALARY>(SELECT MAX(MAX_SALARY) FROM JOBS)
10.SELECT FIRST_NAME,JOB_ID,DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID=(SELECT DEPARTMENT_ID FROM DEPARTMENTS WHERE DEPARTMENT_NAME='ADMINISTRATION')
11. SELECT NAME,JOB FROM EMPLOYEES E,DEPARTMENT D WHERE E.EMPLOYEE_ID=7698
AND D.DEPARTMENT_NAME=’MANAGER’
12. SELECT DEPARTMENT_NAME FROM DEPARTMENTS WHERE DEAPARTMENT_ID = (SELECT DEPARTMENT_ID FROM EMPLOYEES);
14,SELECT FIRST_NAME FROM EMPLOYEES WHERE DEPARTMENT_ID IN (SELECT MAX(DEPARTMENT_ID) DEPARTMENT_ID FROM DEPARTMENTS)

15.SELECT DEPARTMENT_NAME FROM DEPARTMENTS WHERE NOT EXISTS(SELECT DEPARTMENT_ID FROM EMPLOYEES)

16. SELECT * FROM EMPLOYEES WHERE SALARY>=ANY(SELECT SALARY FROM EMPLOYEES)
OUTPUT:
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
100 Steven King SKING 515.123.4567 17-JUN-87 AD_PRES 25000 - - 90
102 Lex De Haan LDEHAAN 515.123.4569 13-JAN-93 AD_VP 19000 - 100 90
101 Neena Kochhar NKOCHHAR 515.123.4568 21-SEP-89 AD_VP 18000 - 100 90
145 John Russell JRUSSEL 011.44.1344.429268 01-OCT-96 SA_MAN 15000 .4 100 80
146 Karen Partners KPARTNER 011.44.1344.467268 05-JAN-97 SA_MAN 14500 .3 100 80
201 Michael Hartstein MHARTSTE 515.123.5555 17-FEB-96 MK_MAN 14000 - 100 20
108 Nancy Greenberg NGREENBE 515.124.4569 17-AUG-94 FI_MGR 13000 - 101 100
147 Alberto Errazuriz AERRAZUR 011.44.1344.429278 10-MAR-97 SA_MAN 13000 .3 100 80
205 Shelley Higgins SHIGGINS 515.123.8080 07-JUN-94 AC_MGR 13000 - 101 110
168 Lisa Ozer LOZER 011.44.1343.929268 11-MAR-97 SA_REP 12500 .25 148 80