owned this note
owned this note
Published
Linked with GitHub
# 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>, < <=, >, >=</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 = “abc”</td>
</tr>
<tr>
<td class="org-left">!= or <></td>
<td class="org-left">Case sensitive exact string inequality comparison</td>
<td class="org-left">column-name != “abcd”</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 “ABC”</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 “ABCD”</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 “%AT%”</td>
</tr>
<tr>
<td class="org-left"> </td>
<td class="org-left"> </td>
<td class="org-left">(matches “AT”, “ATTIC”, “CAT” or even “BATS”)</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 “AN_” (matches ’AND’, but no ’AN’)</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 (“A”, “B”, “C”)</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 (“D”, “E”, “F”)</td>
</tr>
<tr>
<td class="org-left"> </td>
<td class="org-left"> </td>
<td class="org-left"> </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<sub>limit</sub> 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’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 ’NULL’</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