Introduction to SQL by SQLBolt

What does SQL stand for?
SQL stands for Structured Query Language.

What is SQL designed for?
SQL is designed for technical and non-technical users to query, manipulate, and transform data from a relational database.

What is a relational database?
A relational database is a collection of related tables.

What makes up a table?
A table is made up of a fixed number of named columns and any number of rows of data.

Queries

What SQL statement do you use to retrieve data from a database?
The SQL statement you use to retrieve data from a database is the SELECT statement.

What are SELECT statements also called?
SELECT statements are also called queries.

What are the three things a query declares?
The three things a query declares are:

  1. The data we want.
  2. Where to find it in the database.
  3. How to transform it before it is returned (optional).

How do you write a query to select a few columns of data from every row of a table?
To select a few columns of data from every row of a table, write:

SELECT column_name, another_column_name, ...
FROM table_name;

How do you write a query to select every column of data from every row of a table?
To select every column of data from every row of a table, write:

SELECT *
FROM table_name;

What clause can you add to a query to select rows from a table based on one or more conditions?
The clause you can add to a query to filter what rows from a table are selected based on one or more conditions is the WHERE clause.

How do you write a query to select a few columns of data from rows from a table which satisfy one or more conditions?
To select a few columns of data from rows in a table which satisfy one or more conditions, write:

SELECT column_name, another_column_name, ....
FROM table_name
WHERE condition
    AND another_condition
    OR ...;

What operators can you use for conditions on numerical values in a SQL query?
You can use these operators for conditions on numerical values in a SQL query:

  • =, !=, <, <=, >, >= - Standard numerical operators.
  • BETWEEN ... AND ... - Within a range of numbers (inclusive).
  • NOT BETWEEN ... AND ... - Not within a range of numbers (inclusive).
  • IN (...) - Exists in a list.
  • NOT IN (...) - Doesn't exist a list.

What operators can you use for conditions on text data in a SQL query?
You can use these operators for conditions on text data in a SQL query:

  • = - Matches case and value.
  • != or <> - Doesn't match case and value.
  • LIKE - Matches value.
  • NOT LIKE - Doesn't match value.
  • % - Zero or more characters in a LIKE operation.
  • _ - A single character in a LIKE operation.
  • IN (...) - Exists in a list.
  • NOT IN (...) - Doesn't exist in a list.

What effect does including conditions have on the speed of running a query?
The effect that including conditions has on the speed of running a query is increasing the speed by reducing the data being returned.

What keyword can add to a query to blindly select rows in a table with unique values?
The keyword you can add to a query to blindly select rows with unique values is the DISTINCT keyword.

How do you write a query to select a few columns of data from rows from a table which have unique values?
To select a few columns of data from rows which have unique values, write:

SELECT DISTINCT column_name, another_column_name, ...
FROM table_name;

What clause can you add to a query to sort rows from a table alpha-numerically?
The clause you can add to a query to sort rows from a table alpha-numerically is the ORDER BY clause.

How do you write a query to select a few columns of data from rows from a table and sort them alpha-numerically?
To select a few columns of data from rows from a table and sort them alpha-numerically, write:

SELECT column_name, another_column_name, ...
FROM table_name
ORDER BY column_name ASC/DESC;

What two clauses are commonly used with ORDER BY to return only a subset of all the selected rows?
The two clauses that are commonly used with ORDER BY to return only a subset of all selected rows are:

  1. LIMIT.
  2. OFFSET.

What does the LIMIT clause do to a query's results?
The LIMIT clause reduces the number of rows returned by a query.

What does the OFFSET clause do to a query with the LIMIT clause?
The OFFSET clause changes where the count begins for the LIMIT clause.

Multi-table queries

What is database normalization?
Database normalization is the process of breaking down data about a particular entity into pieces and storing it across multiple orthogonal tables.

What is the benefit of database normalization?
The benefit of database normalization is that data can grow independently of each other.

What is the downside of database normalization?
The downside of database normalization is that queries can be more complex and take longer to finish.

What do tables that share information about a single entity need?
Tables that share information about a single entity need a primary key.

What is the most common primary key type?
The most common primary key type is an auto-incrementing integer.

What is the one requirement for a primary key?
The one requirement for a primary key is it needs to be unique.

What clause can you add to a query to combine row data across two separate tables using primary keys?
The clause you can add to a query to combine row data across two separate tables using primary keys is the JOIN clause.

What does the INNER JOIN clause do?
The INNER JOIN clause combines rows from two tables which have the same primary key specified by the ON constraint.

How do you write a query to combine rows from two tables using primary keys?
To combine rows from two tables which have the same primary key, write:

SELECT column_name, another_column_name, ...
FROM table_name
INNER JOIN another_table_name
	ON table_name.id = another_table_name.id;

Is writing JOIN equivalent to writing INNER JOIN?
Yes, writing JOIN is equivalent to writing INNER JOIN.

What does the LEFT JOIN clause do?
The LEFT JOIN clause includes rows in the original table regardless of if a matching row is found in the other table.

What does the RIGHT JOIN clause do?
The RIGHT JOIN clause includes rows in the other table regardless of if a matching row is found in the original table.

What does a FULL JOIN clause do?
A FULL JOIN clause includes rows from both tables regardless of if a matching row is found in either of them.