SQL quiz. Choose correct clause: <pre><code>SELECT CountryC

Choose correct clause:

SELECT CountryCode, COUNT(*) FROM City GROUP BY CountryCode _____ COUNT(*) > 20;

Explanation

You would usually specify conditions in WHERE clause like following:

WHERE some_condition

But WHERE clause imposes conditions on columns present in the tables and cannot be used to impose restrictions on groups of records created by GROUP BY clause.
HAVING clause is used to places conditions on groups created by GROUP BY clause.

Some properties of HAVING clause:
- HAVING requires that a GROUP BY clause is present in the query
- WHERE and HAVING clauses can be both present in the same query
- HAVING clause applies to summarized group records, while WHERE clause applies to individual records

Theory

The HAVING Clause

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

SQL HAVING Syntax

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

SQL HAVING Example


|OrderID |EmployeeID|ShipperID  |
+--------+----------+-----------+
|10248	 | 5        |	3       |
+--------+----------+-----------+
|10249	 | 6        |	1       |
+--------+----------+-----------+
|10250	 | 4        |	2       |

And a selection from the "Employees" table:


|EmployeeID |LastName |
+-----------+---------+
|1          |Davolio  |
+-----------+---------+
|2          |Fuller   |
+-----------+---------+
|3          |Leverling|

Now we want to find if any of the employees has registered more than 10 orders.
We use the following SQL statement:

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;

SQL HAVING Example


|OrderID |EmployeeID|ShipperID  |
+--------+----------+-----------+
|10248	 | 5        |	3       |
+--------+----------+-----------+
|10249	 | 6        |	1       |
+--------+----------+-----------+
|10250	 | 4        |	2       |

And a selection from the "Employees" table:


|EmployeeID |LastName |
+-----------+---------+
|1          |Davolio  |
+-----------+---------+
|2          |Fuller   |
+-----------+---------+
|3          |Leverling|

Now we want to find if the employees "Davolio" or "Fuller" have registered more than 25 orders.
We add an ordinary WHERE clause to the SQL statement:

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;