SQL Queries (Required Part)
SQL queries have the following form:
SELECT [] FROM [] WHERE [] GROUP BY [] HAVING [] ORDER BY []
The first two parts are required to have a valid query.
Basic Query
For the following table:
| birthday | first_name | family_name |
|---|---|---|
| 1990-11-10 | Anne | Smith |
| 200-02-05 | David | Jones |
| 1995-05-09 | William | Taylor |
SELECT * FROM Employees
This will return all the entries in a given table.
SELECT
SELECT defines what is outputted, making you able to do four kinds of modifications to it:
- Projection ($\pi$)
DISTINCT- Renaming ($\rho$)
- Creating new columns.
The symbols will be explained in the SQL misc. lecture.
You can also combine these modifications as you like.
Projection ($\pi$)
Projection allows you to sleect attributes you wan to keep.
You use it by writing a list of attributes:
SELECT family_name, birthday FROM Employees;
The order of the attributes matters.
DISTINCT
DISTINCT is for removing duplicated rows. If we select normally like this:
SELECT first_name FROM Employees;
then we will get duplicates if two entries share an attribute.
We can use the following:
SELECT DISTINCT first_name FROM Employees;
to only get unique entries for a projection.
Renaming ($\rho$)
This allows you to rename attributes. You use it by writing AS and then the new name after the attribute:
SELECT birthday, first_name, family_name AS surname FROM Employees;
This would rename the family_name attribute to surname on the output table.
Creating New Columns
You can also create new columns, using maths on current columns. You would also typically give the column a new name.
For the following table:
| name | price | number |
|---|---|---|
| 2L Cola | 3.00 | 20 |
| Banana | 0.10 | 120 |
| Toilet Paper | 2.00 | 0 |
running this query:
SELECT name, price, number, price * number AS total_cost FROM Items;
would give the following result:
| name | price | number | total_cost |
|---|---|---|---|
| 2L Cola | 3.00 | 20 | 90.00 |
| Banana | 0.10 | 120 | 12.00 |
| Toilet Paper | 2.00 | 0 | 0.00 |
Other operations are +, -, / and %.
You can also do aggregates, like sums or counts over the output table. For example, to sum up all the items in the shop you could run the following query:
SELECT SUM(number) FROM Items;
This would produce the following table:
| SUM(number) |
|---|
| 150 |
You can also do COUNT, AVG, MIN and MAX.
FROM
FROM can contain many input tables and we combine them together in various ways. You can combine them with:
- Cross Product ($\times$)
- Natural Join ($\bowtie$)
Cross Product ($\times$)
For the following tables:
| birthday | first_name | family_name |
|---|---|---|
| 1990-11-10 | Anne | Smith |
| 200-02-05 | David | Jones |
| 1995-05-09 | William | Taylor |
| name | price | number |
|---|---|---|
| 2L Cola | 3.00 | 20 |
| Banana | 0.10 | 120 |
with the following query:
SELECT first_name, name FROM Employees, Items;
you will get the following output table:
| first_name | name |
|---|---|
| Anne | 2L Cola |
| Anne | Banana |
| David | 2L Cola |
| David | Banana |
| William | 2L Cola |
| William | Banana |
This is every combination of rows, in the order of Employees $\times$ Items.
It is called the product as the size of the resultant table is $3\times2=6$.
Natural Join ($\bowtie$)
The two tables to be joined should have some overlap. For the following tables:
| birthday | first_name | family_name | e_id |
|---|---|---|---|
| 1990-11-10 | Anne | Smith | 1 |
| 200-02-05 | David | Jones | 2 |
| 1995-05-09 | William | Taylor | 3 |
| t_id | c_id | e_id |
|---|---|---|
| 1 | 3 | 1 |
| 2 | 6 | 1 |
| 3 | 19 | 3 |
with the following query:
SELECT * FROM Employees NATURAL JOIN Transactions
| birthday | first_name | family_name | e_id | t_id | c_id |
|---|---|---|---|---|---|
| 1990-11-10 | Anne | Smith | 1 | 3 | 1 |
| 200-02-05 | David | Jones | 2 | 6 | 1 |
| 1995-05-09 | William | Taylor | 3 | 19 | 3 |
This just extends the attributes and joins with identical attributes.
Formally, you take the cross product, remove all rows where the common attributes don’t match and then only keep one column fro each common attribute.
This means that the following query is the same as the last natural join:
SELECT birthday, first_name, family_name, Employees.e_id AS e_id, t_id, c_id
FROM Employees, Transactions
WHERE Employees.e_id = Transactions.e_id;
Using Tablename.attribute is how you reference an attribute multiple tables have in common.
You can also assign shorthands to table names:
SELECT birthday, first_name, family_name, E.e_id AS e_id, t_id, c_id
FROM Employees E, Transactions T
WHERE E.e_id = T.e_id;
Natural join has some issues when equivalent records (or attributes with different meanings.) can’t be found between tables. This results in empty output.