Mysql

MySQL

SQL commands and their ordering

previous query(s)

WITH - queries as variables yay! (e.g. WITH previous_results AS (the actual previouw query), then you can use the previous table)

first

SELECT - required "view" (col_name1, col_name2 or * for all) FROM - required table name WHERE - filter rows with comparators

before ORDER BY

GROUP BY - group rows by column (e.g. price, movie_category) HAVING - filter groups with comparators

last

ORDER BY __ ASC/DESC - sort by column values LIMIT - row limit

reason why multiple tables are created

• reduce duplicate data
• contain minimal amount of information required to describe the table

tables split by meaningful groups (e.g. customer_orders, customer_subscriptions) are meant to contain just the information necessary to describe what was ordered.

However, a lot of this information would be repeated. If the same customer has multiple subscriptions, that customer’s name and address will be reported multiple times. If the same subscription type is ordered by multiple customers, then the subscription price and subscription description will be repeated. This will make our table big and unmanageable.

JOIN example

SELECT customer_name, address, purchase_date
FROM orders
JOIN customers
    ON orders.customer_id = customers.customer_id;

• SELECT is able to show columns from both tables (orders and customers) because the JOIN operation makes them one table
• both tables orders and customers are joined at their customer_id column
• conceptually, just assume lines 2-4 to be one thing, that returns a joined table for subsequent commands to operate on.

JOINS

JOIN / INNER JOIN

• only keeps matching rows
• unmatched rows are discarded

LEFT JOIN

• keeps all rows on the first table being joined
• there will be gaps in data if some rows in first table don't have corresponding rows in second table

CROSS JOIN

• like "fully connected" layers in ML
• returns all possible combinations of two/three/n-columns
• keeps all rows on both tables, NULL and all
• no ON command needed
• you can CROSS JOIN as many tables as you want

UNION

• append data together
• UNION will not append duplicate data between two tables. reason below:

SQL has strict rules for appending data:

Tables must have the same number of columns.
The columns must have the same data types in the same order as the first table.

primary and foreign keys

PRIMARY KEYS

• none of the values can be NULL
• each value must be unique within the table
• a table cannot have > 1 primary key column

FOREIGN KEYS

• columns of primary keys from other tables
• "when the primary key for one table appears in a different table, it is called a foreign key.
• having a NULL value on a foreign key column is possible, due to LEFT JOIN.

CANDIDATE KEYS

• any column that can uniquely identify a record (row)
• they are candidates for primary keys
• customer_id and phone_num are candidate keys, but when picking a primary key, a customer_id is more likely to be used.

Question
Is it possible for a table to have more than one unique identifier column, like an id column?

Answer
Yes, it is possible for a table to have more than one column which can uniquely identify a row of data. A column that can uniquely identify a record of data is known as a "Candidate Key". Tables can have multiple "Candidate Key"s, each of which could potentially be the "Primary Key", but there must only be one "Primary Key" per table. Usually, the column chosen as the "Primary Key" follows the naming convention like customer_id or product_id.

For example, say that we had a table of employee records, with the columns employee_id and phone_number. Every employee has a unique employee_id value, and a unique phone_number value. Both of these columns can be unique identifiers for a row, so they are "Candidate keys", but the "Primary Key" would most likely be set to employee_id.

the most common types of joins will be joining a foreign key from one table with the primary key from another table

CROSS JOIN example

TASK: find total number of subscriptions per month

-- first, filter out and COUNT() newpaper subscriptions in march (3)
SELECT COUNT(*)
FROM newspaper
WHERE
start_month <= 3 AND
end_month >= 3;

-- now CROSS JOIN over all months (a table with one column depicting months)
SELECT *
FROM newspaper
CROSS JOIN months;

-- now filter them by teach month instead of one month
SELECT *
FROM newspaper
CROSS JOIN months
WHERE
start_month <= month AND
end_month >= month;

-- finally group them and COUNT() them by each month
-- CROSS JOIN is used here just to "loop over" each month
-- kinda like "for i in range(10)" in python
SELECT month, COUNT(*)
FROM newspaper
CROSS JOIN months
WHERE
start_month <= month AND
end_month >= month
GROUP BY month;

WITH example

1. make a table called previous
2. do a SELECT on the table previous

WITH previous AS (
SELECT *, COUNT(subscription_id)
  AS 'subscriptions'
FROM orders
GROUP BY customer_id
)
SELECT * FROM previous ORDER BY subscriptions;

notes on MySQL

1. JOIN without ON is possible (ON is optional for MySQL)
   • this makes it a CROSS JOIN

REFs

• https://github.com/enochtangg/quick-SQL-cheatsheet