3 SQL Fundamentals

Tables, types, and constraints

A relational table stores data in named columns and rows. Each column has a data type suited to the values it holds, such as text for names or a numeric type for prices. Constraints add rules that help prevent invalid data from being stored.

The CREATE TABLE statement defines a table and its columns. In this example, a primary key identifies each product, NOT NULL requires values for selected columns, and CHECK enforces conditions on prices and stock counts.

A UNIQUE constraint can prevent duplicate values, while a primary key ensures each row can be uniquely identified. A primary key may consist of one column or several columns. Available data types and some SQL details differ among database systems, so check the documentation for the system in use.

Selecting and filtering rows

The SELECT statement chooses which columns or expressions to return, and FROM names the table to read. Listing only the needed columns is usually clearer than requesting every column with *.

A query can also calculate an expression and give its output a readable name with an alias:

Use WHERE to keep only rows that meet a condition. Conditions can use comparison operators such as =, <>, <, and >=, as well as AND, OR, and NOT. Parentheses help make combined conditions clear.

IN matches one of several values, BETWEEN tests an inclusive range, and LIKE matches a text pattern. In a LIKE pattern, % matches any sequence of characters.

NULL represents a missing or unknown value; it is not the same as zero or an empty string. Test for it with IS NULL or IS NOT NULL, not = NULL.

Takeaway: Define the rows you want with conditions in WHERE, and use the appropriate IS NULL test when checking for missing values.

Ordering and limiting results

ORDER BY specifies how returned rows should be sorted. Without it, the row order is not guaranteed. ASC sorts in ascending order and is the default; DESC sorts in descending order. Add another sort key to determine the order when values in the first key tie.

Many databases support LIMIT to return only a chosen number of rows, though its syntax can vary. Combining it with ORDER BY makes the selected rows predictable.

Takeaway: Specify ORDER BY whenever the result order matters, especially when limiting the results.

Aggregating and grouping data

An aggregate function calculates a summary from multiple rows. Common examples include COUNT, SUM, AVG, MIN, and MAX. COUNT(*) counts rows, while COUNT(column) counts non-NULL values in that column.

Use GROUP BY to calculate aggregates separately for each group. A selected column that is not aggregated should generally appear in the GROUP BY clause. Use HAVING to filter groups after aggregation; WHERE filters individual rows before grouping.

The query first uses WHERE to exclude out-of-stock products. It then groups the remaining rows by category, calculates a count and average for each group, keeps groups with at least two products using HAVING, and sorts the resulting groups by average price.

Takeaway: Filter rows with WHERE, summarize them with aggregate functions, form groups with GROUP BY, and filter those groups with HAVING.