Test Your Oracle Sub Queries Knowledge with This Engaging SQL Quiz

Progress

True / False

In Oracle, a subquery is a query nested inside another query.

True False

A subquery, also known as an inner query, is a query embedded within another SQL statement.

True / False

In Oracle, a subquery can be used in the SELECT clause to return a single value.

True False

A subquery in the SELECT clause is often used to return a single value, such as an aggregated result.

True / False

In Oracle, subqueries can only be used in the WHERE clause.

True False

Subqueries can be used in various parts of an SQL statement, including the SELECT, FROM, WHERE, HAVING, and ORDER BY clauses.

True / False

In Oracle, subqueries can return multiple rows.

True False

Subqueries can return multiple rows, especially when used with the IN or EXISTS operators.

True / False

In Oracle, a correlated subquery is executed once for each row processed by the outer query.

True False

A correlated subquery refers to columns from the outer query and is evaluated once for each row processed by the outer query.

True / False

In Oracle, subqueries can be used to update records in a table.

True False

Subqueries can be used in UPDATE statements to specify the new values based on a query.

True / False

In Oracle, subqueries can be used in the FROM clause to create a derived table.

True False

Subqueries in the FROM clause are known as derived tables or inline views and can be treated as tables within the main query.

True / False

In Oracle, the ANY and ALL operators can be used with subqueries to compare a value to a set of values returned by the subquery.

True False

The ANY and ALL operators allow comparisons between a single value and a set of values returned by a subquery.

True / False

In Oracle, a subquery cannot be used in a JOIN condition.

True False

Subqueries can be used in JOIN conditions to further refine the join criteria.

Q10

True / False

The Oracle Certified Professional (OCP) exam includes knowledge on using subqueries effectively, understanding correlated subqueries, and optimizing subquery performance in complex SQL queries.

True False

The OCP certification covers advanced topics, including the effective use of subqueries, understanding correlated subqueries, and optimizing their performance in complex SQL queries.

Q11

Single Choice

What is a subquery in ORACLE SQL?

A query that joins multiple tables A query within another query A query that deletes dataA query that creates a new table

A subquery is a query nested inside another query, often used to retrieve data that will be used in the main query.

Q12

Single Choice

Where can a subquery be placed in an ORACLE SQL statement?

Only in the SELECT clause Only in the WHERE clause In the SELECT, FROM, or WHERE clausesOnly in the FROM clause

Subqueries can be placed in various parts of an Oracle SQL query, including the SELECT, FROM, or WHERE clauses.

Q13

Single Choice

Which of the following ORACLE SQL statements correctly uses a subquery?

SELECT department_id FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); SELECT department_id FROM employees WHERE salary > AVG(salary); Which of the following ORACLE SQL statements correctly uses a subquery?SELECT department_id FROM employees WHERE salary > salary;

This query uses a subquery to compare employee salaries with the average salary.

Q14

Single Choice

Consider the following ORACLE SQL query:What does this query return?

SQL Code

SELECT employee_id, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = 10);

Employees with salaries greater than the overall average salary Employees with salaries less than the overall average salary Employees with salaries greater than the average salary in department 10Employees with salaries equal to the average salary in department 10

The subquery calculates the average salary in department 10, and the main query returns employees with salaries greater than this value.

Q15

Single Choice

In ORACLE SQL, what is the difference between a correlated subquery and a regular subquery?

A correlated subquery depends on the outer query for its values A correlated subquery is faster than a regular subquery A correlated subquery is always executed firstThere is no difference between them

A correlated subquery refers to columns in the outer query, meaning it is evaluated once for each row processed by the outer query.

Q16

Single Choice

Which of the following ORACLE SQL queries uses a correlated subquery?

SELECT employee_id, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); SELECT e1.employee_id, e1.salary FROM employees e1 WHERE e1.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e1.department_id = e2.department_id); SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);SELECT employee_id, salary FROM employees WHERE salary = (SELECT MAX(salary) FROM employees);

This query is an example of a correlated subquery because it references the outer query’s department_id in the subquery.

Q17

Single Choice

What is the result of using a subquery in the FROM clause of an ORACLE SQL statement?

It creates a temporary table that can be queried It filters rows before the main query is executed It performs a join operation between two tablesIt deletes data from a table

A subquery in the FROM clause is often referred to as an inline view or derived table, and it creates a temporary result set that can be used by the outer query.

Q18

Single Choice

Consider the following ORACLE SQL query:What does this query do?

SQL Code

SELECT department_name, MAX(salary)
FROM (SELECT department_id, salary FROM employees WHERE department_id IN (10, 20, 30))
GROUP BY department_id;

Returns the highest salary in the departments 10, 20, and 30 Returns the highest salary in the entire company Returns the lowest salary in the departments 10, 20, and 30Returns the total salary in the departments 10, 20, and 30

The subquery filters for departments 10, 20, and 30, and the outer query calculates the maximum salary within each of these departments.

Q19

Single Choice

Which of the following ORACLE SQL queries demonstrates the use of a subquery with the EXISTS operator?

SELECT employee_id, first_name FROM employees WHERE EXISTS (SELECT 1 FROM departments WHERE department_id = employees.department_id); SELECT employee_id, first_name FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Sales'); SELECT employee_id, first_name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);SELECT employee_id, first_name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 100);

This query uses the EXISTS operator to check if a corresponding department exists for each employee, returning those that do.

Q20

Single Choice

Given the following ORACLE SQL query:What does this query return?

SQL Code

SELECT employee_id, first_name, salary
FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = 10);

Employees with salaries greater than every employee in department 10 Employees with salaries less than every employee in department 10 Employees with the same salary as the highest salary in department 10Employees with the lowest salary in the company

The ALL operator is used to compare the salary of each employee against all salaries in department 10, returning those who earn more than every employee in that department.

Q21

Multiple Choice

Which SQL code snippet demonstrates the use of a subquery to retrieve the highest salary in each department in Oracle?

SQL Code

SELECT department_id, employee_id, salary
FROM employees
WHERE salary = (SELECT MAX(salary)
FROM employees e
WHERE e.department_id = employees.department_id);

SELECT department_id, employee_id, salary
FROM employees
WHERE salary = (SELECT MAX(salary)
FROM employees e
WHERE e.department_id = employees.department_id);

SELECT department_id, employee_id, salary
FROM employees
WHERE salary = (SELECT MAX(salary)
FROM employees e
WHERE e.department_id = employees.department_id);

SELECT department_id, employee_id, salary FROM employees WHERE salary = (SELECT MAX(salary) FROM employees e WHERE e.department_id = employees.department_id); SELECT department_id, employee_id, salary FROM employees WHERE salary = (SELECT MAX(salary) FROM employees e WHERE e.department_id = employees.department_id); SELECT department_id, employee_id, salary FROM employees WHERE salary = (SELECT MAX(salary) FROM employees e WHERE e.department_id = employees.department_id);SELECT department_id, employee_id, salary FROM employees WHERE salary > 50000;

Options A, B, and C correctly demonstrate the use of a subquery to retrieve the highest salary in each department. Option D is incorrect because it does not use a subquery.

Q22

Multiple Choice

Which SQL code snippet demonstrates the use of a subquery to find all employees who earn more than the average salary in their department in Oracle?

SQL Code

SELECT employee_id, first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary)
FROM employees e
WHERE e.department_id = employees.department_id);

SELECT employee_id, first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary)
FROM employees e
WHERE e.department_id = employees.department_id);

SELECT employee_id, first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary)
FROM employees e
WHERE e.department_id = employees.department_id);

SELECT employee_id, first_name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees e WHERE e.department_id = employees.department_id); SELECT employee_id, first_name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees e WHERE e.department_id = employees.department_id); SELECT employee_id, first_name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees e WHERE e.department_id = employees.department_id);SELECT employee_id, first_name, salary FROM employees WHERE salary > 60000;

Options A, B, and C correctly use a subquery to find all employees who earn more than the average salary in their department. Option D is incorrect because it does not use a subquery.

Q23

Multiple Choice

Which SQL code snippet demonstrates the use of a correlated subquery to retrieve employees with the same job title as their manager in Oracle?

SQL Code

SELECT employee_id, first_name, job_id
FROM employees
WHERE job_id = (SELECT job_id
FROM employees e
WHERE e.employee_id = employees.manager_id);

SELECT employee_id, first_name, job_id
FROM employees
WHERE job_id = (SELECT job_id
FROM employees e
WHERE e.employee_id = employees.manager_id);

SELECT employee_id, first_name, job_id
FROM employees
WHERE job_id = (SELECT job_id
FROM employees e
WHERE e.employee_id = employees.manager_id);

SELECT employee_id, first_name, job_id FROM employees WHERE job_id = (SELECT job_id FROM employees e WHERE e.employee_id = employees.manager_id); SELECT employee_id, first_name, job_id FROM employees WHERE job_id = (SELECT job_id FROM employees e WHERE e.employee_id = employees.manager_id); SELECT employee_id, first_name, job_id FROM employees WHERE job_id = (SELECT job_id FROM employees e WHERE e.employee_id = employees.manager_id);SELECT employee_id, first_name, job_id FROM employees WHERE job_id = 'SA_REP';

Options A, B, and C correctly use a correlated subquery to retrieve employees with the same job title as their manager. Option D is incorrect because it does not use a correlated subquery.

Q24

Multiple Choice

Which SQL code snippet demonstrates the use of a subquery in the WHERE clause to find departments with at least one employee earning more than $80,000 in Oracle?

SQL Code

SELECT department_name
FROM departments
WHERE department_id IN (SELECT department_id
FROM employees
WHERE salary > 80000);

SELECT department_name
FROM departments
WHERE department_id IN (SELECT department_id
FROM employees
WHERE salary > 80000);

SELECT department_name
FROM departments
WHERE department_id IN (SELECT department_id
FROM employees
WHERE salary > 80000);

SELECT department_name FROM departments WHERE department_id IN (SELECT department_id FROM employees WHERE salary > 80000); SELECT department_name FROM departments WHERE department_id IN (SELECT department_id FROM employees WHERE salary > 80000); SELECT department_name FROM departments WHERE department_id IN (SELECT department_id FROM employees WHERE salary > 80000);SELECT department_name FROM departments WHERE department_id = 10;

Options A, B, and C correctly use a subquery in the WHERE clause to find departments with at least one employee earning more than $80,000. Option D is incorrect because it does not use a subquery.

Q25

Multiple Choice

Which SQL code snippet demonstrates the use of a subquery to list all employees who work in departments with more than 10 employees in Oracle?

SQL Code

SELECT employee_id, first_name
FROM employees
WHERE department_id IN (SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 10);

SELECT employee_id, first_name
FROM employees
WHERE department_id IN (SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 10);

SELECT employee_id, first_name
FROM employees
WHERE department_id IN (SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 10);

SELECT employee_id, first_name FROM employees WHERE department_id IN (SELECT department_id FROM employees GROUP BY department_id HAVING COUNT(*) > 10); SELECT employee_id, first_name FROM employees WHERE department_id IN (SELECT department_id FROM employees GROUP BY department_id HAVING COUNT(*) > 10); SELECT employee_id, first_name FROM employees WHERE department_id IN (SELECT department_id FROM employees GROUP BY department_id HAVING COUNT(*) > 10);SELECT employee_id, first_name FROM employees WHERE department_id = 20;

Options A, B, and C correctly use a subquery to list all employees who work in departments with more than 10 employees. Option D is incorrect because it does not use a subquery.

Q26

Multiple Choice

Which SQL code snippet demonstrates advanced use of a subquery in the SELECT clause to display the average salary of each department along with each employee's details in Oracle?

SQL Code

SELECT employee_id, first_name, department_id, salary,
(SELECT AVG(salary)
FROM employees e
WHERE e.department_id = employees.department_id) AS avg_salary
FROM employees;

SELECT employee_id, first_name, department_id, salary,
(SELECT AVG(salary)
FROM employees e
WHERE e.department_id = employees.department_id) AS avg_salary
FROM employees;

SELECT employee_id, first_name, department_id, salary,
(SELECT AVG(salary)
FROM employees e
WHERE e.department_id = employees.department_id) AS avg_salary
FROM employees;

SELECT employee_id, first_name, department_id, salary, (SELECT AVG(salary) FROM employees e WHERE e.department_id = employees.department_id) AS avg_salary FROM employees; SELECT employee_id, first_name, department_id, salary, (SELECT AVG(salary) FROM employees e WHERE e.department_id = employees.department_id) AS avg_salary FROM employees; SELECT employee_id, first_name, department_id, salary, (SELECT AVG(salary) FROM employees e WHERE e.department_id = employees.department_id) AS avg_salary FROM employees;SELECT employee_id, first_name, department_id, salary FROM employees;

Options A, B, and C correctly demonstrate advanced use of a subquery in the SELECT clause to display the average salary of each department along with each employee's details. Option D is incorrect because it does not use a subquery.

Q27

Multiple Choice

Which SQL code snippet demonstrates advanced use of a subquery to retrieve employees who earn more than the company average salary in Oracle?

SQL Code

SELECT employee_id, first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary)
FROM employees);

SELECT employee_id, first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary)
FROM employees);

SELECT employee_id, first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary)
FROM employees);

SELECT employee_id, first_name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); SELECT employee_id, first_name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); SELECT employee_id, first_name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);SELECT employee_id, first_name, salary FROM employees WHERE salary > 70000;

Options A, B, and C correctly demonstrate advanced use of a subquery to retrieve employees who earn more than the company average salary. Option D is incorrect because it does not use a subquery.

Q28

Multiple Choice

Which SQL code snippet demonstrates certification-level use of a correlated subquery to find the top earning employee in each department in Oracle?

SQL Code

SELECT department_id, employee_id, salary
FROM employees e1
WHERE salary = (SELECT MAX(salary)
FROM employees e2
WHERE e2.department_id = e1.department_id);

SELECT department_id, employee_id, salary
FROM employees e1
WHERE salary = (SELECT MAX(salary)
FROM employees e2
WHERE e2.department_id = e1.department_id);

SELECT department_id, employee_id, salary
FROM employees e1
WHERE salary = (SELECT MAX(salary)
FROM employees e2
WHERE e2.department_id = e1.department_id);

SELECT department_id, employee_id, salary FROM employees e1 WHERE salary = (SELECT MAX(salary) FROM employees e2 WHERE e2.department_id = e1.department_id); SELECT department_id, employee_id, salary FROM employees e1 WHERE salary = (SELECT MAX(salary) FROM employees e2 WHERE e2.department_id = e1.department_id); SELECT department_id, employee_id, salary FROM employees e1 WHERE salary = (SELECT MAX(salary) FROM employees e2 WHERE e2.department_id = e1.department_id);SELECT department_id, employee_id, salary FROM employees WHERE salary > 80000;

Options A, B, and C correctly demonstrate certification-level use of a correlated subquery to find the top earning employee in each department. Option D is incorrect because it does not use a correlated subquery.

Q29

Multiple Choice

Which SQL code snippet demonstrates certification-level use of a subquery to retrieve departments where the total salary exceeds $1 million in Oracle?

SQL Code

SELECT department_name
FROM departments
WHERE department_id IN (SELECT department_id
FROM employees
GROUP BY department_id
HAVING SUM(salary) > 1000000);

SELECT department_name
FROM departments
WHERE department_id IN (SELECT department_id
FROM employees
GROUP BY department_id
HAVING SUM(salary) > 1000000);

SELECT department_name
FROM departments
WHERE department_id IN (SELECT department_id
FROM employees
GROUP BY department_id
HAVING SUM(salary) > 1000000);

SELECT department_name FROM departments WHERE department_id IN (SELECT department_id FROM employees GROUP BY department_id HAVING SUM(salary) > 1000000); SELECT department_name FROM departments WHERE department_id IN (SELECT department_id FROM employees GROUP BY department_id HAVING SUM(salary) > 1000000); SELECT department_name FROM departments WHERE department_id IN (SELECT department_id FROM employees GROUP BY department_id HAVING SUM(salary) > 1000000);SELECT department_name FROM departments WHERE department_id = 30;

Options A, B, and C correctly demonstrate certification-level use of a subquery to retrieve departments where the total salary exceeds $1 million. Option D is incorrect because it does not use a subquery.

Q30

Multiple Choice

Which SQL code snippet demonstrates certification-level use of a subquery to generate a list of employees who are earning above the average salary across the entire company in Oracle?

SQL Code

SELECT employee_id, first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary)
FROM employees);

SELECT employee_id, first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary)
FROM employees);

SELECT employee_id, first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary)
FROM employees);

Options A, B, and C correctly demonstrate certification-level use of a subquery to generate a list of employees who are earning above the average salary across the entire company. Option D is incorrect because it does not use a subquery.

Oracle Database Quiz Questions