Table of Contents

1. SQL Simple Query Examples:
   1. SQl Query w/ constraints example:
   2. Operators for numerical data
   3. Operators for text data
   4. SQL Query that removes duplicates:
   5. SQL Query w/ ordered results:
   6. SQL example w/ LIMIT and OFFSET:
   7. SQL example of JOIN:
   8. SQL example of OUTER JOIN:
   9. SQL Example of Query with expression
   10. SQL Example of aliasing columns and a table:
   11. SQL Example of grouping by a column
       1. Without HAVING
       2. Filtering with HAVING
   12. SQL Example of Queries with aggregate:
2. SQL Complete Query:
3. Inserting data:
   1. Insert statements with values for all columns
   2. Insert statements with values for specific columns
   3. Insert statements with expressions
4. Updating data:
   1. Update statement with values
5. Deleting data:
   1. BE CAREFUL, WITHOUT A WHERE CONSTRAINT EVERY ROW IS REMOVED
6. Creating a table w/ optional existence check:
   1. Some table data types:
   2. Some table constraints:
7. Adding columns:
8. Removing columns:
9. Renaming a table:
10. Deleting a table w/ check to prevent errors:

SQL Simple Examples:

SQL Query w/ constraints example:

​​​​SELECT column, another_column, …
​​​​FROM mytable
​​​​WHERE condition
​​​​    AND/OR another_condition
​​​​    AND/OR …;

Operators for numerical data

Operators for text data

SQL Query that removes duplicates:

​​​​SELECT DISTINCT column, another_column, …
​​​​FROM mytable
​​​​WHERE condition(s);

SQL Query w/ ordered results:

​​​​SELECT column, another_column, …
​​​​FROM mytable
​​​​WHERE condition(s)
​​​​ORDER BY column ASC/DESC;

SQL example w/ LIMIT and OFFSET:

​​​​SELECT column, another_column, …
​​​​FROM mytable
​​​​WHERE condition(s)
​​​​ORDER BY column ASC/DESC
​​​​LIMIT num_limit OFFSET num_offset;

LIMIT returns up to num_limit rows and OFFSET specifies from where to start counting rows.

SQL example of JOIN:

Using the JOIN clause in a query, we can combine row data across two separate tables using this unique key. The first of the joins that we will introduce is the INNER JOIN.

​​​​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;

The INNER JOIN is a process that matches rows from the first table and the second table which have the same key (as defined by the ON constraint) to create a result row with the combined columns from both tables. After the tables are joined, the other clauses we learned previously are then applied.

SQL example of OUTER JOIN:

​​​​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;

When joining table A to table B, a LEFT JOIN simply includes rows from A regardless of whether a matching row is found in B. The RIGHT JOIN is the same, but reversed, keeping rows in B regardless of whether a match is found in A. Finally, a FULL JOIN simply means that rows from both tables are kept, regardless of whether a matching row exists in the other table.

SQL Example of Query with expression

​​​​SELECT particle_speed / 2.0 AS half_particle_speed
​​​​FROM physics_data
​​​​WHERE ABS(particle_position) * 10.0 > 500;

SQL Example of aliasing columns and a table:

​​​​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;

SQL Example of grouping by a column

Without HAVING

​​​​SELECT group_by_column, AGG_FUNC(column_expression) AS aggregate_result_alias, …
​​​​FROM mytable
​​​​WHERE condition
​​​​GROUP BY column

Filtering with HAVING

​​​​SELECT group_by_column, AGG_FUNC(column_expression) AS aggregate_result_alias, …
​​​​FROM mytable
​​​​WHERE condition
​​​​GROUP BY column
​​​​HAVING group_condition;

GROUP BY divides the query result into groups of rows.
HAVING filters rows resulting from a GROUP.

SQL Example of Queries with aggregate:

SQL also supports the use of aggregate expressions (or functions) that allow you to summarize information about a group of rows of data.

​​​​SELECT AGG_FUNC(column_or_expression) AS aggregate_description, …
​​​​FROM mytable
​​​​WHERE constraint_expression;

SQL Complete 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;

Inserting data:

Insert statements 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 statements with values for specific columns

​​​​INSERT INTO mytable
​​​​(column, another_column, …)
​​​​VALUES (value_or_expr, another_value_or_expr, …),
​​​​      (value_or_expr_2, another_value_or_expr_2, …),
​​​​      …;

Insert statements with expressions

​​​​INSERT INTO boxoffice
​​​​(movie_id, rating, sales_in_millions)
​​​​VALUES (1, 9.9, 283742034 / 1000000);

Updating data:

Update statement with values

​​​​UPDATE mytable
​​​​SET column = value_or_expr,
​​​​    other_column = another_value_or_expr,
​​​​    …
​​​​WHERE condition;

Deleting data:

BE CAREFUL, WITHOUT A WHERE CONSTRAINT EVERY ROW IS REMOVED

​​​​DELETE FROM mytable
​​​​WHERE condition;

Creating a table w/ optional existence check:

​​​​CREATE TABLE IF NOT EXISTS mytable (
​​​​       column DataType TableConstraint DEFAULT default_value,
​​​​       another_column DataType TableConstraint DEFAULT default_value,
​​​​       …
​​​​);

Some table data types:

Some table constraints:

Adding columns:

​​​​ALTER TABLE mytable
​​​​ADD column DataType OptionalTableConstraint
​​​​    DEFAULT default_value;

Removing columns:

​​​​ALTER TABLE mytable
​​​​DROP column_to_be_deleted;

Renaming a table:

​​​​ALTER TABLE mytable