Subqueries · SQL SQL
Which of the following queries correctly uses a scalar subquery in the SELECT list to retrieve each employee's salary alongside the company-wide aver…
Practice question from Subqueries (SQL SQL), with the correct answer and a step-by-step solution.
medium · MCQ · Q-0918E1
Which of the following queries correctly uses a scalar subquery in the SELECT list to retrieve each employee's salary alongside the company-wide average salary?
- A.SELECT name, salary, (SELECT AVG(salary) FROM employees) AS avg_sal FROM employees;Correct answer
- B.SELECT name, salary, AVG(salary) FROM employees GROUP BY name, salary;
- C.SELECT name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
- D.SELECT name, salary, AVG(salary) OVER () AS avg_sal FROM employees;
Solution
Option A uses a scalar subquery (SELECT AVG(salary) FROM employees) inside the SELECT clause, which returns a single value per row. While Option D achieves the same result using a window function, Option A is the classic scalar subquery implementation.
More from Subqueries
- Which of the following best describes a correlated subquery?
- Consider the query: SELECT FROM employees WHERE departmentid IN (SELECT departmentid FROM departments WHERE location = 'New York'); Which r…
- What is the primary operational difference between using the EXISTS operator with a correlated subquery versus using IN with a subquery?
- Explain the concept of a derived table (also known as an inline view) and state where it must be placed in a SQL query.
Practise more SQL
Sign up free for adaptive practice on this chapter, with instant feedback and hints on every question.
Start practising freeFree to start · No credit card needed