Here are 50 practice SQL questions for beginners, using the departments and employees tables, covering operators, functions like TO_DATE and TO_CHAR, simple joins, and basic SQL operations.
This question will help you to write Oracle SQL queries. It covered different syntax related to oracle SQL.
Any version of oracle database can be used from oracle database 9 onwards. The tables used for this practice are departments and employees.
Schema should be unlock for this practice or download the sample schema from GitHub or oracle website.
1. Basic INNER JOIN Between Employees and Departments
Write a query to find the department name, employee’s first and last names, and their salary by joining the employees table with the departments table using the department_id.
2. Using TO_DATE for Date Comparison
Write a query to find all employees who were hired before '01-JAN-2015'. Use the TO_DATE function for date comparison.
3. Using TO_CHAR for Date Formatting
Write a query to display the employee's hire date formatted as YYYY-MM-DD. Only show employees hired after '01-JAN-2010'.
4. Find Employees with Salary Between a Range
Write a query to find all employees whose salary is between 50,000 and 100,000. Display their first name, last name, salary, and department name.
5. LEFT OUTER JOIN Between Employees and Departments
Write a query to find all departments and their employees, showing departments with no employees as well (use LEFT OUTER JOIN).
6. Using LIKE to Filter Employees' Names
Write a query to find all employees whose first name starts with the letter 'A'. Display their names and department names.
7. Using IN to Filter Multiple Values
Write a query to find all employees who work in either the 'HR' or 'Finance' department. Return their names and department names.
8. Finding Employees with a Specific Job Title
Write a query to find all employees with the job title 'Manager' and display their first name, last name, and department name.
9. Filtering with AND Operator
Write a query to display the names of employees whose salary is greater than 60,000 and were hired after '01-JAN-2014'. Display their first name, last name, and hire date.
10. Finding Employees Not in a Specific Department
Write a query to find all employees who do not work in the 'Sales' department. Display their names and department names.
11. Using BETWEEN to Find Employees with Salary Within a Range
Write a query to list all employees whose salary is between 40,000 and 80,000. Show their first name, last name, and salary.
12. Using COUNT to Find the Number of Employees in Each Department
Write a query to list each department and the number of employees working in each department. Use GROUP BY.
13. Using HAVING to Filter Groups Based on Aggregate
Write a query to find all departments where the number of employees is greater than 5. Use the HAVING clause to filter based on the count of employees.
14. Sorting Employees by Salary
Write a query to retrieve employees' names and salaries, sorted in descending order by salary.
15. Formatting Employee Hire Date with TO_CHAR
Write a query to show employee names and hire dates formatted as Day, DD Month YYYY. Use the TO_CHAR function.
16. Find Employees Who Do Not Have a Manager
Write a query to find all employees whose manager_id is NULL. Display their first name, last name, and department name.
17. Find Employees Working in More Than One Department
Write a query to list employees who are working in both the 'HR' and 'Sales' departments. Display their names and department names.
18. Find the Highest Salary in Each Department
Write a query to find the highest-paid employee in each department. Return the department name, employee name, and salary.
19. Using TO_DATE with Date Range
Write a query to list all employees who were hired between '01-JAN-2015' and '31-DEC-2020'. Use the TO_DATE function for the date range.
20. Using DISTINCT to Get Unique Department Names
Write a query to display all unique department names from the departments table.
21. Finding the Average Salary in Each Department
Write a query that calculates the average salary of employees in each department. Display the department name and the average salary.
22. Using NULL to Filter Missing Data
Write a query to find employees whose hire_date is missing (NULL). Display their first name, last name, and department name.
23. Finding Employees Who Have Been with the Company for Over 5 Years
Write a query to find employees whose hire date is more than 5 years ago from today's date. Show their first name, last name, and hire date.
24. Find Departments with More Than 10 Employees
Write a query to find all departments that have more than 10 employees. Return the department name and the number of employees in that department.
25. Using TO_CHAR to Extract the Year from the Hire Date
Write a query to list employees' names, job titles, and the year they were hired (formatted as YYYY).
26. Find Employees with Salaries Below Average
Write a query to find all employees whose salary is below the average salary in the company. Display their first name, last name, and salary.
27. Join Employees with Managers
Write a query to display employees' names along with their manager's name. Use employees.manager_id to join with employees.employee_id.
6
28. Find Departments Without Employees
Write a query to list departments that do not have any employees. Use a LEFT OUTER JOIN and filter for NULL values in the employee columns.
29. Find Employees Who Have a Salary Greater than the Average Salary
Write a query to find all employees who have a salary greater than the average salary. Display their first name, last name, and salary.
30. Find Employees with Salary Greater Than 50,000 and Job Title as 'Sales Representative'
Write a query to list employees who have a salary greater than 50,000 and a job title of 'Sales Representative'. Display their first name, last name, and salary.
31. Using GROUP BY with Aggregates
Write a query to list the department name and the total salary expense for each department.
32. Using ORDER BY with Multiple Columns
Write a query to list employees’ names and their salaries, ordered first by department name (ascending) and then by salary (descending).
33. Find Employees Hired in the Same Year
Write a query to find all employees who were hired in the year 2017. Display their first name, last name, and hire date.
34. Using OR for Multiple Conditions
Write a query to find employees who are either in the 'HR' department or have a salary greater than 75,000.
35. Find Employees Whose Names Start with 'S'
Write a query to find all employees whose first name starts with the letter 'S'. Display their names and department names.
36. Find the Employee Who Has Worked the Longest
Write a query to find the employee who has been with the company the longest. Return their first name, last name, and hire date.
37. Find the Department with the Highest Average Salary
6
Write a query to find the department with the highest average salary. Display the department name and the average salary.
38. Using NOT IN to Exclude Certain Departments
Write a query to find all employees who do not work in the 'Sales' or 'Marketing' department. Display their names and department names.
39. Find Employees with the Same Job Title
Write a query to find all employees with the job title 'Engineer'. Display their names and department names.
40. Find Employees with Salary Greater than the Department's Average Salary
Write a query to find employees who earn more than the average salary in their department. Display their first name, last name, and salary.
41. Using TO_DATE to Convert Date Strings
Write a query to find employees hired after '01-JAN-2014', using the TO_DATE function to convert the string date into a date format.
42. Find Employees Who Work in Multiple Departments
Write a query to list employees who work in both the 'HR' and 'Sales' departments.
43. Using CONCAT to Combine First and Last Name
Write a query to concatenate the first and last name of employees and display the full name. Include their department names.
44. Find Employees Whose First Name is Exactly 4 Characters Long
Write a query to find all employees whose first name has exactly 4 characters. Display their names and department names.
45. Find Employees with the Minimum Salary
Write a query to find the employee with the lowest salary. Display their name and salary.
46. Use TO_CHAR to Format Salaries with Commas
Write a query to format employee salaries with commas (e.g., '12,000'). Display their name and formatted salary.
6
47. Find Employees with No Department
Write a query to find all employees who do not belong to any department. Display their name and department name (NULL if no department).
48. Find Employees with a Salary Greater than the Median Salary
Write a query to find employees who have a salary greater than the median salary in the company. Display their first name, last name, and salary.
49. Using COALESCE to Replace NULL Values
Write a query to display employee names and their managers. If an employee has no manager (NULL), display 'No Manager' instead.
50. Find the Top 3 Earning Employees
Write a query to find the top 3 highest-paid employees. Display their names and salaries, sorted by salary in descending order.
These 50 questions should provide a comprehensive range of topics for beginner Oracle SQL practice, allowing you to practice a variety of techniques such as joins, aggregate functions, date functions, and conditional filtering.
Please share your feedback in comment section.
0 Comments