Worked SQL Examples


Mark Mahoney

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 (download it here). 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.
The playbacks have several pictures of the ER Diagram and Schema for this 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" on this site.

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

1.1 Q1
1.2 Q2
1.3 Q3
1.4 Q4
1.5 Q5
2.1 Q6
2.2 Q7
2.3 Q8
2.4 Q9
2.5 Q10
3.1 Q11
3.2 Q12
3.3 Q13
3.4 Q14
3.5 Q15
4.1 Q16
4.2 Q17
4.3 Q18
4.4 Q19
4.5 Q20
5.1 Q21
5.2 Q22
5.3 Q23
5.4 Q24
5.5 Q25
6.1 Q26
6.2 Q27
6.3 Q28
6.4 Q29
6.5 Q30