VI. SQL

Populating Foreign Keys

In MySQL (and most DBMS), the primary key of table A does not automatically populate in table B where it is a foreign key because it is not a default behavior of the database management system.

It is the responsibility of the programmer or database administrator to ensure that the data is entered correctly and consistently across tables.

Reference: General SQL Query Structure

SELECT <attribute list> // specifies columns (variables) to be retrieved

FROM <table list> // tables to be queried

JOIN <table2> ON table1.column_field = table.common_field

WHERE <condition> LOGICAL CONNECTIVE <condition>  // subset based on condition

GROUP BY <grouoping attribute(s)> // grouping based on a conditon

HAVING <grouping condition>

ORDER BY <attribute(s)> ASC/DESC;

General Application

Order	File Name	Comments
1	_Introduction to SQL	About SQL, how SQL statements are executed (`;`), comparison using `NULL` and the 3 logical values in SQL (`TRUE`, `FALSE`, and `UNKNOWN`).
2	Aliasing	Can be applied to attributes and relation.
3	Creating Views (Virtual Tables)	Creating views and how it is different from regular result-set.
4	Checking for Missing Values	How to check for missing values in a column using `IS NULL` and `IS NOT NULL`.
5	Handling Ambiguous Attribute Names	How to deal with retrieving attributes with the same names but in different relation - `qualifying relation with attribute name`
6	USE Command	How to specify which database to work with in a DBMS.
7	Logical and Comparison Operators	Logical gates (AND, OR, NOT) and comparison operators (incl. `LIKE`, `BETWEEN`, 'IN')
8	Wildcard Matching (Pattern Matching)	Pattern matching within `string` values.
9	Set Operations	Set operations to combine or compare results of multiple `SELECT` statements.
10	_Terminology	Terminology of commonly used words in SQL.
11	Arthmetic Operations with SELECT	Using standard arithmetic with the `SELECT` statement.
12	_FAQ For SQL

Basic SQL Queries

Order	File Name	Comments
1	The CREATE TABLE Statement	AKA `Base relations`. Creating tables and adding content.
2	The SELECT Statement	Specifying the type of information to be retrieved from the database. Using `*` and `DISTINCT`.
3	The JOIN Clause	Combining records from two different tables.
4	The WHERE Clause	Filtering result of a returned query. Can also be used with other keywords like `JOIN` to link tables together based on a specified condition.
5	The WHERE Clause (Dynamic)	How to create dynamic WHERE Clauses with `WHERE 1=1`.
6	The GROUP BY Clause	Group rows by a specified criterion, filtering by using a `HAVING` clause, extensions of the function using `CUBE()`, `ROLLUP()`, and custom GROUPING SETS.
7	The ORDER BY Keyword	Ordering result-set by a `ascending` or `descending` order.
8	The LIMIT Clause	Restricting the size of the result-set.
9	The CREATE SCHEMA Statement	Understanding the use of schemas in a DBMS, how to initialize a schema by code (very rarely used), and how to add objects to a schema.
10	The INSERT INTO Command	How to add records to a table (individually and in bulk).
11	The UPDATE Statement	How to modify an existing tuple in a relation.
12	The DELETE Statement	Used to delete specified rows in a table or all.
13	The TRUNCATE() Statement	Used to delete all the rows from a table.

Complex SQL Queries

Order	File Name	Comments
1	Nested Queries	Building nested queries using different comparison operators within the `WHERE` clause (with/without additional keywords (e.g., `SOME`, `ALL`)), and collapsing nested queries (only for `IN`). Overview of subqueries after the FROM clause.
2	EXISTS, NOT EXISTS, UNIQUE Keywords with Nested Queries	Keywords to use with nested queries (`EXISTS`, `UNIQUE`, `NOT EXISTS`, `IN`).
2.1	Using "Select 1 from" to Check Existence	Checking for existence of rows that match certain conditions without returning any data.
3	Correlated Nested Queries	Nested queries where the inner query refers the same table from the outer query.
4	Multiway Joins	How to join three or more tables in a query.
5	Generating Temp Result-set Using `With` as CTEs	AKA `common table expression (CTE)`. Referenced in main query as like any relation. Can think of it as a "subquery". Plus how to created multiple CTEs.
6	Recursive (Hierarchical) Query	CTEs
6	Conditional Logic using CASE	Define set of conditions and corresponding actions to be taken based on those conditions. Analogous to the `IF-THEN-ELSE` in Python.
7	Conditional Logic Using `Decode()`	DECODE() is a single expression conditional function that compares an expression against a sequence of search values and returns the corresponding result when a match is found. `CASE WHEN` conditional logic is preferable for more complex logic.
8	Querying with JUNCTION Tables	Querying with junction tables.
9	UNIONs vs JOINs	When to use `UNION ALL` vs `JOINs`.
10	Loop Constructs	Only works with procedural extensions like PL/SQL in Oracle. No native looping constructs in SQL.

Specifying Constraints When Creating a DB

Order	File Name	Comments
1	The CONSTRAINT Keyword	Naming constraints and for altering constraints.
2	Primary Keys	How to specify primary keys when creating tables. Dropping and altering PKs.
3	Foreign Keys	Specifying foreign keys and specifying `referential triggered action` clause in response to changes or deletion of a record in the referenced table.
4	Candidate Keys	Specifying candidate keys using the `UNIQUE` clause
5	Attribute Domains	Specifying the domain of an attribute to enfore data integrity (e.g., ensure values are within permitted range of values given the datatype).
6	Semantic Constraints	Creating `ASSERTIONS` and `TRIGGERS`
7	Attribute Constraints	Other restrictions on the domains of attributes.

Aggregate Functions

SQL aggregate functions are used to perform calculations on a set of values and return a single value.

How does this differ from using arithmetic operators?

Aggregate functions are used for performing operations on a set of values, typically over a group of rows in a table.
Aggregate functions also return a single value for a group of rows, while arithmetic operators return a value for each individual row.

Restrictions in Calculation

Aggregate functions only aggregate vertically. If you want to perform a calculation across rows, you would do this with simple arithmetic. SQL aggregation is the task of collecting a set of values to return a single value

Order	File Name	Comments
1	SUM Function	Computing the total value of over an entire column.
2	COUNT Function	Counting the number of rows in a table or the number of values in a particular column.
3	AVG Function	Returning the average value of a numeric column.
4	MAX and MIN Functions	Retrieving the MAX and MIN values of a column or group.
5	LISTAGG Function	LISTAGG() lets you collect values from multiple rows and join them into a single string, usually separated by a comma or other delimiter.
6	PERCENTILE_CONT Function	Find value that cuts off the bottom n-th percentile of a ordered list of values.

Scalar Functions

How is this different from Scalar Functions?

Aggregate Functions: Operate over multiple rows and produce a summary result (i.e., computes over a set of rows and return a single result for each group or the entire dataset).
Scalar Functions: Operate on individual values and produce a result for each row.

Order	File Name	Comments
1	The GREATEST() Function	Return the largest value for a given row in a dataset against a list of expressions. These expressions can be columns directly from the dataset or combined with other functions.

Useful SQL Functions

Order	File Name	Comments
1	The Conditional Aggregation	How to use aggregate data based on specified conditions.
2	The DECODE() Function	Function unique to Oracle Databases. Extension of a more complex and powerful conditional statement in SQL.
3	The OVER() and PARTITION BY() Function	How to apply aggregation functions to a dataset by partitioning data into specific groups (i.e., partitions) based on 1 or more columns.
3	The NVL() and NVL2() Function	Oracle. How to replace NULL values with a specified default value with `NVL` function and `NVL2`.
4	The WINDOW Function	What is a window function and example of how to assign sequential integer to each row to a returned result set using ROW_NUMBER() window function.
5	The FIRST_VALUE() Function	Retrieve first value of a selected column of a specified window or partition.
6	The LAG() Function	Retrieve value of the previous row of the same result set.
7	The OFFSET Keyword	Skip a specified number of rows from the beginning of a query's result set before returning the remaining rows.

Schema Changes

Good Practice!

Back-up schema before making any changes!

In SQL, schema changes refer to modifications made to the structure or design of a database.
To change an operational database schema without recompilation of the entire database, below are some common schema change commands.

Order	File Name	Comments
1	ALTER TABLE	How to alter the structure of an existing table in the database schema.
2	DROP TABLE	How to remove an existing table from a database.