PL/SQL -> SQL -> SQL Queries

SQL Queries for Practice

Using SQL Functions

Using SELECT

  1. SELECT * FROM dept;
  2. SELECT * FROM salgrade;
  3. SELECT * FROM emp;
  4. Using SQL*Plus:

    1. SQL> COLUMN empno FORMAT 9999
    2. SQL> COLUMN deptno FORMAT 99
    3. SQL> COLUMN mgr FORMAT 9999
    4. SQL> COLUMN sal FORMAT 9999
    5. SQL> COLUMN comm FORMAT 9999
    6. SQL> COLUMN ename FORMAT A7
    7. SQL> SELECT * FROM emp;
  5. SELECT deptno, dname, loc FROM dept;
  6. SELECT deptno, dname FROM dept;
  7. SELECT ename, sal, hiredate, job FROM emp;
  8. SELECT Ename, Deptno, Sal, Sal * 12 Yearly_Sal FROM Emp;
  9. SELECT Ename, Sal, Comm, Sal + Comm Total_Sal FROM Emp;
  10. SELECT Ename, Sal, Comm, Sal + NVL(Comm, 0) Total_Sal FROM Emp;
  11. SELECT 12*5+100/10-2 FROM dual;
  12. SELECT Ename Name, Sal Salary, Comm Commission, Sal + NVL(Comm, 0) "Gross Salary" FROM Emp;
  13. SELECT Ename, ' is Working for the Department ', Deptno FROM Emp;
  14. SELECT Ename, '''s role is ', Job FROM Emp;
  15. SELECT Empno||Ename||Job||Sal||NVL(Comm,0)||MGR||HireDate||Deptno Employee FROM Emp;
  16. SELECT DISTINCT Deptno FROM Emp;
  17. SELECT DISTINCT Job FROM Emp;
  18. SELECT DISTINCT Comm FROM Emp;
  19. SELECT DISTINCT Deptno, Job, mgr FROM Emp
  20. SELECT Ename, Sal, Deptno, Job FROM Emp WHERE Job = 'MANAGER';
  21. SELECT Ename, Sal FROM Emp WHERE Sal >= 3000;
  22. SELECT Ename, Deptno, Job, HireDate FROM Emp WHERE HireDate = '03-DEC-81';
  23. SELECT DISTINCT Job FROM Emp WHERE Job <> 'CLERK';
  24. SELECT Ename, Sal, Deptno, Job FROM Emp WHERE Job <> 'CLERK';
  25. SELECT Ename, Deptno, Sal FROM Emp WHERE Sal * 12 > 35000;
  26. SELECT Ename, Deptno, Sal, Comm, Sal + NVL(Comm, 0) TotSal FROM Emp WHERE Sal + NVL(Comm, 0) > 2800;
  27. SELECT 200 a, 50 b, 250 as "a+b" FROM DUAL;
  28. SELECT 200 a, 50 b, 250 as "a+b" FROM DUAL WHERE 1 = 2;
  29. SELECT 200 a, 50 b, 250 as "a+b" FROM DUAL WHERE 'ABC' = 'abc';
  30. SELECT Ename, Sal , Job FROM Emp WHERE Job = 'MANAGER' AND Job = 'ANALYST';
  31. SELECT Ename, Sal, Job FROM Emp WHERE Sal >= 1500 AND Sal <= 2850 AND Job = 'MANAGER';
  32. SELECT Ename, Sal, Job FROM Emp WHERE (Sal >= 1500 AND Sal <= 2850) AND Job = 'MANAGER';
  33. SELECT Ename, Sal, Deptno, Job FROM Emp WHERE Sal <= 1250 OR Sal >= 3000;
  34. SELECT Ename, Deptno, Job FROM Emp WHERE Deptno = 20 OR Job = 'CLERK';
  35. SELECT Ename, Sal, Deptno, Job FROM Emp WHERE NOT Job = 'MANAGER';
  36. SELECT Ename, Deptno, Job FROM Emp WHERE Job <> 'SALESMAN' AND NOT Deptno = 30;
  37. SELECT * FROM emp WHERE NOT(Job <> 'SALESMAN' AND Deptno <> 30);
  38. SELECT Ename, Sal, Deptno, Job FROM Emp WHERE Job > 'MANAGER';
  39. SELECT Ename, Sal, Deptno, HireDate FROM Emp WHERE HireDate > '03-DEC-81';
  40. SELECT * FROM Emp WHERE Comm = NULL;
  41. SELECT Ename, Sal, Comm FROM Emp WHERE Comm <> NULL;
  42. SELECT Ename, Sal, Job, Deptno FROM Emp WHERE 'MANAGER' = Job;
  43. Using HR Schema

    			  SELECT department_id, 
    					   job_id, 
    					   SUM(salary), 
    					   AVG(salary) 
    				FROM   employees 
    			  WHERE  ( ( department_id = 20 
    						   AND ( job_id = 'MK_MAN' 
    								  OR job_id = 'MK_REP' 
    								  OR job_id = 'PU_MAN' 
    								  OR job_id = 'PU_CLERK' 
    								  OR job_id = 'HR_REP' 
    								  OR job_id = 'SH_CLERK' 
    								  OR job_id = 'IT_PROG' 
    								  OR job_id = 'SA_REP' ) 
    						   AND ( salary >= 5000 
    								 AND salary <= 30000 ) ) 
    					  OR ( department_id = 30 
    						   AND ( job_id = 'MK_MAN' 
    								  OR job_id = 'MK_REP' 
    								  OR job_id = 'PU_MAN' 
    								  OR job_id = 'PU_CLERK' 
    								  OR job_id = 'HR_REP' 
    								  OR job_id = 'SH_CLERK' 
    								  OR job_id = 'IT_PROG' 
    								  OR job_id = 'SA_REP' ) 
    						   AND ( salary >= 5000 
    								 AND salary <= 30000 ) ) 
    					  OR ( department_id = 40 
    						   AND ( job_id = 'MK_MAN' 
    								  OR job_id = 'MK_REP' 
    								  OR job_id = 'PU_MAN' 
    								  OR job_id = 'PU_CLERK' 
    								  OR job_id = 'HR_REP' 
    								  OR job_id = 'SH_CLERK' 
    								  OR job_id = 'IT_PROG' 
    								  OR job_id = 'SA_REP' ) 
    						   AND ( salary >= 5000 
    								 AND salary <= 30000 ) ) 
    					  OR ( department_id = 50 
    						   AND ( job_id = 'MK_MAN' 
    								  OR job_id = 'MK_REP' 
    								  OR job_id = 'PU_MAN' 
    								  OR job_id = 'PU_CLERK' 
    								  OR job_id = 'HR_REP' 
    								  OR job_id = 'SH_CLERK' 
    								  OR job_id = 'IT_PROG' 
    								  OR job_id = 'SA_REP' ) 
    						   AND ( salary >= 5000 
    								 AND salary <= 30000 ) ) 
    					  OR ( department_id = 60 
    						   AND ( job_id = 'MK_MAN' 
    								  OR job_id = 'MK_REP' 
    								  OR job_id = 'PU_MAN' 
    								  OR job_id = 'PU_CLERK' 
    								  OR job_id = 'HR_REP' 
    								  OR job_id = 'SH_CLERK' 
    								  OR job_id = 'IT_PROG' 
    								  OR job_id = 'SA_REP' ) 
    						   AND ( salary >= 5000 
    								 AND salary <= 30000 ) ) 
    					  OR ( department_id = 80 
    						   AND ( job_id = 'MK_MAN' 
    								  OR job_id = 'MK_REP' 
    								  OR job_id = 'PU_MAN' 
    								  OR job_id = 'PU_CLERK' 
    								  OR job_id = 'HR_REP' 
    								  OR job_id = 'SH_CLERK' 
    								  OR job_id = 'IT_PROG' 
    								  OR job_id = 'SA_REP' ) 
    						   AND ( salary >= 5000 
    								 AND salary <= 30000 ) ) 
    					  OR ( department_id = 20 
    						   AND ( job_id = 'MK_MAN' 
    								  OR job_id = 'MK_REP' 
    								  OR job_id = 'PU_MAN' 
    								  OR job_id = 'PU_CLERK' 
    								  OR job_id = 'HR_REP' 
    								  OR job_id = 'SH_CLERK' 
    								  OR job_id = 'IT_PROG' 
    								  OR job_id = 'SA_REP' ) 
    						   AND ( salary >= 5000 
    								 AND salary <= 30000 ) ) 
    					  OR ( department_id = 20 
    						   AND ( job_id = 'MK_MAN' 
    								  OR job_id = 'MK_REP' 
    								  OR job_id = 'PU_MAN' 
    								  OR job_id = 'PU_CLERK' 
    								  OR job_id = 'HR_REP' 
    								  OR job_id = 'SH_CLERK' 
    								  OR job_id = 'IT_PROG' 
    								  OR job_id = 'SA_REP' ) 
    						   AND ( salary >= 5000 
    								 AND salary <= 30000 ) ) ) 
    				GROUP  BY department_id, 
    						    job_id; 		
    			
  44. SELECT Ename, Sal, Job FROM Emp WHERE Ename IN('FORD', 'ALLEN');
  45. SELECT Ename, Sal, Comm FROM Emp WHERE Comm IS NULL;
  46. SELECT Ename, Sal, Comm FROM Emp WHERE Comm IS NOT NULL;
  47. SELECT Ename, Job FROM Emp WHERE Ename LIKE 'S%';
  48. SELECT Ename, Job FROM Emp WHERE Ename NOT LIKE 'S%';
  49. SELECT Ename, Job FROM Emp WHERE Ename LIKE '_A%';
  50. SELECT Ename, Sal, Deptno, HireDate FROM Emp WHERE HireDate LIKE '03-DEC-1981';
  51. SELECT Ename, Sal, Deptno, HireDate FROM Emp WHERE HireDate = '03-DEC-1981';
  52. INSERT INTO Dept VALUES(50, 'SHIPPING_DESK', 'HYDERABAD');
  53. SELECT Deptno, Dname, Loc FROM Dept WHERE Dname LIKE '%\_%' ESCAPE '\';
  54. SELECT Ename, Sal, Deptno, job FROM Emp ORDER BY Ename;
  55. SELECT Ename, Sal, Deptno, job FROM Emp ORDER BY Deptno, Sal;
  56. SELECT Ename, Sal, Deptno, job FROM Emp ORDER BY Deptno, Sal, Ename DESC ;
  57. SELECT Ename, Sal, Comm, Sal + NVL(Comm, 0) TotSal FROM Emp ORDER BY Sal + NVL(Comm, 0);
  58. SELECT Ename, Sal, Comm, Sal + NVL(Comm, 0) TotSal FROM Emp ORDER BY 4;
  59. SELECT Ename, Sal, Comm, Sal + NVL(Comm, 0) TotSal FROM Emp ORDER BY 4, 2 DESC, 1, Comm;

Go Back