ORACLE SQL

Oracle SQL Practice

 Assumed Table Structure (Standard Oracle)

EMP: EMPNO,  ENAME. JOB, MGR, HIREDATE, SAL, COMM, DEPTNO

DEPT: DEPTNO, DNAME, LOC

Choose Topic: -- Select Topic -- SELECT QUERY WHERE CLAUSE LIKE OPERATOR GROUP FUNCTION, GROUP BY, HAVING SUBQUERIES

SELECT QUERY (1–10)

1. Display all columns of the EMP table.
2. Display EMPNO, ENAME, and SAL of all employees.
3. Display unique job titles from EMP.
4. Display employee names along with their department numbers.
5. Display all department details.
6. Display distinct department numbers from EMP.
7. Display employee name and annual salary (SAL × 12).
8. Display employee name, salary, and commission.
9. Display job and salary of all employees.
10. Display employees along with their manager numbers.

WHERE CLAUSE (1–60)

1. Display employees whose salary is greater than 3000.
2. Display employees working in department 10.
3. Display employees whose job is 'CLERK'.
4. Display employees hired after 01-JAN-81.
5. Display employees whose salary is less than 1500.
6. Display employees whose salary is > 2000 and job is 'MANAGER'.
7. Display employees working in department 10 or 20.
8. Display employees whose job is 'SALESMAN' and salary > 1500.
9. Display employees who are either CLERK or ANALYST.
10. Display employees hired before 1982 and working in department 30.
11. Display employees working in departments 10, 20, or 30.
12. Display employees whose job is not MANAGER or CLERK.
13. Display employees whose department is not 10.
14. Display employees whose salary is between 1000 and 3000.
15. Display employees hired between 01-JAN-81 and 31-DEC-81.
16. Display employees whose name starts with 'A'.
17. Display employees whose name ends with 'N'.
18. Display employees whose name contains 'LL'.
19. Display employees whose name has exactly 5 characters.
20. Display employees whose second character is 'A'.
21. Display all employees ordered by salary in ascending order.
22. Display all employees ordered by salary in descending order.
23. Display employees ordered by hire date.
24. Display employees ordered by department number and salary.
25. Display employees ordered by job (ascending) and salary (descending).
26. Display employee names ordered alphabetically.
27. Display employees of department 30 ordered by salary.
28. Display employees ordered by annual salary.
29. Display employees ordered by commission.
30. Display employees ordered by job and name.
31. Display employee name as `Employee_Name`.
32. Display salary as `Monthly_Salary`.
33. Display salary × 12 as `Annual_Salary`.
34. Display employee name and job with aliases.
35. Display department number as `Dept_No`.
36. Display employee name and salary with meaningful aliases.
37. Display commission as `Incentive`.
38. Display hire date as `Joining_Date`.
39. Display employee name and department number with aliases.
40. Display job and salary using aliases.
41. Display employees whose commission is NULL.
42. Display employees who are getting commission.
43. Display employees who do not have a manager.
44. Display employees whose manager is not NULL.
45. Display employees whose commission is NULL and salary > 2000.
46. Display employees whose commission is NULL or salary > 3000.
47. Display employees whose commission is NOT NULL and department is 30.
48. Display employees who do not earn commission and work in department 20.
49. Display employees whose manager is NULL and salary > 4000.
50. Display employees whose commission is NULL but job is SALESMAN.
51. Display employee name and annual salary, ordered by annual salary.
52. Display employees whose commission is NULL, ordered by salary descending.
53. Display employee name as Name and salary as Pay for department 10.
54. Display employees hired before 1982 with aliases, ordered by hire date.
55. Display employees whose name starts with 'S', ordered by salary.
56. Display employee name and department, ordered by department and name.
57. Display employees whose salary is between 1500 and 3000, ordered by salary.
58. Display employees whose commission is NOT NULL, ordered by commission.
59. Display employees of department 30 whose salary > 1500, ordered by salary.
60. Display employee name, job, and salary with aliases, ordered by job.

LIKE OPERATOR (1–60)

1. Display employees whose name starts with A.
2. Display employees whose name starts with S.
3. Display employees whose name ends with N.
4. Display employees whose name ends with R.
5. Display employees whose name contains LL.
6. Display employees whose name contains AR.
7. Display employees whose job starts with M.
8. Display employees whose job ends with AN.
9. Display employees whose job contains CL.
10. Display departments whose location starts with N.
11. Display employees whose name has exactly 4 characters.
12. Display employees whose name has exactly 5 characters.
13. Display employees whose name has A as the second character.
14. Display employees whose name has L as the third character.
15. Display employees whose job has exactly 6 characters.
16. Display employees whose name starts with any letter but ends with N.
17. Display employees whose job starts with any one character and ends with ER.
18. Display employees whose name has A as the first and N as the last character.
19. Display employees whose name has exactly 2 characters before 'E'.
20. Display employees whose name has exactly 1 character after 'A'.
21. Display employees whose name starts with A and ends with N.
22. Display employees whose name contains E anywhere.
23. Display employees whose name does not contain A.
24. Display employees whose job contains MAN.
25. Display employees whose job does not start with C.
26. Display employees whose name contains two L consecutively.
27. Display employees whose name starts with J and has S anywhere.
28. Display employees whose job starts with S and ends with AN.
29. Display employees whose name contains A as the second last character.
30. Display employees whose name contains exactly one character between A and E.
31. Display employees whose name starts with S and salary > 2000.
32. Display employees whose job ends with MAN and department is 20.
33. Display employees whose name contains A and commission is NULL.
34. Display employees whose job starts with C and salary < 1500.
35. Display employees whose name ends with S and hired after 1981.
36. Display employees whose name starts with M and department is not 10.
37. Display employees whose job contains ER and salary between 2000 and 3000.
38. Display employees whose name starts with A or job starts with S.
39. Display employees whose name contains I and manager is NOT NULL.
40. Display employees whose name ends with N and commission is NOT NULL.
41. Display employee name as Name whose name starts with S, ordered alphabetically.
42. Display employee name and salary as Pay whose name contains A, ordered by salary.
43. Display employees whose job starts with M, ordered by job and salary.
44. Display employees whose name ends with E, ordered by hire date.
45. Display employee name and annual salary for employees whose name starts with J.
46. Display employees whose name contains R, ordered by department number.
47. Display employee name as Employee whose job ends with AN.
48. Display employees whose name starts with K, ordered by salary descending.
49. Display employees whose name contains L, ordered by name and salary.
50. Display employees whose job starts with C, ordered by job.
51. Display employees whose name has exactly 6 characters and ends with R.
52. Display employees whose name has A as the third character from start.
53. Display employees whose name has E as the second character from end.
54. Display employees whose name contains at least two characters before 'A'.
55. Display employees whose job contains exactly one 'A'.
56. Display employees whose name does not start with a vowel.
57. Display employees whose job does not contain ER.
58. Display employees whose name starts and ends with the same character.
59. Display employees whose name contains A and E in any order.
60. Display employees whose name contains A but not as the first character.

GROUP FUNCTION, GROUP BY, HAVING (1–70)

1. Display the total number of employees.
2. Display the total salary of all employees.
3. Display the average salary of all employees.
4. Display the highest salary.
5. Display the lowest salary.
6. Display the total number of departments in EMP table.
7. Display the number of employees who are managers.
8. Display the total commission paid to employees.
9. Display the average commission.
10. Display the number of employees who are getting commission.
11. Display department-wise total salary.
12. Display department-wise average salary.
13. Display department-wise number of employees.
14. Display job-wise number of employees.
15. Display job-wise total salary.
16. Display job-wise maximum salary.
17. Display job-wise minimum salary.
18. Display department-wise maximum salary.
19. Display department-wise minimum salary.
20. Display department-wise average commission.
21. Display department-wise job-wise number of employees.
22. Display department-wise job-wise total salary.
23. Display department-wise job-wise average salary.
24. Display job-wise department-wise maximum salary.
25. Display job-wise department-wise minimum salary.
26. Display department and job having highest total salary.
27. Display department and job having lowest average salary.
28. Display department-wise job-wise total commission.
29. Display department-wise job-wise employee count.
30. Display department and job having maximum number of employees.
31. Display department-wise total salary for department numbers greater than 10.
32. Display job-wise employee count where salary > 2000.
33. Display department-wise average salary for employees hired after 1981.
34. Display job-wise total salary for CLERK and MANAGER.
35. Display department-wise number of employees where commission is NOT NULL.
36. Display job-wise average salary where department is 20.
37. Display department-wise total salary excluding department 30.
38. Display job-wise max salary where salary > 1500.
39. Display department-wise employee count where job is SALESMAN.
40. Display job-wise total commission where commission is NOT NULL.
41. Display departments having more than 3 employees.
42. Display jobs having average salary greater than 2500.
43. Display departments whose total salary is greater than 10000.
44. Display jobs having more than 2 employees.
45. Display departments having maximum salary greater than 4000.
46. Display jobs whose minimum salary is less than 1000.
47. Display department-wise total salary where employee count > 4.
48. Display job-wise average salary where total salary > 5000.
49. Display departments having employees with commission.
50. Display jobs having more than one manager.
51. Display department-wise count of employees getting commission.
52. Display job-wise total commission.
53. Display department-wise average commission excluding NULLs.
54. Display total commission paid (ignore NULLs).
55. Display number of employees without commission.
56. Display department-wise count of employees where manager is NULL.
57. Display job-wise average commission.
58. Display departments where all employees get commission.
59. Display departments where no employee gets commission.
60. Display job-wise count of employees not getting commission.
61. Display department number and total salary where average salary is greater than overall average salary.
62. Display job having highest average salary.
63. Display department having minimum total salary.
64. Display jobs where max salary is equal to department 20 max salary.
65. Display departments having same number of employees.
66. Display jobs whose total salary is more than salary of all CLERKs combined.
67. Display department having highest number of employees.
68. Display jobs having average salary greater than department-wise average salary.
69. Display department where sum of salaries is maximum.
70. Display job having minimum average salary.

SUBQUERIES (1–70)

1. Display employees whose salary is greater than JONES.
2. Display employees earning the same salary as SCOTT.
3. Display employees hired after ALLEN.
4. Display employees working in the same department as BLAKE.
5. Display employees having the same job as MILLER.
6. Display employees earning less than the average salary.
7. Display employees earning more than the maximum salary of CLERKs.
8. Display employee(s) with the minimum salary.
9. Display employee(s) with the maximum salary.
10. Display employees working in the department named ACCOUNTING.
11. Display employees working in departments located in NEW YORK.
12. Display employees whose salary matches any salary in department 30.
13. Display employees earning more than all CLERKs.
14. Display employees earning less than any MANAGER.
15. Display employees whose job appears in department 20.
16. Display employees whose department has at least one SALESMAN.
17. Display employees earning the same salary as any CLERK.
18. Display employees who are not working in departments of SALESMAN.
19. Display employees earning more than all employees in department 10.
20. Display employees whose department number is among departments with more than 3 employees.
21. Display employees earning more than the department average salary.
22. Display employees earning the highest salary in each department.
23. Display employees earning the lowest salary in each job.
24. Display departments having total salary greater than department 20.
25. Display employees whose salary equals the maximum salary of their job.
26. Display employees earning more than the overall average salary.
27. Display department(s) having the maximum number of employees.
28. Display jobs having minimum average salary.
29. Display employees earning more than the average salary of CLERKs.
30. Display department(s) where average salary is highest.
31. Display employees earning more than the average salary of their own department.
32. Display employees earning the maximum salary in their department.
33. Display employees earning the minimum salary in their department.
34. Display employees whose salary is greater than the average salary of their job.
35. Display employees who are the only employee in their department.
36. Display departments having employees earning more than 5000.
37. Display employees whose department has more than 3 employees.
38. Display employees whose job has more than 2 employees.
39. Display employees working in departments where no one gets commission.
40. Display employees whose department has at least one manager.
41. Display departments that have employees.
42. Display departments that have no employees.
43. Display employees who are managers (have subordinates).
44. Display employees who are not managers.
45. Display employees working in departments where SALESMAN exists.
46. Display departments where no SALESMAN exists.
47. Display employees whose department has employees with commission.
48. Display departments where all employees get commission.
49. Display employees whose department has no CLERKs.
50. Display employees who work in departments located in CHICAGO.
51. Display department number and average salary of employees whose salary is greater than 2000.
52. Display job and total salary of employees earning more than the department average.
53. Display departments having average salary greater than overall average salary.
54. Display job-wise employee count from employees earning more than 1500.
55. Display departments having more than 2 employees earning commission.
56. Display job and maximum salary from employees hired after 1981.
57. Display department-wise total salary excluding department 30.
58. Display departments having total salary greater than overall total salary average.
59. Display job-wise average salary where department has more than 3 employees.
60. Display department and job having maximum total salary.
61. Display employees earning the second highest salary.
62. Display employees earning the third highest salary.
63. Display employees whose salary is greater than 50% of employees.
64. Display employees earning more than the median salary.
65. Display employees whose salary is equal to department-wise second highest salary.
66. Display employees whose salary is less than all MANAGERS but more than any CLERK.
67. Display departments where maximum salary equals minimum salary.
68. Display employees earning more than the average salary of departments located in DALLAS.
69. Display jobs where average salary is greater than overall average salary.
70. Display employees whose salary is greater than salary of their manager.