SQL Review

sql_review.md

SQL Basics

Notes from CodeSchool's "Sequel to SQL"

Aggregate Functions

Some commonly used aggregate functions are: min, max, avg, sum, and count.

Examples:

SELECT count(*) FROM Countries

Using * includes NULL values. But if applied to a specific column, nulls are omitted.

SELECT count(abbr) FROM Countries

Grouping

GROUP BY allows more sophisticated queries. Use HAVING to control which potential groups are ommitted according to certain criteria.

Constraints

Common constraints include NOT NULL, UNIQUE, PRIMARY KEY, and REFERENCES.

NOT NULL ensures that null cannot be inserted for the column, UNIQUE ensures that the value inserted is always unique, PRIMARY KEY is a constraint that includes NOT NULL and UNIQUE but can only be used once per table, and REFERENCES ensures that the foreign key references an existing row in another table before allowing the insert.

It is best practice to use REFERENCES with foreign keys because it prevents the orphaning of records. When a record is deleted which is referenced from another table with the foreign key defined with the REFERENCES constraint, an error will be thrown and the operation refused. This prevents bad data.

Another commonly useful constraint is the CHECK constraint. It allows the validation of values, such as ensuring all number values in a column are positive.

CREATE TABLE Actors (
  id int PRIMARY KEY,
  name varchar(50) NOT NULL UNIQUE,
  salary integer CHECK (salary >= 500),
  bonus integer CHECK (bonus < salary),
  country_id int REFERENCES Countries(id)
);

Normalization

Normal form rules:

1. Tables must not contain repeating groups of information in one column.
2. Tables must not contain redundancy (unnecessary repeating information).

Associations

1. One to one
2. One to many
3. Many to many

Subqueries

Some syntax options:

1. WHERE <field> IN (Subquery)
2. WHERE <field> NOT IN (Subquery)

Correlated subqueries (where the inner and outer queries depend on each other):

Movies where duration is greater than the average duration.

SELECT * FROM Movies
WHERE duration > (SELECT AVG(duration) FROM Movies);