Subquery SQL

Question 1

Which of the following queries correctly uses a subquery to find employees who earn more than the average salary?

- a) `SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);`

- b) `SELECT * FROM employees WHERE salary > AVG(salary);`

- c) `SELECT * FROM employees WHERE salary IN (SELECT salary FROM employees);`

- d) `SELECT * FROM employees WHERE salary > ALL (SELECT salary FROM employees);`

Answer 1

a SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

Question 2

Which subquery type returns exactly one value?

- a) Correlated subquery
- b) Non-correlated subquery
- c) Scalar subquery
- d) Inline subquery

Answer 2

c Scalar subquery

Question 3

What will the following query return?

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

- a) All departments
- b) Departments with no employees
- c) Departments with employees earning more than 5000
- d) Departments with employees earning less than 5000

Answer 3

c Departments with employees earning more than 5000

Question 4

Which of the following statements about subqueries is TRUE?
- a) A subquery can only be used in the WHERE clause.
- b) A subquery cannot return multiple rows.
- c) A subquery can be used in the FROM clause.
- d) A subquery cannot reference columns from the outer query.

Answer 4

c A subquery can be used in the FROM clause.

Question 5

What does the following correlated subquery do?**

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);

- a) Selects employees earning more than the average salary in their department

- b) Selects employees earning more than the company average salary

- c) Selects employees earning less than the average salary in their department

- d) Selects employees earning less than the company average salar

Answer 5

a Selects employees earning more than the average salary in their department