W3. SQL Having

Question 1

Q: What is the purpose of the SQL HAVING clause?

Answer 1

A: HAVING is used to filter records with aggregate functions, which WHERE cannot do.

Question 2

Q: Write the syntax for using HAVING in a SQL query.

Answer 2

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

Question 3

Q: Why was the HAVING clause added to SQL?

Answer 3

A: Because the WHERE clause cannot be used with aggregate functions.

Question 4

Q: Write a query to list countries with more than 5 customers.

Answer 4

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;

Question 5

Q: How can you sort a HAVING result set in descending order by count?

Answer 5

A: Use ORDER BY COUNT(column_name) DESC after HAVING.

Question 6

Q: Write a query to list countries with more than 5 customers, sorted high to low.

Answer 6

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC;

Question 7

Q: Write a query to list employees with more than 10 orders.

Answer 7

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 10;

Question 8

Q: Write a query to check if “Davolio” or “Fuller” have registered more than 25 orders.

Answer 8

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
WHERE LastName = ‘Davolio’ OR LastName = ‘Fuller’
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 25;