Test Your Microsoft SQL Server CASE Statement Knowledge with This Quiz

Progress

True / False

The CASE statement can be used in both SELECT and WHERE clauses.

True False

The CASE statement can be used in various parts of a SQL query, including SELECT, WHERE, ORDER BY, and others.

True / False

The CASE statement requires a THEN clause for every WHEN clause.

True False

Each WHEN clause in a CASE statement must have a corresponding THEN clause to specify the result for the condition.

True / False

The CASE statement can handle NULL values in SQL queries.

True False

The CASE statement can be used to handle NULL values by including specific conditions for NULL cases.

True / False

The CASE statement can be nested within another CASE statement.

True False

Nesting of CASE statements is allowed in SQL Server, which can be used to handle complex conditional logic.

True / False

The CASE statement can be used to update records in a table.

True False

The CASE statement can be used in the SET clause of an UPDATE statement to conditionally update records.

True / False

A CASE statement must have at least one WHEN clause.

True False

A CASE statement must have at least one WHEN clause to specify a condition and a corresponding THEN clause for the result.

True / False

The CASE statement supports multiple ELSE clauses.

True False

The CASE statement supports only one ELSE clause, which provides a default result if none of the WHEN conditions are met.

True / False

The CASE statement can be used in aggregate functions like SUM and AVG.

True False

The CASE statement can be used within aggregate functions to conditionally include or exclude values in the calculations.

True / False

The CASE statement can only be used in SELECT queries.

True False

The CASE statement can be used in various SQL statements, not just SELECT queries, but also in WHERE, ORDER BY, and UPDATE clauses.

Q10

True / False

In a CASE statement, if no WHEN conditions are met, the ELSE clause is executed.

True False

If none of the WHEN conditions are met in a CASE statement, the ELSE clause is executed, providing a default result.

Q11

True / False

The CASE statement can only return values of the same data type.

True False

All return values in a CASE statement must be of the same data type or implicitly convertible to the same type.

Q12

True / False

The CASE statement can be used to conditionally format the result set.

True False

The CASE statement can be used to conditionally format or modify the results returned by a query.

Q13

True / False

The CASE statement can be used in combination with other functions, such as CONCAT or SUBSTRING.

True False

The CASE statement can be combined with other functions to perform complex data manipulations and conditional logic.

Q14

True / False

The CASE statement must be used in the SELECT clause only.

True False

The CASE statement can be used in various clauses of a SQL query, including SELECT, WHERE, ORDER BY, and UPDATE.

Q15

True / False

The CASE statement can be used to replace the IF...ELSE control-of-flow statement.

True False

The CASE statement can handle conditional logic in queries, but it's not a direct replacement for control-of-flow statements like IF...ELSE in T-SQL procedures.

Q16

Single Choice

Which SQL statement uses the CASE statement to categorize employees into 'High', 'Medium', and 'Low' salary groups?

SQL Code

SELECT EmployeeName,
CASE
 WHEN Salary > 80000 THEN 'High'
 WHEN Salary BETWEEN 50000 AND 80000 THEN 'Medium'
 ELSE 'Low'
END AS SalaryGroup
FROM Employees;

SELECT EmployeeName, CASE WHEN Salary > 80000 THEN 'High' WHEN Salary BETWEEN 50000 AND 80000 THEN 'Medium' ELSE 'Low' END AS SalaryGroup FROM Employees; SELECT EmployeeName, CASE WHEN Salary < 50000 THEN 'Low' WHEN Salary BETWEEN 50000 AND 80000 THEN 'Medium' ELSE 'High' END AS SalaryGroup FROM Employees; SELECT EmployeeName, CASE WHEN Salary > 80000 THEN 'Low' WHEN Salary BETWEEN 50000 AND 80000 THEN 'High' ELSE 'Medium' END AS SalaryGroup FROM Employees;SELECT EmployeeName, CASE WHEN Salary BETWEEN 50000 AND 80000 THEN 'Medium' ELSE 'High' END AS SalaryGroup FROM Employees;

The CASE statement categorizes employees into different salary groups based on their salary amounts.

Q17

Single Choice

Which query correctly uses the CASE statement to assign grades based on scores?

SQL Code

SELECT StudentName,
CASE
 WHEN Score >= 90 THEN 'A'
 WHEN Score >= 80 THEN 'B'
 WHEN Score >= 70 THEN 'C'
 ELSE 'D'
END AS Grade
FROM Students;

SELECT StudentName, CASE WHEN Score >= 90 THEN 'A' WHEN Score >= 80 THEN 'B' WHEN Score >= 70 THEN 'C' ELSE 'D' END AS Grade FROM Students; SELECT StudentName, CASE WHEN Score >= 90 THEN 'B' WHEN Score >= 80 THEN 'C' WHEN Score >= 70 THEN 'D' ELSE 'A' END AS Grade FROM Students; SELECT StudentName, CASE WHEN Score >= 80 THEN 'A' WHEN Score >= 70 THEN 'B' WHEN Score >= 60 THEN 'C' ELSE 'D' END AS Grade FROM Students;SELECT StudentName, CASE WHEN Score < 70 THEN 'D' WHEN Score BETWEEN 70 AND 80 THEN 'C' WHEN Score BETWEEN 80 AND 90 THEN 'B' ELSE 'A' END AS Grade FROM Students;

The CASE statement assigns grades based on score ranges, providing a conditional evaluation for each grade.

Q18

Single Choice

What is the correct syntax for using CASE in an ORDER BY clause?

SQL Code

SELECT ProductName, Price
FROM Products
ORDER BY
CASE
 WHEN Price < 20 THEN 'Cheap'
 WHEN Price BETWEEN 20 AND 50 THEN 'Moderate'
 ELSE 'Expensive'
END;

SELECT ProductName, Price FROM Products ORDER BY CASE WHEN Price < 20 THEN 'Cheap' WHEN Price BETWEEN 20 AND 50 THEN 'Moderate' ELSE 'Expensive' END; SELECT ProductName, Price FROM Products ORDER BY CASE WHEN Price < 20 THEN 'Expensive' WHEN Price BETWEEN 20 AND 50 THEN 'Cheap' ELSE 'Moderate' END; SELECT ProductName, Price FROM Products ORDER BY CASE WHEN Price BETWEEN 20 AND 50 THEN 'Cheap' ELSE 'Expensive' END;SELECT ProductName, Price FROM Products ORDER BY CASE WHEN Price < 50 THEN 'Expensive' ELSE 'Cheap' END;

The CASE statement in ORDER BY allows sorting based on conditional logic applied to column values.

Q19

Single Choice

Which query uses CASE to display different messages based on the quantity of items in stock?

SQL Code

SELECT ProductName,
CASE
 WHEN QuantityInStock > 100 THEN 'In Stock'
 WHEN QuantityInStock BETWEEN 50 AND 100 THEN 'Low Stock'
 ELSE 'Out of Stock'
END AS StockStatus
FROM Inventory;

SELECT ProductName, CASE WHEN QuantityInStock > 100 THEN 'In Stock' WHEN QuantityInStock BETWEEN 50 AND 100 THEN 'Low Stock' ELSE 'Out of Stock' END AS StockStatus FROM Inventory; SELECT ProductName, CASE WHEN QuantityInStock > 100 THEN 'Low Stock' WHEN QuantityInStock BETWEEN 50 AND 100 THEN 'In Stock' ELSE 'Out of Stock' END AS StockStatus FROM Inventory; SELECT ProductName, CASE WHEN QuantityInStock < 50 THEN 'In Stock' ELSE 'Out of Stock' END AS StockStatus FROM Inventory;SELECT ProductName, CASE WHEN QuantityInStock < 100 THEN 'Out of Stock' ELSE 'In Stock' END AS StockStatus FROM Inventory;

The CASE statement provides different stock status messages based on the quantity in stock.

Q20

Single Choice

Which statement is correct about using CASE with numeric comparisons?

SQL Code

SELECT EmployeeName,
CASE
 WHEN Age < 30 THEN 'Young'
 WHEN Age BETWEEN 30 AND 50 THEN 'Middle-aged'
 ELSE 'Senior'
END AS AgeGroup
FROM Employees;

SELECT EmployeeName, CASE WHEN Age < 30 THEN 'Young' WHEN Age BETWEEN 30 AND 50 THEN 'Middle-aged' ELSE 'Senior' END AS AgeGroup FROM Employees; SELECT EmployeeName, CASE WHEN Age < 30 THEN 'Senior' WHEN Age BETWEEN 30 AND 50 THEN 'Young' ELSE 'Middle-aged' END AS AgeGroup FROM Employees; SELECT EmployeeName, CASE WHEN Age BETWEEN 30 AND 50 THEN 'Young' ELSE 'Senior' END AS AgeGroup FROM Employees;SELECT EmployeeName, CASE WHEN Age > 50 THEN 'Young' ELSE 'Senior' END AS AgeGroup FROM Employees;

The CASE statement can be used with numeric comparisons to categorize data based on value ranges.

Q21

Single Choice

Which syntax correctly uses CASE to return a value based on a column's value in a WHERE clause?

SQL Code

SELECT ProductName
FROM Products
WHERE
CASE
 WHEN Price < 50 THEN 1
 ELSE 0
END = 1;

SELECT ProductName FROM Products WHERE CASE WHEN Price < 50 THEN 1 ELSE 0 END = 1; SELECT ProductName FROM Products WHERE CASE WHEN Price < 50 THEN 'Low' ELSE 'High' END = 'Low'; SELECT ProductName FROM Products WHERE CASE WHEN Price < 50 THEN 'High' ELSE 'Low' END = 'High';SELECT ProductName FROM Products WHERE CASE WHEN Price >= 50 THEN 1 ELSE 0 END = 0;

The CASE statement can be used in the WHERE clause to include conditional logic for filtering results.

Q22

Single Choice

Which query uses CASE to determine the commission rate based on sales volume?

SQL Code

SELECT SalesPerson,
CASE
 WHEN SalesAmount > 100000 THEN 0.1
 WHEN SalesAmount BETWEEN 50000 AND 100000 THEN 0.07
 ELSE 0.05
END AS CommissionRate
FROM Sales;

SELECT SalesPerson, CASE WHEN SalesAmount > 100000 THEN 0.1 WHEN SalesAmount BETWEEN 50000 AND 100000 THEN 0.07 ELSE 0.05 END AS CommissionRate FROM Sales; SELECT SalesPerson, CASE WHEN SalesAmount > 100000 THEN 0.05 WHEN SalesAmount BETWEEN 50000 AND 100000 THEN 0.07 ELSE 0.1 END AS CommissionRate FROM Sales; SELECT SalesPerson, CASE WHEN SalesAmount < 50000 THEN 0.1 WHEN SalesAmount BETWEEN 50000 AND 100000 THEN 0.07 ELSE 0.05 END AS CommissionRate FROM Sales;SELECT SalesPerson, CASE WHEN SalesAmount < 50000 THEN 0.07 WHEN SalesAmount BETWEEN 50000 AND 100000 THEN 0.1 ELSE 0.05 END AS CommissionRate FROM Sales;

The CASE statement calculates the commission rate based on sales volume, providing different rates for different ranges.

Q23

Single Choice

Which query demonstrates using CASE with a text column?

SQL Code

SELECT ProductName,
CASE
 WHEN Category = 'Electronics' THEN 'Electronics Category'
 WHEN Category = 'Clothing' THEN 'Clothing Category'
 ELSE 'Other'
END AS ProductCategory
FROM Products;

SELECT ProductName, CASE WHEN Category = 'Electronics' THEN 'Electronics Category' WHEN Category = 'Clothing' THEN 'Clothing Category' ELSE 'Other' END AS ProductCategory FROM Products; SELECT ProductName, CASE WHEN Category = 'Electronics' THEN 'Other' WHEN Category = 'Clothing' THEN 'Electronics Category' ELSE 'Clothing Category' END AS ProductCategory FROM Products; SELECT ProductName, CASE WHEN Category = 'Clothing' THEN 'Electronics Category' ELSE 'Other' END AS ProductCategory FROM Products;SELECT ProductName, CASE WHEN Category = 'Electronics' THEN 'Electronics' ELSE 'Other' END AS ProductCategory FROM Products;

The CASE statement evaluates the text column to categorize products based on their category.

Q24

Single Choice

Which syntax shows a correct use of CASE to handle NULL values?

SQL Code

SELECT EmployeeName,
CASE
 WHEN Commission IS NULL THEN 'No Commission'
 ELSE 'Has Commission'
END AS CommissionStatus
FROM Employees;

SELECT EmployeeName, CASE WHEN Commission IS NULL THEN 'No Commission' ELSE 'Has Commission' END AS CommissionStatus FROM Employees; SELECT EmployeeName, CASE WHEN Commission IS NOT NULL THEN 'No Commission' ELSE 'Has Commission' END AS CommissionStatus FROM Employees; SELECT EmployeeName, CASE WHEN Commission IS NULL THEN 'Has Commission' ELSE 'No Commission' END AS CommissionStatus FROM Employees;SELECT EmployeeName, CASE WHEN Commission IS NULL THEN 'Unknown Commission' ELSE 'Known Commission' END AS CommissionStatus FROM Employees;

The CASE statement can handle NULL values to provide specific results when a column contains NULLs.

Q25

Single Choice

Which query correctly uses CASE to assign labels based on a numeric column?

SQL Code

SELECT ProductName,
CASE
 WHEN Discount > 20 THEN 'High Discount'
 WHEN Discount BETWEEN 10 AND 20 THEN 'Medium Discount'
 ELSE 'Low Discount'
END AS DiscountLabel
FROM Products;

SELECT ProductName, CASE WHEN Discount > 20 THEN 'High Discount' WHEN Discount BETWEEN 10 AND 20 THEN 'Medium Discount' ELSE 'Low Discount' END AS DiscountLabel FROM Products; SELECT ProductName, CASE WHEN Discount > 20 THEN 'Low Discount' WHEN Discount BETWEEN 10 AND 20 THEN 'High Discount' ELSE 'Medium Discount' END AS DiscountLabel FROM Products; SELECT ProductName, CASE WHEN Discount BETWEEN 10 AND 20 THEN 'High Discount' ELSE 'Low Discount' END AS DiscountLabel FROM Products;SELECT ProductName, CASE WHEN Discount < 10 THEN 'High Discount' ELSE 'Low Discount' END AS DiscountLabel FROM Products;

The CASE statement categorizes products based on discount values, providing different labels for different ranges.

Q26

Single Choice

Which query correctly uses CASE to generate different output based on a Boolean column?

SQL Code

SELECT CustomerName,
CASE
 WHEN IsActive = 1 THEN 'Active'
 ELSE 'Inactive'
END AS Status
FROM Customers;

SELECT CustomerName, CASE WHEN IsActive = 1 THEN 'Active' ELSE 'Inactive' END AS Status FROM Customers; SELECT CustomerName, CASE WHEN IsActive = 1 THEN 'Inactive' ELSE 'Active' END AS Status FROM Customers; SELECT CustomerName, CASE WHEN IsActive = 0 THEN 'Active' ELSE 'Inactive' END AS Status FROM Customers;SELECT CustomerName, CASE WHEN IsActive = 0 THEN 'Inactive' ELSE 'Active' END AS Status FROM Customers;

The CASE statement evaluates a Boolean column to provide different output values based on the condition.

Q27

Single Choice

Which query uses CASE to create a label based on a date column?

SQL Code

SELECT OrderID,
CASE
 WHEN OrderDate < '2024-01-01' THEN 'Old Order'
 ELSE 'New Order'
END AS OrderType
FROM Orders;

SELECT OrderID, CASE WHEN OrderDate < '2024-01-01' THEN 'Old Order' ELSE 'New Order' END AS OrderType FROM Orders; SELECT OrderID, CASE WHEN OrderDate >= '2024-01-01' THEN 'Old Order' ELSE 'New Order' END AS OrderType FROM Orders; SELECT OrderID, CASE WHEN OrderDate < '2024-01-01' THEN 'New Order' ELSE 'Old Order' END AS OrderType FROM Orders;SELECT OrderID, CASE WHEN OrderDate BETWEEN '2023-01-01' AND '2024-01-01' THEN 'Old Order' ELSE 'New Order' END AS OrderType FROM Orders;

The CASE statement categorizes orders based on their order date, providing labels for different date ranges.

Q28

Single Choice

Which query uses CASE to assign discount levels based on order quantity?

SQL Code

SELECT OrderID,
CASE
 WHEN QuantityOrdered > 100 THEN 'High Discount'
 WHEN QuantityOrdered BETWEEN 50 AND 100 THEN 'Medium Discount'
 ELSE 'No Discount'
END AS DiscountLevel
FROM Orders;

SELECT OrderID, CASE WHEN QuantityOrdered > 100 THEN 'High Discount' WHEN QuantityOrdered BETWEEN 50 AND 100 THEN 'Medium Discount' ELSE 'No Discount' END AS DiscountLevel FROM Orders; SELECT OrderID, CASE WHEN QuantityOrdered < 50 THEN 'High Discount' WHEN QuantityOrdered BETWEEN 50 AND 100 THEN 'Medium Discount' ELSE 'No Discount' END AS DiscountLevel FROM Orders; SELECT OrderID, CASE WHEN QuantityOrdered > 100 THEN 'No Discount' WHEN QuantityOrdered BETWEEN 50 AND 100 THEN 'High Discount' ELSE 'Medium Discount' END AS DiscountLevel FROM Orders;SELECT OrderID, CASE WHEN QuantityOrdered BETWEEN 50 AND 100 THEN 'No Discount' ELSE 'High Discount' END AS DiscountLevel FROM Orders;

The CASE statement determines discount levels based on the quantity ordered, assigning different levels accordingly.

Q29

Single Choice

Which syntax is correct for using CASE to modify column values in an UPDATE statement?

SQL Code

UPDATE Employees
SET Salary =
CASE
 WHEN JobTitle = 'Manager' THEN Salary * 1.1
 WHEN JobTitle = 'Staff' THEN Salary * 1.05
 ELSE Salary
END;

UPDATE Employees SET Salary = CASE WHEN JobTitle = 'Manager' THEN Salary * 1.1 WHEN JobTitle = 'Staff' THEN Salary * 1.05 ELSE Salary END; UPDATE Employees SET Salary = CASE WHEN JobTitle = 'Manager' THEN Salary * 1.05 WHEN JobTitle = 'Staff' THEN Salary * 1.1 ELSE Salary END; UPDATE Employees SET Salary = CASE WHEN JobTitle = 'Manager' THEN Salary * 1.1 ELSE Salary * 1.05 END;UPDATE Employees SET Salary = CASE WHEN JobTitle = 'Manager' THEN Salary * 1.05 ELSE Salary * 1.1 END;

The CASE statement can be used in the SET clause of an UPDATE statement to conditionally modify column values based on specific criteria.

Q30

Single Choice

Which query uses CASE to handle multiple conditions with nested CASE statements?

SQL Code

SELECT EmployeeName,
CASE
 WHEN Salary > 50000 THEN
 CASE
 WHEN Experience > 10 THEN 'Senior High Salary'
 ELSE 'Junior High Salary'
 END
 ELSE 'Low Salary'
END AS SalaryLevel
FROM Employees;

SELECT EmployeeName, CASE WHEN Salary > 50000 THEN CASE WHEN Experience > 10 THEN 'Senior High Salary' ELSE 'Junior High Salary' END ELSE 'Low Salary' END AS SalaryLevel FROM Employees; SELECT EmployeeName, CASE WHEN Salary <= 50000 THEN CASE WHEN Experience > 10 THEN 'Senior Low Salary' ELSE 'Junior Low Salary' END ELSE 'High Salary' END AS SalaryLevel FROM Employees; SELECT EmployeeName, CASE WHEN Salary > 50000 THEN 'High Salary' ELSE CASE WHEN Experience > 10 THEN 'Senior Low Salary' ELSE 'Junior Low Salary' END END AS SalaryLevel FROM Employees;SELECT EmployeeName, CASE WHEN Salary > 50000 THEN 'High Salary' WHEN Salary <= 50000 THEN 'Low Salary' ELSE 'Unknown' END AS SalaryLevel FROM Employees;

Nested CASE statements can be used to handle multiple conditions and provide more granular results.

Q31

Multiple Choice

Which query demonstrates using CASE to evaluate and modify values within a SELECT statement?

SQL Code

SELECT OrderID,
CASE
 WHEN OrderAmount > 1000 THEN 'Large Order'
 WHEN OrderAmount BETWEEN 500 AND 1000 THEN 'Medium Order'
 ELSE 'Small Order'
END AS OrderSize,
CASE
 WHEN OrderDate < GETDATE() - 30 THEN 'Old Order'
 ELSE 'Recent Order'
END AS OrderAge
FROM Orders;

SELECT OrderID, CASE WHEN OrderAmount > 1000 THEN 'Large Order' WHEN OrderAmount BETWEEN 500 AND 1000 THEN 'Medium Order' ELSE 'Small Order' END AS OrderSize, CASE WHEN OrderDate < GETDATE() - 30 THEN 'Old Order' ELSE 'Recent Order' END AS OrderAge FROM Orders; SELECT OrderID, CASE WHEN OrderAmount > 1000 THEN 'Medium Order' WHEN OrderAmount BETWEEN 500 AND 1000 THEN 'Large Order' ELSE 'Small Order' END AS OrderSize, CASE WHEN OrderDate > GETDATE() - 30 THEN 'Old Order' ELSE 'Recent Order' END AS OrderAge FROM Orders; SELECT OrderID, CASE WHEN OrderAmount > 1000 THEN 'Large Order' ELSE 'Small Order' END AS OrderSize, CASE WHEN OrderDate < GETDATE() - 30 THEN 'Recent Order' ELSE 'Old Order' END AS OrderAge FROM Orders;SELECT OrderID, CASE WHEN OrderAmount BETWEEN 500 AND 1000 THEN 'Medium Order' ELSE 'Small Order' END AS OrderSize, CASE WHEN OrderDate < GETDATE() - 30 THEN 'Old Order' ELSE 'Recent Order' END AS OrderAge FROM Orders;

The CASE statement evaluates different conditions to modify and categorize column values in a SELECT statement.

Q32

Multiple Choice

Which query uses CASE to generate different outputs based on varying ranges of a numeric column?

SQL Code

SELECT ProductName,
CASE
 WHEN Price < 10 THEN 'Budget'
 WHEN Price BETWEEN 10 AND 50 THEN 'Standard'
 ELSE 'Premium'
END AS PriceCategory,
CASE
 WHEN Stock < 10 THEN 'Low Stock'
 WHEN Stock BETWEEN 10 AND 50 THEN 'Medium Stock'
 ELSE 'High Stock'
END AS StockLevel
FROM Products;

SELECT ProductName, CASE WHEN Price < 10 THEN 'Budget' WHEN Price BETWEEN 10 AND 50 THEN 'Standard' ELSE 'Premium' END AS PriceCategory, CASE WHEN Stock < 10 THEN 'Low Stock' WHEN Stock BETWEEN 10 AND 50 THEN 'Medium Stock' ELSE 'High Stock' END AS StockLevel FROM Products; SELECT ProductName, CASE WHEN Price BETWEEN 10 AND 50 THEN 'Budget' ELSE 'Premium' END AS PriceCategory, CASE WHEN Stock BETWEEN 10 AND 50 THEN 'Low Stock' ELSE 'High Stock' END AS StockLevel FROM Products; SELECT ProductName, CASE WHEN Price < 10 THEN 'Budget' WHEN Price BETWEEN 10 AND 50 THEN 'Standard' ELSE 'Premium' END AS PriceCategory, CASE WHEN Stock < 10 THEN 'High Stock' ELSE 'Medium Stock' END AS StockLevel FROM Products;SELECT ProductName, CASE WHEN Price < 10 THEN 'Standard' WHEN Price BETWEEN 10 AND 50 THEN 'Premium' ELSE 'Budget' END AS PriceCategory, CASE WHEN Stock BETWEEN 10 AND 50 THEN 'Medium Stock' ELSE 'High Stock' END AS StockLevel FROM Products;

The CASE statement can evaluate and categorize values based on different ranges of numeric columns.

Q33

Multiple Choice

Which query correctly uses CASE in a GROUP BY clause to summarize data based on different categories?

SQL Code

SELECT
 CASE
 WHEN Rating >= 4 THEN 'High'
 WHEN Rating = 3 THEN 'Medium'
 ELSE 'Low'
 END AS RatingCategory,
 COUNT(*) AS NumberOfProducts
FROM Products
GROUP BY
 CASE
 WHEN Rating >= 4 THEN 'High'
 WHEN Rating = 3 THEN 'Medium'
 ELSE 'Low'
 END;

SELECT CASE WHEN Rating >= 4 THEN 'High' WHEN Rating = 3 THEN 'Medium' ELSE 'Low' END AS RatingCategory, COUNT(*) AS NumberOfProducts FROM Products GROUP BY CASE WHEN Rating >= 4 THEN 'High' WHEN Rating = 3 THEN 'Medium' ELSE 'Low' END; SELECT CASE WHEN Rating < 4 THEN 'High' ELSE 'Low' END AS RatingCategory, COUNT(*) AS NumberOfProducts FROM Products GROUP BY CASE WHEN Rating < 4 THEN 'High' ELSE 'Low' END; SELECT CASE WHEN Rating = 3 THEN 'High' ELSE 'Low' END AS RatingCategory, COUNT(*) AS NumberOfProducts FROM Products GROUP BY CASE WHEN Rating = 3 THEN 'High' ELSE 'Low' END;SELECT CASE WHEN Rating >= 4 THEN 'Low' ELSE 'High' END AS RatingCategory, COUNT(*) AS NumberOfProducts FROM Products GROUP BY CASE WHEN Rating >= 4 THEN 'Low' ELSE 'High' END;

The CASE statement within the GROUP BY clause allows you to group and summarize data based on different categories.

Q34

Multiple Choice

Which query demonstrates using CASE with aggregate functions to categorize results?

SQL Code

SELECT
 COUNT(CASE WHEN SalesAmount > 1000 THEN 1 END) AS HighSalesCount,
 AVG(CASE WHEN SalesAmount > 1000 THEN SalesAmount END) AS AvgHighSales
FROM Sales;

SELECT COUNT(CASE WHEN SalesAmount > 1000 THEN 1 END) AS HighSalesCount, AVG(CASE WHEN SalesAmount > 1000 THEN SalesAmount END) AS AvgHighSales FROM Sales; SELECT SUM(CASE WHEN SalesAmount > 1000 THEN SalesAmount ELSE 0 END) AS TotalHighSales, COUNT(CASE WHEN SalesAmount > 1000 THEN 1 END) AS HighSalesCount FROM Sales; SELECT AVG(CASE WHEN SalesAmount > 1000 THEN SalesAmount ELSE 0 END) AS AvgHighSales, SUM(CASE WHEN SalesAmount > 1000 THEN SalesAmount END) AS TotalHighSales FROM Sales;SELECT COUNT(CASE WHEN SalesAmount <= 1000 THEN 1 END) AS LowSalesCount, AVG(CASE WHEN SalesAmount <= 1000 THEN SalesAmount END) AS AvgLowSales FROM Sales;

The CASE statement can be used with aggregate functions to categorize and compute specific metrics based on conditions.

Q35

Single Choice

Which query correctly demonstrates the use of CASE for conditional data transformation in an UPDATE statement?

SQL Code

UPDATE Employees
SET Bonus =
CASE
 WHEN PerformanceRating = 'Excellent' THEN Salary * 0.1
 WHEN PerformanceRating = 'Good' THEN Salary * 0.05
 ELSE 0
END;

UPDATE Employees SET Bonus = CASE WHEN PerformanceRating = 'Excellent' THEN Salary * 0.1 WHEN PerformanceRating = 'Good' THEN Salary * 0.05 ELSE 0 END; UPDATE Employees SET Bonus = CASE WHEN PerformanceRating = 'Excellent' THEN Salary * 0.15 WHEN PerformanceRating = 'Good' THEN Salary * 0.05 ELSE 0 END; UPDATE Employees SET Bonus = CASE WHEN PerformanceRating = 'Excellent' THEN Salary * 0.1 ELSE Salary * 0.02 END;UPDATE Employees SET Bonus = CASE WHEN PerformanceRating = 'Excellent' THEN Salary * 0.1 WHEN PerformanceRating = 'Good' THEN Salary * 0.1 ELSE 0 END;

Using CASE in an UPDATE statement allows for conditional modifications based on different criteria.

Q36

Multiple Choice

Which query uses CASE to categorize and summarize employee data by department?

SQL Code

SELECT Department,
 COUNT(CASE WHEN Salary > 50000 THEN 1 END) AS HighEarners,
 AVG(CASE WHEN Salary > 50000 THEN Salary END) AS AvgHighSalary
FROM Employees
GROUP BY Department;

SELECT Department, COUNT(CASE WHEN Salary > 50000 THEN 1 END) AS HighEarners, AVG(CASE WHEN Salary > 50000 THEN Salary END) AS AvgHighSalary FROM Employees GROUP BY Department; SELECT Department, SUM(CASE WHEN Salary > 50000 THEN Salary ELSE 0 END) AS TotalHighSalaries, COUNT(CASE WHEN Salary > 50000 THEN 1 END) AS HighEarners FROM Employees GROUP BY Department; SELECT Department, COUNT(CASE WHEN Salary <= 50000 THEN 1 END) AS LowEarners, AVG(CASE WHEN Salary <= 50000 THEN Salary END) AS AvgLowSalary FROM Employees GROUP BY Department;SELECT Department, COUNT(CASE WHEN Salary > 50000 THEN 1 END) AS HighEarners, SUM(CASE WHEN Salary > 50000 THEN Salary ELSE 0 END) AS TotalHighSalaries FROM Employees GROUP BY Department;

The CASE statement can be used in conjunction with aggregate functions to categorize and summarize data by grouping criteria.

Q37

Multiple Choice

Which query correctly uses CASE to transform date-based conditions in a SELECT statement?

SQL Code

SELECT OrderID,
 CASE
 WHEN OrderDate BETWEEN DATEADD(MONTH, -1, GETDATE()) AND GETDATE() THEN 'Recent'
 WHEN OrderDate BETWEEN DATEADD(MONTH, -6, GETDATE()) AND DATEADD(MONTH, -1, GETDATE()) THEN 'Last 6 Months'
 ELSE 'Older'
 END AS OrderAge
FROM Orders;

SELECT OrderID, CASE WHEN OrderDate BETWEEN DATEADD(MONTH, -1, GETDATE()) AND GETDATE() THEN 'Recent' WHEN OrderDate BETWEEN DATEADD(MONTH, -6, GETDATE()) AND DATEADD(MONTH, -1, GETDATE()) THEN 'Last 6 Months' ELSE 'Older' END AS OrderAge FROM Orders; SELECT OrderID, CASE WHEN OrderDate BETWEEN DATEADD(MONTH, -6, GETDATE()) AND GETDATE() THEN 'Recent' WHEN OrderDate BETWEEN DATEADD(MONTH, -12, GETDATE()) AND DATEADD(MONTH, -6, GETDATE()) THEN 'Last 6 Months' ELSE 'Older' END AS OrderAge FROM Orders; SELECT OrderID, CASE WHEN OrderDate > DATEADD(MONTH, -1, GETDATE()) THEN 'Recent' ELSE 'Older' END AS OrderAge FROM Orders;SELECT OrderID, CASE WHEN OrderDate BETWEEN DATEADD(MONTH, -3, GETDATE()) AND GETDATE() THEN 'Recent' WHEN OrderDate BETWEEN DATEADD(MONTH, -6, GETDATE()) AND DATEADD(MONTH, -3, GETDATE()) THEN 'Last 3 Months' ELSE 'Older' END AS OrderAge FROM Orders;

The CASE statement can handle and transform data based on date-based conditions for categorization.

Q38

Multiple Choice

Which query demonstrates using CASE to evaluate and classify products based on multiple attributes?

SQL Code

SELECT ProductName,
 CASE
 WHEN Stock > 100 AND Price < 50 THEN 'High Stock, Low Price'
 WHEN Stock <= 100 AND Price >= 50 THEN 'Low Stock, High Price'
 ELSE 'Other'
 END AS ProductCategory
FROM Products;

SELECT ProductName, CASE WHEN Stock > 100 AND Price < 50 THEN 'High Stock, Low Price' WHEN Stock <= 100 AND Price >= 50 THEN 'Low Stock, High Price' ELSE 'Other' END AS ProductCategory FROM Products; SELECT ProductName, CASE WHEN Stock > 100 THEN 'High Stock' WHEN Price < 50 THEN 'Low Price' ELSE 'Other' END AS ProductCategory FROM Products; SELECT ProductName, CASE WHEN Price < 50 THEN 'Low Price' ELSE 'High Price' END AS PriceCategory FROM Products;SELECT ProductName, CASE WHEN Stock > 100 THEN 'High Stock' WHEN Stock <= 100 AND Price < 50 THEN 'Low Stock, Low Price' ELSE 'Other' END AS ProductCategory FROM Products;

The CASE statement can classify and evaluate data based on multiple attributes for detailed categorization.

Q39

Multiple Choice

Which query effectively uses CASE to format and display salary ranges in a SELECT statement?

SQL Code

SELECT EmployeeName,
 CASE
 WHEN Salary < 30000 THEN 'Below Average'
 WHEN Salary BETWEEN 30000 AND 60000 THEN 'Average'
 ELSE 'Above Average'
 END AS SalaryRange
FROM Employees;

SELECT EmployeeName, CASE WHEN Salary < 30000 THEN 'Below Average' WHEN Salary BETWEEN 30000 AND 60000 THEN 'Average' ELSE 'Above Average' END AS SalaryRange FROM Employees; SELECT EmployeeName, CASE WHEN Salary < 20000 THEN 'Below Average' WHEN Salary BETWEEN 20000 AND 50000 THEN 'Average' ELSE 'Above Average' END AS SalaryRange FROM Employees; SELECT EmployeeName, CASE WHEN Salary < 30000 THEN 'Below Average' WHEN Salary BETWEEN 30000 AND 60000 THEN 'Average' WHEN Salary BETWEEN 60000 AND 80000 THEN 'High' ELSE 'Very High' END AS SalaryRange FROM Employees;SELECT EmployeeName, CASE WHEN Salary < 30000 THEN 'Low' WHEN Salary BETWEEN 30000 AND 60000 THEN 'Medium' ELSE 'High' END AS SalaryRange FROM Employees;

Using CASE in SELECT statements allows formatting and displaying data based on defined ranges or conditions.

Q40

Multiple Choice

Which query uses CASE to handle multiple conditions within an ORDER BY clause?

SQL Code

SELECT EmployeeName, Salary
FROM Employees
ORDER BY
 CASE
 WHEN Salary < 30000 THEN 1
 WHEN Salary BETWEEN 30000 AND 60000 THEN 2
 ELSE 3
 END;

SELECT EmployeeName, Salary FROM Employees ORDER BY CASE WHEN Salary < 30000 THEN 1 WHEN Salary BETWEEN 30000 AND 60000 THEN 2 ELSE 3 END; SELECT EmployeeName, Salary FROM Employees ORDER BY CASE WHEN Salary < 20000 THEN 1 WHEN Salary BETWEEN 20000 AND 50000 THEN 2 ELSE 3 END; SELECT EmployeeName, Salary FROM Employees ORDER BY CASE WHEN Salary < 30000 THEN 2 WHEN Salary BETWEEN 30000 AND 60000 THEN 1 ELSE 3 END;SELECT EmployeeName, Salary FROM Employees ORDER BY CASE WHEN Salary < 30000 THEN 3 WHEN Salary BETWEEN 30000 AND 60000 THEN 2 ELSE 1 END;

CASE can be used in ORDER BY clauses to sort data based on multiple conditional categories.

Q41

Multiple Choice

Which query uses CASE to compute and display different results based on sales targets?

SQL Code

SELECT SalesPerson,
 CASE
 WHEN SalesAmount > 100000 THEN 'Top Performer'
 WHEN SalesAmount BETWEEN 50000 AND 100000 THEN 'Achiever'
 ELSE 'Needs Improvement'
 END AS PerformanceCategory
FROM Sales;

SELECT SalesPerson, CASE WHEN SalesAmount > 100000 THEN 'Top Performer' WHEN SalesAmount BETWEEN 50000 AND 100000 THEN 'Achiever' ELSE 'Needs Improvement' END AS PerformanceCategory FROM Sales; SELECT SalesPerson, CASE WHEN SalesAmount > 100000 THEN 'Top Performer' WHEN SalesAmount BETWEEN 50000 AND 100000 THEN 'Achiever' WHEN SalesAmount < 50000 THEN 'Below Average' END AS PerformanceCategory FROM Sales; SELECT SalesPerson, CASE WHEN SalesAmount > 100000 THEN 'Excellent' ELSE 'Average' END AS PerformanceCategory FROM Sales;SELECT SalesPerson, CASE WHEN SalesAmount > 100000 THEN 'Top Performer' WHEN SalesAmount BETWEEN 50000 AND 100000 THEN 'Good' ELSE 'Poor' END AS PerformanceCategory FROM Sales;

CASE can categorize and compute different results based on specific sales targets or performance metrics.

Q42

Multiple Choice

Which query demonstrates the use of CASE for conditional aggregation?

SQL Code

SELECT
 Department,
 SUM(CASE WHEN Salary > 50000 THEN Salary ELSE 0 END) AS TotalHighSalaries,
 COUNT(CASE WHEN Salary > 50000 THEN 1 END) AS HighSalaryCount
FROM Employees
GROUP BY Department;

SELECT Department, SUM(CASE WHEN Salary > 50000 THEN Salary ELSE 0 END) AS TotalHighSalaries, COUNT(CASE WHEN Salary > 50000 THEN 1 END) AS HighSalaryCount FROM Employees GROUP BY Department; SELECT Department, COUNT(CASE WHEN Salary > 50000 THEN Salary END) AS HighSalaryCount, AVG(CASE WHEN Salary > 50000 THEN Salary ELSE 0 END) AS AvgHighSalary FROM Employees GROUP BY Department; SELECT Department, SUM(CASE WHEN Salary <= 50000 THEN Salary ELSE 0 END) AS TotalLowSalaries, COUNT(CASE WHEN Salary <= 50000 THEN 1 END) AS LowSalaryCount FROM Employees GROUP BY Department;SELECT Department, COUNT(CASE WHEN Salary > 50000 THEN 1 END) AS HighSalaryCount, AVG(CASE WHEN Salary <= 50000 THEN Salary END) AS AvgLowSalary FROM Employees GROUP BY Department;

CASE can be used in aggregate functions to conditionally compute sums and counts based on specific criteria.

Q43

Multiple Choice

Which query effectively uses CASE to categorize product sales based on different periods?

SQL Code

SELECT ProductName,
 CASE
 WHEN SalesAmount > 100000 THEN 'High Sales'
 WHEN SalesAmount BETWEEN 50000 AND 100000 THEN 'Moderate Sales'
 ELSE 'Low Sales'
 END AS SalesCategory,
 CASE
 WHEN SalesDate BETWEEN DATEADD(MONTH, -1, GETDATE()) AND GETDATE() THEN 'Recent Sales'
 ELSE 'Older Sales'
 END AS SalesPeriod
FROM Sales;

SELECT ProductName, CASE WHEN SalesAmount > 100000 THEN 'High Sales' WHEN SalesAmount BETWEEN 50000 AND 100000 THEN 'Moderate Sales' ELSE 'Low Sales' END AS SalesCategory, CASE WHEN SalesDate BETWEEN DATEADD(MONTH, -1, GETDATE()) AND GETDATE() THEN 'Recent Sales' ELSE 'Older Sales' END AS SalesPeriod FROM Sales; SELECT ProductName, CASE WHEN SalesAmount BETWEEN 50000 AND 100000 THEN 'High Sales' ELSE 'Low Sales' END AS SalesCategory, CASE WHEN SalesDate BETWEEN DATEADD(MONTH, -3, GETDATE()) AND GETDATE() THEN 'Recent Sales' ELSE 'Older Sales' END AS SalesPeriod FROM Sales; SELECT ProductName, CASE WHEN SalesAmount < 50000 THEN 'Low Sales' WHEN SalesAmount BETWEEN 50000 AND 100000 THEN 'Moderate Sales' ELSE 'High Sales' END AS SalesCategory FROM Sales;SELECT ProductName, CASE WHEN SalesAmount > 100000 THEN 'High Sales' WHEN SalesAmount BETWEEN 50000 AND 100000 THEN 'Moderate Sales' WHEN SalesAmount < 50000 THEN 'Low Sales' END AS SalesCategory FROM Sales;

The CASE statement allows for categorizing sales data based on different periods and amounts.

Q44

Multiple Choice

Which query demonstrates using CASE for formatting numerical data with conditional logic in a SELECT statement?

SQL Code

SELECT ProductName,
 CASE
 WHEN Discount > 0 THEN CONCAT('Discounted: ', Discount, '%')
 ELSE 'No Discount'
 END AS DiscountStatus
FROM Products;

SELECT ProductName, CASE WHEN Discount > 0 THEN CONCAT('Discounted: ', Discount, '%') ELSE 'No Discount' END AS DiscountStatus FROM Products; SELECT ProductName, CASE WHEN Discount = 0 THEN 'No Discount' ELSE CONCAT('Discounted: ', Discount, '%') END AS DiscountStatus FROM Products; SELECT ProductName, CASE WHEN Discount < 0 THEN CONCAT('Invalid Discount') ELSE CONCAT('Discounted: ', Discount, '%') END AS DiscountStatus FROM Products;SELECT ProductName, CASE WHEN Discount > 0 THEN 'Discounted' ELSE 'No Discount' END AS DiscountStatus FROM Products;

The CASE statement can format numerical data into human-readable text based on conditional logic.

Q45

Multiple Choice

Which query effectively uses CASE to handle null values and provide default values?

SQL Code

SELECT ProductName,
 CASE
 WHEN Discount IS NULL THEN 'No Discount'
 ELSE CONCAT('Discounted: ', Discount, '%')
 END AS DiscountStatus
FROM Products;

SELECT ProductName, CASE WHEN Discount IS NULL THEN 'No Discount' ELSE CONCAT('Discounted: ', Discount, '%') END AS DiscountStatus FROM Products; SELECT ProductName, CASE WHEN Discount IS NOT NULL THEN CONCAT('Discounted: ', Discount, '%') ELSE 'No Discount' END AS DiscountStatus FROM Products; SELECT ProductName, CASE WHEN Discount IS NULL THEN 'Discount Unavailable' ELSE 'Discount Available' END AS DiscountStatus FROM Products;SELECT ProductName, CASE WHEN Discount IS NULL THEN 'No Discount' ELSE 'Discount Available' END AS DiscountStatus FROM Products;

CASE can handle null values and provide default values based on the conditions specified.

Microsoft SQL Server Database Quiz Questions