Database SQL Questions (Intermediate)

This unit was written to provide homework, class assessments or additional tasks to complement your own database teaching. The theme of the five table relational database provided is Scottish golf club members and their competition results.

There are eight task sheets included as MS Word files:

• Task 1 – Analysis & Design – students must identify how problems identified from the beginner design can be solved through the introduction of more tables.
• Tasks 2,3 – Query design questions where the columns, tables, criteria and required functions are identified.
• Task 4 to 8 – SQL question sets covering: SELECT from multiple tables, alias, calculations, wildcards, aggregate functions (COUNT, MIN, MAX, AVG) and sub-queries. Two versions of task 8 are included showing solution as two separate queries (MS Access) and using a single query with a sub-query.

All marking instructions are provided.

The above tasks were initially written as homework/assessment style questions (I spent 20 years writing questions for the Scottish exam system) but I have since created database files should you wish your students to code the SQL.
The database files are provided in three formats for different environments:

• Access files (a student version and others with the completed SQL tasks 2 to 8 queries)
• CSV files that you can use to import the table data and build the database
• Text files that contain SQL CREATE and INSERT statements which you could use to create the database on a database server
• Excel files, Python programs and explanations that can be used to add more of your own data.