Lecture 7: SQL

Title slideThis 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, and the offside rule in football.

Link: Slides for Lecture 7

Homework

Try writing 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 Fiddle This is a substantial developer tool: significantly more functionality, but also a little bit more work to get operating.

References

2D Goggles
or
The Thrilling Adventures of Lovelace and Babbage

A Highly Irregular Webcomic, With Footnotes

By Sydney Padua. Contains mathematics, computer science, and crime-fighting.

Diagram illustrating a player in offside positionOne of these players is offside Association Football Offside Law

Important and conceptually well-motivated; tried, tested and improved over decades; but notoriously difficult to use and an endless source of dispute.

Link: Wikipedia on the offside law