SQL

Use the DISTINCT keyword to find unique rows. Note that the keyword applies to all columns, not just the one it precedes.

SELECT DISTINCT first_name FROM People;

Use the LIMIT keyword to control the number of rows returned. Note that SQL implementations differ here.

SELECT first_name FROM People LIMIT 5;

-- Start from row 5.
SELECT first_name FROM People LIMIT 5 OFFSET 5;

Use the ORDER BY clause to sort data retrieved using a SELECT. Be sure that the ORDER BY clause is the last clause in your SELECT statement.

SELECT first_name FROM People ORDER BY first_name;

-- Sort by multiple column names.
SELECT id FROM People ORDER BY first_name, last_name;

-- Sort in descending order (Z to A).
SELECT email FROM People ORDER BY first_name DESC;

Use the WHERE clause to filter data retrieved by the SELECT statement. The WHERE clause is specified right after the table name. AND, OR, and NOT may be used in where clauses. SQL (like most languages) processes AND operators before OR operators. Use parenthesis to explicitly group related operators.

SELECT first_name, last_name FROM People WHERE age = 21;

SELECT first_name FROM People WHERE last_name = 'Penson' AND age <= 30;

SELECT first_name FROM People WHERE last_name = 'Penson' OR last_name = 'Bland';

SELECT first_name FROM People WHERE NOT last_name = 'Penson';

The IN operator is used to specify a range of conditions, any of which can be matched.

SELECT last_name FROM People WHERE zip IN ('20005', '20036') ORDER BY last_name;

Various operators are allowed. Note that single quotes should be used for string literals.

To use wildcards in search clauses, the LIKE operator must be used. Wildcard searching can only be used with text fields. The % sign matches any number of occurrences of any character. The underscore (_) matches any single character.

SELECT email FROM People WHERE email LIKE '%@gmail.com';

-- Find People (babies) with single digit heights.
SELECT height FROM People WHERE height LIKE '_ inches';

A calculated field is created on-the-fly within a SQL SELECT statement. Concatenate strings with the || operator. SQL supports mathematical operators like +, -, *, and /.

SELECT last_name || ', ' || first_name FROM People;

SELECT item_price * quantity FROM Orders;

An alias is an alternate name for a field or value. Aliases are assigned with the AS keyword.

SELECT last_name || ', ' || first_name AS name FROM People ORDER BY name;

SQL also provides a variety of functions for manipulating values. Unfortunately, functions tend to be very DBMS specific. Unlike SQL statements, SQL functions are not portable. Be sure to look at documentation for your specific SQL implementation!

SQL supports five efficient aggregation functions.

The COUNT function can be used in a few different ways.

-- Count the number of rows in a table.
SELECT COUNT(*) FROM People;

-- Count the number of rows that have values in a specific column.
SELECT COUNT(email) FROM People;

-- Count unique values.
SELECT COUNT(DISTINCT age) FROM People;

GROUP BY divides data into logical sets so that you can perform aggregate calculations on each group. Note that groups may be nested by specifying multiple columns in the GROUP BY clause. Filter groups with the HAVING clause. HAVING filters groups, WHERE filters rows.

SELECT age, COUNT(*) AS num_people FROM People GROUP BY age;

SELECT zip_code, COUNT(*) AS num_people FROM People GROUP BY age HAVING COUNT(*) > 2;

Subqueries are queries embedded into other queries. SQL imposes no limit on the number of subqueries that can be nested. Note that subquery SELECT statements can only retrieve a single column. Attempting to retrieve multiple columns will return an error.

SELECT last_name, first_name FROM People
WHERE id IN (SELECT customer_id FROM Orders WHERE product_id = 32);

Joins are usually more performant than subqueries.

Relational tables are designed so that information is split into multiple tables, one for each data type. The tables are related to each other through common values. A join is a mechanism used to associate tables within a SELECT statement.

Note that you must use the fully qualified column name (table and column separated by a period) whenever there is a possible ambiguity about which column you are referring to.

A UNION is composed of two or more SELECT statements, each separated by the keyword UNION. Each query in a UNION must contain the same columns, expressions, or aggregate functions.

SELECT first_name, last_name FROM People WHERE state IN ('MA', 'IL')
UNION
SELECT first_name, last_name FROM People WHERE age = 50;

Note that this specific query could have been performed with multiple WHERE clauses instead.

A primary key is a special constraint used to ensure that values in a column (or set of columns) are unique and never change. No two rows may have the same primary key value. Use the PRIMARY KEY keyword in a table definition to define a primary key.

A foreign key is a column whose values must be listed in the primary key of another table. Foreign keys are essential for ensuring referential integrity. Use the REFERENCES keyword to define a foreign key.

CREATE TABLE Orders (
    order_number INT      NOT NULL  PRIMARY KEY,
    order_date   DATETIME NOT NULL,
    customer_id  INT      NOT NULL REFERENCES People(id)
);

Unique constraints are used to ensure that all data in a column is unique. Unique constraints are similar to primary keys. In contrast, though, a table can contain multiple constraints, but only one primary key. Unique constraints can also be modified or updated. Use the UNIQUE keyword to define a unique constraint.

Check constraints ensure that data in a column (or set of columns) meet a set of criteria. Common criteria include minimum or maximum values, a range, and specific values. Use the CHECK keyword to define a check constraint.

CREATE TABLE Order (
   order_number INT      NOT NULL PRIMARY KEY,
   product_id   CHAR(10) NOT NULL,
   quantity     INT      NOT NULL CHECK (quantity > 0),
   price        MONEY    NOT NULL
);

Suppose we have a People table with Id and Email columns. The following command will delete all duplicate email entries keeping entries based on the smallest ID.

DELETE * FROM People p1
INNER JOIN People p2
WHERE p1.Email = p2.Email AND p1.Id > p2.Id;