Tough SQL Queries - Part 1

(click for SQL jobs)

SQL-QUERIES 1. DISPLAY ALL THE INFORMATION OF THE EMP TABLE? A)
SELECT *
FROM EMP;

 2. DISPLAY UNIQUE JOBS FROM EMP TABLE? 

A)
SELECT DISTINCT JOB
FROM EMP;

 B)
SELECT UNIQUE JOB
FROM EMP;

 3. LIST THE EMPS IN THE ASC ORDER OF THEIR SALARIES? 

A)
SELECT *
FROM EMP
ORDER BY SAL ASC;

 4. LIST THE DETAILS OF THE EMPS IN ASC ORDER OF THE DPTNOS AND DESC OF JOBS? 

A)
SELECT *
FROM EMP
ORDER BY DEPTNO ASC,
         JOB DESC;

 5. DISPLAY ALL THE UNIQUE JOB GROUPS IN THE DESCENDING
ORDER? A)
SELECT DISTINCT JOB
FROM EMP
ORDER BY JOB DESC;

 6. DISPLAY ALL THE DETAILS OF ALL ‘MGRS’

 A)
SELECT *
FROM EMP
WHERE EMPNO IN
    (SELECT MGR
     FROM EMP) ;

 7. LIST THE EMPS WHO JOINED BEFORE 1981. 

A)
SELECT *
FROM EMP
WHERE HIREDATE < (’01-JAN-81’);

 8. LIST THE EMPNO,  ENAME,  SAL,  DAILY SAL OF ALL EMPS IN THE ASC ORDER OF ANNSAL. 

A)
SELECT EMPNO,
       ENAME,
       SAL,
       SAL/30,
       12*SAL ANNSAL
FROM EMP
ORDER BY ANNSAL ASC;

 9. DISPLAY THE EMPNO, ENAME, JOB, HIREDATE, EXP OF ALL MGRS 

A)
SELECT EMPNO,
       ENAME,
       JOB,
       HIREDATE,
       MONTHS_BETWEEN(SYSDATE,HIREDATE) EXP
FROM EMP
WHERE EMPNO IN
    (SELECT MGR
     FROM EMP);

 10. LIST THE EMPNO, ENAME, SAL, EXP OF ALL EMPS WORKINGFOR MGR 7369. 

A)

SELECT EMPNO,
       ENAME,
       SAL,
       EXP
FROM EMP
WHERE MGR = 7369;

 11. DISPLAY ALL THE DETAILS OF THE EMPS WHOSE COMM. IS MORE THAN THEIR SAL. 

A)
SELECT *
FROM EMP
WHERE COMM. > SAL;

 12. LIST THE EMPS IN THE ASC
ORDER OF DESIGNATIONS OF THOSE JOINED AFTER THE SECOND HALF OF 1981. 

A)
SELECT *
FROM EMP
WHERE HIREDATE > (’30-JUN-81’)
  AND TO_CHAR(HIREDATE,’YYYY’) = 1981
ORDER BY JOB ASC;

 13. LIST THE EMPS ALONG WITH THEIR EXP AND DAILY SAL IS MORE THAN RS.100. 

A)
SELECT *
FROM EMP
WHERE (SAL/30) >100;

 14. LIST THE EMPS WHO ARE EITHER ‘CLERK’OR ‘ANALYST’ IN THE DESC ORDER. 

A)
SELECT *
FROM EMP
WHERE JOB = ‘CLERK’
  OR JOB = ‘ANALYST’
ORDER BY JOB DESC;

 15. LIST THE EMPS WHO JOINED ON 1-MAY-81,3-DEC-81, 17-DEC-81,19-JAN-80 IN ASC ORDER OF SENIORITY. 

A)
SELECT *
FROM EMP
WHERE HIREDATE IN (’01-MAY-81’,’03-DEC-81’,’17-DEC-81’,’19-JAN-80’)
ORDER BY HIREDATE ASC;

 16. LIST THE EMP WHO ARE WORKING FOR THE DEPTNO 10 OR 20. 

A)
SELECT *
FROM EMP
WHERE DEPTNO = 10
  OR DEPTNO = 20 ;

 17. LIST THE EMPS WHO ARE JOINED IN THE YEAR 81. 

A)
SELECT *
FROM EMP
WHERE HIREDATE BETWEEN ’01-JAN-81’ AND ’31-DEC-81’;

 18. LIST THE EMPS WHO ARE JOINED IN THE MONTH OF AUG 1980. A)
SELECT *
FROM EMP
WHERE HIREDATE BETWEEN ’01-AUG-80’ AND ’31-AUG-80’;

(
 OR)
SELECT *
FROM EMP
WHERE TO_CHAR(HIREDATE,’MON-YYYY’) =’AUG-1980;

 19. LIST THE EMPS WHO ANNUAL SAL RANGING FROM 22000 AND 45000. 

A)
SELECT *
FROM EMP
WHERE 12*SAL BETWEEN 22000 AND 45000;

 20. LIST THE ENAMES THOSE ARE HAVING FIVE CHARACTERS IN THEIR NAMES.

A)
SELECT ENAME
FROM EMP
WHERE LENGTH (ENAME) = 5;

 21. LIST THE ENAMES THOSE ARE STARTING WITH ‘S’ AND WITH FIVE CHARACTERS. 

A)
SELECT ENAME
FROM EMP
WHERE ENAME LIKE ‘S%’
  AND LENGTH (ENAME) = 5;

 22. LIST THE EMPS THOSE ARE HAVING FOUR CHARS AND THIRD CHARACTER MUST BE ‘R’. 

A)
SELECT *
FROM EMP
WHERE LENGTH(ENAME) = 4
  AND ENAME LIKE ‘__R%’;

 23. LIST THE FIVE CHARACTER NAMES STARTING WITH ‘S’
AND ENDING WITH ‘H’. A)
SELECT *
FROM EMP
WHERE LENGTH(ENAME) = 5
  AND ENAME LIKE ‘S%H’;

 24. LIST THE EMPS WHO JOINED IN JANUARY. 

A)
SELECT *
FROM EMP
WHERE TO_CHAR (HIREDATE,’MON’) = ‘JAN’;

 25. LIST THE EMPS WHO JOINED IN THE MONTH OF WHICH SECOND CHARACTER IS ‘A’. 

A)
SELECT *
FROM EMP
WHERE TO_CHAR(HIREDATE,’MON’) LIKE ‘_A_’;

(
 OR) B)
SELECT *
FROM EMP
WHERE TO_CHAR(HIREDATE,’MON’) LIKE ‘_A%’;

 26. LIST THE EMPS WHOSE SAL IS FOUR DIGIT NUMBER ENDING WITH ZERO.

A)
SELECT *
FROM EMP
WHERE LENGTH (SAL) = 4
  AND SAL LIKE ‘%0’;

 27. LIST THE EMPS WHOSE NAMES HAVING A CHARACTER SET ‘LL’ TOGETHER. 

A)
SELECT *
FROM EMP
WHERE ENAME LIKE ‘%LL%’;

 28. LIST THE EMPS THOSE WHO JOINED IN 80’S. 

A)
SELECT *
FROM EMP
WHERE TO_CHAR(HIREDATE,’YY’) LIKE ‘8%’;

 29. LIST THE EMPS WHO DOES NOT BELONG TO DEPTNO 20. 

A)
SELECT *
FROM EMP
WHERE DEPTNO NOT IN (20);

(
 OR) B)
SELECT *
FROM EMP
WHERE DEPTNO != 20;

(
 OR) C)
SELECT *
FROM EMP
WHERE DEPTNO <>20;

(
 OR) D)
SELECT *
FROM EMP
WHERE DEPTNO NOT LIKE ‘20’;

 30. LIST ALL THE EMPS EXCEPT ‘PRESIDENT’ & ‘MGR” IN ASC ORDER OF SALARIES.

A)
SELECT *
FROM EMP
WHERE JOB NOT IN (‘PRESIDENT’,’MANAGER’)
ORDER BY SAL ASC;

 B)
SELECT *
FROM EMP
WHERE JOB NOT LIKE ‘PRESIDENT’
  AND JOB NOT LIKE ‘MANAGER’
ORDER BY SAL ASC;

 C)
SELECT *
FROM EMP
WHERE JOB != ‘PRESIDENT’
  AND JOB <> ‘MANAGER’

ORDER BY SAL ASC;