sql fundamentals.md

WHERE

+-----------+----------+
| firstname | lastname |
+-----------+----------+
| alice     | bob      |
+-----------+----------+

SELECT
  firstname, 
  lastname
FROM
  Employee
WHERE
  lastname = 'bob'

Conditions

1. >, <, >=, <=, = age >= 21
2. Not equal: <> or != isDeleted != TRUE
3. Within a range temperature BETWEEN 68 AND 75
4. Member of a set companyname in ('Microsoft', 'LinkedIn')
5. String ends with email LIKE '%.gov'
6. String includes summary LIKE '%spicy%'
7. String length billing_state LIKE '__'

AND, OR NOT

SELECT
  productname
FROM
  Product
WHERE (
  unitprice > 60
  AND unitsinstock > 20
)

Core Functions

• lower
• max
• min
• count
• substr
• ...

SELECT productname
FROM Product
WHERE lower(productname) LIKE '%dride%'

Debugging conditions

SELECT 'bob marley' LIKE '%marley'; -- TRUE

Sorting

• ORDER BY
• ASC or DESC

SELECT productname, unitprice
FROM Product
ORDER BY unitprice DESC

Relationship

1. one to one
2. one to many
3. many to many

Joins

# Order
+----+------------+---------+
| id | customerid | amount  |
+----+------------+---------+
|  1 |          1 |   99.00 |
|  2 |          1 |  212.00 |
|  3 |          3 |   14.50 |
|  4 |         12 | 1000.00 |
+----+------------+---------+

# Customer
+----+------+
| id | name |
+----+------+
|  1 | mike |
|  2 | mark |
|  3 | lisa |
+----+------+

INNER JOIN

SELECT *
FROM Order o
INNER JOIN Customer c
ON o.customerid = c.id

+----+------------+--------+----+------+
| id | customerid | amount | id | name |
+----+------------+--------+----+------+
|  1 |          1 |  99.00 |  1 | mike |
|  2 |          1 | 212.00 |  1 | mike |
|  3 |          3 |  14.50 |  3 | lisa |
+----+------------+--------+----+------+

LEFT JOIN

• rows from the left will always be selected

SELECT *
FROM Order o
LEFT JOIN Customer c
ON o.customerid = c.id

+----+------------+---------+----+------+
| id | customerid | amount  | id | name |
+----+------------+---------+----+------+
|  1 |          1 |   99.00 |  1 | mike |
|  2 |          1 |  212.00 |  1 | mike |
|  3 |          3 |   14.50 |  3 | lisa |
|  4 |         12 | 1000.00 |    |      |
+----+------------+---------+----+------+

RIGHT JOIN

• rows from the right will always be selected

SELECT *
FROM Order o
RIGHT JOIN Customer c
ON o.customerid = c.id

+----+------------+--------+----+------+
| id | customerid | amount | id | name |
+----+------------+--------+----+------+
|  1 |          1 |  99.00 |  1 | mike |
|  2 |          1 | 212.00 |  1 | mike |
|  3 |          3 |  14.50 |  3 | lisa |
|    |            |        |  2 | mark |
+----+------------+--------+----+------+

FULL JOIN

SELECT *
FROM Order o
FULL JOIN Customer c
ON o.customerid = c.id

+----+------------+---------+----+------+
| id | customerid | amount  | id | name |
+----+------------+---------+----+------+
|  1 |          1 |   99.00 |  1 | mike |
|  2 |          1 |  212.00 |  1 | mike |
|  3 |          3 |   14.50 |  3 | lisa |
|    |            |         |  2 | mark |
|  4 |         12 | 1000.00 |    |      |
+----+------------+---------+----+------+

JOIN - Selecting columns

SELECT o.id,
       o.customerid,
       o.amount,
       c.name
FROM Order o
INNER JOIN Customer c
ON o.customerid = c.id

+----+------------+--------+------+
| id | customerid | amount | name |
+----+------------+--------+------+
|  1 |          1 |  99.00 | mike |
|  2 |          1 | 212.00 | mike |
|  3 |          3 |  14.50 | lisa |
+----+------------+--------+------+

Aggregate Functions

• perform calculations on a set of values
• SUM adds values together
• COUNT counts the number of values
• MIN/MAX find min/max value
• AVG calculates the average of values

select SUM(amount) from Order

Aggregate Functions and GROUP BY

• find total amount per customer

SELECT c.id,
       c.name,
       o.amount,
FROM Order o
INNER JOIN Customer c
ON o.customerid = c.id

+----+------+--------+
| id | name | amount |
+----+------+--------+
|  1 | mike |  99.00 |
|  1 | mike | 212.00 |
|  3 | lisa |  14.50 |
+----+------+--------+

• collapse the id using GROUP BY and sum the amount

SELECT c.id,
       c.name,
       sum(o.amount),
FROM Order o
INNER JOIN Customer c
ON o.customerid = c.id
GROUP BY c.id

+----+------+--------+
| id | name | amount |
+----+------+--------+
|  1 | mike | 311.00 |
|  3 | lisa |  14.50 |
+----+------+--------+

HAVING

• second filtering after all the groups are done

SELECT month,
       sum(amount)
FROM Customer
GROUP BY month
HAVING sum(amount) >= 300

Subquery

• nested SELECT query

SELECT *
FROM Product
WHERE categoryid=(
  SELECT id
  FROM Category
  WHERE categoryname='Beerages'
)

Creating Reconrds

INSERT INTO Customer
VALUES (8424, 'alice', 'sleeps')

SQL Injection

Name: bob

INSERT INTO Customer (name, notes)
VALUES ('bob                       ', 'bob notes')

Name: '); DROP TABLE Customer; --

INSERT INTO Customer (name, notes)
VALUES (''); DROP TABLE Customer; --', 'bob notes')

Deleting Records

DELETE FROM Customer
WHERE name = 'alice'

Transactions

• queries either happens or ignored

happens

BEGIN;
INSERT INTO Customer ....;
COMMIT; -- save!

ignored

BEGIN;
INSERT INTO Customer ....;
ROLLBACK; -- revert!

Updating Records

Order
+----+-----------+----------+
| id | unitprice | quantity |
+----+-----------+----------+
|  1 |      9.00 |       14 |
|  2 |      7.00 |        8 |
+----+-----------+----------+

UPDATE Order
SET quantity = 50
WHERE id=1

Order
+----+-----------+----------+
| id | unitprice | quantity |
+----+-----------+----------+
|  1 |      9.00 |       50 |
|  2 |      7.00 |        8 |
+----+-----------+----------+

Indices

• for quick retrieval later

# People
+----+-----------+-----------+
| id | firstname | last_name |
+----+-----------+-----------+
|  1 | rick      | sanchez   |
|  2 | mike      | north     |
|  3 | mark      | grabanski |
|  4 | morty     | smith     |
|  5 | jerry     | smith     |
|  6 | summer    | smith     |
|  7 | beth      | smith     |
+----+-----------+-----------+

# Index: People (last_name)
+-----------+-----------+
|   value   |  records  |
+-----------+-----------+
| grabanski |         3 |
| north     |         2 |
| sanchez   |         1 |
| smith     | 4,5, 6, 7 |
+-----------+-----------+

without index took 55 miliseconds

SELECT * FROM Order WHERE orderid = 10793

with index took 28 miliseconds

CREATE INDEX order_orderid ON Order(orderid)

SELECT * FROM Order WHERE orderid = 10793

create index on multiple columns

CREATE INDEX order_orderid_customerid
ON Order(orderid, customerid)

Column Constraints - Create a Table

• name/type pairs

CREATE TABLE UserPreferences (
  id                    INT PRIMARY KEY, -- an integer
  favorite_color        CHAR(6), -- exactly 6 characters
  age                   SMALLINT, -- an "small" integer
  birth_date            DATE, -- date (may or may not include time)
  notes                 VARCHAR(255), -- up to 255 characters
  is_active             BOOLEAN -- boolean
)

Column Constraints - NOT NULL

CREATE TABLE UserPreferences (
  id                    INT PRIMARY KEY NOT NULL, -- required
  favorite_color        CHAR(6), 
  age                   SMALLINT,
  birth_date            DATE NOT NULL, -- required
  notes                 VARCHAR(255),
  is_active             BOOLEAN NOT NULL -- required
)

Column Constraints - UNIQUE

CREATE TABLE UserAccount (
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(255)
)

CREATE UNIQUE INDEX useraccount_email ON UserAccount(email)

Column Constraints - PRIMARY KEY

• combination of NOT NULL and UNIQUE

CREATE TABLE UserAccount (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255)
)

Column Constraints - FOREIGN KEY

# Order
+----+------------+---------+
| id | customerid | amount  |
+----+------------+---------+
|  1 |          1 |   99.00 |
|  2 |          1 |  212.00 |
|  3 |          3 |   14.50 |
|  4 |         12 | 1000.00 |
+----+------------+---------+

# Customer
+----+------+
| id | name |
+----+------+
|  1 | mike |
|  2 | mark |
|  3 | lisa |
+----+------+

CREATE TABLE Customer (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255)
)

CREATE TABLE Order (
  id SERIAL PRIMARY KEY,
  customerid INTEGER NOT NULL REFERENCES Customer(id),
  amount REAL
)