Oracle SQL Queries: where statement Exercises with Solution

Oracle Queries: where statement [14 exercises with solution]

A WHERE clause is an optional part of a Select Expression, DELETE statement, or UPDATE statement. The WHERE clause lets you select rows based on a Boolean expression. Only rows for which the expression evaluates to TRUE are returned in the result, or, in the case of a DELETE statement, deleted, or, in the case of an UPDATE statement, updated.

1. Write a Oracle SQL query to get the employee no, first name and last name for those employees who are earning commission.

Click me to see the solution

2. Write a Oracle SQL query to get the employee id, first_name and last_name for those employees do not earn any commission.

Click me to see the solution

3. Write a Oracle SQL query to get the the first_name and last_name of the employees who are working as 'ST_CLERK', 'SA_MAN' or 'IT_PROG' and drawing a salary more than 3000.

Click me to see the solution

4. Write a Oracle SQL query to get the first_name and last_name, job_id, department_id of the employees who are working in the department no 10 or 20 or 40 or employees working as 'ST_CLERK', 'SA_MAN' or 'IT_PROG'.

Click me to see the solution

5. Write a Oracle SQL query to get the first_name and last_name of the employees whose first_name are exactly five characters in length.

Click me to see the solution

6. Write a Oracle SQL query to get the the first_name and last_name of the employees who are not working as 'AC_MGR'.

Click me to see the solution

7. Write a Oracle SQL query to get the the first_name and last_name of the employees who are not working as 'ST_CLERK', 'SA_MAN' or 'IT_PROG'.

Click me to see the solution

8. Write a Oracle SQL query to get the maximum salary being paid to 'ST_CLERK'.

Click me to see the solution

9. Write a Oracle SQL query to get the maximum salary being paid to department ID.

Click me to see the solution

10. Write a Oracle SQL query to get the minimum salary being paid to SA_MAN.

Click me to see the solution

11. Write a Oracle SQL query to get the total salary drawn by 'SA_MAN' working in the department number 80.

Click me to see the solution

12. Write a Oracle SQL query to get the department number with more than 10 employees in each department.

Click me to see the solution

13. Write a Oracle SQL query to get the designations (jobs) along with the total salary for those designations where total salary is more than 50000.

Click me to see the solution

14. Write a Oracle SQL query to get the designations (jobs) along with the total number of employees in each designation. The output should contain only those jobs with more than three employees.

Click me to see the solution

More to Come !