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

Database Schema

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 INTO person (id_person, first_name,
    last_name, nickname, birth_day, gender,
    height, id_location)
VALUES (DEFAULT, 'John', 'Doe', 'Johnny',
    NULL, 'male', DEFAULT, NULL)
INSERT INTO person
VALUES (DEFAULT, 'John', 'Doe', 'Johnny')

Insert – dates

  • Dates are inserted as strings (usually Y-m-d) or with a conversion function.
  • Insert date – PostgreSQL (first) vs. MySQL (second):
INSERT INTO person (first_name, last_name,
    nickname, birth_day)
VALUES ('John', 'Doe', 'Johnny',
    TO_TIMESTAMP('5.1.1984', 'DD.MM.YYYY'))
INSERT INTO person (first_name, last_name,
    nickname, birth_day)
VALUES ('John', 'Doe', 'Johnny',
    STR_TO_DATE('5.1.1984', '%d.%m.%Y'))

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

UPDATE person SET nickname = 'EthyHethy'
WHERE id_person = '1'
UPDATE person SET nickname = 'EthyHethy'
WHERE first_name = 'Ethyl'
    AND last_name = 'Herren' AND nickname='Ethy'

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

  • Equivalent queries:
SELECT * FROM
    contact LEFT JOIN contact_type
    ON contact.id_contact_type =
        contact_type.id_contact_type
SELECT * FROM
    contact_type RIGHT JOIN contact
    ON contact.id_contact_type =
        contact_type.id_contact_type

JOIN Examples

  • Equivalent queries:
SELECT * FROM
    contact INNER JOIN contact_type
    ON contact.id_contact_type =
        contact_type.id_contact_type
SELECT * FROM contact NATURAL JOIN contact_type

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 } pod-dotaz |
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%'
SELECT first_name, last_name FROM person
WHERE (first_name LIKE 'P%')
    OR (first_name LIKE 'L%')

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.
SELECT first_name, last_name FROM person
WHERE first_name IN ('Judy', 'July')

IN Operator

SELECT first_name, last_name FROM person
WHERE id_location IN (
    SELECT id_location FROM location
    WHERE country = 'United Kingdom'
)

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).
SELECT * FROM person
ORDER BY last_name DESC, nickname DESC

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?