Test Your Microsoft SQL Server HAVING Clause Knowledge with This Quiz

Progress

True / False

The HAVING clause is used to filter records after the GROUP BY clause.

True False

True. The HAVING clause is used to filter groups of records created by the GROUP BY clause, whereas the WHERE clause filters individual records before grouping.

True / False

The HAVING clause can be used without a GROUP BY clause.

True False

False. The HAVING clause is intended to filter results of a GROUP BY clause and cannot be used effectively without it.

True / False

The HAVING clause can only use aggregate functions.

True False

True. The HAVING clause is used to filter records based on aggregate functions such as COUNT(), SUM(), AVG(), etc.

True / False

The WHERE clause can be used to filter records after the GROUP BY clause has been applied.

True False

False. The WHERE clause filters records before grouping. The HAVING clause is used to filter after grouping.

True / False

You can use the HAVING clause to filter groups that have a specific number of records.

True False

True. The HAVING clause can be used to filter groups based on the number of records using aggregate functions like COUNT().

True / False

The HAVING clause can be used to filter rows based on specific column values.

True False

False. The HAVING clause filters based on aggregate values, not individual column values. Individual values should be filtered using the WHERE clause.

True / False

The HAVING clause can be used to filter data from multiple tables in a JOIN operation.

True False

True. The HAVING clause can filter data after performing a JOIN operation if a GROUP BY clause is used.

True / False

The HAVING clause can only be used with SELECT statements.

True False

True. The HAVING clause is only applicable in SELECT statements where a GROUP BY clause is used.

True / False

You can use the HAVING clause to filter out records where the aggregate function result is greater than a certain value.

True False

True. The HAVING clause can filter groups based on the result of aggregate functions, such as keeping groups where the result is greater than a specified value.

Q10

True / False

The HAVING clause can be used to filter records before the aggregation process.

True False

False. The HAVING clause is used after the aggregation process, whereas the WHERE clause is used before.

Q11

True / False

The HAVING clause is more efficient than the WHERE clause when dealing with aggregated data.

True False

False. The HAVING clause is used for filtering after aggregation, while the WHERE clause is used for filtering before aggregation. Efficiency depends on the specific query and data.

Q12

True / False

The HAVING clause can be used to filter groups based on non-aggregate expressions.

True False

False. The HAVING clause is specifically designed for filtering based on aggregate functions, not on non-aggregate expressions.

Q13

True / False

The HAVING clause can be used in conjunction with the DISTINCT keyword.

True False

True. The HAVING clause can be used with DISTINCT to filter distinct groups based on aggregate results.

Q14

True / False

The HAVING clause cannot be used with subqueries.

True False

False. The HAVING clause can be used with subqueries, typically within the main SELECT statement to filter grouped results from a subquery.

Q15

True / False

The HAVING clause is always used after the GROUP BY clause in a SQL query.

True False

True. The HAVING clause is used after the GROUP BY clause to filter the results of the grouping operation.

Q16

Single Choice

Which SQL query filters groups where the average salary is greater than 50000?

SQL Code

SELECT DepartmentID, AVG(Salary)
FROM Employees
GROUP BY DepartmentID
HAVING AVG(Salary) > 50000;

SELECT DepartmentID, AVG(Salary) FROM Employees GROUP BY DepartmentID HAVING AVG(Salary) > 50000; SELECT DepartmentID, AVG(Salary) FROM Employees WHERE AVG(Salary) > 50000; SELECT DepartmentID, AVG(Salary) FROM Employees HAVING AVG(Salary) > 50000;SELECT DepartmentID, AVG(Salary) FROM Employees GROUP BY DepartmentID WHERE AVG(Salary) > 50000;

This query groups employees by DepartmentID and filters the groups to include only those where the average salary is greater than 50000.

Q17

Single Choice

Which SQL query filters groups where the total sales amount is less than 100000?

SQL Code

SELECT SalesRepID, SUM(SaleAmount)
FROM Sales
GROUP BY SalesRepID
HAVING SUM(SaleAmount) < 100000;

SELECT SalesRepID, SUM(SaleAmount) FROM Sales GROUP BY SalesRepID HAVING SUM(SaleAmount) < 100000; SELECT SalesRepID, SUM(SaleAmount) FROM Sales WHERE SUM(SaleAmount) < 100000; SELECT SalesRepID, SUM(SaleAmount) FROM Sales HAVING SUM(SaleAmount) < 100000;SELECT SalesRepID, SUM(SaleAmount) FROM Sales GROUP BY SalesRepID WHERE SUM(SaleAmount) < 100000;

This query groups sales data by SalesRepID and filters the groups to include only those where the total sales amount is less than 100000.

Q18

Single Choice

Which SQL query filters groups with more than 10 orders?

SQL Code

SELECT CustomerID, COUNT(OrderID)
FROM Orders
GROUP BY CustomerID
HAVING COUNT(OrderID) > 10;

SELECT CustomerID, COUNT(OrderID) FROM Orders GROUP BY CustomerID HAVING COUNT(OrderID) > 10; SELECT CustomerID, COUNT(OrderID) FROM Orders WHERE COUNT(OrderID) > 10; SELECT CustomerID, COUNT(OrderID) FROM Orders HAVING COUNT(OrderID) > 10;SELECT CustomerID, COUNT(OrderID) FROM Orders GROUP BY CustomerID WHERE COUNT(OrderID) > 10;

This query groups orders by CustomerID and filters the groups to include only those with more than 10 orders.

Q19

Single Choice

Which SQL query filters groups where the maximum sale amount is equal to 5000?

SQL Code

SELECT ProductID, MAX(SaleAmount)
FROM Sales
GROUP BY ProductID
HAVING MAX(SaleAmount) = 5000;

SELECT ProductID, MAX(SaleAmount) FROM Sales GROUP BY ProductID HAVING MAX(SaleAmount) = 5000; SELECT ProductID, MAX(SaleAmount) FROM Sales WHERE MAX(SaleAmount) = 5000; SELECT ProductID, MAX(SaleAmount) FROM Sales HAVING MAX(SaleAmount) = 5000;SELECT ProductID, MAX(SaleAmount) FROM Sales GROUP BY ProductID WHERE MAX(SaleAmount) = 5000;

This query groups sales data by ProductID and filters the groups to include only those where the maximum sale amount is equal to 5000.

Q20

Single Choice

Which SQL query filters groups where the average quantity is between 10 and 50?

SQL Code

SELECT ProductID, AVG(Quantity)
FROM Sales
GROUP BY ProductID
HAVING AVG(Quantity) BETWEEN 10 AND 50;

SELECT ProductID, AVG(Quantity) FROM Sales GROUP BY ProductID HAVING AVG(Quantity) BETWEEN 10 AND 50; SELECT ProductID, AVG(Quantity) FROM Sales WHERE AVG(Quantity) BETWEEN 10 AND 50; SELECT ProductID, AVG(Quantity) FROM Sales HAVING AVG(Quantity) BETWEEN 10 AND 50;SELECT ProductID, AVG(Quantity) FROM Sales GROUP BY ProductID WHERE AVG(Quantity) BETWEEN 10 AND 50;

This query groups sales data by ProductID and filters the groups to include only those where the average quantity is between 10 and 50.

Q21

Single Choice

Which SQL query filters groups where the sum of sales is not equal to 20000?

SQL Code

SELECT Region, SUM(SaleAmount)
FROM Sales
GROUP BY Region
HAVING SUM(SaleAmount) <> 20000;

SELECT Region, SUM(SaleAmount) FROM Sales GROUP BY Region HAVING SUM(SaleAmount) <> 20000; SELECT Region, SUM(SaleAmount) FROM Sales WHERE SUM(SaleAmount) <> 20000; SELECT Region, SUM(SaleAmount) FROM Sales HAVING SUM(SaleAmount) <> 20000;SELECT Region, SUM(SaleAmount) FROM Sales GROUP BY Region WHERE SUM(SaleAmount) <> 20000;

This query groups sales data by Region and filters the groups to include only those where the sum of sales is not equal to 20000.

Q22

Single Choice

Which SQL query filters groups where the total number of products is greater than 100?

SQL Code

SELECT CategoryID, COUNT(ProductID)
FROM Products
GROUP BY CategoryID
HAVING COUNT(ProductID) > 100;

SELECT CategoryID, COUNT(ProductID) FROM Products GROUP BY CategoryID HAVING COUNT(ProductID) > 100; SELECT CategoryID, COUNT(ProductID) FROM Products WHERE COUNT(ProductID) > 100; SELECT CategoryID, COUNT(ProductID) FROM Products HAVING COUNT(ProductID) > 100;SELECT CategoryID, COUNT(ProductID) FROM Products GROUP BY CategoryID WHERE COUNT(ProductID) > 100;

This query groups products by CategoryID and filters the groups to include only those where the total number of products is greater than 100.

Q23

Single Choice

Which SQL query filters groups where the minimum price is less than 50?

SQL Code

SELECT ProductID, MIN(Price)
FROM Products
GROUP BY ProductID
HAVING MIN(Price) < 50;

SELECT ProductID, MIN(Price) FROM Products GROUP BY ProductID HAVING MIN(Price) < 50; SELECT ProductID, MIN(Price) FROM Products WHERE MIN(Price) < 50; SELECT ProductID, MIN(Price) FROM Products HAVING MIN(Price) < 50;SELECT ProductID, MIN(Price) FROM Products GROUP BY ProductID WHERE MIN(Price) < 50;

This query groups products by ProductID and filters the groups to include only those where the minimum price is less than 50.

Q24

Single Choice

Which SQL query filters groups where the total order amount is exactly 1000?

SQL Code

SELECT CustomerID, SUM(OrderAmount)
FROM Orders
GROUP BY CustomerID
HAVING SUM(OrderAmount) = 1000;

SELECT CustomerID, SUM(OrderAmount) FROM Orders GROUP BY CustomerID HAVING SUM(OrderAmount) = 1000; SELECT CustomerID, SUM(OrderAmount) FROM Orders WHERE SUM(OrderAmount) = 1000; SELECT CustomerID, SUM(OrderAmount) FROM Orders HAVING SUM(OrderAmount) = 1000;SELECT CustomerID, SUM(OrderAmount) FROM Orders GROUP BY CustomerID WHERE SUM(OrderAmount) = 1000;

This query groups orders by CustomerID and filters the groups to include only those where the total order amount is exactly 1000.

Q25

Single Choice

Which SQL query filters groups where the average quantity is greater than or equal to 20?

SQL Code

SELECT ProductID, AVG(Quantity)
FROM Sales
GROUP BY ProductID
HAVING AVG(Quantity) >= 20;

SELECT ProductID, AVG(Quantity) FROM Sales GROUP BY ProductID HAVING AVG(Quantity) >= 20; SELECT ProductID, AVG(Quantity) FROM Sales WHERE AVG(Quantity) >= 20; SELECT ProductID, AVG(Quantity) FROM Sales HAVING AVG(Quantity) >= 20;SELECT ProductID, AVG(Quantity) FROM Sales GROUP BY ProductID WHERE AVG(Quantity) >= 20;

This query groups sales data by ProductID and filters the groups to include only those where the average quantity is greater than or equal to 20.

Q26

Single Choice

Which SQL query filters groups where the total sales amount is between 10000 and 50000?

SQL Code

SELECT Region, SUM(SaleAmount)
FROM Sales
GROUP BY Region
HAVING SUM(SaleAmount) BETWEEN 10000 AND 50000;

SELECT Region, SUM(SaleAmount) FROM Sales GROUP BY Region HAVING SUM(SaleAmount) BETWEEN 10000 AND 50000; SELECT Region, SUM(SaleAmount) FROM Sales WHERE SUM(SaleAmount) BETWEEN 10000 AND 50000; SELECT Region, SUM(SaleAmount) FROM Sales HAVING SUM(SaleAmount) BETWEEN 10000 AND 50000;SELECT Region, SUM(SaleAmount) FROM Sales GROUP BY Region WHERE SUM(SaleAmount) BETWEEN 10000 AND 50000;

This query groups sales data by Region and filters the groups to include only those where the total sales amount is between 10000 and 50000.

Q27

Single Choice

Which SQL query filters groups where the count of orders is less than or equal to 50?

SQL Code

SELECT CustomerID, COUNT(OrderID)
FROM Orders
GROUP BY CustomerID
HAVING COUNT(OrderID) <= 50;

SELECT CustomerID, COUNT(OrderID) FROM Orders GROUP BY CustomerID HAVING COUNT(OrderID) <= 50; SELECT CustomerID, COUNT(OrderID) FROM Orders WHERE COUNT(OrderID) <= 50; SELECT CustomerID, COUNT(OrderID) FROM Orders HAVING COUNT(OrderID) <= 50;SELECT CustomerID, COUNT(OrderID) FROM Orders GROUP BY CustomerID WHERE COUNT(OrderID) <= 50;

This query groups orders by CustomerID and filters the groups to include only those where the count of orders is less than or equal to 50.

Q28

Single Choice

Which SQL query filters groups where the maximum order amount is greater than 1000?

SQL Code

SELECT OrderID, MAX(OrderAmount)
FROM Orders
GROUP BY OrderID
HAVING MAX(OrderAmount) > 1000;

SELECT OrderID, MAX(OrderAmount) FROM Orders GROUP BY OrderID HAVING MAX(OrderAmount) > 1000; SELECT OrderID, MAX(OrderAmount) FROM Orders WHERE MAX(OrderAmount) > 1000; SELECT OrderID, MAX(OrderAmount) FROM Orders HAVING MAX(OrderAmount) > 1000;SELECT OrderID, MAX(OrderAmount) FROM Orders GROUP BY OrderID WHERE MAX(OrderAmount) > 1000;

This query groups orders by OrderID and filters the groups to include only those where the maximum order amount is greater than 1000.

Q29

Single Choice

Which SQL query filters groups where the total number of items is exactly 200?

SQL Code

SELECT CategoryID, SUM(ItemCount)
FROM Inventory
GROUP BY CategoryID
HAVING SUM(ItemCount) = 200;

SELECT CategoryID, SUM(ItemCount) FROM Inventory GROUP BY CategoryID HAVING SUM(ItemCount) = 200; SELECT CategoryID, SUM(ItemCount) FROM Inventory WHERE SUM(ItemCount) = 200; SELECT CategoryID, SUM(ItemCount) FROM Inventory HAVING SUM(ItemCount) = 200;SELECT CategoryID, SUM(ItemCount) FROM Inventory GROUP BY CategoryID WHERE SUM(ItemCount) = 200;

This query groups inventory data by CategoryID and filters the groups to include only those where the total number of items is exactly 200.

Q30

Single Choice

Which SQL query filters groups where the average discount is less than 10?

SQL Code

SELECT ProductID, AVG(Discount)
FROM Sales
GROUP BY ProductID
HAVING AVG(Discount) < 10;

SELECT ProductID, AVG(Discount) FROM Sales GROUP BY ProductID HAVING AVG(Discount) < 10; SELECT ProductID, AVG(Discount) FROM Sales WHERE AVG(Discount) < 10; SELECT ProductID, AVG(Discount) FROM Sales HAVING AVG(Discount) < 10;SELECT ProductID, AVG(Discount) FROM Sales GROUP BY ProductID WHERE AVG(Discount) < 10;

This query groups sales data by ProductID and filters the groups to include only those where the average discount is less than 10.

Q31

Multiple Choice

Which SQL queries filter groups based on aggregate values? (Select all that apply)

SQL Code

SELECT DepartmentID, AVG(Salary)
FROM Employees
GROUP BY DepartmentID
HAVING AVG(Salary) > 50000;

SELECT CustomerID, COUNT(OrderID)
FROM Orders
GROUP BY CustomerID
HAVING COUNT(OrderID) > 10;

SELECT ProductID, MIN(Price)
FROM Products
GROUP BY ProductID
HAVING MIN(Price) < 50;

SELECT SalesRepID, SUM(SaleAmount)
FROM Sales
GROUP BY SalesRepID
HAVING SUM(SaleAmount) < 100000;

SELECT DepartmentID, AVG(Salary) FROM Employees GROUP BY DepartmentID HAVING AVG(Salary) > 50000; SELECT CustomerID, COUNT(OrderID) FROM Orders GROUP BY CustomerID HAVING COUNT(OrderID) > 10; SELECT ProductID, MIN(Price) FROM Products GROUP BY ProductID HAVING MIN(Price) < 50;SELECT SalesRepID, SUM(SaleAmount) FROM Sales GROUP BY SalesRepID HAVING SUM(SaleAmount) < 100000;

These queries use the HAVING clause to filter groups based on aggregate values such as AVG(Salary), COUNT(OrderID), MIN(Price), and SUM(SaleAmount).

Q32

Multiple Choice

Which SQL queries filter groups where the aggregate value is between a range? (Select all that apply)

SQL Code

SELECT ProductID, AVG(Quantity)
FROM Sales
GROUP BY ProductID
HAVING AVG(Quantity) BETWEEN 10 AND 50;

SELECT Region, SUM(SaleAmount)
FROM Sales
GROUP BY Region
HAVING SUM(SaleAmount) BETWEEN 10000 AND 50000;

SELECT CategoryID, COUNT(ProductID)
FROM Products
GROUP BY CategoryID
HAVING COUNT(ProductID) BETWEEN 50 AND 100;

SELECT OrderID, MAX(OrderAmount)
FROM Orders
GROUP BY OrderID
HAVING MAX(OrderAmount) BETWEEN 500 AND 1000;

SELECT ProductID, AVG(Quantity) FROM Sales GROUP BY ProductID HAVING AVG(Quantity) BETWEEN 10 AND 50; SELECT Region, SUM(SaleAmount) FROM Sales GROUP BY Region HAVING SUM(SaleAmount) BETWEEN 10000 AND 50000; SELECT CategoryID, COUNT(ProductID) FROM Products GROUP BY CategoryID HAVING COUNT(ProductID) BETWEEN 50 AND 100;SELECT OrderID, MAX(OrderAmount) FROM Orders GROUP BY OrderID HAVING MAX(OrderAmount) BETWEEN 500 AND 1000;

These queries use the HAVING clause to filter groups where aggregate values fall within a specific range using BETWEEN.

Q33

Multiple Choice

Which SQL query filters groups where the total sales amount is exactly 5000?

SQL Code

SELECT ProductID, SUM(SaleAmount)
FROM Sales
GROUP BY ProductID
HAVING SUM(SaleAmount) = 5000;

SELECT Region, SUM(SaleAmount)
FROM Sales
GROUP BY Region
HAVING SUM(SaleAmount) = 5000;

SELECT CategoryID, SUM(ItemCost)
FROM Inventory
GROUP BY CategoryID
HAVING SUM(ItemCost) = 5000;

SELECT SalesRepID, SUM(SaleAmount)
FROM Sales
GROUP BY SalesRepID
HAVING SUM(SaleAmount) = 5000;

SELECT ProductID, SUM(SaleAmount) FROM Sales GROUP BY ProductID HAVING SUM(SaleAmount) = 5000; SELECT Region, SUM(SaleAmount) FROM Sales GROUP BY Region HAVING SUM(SaleAmount) = 5000; SELECT CategoryID, SUM(ItemCost) FROM Inventory GROUP BY CategoryID HAVING SUM(ItemCost) = 5000;SELECT SalesRepID, SUM(SaleAmount) FROM Sales GROUP BY SalesRepID HAVING SUM(SaleAmount) = 5000;

These queries use the HAVING clause to filter groups where the total sales or item cost is exactly 5000.

Q34

Multiple Choice

Which SQL query filters groups where the average order quantity is greater than 100?

SQL Code

SELECT OrderID, AVG(Quantity)
FROM Orders
GROUP BY OrderID
HAVING AVG(Quantity) > 100;

SELECT CustomerID, AVG(Quantity)
FROM Orders
GROUP BY CustomerID
HAVING AVG(Quantity) > 100;

SELECT ProductID, AVG(Quantity)
FROM Orders
GROUP BY ProductID
HAVING AVG(Quantity) > 100;

SELECT Region, AVG(Quantity)
FROM Orders
GROUP BY Region
HAVING AVG(Quantity) > 100;

SELECT OrderID, AVG(Quantity) FROM Orders GROUP BY OrderID HAVING AVG(Quantity) > 100; SELECT CustomerID, AVG(Quantity) FROM Orders GROUP BY CustomerID HAVING AVG(Quantity) > 100; SELECT ProductID, AVG(Quantity) FROM Orders GROUP BY ProductID HAVING AVG(Quantity) > 100;SELECT Region, AVG(Quantity) FROM Orders GROUP BY Region HAVING AVG(Quantity) > 100;

These queries use the HAVING clause to filter groups where the average order quantity is greater than 100.

Q35

Multiple Choice

Which SQL query filters groups where the count of distinct products is exactly 10?

SQL Code

SELECT CategoryID, COUNT(DISTINCT ProductID)
FROM Products
GROUP BY CategoryID
HAVING COUNT(DISTINCT ProductID) = 10;

SELECT SupplierID, COUNT(DISTINCT ProductID)
FROM Products
GROUP BY SupplierID
HAVING COUNT(DISTINCT ProductID) = 10;

SELECT ManufacturerID, COUNT(DISTINCT ProductID)
FROM Products
GROUP BY ManufacturerID
HAVING COUNT(DISTINCT ProductID) = 10;

SELECT WarehouseID, COUNT(DISTINCT ProductID)
FROM Inventory
GROUP BY WarehouseID
HAVING COUNT(DISTINCT ProductID) = 10;

SELECT CategoryID, COUNT(DISTINCT ProductID) FROM Products GROUP BY CategoryID HAVING COUNT(DISTINCT ProductID) = 10; SELECT SupplierID, COUNT(DISTINCT ProductID) FROM Products GROUP BY SupplierID HAVING COUNT(DISTINCT ProductID) = 10; SELECT ManufacturerID, COUNT(DISTINCT ProductID) FROM Products GROUP BY ManufacturerID HAVING COUNT(DISTINCT ProductID) = 10;SELECT WarehouseID, COUNT(DISTINCT ProductID) FROM Inventory GROUP BY WarehouseID HAVING COUNT(DISTINCT ProductID) = 10;

These queries use the HAVING clause to filter groups where the count of distinct products is exactly 10.

Q36

Multiple Choice

Which SQL query filters groups where the minimum sale amount is less than 50?

SQL Code

SELECT SalesRepID, MIN(SaleAmount)
FROM Sales
GROUP BY SalesRepID
HAVING MIN(SaleAmount) < 50;

SELECT Region, MIN(SaleAmount)
FROM Sales
GROUP BY Region
HAVING MIN(SaleAmount) < 50;

SELECT ProductID, MIN(SaleAmount)
FROM Sales
GROUP BY ProductID
HAVING MIN(SaleAmount) < 50;

SELECT CustomerID, MIN(SaleAmount)
FROM Sales
GROUP BY CustomerID
HAVING MIN(SaleAmount) < 50;

SELECT SalesRepID, MIN(SaleAmount) FROM Sales GROUP BY SalesRepID HAVING MIN(SaleAmount) < 50; SELECT Region, MIN(SaleAmount) FROM Sales GROUP BY Region HAVING MIN(SaleAmount) < 50; SELECT ProductID, MIN(SaleAmount) FROM Sales GROUP BY ProductID HAVING MIN(SaleAmount) < 50;SELECT CustomerID, MIN(SaleAmount) FROM Sales GROUP BY CustomerID HAVING MIN(SaleAmount) < 50;

These queries use the HAVING clause to filter groups where the minimum sale amount is less than 50.

Q37

Multiple Choice

Which SQL query filters groups where the total quantity is greater than 1000?

SQL Code

SELECT SupplierID, SUM(Quantity)
FROM Inventory
GROUP BY SupplierID
HAVING SUM(Quantity) > 1000;

SELECT ProductID, SUM(Quantity)
FROM Inventory
GROUP BY ProductID
HAVING SUM(Quantity) > 1000;

SELECT CategoryID, SUM(Quantity)
FROM Inventory
GROUP BY CategoryID
HAVING SUM(Quantity) > 1000;

SELECT WarehouseID, SUM(Quantity)
FROM Inventory
GROUP BY WarehouseID
HAVING SUM(Quantity) > 1000;

SELECT SupplierID, SUM(Quantity) FROM Inventory GROUP BY SupplierID HAVING SUM(Quantity) > 1000; SELECT ProductID, SUM(Quantity) FROM Inventory GROUP BY ProductID HAVING SUM(Quantity) > 1000; SELECT CategoryID, SUM(Quantity) FROM Inventory GROUP BY CategoryID HAVING SUM(Quantity) > 1000;SELECT WarehouseID, SUM(Quantity) FROM Inventory GROUP BY WarehouseID HAVING SUM(Quantity) > 1000;

These queries use the HAVING clause to filter groups where the total quantity is greater than 1000.

Q38

Multiple Choice

Which SQL query filters groups where the count of orders is between 20 and 50?

SQL Code

SELECT CustomerID, COUNT(OrderID)
FROM Orders
GROUP BY CustomerID
HAVING COUNT(OrderID) BETWEEN 20 AND 50;

SELECT SalesRepID, COUNT(OrderID)
FROM Orders
GROUP BY SalesRepID
HAVING COUNT(OrderID) BETWEEN 20 AND 50;

SELECT Region, COUNT(OrderID)
FROM Orders
GROUP BY Region
HAVING COUNT(OrderID) BETWEEN 20 AND 50;

SELECT ProductID, COUNT(OrderID)
FROM Orders
GROUP BY ProductID
HAVING COUNT(OrderID) BETWEEN 20 AND 50;

SELECT CustomerID, COUNT(OrderID) FROM Orders GROUP BY CustomerID HAVING COUNT(OrderID) BETWEEN 20 AND 50; SELECT SalesRepID, COUNT(OrderID) FROM Orders GROUP BY SalesRepID HAVING COUNT(OrderID) BETWEEN 20 AND 50; SELECT Region, COUNT(OrderID) FROM Orders GROUP BY Region HAVING COUNT(OrderID) BETWEEN 20 AND 50;SELECT ProductID, COUNT(OrderID) FROM Orders GROUP BY ProductID HAVING COUNT(OrderID) BETWEEN 20 AND 50;

These queries use the HAVING clause to filter groups where the count of orders is between 20 and 50.

Q39

Multiple Choice

Which SQL query filters groups where the average sale amount is exactly 200?

SQL Code

SELECT SalesRepID, AVG(SaleAmount)
FROM Sales
GROUP BY SalesRepID
HAVING AVG(SaleAmount) = 200;

SELECT ProductID, AVG(SaleAmount)
FROM Sales
GROUP BY ProductID
HAVING AVG(SaleAmount) = 200;

SELECT Region, AVG(SaleAmount)
FROM Sales
GROUP BY Region
HAVING AVG(SaleAmount) = 200;

SELECT CustomerID, AVG(SaleAmount)
FROM Sales
GROUP BY CustomerID
HAVING AVG(SaleAmount) = 200;

SELECT SalesRepID, AVG(SaleAmount) FROM Sales GROUP BY SalesRepID HAVING AVG(SaleAmount) = 200; SELECT ProductID, AVG(SaleAmount) FROM Sales GROUP BY ProductID HAVING AVG(SaleAmount) = 200; SELECT Region, AVG(SaleAmount) FROM Sales GROUP BY Region HAVING AVG(SaleAmount) = 200;SELECT CustomerID, AVG(SaleAmount) FROM Sales GROUP BY CustomerID HAVING AVG(SaleAmount) = 200;

These queries use the HAVING clause to filter groups where the average sale amount is exactly 200.

Q40

Multiple Choice

Which SQL query filters groups where the total revenue is greater than 50000?

SQL Code

SELECT SalesRepID, SUM(Revenue)
FROM Sales
GROUP BY SalesRepID
HAVING SUM(Revenue) > 50000;

SELECT Region, SUM(Revenue)
FROM Sales
GROUP BY Region
HAVING SUM(Revenue) > 50000;

SELECT ProductID, SUM(Revenue)
FROM Sales
GROUP BY ProductID
HAVING SUM(Revenue) > 50000;

SELECT CustomerID, SUM(Revenue)
FROM Sales
GROUP BY CustomerID
HAVING SUM(Revenue) > 50000;

SELECT SalesRepID, SUM(Revenue) FROM Sales GROUP BY SalesRepID HAVING SUM(Revenue) > 50000; SELECT Region, SUM(Revenue) FROM Sales GROUP BY Region HAVING SUM(Revenue) > 50000; SELECT ProductID, SUM(Revenue) FROM Sales GROUP BY ProductID HAVING SUM(Revenue) > 50000;SELECT CustomerID, SUM(Revenue) FROM Sales GROUP BY CustomerID HAVING SUM(Revenue) > 50000;

These queries use the HAVING clause to filter groups where the total revenue is greater than 50000.

Q41

Multiple Choice

Which SQL query filters groups where the count of employees is less than 5?

SQL Code

SELECT DepartmentID, COUNT(EmployeeID)
FROM Employees
GROUP BY DepartmentID
HAVING COUNT(EmployeeID) < 5;

SELECT ManagerID, COUNT(EmployeeID)
FROM Employees
GROUP BY ManagerID
HAVING COUNT(EmployeeID) < 5;

SELECT OfficeID, COUNT(EmployeeID)
FROM Employees
GROUP BY OfficeID
HAVING COUNT(EmployeeID) < 5;

SELECT LocationID, COUNT(EmployeeID)
FROM Employees
GROUP BY LocationID
HAVING COUNT(EmployeeID) < 5;

SELECT DepartmentID, COUNT(EmployeeID) FROM Employees GROUP BY DepartmentID HAVING COUNT(EmployeeID) < 5; SELECT ManagerID, COUNT(EmployeeID) FROM Employees GROUP BY ManagerID HAVING COUNT(EmployeeID) < 5; SELECT OfficeID, COUNT(EmployeeID) FROM Employees GROUP BY OfficeID HAVING COUNT(EmployeeID) < 5;SELECT LocationID, COUNT(EmployeeID) FROM Employees GROUP BY LocationID HAVING COUNT(EmployeeID) < 5;

These queries use the HAVING clause to filter groups where the count of employees is less than 5.

Q42

Multiple Choice

Which SQL query filters groups where the maximum sale amount is greater than 1000?

SQL Code

SELECT SalesRepID, MAX(SaleAmount)
FROM Sales
GROUP BY SalesRepID
HAVING MAX(SaleAmount) > 1000;

SELECT ProductID, MAX(SaleAmount)
FROM Sales
GROUP BY ProductID
HAVING MAX(SaleAmount) > 1000;

SELECT Region, MAX(SaleAmount)
FROM Sales
GROUP BY Region
HAVING MAX(SaleAmount) > 1000;

SELECT CustomerID, MAX(SaleAmount)
FROM Sales
GROUP BY CustomerID
HAVING MAX(SaleAmount) > 1000;

SELECT SalesRepID, MAX(SaleAmount) FROM Sales GROUP BY SalesRepID HAVING MAX(SaleAmount) > 1000; SELECT ProductID, MAX(SaleAmount) FROM Sales GROUP BY ProductID HAVING MAX(SaleAmount) > 1000; SELECT Region, MAX(SaleAmount) FROM Sales GROUP BY Region HAVING MAX(SaleAmount) > 1000;SELECT CustomerID, MAX(SaleAmount) FROM Sales GROUP BY CustomerID HAVING MAX(SaleAmount) > 1000;

These queries use the HAVING clause to filter groups where the maximum sale amount is greater than 1000.

Q43

Multiple Choice

Which SQL query filters groups where the count of unique customers is less than 20?

SQL Code

SELECT Region, COUNT(DISTINCT CustomerID)
FROM Sales
GROUP BY Region
HAVING COUNT(DISTINCT CustomerID) < 20;

SELECT ProductID, COUNT(DISTINCT CustomerID)
FROM Sales
GROUP BY ProductID
HAVING COUNT(DISTINCT CustomerID) < 20;

SELECT SalesRepID, COUNT(DISTINCT CustomerID)
FROM Sales
GROUP BY SalesRepID
HAVING COUNT(DISTINCT CustomerID) < 20;

SELECT CategoryID, COUNT(DISTINCT CustomerID)
FROM Sales
GROUP BY CategoryID
HAVING COUNT(DISTINCT CustomerID) < 20;

SELECT Region, COUNT(DISTINCT CustomerID) FROM Sales GROUP BY Region HAVING COUNT(DISTINCT CustomerID) < 20; SELECT ProductID, COUNT(DISTINCT CustomerID) FROM Sales GROUP BY ProductID HAVING COUNT(DISTINCT CustomerID) < 20; SELECT SalesRepID, COUNT(DISTINCT CustomerID) FROM Sales GROUP BY SalesRepID HAVING COUNT(DISTINCT CustomerID) < 20;SELECT CategoryID, COUNT(DISTINCT CustomerID) FROM Sales GROUP BY CategoryID HAVING COUNT(DISTINCT CustomerID) < 20;

These queries use the HAVING clause to filter groups where the count of unique customers is less than 20.

Q44

Multiple Choice

Which SQL query filters groups where the total cost is less than the average cost?

SQL Code

SELECT ProductID, SUM(Cost)
FROM Products
GROUP BY ProductID
HAVING SUM(Cost) < (SELECT AVG(Cost) FROM Products);

SELECT CategoryID, SUM(Cost)
FROM Products
GROUP BY CategoryID
HAVING SUM(Cost) < (SELECT AVG(Cost) FROM Products);

SELECT SupplierID, SUM(Cost)
FROM Products
GROUP BY SupplierID
HAVING SUM(Cost) < (SELECT AVG(Cost) FROM Products);

SELECT ManufacturerID, SUM(Cost)
FROM Products
GROUP BY ManufacturerID
HAVING SUM(Cost) < (SELECT AVG(Cost) FROM Products);

SELECT ProductID, SUM(Cost) FROM Products GROUP BY ProductID HAVING SUM(Cost) < (SELECT AVG(Cost) FROM Products); SELECT CategoryID, SUM(Cost) FROM Products GROUP BY CategoryID HAVING SUM(Cost) < (SELECT AVG(Cost) FROM Products); SELECT SupplierID, SUM(Cost) FROM Products GROUP BY SupplierID HAVING SUM(Cost) < (SELECT AVG(Cost) FROM Products);SELECT ManufacturerID, SUM(Cost) FROM Products GROUP BY ManufacturerID HAVING SUM(Cost) < (SELECT AVG(Cost) FROM Products);

These queries use the HAVING clause to filter groups where the total cost is less than the average cost across all products.

Q45

Multiple Choice

Which SQL query filters groups where the sum of quantities is exactly 150?

SQL Code

SELECT OrderID, SUM(Quantity)
FROM OrderDetails
GROUP BY OrderID
HAVING SUM(Quantity) = 150;

SELECT ProductID, SUM(Quantity)
FROM OrderDetails
GROUP BY ProductID
HAVING SUM(Quantity) = 150;

SELECT CustomerID, SUM(Quantity)
FROM OrderDetails
GROUP BY CustomerID
HAVING SUM(Quantity) = 150;

SELECT Region, SUM(Quantity)
FROM OrderDetails
GROUP BY Region
HAVING SUM(Quantity) = 150;

SELECT OrderID, SUM(Quantity) FROM OrderDetails GROUP BY OrderID HAVING SUM(Quantity) = 150; SELECT ProductID, SUM(Quantity) FROM OrderDetails GROUP BY ProductID HAVING SUM(Quantity) = 150; SELECT CustomerID, SUM(Quantity) FROM OrderDetails GROUP BY CustomerID HAVING SUM(Quantity) = 150;SELECT Region, SUM(Quantity) FROM OrderDetails GROUP BY Region HAVING SUM(Quantity) = 150;

These queries use the HAVING clause to filter groups where the sum of quantities is exactly 150.

Microsoft SQL Server Database Quiz Questions