QUESTIONS-ANS ON JOINS
1.WAQ TO DISPLAY EMPLOYEE’S DETAILS FROM EMP TABLE WHO ARE WORKING FOR SAME MANAGER. BY USING JOIN AS WELL AS SUB QUERY
ANS:> SELECT * FROM EMP WHERE MGR IN(SELECT MGR FROM EMP WHERE MGR IS NOT NULL GROUP BY MGR HAVING COUNT(MGR)>1);
>SELECT DISTINCT B.ENAME,B.MGR FROM EMP A,EMP B WHERE A.MGR=B.MGR AND A.EMPNO<>B.EMPNO ORDER BY MGR;
2. LIST ENAME, JOB, ANNUAL SAL, DEPTNO, DNAME WHO EARN 30000 PER YEAR AND WHO ARE NOT CLERKS
>Select ENAME,JOB,SAL*12 ANNUAL_SAL,EMP.DEPTNO,DNAME FROM EMP,DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO AND SAL*12>30000 AND
JOB<>'CLERK';
3. LIST OUT THE ALL EMPLOYEES BY NAME AND EMPLOYEE NUMBER ALONG WITH THEIR MANAGER'S NAME AND EMPLOYEE NUMBER.
SELECT A.ENAME EMPLOYEE,A.EMPNO EMPID ,B.ENAME MANAGER,B.EMPNO MGRID FROM EMP A,EMP B WHERE A.MGR=B.EMPNO(+)
4. DISPLAY ENAME,DNAME EVEN IF THERE NO EMPLOYEES WORKING IN A PARTICULAR DEPARTMENT.
SELECT ENAME,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO(+)
=DEPT.DEPTNO
5. DISPLAY EMPLOYEE NAME AND DEPARTMENT NAME FOR EACH EMPLOYEE
Select ename,dname from emp,dept where emp.deptno=dept.deptno
6. DISPLAY LOCATION NAME OF THE EMPLOYEE WHO EARN COMMISSION.
Select loc,ename from dept,emp where emp.deptno=dept.deptno and comm Is not null
7. DISPLAY DEPT NAME OF THE EMPLOYEE WHO EARN MIN SALARY AND HAVE NO REPORTING MANAGER.
Select dname from dept,emp where emp.deptno=dept.deptno and sal=(select min(sal) from emp) and mgr is null
8. DISPLAY ALL THE EMPLOYEES OF DEPT 30,10 WITH THEIR ANNUAL SALARY AND HAVING AT LEAST 2 EMPLOYEES.
>Select ename, sal*12 Annual_sal from emp where deptno in(select deptno from emp where deptno in(10,30) group by deptno having count(*)>=2)
9. LIST ALL THE EMPLOYEES WHOSE JOB IS SAME AS SCOTT AND THEIR SALARY GREATER THAN JONES.
>select * from emp where job=(select job from emp where ename=’SCOTT’) and sal>(select sal from emp where ename=’JONES’) and empno not in(select empno from emp where ename in(‘SCOTT’,’JONES’))
10. DISPLAY DEPT NAME,LOC OF ALL THE EMPLOYEES WHO ARE REPORTING TO SMITH.
SELECT DNAME,LOC FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND MGR=(SELECT EMPNO FROM EMP WHERE ENAME=’SMITH’)
11. LIST ALL THE DEPT NAME AND LOCATION OF ALL THE SALESMAN,MANAGER'S MANAGER.
SELECT DNAME,LOC FROM DEPT,EMP WHERE DEPT.DEPTNO=EMP.DEPTNO AND JOB IN(‘SALESMAN’,’MANAGER’) AND EMPNO IN(SELECT MGR FROM EMP WHERE JOB=’MANAGER’)
12. LIST EMPLOYEES WHO ARE WORKING IN RESEARCH DEPT AND THEY ARE MANAGER.
SELECT ENAME FROM EMP,DEPT WHERE DEPT.DEPTNO=EMP.DEPTNO AND DNAME=’RESEARCH’ AND JOB=’MANAGER’
13. DISPLAY THE NUMBER OF EMPLOYEES WHO ARE GETTING SALARY LESS THAN THE BLAKE'S MANAGER.
SELECT COUNT(*) FROM EMP WHERE SAL<(SELECT SAL FROM EMP WHERE EMPNO=(SELECT MGR FROM EMP WHERE ENAME=’BLAKE’))
14. LIST THE EMPLOYEE DEPTNAME AND LOCATION OF ALL THE EMPLOYEES WHO ARE ANALYST, REPORTING TO BLAKE.
SELECT ENAME,DNAME,LOC FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND JOB =’ANALYST’ AND MGR=(SELECT EMPNO FROM EMP WHERE ENAME=’BLAKE’)
15. DISPLAY THE EMPLOYEE NAMES,HIREDATE,COMM OF FORD'S MANAGER.
SELECT ENAME,HIREDATE,COMM FROM EMP WHERE EMPNO=(SELECT MGR FROM EMP WHERE ENAME=’FORD’)
16. DISPLAY ENAME, DNAME OF ALL THE EMPLOYEES WHOSE SALARY LESS THAN AVG SAL OF DEPT 30
>SELECT ENAME,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND SAL<(SELECT AVG(SAL) FROM EMP WHERE DEPTNO=30 GROUP BY DEPTNO)
17. DISPLAY ENAME DNAME AND LOC OF ALL THE EMPLOYEES WHO ARE WORKING FOR JONES
SELECT ENAME,DNAME,LOC FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND MGR=(SELECT EMPNO FROM EMP WHERE ENAME=’JONES’)
18. DISPLAY ENAME DNAME OF ALL THE EMPLOYEES WHOSE NAME STARTS WITH S
SELECT ENAME,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND ENAME LIKE ‘S%’;
SELECT ENAME,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND SUBSTR(ENAME,1,1)=’S’;
SELECT ENAME,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND INSTR(ENAME,’S’,1,1)=1;
19. LIST THE DNAME WHO ARE NOT HAVING ANY EMPLOYEE IN IT
SELECT DNAME FROM DEPT WHERE DEPTNO NOT IN (SELECT DISTINCT DEPTNO FROM EMP)
20. DISPLAY EMPLOYEE WHO ARE GETTING SAME COMMISSION
SELECT A.ENAME FROM EMP A ,EMP B
WHERE A.COMM=B.COMM AND A.EMPNO<>B.EMPNO AND
B.COMM IS NOT NULL AND A.COMM IS NOT NULL;
21. DISPLAY ALL COUNTRIES ALONG WITH THE REGIONS .
SELECT COUNTRY_NAME,REGION_NAME FROM COUNTRIES,REGIONS WHERE COUNTRIES.REGION_ID=REGIONS.REGION_ID;
22. DISPLAY ALL CITIES AND THE COUNTRY IT BELONGS.
SELECT CITY,COUNTRY_NAME FROM COUNTRIES,REGIONS,LOCATIONS WHERE COUNTRIES.REGION_ID=REGIONS.REGION_ID AND COUNTRIES.COUNTRY_ID=LOCATIONS.COUNTRY_ID
23. DISPLAY ALL REGIONS AND THEIR RESPECTIVE COUNTRIES AND CITIES.
SELECT CITY,REGION_NAME,COUNTRY_NAME FROM COUNTRIES,REGIONS,LOCATIONS WHERE COUNTRIES.REGION_ID=REGIONS.REGION_ID AND COUNTRIES.COUNTRY_ID=LOCATIONS.COUNTRY_ID
24. DISPLAY ALL REGIONS, COUNTRIES AND CITIES WHICH ARE NOT BELONGS TO EUROPE.
SELECT CITY,REGION_NAME,COUNTRY_NAME FROM COUNTRIES,REGIONS,LOCATIONS WHERE COUNTRIES.REGION_ID=REGIONS.REGION_ID AND COUNTRIES.COUNTRY_ID=LOCATIONS.COUNTRY_ID AND REGION_NAME<>’EUROPE’
25. DISPLAY THE REGION NAME OF INDIA.
SELECT REGION_NAME FROM REGIONS,COUNTRIES WHERE Countries.REGION_ID=REGIONS.REGION_ID AND LOWER(COUNTRY_NAME)=LOWER(’INDIA’)
26. DISPLAY ALL THE COUNTRIES ALONG WITH THEIR CITIES AND ALSO THE COUNTRIES DOESN'T HAVE CITIES.
SELECT COUNTRY_NAME, CITY FROM COUNTRIES,LOCATIONS WHERE COUNTRIES.COUNTRY_ID=LOCATIONS.COUNTRY_ID(+)
27. DISPLAY ALL EMPLOYEES FIRST NAME AND LAST NAME AS 'EMPLOYEE NAME ' WITH THEIR MANAGERS FIRST NAME AND LAST NAME AS 'MANAGER NAME ' AND ALSO THE EMPLOYEES WHO DOESN'T HAVE MANAGER.
>SELECT A.FIRST_NAME||' '||A.LAST_NAME EMP,B.FIRST_NAME||' '||B.LAST_NAME MGR FROM EMPLOYEES A,EMPLOYEES B WHERE A.MANAGER_ID=B.EMPLOYEE_ID(+)
28. DISPLAY ALL DEPARTMENTS AND THE CITY TO WHICH IT BELONGS.
<Select DEPARTMENT_NAME,CITY FROM DEPARTMENTS JOIN LOCATIONS ON DEPARTMENTS.LOCATION_ID=LOCATIONS.LOCATION_ID
29. DISPLAY CITIES DOESN'T HAVE ANY DEPARTMENTS.
Select DEPARTMENT_NAME,CITY FROM DEPARTMENTS JOIN LOCATIONS ON DEPARTMENTS.LOCATION_ID(+)=LOCATIONS.LOCATION_ID AND DEPARTMENT_NAME IS NULL
30. DISPLAY ALL THE COUNTRIES OF AMERICA REGION
SELECT COUNTRY_NAME FROM COUNTRIES WHERE REGION_ID=(SELECT REGION_ID FROM REGIONS WHERE LOWER(REGION_NAME)=LOWER('AMERICAS'));
> SELECT COUNTRY_NAME FROM COUNTRIES,REGIONS WHERE LOWER(REGION_NAME)=LOWER('AMERICAS')
31. DISPLAY ALL THE DEPT NAMES IRRESPECTIVE OF ANY EMPLOYEE WORKING IN IT OR NOT. IF AN EMPLOYEE IS WORKING DISPLAY HIS NAME.
Select DEPARTMENT_NAME FROM DEPARTMENTS.
32. WRITE A QUERY TO DISPLAY EMPLOYEE NAME, JOB, DNAME, LOCATION OF ALL EMPLOYEES WHO ARE WORKING AS ACTUAL MANAGERS AND WORKS AT CHICAGO.
SELECT ENAME,JOB,DNAME,LOC FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND EMPNO IN(SELECT MGR FROM EMP) AND LOC=’CHICAGO’
33. LIST THE DEPARTMENT NAMES IN WHICH THE EMPLOYEES ARE HIRED BETWEEN 1ST OF JAN 1981 AND 31ST DEC 1982 WITH SALARY MORE THAN 1800.
SELECT DNAME,HIREDATE FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND HIREDATE BETWEEN ’01-JAN-1981’ AND ’31-DEC-1982’ AND SAL>1800;
34. DISPLAY 2ND LEAST SALARY FROM EMPLOYEE TABLE.
<SELECT MIN(SAL) FROM EMP WHERE SAL>(SELECT MIN(SAL) FROM EMP);
<SELECT DISTINCT A.SAL FROM EMP A, EMP B WHERE A.EMPNO<>B.EMPNO AND 1=(SELECT COUNT(DISTINCT(B.SAL)) FROM EMP B WHERE A.SAL>B.SAL)
35. LIST THE EMPLOYEES WHOSE ANNUAL SALARY IS GREATER THAN 1500 AND WHO ARE JOINED BEFORE 1982 ONLY.
SELECT ENAME,HIREDATE FROM EMP WHERE SAL*12>1500 AND HIREDATE<’01-JAN-1981’;
36. DISPLAY DNAME, LOC, DEPTNO OF EMPLOYEES WHO HAS SAME REPORTING MANAGER.
SELECT DNAME,LOC,DEPTNO FROM DEPT WHERE DEPTNO IN(SELECT A.DEPTNO FROM EMP A,EMP B WHERE A.MGR=B.MGR AND A.EMPNO<>B.EMPNO)
< SELECT DISTINCT DNAME,LOC,B.DEPTNO FROM DEPT,EMP A,EMP B WHERE DEPT.DEPTNO=A.DEPTNO AND A.MGR=B.MGR AND A.EMPNO<>B.EMPNO;
37. DISPLAY EMPLOYEE NAME ALONG WITH THEIR MANAGER NAME.
SELECT A.ENAME EMPLOYEE,B.ENAME MANAGER FROM EMP A,EMP B WHERE A.MGR=B.EMPNO;
38. DISPLAY EMPLOYEE NAME AND HIS DEPT NAME FOR THE EMPLOYEES WHOSE NAME STARTS WITH ‘S’.
SELECT ENAME,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND ENAME LIKE ‘S%’;
SELECT ENAME,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND SUBSTR(ENAME,1,1)=’S’;
SELECT ENAME,DNAME FROM EMP,DEPT WHERE EMP.EMPNO=DEPT.DEPTNO AND INSTR(ENAME,’S’,1,1)=1;
39. DISPLAY EMPLOYEES WHO ARE GETTING SAME SALARY.
SELECT A.ENAME FROM EMP A,EMP B WHERE A.SAL=B.SAL AND A.EMPNO<>B.EMPNO;
SELECT ENAME,SAL FROM EMP WHERE SAL IN(SELECT SAL FROM EMP GROUP BY SAL HAVING COUNT(SAL)>1)
40. DISPLAY ALL THE EMPLOYEES WHOSE JOB SAME AS 'SMITH' AND DEPARTMENT SAME AS 'JONES' AND SALARY MORE THAN 'SCOTT’
SELECT ENAME FROM EMP WHERE JOB=(SELECT JOB FROM EMP WHERE ENAME=’SMITH’) AND DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME=’JONES’)
SELECT ENAME,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND JOB=(SELECT JOB FROM EMP WHERE ENAME='SMITH') AND DEPT.DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME='JONES') AND ENAME<>'SMITH' AND SAL>(SELECT SAL FROM EMP WHERE ENAME=’SCOTT’)
SELECT ENAME FROM EMP WHERE JOB=(SELECT JOB FROM EMP WHERE ENAME=’SMITH’ AND SAL>(SELECT SAL FROM EMP WHERE ENAME=’SCOTT’)) AND DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME=’JONES’)
ASSIGNMENTS ON EQUI-JOINS
41. DISPLAY ALL THE MANAGERS & CLERKS WHO WORK IN ACCOUNTS AND MARKETING DEPARTMENTS.
SELECT ENAME,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND JOB IN('MANAGER','CLERK') AND DNAME IN('MARKETING','ACCOUNTING')
42. DISPLAY ALL THE SALESMEN WHO ARE NOT LOCATED AT DALLAS.
SELECT ENAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND JOB=’SALESMAN’ AND DNAME<>’DALLAS’
43. SELECT DEPARTMENT NAME & LOCATION OF ALL THE EMPLOYEES WORKING FOR CLARK.
SELECT DNAME,LOC FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND MGR=(SELECT EMPNO FROM EMP WHERE ENAME=’CLARK’)
44. SELECT ALL THE DEPARTMENTAL INFORMATION FOR ALL THE MANAGERS.
SELECT DEPT.* FROM DEPT,EMP WHERE EMP.DEPTNO=DEPT.DEPTNO AND JOB=’MANAGER’ ;
45. SELECT ALL THE EMPLOYEES WHO WORK IN DALLAS.
SELECT EMP.* ,LOC FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND LOC=’DALLAS’
ASSIGNMENTS ON OUTER-JOINS
46. DISPLAY ALL THE DEPARTMENTAL INFORMATION FOR ALL THE EXISTING EMPLOYEES AND IF A DEPARTMENT HAS NO EMPLOYEES DISPLAY IT AS “NO EMPLOYEES”.
ANS: SELECT NVL2(ENAME,DNAME,'NO EMPLOYEE'),DNAME
FROM EMP,DEPT
WHERE EMP.DEPTNO(+)=DEPT.DEPTNO
47. GET ALL THE MATCHING & NON-MATCHING RECORDS FROM BOTH THE TABLES.
SELECT EMP.*,DEPT.* FROM EMP FULL OUTER JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;
SELECT EMP.*,DEPT.* FROM EMP,DEPT WHERE EMP.DEPTNO(+)=DEPT.DEPTNO
UNION
SELECT EMP.*,DEPT.* FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO(+) ;
48. GET ONLY THE NON-MATCHING RECORDS FROM DEPT TABLE (MATCHING RECORDS SHOULDN’T BE SELECTED).
SELECT EMP.*,DEPT.* FROM EMP full outer join DEPT on EMP.DEPTNO=DEPT.DEPTNO
MINUS
(SELECT EMP.*,DEPT.* FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO(+) INTERSECT SELECT EMP.*,DEPT.* FROM EMP,DEPT WHERE EMP.DEPTNO(+)=DEPT.DEPTNO)
ASSIGNMENTS ON SELF-JOINS
49. GET ALL THE EMPLOYEES WHO WORK IN THE SAME DEPARTMENTS AS OF SCOTT.
SELECT DISTINCT A.ENAME FROM EMP A, EMP B WHERE A.DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME='SCOTT') AND A.ENAME<>'SCOTT'
50. DISPLAY ALL THE EMPLOYEES WHO HAVE JOINED BEFORE THEIR MANAGERS.
SELECT A.ENAME EMP ,A.HIREDATE EMP_HIREDATE,B.HIREDATE MGR_HIREDATE FROM EMP A,EMP B WHERE A.MGR=B.EMPNO AND A.HIREDATE<B.HIREDATE;
51. LIST ALL THE EMPLOYEES WHO ARE EARNING MORE THAN THEIR MANAGERS.
SELECT A.ENAME EMPLOYEE,A.SAL EMP_SAL, B.ENAME MANAGER,B.SAL MGR_SAL FROM EMP A,EMP B WHERE A.MGR=B.EMPNO AND A.SAL>B.SAL
52. FETCH ALL THE EMPLOYEES WHO ARE EARNING SAME SALARIES.
SELECT A.ENAME,A.SAL FROM EMP A,EMP B WHERE A.SAL=B.SAL AND A.EMPNO<>B.EMPNO
53. DISPLAY EMPLOYEE NAME , HIS DATE OF JOINING, HIS MANAGER NAME & HIS MANAGER'S DATE OF JOINING.
SELECT A.ENAME EMPLOYEE,A.HIREDATE EMP_HIREDATE,B.ENAME MGR,B.HIREDATE MGR_HIREDATE FROM EMP A,EMP B WHERE A.MGR=B.EMPNO;
1.WAQ TO DISPLAY EMPLOYEE’S DETAILS FROM EMP TABLE WHO ARE WORKING FOR SAME MANAGER. BY USING JOIN AS WELL AS SUB QUERY
ANS:> SELECT * FROM EMP WHERE MGR IN(SELECT MGR FROM EMP WHERE MGR IS NOT NULL GROUP BY MGR HAVING COUNT(MGR)>1);
>SELECT DISTINCT B.ENAME,B.MGR FROM EMP A,EMP B WHERE A.MGR=B.MGR AND A.EMPNO<>B.EMPNO ORDER BY MGR;
2. LIST ENAME, JOB, ANNUAL SAL, DEPTNO, DNAME WHO EARN 30000 PER YEAR AND WHO ARE NOT CLERKS
>Select ENAME,JOB,SAL*12 ANNUAL_SAL,EMP.DEPTNO,DNAME FROM EMP,DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO AND SAL*12>30000 AND
JOB<>'CLERK';
3. LIST OUT THE ALL EMPLOYEES BY NAME AND EMPLOYEE NUMBER ALONG WITH THEIR MANAGER'S NAME AND EMPLOYEE NUMBER.
SELECT A.ENAME EMPLOYEE,A.EMPNO EMPID ,B.ENAME MANAGER,B.EMPNO MGRID FROM EMP A,EMP B WHERE A.MGR=B.EMPNO(+)
4. DISPLAY ENAME,DNAME EVEN IF THERE NO EMPLOYEES WORKING IN A PARTICULAR DEPARTMENT.
SELECT ENAME,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO(+)
=DEPT.DEPTNO
5. DISPLAY EMPLOYEE NAME AND DEPARTMENT NAME FOR EACH EMPLOYEE
Select ename,dname from emp,dept where emp.deptno=dept.deptno
6. DISPLAY LOCATION NAME OF THE EMPLOYEE WHO EARN COMMISSION.
Select loc,ename from dept,emp where emp.deptno=dept.deptno and comm Is not null
7. DISPLAY DEPT NAME OF THE EMPLOYEE WHO EARN MIN SALARY AND HAVE NO REPORTING MANAGER.
Select dname from dept,emp where emp.deptno=dept.deptno and sal=(select min(sal) from emp) and mgr is null
8. DISPLAY ALL THE EMPLOYEES OF DEPT 30,10 WITH THEIR ANNUAL SALARY AND HAVING AT LEAST 2 EMPLOYEES.
>Select ename, sal*12 Annual_sal from emp where deptno in(select deptno from emp where deptno in(10,30) group by deptno having count(*)>=2)
9. LIST ALL THE EMPLOYEES WHOSE JOB IS SAME AS SCOTT AND THEIR SALARY GREATER THAN JONES.
>select * from emp where job=(select job from emp where ename=’SCOTT’) and sal>(select sal from emp where ename=’JONES’) and empno not in(select empno from emp where ename in(‘SCOTT’,’JONES’))
10. DISPLAY DEPT NAME,LOC OF ALL THE EMPLOYEES WHO ARE REPORTING TO SMITH.
SELECT DNAME,LOC FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND MGR=(SELECT EMPNO FROM EMP WHERE ENAME=’SMITH’)
11. LIST ALL THE DEPT NAME AND LOCATION OF ALL THE SALESMAN,MANAGER'S MANAGER.
SELECT DNAME,LOC FROM DEPT,EMP WHERE DEPT.DEPTNO=EMP.DEPTNO AND JOB IN(‘SALESMAN’,’MANAGER’) AND EMPNO IN(SELECT MGR FROM EMP WHERE JOB=’MANAGER’)
12. LIST EMPLOYEES WHO ARE WORKING IN RESEARCH DEPT AND THEY ARE MANAGER.
SELECT ENAME FROM EMP,DEPT WHERE DEPT.DEPTNO=EMP.DEPTNO AND DNAME=’RESEARCH’ AND JOB=’MANAGER’
13. DISPLAY THE NUMBER OF EMPLOYEES WHO ARE GETTING SALARY LESS THAN THE BLAKE'S MANAGER.
SELECT COUNT(*) FROM EMP WHERE SAL<(SELECT SAL FROM EMP WHERE EMPNO=(SELECT MGR FROM EMP WHERE ENAME=’BLAKE’))
14. LIST THE EMPLOYEE DEPTNAME AND LOCATION OF ALL THE EMPLOYEES WHO ARE ANALYST, REPORTING TO BLAKE.
SELECT ENAME,DNAME,LOC FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND JOB =’ANALYST’ AND MGR=(SELECT EMPNO FROM EMP WHERE ENAME=’BLAKE’)
15. DISPLAY THE EMPLOYEE NAMES,HIREDATE,COMM OF FORD'S MANAGER.
SELECT ENAME,HIREDATE,COMM FROM EMP WHERE EMPNO=(SELECT MGR FROM EMP WHERE ENAME=’FORD’)
16. DISPLAY ENAME, DNAME OF ALL THE EMPLOYEES WHOSE SALARY LESS THAN AVG SAL OF DEPT 30
>SELECT ENAME,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND SAL<(SELECT AVG(SAL) FROM EMP WHERE DEPTNO=30 GROUP BY DEPTNO)
17. DISPLAY ENAME DNAME AND LOC OF ALL THE EMPLOYEES WHO ARE WORKING FOR JONES
SELECT ENAME,DNAME,LOC FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND MGR=(SELECT EMPNO FROM EMP WHERE ENAME=’JONES’)
18. DISPLAY ENAME DNAME OF ALL THE EMPLOYEES WHOSE NAME STARTS WITH S
SELECT ENAME,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND ENAME LIKE ‘S%’;
SELECT ENAME,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND SUBSTR(ENAME,1,1)=’S’;
SELECT ENAME,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND INSTR(ENAME,’S’,1,1)=1;
19. LIST THE DNAME WHO ARE NOT HAVING ANY EMPLOYEE IN IT
SELECT DNAME FROM DEPT WHERE DEPTNO NOT IN (SELECT DISTINCT DEPTNO FROM EMP)
20. DISPLAY EMPLOYEE WHO ARE GETTING SAME COMMISSION
SELECT A.ENAME FROM EMP A ,EMP B
WHERE A.COMM=B.COMM AND A.EMPNO<>B.EMPNO AND
B.COMM IS NOT NULL AND A.COMM IS NOT NULL;
21. DISPLAY ALL COUNTRIES ALONG WITH THE REGIONS .
SELECT COUNTRY_NAME,REGION_NAME FROM COUNTRIES,REGIONS WHERE COUNTRIES.REGION_ID=REGIONS.REGION_ID;
22. DISPLAY ALL CITIES AND THE COUNTRY IT BELONGS.
SELECT CITY,COUNTRY_NAME FROM COUNTRIES,REGIONS,LOCATIONS WHERE COUNTRIES.REGION_ID=REGIONS.REGION_ID AND COUNTRIES.COUNTRY_ID=LOCATIONS.COUNTRY_ID
23. DISPLAY ALL REGIONS AND THEIR RESPECTIVE COUNTRIES AND CITIES.
SELECT CITY,REGION_NAME,COUNTRY_NAME FROM COUNTRIES,REGIONS,LOCATIONS WHERE COUNTRIES.REGION_ID=REGIONS.REGION_ID AND COUNTRIES.COUNTRY_ID=LOCATIONS.COUNTRY_ID
24. DISPLAY ALL REGIONS, COUNTRIES AND CITIES WHICH ARE NOT BELONGS TO EUROPE.
SELECT CITY,REGION_NAME,COUNTRY_NAME FROM COUNTRIES,REGIONS,LOCATIONS WHERE COUNTRIES.REGION_ID=REGIONS.REGION_ID AND COUNTRIES.COUNTRY_ID=LOCATIONS.COUNTRY_ID AND REGION_NAME<>’EUROPE’
25. DISPLAY THE REGION NAME OF INDIA.
SELECT REGION_NAME FROM REGIONS,COUNTRIES WHERE Countries.REGION_ID=REGIONS.REGION_ID AND LOWER(COUNTRY_NAME)=LOWER(’INDIA’)
26. DISPLAY ALL THE COUNTRIES ALONG WITH THEIR CITIES AND ALSO THE COUNTRIES DOESN'T HAVE CITIES.
SELECT COUNTRY_NAME, CITY FROM COUNTRIES,LOCATIONS WHERE COUNTRIES.COUNTRY_ID=LOCATIONS.COUNTRY_ID(+)
27. DISPLAY ALL EMPLOYEES FIRST NAME AND LAST NAME AS 'EMPLOYEE NAME ' WITH THEIR MANAGERS FIRST NAME AND LAST NAME AS 'MANAGER NAME ' AND ALSO THE EMPLOYEES WHO DOESN'T HAVE MANAGER.
>SELECT A.FIRST_NAME||' '||A.LAST_NAME EMP,B.FIRST_NAME||' '||B.LAST_NAME MGR FROM EMPLOYEES A,EMPLOYEES B WHERE A.MANAGER_ID=B.EMPLOYEE_ID(+)
28. DISPLAY ALL DEPARTMENTS AND THE CITY TO WHICH IT BELONGS.
<Select DEPARTMENT_NAME,CITY FROM DEPARTMENTS JOIN LOCATIONS ON DEPARTMENTS.LOCATION_ID=LOCATIONS.LOCATION_ID
29. DISPLAY CITIES DOESN'T HAVE ANY DEPARTMENTS.
Select DEPARTMENT_NAME,CITY FROM DEPARTMENTS JOIN LOCATIONS ON DEPARTMENTS.LOCATION_ID(+)=LOCATIONS.LOCATION_ID AND DEPARTMENT_NAME IS NULL
30. DISPLAY ALL THE COUNTRIES OF AMERICA REGION
SELECT COUNTRY_NAME FROM COUNTRIES WHERE REGION_ID=(SELECT REGION_ID FROM REGIONS WHERE LOWER(REGION_NAME)=LOWER('AMERICAS'));
> SELECT COUNTRY_NAME FROM COUNTRIES,REGIONS WHERE LOWER(REGION_NAME)=LOWER('AMERICAS')
31. DISPLAY ALL THE DEPT NAMES IRRESPECTIVE OF ANY EMPLOYEE WORKING IN IT OR NOT. IF AN EMPLOYEE IS WORKING DISPLAY HIS NAME.
Select DEPARTMENT_NAME FROM DEPARTMENTS.
32. WRITE A QUERY TO DISPLAY EMPLOYEE NAME, JOB, DNAME, LOCATION OF ALL EMPLOYEES WHO ARE WORKING AS ACTUAL MANAGERS AND WORKS AT CHICAGO.
SELECT ENAME,JOB,DNAME,LOC FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND EMPNO IN(SELECT MGR FROM EMP) AND LOC=’CHICAGO’
33. LIST THE DEPARTMENT NAMES IN WHICH THE EMPLOYEES ARE HIRED BETWEEN 1ST OF JAN 1981 AND 31ST DEC 1982 WITH SALARY MORE THAN 1800.
SELECT DNAME,HIREDATE FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND HIREDATE BETWEEN ’01-JAN-1981’ AND ’31-DEC-1982’ AND SAL>1800;
34. DISPLAY 2ND LEAST SALARY FROM EMPLOYEE TABLE.
<SELECT MIN(SAL) FROM EMP WHERE SAL>(SELECT MIN(SAL) FROM EMP);
<SELECT DISTINCT A.SAL FROM EMP A, EMP B WHERE A.EMPNO<>B.EMPNO AND 1=(SELECT COUNT(DISTINCT(B.SAL)) FROM EMP B WHERE A.SAL>B.SAL)
35. LIST THE EMPLOYEES WHOSE ANNUAL SALARY IS GREATER THAN 1500 AND WHO ARE JOINED BEFORE 1982 ONLY.
SELECT ENAME,HIREDATE FROM EMP WHERE SAL*12>1500 AND HIREDATE<’01-JAN-1981’;
36. DISPLAY DNAME, LOC, DEPTNO OF EMPLOYEES WHO HAS SAME REPORTING MANAGER.
SELECT DNAME,LOC,DEPTNO FROM DEPT WHERE DEPTNO IN(SELECT A.DEPTNO FROM EMP A,EMP B WHERE A.MGR=B.MGR AND A.EMPNO<>B.EMPNO)
< SELECT DISTINCT DNAME,LOC,B.DEPTNO FROM DEPT,EMP A,EMP B WHERE DEPT.DEPTNO=A.DEPTNO AND A.MGR=B.MGR AND A.EMPNO<>B.EMPNO;
37. DISPLAY EMPLOYEE NAME ALONG WITH THEIR MANAGER NAME.
SELECT A.ENAME EMPLOYEE,B.ENAME MANAGER FROM EMP A,EMP B WHERE A.MGR=B.EMPNO;
38. DISPLAY EMPLOYEE NAME AND HIS DEPT NAME FOR THE EMPLOYEES WHOSE NAME STARTS WITH ‘S’.
SELECT ENAME,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND ENAME LIKE ‘S%’;
SELECT ENAME,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND SUBSTR(ENAME,1,1)=’S’;
SELECT ENAME,DNAME FROM EMP,DEPT WHERE EMP.EMPNO=DEPT.DEPTNO AND INSTR(ENAME,’S’,1,1)=1;
39. DISPLAY EMPLOYEES WHO ARE GETTING SAME SALARY.
SELECT A.ENAME FROM EMP A,EMP B WHERE A.SAL=B.SAL AND A.EMPNO<>B.EMPNO;
SELECT ENAME,SAL FROM EMP WHERE SAL IN(SELECT SAL FROM EMP GROUP BY SAL HAVING COUNT(SAL)>1)
40. DISPLAY ALL THE EMPLOYEES WHOSE JOB SAME AS 'SMITH' AND DEPARTMENT SAME AS 'JONES' AND SALARY MORE THAN 'SCOTT’
SELECT ENAME FROM EMP WHERE JOB=(SELECT JOB FROM EMP WHERE ENAME=’SMITH’) AND DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME=’JONES’)
SELECT ENAME,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND JOB=(SELECT JOB FROM EMP WHERE ENAME='SMITH') AND DEPT.DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME='JONES') AND ENAME<>'SMITH' AND SAL>(SELECT SAL FROM EMP WHERE ENAME=’SCOTT’)
SELECT ENAME FROM EMP WHERE JOB=(SELECT JOB FROM EMP WHERE ENAME=’SMITH’ AND SAL>(SELECT SAL FROM EMP WHERE ENAME=’SCOTT’)) AND DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME=’JONES’)
ASSIGNMENTS ON EQUI-JOINS
41. DISPLAY ALL THE MANAGERS & CLERKS WHO WORK IN ACCOUNTS AND MARKETING DEPARTMENTS.
SELECT ENAME,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND JOB IN('MANAGER','CLERK') AND DNAME IN('MARKETING','ACCOUNTING')
42. DISPLAY ALL THE SALESMEN WHO ARE NOT LOCATED AT DALLAS.
SELECT ENAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND JOB=’SALESMAN’ AND DNAME<>’DALLAS’
43. SELECT DEPARTMENT NAME & LOCATION OF ALL THE EMPLOYEES WORKING FOR CLARK.
SELECT DNAME,LOC FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND MGR=(SELECT EMPNO FROM EMP WHERE ENAME=’CLARK’)
44. SELECT ALL THE DEPARTMENTAL INFORMATION FOR ALL THE MANAGERS.
SELECT DEPT.* FROM DEPT,EMP WHERE EMP.DEPTNO=DEPT.DEPTNO AND JOB=’MANAGER’ ;
45. SELECT ALL THE EMPLOYEES WHO WORK IN DALLAS.
SELECT EMP.* ,LOC FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND LOC=’DALLAS’
ASSIGNMENTS ON OUTER-JOINS
46. DISPLAY ALL THE DEPARTMENTAL INFORMATION FOR ALL THE EXISTING EMPLOYEES AND IF A DEPARTMENT HAS NO EMPLOYEES DISPLAY IT AS “NO EMPLOYEES”.
ANS: SELECT NVL2(ENAME,DNAME,'NO EMPLOYEE'),DNAME
FROM EMP,DEPT
WHERE EMP.DEPTNO(+)=DEPT.DEPTNO
47. GET ALL THE MATCHING & NON-MATCHING RECORDS FROM BOTH THE TABLES.
SELECT EMP.*,DEPT.* FROM EMP FULL OUTER JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;
SELECT EMP.*,DEPT.* FROM EMP,DEPT WHERE EMP.DEPTNO(+)=DEPT.DEPTNO
UNION
SELECT EMP.*,DEPT.* FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO(+) ;
48. GET ONLY THE NON-MATCHING RECORDS FROM DEPT TABLE (MATCHING RECORDS SHOULDN’T BE SELECTED).
SELECT EMP.*,DEPT.* FROM EMP full outer join DEPT on EMP.DEPTNO=DEPT.DEPTNO
MINUS
(SELECT EMP.*,DEPT.* FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO(+) INTERSECT SELECT EMP.*,DEPT.* FROM EMP,DEPT WHERE EMP.DEPTNO(+)=DEPT.DEPTNO)
ASSIGNMENTS ON SELF-JOINS
49. GET ALL THE EMPLOYEES WHO WORK IN THE SAME DEPARTMENTS AS OF SCOTT.
SELECT DISTINCT A.ENAME FROM EMP A, EMP B WHERE A.DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME='SCOTT') AND A.ENAME<>'SCOTT'
50. DISPLAY ALL THE EMPLOYEES WHO HAVE JOINED BEFORE THEIR MANAGERS.
SELECT A.ENAME EMP ,A.HIREDATE EMP_HIREDATE,B.HIREDATE MGR_HIREDATE FROM EMP A,EMP B WHERE A.MGR=B.EMPNO AND A.HIREDATE<B.HIREDATE;
51. LIST ALL THE EMPLOYEES WHO ARE EARNING MORE THAN THEIR MANAGERS.
SELECT A.ENAME EMPLOYEE,A.SAL EMP_SAL, B.ENAME MANAGER,B.SAL MGR_SAL FROM EMP A,EMP B WHERE A.MGR=B.EMPNO AND A.SAL>B.SAL
52. FETCH ALL THE EMPLOYEES WHO ARE EARNING SAME SALARIES.
SELECT A.ENAME,A.SAL FROM EMP A,EMP B WHERE A.SAL=B.SAL AND A.EMPNO<>B.EMPNO
53. DISPLAY EMPLOYEE NAME , HIS DATE OF JOINING, HIS MANAGER NAME & HIS MANAGER'S DATE OF JOINING.
SELECT A.ENAME EMPLOYEE,A.HIREDATE EMP_HIREDATE,B.ENAME MGR,B.HIREDATE MGR_HIREDATE FROM EMP A,EMP B WHERE A.MGR=B.EMPNO;
