Introduction
- SQL commands may be terminated with a semicolon
;
(delimiter, not a terminator).
- Strings must be quoted with single quotes
'
(quote numbers as well).
- Create Database – the SQL command
CREATE
.
- Syntax is fairly complicated and highly dependent on the specific database system.
- Check the manual for details.
- No need to learn this thing!
- Proper tools are able to generate the
CREATE
commands from visual database design.
Create Database
Insert data
- The SQL command INSERT INTO [ (column_list) ] VALUES (value_list)
INSERT INTO persons (first_name, last_name, nickname) VALUES ('John', 'Doe', 'Johnny')
- Columns not listed are inserted with default values (usually
NULL
).
- If the default value is
NULL
, and the column is NOT NULL
, then a value must be provided.
- In an application code, all columns should be listed.
- An automatically generated primary key (auto_increment, serial) is almost never inserted.
Insert – examples
Insert – dates
- Dates are inserted as strings (usually
Y-m-d
) or with a conversion function.
- Insert date – PostgreSQL (first) vs. MySQL (second):
Updating data
- The SQL command UPDATE table SET column = expression [,column = expression …] [ WHERE search_condition ]
UPDATE person SET height = '196' WHERE id_person = '42'
- If the
WHERE
clause is missing, all rows are updated!
UPDATE person SET birth_day=NULL
- The
WHERE
condition usually contains a key.
- If compound, then all parts must be provided!
- To remove a value, set it to
NULL
.
Update Examples
Deleting Data
- The SQL command DELETE FROM table WHERE search_condition
DELETE FROM persons WHERE id_person = 42
- If the search condition is missing, all rows in the table will be deleted.
- DELETE removes entire rows; to remove a single value, use UPDATE.
Selecting Data
- The SQL command SELECT FROM tables WHERE search_condition
- SELECT is by far the most complicated SQL command.
- You can be learning it for years and never finish.
- Important clauses:
FROM
– source tables from which you select data,
WHERE
– a search condition – the same as in UPDATE
or DELETE
,
JOIN
– for joining multiple tables together,
ORDER BY
– for ordering rows in the result,
GROUP BY
– for aggregating data,
HAVING
– a search condition for aggregated data.
Select syntax (simplified)
SELECT [ ALL | DISTINCT ] column_expression, ...
FROM table_expression
[ WHERE search_condition ]
[ GROUP BY column_expression [, ... ] ]
[ HAVING search_condition ]
[ ORDER BY { column_expression [ ASC | DESC ] }
[, column_expression [ASC | DESC ], ... ]
Select Expressions
- column_expression is either a column name or expression which evaluates to a single value:
- arithmetic operation:
4 + 2
,
- function call:
MAX(column)
,
- sometimes a SQL query is also allowed.
SELECT ... FROM
– projection from relational algebra
WHERE
– restriction from relational algebra
SELECT * FROM persons
– all columns and all rows from the table persons
SELECT id_person, first_name, last_name FROM persons WHERE height > 190
SELECT Clause
SELECT [ ALL | DISTINCT ] column_expression, ...
FROM table_expression
[ WHERE search_condition ]
[ GROUP BY column_expression [, ... ] ]
[ HAVING search_condition ]
[ ORDER BY { column_expression [ ASC | DESC ] }
[, column_expression [ASC | DESC ], ... ]
SELECT Clause
- SELECT [ ALL | DISTINCT ] { * | table.* | [table.]column [AS alias] [, table.]column [ AS alias] … }
FROM …
*
– select all columns available in the FROM
clause.
table.*
– select all columns from the given table.
table.column
– select the column from the given table.
AS
– assign a temporary different name to a column – alias.
SELECT – Examples
- Variants of the same query:
SELECT meeting.id_meeting, meeting.start, meeting.description, meeting.duration, meeting.id_location FROM meeting
SELECT meeting.* FROM meeting
SELECT * FROM meeting
- Select all places where some meeting occurs:
SELECT id_location FROM meeting
SELECT ALL id_location FROM meeting
- Without duplicate rows (table × relation):
SELECT DISTINCT id_location FROM meeting
FROM Clause
SELECT [ ALL | DISTINCT ] column_expression, ...
FROM table_expression
[ WHERE search_condition ]
[ GROUP BY column_expression [, ... ] ]
[ HAVING search_condition ]
[ ORDER BY { column_expression [ ASC | DESC ] }
[, column_expression [ASC | DESC ], ... ]
Table Expression
- Selecting data from a single table is not really useful.
- To select data from multiple tables, they must be JOINed.
- By joining tables (Θ - join) the original schema can be reconstructed.
- A table is:
- relation (a physical table in a schema)
- view (a virtual table in a schema)
- result of query (a volatile table)
- result of join (a volatile table)
JOIN Operator
... FROM
table [AS alias]
[ LEFT | RIGHT | INNER ] JOIN
table [AS alias]
ON join_condition
[ WHERE ...
- On both left and right side of the JOIN, there is a table.
- The joined table contains rows for which the join condition is true.
- CROSS JOIN / Cartesian Join / Cartesian Product :
SELECT * FROM persons, locations
INNER JOIN Operator
- You can join with Cartesian Product.
JOIN
is more flexible.
SELECT * FROM persons INNER JOIN contact ON persons.id_person = contact.id_person
- Selects rows from both tables which satisfy the join condition (1:1 or 1:1..N).
- Selects all persons which have some contact.
- Each person is listed as many times as she has contacts.
- If column names are same, the condition can be shortened:
SELECT * FROM persons INNER JOIN contact USING(id_person)
INNER JOIN
is default and symmetric.
LEFT / RIGHT JOIN
SELECT * FROM person LEFT JOIN contact ON person.id_person = contact.id_person
- Selects all rows from the left table and rows satisfying the condition from the right table (1:0..1 or 1:0..N).
- Selects all persons and if the persons have contacts, lists the contacts too.
SELECT * FROM person RIGHT JOIN contact ON person.id_person = contact.id_person
- Selects all rows from the right table and rows satisfying the condition from the left table.
- Selects all contacts and lists persons with each contact (same as INNER JOIN in this case).
LEFT / RIGHT JOIN
SELECT * FROM relation LEFT JOIN relation_type ON relation.id_relation_type = relation_type.id_relation_type
- Selects all relations and lists the relation type for each relation.
- Lists also relations which have no type (cannot exist).
SELECT * FROM relation RIGHT JOIN relation_type ON relation.id_relation_type = relation_type.id_relation_type
- Select all relation types and assigns the relation type to each relation.
- Lists also relation types which are not used.
JOIN Examples
JOIN Examples
JOIN Summary
- JOIN is used to obtain data from multiple tables.
- An alias is used when the same table is used more times in join.
- The join condition is usually an equality between two columns.
- One of them usually has a foreign key.
- The join condition can be more exotic (AND / OR, inequality)
- Join connects tables – relations, views, other joins, selects.
WHERE Clause
SELECT [ ALL | DISTINCT ] column_expression, ...
FROM table_expression
[ WHERE search_condition ]
[ GROUP BY column_expression [, ... ] ]
[ HAVING search_condition ]
[ ORDER BY { column_expression [ ASC | DESC ] }
[, column_expression [ASC | DESC ], ... ]
WHERE Clause
WHERE {
column_expression
[NOT] { = | <> | < | <= | > | >= } column_expression |
{ ALL | ANY | SOME } sub-query |
column_expression
[NOT] IN ( { set_of_values | sub-query } ) |
column_expression [NOT] LIKE pattern |
[ { AND | OR } …]
WHERE Clause
LIKE
pattern can contain:
- underscore
_
– placeholder for a single character:
'Ju_y'
matches ‘July’, ‘Judy’
- percent
%
– placeholder for 0 or more characters:
'J%'
matches ‘Jo’, ‘June’, …
NOT
operator is unary boolean operator – two notations:
WHERE NOT first_name LIKE 'J%'
WHERE first_name NOT LIKE 'J%'
IN Operator
- Matches a column value against a set of values:
- static list in the query,
- dynamic result of a sub-query.
- In either case it shouldn’t have duplicates.
IN Operator
ORDER BY Clause
SELECT [ ALL | DISTINCT ] column_expression, ...
FROM table_expression
[ WHERE search_condition ]
[ GROUP BY column_expression [, ... ] ]
[ HAVING search_condition ]
[ ORDER BY { column_expression [ ASC | DESC ] }
[, column_expression [ASC | DESC ], ... ]
ORDER BY Clause
- Ordering can be done by multiple criteria, modifiers apply to each criteria:
ASC
– ascending (A-Z, oldest-newest) – default ordering,
DESC
– descending (Z-A, newest-oldest).
Checkpoint
- Is it possible to join more than two tables?
- What happens if you run a
DELETE
without a WHERE
?
- Can you join a table in the database with result of a
SELECT
?
- What are aliases useful for?
- Is it necessary to give the join condition for every join?
- Why should you list column names in an
INSERT
?
- Can you insert date to the database in arbitrary format?
- What are the most common methods of joining tables?
- Why is using a cartesian product not a good idea?