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 most elementary form of SQL Select statement. It uses that
statement to introduce students to the hospital database
The video here introduces students to the most basic way of merging two tables together. The SQL89
way of joining tables with a where clause is also introduced.
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.
The video here introduces students to joins where the primary key and foreign key
have exactly the same name and data type. I also advocate for not using this clause
as it leads to sloppy thinking.
Interestingly, the concept is not covered well by W3C, but is covered elsewhere:
The video here introduces students to the minus clause that essentially is the opposite
of a union clause. Note MySQL does not support the minus clause.
The W3C syllabus does not cover minus for some reason.
Not In
The video introduces students to Type I nested queries- queries involving in and not in.
The video introduces students to Type II nested queries- queries involving exists and not exists.
It also introduces students to the problem of three-valued logic and nulls in SQL.
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.