SQL (pronounced "ess-que-el") stands for Structured Query Language. SQL is used to communicate with a database, and is the standard language for relational database management systems (DBMS). SQL allows for the creation of a database's structure and data, and allows for data search and retrieval.
Databases store information in a table, which is like a spreadsheet. Each column represents a single piece of data (i.e. first name, last name, etc.). Each row represents a different value for the column, a single instance of data (i.e. Mary, Jones, etc.). Each table must have a primary key, unique to each instance with no duplicates. Things like an ID number, SSN or driver's license number are good primary keys, while things like names are not.
There are three types of relationships between tables: (1) One-to-Many, (2) Many-to-Many, and (3) One-to-One.
One-to-Many: A one-to-many relationship is the most common type of relationship. In this type of relationship, a row in table A can have many matching rows in table B, but a row in table B can have only one matching row in table A. For example, publishers and titles tables have a one-to-many relationship: each publisher produces many titles, but each title comes from only one publisher. Make a one-to-many relationship if only one of the related columns is a primary key or has a unique constraint.
Many-to-Many: In a many-to-many relationship, a row in table A can have many matching rows in table B, and vice versa. You create such a relationship by defining a third table, called a junction table, whose primary key consists of the foreign keys from both table A and table B. For example, an authors table and a titles table have a many-to-many relationship that is defined by a one-to-many relationship from each of these tables to the title/authors table. The primary key of the title/authors table is the combination of the author_id column (the authors table's primary key) and the title_id column (the titles table's primary key).
One-to-One: In a one-to-one relationship, a row in table A can have no more than one matching row in table B, and vice versa. A one-to-one relationship is created if both of the related columns are primary keys or have unique constraints. This type of relationship is not common because most information related in this way would be all in one table. You might use a one-to-one relationship to divide a table with many columns, isolate part of a table for security reasons, store data that is short-lived and could be easily deleted by simply deleting the table, or store information that applies only to a subset of the main table.
The select statement is used to retrieve data from a database. The result is stored in a result table, called the result-set. The syntax is as follows:
SELECT [ALL/DISTINCT] column1 [column2, etc.]
FROM table1 [table2, etc.]
[WHERE conditions]
[GROUP BY columnlist]
[HAVING conditions]
[ORDER BY column list [ASC/DESC]]
SQL keywords are often written in CAPS, and the values in brackets are optional. SELECT and FROM are the only required keywords.
To Select All:
SELECT * FROM table
To Eliminate Duplicate Values From a Column:
SELECT DISTINCT column FROM table
To Sort in a Specific Order: When sorting, ASC (ascending) is the default and doesn't need to be specified, while DESC (descending) does need to be specified.
SELECT column FROM table
ORDER BY column DESC
Operators are to be used with the WHERE keyword in order to select more specific data:
Comparison: Equals (=), Greater Than (>), Less Than (<) , Greater Than or Equal To (>=), Less Than or Equal To (<=)
WHERE age >= 80Boolean: AND, OR
AND returns rows for which all conditions are true
OR returns rows for which either condition is true
WHERE age 75 AND last_name = 'Smith'IN: Used to specify multiple values
WHERE age IN (23, 51, 75)Negative Conditions: Not Equal (<>), NOT
WHERE age <> 80
WHERE NOT age 80BETWEEN: Used to select a range of data between two values
WHERE age BETWEEN 23 and 80IS NULL/IS NOT NULL: Used to test for a null value
WHERE age IS NULL
WHERE AGE IS NOT NULLLIKE: Used to search for a specified pattern, where % represents multiple missing characters before/after the pattern or _ represents a single missing character in the pattern
WHERE first_name LIKE 'Nan%' => Nan, Nancy
WHERE first_name LIKE '%ob%' => Robert, Bob
WHERE first_name LIKE '_ean' => Dean, Sean
SQL joins combine records coming from more than one table using the common values between those tables:
Cross Join:
SELECT * FROM table1 CROSS JOIN table2Join/Inner Join: Returns rows where there is at least one match in both tables. Can use the keyword JOIN or the keyword INNER JOIN.
SELECT * FROM table1 JOIN table2
ON table1_column = table2_columnEqui-Join: Uses only equality comparisons in the join (e.g. no > or < comparison operators)
Equi-Join With the Using Construct: Applicable with columns having the same name
SELECT * FROM faculty INNER JOIN dept USING dept_idNatural Join: An equi-join with only one column for each equally-name column
SELECT * FROM faculty NATURAL JOIN deptFull Join/Full Outer Join: Returns rows where there is a match in at least one of the tables
SELECT * FROM table1 FULL JOIN table2
ON table1_column = table2_columnRight Join/Right Outer Join: Returns all rows from the right table even if there are no matches in the left table
SELECT * FROM table1 RIGHT JOIN table2
ON table1_column = table2_columnLeft Join/Left Outer Join: Returns all rows from the left table even if there are no matches in the right table
SELECT * FROM table1 LEFT JOIN table2
ON table1_column = table2_column* Table names are always plural
* Each row is identified by automatically-incrementing integer field IDs
* use_snake_case_to_name_fields
* Fields involving dates/times end in _at or _on unless it's obvious they already refer to something time related (birthday)
* Make field and table names as self-explanatory as possible
* Rails includes created_at and updated_at fields by default