# Table of Contents

1. [SQL Simple Query Examples:](#orgca98bc3)
1. [SQl Query w/ constraints example:](#orgeeb1cd1)
2. [Operators for numerical data](#org1c38fc1)
3. [Operators for text data](#org5b8de30)
4. [SQL Query that removes duplicates:](#org61dafcf)
5. [SQL Query w/ ordered results:](#orgb6c8b93)
6. [SQL example w/ LIMIT and OFFSET:](#orga96d0d5)
7. [SQL example of JOIN:](#orgf299c27)
8. [SQL example of OUTER JOIN:](#orgead070d)
9. [SQL Example of Query with expression](#org2a7fdbd)
10. [SQL Example of aliasing columns and a table:](#org2ae5aa3)
11. [SQL Example of grouping by a column](#org34d0b22)
1. [Without HAVING](#org2ed3131)
2. [Filtering with HAVING](#org3a9d835)
12. [SQL Example of Queries with aggregate:](#org8e35462)
2. [SQL Complete Query:](#org09fc91c)
3. [Inserting data:](#org9943377)
1. [Insert statements with values for all columns](#org8b6f899)
2. [Insert statements with values for specific columns](#org7c399f4)
3. [Insert statements with expressions](#org1aaa150)
4. [Updating data:](#orgdd03349)
1. [Update statement with values](#orgbd9bc62)
5. [Deleting data:](#org8c46bf0)
1. [BE CAREFUL, WITHOUT A WHERE CONSTRAINT *EVERY* ROW IS REMOVED](#org8a2734d)
6. [Creating a table w/ optional existence check:](#orgdd3ba5d)
1. [Some table data types:](#orgdbb02b2)
2. [Some table constraints:](#org7e17591)
7. [Adding columns:](#org1b60415)
8. [Removing columns:](#org3a60c2a)
9. [Renaming a table:](#orgd04027a)
10. [Deleting a table w/ check to prevent errors:](#orgfeb5e45)

<a id="orgca98bc3"></a>

# SQL Simple Examples:

<a id="orgeeb1cd1"></a>

## SQL Query w/ constraints example:

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

<a id="org1c38fc1"></a>

## Operators for numerical data

<table border="2" cellspacing="0" cellpadding="6" rules="groups" frame="hsides">

<colgroup>
<col class="org-left" />

<col class="org-left" />

<col class="org-left" />
</colgroup>
<tbody>
<tr>
<td class="org-left">Operator</td>
<td class="org-left">Condition</td>
<td class="org-left">SQL Example</td>
</tr>

<tr>
<td class="org-left"><code>, !</code>, &lt; &lt;=, &gt;, &gt;=</td>
<td class="org-left">Standard numerical operators</td>
<td class="org-left">column-name != 4</td>
</tr>

<tr>
<td class="org-left">BETWEEN … AND …</td>
<td class="org-left">Number is within range of two values (inclusive)</td>
<td class="org-left">column-name BETWEEN 1.5 AND 10.5</td>
</tr>

<tr>
<td class="org-left">NOT BETWEEN … AND …</td>
<td class="org-left">Number is not within range of two values (inclusive)</td>
<td class="org-left">column-name NOT BETWEEN 1 AND 10</td>
</tr>

<tr>
<td class="org-left">IN (…)</td>
<td class="org-left">Number exists in a list</td>
<td class="org-left">column-name IN (2, 4, 6)</td>
</tr>

<tr>
<td class="org-left">NOT IN (…)</td>
<td class="org-left">Number does not exist in a list</td>
<td class="org-left">column-name NOT IN (1, 3, 5)</td>
</tr>
</tbody>
</table>

<a id="org5b8de30"></a>

## Operators for text data

<table border="2" cellspacing="0" cellpadding="6" rules="groups" frame="hsides">

<colgroup>
<col class="org-left" />

<col class="org-left" />

<col class="org-left" />
</colgroup>
<tbody>
<tr>
<td class="org-left">Operator</td>
<td class="org-left">Condition</td>
<td class="org-left">Example</td>
</tr>

<tr>
<td class="org-left">=</td>
<td class="org-left">Case sensitive exact string comparison (notice the single equals)</td>
<td class="org-left">column-name = &ldquo;abc&rdquo;</td>
</tr>

<tr>
<td class="org-left">!= or &lt;&gt;</td>
<td class="org-left">Case sensitive exact string inequality comparison</td>
<td class="org-left">column-name != &ldquo;abcd&rdquo;</td>
</tr>

<tr>
<td class="org-left">LIKE</td>
<td class="org-left">Case insensitive exact string comparison</td>
<td class="org-left">column-name LIKE &ldquo;ABC&rdquo;</td>
</tr>

<tr>
<td class="org-left">NOT LIKE</td>
<td class="org-left">Case insensitive exact string inequality comparison</td>
<td class="org-left">column-name NOT LIKE &ldquo;ABCD&rdquo;</td>
</tr>

<tr>
<td class="org-left">%</td>
<td class="org-left">Used anywhere in a string to match a sequence of zero or more characters</td>
<td class="org-left">column-name LIKE &ldquo;%AT%&rdquo;</td>
</tr>

<tr>
<td class="org-left">&#xa0;</td>
<td class="org-left">&#xa0;</td>
<td class="org-left">(matches &ldquo;AT&rdquo;, &ldquo;ATTIC&rdquo;, &ldquo;CAT&rdquo; or even &ldquo;BATS&rdquo;)</td>
</tr>

<tr>
<td class="org-left">-</td>
<td class="org-left">Used anywhere in a string to match a single character (only with LIKE or NOT LIKE)</td>
<td class="org-left">column-name LIKE &ldquo;AN_&rdquo; (matches &rsquo;AND&rsquo;, but no &rsquo;AN&rsquo;)</td>
</tr>

<tr>
<td class="org-left">IN (…)</td>
<td class="org-left">String exists in a list</td>
<td class="org-left">column-name IN (&ldquo;A&rdquo;, &ldquo;B&rdquo;, &ldquo;C&rdquo;)</td>
</tr>

<tr>
<td class="org-left">NOT IN (…)</td>
<td class="org-left">String does not exist in a list</td>
<td class="org-left">column-name NOT IN (&ldquo;D&rdquo;, &ldquo;E&rdquo;, &ldquo;F&rdquo;)</td>
</tr>

<tr>
<td class="org-left">&#xa0;</td>
<td class="org-left">&#xa0;</td>
<td class="org-left">&#xa0;</td>
</tr>
</tbody>
</table>

<a id="org61dafcf"></a>

## SQL Query that removes duplicates:

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

<a id="orgb6c8b93"></a>

## SQL Query w/ ordered results:

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

<a id="orga96d0d5"></a>

## 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.

<a id="orgf299c27"></a>

## 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.

<a id="orgead070d"></a>

## 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.

<a id="org2a7fdbd"></a>

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

<a id="org2ae5aa3"></a>

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

<a id="org34d0b22"></a>

## SQL Example of grouping by a column

<a id="org2ed3131"></a>

### Without HAVING

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

<a id="org3a9d835"></a>

### 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.

<a id="org8e35462"></a>

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

<table border="2" cellspacing="0" cellpadding="6" rules="groups" frame="hsides">

<colgroup>
<col class="org-left" />

<col class="org-left" />
</colgroup>
<tbody>
<tr>
<td class="org-left">Function</td>
<td class="org-left">Description</td>
</tr>

<tr>
<td class="org-left">MIN(column)</td>
<td class="org-left">Finds the smallest numerical value in the specified column for all rows in the group.</td>
</tr>

<tr>
<td class="org-left">MAX(column)</td>
<td class="org-left">Finds the largest numerical value in the specified column for all rows in the group.</td>
</tr>

<tr>
<td class="org-left">AVG(column)</td>
<td class="org-left">Finds the average numerical value in the specified column for all rows in the group.</td>
</tr>

<tr>
<td class="org-left">SUM(column)</td>
<td class="org-left">Finds the sum of all numerical values in the specified column for the rows in the group.</td>
</tr>

<tr>
<td class="org-left">Count(*)</td>
<td class="org-left">A common function used to counts the number of rows in the group if no column name is specified.</td>
</tr>

<tr>
<td class="org-left">Count(Column)</td>
<td class="org-left">Count the number of rows in the group with non-NULL values in the specified column.</td>
</tr>
</tbody>
</table>

<a id="org09fc91c"></a>

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

<a id="org9943377"></a>

# Inserting data:

<a id="org8b6f899"></a>

## 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, …),
…;

<a id="org7c399f4"></a>

## 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, …),
…;

<a id="org1aaa150"></a>

## Insert statements with expressions

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

<a id="orgdd03349"></a>

# Updating data:

<a id="orgbd9bc62"></a>

## Update statement with values

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

<a id="org8c46bf0"></a>

# Deleting data:

<a id="org8a2734d"></a>

## BE CAREFUL, WITHOUT A WHERE CONSTRAINT *EVERY* ROW IS REMOVED

DELETE FROM mytable
WHERE condition;

<a id="orgdd3ba5d"></a>

# 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,
…
);

<a id="orgdbb02b2"></a>

## Some table data types:

<table border="2" cellspacing="0" cellpadding="6" rules="groups" frame="hsides">

<colgroup>
<col class="org-left" />

<col class="org-left" />
</colgroup>
<thead>
<tr>
<th scope="col" class="org-left">Type</th>
<th scope="col" class="org-left">Description</th>
</tr>
</thead>

<tbody>
<tr>
<td class="org-left">INTEGER, BOOLEAN</td>
<td class="org-left">Same as in any programming language</td>
</tr>

<tr>
<td class="org-left">FLOAT, DOUBLE, REAL</td>
<td class="org-left">Floating point numbers withy varying precision</td>
</tr>

<tr>
<td class="org-left">CHARACTER(num), VARCHAR(num), TEXT</td>
<td class="org-left">Data type for strings. TEXT stores strings in all sorts of locales, the other data types impose a limit on the character number for performance</td>
</tr>

<tr>
<td class="org-left">DATE, DATETIME</td>
<td class="org-left">It&rsquo;s in the name</td>
</tr>

<tr>
<td class="org-left">BLOB</td>
<td class="org-left">Binary Large OBject: usually multimedia or binary executables, usually stored with some form of metadata for identification purposes</td>
</tr>
</tbody>
</table>

<a id="org7e17591"></a>

## Some table constraints:

<table border="2" cellspacing="0" cellpadding="6" rules="groups" frame="hsides">

<colgroup>
<col class="org-left" />

<col class="org-left" />
</colgroup>
<thead>
<tr>
<th scope="col" class="org-left">Constraint</th>
<th scope="col" class="org-left">Description</th>
</tr>
</thead>

<tbody>
<tr>
<td class="org-left">PRIMARY KEY</td>
<td class="org-left">This means that the values in this column are unique, and each value can be used to identify a single row in this table.</td>
</tr>

<tr>
<td class="org-left">AUTOINCREMENT</td>
<td class="org-left">For integer values, this means that the value is automatically filled in and incremented with each row insertion. Not supported in all databases.</td>
</tr>

<tr>
<td class="org-left">UNIQUE</td>
<td class="org-left">The values in this column must be unique. It does not have to be a key for a row, unlike PRIMARY KEY.</td>
</tr>

<tr>
<td class="org-left">NOT NULL</td>
<td class="org-left">This column cannot be &rsquo;NULL&rsquo;</td>
</tr>

<tr>
<td class="org-left">FOREIGN KEY</td>
<td class="org-left">This is a consistency check which ensures that each value in this column corresponds to another value in a column in another table.</td>
</tr>
</tbody>
</table>

<a id="org1b60415"></a>

# Adding columns:

ALTER TABLE mytable
ADD column DataType OptionalTableConstraint
DEFAULT default_value;

<a id="org3a60c2a"></a>

# Removing columns:

ALTER TABLE mytable
DROP column_to_be_deleted;

<a id="orgd04027a"></a>

# Renaming a table:

ALTER TABLE mytable