Database Research Group

WSI – Database Systems Research Group

Advanced SQL


News
  • Apr 17, 2017 — First lecture on Tuesday, April 18, 10:15 in room C215. First tutorial on Thursday, April 27, when we have collected the first batch of interesting material. — Torsten Grust

  • Apr 20, 2017 — The forum is now accessible via this link. We will use it, to communicate details and discussions about the lecture, weekly assignments and other related topics. — Christian Duta


Relational database systems provide efficient storage for large volumes of data. This course highlights that these systems also provide a versatile and expressive data processing language: SQL. There's much more to SQL than the plain SELECT...FROM...WHERE clause and we will see that a surprisingly large number of algorithmic problems can be tackled using SQL. Moving computation close to the data is key to unlock the true potential of database systems.

Selected course topics include

  • common table expressions (WITH),
  • non-standard data types (arrays, geometric data, JSON, XML),
  • table functions,
  • window functions,
  • recursive computation,
  • user-defined SQL procedures (PL/SQL),
  • index design for complex SQL queries,
  • off-beat SQL applications, useful SQL idioms, and fun SQL puzzles.

The course will only provide a brief introduction to the fundamental aspects of relational database systems. We expect you to have basic SQL skills (through prior attendance of Datenbanksysteme I or personal projects, for example) or be willing to acquire such skills.

Join us for a boatload of SQL fun! We will provide more course details once the semester approaches.


Slides
NrChapterDownload
1Welcome
  • Administrativa
  • Fundamentals of the tabular (relational) data model
pdf
2The Core of SQL
  • A tour of core SQL constructs
  • Query conventions in this course

[ updated: April 25, 2017, 13:54 ]

pdf
3Standard and Non-Standard Data Types
  • Type casts (in particular from type text)
  • The variety of types of values that may be stored in table cells:
    • text and numeric data
    • ranges
    • user-defined enumerated types
    • dates, times, timestamps, and intervals
    • bit strings
    • binary large objects (BLOBs)
    • geometric objects
    • JSON and XML documents
    • sequences

[ updated: May 9, 2017, 13:41 ]

pdf
4Arrays and User-Defined Functions
  • The type τ[] (or τ array)
  • Computation over arrays
  • Array unnesting and aggregation
  • Table-generating functions
  • User-defined SQL functions (UDFs)
  • LATERAL (sideways row variable passing)
  • Sample problem (Finding Seats)

[ updated: May 22, 2017, 22:27 ]

pdf
Additional material (code, data)
NrFileDownload
1Handout

Instructions for participating in the "Advanced SQL" exercises.

pdf
2core-of-SQL.sql

A collection of example SQL queries, formulated using the core constructs of SQL.

Usage:

  • Cut & paste individual table definition and queries into your psql PostgreSQL shell (just like in the lecture), or
  • use psql -d ‹database› -f core-of-SQL.sql to execute the entire file at once (probably less useful).
sql
3data-types.sql

Examples that demonstrate the use of a variety of SQL data types.

Usage:

  • Cut & paste individual table definition and queries into your psql PostgreSQL shell (just like in the lecture), or
  • use psql -d ‹database› -f data-types.sql to execute the entire file at once (probably less useful).
sql
4glados.sql

Demonstrates the use of BLOBs (binary large objects) stored in columns of type bytea.

Notes:

  • User-defined function read_blob() opens and reads the specified file (you need to provide an absolute path to the file) and returns a bytea value.

  • Adapt the blob_path variable to point a directory (absolute path) where your multi-media objects (e.g. audio/video/image files) are located.

  • The .wav files with GlaDOS quotes are not included in this file. You'll find countless examples for download in the Portal Wiki.

  • The final query in the file extracts one selected GlaDOS quote and emits it in base64 encoding. Use a base64 decoder to obtain the original multi-media object again.

sql
5scanner.sql

Demonstrates the use of geometric objects and operations on these: scan the contour of a 2D polygon.

Notes:

  • Column shapes of common table expression shapes can contain arbitrary polygons.

  • Variable shape (see \set shape ...) defines the shape id in table shapes whose contour will be output (see below).

  • Adapt variable csv to point to an absolute file path where the contour will be saved in the textual CSV format.

  • If you have GnuPlot installed, run gnuplot scanner.gpl to render the contour in the CSV file in ASCII format. For scanner.gpl, see below.

sql
6scanner.gpl

Companion GnuPlot file to be used with scanner.sql (see above). Adapt the paths in this file to match the variable csv in scanner.sql.

gpl
7arrays.sql
  • A representation of labeled trees and forests using PostgreSQL arrays of type int[] and text[].

sql
8table-functions.sql
  • Table-generating functions (generateseries(), regexpmatches(), ...)

  • User-defined SQL functions (UDFs)

  • LATERAL queries

sql