Test Your Microsoft SQL Server Subquery Knowledge with This Quiz

Progress

True / False

Subqueries can be used in SELECT, WHERE, and FROM clauses.

True False

Subqueries are versatile and can be used in various clauses of a SQL query, including SELECT, WHERE, and FROM.

True / False

Subqueries can only return a single value.

True False

Subqueries can return multiple rows and columns, not just a single value. For instance, they can be used to return a set of values that can be used in a main query.

True / False

The EXISTS operator is used to check if a subquery returns any rows.

True False

The EXISTS operator is used to determine whether a subquery returns any results. It returns TRUE if the subquery returns one or more rows.

True / False

Subqueries cannot be used in JOIN conditions.

True False

Subqueries can indeed be used in JOIN conditions. They are often used in ON clauses to filter results based on complex criteria.

True / False

A subquery in the FROM clause is known as a derived table.

True False

A subquery in the FROM clause is referred to as a derived table or inline view. It is used to provide a set of results that can be queried further.

True / False

Subqueries in the SELECT clause must return a single value.

True False

When a subquery is used in the SELECT clause, it must return a single value because it is used to compute a single value per row in the result set.

True / False

Correlated subqueries are executed once for each row processed by the outer query.

True False

Correlated subqueries are evaluated once for each row processed by the outer query. This is because they reference columns from the outer query.

True / False

In a subquery, you can use aggregate functions without a GROUP BY clause.

True False

Aggregate functions can be used in a subquery without a GROUP BY clause if they are used to calculate a single value, such as the total sum of a column.

True / False

Subqueries in the WHERE clause can be used with any comparison operator.

True False

Subqueries in the WHERE clause can be used with various comparison operators, such as =, >, <, IN, EXISTS, etc., to filter data based on the results of the subquery.

Q10

True / False

Subqueries used in the FROM clause cannot have aliases.

True False

Subqueries used in the FROM clause can have aliases. The alias allows you to reference the subquery's results in the outer query.

Q11

True / False

Subqueries that return multiple rows can be used with the IN operator.

True False

Subqueries that return multiple rows can indeed be used with the IN operator to match any of the values returned by the subquery.

Q12

True / False

Using subqueries in the SELECT clause can improve query performance.

True False

Using subqueries in the SELECT clause might not always improve performance; it often depends on the specific query and how the database engine optimizes it.

Q13

True / False

A scalar subquery returns a single value and can be used where a single value is expected.

True False

A scalar subquery returns a single value and can be used in places where a single value is expected, such as in a SELECT list or in a comparison operator.

Q14

True / False

Subqueries in the SELECT clause must be correlated with the outer query.

True False

Subqueries in the SELECT clause do not necessarily have to be correlated with the outer query; they can also be independent if they return a fixed value or set of values.

Q15

True / False

Subqueries can be used to perform complex data transformations.

True False

Subqueries can be very effective for performing complex data transformations, such as aggregations and filtering, which might be challenging to achieve with simple queries.

Q16

Multiple Choice

Which of the following SQL snippets correctly uses a subquery to find employees with salaries higher than the average salary?

SQL Code

SELECT EmployeeName
FROM Employees
WHERE Salary > (
 SELECT AVG(Salary)
 FROM Employees
);

SELECT EmployeeName FROM Employees WHERE Salary > ( SELECT AVG(Salary) FROM Employees ); SELECT EmployeeName FROM Employees WHERE Salary IN ( SELECT AVG(Salary) FROM Employees ); SELECT EmployeeName FROM Employees WHERE Salary = ( SELECT AVG(Salary) FROM Employees );SELECT EmployeeName FROM Employees WHERE Salary NOT IN ( SELECT AVG(Salary) FROM Employees );

This snippet uses a scalar subquery to compare each employee's salary against the average salary calculated from the subquery.

Q17

Multiple Choice

Which SQL snippet uses a subquery to find departments with employees earning above a specified threshold?

SQL Code

SELECT DepartmentName
FROM Departments
WHERE DepartmentID IN (
 SELECT DepartmentID
 FROM Employees
 WHERE Salary > 70000
);

SELECT DepartmentName FROM Departments WHERE DepartmentID IN ( SELECT DepartmentID FROM Employees WHERE Salary > 70000 ); SELECT DepartmentName FROM Departments WHERE DepartmentID = ( SELECT DepartmentID FROM Employees WHERE Salary > 70000 ); SELECT DepartmentName FROM Departments WHERE DepartmentID NOT IN ( SELECT DepartmentID FROM Employees WHERE Salary < 70000 );SELECT DepartmentName FROM Departments WHERE DepartmentID IN ( SELECT DepartmentID FROM Employees GROUP BY DepartmentID HAVING MAX(Salary) > 70000 );

This snippet uses a subquery to filter departments based on whether any employee earns more than $70,000.

Q18

Multiple Choice

Which SQL code snippet demonstrates a subquery used in the ORDER BY clause?

SQL Code

SELECT EmployeeName
FROM Employees
ORDER BY (
 SELECT AVG(Salary)
 FROM Employees
);

SELECT EmployeeName FROM Employees ORDER BY ( SELECT AVG(Salary) FROM Employees ); SELECT EmployeeName FROM Employees ORDER BY Salary DESC; SELECT EmployeeName FROM Employees ORDER BY ( SELECT MIN(Salary) FROM Employees );SELECT EmployeeName FROM Employees ORDER BY DepartmentID;

This snippet demonstrates using a subquery in the ORDER BY clause to sort employees based on the average salary.

Q19

Multiple Choice

Which SQL snippet uses a subquery in the WHERE clause to filter employees by department average salary?

SQL Code

SELECT EmployeeName
FROM Employees e1
WHERE Salary > (
 SELECT AVG(Salary)
 FROM Employees e2
 WHERE e1.DepartmentID = e2.DepartmentID
);

SELECT EmployeeName FROM Employees e1 WHERE Salary > ( SELECT AVG(Salary) FROM Employees e2 WHERE e1.DepartmentID = e2.DepartmentID ); SELECT EmployeeName FROM Employees WHERE Salary > ( SELECT AVG(Salary) FROM Employees ); SELECT EmployeeName FROM Employees WHERE Salary IN ( SELECT AVG(Salary) FROM Employees );SELECT EmployeeName FROM Employees WHERE Salary > ( SELECT MIN(Salary) FROM Employees WHERE DepartmentID = 1 );

This snippet uses a correlated subquery in the WHERE clause to filter employees whose salaries are greater than the average salary of their department.

Q20

Multiple Choice

Which SQL snippet correctly uses a subquery to find employees who earn more than the average salary of their department, considering the subquery is correlated?

SQL Code

SELECT EmployeeName
FROM Employees e1
WHERE Salary > (
 SELECT AVG(Salary)
 FROM Employees e2
 WHERE e1.DepartmentID = e2.DepartmentID
);

SELECT EmployeeName FROM Employees e1 WHERE Salary > ( SELECT AVG(Salary) FROM Employees e2 WHERE e1.DepartmentID = e2.DepartmentID ); SELECT EmployeeName FROM Employees WHERE Salary > ( SELECT AVG(Salary) FROM Employees ); SELECT EmployeeName FROM Employees WHERE Salary IN ( SELECT AVG(Salary) FROM Employees GROUP BY DepartmentID );SELECT EmployeeName FROM Employees WHERE Salary < ( SELECT AVG(Salary) FROM Employees );

This snippet correctly uses a correlated subquery to compare each employee's salary to the average salary within their own department.

Q21

Multiple Choice

Which SQL snippet uses a subquery to list employees who have a higher salary than the average salary of employees in their department?

SQL Code

SELECT EmployeeName
FROM Employees e1
WHERE Salary > (
 SELECT AVG(Salary)
 FROM Employees e2
 WHERE e1.DepartmentID = e2.DepartmentID
);

SELECT EmployeeName FROM Employees e1 WHERE Salary > ( SELECT AVG(Salary) FROM Employees e2 WHERE e1.DepartmentID = e2.DepartmentID ); SELECT EmployeeName FROM Employees WHERE Salary > ( SELECT MAX(Salary) FROM Employees ); SELECT EmployeeName FROM Employees WHERE Salary < ( SELECT MIN(Salary) FROM Employees );SELECT EmployeeName FROM Employees WHERE Salary > ( SELECT AVG(Salary) FROM Employees GROUP BY DepartmentID );

The subquery calculates the average salary per department, and the outer query finds employees with salaries higher than this average.

Q22

Multiple Choice

Which SQL snippet demonstrates the use of a subquery to find employees in departments that have more than 10 employees?

SQL Code

SELECT EmployeeName
FROM Employees
WHERE DepartmentID IN (
 SELECT DepartmentID
 FROM Employees
 GROUP BY DepartmentID
 HAVING COUNT(*) > 10
);

SELECT EmployeeName FROM Employees WHERE DepartmentID IN ( SELECT DepartmentID FROM Employees GROUP BY DepartmentID HAVING COUNT(*) > 10 ); SELECT EmployeeName FROM Employees WHERE DepartmentID = ( SELECT DepartmentID FROM Employees GROUP BY DepartmentID HAVING COUNT(*) > 10 ); SELECT EmployeeName FROM Employees WHERE DepartmentID IN ( SELECT DepartmentID FROM Employees GROUP BY DepartmentID HAVING COUNT(*) < 10 );SELECT EmployeeName FROM Employees WHERE DepartmentID NOT IN ( SELECT DepartmentID FROM Employees GROUP BY DepartmentID HAVING COUNT(*) > 10 );

This snippet uses a subquery to identify departments with more than 10 employees, and then selects employees from these departments.

Q23

Multiple Choice

Which SQL snippet uses a subquery to find employees who do not have the lowest salary in their department?

SQL Code

SELECT EmployeeName
FROM Employees e1
WHERE Salary > (
 SELECT MIN(Salary)
 FROM Employees e2
 WHERE e1.DepartmentID = e2.DepartmentID
);

SELECT EmployeeName FROM Employees e1 WHERE Salary > ( SELECT MIN(Salary) FROM Employees e2 WHERE e1.DepartmentID = e2.DepartmentID ); SELECT EmployeeName FROM Employees WHERE Salary = ( SELECT MIN(Salary) FROM Employees ); SELECT EmployeeName FROM Employees WHERE Salary IN ( SELECT MIN(Salary) FROM Employees GROUP BY DepartmentID );SELECT EmployeeName FROM Employees WHERE Salary NOT IN ( SELECT MIN(Salary) FROM Employees );

This snippet identifies employees who earn more than the minimum salary in their own department by using a correlated subquery.

Q24

Multiple Choice

Which SQL snippet uses a subquery in the WHERE clause to exclude departments without any employees earning more than $50,000?

SQL Code

SELECT DepartmentName
FROM Departments
WHERE DepartmentID NOT IN (
 SELECT DepartmentID
 FROM Employees
 WHERE Salary > 50000
);

SELECT DepartmentName FROM Departments WHERE DepartmentID NOT IN ( SELECT DepartmentID FROM Employees WHERE Salary > 50000 ); SELECT DepartmentName FROM Departments WHERE DepartmentID IN ( SELECT DepartmentID FROM Employees WHERE Salary > 50000 ); SELECT DepartmentName FROM Departments WHERE DepartmentID = ( SELECT DepartmentID FROM Employees WHERE Salary > 50000 );SELECT DepartmentName FROM Departments WHERE DepartmentID IN ( SELECT DepartmentID FROM Employees GROUP BY DepartmentID HAVING MAX(Salary) > 50000 );

This snippet excludes departments where no employees earn more than $50,000 by using a subquery in the WHERE clause with the NOT IN operator.

Q25

Multiple Choice

Which SQL snippet uses a subquery to identify employees who have the highest salary within each department?

SQL Code

SELECT EmployeeName
FROM Employees e1
WHERE Salary = (
 SELECT MAX(Salary)
 FROM Employees e2
 WHERE e1.DepartmentID = e2.DepartmentID
);

SELECT EmployeeName FROM Employees e1 WHERE Salary = ( SELECT MAX(Salary) FROM Employees e2 WHERE e1.DepartmentID = e2.DepartmentID ); SELECT EmployeeName FROM Employees WHERE Salary = ( SELECT MAX(Salary) FROM Employees ); SELECT EmployeeName FROM Employees WHERE Salary IN ( SELECT MAX(Salary) FROM Employees GROUP BY DepartmentID );SELECT EmployeeName FROM Employees WHERE Salary = ( SELECT MIN(Salary) FROM Employees );

This snippet finds employees with the highest salary within each department by using a correlated subquery.

Q26

Multiple Choice

Which SQL snippet correctly uses a subquery to find employees with a salary less than the average salary of employees in a specific department?

SQL Code

SELECT EmployeeName
FROM Employees e1
WHERE Salary < (
 SELECT AVG(Salary)
 FROM Employees e2
 WHERE e1.DepartmentID = e2.DepartmentID
);

SELECT EmployeeName FROM Employees e1 WHERE Salary < ( SELECT AVG(Salary) FROM Employees e2 WHERE e1.DepartmentID = e2.DepartmentID ); SELECT EmployeeName FROM Employees WHERE Salary < ( SELECT AVG(Salary) FROM Employees ); SELECT EmployeeName FROM Employees WHERE Salary IN ( SELECT AVG(Salary) FROM Employees GROUP BY DepartmentID );SELECT EmployeeName FROM Employees WHERE Salary < ( SELECT MAX(Salary) FROM Employees );

This snippet uses a correlated subquery to find employees whose salary is below the average salary within their department.

Q27

Multiple Choice

Which SQL snippet demonstrates the use of a subquery to select employees whose salaries are greater than the average salary of all employees?

SQL Code

SELECT EmployeeName
FROM Employees
WHERE Salary > (
 SELECT AVG(Salary)
 FROM Employees
);

SELECT EmployeeName FROM Employees WHERE Salary > ( SELECT AVG(Salary) FROM Employees ); SELECT EmployeeName FROM Employees WHERE Salary < ( SELECT AVG(Salary) FROM Employees ); SELECT EmployeeName FROM Employees WHERE Salary = ( SELECT AVG(Salary) FROM Employees );SELECT EmployeeName FROM Employees WHERE Salary IN ( SELECT AVG(Salary) FROM Employees );

This snippet selects employees whose salaries exceed the average salary of all employees by using a subquery.

Q28

Multiple Choice

Which SQL snippet uses a subquery to find employees who work in departments with more than 5 employees?

SQL Code

SELECT EmployeeName
FROM Employees
WHERE DepartmentID IN (
 SELECT DepartmentID
 FROM Employees
 GROUP BY DepartmentID
 HAVING COUNT(*) > 5
);

SELECT EmployeeName FROM Employees WHERE DepartmentID IN ( SELECT DepartmentID FROM Employees GROUP BY DepartmentID HAVING COUNT(*) > 5 ); SELECT EmployeeName FROM Employees WHERE DepartmentID IN ( SELECT DepartmentID FROM Employees GROUP BY DepartmentID HAVING COUNT(*) < 5 ); SELECT EmployeeName FROM Employees WHERE DepartmentID NOT IN ( SELECT DepartmentID FROM Employees GROUP BY DepartmentID HAVING COUNT(*) > 5 );SELECT EmployeeName FROM Employees WHERE DepartmentID = ( SELECT DepartmentID FROM Employees GROUP BY DepartmentID HAVING COUNT(*) > 5 );

This snippet finds employees who work in departments where the employee count exceeds 5 by using a subquery.

Q29

Multiple Choice

Which SQL snippet demonstrates a subquery to list departments with a maximum salary greater than $60,000?

SQL Code

SELECT DepartmentName
FROM Departments
WHERE DepartmentID IN (
 SELECT DepartmentID
 FROM Employees
 GROUP BY DepartmentID
 HAVING MAX(Salary) > 60000
);

SELECT DepartmentName FROM Departments WHERE DepartmentID IN ( SELECT DepartmentID FROM Employees GROUP BY DepartmentID HAVING MAX(Salary) > 60000 ); SELECT DepartmentName FROM Departments WHERE DepartmentID NOT IN ( SELECT DepartmentID FROM Employees GROUP BY DepartmentID HAVING MAX(Salary) > 60000 ); SELECT DepartmentName FROM Departments WHERE DepartmentID = ( SELECT DepartmentID FROM Employees GROUP BY DepartmentID HAVING MAX(Salary) > 60000 );SELECT DepartmentName FROM Departments WHERE DepartmentID IN ( SELECT DepartmentID FROM Employees GROUP BY DepartmentID HAVING MIN(Salary) > 60000 );

This snippet uses a subquery to find departments where the maximum salary exceeds $60,000.

Q30

Multiple Choice

Which SQL snippet correctly uses a subquery to find employees with a salary less than the average salary of employees in their own department?

SQL Code

SELECT EmployeeName
FROM Employees e1
WHERE Salary < (
 SELECT AVG(Salary)
 FROM Employees e2
 WHERE e1.DepartmentID = e2.DepartmentID
);

SELECT EmployeeName FROM Employees e1 WHERE Salary < ( SELECT AVG(Salary) FROM Employees e2 WHERE e1.DepartmentID = e2.DepartmentID ); SELECT EmployeeName FROM Employees WHERE Salary < ( SELECT MAX(Salary) FROM Employees ); SELECT EmployeeName FROM Employees WHERE Salary = ( SELECT AVG(Salary) FROM Employees GROUP BY DepartmentID );SELECT EmployeeName FROM Employees WHERE Salary < ( SELECT AVG(Salary) FROM Employees );

This snippet uses a correlated subquery to find employees with a salary lower than the average salary of their respective department.

Q31

Single Choice

Which SQL code snippet demonstrates a valid use of a subquery in the WHERE clause?

SQL Code

SELECT *
FROM Employees
WHERE DepartmentID = (
 SELECT DepartmentID
 FROM Departments
 WHERE DepartmentName = 'Sales'
);

SELECT * FROM Employees WHERE DepartmentID = ( SELECT DepartmentID FROM Departments WHERE DepartmentName = 'Sales' ); SELECT * FROM Employees WHERE DepartmentID IN ( SELECT DepartmentID FROM Departments WHERE DepartmentName = 'Sales' ); SELECT * FROM Employees WHERE DepartmentID = ( SELECT DepartmentName FROM Departments WHERE DepartmentID = 1 );SELECT * FROM Employees WHERE DepartmentID NOT IN ( SELECT DepartmentID FROM Departments WHERE DepartmentName = 'Sales' );

This snippet correctly uses a subquery in the WHERE clause to filter employees based on the department ID of the 'Sales' department.

Q32

Single Choice

Which of the following SQL snippets correctly demonstrates a scalar subquery?

SQL Code

SELECT EmployeeName
FROM Employees
WHERE Salary = (
 SELECT MAX(Salary)
 FROM Employees
);

SELECT EmployeeName FROM Employees WHERE Salary = ( SELECT MAX(Salary) FROM Employees ); SELECT EmployeeName FROM Employees WHERE Salary IN ( SELECT MAX(Salary) FROM Employees ); SELECT EmployeeName FROM Employees WHERE Salary = ( SELECT EmployeeName FROM Employees WHERE EmployeeID = 1 );SELECT EmployeeName FROM Employees WHERE Salary NOT IN ( SELECT MAX(Salary) FROM Employees );

This snippet demonstrates a scalar subquery that returns the maximum salary and compares it to the salary of each employee.

Q33

Single Choice

Which SQL code snippet shows a subquery used as a derived table in the FROM clause?

SQL Code

SELECT DepartmentName, AVG(Salary)
FROM (
 SELECT DepartmentName, Salary
 FROM Employees
) AS EmployeeData
GROUP BY DepartmentName;

SELECT DepartmentName, AVG(Salary) FROM ( SELECT DepartmentName, Salary FROM Employees ) AS EmployeeData GROUP BY DepartmentName; SELECT DepartmentName, AVG(Salary) FROM Employees WHERE Salary IN ( SELECT AVG(Salary) FROM Employees GROUP BY DepartmentName ); SELECT DepartmentName, MAX(Salary) FROM Employees WHERE DepartmentID = ( SELECT DepartmentID FROM Departments WHERE DepartmentName = 'Sales' );SELECT DepartmentName, SUM(Salary) FROM Employees GROUP BY DepartmentName;

This snippet uses a subquery as a derived table to calculate the average salary for each department.

Q34

Single Choice

What does the following SQL snippet demonstrate about correlated subqueries?

SQL Code

SELECT EmployeeName
FROM Employees e
WHERE Salary > (
 SELECT AVG(Salary)
 FROM Employees
 WHERE DepartmentID = e.DepartmentID
);

The subquery is correlated with the outer query by referencing a column from the outer query. The subquery is independent and can be executed on its own. The subquery is used to filter results based on a fixed value.The subquery returns multiple rows and is used with the EXISTS operator.

This snippet demonstrates a correlated subquery, where the subquery references the outer query's DepartmentID column.

Q35

Single Choice

Which SQL code snippet demonstrates the use of a subquery in the SELECT clause to calculate the average salary?

SQL Code

SELECT EmployeeName,
 (SELECT AVG(Salary)
 FROM Employees) AS AvgSalary
FROM Employees;

SELECT EmployeeName, (SELECT AVG(Salary) FROM Employees) AS AvgSalary FROM Employees; SELECT EmployeeName, AVG(Salary) FROM Employees WHERE Salary = ( SELECT AVG(Salary) FROM Employees ); SELECT EmployeeName FROM Employees WHERE Salary > ( SELECT AVG(Salary) FROM Employees );SELECT EmployeeName, (SELECT MAX(Salary) FROM Employees) AS MaxSalary FROM Employees;

This snippet demonstrates the use of a subquery in the SELECT clause to compute the average salary and include it as a column in the result set.

Q36

Single Choice

Which of the following is true about using subqueries with the EXISTS operator?

EXISTS returns TRUE if the subquery returns any rows. EXISTS returns TRUE if the subquery returns exactly one row. EXISTS returns FALSE if the subquery returns any rows.EXISTS returns TRUE if the subquery returns no rows.

The EXISTS operator returns TRUE if the subquery returns one or more rows. It does not care about the actual values returned by the subquery.

Q37

Single Choice

Which SQL snippet demonstrates using a subquery to filter records based on the results of another subquery?

SQL Code

SELECT EmployeeName
FROM Employees
WHERE DepartmentID IN (
 SELECT DepartmentID
 FROM Employees
 WHERE Salary > (
 SELECT AVG(Salary)
 FROM Employees
 )
);

SELECT EmployeeName FROM Employees WHERE DepartmentID IN ( SELECT DepartmentID FROM Employees WHERE Salary > ( SELECT AVG(Salary) FROM Employees ) ); SELECT EmployeeName FROM Employees WHERE DepartmentID = ( SELECT DepartmentID FROM Employees WHERE Salary > ( SELECT AVG(Salary) FROM Employees ) ); SELECT EmployeeName FROM Employees WHERE DepartmentID IN ( SELECT DepartmentID FROM Employees WHERE Salary > 50000 );SELECT EmployeeName FROM Employees WHERE DepartmentID NOT IN ( SELECT DepartmentID FROM Employees WHERE Salary < ( SELECT AVG(Salary) FROM Employees ) );

This snippet demonstrates using nested subqueries to filter employees based on whether their department's average salary is less than a certain value.

Q38

Single Choice

Which SQL snippet correctly uses a subquery to find employees with salaries higher than the average salary of their department?

SQL Code

SELECT EmployeeName
FROM Employees e1
WHERE Salary > (
 SELECT AVG(Salary)
 FROM Employees e2
 WHERE e1.DepartmentID = e2.DepartmentID
);

SELECT EmployeeName FROM Employees e1 WHERE Salary > ( SELECT AVG(Salary) FROM Employees e2 WHERE e1.DepartmentID = e2.DepartmentID ); SELECT EmployeeName FROM Employees WHERE Salary > ( SELECT MAX(Salary) FROM Employees ); SELECT EmployeeName FROM Employees WHERE Salary IN ( SELECT AVG(Salary) FROM Employees GROUP BY DepartmentID );SELECT EmployeeName FROM Employees WHERE Salary > ( SELECT MIN(Salary) FROM Employees WHERE DepartmentID = 1 );

This snippet correctly uses a correlated subquery to find employees whose salaries are greater than the average salary of their own department.

Q39

Single Choice

Which SQL snippet uses a subquery to list departments with at least one employee earning more than $60,000?

SQL Code

SELECT DepartmentName
FROM Departments
WHERE DepartmentID IN (
 SELECT DepartmentID
 FROM Employees
 WHERE Salary > 60000
);

SELECT DepartmentName FROM Departments WHERE DepartmentID IN ( SELECT DepartmentID FROM Employees WHERE Salary > 60000 ); SELECT DepartmentName FROM Departments WHERE DepartmentID = ( SELECT DepartmentID FROM Employees WHERE Salary > 60000 ); SELECT DepartmentName FROM Departments WHERE DepartmentID NOT IN ( SELECT DepartmentID FROM Employees WHERE Salary < 60000 );SELECT DepartmentName FROM Departments WHERE DepartmentID IN ( SELECT DepartmentID FROM Employees GROUP BY DepartmentID HAVING MAX(Salary) > 60000 );

This snippet lists departments that have at least one employee earning more than $60,000 by using a subquery to filter departments based on employee salaries.

Q40

Single Choice

Which SQL code snippet demonstrates a subquery used in the HAVING clause?

SQL Code

SELECT DepartmentID, COUNT(*) AS NumEmployees
FROM Employees
GROUP BY DepartmentID
HAVING COUNT(*) > (
 SELECT AVG(EmployeeCount)
 FROM (
 SELECT DepartmentID, COUNT(*) AS EmployeeCount
 FROM Employees
 GROUP BY DepartmentID
 ) AS DeptCounts
);

SELECT DepartmentID, COUNT(*) AS NumEmployees FROM Employees GROUP BY DepartmentID HAVING COUNT(*) > ( SELECT AVG(EmployeeCount) FROM ( SELECT DepartmentID, COUNT(*) AS EmployeeCount FROM Employees GROUP BY DepartmentID ) AS DeptCounts ); SELECT DepartmentID, COUNT(*) AS NumEmployees FROM Employees GROUP BY DepartmentID HAVING COUNT(*) > ( SELECT MAX(EmployeeCount) FROM Employees ); SELECT DepartmentID, COUNT(*) AS NumEmployees FROM Employees WHERE DepartmentID IN ( SELECT DepartmentID FROM Employees GROUP BY DepartmentID HAVING COUNT(*) > 10 );SELECT DepartmentID, COUNT(*) AS NumEmployees FROM Employees GROUP BY DepartmentID HAVING COUNT(*) = ( SELECT COUNT(*) FROM Employees WHERE DepartmentID = 1 );

This snippet uses a subquery in the HAVING clause to filter departments based on the count of employees compared to the average employee count across departments.

Q41

Single Choice

Which SQL snippet uses a subquery to identify employees who do not belong to any department listed in a given subquery?

SQL Code

SELECT EmployeeName
FROM Employees
WHERE DepartmentID NOT IN (
 SELECT DepartmentID
 FROM Departments
 WHERE DepartmentName LIKE '%Sales%'
);

SELECT EmployeeName FROM Employees WHERE DepartmentID NOT IN ( SELECT DepartmentID FROM Departments WHERE DepartmentName LIKE '%Sales%' ); SELECT EmployeeName FROM Employees WHERE DepartmentID NOT IN ( SELECT DepartmentID FROM Departments WHERE DepartmentID = 1 ); SELECT EmployeeName FROM Employees WHERE DepartmentID IN ( SELECT DepartmentID FROM Departments WHERE DepartmentName LIKE '%Sales%' );SELECT EmployeeName FROM Employees WHERE DepartmentID = ( SELECT DepartmentID FROM Departments WHERE DepartmentName LIKE '%Sales%' );

This snippet identifies employees who do not belong to any department where the department name includes 'Sales', using a subquery with the NOT IN operator.

Q42

True / False

Subqueries in the WHERE clause can return multiple columns if used with certain operators.

True False

In general, subqueries in the WHERE clause should return a single column. However, if used with operators like EXISTS, they can be more flexible.

Q43

True / False

Subqueries can be used with aggregate functions to perform calculations on grouped data.

True False

Subqueries can be effectively used with aggregate functions to perform calculations on grouped data, such as calculating averages or totals for subsets of data.

Q44

Single Choice

Which SQL code snippet demonstrates using a subquery to filter records based on aggregated data?

SQL Code

SELECT DepartmentName
FROM Departments
WHERE DepartmentID IN (
 SELECT DepartmentID
 FROM Employees
 GROUP BY DepartmentID
 HAVING AVG(Salary) > 50000
);

SELECT DepartmentName FROM Departments WHERE DepartmentID IN ( SELECT DepartmentID FROM Employees GROUP BY DepartmentID HAVING AVG(Salary) > 50000 ); SELECT DepartmentName FROM Departments WHERE DepartmentID = ( SELECT AVG(Salary) FROM Employees ); SELECT DepartmentName FROM Departments WHERE DepartmentID IN ( SELECT DepartmentID FROM Employees WHERE Salary > 50000 );SELECT DepartmentName FROM Departments WHERE DepartmentID = ( SELECT DepartmentID FROM Employees GROUP BY DepartmentID HAVING COUNT(*) > 10 );

This snippet uses a subquery to filter departments based on the average salary of employees, only including departments where the average salary exceeds 50,000.

Q45

Single Choice

Which SQL code snippet uses a subquery to find employees who have the same manager as a specific employee?

SQL Code

SELECT EmployeeName
FROM Employees e1
WHERE ManagerID = (
 SELECT ManagerID
 FROM Employees e2
 WHERE EmployeeName = 'John Doe'
);

SELECT EmployeeName FROM Employees e1 WHERE ManagerID = ( SELECT ManagerID FROM Employees e2 WHERE EmployeeName = 'John Doe' ); SELECT EmployeeName FROM Employees WHERE ManagerID IN ( SELECT ManagerID FROM Employees WHERE EmployeeName = 'John Doe' ); SELECT EmployeeName FROM Employees WHERE ManagerID = ( SELECT EmployeeName FROM Employees WHERE EmployeeID = 1 );SELECT EmployeeName FROM Employees WHERE ManagerID NOT IN ( SELECT ManagerID FROM Employees WHERE EmployeeName = 'John Doe' );

This snippet demonstrates using a subquery to find employees who have the same manager as 'John Doe' by comparing ManagerID values.

Microsoft SQL Server Database Quiz Questions