This section of the website provides lecture materials, exercises and videos for Information Systems instructors interested in developing courses for teaching SQL.
The lectures proceed in order and the examples become increasingly difficult. It is expected
the viewer has done the previous examples before going to a specific video. For example,
while traditional insert, update and delete commands are straightforward, by the time we introduce
those concepts, we have already learned various forms of nesting and the insert, update and delete
commands introduced come with that nesting.
The lectures are based on standard SQL, specifically SQL92. Additions to the standard in SQL:1999
and beyond are for particular niche cases of SQL (e.g., data warehousing) and don't need to be
taught in an introductory database course. The lecture structure is closely patterned
after the W3C syllabus.
It is assumed the idea of a relational database has already been introduced, and the concepts of
tables/relations, attributes/columns, tuples/rows, primary key, foreign key and composite keys
are already known.
The lectures employ a specific hospital database as a running example. Versions of that database
are provided here.
The video here introduces students to the left, right and full outer join concepts. It also introduces
students to the union concept and shows how a left, right and full outer join are equivalent
to various kinds of union.
The video here introduces students to sorting in SQL. Traditionally, order by is taught
together with other single-table clauses. However, order by is unique in that it must be the
last clause in an SQL select statement. Students don't really understand what that means
until the union clause is introduced and so I teach it here.
I don't teach the SQL commands to create tables, because (1) they vary considerably across
commercial database platforms and (2) it is more efficient to create tables with a user interface
you should never have to create tables programmatically.
These are exercises for creating tables I use with my students.