Aggregation

Database schema before class: database_before_day3.sql

Slides from class
SQL script from class (posted after class)

Database notes

aggregate functions

SQL functions that return a summary of data. These include: average (AVG), min, max, sum, and count.

NULL

The absence of a value/value is unknown. Not a blank! Can cause issues if used in conditional evaluation (e.g., false and null is null, not false).

GROUP BY

A SQL clause used to create frequency distributions when combined with any of the aggregate functions in a SELECT command.

HAVING

A clause applied to the output of a GROUP BY operation to restrict the selected rows.

nested query (subquery or inner query)

A query within another query. In a SELECT command, a nested query can be used in the SELECT, FROM, or WHERE clauses.

WITH

A SQL clause that provides a way of defining a temporary relation whose definition is available only to the query in which the WITH clause occurs.

SQL command that evaluates an expression and return one value if true and another value if false. Example: SELECT RestaurantID, IF (Grade IS NULL,'N/A',Grade) AS Grade FROM Inspections;

COALESCE

SQL command that returns the first non-null value. Example: SELECT OrderID, COALESCE(State, Country, 'N/A') AS Location FROM Orders;

CASE

SQL command that provides if-then-else logic. Example:

SELECT i.RestaurantID, RestaurantName, count(*) AS `Total Inspections`,
	CASE 
		WHEN count(*) < 10 THEN 'Infrequent inspections '
		WHEN count(*) BETWEEN 10 AND 15 THEN 'Moderate inspections'
	    	ELSE 'Frequent inspections'
	END AS Frequency
	FROM Inspections i, Restaurants r
	WHERE i.RestaurantID = r.RestaurantID
	GROUP BY i.RestaurantID;

Note: many definitions from Coronel and Morris.