Notes on SQL

-- Select query for a specific columns
SELECT column, another_column, … FROM mytable;

-- Select query for all columns
SELECT * FROM mytable;

-- Select query with constraints
SELECT column, another_column, … FROM mytable WHERE _condition_ AND/OR _another_condition_ AND/OR …;

-- Select query with unique results
SELECT DISTINCT column, another_column, … FROM mytable WHERE _condition(s)_;

-- Select query with ordered results
SELECT column, another_column, … FROM mytable WHERE _condition(s)_ ORDER BY column ASC/DESC;

-- Select query with limited rows
SELECT column, another_column, … FROM mytable WHERE _condition(s)_ ORDER BY column ASC/DESC LIMIT num_limit OFFSET num_offset;

-- Select query with INNER JOIN on multiple tables
SELECT column, another_table_column, … FROM mytable INNER JOIN another_table ON mytable.id = another_table.id WHERE _condition(s)_ ORDER BY column, … ASC/DESC LIMIT num_limit OFFSET num_offset;

-- Select query with LEFT/RIGHT/FULL JOINs on multiple tables
SELECT column, another_column, … FROM mytable INNER/LEFT/RIGHT/FULL JOIN another_table ON mytable.id = another_table.matching_id WHERE _condition(s)_ ORDER BY column, … ASC/DESC LIMIT num_limit OFFSET num_offset;

-- Select query with constraints on NULL values
SELECT column, another_column, … FROM mytable WHERE column IS/IS NOT NULL AND/OR _another_condition_ AND/OR …;

-- Example query with expressions
SELECT particle_speed / 2.0 AS half_particle_speed FROM physics_data WHERE ABS(particle_position) * 10.0 > 500;

-- Example query with both column and table name aliases
SELECT column AS better_column_name, … FROM a_long_widgets_table_name AS mywidgets INNER JOIN widget_sales ON mywidgets.id = widget_sales.widget_id;

-- Select query with aggregate functions over all rows
SELECT AGG_FUNC(_column_or_expression_) AS aggregate_description, … FROM mytable WHERE _constraint_expression_;

-- Select query with aggregate functions over groups
SELECT AGG_FUNC(_column_or_expression_) AS aggregate_description, … FROM mytable WHERE _constraint_expression_ GROUP BY column;

-- Select query with HAVING constraint
SELECT group_by_column, AGG_FUNC(_column_expression_) AS aggregate_result_alias, … FROM mytable WHERE _condition_ GROUP BY column HAVING _group_condition_;

-- Complete SELECT query
SELECT DISTINCT column, AGG_FUNC(_column_or_expression_), … FROM mytable JOIN another_table ON mytable.column = another_table.column WHERE _constraint_expression_ GROUP BY column HAVING _constraint_expression_ ORDER BY _column_ ASC/DESC LIMIT _count_ OFFSET _COUNT_;

-- Insert statement with values for all columns
INSERT INTO mytable VALUES (value_or_expr, another_value_or_expr, …), (value_or_expr_2, another_value_or_expr_2, …), …;

-- Insert statement with specific columns (The number of values need to match the number of columns specified). Inserting values this way has the benefit of being forward compatible. For example, if you add a new column to the table with a default value, no hardcoded `INSERT` statements will have to change as a result to accommodate that change.
INSERT INTO mytable (column, another_column, …) VALUES (value_or_expr, another_value_or_expr, …), (value_or_expr_2, another_value_or_expr_2, …), …;

-- Update statement with values
UPDATE mytable SET column = value_or_expr, other_column = another_value_or_expr, … WHERE condition;

-- Delete statement with condition
DELETE FROM mytable WHERE condition;

-- Create table statement w/ optional table constraint and default value
CREATE TABLE IF NOT EXISTS mytable ( column _DataType_ _TableConstraint_ DEFAULT _default_value_, another_column _DataType_ _TableConstraint_ DEFAULT _default_value_, … );

-- Altering table to add new column(s)
ALTER TABLE mytable ADD column _DataType_ _OptionalTableConstraint_ DEFAULT default_value;

-- Altering table to remove column(s)
ALTER TABLE mytable DROP column_to_be_deleted;

-- Altering table name
ALTER TABLE mytable RENAME TO new_table_name;

-- Drop table statement
DROP TABLE IF EXISTS mytable;

-- Example: General subquery
/*
Lets say your company has a list of all Sales Associates, with data on the revenue that each Associate brings in, and their individual salary. Times are tight, and you now want to find out which of your Associates are costing the company more than the average revenue brought per Associate.

First, you would need to calculate the average revenue all the Associates are generating:
*/
SELECT AVG(revenue_generated) FROM sales_associates;
/*
And then using that result, we can then compare the costs of each of the Associates against that value. To use it as a subquery, we can just write it straight into the `WHERE` clause of the query:
*/
SELECT * FROM sales_associates WHERE salary > (SELECT AVG(revenue_generated) FROM sales_associates);

/*
Example: Correlated subquery

Instead of the list of just Sales Associates above, imagine if you have a general list of Employees, their departments (engineering, sales, etc.), revenue, and salary. This time, you are now looking across the company to find the employees who perform worse than average in their department.

For each employee, you would need to calculate their cost relative to the average revenue generated by all people in their department. To take the average for the department, the subquery will need to know what department each employee is in:
*/
`SELECT * FROM employees WHERE salary > (SELECT AVG(revenue_generated) FROM employees AS dept_employees **WHERE dept_employees.department = employees.department**);`

-- Select query with set operators
SELECT column, another_column FROM mytable **UNION / UNION ALL / INTERSECT / EXCEPT SELECT other_column, yet_another_column FROM another_table** ORDER BY column DESC LIMIT _n_;