Test Your Microsoft SQL Server GROUP BY Clause Knowledge with This Quiz

Progress

True / False

The GROUP BY clause is used to arrange identical data into groups.

True False

The GROUP BY clause groups rows that have the same values into summary rows, like 'find the number of customers in each country'.

True / False

You can use the GROUP BY clause without any aggregate functions.

True False

The GROUP BY clause is typically used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to perform calculations on each group of data.

True / False

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

True False

The HAVING clause is typically used in combination with the GROUP BY clause to filter groups based on a condition.

True / False

The GROUP BY clause can be used with the ORDER BY clause.

True False

You can use the ORDER BY clause to sort the result set in either ascending or descending order.

True / False

Columns in the SELECT statement that are not part of aggregate functions must be included in the GROUP BY clause.

True False

Every column in the SELECT clause that isn't part of an aggregate function must be included in the GROUP BY clause.

True / False

The GROUP BY clause can group rows based on the values of one or more columns.

True False

The GROUP BY clause can group rows based on the values of one or more columns, allowing for multi-level grouping.

True / False

The GROUP BY clause must be placed before the WHERE clause in a SQL statement.

True False

The GROUP BY clause must be placed after the WHERE clause and before the ORDER BY clause.

True / False

Aggregate functions can be used directly in the WHERE clause without the GROUP BY clause.

True False

Aggregate functions cannot be used directly in the WHERE clause; they should be used in the HAVING clause when a GROUP BY clause is present.

True / False

GROUP BY can be used to arrange data based on derived columns.

True False

You can use derived columns in the GROUP BY clause, which are columns defined in the SELECT statement.

Q10

True / False

The HAVING clause can filter data after it has been grouped by the GROUP BY clause.

True False

The HAVING clause is used to filter records that are returned by a GROUP BY clause, similar to how the WHERE clause is used to filter rows.

Q11

True / False

GROUP BY can be used with SELECT INTO statements.

True False

You can use the GROUP BY clause with SELECT INTO statements to create a new table with grouped data.

Q12

True / False

GROUP BY and DISTINCT can be used together in a single query.

True False

While both GROUP BY and DISTINCT are used to remove duplicates, they can be used together when distinct and grouped results are needed.

Q13

True / False

The GROUP BY clause is optional when using aggregate functions.

True False

The GROUP BY clause is optional when you want to aggregate all rows in the table without grouping.

Q14

True / False

You can use column aliases in the GROUP BY clause.

True False

Column aliases defined in the SELECT clause cannot be used in the GROUP BY clause; the original column names must be used.

Q15

True / False

The GROUP BY clause can be used with multiple columns to create nested groups.

True False

The GROUP BY clause can use multiple columns to create nested groupings, providing more granular groupings of data.

Q16

Single Choice

Which SQL query correctly groups the employees by their department?

SQL Code

SELECT DepartmentID, COUNT(*)
FROM Employees
GROUP BY DepartmentID;

SELECT DepartmentID, COUNT(*) FROM Employees GROUP BY DepartmentID; SELECT DepartmentID, COUNT(*) FROM Employees; SELECT COUNT(*) FROM Employees GROUP BY DepartmentID;SELECT DepartmentID, COUNT(*) FROM Employees WHERE DepartmentID IS NOT NULL;

This query groups the employees by DepartmentID and counts the number of employees in each department.

Q17

Single Choice

Which SQL query correctly groups the sales data by year?

SQL Code

SELECT YEAR(SaleDate) AS SaleYear, SUM(SaleAmount)
FROM Sales
GROUP BY YEAR(SaleDate);

SELECT YEAR(SaleDate) AS SaleYear, SUM(SaleAmount) FROM Sales GROUP BY YEAR(SaleDate); SELECT SaleDate AS SaleYear, SUM(SaleAmount) FROM Sales GROUP BY SaleDate; SELECT YEAR(SaleDate), SUM(SaleAmount) FROM Sales GROUP BY SaleDate;SELECT YEAR(SaleDate) AS SaleYear, SUM(SaleAmount) FROM Sales GROUP BY SaleDate;

This query groups the sales data by year and sums the SaleAmount for each year.

Q18

Single Choice

How do you group the orders by customer ID?

SQL Code

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

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

This query groups the orders by CustomerID and counts the number of orders for each customer.

Q19

Single Choice

Which query shows the total quantity of products sold by each supplier?

SQL Code

SELECT SupplierID, SUM(Quantity)
FROM OrderDetails
GROUP BY SupplierID;

SELECT SupplierID, SUM(Quantity) FROM OrderDetails GROUP BY SupplierID; SELECT SupplierID, SUM(Quantity) FROM OrderDetails WHERE Quantity > 0; SELECT SupplierID, SUM(Quantity) FROM OrderDetails GROUP BY SupplierName;SELECT SupplierID, Quantity FROM OrderDetails GROUP BY SupplierID;

This query groups the order details by SupplierID and sums the Quantity for each supplier.

Q20

Single Choice

How do you calculate the average order amount for each customer?

SQL Code

SELECT CustomerID, AVG(OrderAmount)
FROM Orders
GROUP BY CustomerID;

SELECT CustomerID, AVG(OrderAmount) FROM Orders GROUP BY CustomerID; SELECT CustomerID, SUM(OrderAmount) FROM Orders GROUP BY CustomerID; SELECT CustomerID, AVG(OrderAmount) FROM Orders;SELECT CustomerID, OrderAmount FROM Orders GROUP BY CustomerID;

This query groups the orders by CustomerID and calculates the average OrderAmount for each customer.

Q21

Single Choice

Which SQL query correctly groups the employees by their job title?

SQL Code

SELECT JobTitle, COUNT(*)
FROM Employees
GROUP BY JobTitle;

SELECT JobTitle, COUNT(*) FROM Employees GROUP BY JobTitle; SELECT JobTitle, COUNT(*) FROM Employees; SELECT JobTitle, COUNT(*) FROM Employees WHERE JobTitle IS NOT NULL;SELECT JobTitle, COUNT(EmployeeID) FROM Employees GROUP BY JobTitle;

This query groups the employees by JobTitle and counts the number of employees in each job title.

Q22

Single Choice

Which query shows the highest sales amount for each product?

SQL Code

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

SELECT ProductID, MAX(SaleAmount) FROM Sales GROUP BY ProductID; SELECT ProductID, MAX(SaleAmount) FROM Sales GROUP BY SaleDate; SELECT ProductID, MAX(SaleAmount) FROM Sales;SELECT ProductID, SaleAmount FROM Sales GROUP BY ProductID;

This query groups the sales data by ProductID and finds the maximum SaleAmount for each product.

Q23

Single Choice

How do you group the sales data by region?

SQL Code

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

SELECT Region, SUM(SaleAmount) FROM Sales GROUP BY Region; SELECT Region, SUM(SaleAmount) FROM Sales GROUP BY SaleDate; SELECT Region, SaleAmount FROM Sales GROUP BY Region;SELECT Region, SUM(SaleAmount) FROM Sales WHERE Region IS NOT NULL;

This query groups the sales data by Region and sums the SaleAmount for each region.

Q24

Single Choice

Which SQL query correctly groups the employees by their manager ID?

SQL Code

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

SELECT ManagerID, COUNT(EmployeeID) FROM Employees GROUP BY ManagerID; SELECT ManagerID, COUNT(EmployeeID) FROM Employees; SELECT ManagerID, COUNT(*) FROM Employees GROUP BY ManagerID;SELECT ManagerID, EmployeeID FROM Employees GROUP BY ManagerID;

This query groups the employees by ManagerID and counts the number of employees under each manager.

Q25

Single Choice

How do you group the inventory data by product category?

SQL Code

SELECT ProductCategory, SUM(Quantity)
FROM Inventory
GROUP BY ProductCategory;

SELECT ProductCategory, SUM(Quantity) FROM Inventory GROUP BY ProductCategory; SELECT ProductCategory, SUM(Quantity) FROM Inventory; SELECT ProductCategory, Quantity FROM Inventory GROUP BY ProductCategory;SELECT ProductCategory, SUM(Quantity) FROM Inventory WHERE ProductCategory IS NOT NULL;

This query groups the inventory data by ProductCategory and sums the Quantity for each category.

Q26

Single Choice

Which SQL query correctly groups the orders by their status?

SQL Code

SELECT OrderStatus, COUNT(*)
FROM Orders
GROUP BY OrderStatus;

SELECT OrderStatus, COUNT(*) FROM Orders GROUP BY OrderStatus; SELECT OrderStatus, COUNT(*) FROM Orders; SELECT OrderStatus, COUNT(OrderID) FROM Orders GROUP BY OrderStatus;SELECT OrderStatus, OrderID FROM Orders GROUP BY OrderStatus;

This query groups the orders by OrderStatus and counts the number of orders for each status.

Q27

Single Choice

How do you group the sales data by product and customer?

SQL Code

SELECT ProductID, CustomerID, SUM(SaleAmount)
FROM Sales
GROUP BY ProductID, CustomerID;

SELECT ProductID, CustomerID, SUM(SaleAmount) FROM Sales GROUP BY ProductID, CustomerID; SELECT ProductID, CustomerID, SUM(SaleAmount) FROM Sales GROUP BY ProductID; SELECT ProductID, CustomerID, SUM(SaleAmount) FROM Sales;SELECT ProductID, CustomerID FROM Sales GROUP BY ProductID, CustomerID;

This query groups the sales data by both ProductID and CustomerID and sums the SaleAmount for each combination.

Q28

Single Choice

Which query shows the total sales amount for each sales representative?

SQL Code

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

SELECT SalesRepID, SUM(SaleAmount) FROM Sales GROUP BY SalesRepID; SELECT SalesRepID, SUM(SaleAmount) FROM Sales; SELECT SalesRepID, SaleAmount FROM Sales GROUP BY SalesRepID;SELECT SalesRepID, SUM(SaleAmount) FROM Sales WHERE SalesRepID IS NOT NULL;

This query groups the sales data by SalesRepID and sums the SaleAmount for each sales representative.

Q29

Single Choice

How do you group the customer orders by order date?

SQL Code

SELECT OrderDate, COUNT(OrderID)
FROM Orders
GROUP BY OrderDate;

SELECT OrderDate, COUNT(OrderID) FROM Orders GROUP BY OrderDate; SELECT OrderDate, COUNT(OrderID) FROM Orders; SELECT OrderDate, COUNT(*) FROM Orders GROUP BY OrderDate;SELECT OrderDate, OrderID FROM Orders GROUP BY OrderDate;

This query groups the orders by OrderDate and counts the number of orders for each date.

Q30

Single Choice

Which SQL query correctly groups the products by their category?

SQL Code

SELECT ProductCategory, COUNT(*)
FROM Products
GROUP BY ProductCategory;

SELECT ProductCategory, COUNT(*) FROM Products GROUP BY ProductCategory; SELECT ProductCategory, COUNT(*) FROM Products; SELECT ProductCategory, COUNT(ProductID) FROM Products GROUP BY ProductCategory;SELECT ProductCategory, ProductID FROM Products GROUP BY ProductCategory;

This query groups the products by ProductCategory and counts the number of products in each category.

Q31

Multiple Choice

Which SQL query groups the employees by department and calculates the total salary for each department?

SQL Code

SELECT DepartmentID, SUM(Salary)
FROM Employees
GROUP BY DepartmentID;

SELECT DepartmentID, SUM(Salary) FROM Employees GROUP BY DepartmentID; SELECT DepartmentID, COUNT(Salary) FROM Employees GROUP BY DepartmentID; SELECT DepartmentID, AVG(Salary) FROM Employees GROUP BY DepartmentID;SELECT DepartmentID, MAX(Salary) FROM Employees GROUP BY DepartmentID;

This query groups the employees by DepartmentID and sums the Salary for each department.

Q32

Multiple Choice

Which SQL query groups the products by supplier and calculates the total quantity for each supplier?

SQL Code

SELECT SupplierID, SUM(Quantity)
FROM Products
GROUP BY SupplierID;

SELECT SupplierID, SUM(Quantity) FROM Products GROUP BY SupplierID; SELECT SupplierID, COUNT(Quantity) FROM Products GROUP BY SupplierID; SELECT SupplierID, AVG(Quantity) FROM Products GROUP BY SupplierID;SELECT SupplierID, MAX(Quantity) FROM Products GROUP BY SupplierID;

This query groups the products by SupplierID and sums the Quantity for each supplier.

Q33

Multiple Choice

Which SQL query groups the sales data by region and calculates the average sales amount for each region?

SQL Code

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

SELECT Region, AVG(SaleAmount) FROM Sales GROUP BY Region; SELECT Region, COUNT(SaleAmount) FROM Sales GROUP BY Region; SELECT Region, SUM(SaleAmount) FROM Sales GROUP BY Region;SELECT Region, MAX(SaleAmount) FROM Sales GROUP BY Region;

This query groups the sales data by Region and calculates the average SaleAmount for each region.

Q34

Multiple Choice

Which SQL query groups the orders by customer and calculates the highest order amount for each customer?

SQL Code

SELECT CustomerID, MAX(OrderAmount)
FROM Orders
GROUP BY CustomerID;

SELECT CustomerID, MAX(OrderAmount) FROM Orders GROUP BY CustomerID; SELECT CustomerID, COUNT(OrderAmount) FROM Orders GROUP BY CustomerID; SELECT CustomerID, SUM(OrderAmount) FROM Orders GROUP BY CustomerID;SELECT CustomerID, AVG(OrderAmount) FROM Orders GROUP BY CustomerID;

This query groups the orders by CustomerID and finds the maximum OrderAmount for each customer.

Q35

Multiple Choice

Which SQL query groups the employees by job title and calculates the total number of employees in each job title?

SQL Code

SELECT JobTitle, COUNT(*)
FROM Employees
GROUP BY JobTitle;

SELECT JobTitle, COUNT(*) FROM Employees GROUP BY JobTitle; SELECT JobTitle, SUM(*) FROM Employees GROUP BY JobTitle; SELECT JobTitle, AVG(*) FROM Employees GROUP BY JobTitle;SELECT JobTitle, MAX(*) FROM Employees GROUP BY JobTitle;

This query groups the employees by JobTitle and counts the number of employees in each job title.

Q36

Multiple Choice

Which SQL query groups the sales data by product category and calculates the total sales amount for each category?

SQL Code

SELECT ProductCategory, SUM(SaleAmount)
FROM Sales
GROUP BY ProductCategory;

SELECT ProductCategory, SUM(SaleAmount) FROM Sales GROUP BY ProductCategory; SELECT ProductCategory, COUNT(SaleAmount) FROM Sales GROUP BY ProductCategory; SELECT ProductCategory, AVG(SaleAmount) FROM Sales GROUP BY ProductCategory;SELECT ProductCategory, MAX(SaleAmount) FROM Sales GROUP BY ProductCategory;

This query groups the sales data by ProductCategory and sums the SaleAmount for each category.

Q37

Multiple Choice

Which SQL query groups the orders by order date and calculates the total number of orders for each date?

SQL Code

SELECT OrderDate, COUNT(*)
FROM Orders
GROUP BY OrderDate;

SELECT OrderDate, COUNT(*) FROM Orders GROUP BY OrderDate; SELECT OrderDate, SUM(*) FROM Orders GROUP BY OrderDate; SELECT OrderDate, AVG(*) FROM Orders GROUP BY OrderDate;SELECT OrderDate, MAX(*) FROM Orders GROUP BY OrderDate;

This query groups the orders by OrderDate and counts the number of orders for each date.

Q38

Multiple Choice

Which SQL query groups the inventory data by product category and calculates the average quantity for each category?

SQL Code

SELECT ProductCategory, AVG(Quantity)
FROM Inventory
GROUP BY ProductCategory;

SELECT ProductCategory, AVG(Quantity) FROM Inventory GROUP BY ProductCategory; SELECT ProductCategory, SUM(Quantity) FROM Inventory GROUP BY ProductCategory; SELECT ProductCategory, COUNT(Quantity) FROM Inventory GROUP BY ProductCategory;SELECT ProductCategory, MAX(Quantity) FROM Inventory GROUP BY ProductCategory;

This query groups the inventory data by ProductCategory and calculates the average Quantity for each category.

Q39

Multiple Choice

Which SQL query groups the employees by department and calculates the average salary for each department?

SQL Code

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

SELECT DepartmentID, AVG(Salary) FROM Employees GROUP BY DepartmentID; SELECT DepartmentID, SUM(Salary) FROM Employees GROUP BY DepartmentID; SELECT DepartmentID, COUNT(Salary) FROM Employees GROUP BY DepartmentID;SELECT DepartmentID, MAX(Salary) FROM Employees GROUP BY DepartmentID;

This query groups the employees by DepartmentID and calculates the average Salary for each department.

Q40

Multiple Choice

Which SQL query groups the sales data by product and calculates the highest sales amount for each product?

SQL Code

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

SELECT ProductID, MAX(SaleAmount) FROM Sales GROUP BY ProductID; SELECT ProductID, SUM(SaleAmount) FROM Sales GROUP BY ProductID; SELECT ProductID, COUNT(SaleAmount) FROM Sales GROUP BY ProductID;SELECT ProductID, AVG(SaleAmount) FROM Sales GROUP BY ProductID;

This query groups the sales data by ProductID and finds the maximum SaleAmount for each product.

Q41

Multiple Choice

Which SQL query groups the customer orders by customer ID and calculates the total order amount for each customer?

SQL Code

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

SELECT CustomerID, SUM(OrderAmount) FROM Orders GROUP BY CustomerID; SELECT CustomerID, COUNT(OrderAmount) FROM Orders GROUP BY CustomerID; SELECT CustomerID, AVG(OrderAmount) FROM Orders GROUP BY CustomerID;SELECT CustomerID, MAX(OrderAmount) FROM Orders GROUP BY CustomerID;

This query groups the orders by CustomerID and sums the OrderAmount for each customer.

Q42

Multiple Choice

Which SQL query groups the sales data by sales representative and calculates the average sales amount for each representative?

SQL Code

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

SELECT SalesRepID, AVG(SaleAmount) FROM Sales GROUP BY SalesRepID; SELECT SalesRepID, SUM(SaleAmount) FROM Sales GROUP BY SalesRepID; SELECT SalesRepID, COUNT(SaleAmount) FROM Sales GROUP BY SalesRepID;SELECT SalesRepID, MAX(SaleAmount) FROM Sales GROUP BY SalesRepID;

This query groups the sales data by SalesRepID and calculates the average SaleAmount for each representative.

Q43

Multiple Choice

Which SQL query groups the employees by job title and calculates the highest salary for each job title?

SQL Code

SELECT JobTitle, MAX(Salary)
FROM Employees
GROUP BY JobTitle;

SELECT JobTitle, MAX(Salary) FROM Employees GROUP BY JobTitle; SELECT JobTitle, SUM(Salary) FROM Employees GROUP BY JobTitle; SELECT JobTitle, COUNT(Salary) FROM Employees GROUP BY JobTitle;SELECT JobTitle, AVG(Salary) FROM Employees GROUP BY JobTitle;

This query groups the employees by JobTitle and finds the maximum Salary for each job title.

Q44

Multiple Choice

Which SQL query groups the sales data by region and calculates the highest sales amount for each region?

SQL Code

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

SELECT Region, MAX(SaleAmount) FROM Sales GROUP BY Region; SELECT Region, SUM(SaleAmount) FROM Sales GROUP BY Region; SELECT Region, COUNT(SaleAmount) FROM Sales GROUP BY Region;SELECT Region, AVG(SaleAmount) FROM Sales GROUP BY Region;

This query groups the sales data by Region and finds the maximum SaleAmount for each region.

Q45

Multiple Choice

Which SQL query groups the products by category and calculates the total quantity for each category?

SQL Code

SELECT ProductCategory, SUM(Quantity)
FROM Products
GROUP BY ProductCategory;

SELECT ProductCategory, SUM(Quantity) FROM Products GROUP BY ProductCategory; SELECT ProductCategory, COUNT(Quantity) FROM Products GROUP BY ProductCategory; SELECT ProductCategory, AVG(Quantity) FROM Products GROUP BY ProductCategory;SELECT ProductCategory, MAX(Quantity) FROM Products GROUP BY ProductCategory;

This query groups the products by ProductCategory and sums the Quantity for each category.

Microsoft SQL Server Database Quiz Questions