Worked SQL Examples

This is a collection of 30 'worked examples' where I show the viewer how I go about writing SQL queries. SQL queries are rarely written left-to-right, top-to-bottom. Instead, a database developer typically builds their queries up from small and simple to more complex. These playbacks show how my queries grow as I get closer and closer to a correct solution. They also emphasize how to use an ER Diagram and schema when writing queries.

The database I am using describes courses taken by students in a university. It is a SQLite database called studentGrades.sqlite. Here are the entities and relationships:

Entites:

• Students
• Sections
• Courses
• Professors
• Departments

Relationships:

• Any one student takes zero or more sections (every student receives a grade).
• Any one section has zero or more students taking it.
• Any one section is an instance of one course.
• Any one course has zero or more instances of sections.
• Any one sections is taught by a single professor.
• Any one professor teaches zero or more sections.
• Any one professor belongs to zero or more departments.
• Any one department has zero or more professors.
• Any one department has zero or one professor who is the chairperson of the department.
• Any one professor chairs zero or one department.
• Any one course is offered by one department.
• Any one department offers zero or more courses.

Here is an ER Diagram and schema describing the database:

If you are brand new to SQL you might want to start with another one of my 'books' of code playbacks:
Database Design and SQL for Beginners

If you would like to learn how to write programs that use a database you can read:
Programming with SQLite.

If you'd like to stay connected and get updates when I add new playbacks you can follow me on twitter:@markm208.