CASE 2
[Link] DEPARTMENT NAME OF THE EMPLOYEES WHO EARN COMMISSION
SELECT DNAME
FROM DEPT
WHERE DEPTNO IN (SELECT DEPTNO
FROM EMP
WHERE COMM IS NOT NULL);
[Link] THE DEPARTMENT NUMBER WHO WORKING IN SALES DEPARTMENT AND THEY ARE
MANAGER
SELECT DEPTNO
FROM DEPT
WHERE DEPTNO IN(SELECT DEPTNO
FROM DEPT
WHERE DNAME='SALES') AND DEPTNO IN(SELECT DEPTNO
FROM EMP
WHERE JOB='MANAGER');
[Link] HIREDATE AND JOB OF ALL THE EMPLOYEES WORKING FOR SALES
SELECT HIREDATE,JOB
FROM EMP
WHERE DEPTNO IN(SELECT DEPTNO
FROM DEPT
WHERE DNAME='SALES');
[Link] LOCATION AND DNAME OF EMPLOYEE WHO WORKING AS PRESIDENT
SELECT LOC,DNAME
FROM DEPT
WHERE DEPTNO IN(SELECT DEPTNO
FROM EMP
WHERE JOB='PRESIDENT');
[Link] THE DNAME THAT ARE HAVING CLERK IN IT
SELECT DNAME
FROM DEPT
WHERE DEPTNO IN(SELECT DEPTNO
FROM EMP
WHERE JOB='CLERK');
[Link] THE EMPLOYEES WHOSE DNAME IS HAVING AT LEAST TWO 'E' IN IT.
SELECT ENAME
FROM EMP
WHERE DEPTNO IN(SELECT DEPTNO
FROM DEPT
WHERE DNAME LIKE '%E%E%');
[Link] ALL THE EMPLOYEES WHO ARE WORKING FOR CHICAGO
SELECT ENAME
FROM EMP
WHERE DEPTNO IN(SELECT DEPTNO
FROM DEPT
WHERE LOC='CHICAGO');
[Link] THE DEPARTMENT NAMES THAT ARE HAVING SALESMAN.
SELECT DNAME
FROM DEPT
WHERE DEPTNO IN(SELECT DEPTNO
FROM EMP
WHERE JOB='SALESMAN');
[Link] THE LOCATION OF ALL THE DEAPRTMENTS WHICH HAVE EMPLOYEES JOINED IN THE
YEAR 81
SELECT LOC
FROM DEPT
WHERE DEPTNO IN(SELECT DEPTNO
FROM EMP
WHERE HIREDATE LIKE '%81');
[Link] ALL THE EMPLOYEE INFORMATION WHO ARE LIVING IN A LOCATION WHICH IS
HAVING AT LEAST 2 'O' IN IT.
SELECT *
FROM EMP
WHERE DEPTNO IN(SELECT DEPTNO
FROM DEPT
WHERE LOC LIKE '%O%O%');
[Link] THE NUMBER OF EMPLOYEES WHOSE JOB IS SALESMAN WORKING FOR NEWYORK AND
CHICAGO
SELECT COUNT(*)
FROM EMP
WHERE JOB='SALESMAN' AND DEPTNO IN(SELECT DEPTNO
FROM DEPT
WHERE LOC IN ('NEW YORK' ,'CHICAGO'));
[Link] 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
FROM DEPT
WHERE DEPTNO IN(SELECT DEPTNO
FROM EMP
WHERE HIREDATE BETWEEN '01-JAN-81' AND '31-DEC-82' AND
SAL>1800);
[Link] LOCATION OF THE EMPLOYEE WHO EARN COMMISSION
SELECT LOC
FROM DEPT
WHERE DEPTNO IN(SELECT DEPTNO
FROM EMP
WHERE COMM IS NOT NULL);
[Link] EMPLOYEES LOCATION WHO HAS SOME COMMISSION
SELECT LOC
FROM DEPT
WHERE DEPTNO IN(SELECT DEPTNO
FROM EMP
WHERE COMM IS NOT NULL);
[Link] ALL THE EMPLOYEES WHOSE JOB SAME AS 'SMITH' AND DEPARTMENT SAME AS
'JONES' AND SALARY MORE THAN 'TURNER'
SELECT ENAME
FROM EMP
WHERE JOB IN(SELECT JOB
FROM EMP
WHERE ENAME='SMITH') AND DEPTNO IN(SELECT DEPTNO
FROM DEPT
WHERE DEPTNO IN(SELECT DEPTNO
FROM EMP
WHERE ENAME='JONES')) AND SAL>(SELECT SAL
FROM EMP
WHERE ENAME='TURNER');
[Link] ALL THE EMPLOYEES WORKING FOR DALLAS
SELECT ENAME
FROM EMP
WHERE DEPTNO IN(SELECT DEPTNO
FROM DEPT
WHERE LOC='DALLAS');
[Link] THE LOCATION OF AN EMPLOYEE IN ACCOUNTING DEPARTMENT.
SELECT LOC
FROM DEPT
WHERE DEPTNO IN (SELECT DEPTNO
FROM DEPT
WHERE DNAME=’ACCOUNTING’);
[Link] THE DEPARTMENT INFORMATION OF EMPLOYEE WHO IS WORKING FOR NEW YORK
LOCATION
SELECT *
FROM DEPT
WHERE DEPTNO IN (SELECT DEPTNO
FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO
FROM DEPT
WHERE LOC='NEW YORK'));
[Link] ALL THE CLERKS AND ANALYST WHO ARE NOT WORKING FOR 'DALLAS'
SELECT ENAME
FROM EMP
WHERE JOB IN ('CLERK','ANALYST') AND DEPTNO NOT IN(SELECT
DEPTNO
FROM DEPT
WHERE LOC='DALLAS');
[Link] ALL THE EMPLOYEES WHOSE DEPARTMET NAMES ENDING 'S'
SELECT ENAME
FROM EMP
WHERE DEPTNO IN(SELECT DEPTNO
FROM DEPT
WHERE DNAME LIKE '%S');
[Link] TO DISPLAY ALL THE EMPLOYEES IN 'OPERATIONS AND ACCOUNTING' DEPT.
SELECT ENAME
FROM EMP
WHERE DEPTNO IN(SELECT DEPTNO
FROM DEPT
WHERE DNAME IN('OPERATIONS','ACCOUNTING'));
[Link] EMPLOYEES WHO LOCATED IN CHICAGO AND THEIR COMMISSION IS ZERO.
SELECT ENAME
FROM EMP
WHERE DEPTNO=(SELECT DEPTNO
FROM DEPT
WHERE LOC=’CHICAGO’) AND COMM =0;
[Link] EMPLOYEES WHO ARE WORKING IN RESEARCH DEPARTMENT AND THEY ARE MANAGER.
SELECT ENAME
FROM EMP
WHERE DEPTNO=(SELECT DEPTNO
FROM DEPT
WHERE DNAME='RESEARCH') AND JOB='MANAGER';
