Relational Databases and SQL

Terminology

Relational Database

Data organized into one or more tables of columns and rows, with a unique key identifying each row.

Relation

A relationship between two columns in one or more tables. Relations can be one-to-one, one-to-many or many-to-many.

SQL

Structured query language (SQL) is a standard language for database creation and manipulation.

Relational Databases

Fancy Google Sheets

Sheet names → Entities (tables)

Entities (table names) are the nouns that are described by the columns in a table.

Columns → Attributes

Attributes describe the individual aspects of a particular Entity and are usually just called columns.

Rows → Records

Records are the actual data that are described by the data in each column. These are often called rows.

Cell Format → Data Type

Every column has a data type defined when it's created.

Naming conventions

Table and Column names should:

• be lowercase
• use underscores instead of spaces
• be singular instead of plural
• not begin with a number

Constraints

Added for data integrity

Primary Key

Primary keys uniquely identify each Record in a Table. By convention, the primary key of a table is named id.

Compound Primary Key

A Compound Primary Key uniquely defines a table row with a combination of two primary/foreign keys.

By convention, these tables are named by the two joining table names, separated by an underscore.

Foreign Key

Foreign keys create a column relationship between two tables.

By convention, column names with foreign key relationships end in _id.

Check Constraints

When a column has a limited range of allowed values.

Not NULL

When a column cannot be empty.

Unique

When each value in a table column must be unique.

Default

When a default column value is defined if one is not provided when a row is created or updated.

Relations

Types of Foreign Key Relationships

One-to-Many

Entity A belongs to one Entity B but Entity B can belong to many Entity A.

Example: A country can have many cities but a city can only be in one country.

Many-to-Many

Entity A belongs to many Entity B and vice versa.

Example: A movie can have many actors and an actor can be in many movies.

One-to-one (rare)

One-to-one relationships are rare but are sometimes used in more advanced "super/subtype" data model architectures.

SQL

Structured Query Language

Common SQL Commands

The most common SQL statements follow traditional CRUD Operations:

Create → INSERT

Retrieve → SELECT

Update → UPDATE

Delete → DELETE

Create → INSERT INTO

Insert data into all columns of a table:

INSERT INTO 
  table_name
VALUES 
  (value1, value2, value3, ...); 

Example

Insert data into specific columns of a table:

INSERT INTO 
  table_name (column1, column2, column3, ...)
VALUES 
  (value1, value2, value3, ...);  

Retrieve → SELECT

Select specific columns from all rows from a table:

SELECT 
  column1, column2, ...
FROM 
  table_name; 

Examples

SELECT first_name, last_name FROM customer

SELECT first_name, last_name FROM customer;

SELECT all columns FROM customer with *:

SELECT * FROM customer;

SELECT with WHERE

Select from specific rows with WHERE

SELECT 
  column1, column2, ...
FROM 
  table_name 
WHERE
  condition;

Example

Find all fields for anyone with a last name of 'Packe':

SELECT * FROM customer WHERE last_name = 'Packe';

SELECT with ORDER BY

Order the rows returned with ORDER BY:

SELECT 
  column1, column2, ...
FROM 
  table_name 
WHERE
  condition;
ORDER BY
  column1, column2 ASC|DESC

Example

Find all customers by last name, starting at Z:

SELECT id, first_name, last_name FROM customer ORDER BY last_name DESC

Update → UPDATE

Update a row using SET and WHERE:

UPDATE 
  table_name
SET 
  column1 = value1, column2 = value2, ...
WHERE 
  condition; 

Example

Update the last name of customer 4 to "Marcias":

UPDATE customer SET last_name = 'Marcias' WHERE id = '4' 

Delete → DELETE

Delete rows with WHERE (we rarely delete rows in production):

DELETE FROM 
  table_name 
WHERE 
  condition; 

Example

Delete all records that total more than 1000 (then get fired):

DELETE FROM rental_invoice WHERE total_amount_payable > 1000; 

Further Reading

• Summary - Relational Database Model
  
  

Videos

• IBM Technology: What is a Relational Database?
• Free Code Camp: SQL Tutorial - Full Database Course for Beginners
  • [2:36] - What is a Database?
  • [23:10] - Tables & Keys
  • [43:31] - SQL Basics