Notes on SQL

  1-- Select query for a specific columns
  2SELECT column,
  3			 another_column,
  4			 …
  5FROM mytable;
  6
  7-- Select query for all columns
  8SELECT *
  9FROM mytable;
 10
 11-- Select query with constraints
 12SELECT column,
 13			 another_column,
 14			 …
 15FROM mytable
 16WHERE _condition_
 17	AND/OR _another_condition_
 18	AND/OR …;
 19
 20-- Select query with unique results
 21SELECT DISTINCT column,
 22								another_column,
 23								…
 24FROM mytable
 25WHERE _condition(s)_;
 26
 27-- Select query with ordered results
 28SELECT column,
 29			 another_column,
 30			 …
 31FROM mytable
 32WHERE _condition(s)_
 33ORDER BY column ASC/DESC;
 34
 35-- Select query with limited rows
 36SELECT column,
 37			 another_column,
 38			 …
 39FROM mytable
 40WHERE _condition(s)_
 41ORDER BY column ASC/DESC
 42LIMIT num_limit
 43OFFSET num_offset;
 44
 45-- Select query with INNER JOIN on multiple tables
 46SELECT column,
 47			 another_table_column,
 48			 …
 49FROM mytable
 50INNER JOIN another_table
 51	ON mytable.id = another_table.id
 52WHERE _condition(s)_
 53ORDER BY column, … ASC/DESC
 54LIMIT num_limit
 55OFFSET num_offset;
 56
 57-- Select query with LEFT/RIGHT/FULL JOINs on multiple tables
 58SELECT column,
 59			 another_column,
 60			 …
 61FROM mytable
 62INNER/LEFT/RIGHT/FULL JOIN another_table
 63	ON mytable.id = another_table.matching_id
 64WHERE _condition(s)_
 65ORDER BY column, … ASC/DESC
 66LIMIT num_limit
 67OFFSET num_offset;
 68
 69-- Select query with constraints on NULL values
 70SELECT column,
 71			 another_column,
 72			 …
 73FROM mytable
 74WHERE column IS/IS NOT NULL
 75	AND/OR _another_condition_
 76	AND/OR …;
 77
 78-- Example query with expressions
 79SELECT particle_speed / 2.0 AS half_particle_speed
 80FROM physics_data
 81WHERE ABS(particle_position) * 10.0 > 500;
 82
 83-- Example query with both column and table name aliases
 84SELECT column AS better_column_name,
 85			 …
 86FROM a_long_widgets_table_name AS mywidgets
 87INNER JOIN widget_sales
 88	ON mywidgets.id = widget_sales.widget_id;
 89
 90-- Select query with aggregate functions over all rows
 91SELECT AGG_FUNC(_column_or_expression_) AS aggregate_description,
 92			 …
 93FROM mytable
 94WHERE _constraint_expression_;
 95
 96-- Select query with aggregate functions over groups
 97SELECT AGG_FUNC(_column_or_expression_) AS aggregate_description,
 98			 …
 99FROM mytable
100WHERE _constraint_expression_
101GROUP BY column;
102
103-- Select query with HAVING constraint
104SELECT group_by_column,
105			 AGG_FUNC(_column_expression_) AS aggregate_result_alias,
106			 …
107FROM mytable
108WHERE _condition_
109GROUP BY column
110HAVING _group_condition_;
111
112-- Complete SELECT query
113SELECT DISTINCT column,
114			 AGG_FUNC(_column_or_expression_),
115			 …
116FROM mytable
117JOIN another_table
118	ON mytable.column = another_table.column
119WHERE _constraint_expression_
120GROUP BY column
121HAVING _constraint_expression_
122ORDER BY _column_ ASC/DESC
123LIMIT _count_
124OFFSET _COUNT_;
125
126-- Insert statement with values for all columns
127INSERT INTO mytable
128VALUES (value_or_expr, another_value_or_expr, …),
129			 (value_or_expr_2, another_value_or_expr_2, …),
130			 …;
131
132-- Insert statement with specific columns (The number of values need to match
133the number of columns specified). Inserting values this way has the benefit of
134being forward compatible. For example, if you add a new column to the table with
135a default value, no hardcoded `INSERT` statements will have to change as a
136result to accommodate that change.
137INSERT INTO mytable (column, another_column, …)
138VALUES (value_or_expr, another_value_or_expr, …),
139			 (value_or_expr_2, another_value_or_expr_2, …),
140			 …;
141
142-- Update statement with values
143UPDATE mytable
144SET column = value_or_expr,
145		other_column = another_value_or_expr,
146		…
147WHERE condition;
148
149-- Delete statement with condition
150DELETE FROM mytable
151WHERE condition;
152
153-- Create table statement w/ optional table constraint and default value
154CREATE TABLE IF NOT EXISTS mytable ( column _DataType_ _TableConstraint_ DEFAULT
155_default_value_, another_column _DataType_ _TableConstraint_ DEFAULT
156_default_value_, … );
157
158-- Altering table to add new column(s)
159ALTER TABLE mytable ADD column _DataType_ _OptionalTableConstraint_ DEFAULT
160default_value;
161
162-- Altering table to remove column(s)
163ALTER TABLE mytable DROP column_to_be_deleted;
164
165-- Altering table name
166ALTER TABLE mytable RENAME TO new_table_name;
167
168-- Drop table statement
169DROP TABLE IF EXISTS mytable;

 1-- Example: General subquery
 2/*
 3Lets say your company has a list of all Sales Associates, with data on the
 4revenue that each Associate brings in, and their individual salary. Times are
 5tight, and you now want to find out which of your Associates are costing the
 6company more than the average revenue brought per Associate.
 7
 8First, you would need to calculate the average revenue all the Associates are
 9generating:
10*/
11SELECT AVG(revenue_generated)
12FROM sales_associates;
13/*
14And then using that result, we can then compare the costs of each of the
15Associates
16against that value. To use it as a subquery, we can just write it straight into
17the
18`WHERE` clause of the query:
19*/
20SELECT *
21FROM sales_associates
22WHERE salary > (
23	SELECT AVG(revenue_generated)
24	FROM sales_associates
25);

 1/*
 2Example: Correlated subquery
 3
 4Instead of the list of just Sales Associates above, imagine if you have a
 5general
 6list of Employees, their departments (engineering, sales, etc.), revenue, and
 7salary. This time, you are now looking across the company to find the employees
 8who perform worse than average in their department.
 9
10For each employee, you would need to calculate their cost relative to the
11average
12revenue generated by all people in their department. To take the average for the
13department, the subquery will need to know what department each employee is in:
14*/
15SELECT *
16FROM employees
17WHERE salary > (
18	SELECT AVG(revenue_generated)
19	FROM employees AS dept_employees
20	WHERE dept_employees.department = employees.department
21);

 1-- Select query with set operators
 2SELECT column,
 3	   another_column
 4FROM mytable
 5UNION / UNION ALL / INTERSECT / EXCEPT
 6SELECT other_column,
 7	   yet_another_column
 8FROM another_table
 9ORDER BY column DESC
10LIMIT _n_;