Lecture 7: SQL

Title slide

Slides : Recording

Today’s lecture introduced the basic structure and format of SQL queries: SELECT … FROM … WHERE …. That’s enough to write a huge range of queries, from single summary statistics to large integrated views that bring together multiple tables.

The written form of SQL queries is closely related to tuple relational calculus, while their implementation requires mapping into expressions of relational algebra. There’s an important step here: the SQL queries set out what information we want to find out, and the database engine beneath does some serious planning work to determine how to efficiently compute that information.

The lecture also covered sets and bags, fiddling details of quotation in SQL token syntax, some advice about tutorials and working hours, and a graph from the Bank of England.

Links: Slides for Lecture 7; Recording

Homework

Do This

The features that characterise a reliable implementation of database transactions are standardly initialized as the ACID properties.

Find out what each letter A C I D stands for here, and what those four terms mean.

Try This

Write some SQL by hand using one of these web demonstrators.

  • SQL Tryit. This is from W3Schools and is ready set up with some small sample tables.
  • SQL Bolt Includes an SQL tutorial with exercises: I recommend Lessons 1–3.
  • SQL Fiddle This is a substantial developer tool: significantly more functionality, but also a little bit more work to get operating.

References

Graph of weekly working hours in Britain from 1765 to 2015

Three Centuries of Macroeconomic Data
Bank of England

Average full-time working week in Britain for the last 250 years.

Link: Three Centuries dataset

There is no spoon

The Matrix
The Wachowski Brothers (1999)

There is no spoon

Link: YouTube clip