Introduction

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.

• Oracle version
• MySQL version
• ER Diagram and Relational Schema

The current contents are:

• Introduction
• One Table Queries
• Simple Select
• Where and Distinct clauses and logical operations
• In and Between operators
• Like operator
• Joins
• Inner Join
• Outer Joins
• Order By
• Self Joins
• Natural Joins
• Nested Queries
• Minus clause
• Not in operator
• Not exists operator
• Statistical Queries
• Simple statistical queries
• Group by clause
• Having clause
• Nested statistical queries
• Changing the Database
• Insert queries
• Update queries
• Delete queries
• Creating tables and relationships

Any questions or comments should be directed to: The creator's email

Simple Select

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

Materials covered in the W3C syllabus include:

• SQL Syntax
• SQL Select

Where, Distinct, Logical Operations

The video here introduces students to the basic commands for filtering data in a table or query.

Materials covered in the W3C syllabus include:

• Distinct
• Where Clause
• And, Or, Not

In and Between Operations

The video here introduces students to ways of obtaining data for an attribute belonging to a set or a range.

Materials covered in the W3C syllabus include:

• In
• Between

Like Operations

The video here introduces students to ways of obtaining data for an attribute using a string wildcard search.

Materials covered in the W3C syllabus include:

• Like
• Wildcards

Inner Join

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.

Materials covered in the W3C syllabus include:

• Joins
• Inner Join

Outer Join

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.

Materials covered in the W3C syllabus include:

• Left Join
• Right Join
• Full Outer Join
• Union

Order By

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.

Materials covered in the W3C syllabus include:

• Order By
• Order By in Union

Self Joins

The video here introduces students to joins where a table is summoned more than once. The concept of aliasing is also introduced.

Materials covered in the W3C syllabus include:

• Self Join
• Aliases

Natural Joins

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:

• Oracle
• W3Resource

Minus

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 W3C Syllabus doesn't cover this very well:

• W3C Type I Nested Query
• W3Schools Nested Query

Not Exists

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.

The W3C Syllabus doesn't cover this very well:

• W3C Type II Nested Query
• The problem with three valued logic

Simple Statistical Functions

The video introduces students to aggregation in SQL and simple statistical functions like sum, min, max, average, etc.

Materials covered by the W3C syllabus include:

• Min and Max
• Count, Average and Sum

Group By

The video introduces students to the group by clause in SQL aggregation.

Materials covered by the W3C syllabus include:

• Group By

Having

The video introduces students to the having clause in SQL aggregation- basically a where clause after a group by.

Materials covered by the W3C syllabus include:

• Having

Nested Statistical Queries

The video introduces students to the any and all operators used to nest statistical queries.

Materials covered by the W3C syllabus include:

• Any and All

Insert Queries

The video introduces students to use SQL to add records to a table.

Materials covered by the W3C syllabus include:

• Insert

Update Queries

The video introduces students to how to use SQL to change records in a table.

Materials covered by the W3C syllabus include:

• Update

Delete Queries

The video introduces students to use SQL to remove records from a table.

Materials covered by the W3C syllabus include:

• Delete

Creating Tables

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.

• Create tables in Oracle with dBeaver
• Create tables in mySQL with the web version of phpMyAdmin