Case1.sql

Q1.WAQTD NAME OF THE EMPLOYEES EARNING MORE THAN
ADAMS.

1. SELECT *
 FROM EMP
 WHERE ENAME=(SELECT SAL
FROM EMP
 WHERE ENAME='ADAMS');

Q2.WAQTD NAME AND SALARY OF THE EMPLOYEES EARNING
LESS THAN KING.

2. SELECT ENAME,SAL
 FROM EMP
 WHERE SAL<(SELECT SAL
 FROM EMP
 WHERE ENAME='KING');

Q3.WAQTD NAME AND DEPTNO OF THE EMPLOYEES IF THEY ARE
WORKING IN THE SAME DEPT AS JONES.

3.SELECT ENAME,DEPTNO
 FROM EMP
 WHERE DEPTNO=(SELECT DEPTNO
 FROM EMP
 WHERE ENAME='JONES');

Q4.WAQTD NAME AND JOB OF ALL THE EMPLOYEES WORKING
IN THE SAME DESIGNATION AS JAMES.

4.SELECT ENAME,JOB
FROM EMP
WHERE JOB=(SELECT JOB
FROM EMP
WHERE ENAME='JAMES');

Q5.WAQTD EMPNO AND ENAME ALONG WITH ANNUAL SALARY
OF ALL THEEMPLOYEES IF THEIR ANNUAL SALARY IS
GREATER THAN WARDS ANNUAL SALARY.

5. SELECT EMPNO,ENAME,(SAL*12)
FROM EMP
WHERE SAL>(SELECT (SAL*12)
FROM EMP
WHERE ENAME='WARD');

Q6.WAQTD NAME AND HIREDATE OF THE EMPLOYEES IF THEY
ARE HIRED BEFORE SCOTT.

6.SELECT ENAME,HIREDATE
  FROM EMP
  WHERE HIREDATE<(SELECT HIREDATE
 FROM EMP
 WHERE ENAME='SCOTT');


Q7.WAQTD NAME AND HIREDATE OF THE EMPLOYEES IF THEY
ARE HIRED AFTER THE PRESIDENT.

7.SELECT ENAME,HIREDATE
FROM EMP
WHERE HIREDATE>(SELECT HIREDATE
FROM EMP
WHERE JOB='PRESIDENT');

Q8.WAQTD NAME AND SAL OF THE EMPLOYEE IF THEY ARE
EARNING SAL LESS THAN THE EMPLOYEE WHOS EMPNO IS 7839.

8. SELECT ENAME,SAL
FROM EMP
WHERE SAL<(SELECT SAL
FROM EMP
WHERE EMPNO='7839');

Q9.WAQTD ALL THE DETAILS OF THE EMPLOYEES IF THE
EMPLOYEES ARE HIRED BEFORE MILLER.

9.  SELECT *
 FROM EMP
 WHERE HIREDATE<(SELECT HIREDATE
 FROM EMP
 WHERE ENAME='MILLER');

Q10.WAQTD ENAME AND EMPNO OF THE EMPLOYEES IF
EMPLOYEES ARE EARNING MORE THAN ALLEN.

10.SELECT ENAME,EMPNO
 FROM EMP
 WHERE SAL>(SELECT SAL
 FROM EMP
 WHERE ENAME='ALLEN');

Q11.WAQTD ENAME AND SALARY OF ALL THE EMPLOYEES WHO
ARE EARNING MORE THAN MILLER BUT LESS THAN ALLEN .

11.SELECT ENAME,SAL
FROM EMP
WHERE SAL>(SELECT SAL
 FROM EMP
 WHERE ENAME='MILLER') AND SAL<(SELECT SAL
 FROM EMP
 WHERE ENAME='ALLEN');

Q12.WAQTD ALL THE DETAILS OF THE EMPLOYEES WORKING IN
DEPT 20 AND WORKING IN THE SAME DESIGNATION AS SMITH.

12.SELECT *
FROM EMP
WHERE DEPTNO=20 AND JOB=(SELECT JOB
FROM EMP
WHERE ENAME='SMITH');

Q13.WAQTD ALL THE DETAILS OF THE EMPLOYEES WORKING AS
MANAGER IN THE SAME DEPT AS TURNER.

13. SELECT *
 FROM EMP
 WHERE JOB='MANAGER' AND DEPTNO= (SELECT DEPTNO
  FROM EMP
  WHERE ENAME='TURNER');

Q14.WAQTD NAME AND HIREDATE OF THE EMPLOYEES HIRED
AFTER 1980 AND BEFORE KING.

14. SELECT ENAME,HIREDATE
FROM EMP
WHERE HIREDATE>(SELECT HIREDATE
FROM EMP
WHERE ENAME='KING');

Q15.WAQTD NAME AND SAL ALONG WITH ANNUAL SAL FOR ALL
EMPLOYEES WHOS SAL IS LESS THAN BLAKE AND MORE THAN
3500.

15.SELECT ENAME,SAL,(SAL*12)
   FROM EMP 
  WHERE SAL>3500 AND SAL<(SELECT SAL
  FROM EMP
 WHERE ENAME='BLAKE');

Q16.WAQTD ALL THE DETAILS OF EMPLOYEES WHO EARN MORE
THAN SCOTT BUT LESS THAN KING. 

16.SELECT *
FROM EMP
WHERE SAL> (SELECT SAL
FROM EMP
WHERE ENAME='SCOTT') AND SAL<(SELECT SAL
FROM EMP
WHERE ENAME='KING');

Q17.WAQTD NAME OF THE EMPLOYEES WHOS NAME STARTS
WITH 'A' AND WORKS IN THE SAME DEPT AS BLAKE.

17. SELECT ENAME
FROM EMP
WHERE ENAME LIKE 'A%' AND DEPTNO=(SELECT DEPTNO
FROM EMP
WHERE ENAME='BLAKE');

Q18.WAQTD NAME AND COMM IF EMPLOYEES EARN COMISSION
AND WORK IN THE SAME DESIGNATION AS SMITH.

18.  SELECT ENAME,COMM
 FROM EMP
 WHERE COMM IS NOT NULL AND JOB=(SELECT JOB
 FROM  EMP
 WHERE ENAME='SMITH');

Q19.WAQTD DETAILS OF ALL THE EMPLOYEES WORKING AS
CLERK IN THE SAME DEPT AS TURNER .

19. SELECT *
FROM EMP
WHERE JOB='CLERK' AND DEPTNO=(SELECT DEPTNO
FROM EMP
WHERE ENAME='TURNER');


Q20.WAQTD ENAME, SAL AND DESIGNATION OF THE EMPLOYEES
WHOS ANNUAL SALARY IS MORE THAN SMITH AND LESS THAN
KING.

20. SELECT ENAME,SAL,JOB
FROM EMP
WHERE SAL*12>(SELECT SAL*12
FROM EMP
WHERE ENAME='SMITH') AND SAL<(SELECT SAL*12
FROM EMP
WHERE ENAME='KING');