[TODO]

  • Querrying SELECT, FROM, WHERE, ORDER BY
    • Subquerries
    • JOINS
    • UNIONS?
  • Triggers, stored procedures

Guide

Constraints

SQL constraints are used to specify rules for the data in a table.

Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.

Constraints can be column level or table level. Column level constraints apply to a column, and table level constraints apply to the whole table.

The following constraints are commonly used in SQL:

  • NOT NULL - Ensures that a column cannot have a NULL value
  • UNIQUE - Ensures that all values in a column are different
  • PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
  • FOREIGN KEY - Prevents actions that would destroy links between tables
  • CHECK - Ensures that the values in a column satisfies a specific condition
  • DEFAULT - Sets a default value for a column if no value is specified
  • CREATE INDEX - Used to create and retrieve data from the database very quickly

Syntax

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    column3 datatype constraint,
    ....
);

Data Integrity

Data integrity ensures the accuracy, and consistency of data over its life-cycle.

Referential Integrity

Referential Integrity ensures that changes to a table do not break logic constraints of other tables.

Relations

One-to-One

One-to-Many (Associative)

Denormalization

The process of transforming normalized relations into nonnormalized physical record specifications.

Examples from Khan Academy

Creating tables

Many data types

CREATE TABLE customers (
    id INTEGER PRIMARY KEY, 
    name TEXT, 
    age INTEGER, 
    weight REAL
);

Using primary keys

CREATE TABLE customers (
    id INTEGER PRIMARY KEY, 
    age INTEGER
);

Inserting data

Inserting data

INSERT INTO customers VALUES (73, "Brian", 33);

Inseting data for named columns

INSERT INTO customers (name, age) VALUES ("Brian", 33);

Querying Data

Select everything

SELECT * FROM customers;

Select specific columns

SELECT name, age FROM customers;

Filter with condition

SELECT * FROM customers 
WHERE age > 21;

Filter with multiple conditions

SELECT * FROM customers 
WHERE age < 21 AND state = "NY";

Filter with IN

SELECT * FROM customers 
WHERE plan IN ("free", "basic");

Order results

SELECT * FROM customers
WHERE age > 21 ORDER BY age DESC;

Transform with CASE

SELECT name, CASE WHEN age > 18 THEN "adult" ELSE "minor" END "type" FROM customers;

Aggregating data

Aggregate functions

SELECT MAX(age) FROM customers;

Grouping data

SELECT gender, COUNT(*) FROM students GROUP BY gender;

Updating and deleting data

Updating data

UPDATE customers SET age = 44 WHERE id = 73;

Deleting data

DELETE FROM customers WHERE id = 73;

Inner Join

SELECT customers.name, orders.time FROM customers 
JOIN orders ON customers.id = order.customer_id;

Outer Join

SELECT customers.name, orders.item FROM customers
LEFT OUTER JOIN orders ON customers.id = orders.customer_id;