Oracle SQL queries practice questions
Oracle database is one of the top database use in many organizations for manage data, transactions, data analysis.
Oracle SQL queries play vital role in this regards. Below question will help out students, beginners or intermediate level to understand and get check their knowledge of Oracle SQL by writing queries based on provided questions.
The schema uses for this practice is emp which can be download fro oracle website or git hub.
· EMPLOYEES (employee_id, first_name, last_name, job_id, salary, manager_id, department_id, hire_date)
· DEPARTMENTS (department_id, department_name, manager_id, location_id)
· LOCATIONS (location_id, city, state_province, country_id)
· JOBS (job_id, job_title, min_salary, max_salary)
· COUNTRIES (country_id, country_name, region_id)
· REGIONS (region_id, region_name)
---
🔹 JOINs (10 Questions)
1. List all employees with their department names.
2. Show all employees with their job titles.
3. List employee names along with the name of their manager.
4. Retrieve all employees and their location (city and state).
5. Show department names and the names of employees working in them.
6. List all departments and their managers’ full names.
7. Get all employees along with their department and job title.
8. Display employee name, department name, location city, and job title.
9. Show the employee name and the region name they work in.
10. List all employees even if they don’t belong to any department.
---
🔹 Aggregate Functions (10 Questions)
11. Count total number of employees.
12. Find the average salary of all employees.
13. Display maximum and minimum salaries.
14. Count how many employees work in each department.
15. Get average salary for each job title.
16. Count how many employees report to each manager.
17. Show total number of employees hired each year.
18. Find total salary per department.
19. Count employees working in each location.
20. Show how many departments exist in each region.
---
🔹 Character/String Functions (8 Questions)
21. Display all employee names in uppercase.
22. Concatenate first_name and last_name into a full_name column.
23. Get first 3 characters of each employee’s last name.
2 / 6
24. List employees whose first name starts with 'S'.
25. Show employee names and length of their names.
26. Replace 'Manager' with 'Lead' in job titles.
27. Trim spaces from employee last names.
28. Find employees whose name contains 'an' (case insensitive).
---
🔹 Date Functions (8 Questions)
29. Show all employees hired in the year 2022.
30. Display employees hired in the last 90 days.
31. Get the number of days since each employee was hired.
32. Show hire_date and the day of the week employees were hired.
33. Add 30 days to each employee's hire_date.
34. Show employees who were hired on a Monday.
35. Extract month and year from hire_date.
36. List employees hired in the same year as ‘employee_id = 101’.
---
🔹 Math Functions (8 Questions)
37. Show employee salary rounded to the nearest thousand.
38. Calculate 15% bonus for all employees.
39. Display salary after a 10% raise.
40. Find employees with even-numbered employee IDs.
41. Use FLOOR and CEIL on salary to show variations.
42. Find absolute difference between min and max salary in each job.
43. Show salary MOD 1000 for each employee.
44. Show employees whose salary is divisible by 500.
---
🔹 GROUP BY (8 Questions)
45. Show average salary per department.
46. Count employees per job title.
47. Get total employees per location.
48. Display total salary per department having more than 3 employees.
49. Count employees grouped by year of hire.
50. Get average salary per location city.
51. Show departments where the average salary is over 7000.
52. List number of employees per region.
3 / 6
---
🔹 ORDER BY (4 Questions)
53. List employees ordered by salary descending.
54. Show departments sorted by name alphabetically.
55. Display employees by hire_date, latest first.
56. List jobs ordered by minimum salary.
---
🔹 WHERE Clause (4 Questions)
57. Show employees in department 90.
58. List employees hired after January 1, 2023.
59. Get employees with salaries between 5000 and 9000.
60. Find employees who work in the ‘Seattle’ location.
Intermediate level
JOINs (10 Questions)
1. Retrieve employees who do not have a department assigned, but their manager does.
2. List the names of employees who work in the same location as their manager.
3. Display the departments that have more than one location using joins.
4. List employees who share the same job title as someone in another department.
5. Get employee name, department name, and region name in a multi-table join.
6. Show employees and their job title history (assume a JOB_HISTORY table).
7. List employees who report to a manager in a different department.
8. Show the manager and the number of direct reports, including those with no reports.
9. List all employees whose location city starts with the same letter as their name.
10. Display all departments with no employees, but still show the location.
---
4 / 6
🔹 Aggregate Functions (10 Questions)
11. Find departments where the average salary is above the overall average salary.
12. For each department, show employee count, but exclude the highest paid employee.
13. List all job titles where the max salary exceeds the company-wide average salary.
14. Find the employee(s) with the second highest salary.
15. Show all locations where the total payroll exceeds 100,000.
16. List departments where the sum of salaries exceeds the max salary in the company.
17. Count employees who were hired in the same year as their manager.
18. Show the difference between the max and min salary per department.
19. Get the total salary paid to employees hired after 2020, grouped by year.
20. Find all departments with more than 50% of their employees earning below average.
---
🔹 Character Functions (8 Questions)
21. Display all employee names where the last 3 letters of the first name match the first 3 letters of the last name.
22. Find all employees whose names are palindromes (same forwards and backwards).
23. List employees whose full name contains exactly 2 vowels.
24. Show job titles that do not contain any digits or special characters.
25. Find employees whose first name starts and ends with the same letter.
26. Convert employee names to proper case (e.g., john DOE → John Doe).
27. Show departments where the department name length is prime.
28. List cities where the city name is longer than the country name.
---
🔹 Date Functions (8 Questions)
29. List employees who were hired in a leap year.
30. Find employees hired on a weekend.
31. Show employees whose hire date is the same day and month as their manager’s hire date.
32. Calculate the tenure in years and months for each employee.
33. Find departments where the average hire date is after Jan 1, 2021.
34. List employees hired within 30 days before or after their department’s creation date (assume DEPT_CREATION_DATE).
35. Show the number of employees hired per month in the last 5 years, including months with 0 hires.
36. Display the number of days between an employee’s hire date and their manager’s hire date.
---
🔹 Math Functions (8 Questions)
37. Find all employees whose salary is a perfect square.
38. Show employees where the difference between salary and manager's salary is more than 20%.
5 / 6
39. List departments where sum(salary)/count(employees) is not equal to the actual AVG(salary) (hint: test for data issues).
40. Find the modulo between an employee’s salary and the average salary in their job.
41. List employees whose salary raised by 10% is still less than the company average.
42. Round employee salaries to the nearest multiple of 500.
43. Find employees whose salary is the closest to the average in their department.
44. List job titles where the max salary is a multiple of min salary.
---
🔹 GROUP BY / HAVING (8 Questions)
45. List departments with more than 3 employees, and the average salary > 5000.
46. Find locations where every department has more than 5 employees.
47. Show job titles where total salary > 2x the average department salary.
48. List departments where min hire date is after 2020 and average salary < max salary company-wide.
49. For each region, show the average employee count per department.
50. Show departments where the number of managers > 1.
51. Find departments where more than half the employees were hired in the last 2 years.
52. List cities where the average department salary is above average city salary.
---
🔹 ORDER BY (4 Questions)
53. List employees sorted by length of service descending, then by salary descending.
54. Show job titles ordered by the number of employees in them.
55. List cities ordered by total salary payout, descending.
56. Show employees ordered by how close their salary is to the max salary in their department.
---
🔹 WHERE / Subqueries (4 Questions)
57. Find employees who earn more than the average salary of their job title.
58. List employees who were hired before their manager.
59. Show emp
loyees working in the top 3 highest paying departments (by avg salary).
60. Find departments where no employee has a salary above the company average.
0 Comments