Test Your Microsoft SQL Server COUNT, SUM, AVG, MIN, and MAX Functions Knowledge with This Quiz

Progress

True / False

The COUNT function can be used to count the number of rows in a table that satisfy a condition.

True False

The COUNT function counts the number of rows that match a specific condition in a query. For example, `SELECT COUNT(*) FROM Orders WHERE Status = 'Shipped';` counts all orders with the status 'Shipped'.

True / False

The SUM function can be used to calculate the total amount of all values in a column.

True False

The SUM function adds up all the values in a specified column. For example, `SELECT SUM(Price) FROM Products;` calculates the total price of all products.

True / False

The AVG function can be used to find the highest value in a column.

True False

The AVG function calculates the average value of a column, not the highest value. The MAX function is used to find the highest value.

True / False

The MIN function returns the smallest value in a column.

True False

The MIN function returns the minimum value from a set of values in a specified column. For example, `SELECT MIN(Salary) FROM Employees;` returns the lowest salary.

True / False

The MAX function can be used to find the minimum price from the Products table.

True False

The MAX function is used to find the highest value, not the minimum. To find the minimum price, you should use the MIN function.

True / False

The COUNT function can be used to count the number of distinct values in a column.

True False

The COUNT function can count distinct values if used with `COUNT(DISTINCT column)`. For example, `SELECT COUNT(DISTINCT Country) FROM Customers;` counts the unique countries in the Customers table.

True / False

The SUM function can be used to calculate the total number of items sold for each product.

True False

The SUM function calculates the total sum of a specified column. To find the total number of items sold for each product, you would use `SUM(Quantity)` and group by the product.

True / False

The AVG function can be used to calculate the total average value of a numeric column.

True False

The AVG function calculates the average value of a numeric column. For example, `SELECT AVG(Salary) FROM Employees;` calculates the average salary of employees.

True / False

The MIN function can be used to find the maximum value in a column.

True False

The MIN function returns the smallest value in a column. To find the maximum value, you should use the MAX function.

Q10

True / False

The MAX function can be used to determine the total number of rows in a table.

True False

The MAX function determines the maximum value from a set of values, not the total number of rows. To get the total number of rows, use the COUNT function.

Q11

True / False

The COUNT function can be used to count rows where a specific column value is null.

True False

The COUNT function does not count NULL values. To count rows where a column value is NULL, use `COUNT(*)` and filter by `WHERE column IS NULL`.

Q12

True / False

The SUM function can be used with the HAVING clause to filter groups by total revenue.

True False

The SUM function can be used with the HAVING clause to filter groups based on their total revenue. For example, `SELECT Department, SUM(Revenue) FROM Sales GROUP BY Department HAVING SUM(Revenue) > 10000;` filters departments with total revenue greater than 10000.

Q13

True / False

The AVG function can be used to find the total number of employees in each department.

True False

The AVG function calculates the average value, not the total number of employees. To find the total number of employees in each department, use the COUNT function and group by department.

Q14

True / False

The MIN function can be used to find the smallest sale amount for each salesperson.

True False

The MIN function returns the smallest value in a column. To find the smallest sale amount for each salesperson, you can use `SELECT SalespersonID, MIN(SaleAmount) FROM Sales GROUP BY SalespersonID;`.

Q15

True / False

The MAX function can be used to find the average salary of employees.

True False

The MAX function returns the maximum value, not the average. To find the average salary of employees, use the AVG function.

Q16

Single Choice

Which function is used to find the total number of products sold?

SQL Code

SELECT SUM(Quantity) FROM Sales;

COUNT SUM AVGMIN

The SUM function is used to find the total number of products sold by summing up the values in the Quantity column.

Q17

Single Choice

Which function would you use to find the highest salary in a table?

SQL Code

SELECT MAX(Salary) FROM Employees;

COUNT SUM AVGMAX

The MAX function is used to find the highest salary from a table of employees.

Q18

Single Choice

To calculate the average order value, which function should be used?

SQL Code

SELECT AVG(OrderValue) FROM Orders;

COUNT SUM AVGMIN

The AVG function calculates the average value of a specified column, such as OrderValue.

Q19

Single Choice

Which function calculates the smallest price in a table?

SQL Code

SELECT MIN(Price) FROM Products;

COUNT SUM AVGMIN

The MIN function calculates the smallest price from the Products table.

Q20

Single Choice

Which function calculates the total revenue from sales?

SQL Code

SELECT SUM(Revenue) FROM Sales;

COUNT SUM AVGMAX

The SUM function calculates the total revenue by summing up the values in the Revenue column.

Q21

Single Choice

Which function is used to count the number of unique customers?

SQL Code

SELECT COUNT(DISTINCT CustomerID) FROM Orders;

COUNT SUM AVGMAX

The COUNT function with DISTINCT counts the number of unique customers based on the CustomerID column.

Q22

Single Choice

To get the total number of employees in each department, which function is used?

SQL Code

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

COUNT SUM AVGMIN

The COUNT function is used to find the total number of employees in each department when grouped by DepartmentID.

Q23

Single Choice

To determine the highest sale amount for each product, which function should be used?

SQL Code

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

COUNT SUM AVGMAX

The MAX function determines the highest sale amount for each product by grouping the results by ProductID.

Q24

Single Choice

Which function would be used to find the average discount applied to orders?

SQL Code

SELECT AVG(Discount) FROM Orders;

COUNT SUM AVGMIN

The AVG function calculates the average discount applied to orders by averaging the values in the Discount column.

Q25

Single Choice

To calculate the total quantity sold for each category, which function should be used?

SQL Code

SELECT CategoryID, SUM(Quantity) FROM Sales GROUP BY CategoryID;

COUNT SUM AVGMAX

The SUM function calculates the total quantity sold for each category when grouped by CategoryID.

Q26

Single Choice

To find the smallest order amount from each customer, which function should be used?

SQL Code

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

COUNT SUM AVGMIN

The MIN function finds the smallest order amount for each customer by grouping the results by CustomerID.

Q27

Single Choice

Which function is used to calculate the average salary of employees in each department?

SQL Code

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

COUNT SUM AVGMAX

The AVG function calculates the average salary for employees in each department when grouped by DepartmentID.

Q28

Single Choice

To determine the total revenue for each region, which function should be used?

SQL Code

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

COUNT SUM AVGMAX

The SUM function calculates the total revenue for each region by grouping the results by Region.

Q29

Single Choice

Which function calculates the total number of orders placed?

SQL Code

SELECT COUNT(OrderID) FROM Orders;

COUNT SUM AVGMIN

The COUNT function is used to calculate the total number of orders by counting the OrderID values.

Q30

Single Choice

Which function finds the highest price of products?

SQL Code

SELECT MAX(Price) FROM Products;

COUNT SUM AVGMAX

The MAX function is used to find the highest price among all products.

Q31

Multiple Choice

Which SQL query calculates the total revenue for each product category?

SQL Code

SELECT CategoryID, SUM(Revenue)
FROM Sales
GROUP BY CategoryID;

SELECT CategoryName, SUM(Revenue)
FROM Sales
JOIN Categories ON Sales.CategoryID = Categories.CategoryID
GROUP BY CategoryName;

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

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

SELECT CategoryID, SUM(Revenue) FROM Sales GROUP BY CategoryID; SELECT CategoryName, SUM(Revenue) FROM Sales JOIN Categories ON Sales.CategoryID = Categories.CategoryID GROUP BY CategoryName; SELECT CategoryID, SUM(SaleAmount) FROM Sales GROUP BY CategoryID;SELECT CategoryID, SUM(OrderAmount) FROM Orders GROUP BY CategoryID;

These queries use the SUM function to calculate the total revenue for each product category, grouped by CategoryID or CategoryName.

Q32

Multiple Choice

Which query finds the average salary of employees in each department?

SQL Code

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

SELECT DepartmentName, AVG(Salary)
FROM Employees
JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID
GROUP BY DepartmentName;

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

SELECT DepartmentID, AVG(Salary)
FROM Employees
WHERE Salary IS NOT NULL
GROUP BY DepartmentID;

SELECT DepartmentID, AVG(Salary) FROM Employees GROUP BY DepartmentID; SELECT DepartmentName, AVG(Salary) FROM Employees JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID GROUP BY DepartmentName; SELECT DepartmentID, AVG(Salary) FROM Employees GROUP BY DepartmentID HAVING AVG(Salary) > 50000;SELECT DepartmentID, AVG(Salary) FROM Employees WHERE Salary IS NOT NULL GROUP BY DepartmentID;

These queries use the AVG function to calculate the average salary for employees grouped by DepartmentID or DepartmentName.

Q33

Multiple Choice

Which queries calculate the smallest and largest order amounts for each customer?

SQL Code

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

SELECT CustomerID, MIN(Amount), MAX(Amount)
FROM Orders
GROUP BY CustomerID;

SELECT CustomerID, MIN(TotalAmount), MAX(TotalAmount)
FROM Invoices
GROUP BY CustomerID;

SELECT CustomerID, MIN(PurchaseAmount), MAX(PurchaseAmount)
FROM Transactions
GROUP BY CustomerID;

SELECT CustomerID, MIN(OrderAmount), MAX(OrderAmount) FROM Orders GROUP BY CustomerID; SELECT CustomerID, MIN(Amount), MAX(Amount) FROM Orders GROUP BY CustomerID; SELECT CustomerID, MIN(TotalAmount), MAX(TotalAmount) FROM Invoices GROUP BY CustomerID;SELECT CustomerID, MIN(PurchaseAmount), MAX(PurchaseAmount) FROM Transactions GROUP BY CustomerID;

These queries use the MIN and MAX functions to find the smallest and largest order amounts for each customer.

Q34

Multiple Choice

Which function is used to calculate the total number of orders with a discount greater than 10?

SQL Code

SELECT COUNT(OrderID)
FROM Orders
WHERE Discount > 10;

SELECT SUM(Quantity)
FROM Orders
WHERE Discount > 10;

SELECT AVG(OrderAmount)
FROM Orders
WHERE Discount > 10;

SELECT MAX(OrderAmount)
FROM Orders
WHERE Discount > 10;

SELECT COUNT(OrderID) FROM Orders WHERE Discount > 10; SELECT SUM(Quantity) FROM Orders WHERE Discount > 10; SELECT AVG(OrderAmount) FROM Orders WHERE Discount > 10;SELECT MAX(OrderAmount) FROM Orders WHERE Discount > 10;

The COUNT function is used to calculate the number of orders with a discount greater than 10. The other functions perform different operations.

Q35

Multiple Choice

To find the average discount given in each region, which function should be used?

SQL Code

SELECT Region, AVG(Discount)
FROM Orders
GROUP BY Region;

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

SELECT Region, SUM(Discount)
FROM Orders
GROUP BY Region;

SELECT Region, MIN(Discount)
FROM Orders
GROUP BY Region;

SELECT Region, AVG(Discount) FROM Orders GROUP BY Region; SELECT Region, AVG(Discount) FROM Sales GROUP BY Region; SELECT Region, SUM(Discount) FROM Orders GROUP BY Region;SELECT Region, MIN(Discount) FROM Orders GROUP BY Region;

The AVG function calculates the average discount for each region by grouping the results by Region. The other functions perform different operations.

Q36

Multiple Choice

Which queries calculate the total and average order amounts for each product?

SQL Code

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

SELECT ProductID, COUNT(OrderID), AVG(OrderAmount)
FROM Orders
GROUP BY ProductID;

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

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

SELECT ProductID, SUM(OrderAmount), AVG(OrderAmount) FROM Orders GROUP BY ProductID; SELECT ProductID, COUNT(OrderID), AVG(OrderAmount) FROM Orders GROUP BY ProductID; SELECT ProductID, SUM(OrderAmount) FROM Orders GROUP BY ProductID;SELECT ProductID, AVG(OrderAmount) FROM Orders GROUP BY ProductID;

These queries use SUM and AVG functions to calculate the total and average order amounts for each product.

Q37

Multiple Choice

Which function would be used to find the highest value in a list of discounts?

SQL Code

SELECT MAX(Discount)
FROM Orders;

SELECT MIN(Discount)
FROM Orders;

SELECT AVG(Discount)
FROM Orders;

SELECT SUM(Discount)
FROM Orders;

SELECT MAX(Discount) FROM Orders; SELECT MIN(Discount) FROM Orders; SELECT AVG(Discount) FROM Orders;SELECT SUM(Discount) FROM Orders;

The MAX function is used to find the highest value in a list of discounts. The other functions perform different operations.

Q38

Multiple Choice

To calculate the smallest discount given in each department, which function should be used?

SQL Code

SELECT DepartmentID, MIN(Discount)
FROM Orders
GROUP BY DepartmentID;

SELECT DepartmentName, MIN(Discount)
FROM Orders
JOIN Departments ON Orders.DepartmentID = Departments.DepartmentID
GROUP BY DepartmentName;

SELECT DepartmentID, MIN(Discount)
FROM Sales
GROUP BY DepartmentID;

SELECT DepartmentID, MAX(Discount)
FROM Orders
GROUP BY DepartmentID;

SELECT DepartmentID, MIN(Discount) FROM Orders GROUP BY DepartmentID; SELECT DepartmentName, MIN(Discount) FROM Orders JOIN Departments ON Orders.DepartmentID = Departments.DepartmentID GROUP BY DepartmentName; SELECT DepartmentID, MIN(Discount) FROM Sales GROUP BY DepartmentID;SELECT DepartmentID, MAX(Discount) FROM Orders GROUP BY DepartmentID;

The MIN function is used to calculate the smallest discount given in each department, grouped by DepartmentID or DepartmentName.

Q39

Multiple Choice

Which queries use the SUM function to calculate the total sales for each product?

SQL Code

SELECT ProductID, SUM(SalesAmount)
FROM Sales
GROUP BY ProductID;

SELECT ProductName, SUM(SalesAmount)
FROM Sales
JOIN Products ON Sales.ProductID = Products.ProductID
GROUP BY ProductName;

SELECT ProductID, SUM(Quantity)
FROM Orders
GROUP BY ProductID;

SELECT ProductID, COUNT(SalesAmount)
FROM Sales
GROUP BY ProductID;

SELECT ProductID, SUM(SalesAmount) FROM Sales GROUP BY ProductID; SELECT ProductName, SUM(SalesAmount) FROM Sales JOIN Products ON Sales.ProductID = Products.ProductID GROUP BY ProductName; SELECT ProductID, SUM(Quantity) FROM Orders GROUP BY ProductID;SELECT ProductID, COUNT(SalesAmount) FROM Sales GROUP BY ProductID;

These queries use the SUM function to calculate the total sales for each product, either by SalesAmount or Quantity.

Q40

Multiple Choice

Which function is used to find the average quantity of products ordered in each region?

SQL Code

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

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

SELECT Region, SUM(Quantity)
FROM Orders
GROUP BY Region;

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

SELECT Region, AVG(Quantity) FROM Orders GROUP BY Region; SELECT Region, AVG(OrderAmount) FROM Orders GROUP BY Region; SELECT Region, SUM(Quantity) FROM Orders GROUP BY Region;SELECT Region, COUNT(OrderID) FROM Orders GROUP BY Region;

The AVG function calculates the average quantity of products ordered in each region by grouping the results by Region. The other functions perform different operations.

Q41

Multiple Choice

Which function calculates the total number of distinct orders placed by each customer?

SQL Code

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

SELECT CustomerID, COUNT(DISTINCT OrderAmount)
FROM Orders
GROUP BY CustomerID;

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

SELECT CustomerID, COUNT(DISTINCT CustomerName)
FROM Orders
GROUP BY CustomerID;

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

The COUNT function with DISTINCT counts the total number of unique orders placed by each customer.

Q42

Multiple Choice

To find the average order amount for each product, which function is appropriate?

SQL Code

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

SELECT ProductName, AVG(OrderAmount)
FROM Orders
JOIN Products ON Orders.ProductID = Products.ProductID
GROUP BY ProductName;

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

SELECT ProductID, COUNT(OrderAmount)
FROM Orders
GROUP BY ProductID;

SELECT ProductID, AVG(OrderAmount) FROM Orders GROUP BY ProductID; SELECT ProductName, AVG(OrderAmount) FROM Orders JOIN Products ON Orders.ProductID = Products.ProductID GROUP BY ProductName; SELECT ProductID, SUM(OrderAmount) FROM Orders GROUP BY ProductID;SELECT ProductID, COUNT(OrderAmount) FROM Orders GROUP BY ProductID;

The AVG function calculates the average order amount for each product, grouped by ProductID or ProductName.

Q43

Multiple Choice

Which queries can be used to find the maximum and minimum order amounts?

SQL Code

SELECT MAX(OrderAmount), MIN(OrderAmount)
FROM Orders;

SELECT MAX(SaleAmount), MIN(SaleAmount)
FROM Sales;

SELECT MAX(Discount), MIN(Discount)
FROM Orders;

SELECT MAX(TotalAmount), MIN(TotalAmount)
FROM Invoices;

SELECT MAX(OrderAmount), MIN(OrderAmount) FROM Orders; SELECT MAX(SaleAmount), MIN(SaleAmount) FROM Sales; SELECT MAX(Discount), MIN(Discount) FROM Orders;SELECT MAX(TotalAmount), MIN(TotalAmount) FROM Invoices;

These queries use the MAX and MIN functions to find the highest and lowest values for order amounts, sales, discounts, and total amounts.

Q44

Multiple Choice

Which function calculates the total number of records in a table?

SQL Code

SELECT COUNT(*)
FROM Orders;

SELECT COUNT(OrderID)
FROM Orders;

SELECT COUNT(DISTINCT OrderID)
FROM Orders;

SELECT SUM(Quantity)
FROM Orders;

SELECT COUNT(*) FROM Orders; SELECT COUNT(OrderID) FROM Orders; SELECT COUNT(DISTINCT OrderID) FROM Orders;SELECT SUM(Quantity) FROM Orders;

The COUNT function (with or without DISTINCT) calculates the total number of records in a table. The SUM function calculates the total of a numeric column.

Q45

Multiple Choice

Which queries use the COUNT function to determine the number of orders placed by each employee?

SQL Code

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

SELECT EmployeeName, COUNT(OrderID)
FROM Orders
JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
GROUP BY EmployeeName;

SELECT EmployeeID, COUNT(DISTINCT OrderID)
FROM Orders
GROUP BY EmployeeID;

SELECT EmployeeID, COUNT(OrderAmount)
FROM Orders
GROUP BY EmployeeID;

SELECT EmployeeID, COUNT(OrderID) FROM Orders GROUP BY EmployeeID; SELECT EmployeeName, COUNT(OrderID) FROM Orders JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID GROUP BY EmployeeName; SELECT EmployeeID, COUNT(DISTINCT OrderID) FROM Orders GROUP BY EmployeeID;SELECT EmployeeID, COUNT(OrderAmount) FROM Orders GROUP BY EmployeeID;

These queries use the COUNT function to determine the number of orders placed by each employee, either by EmployeeID or EmployeeName.

Microsoft SQL Server Database Quiz Questions