In grade 12 you need to be familiar with composite SQL statements (embedded SQL statements). This means that two statements are joined together – the result set on one query is passed to the other.
There are six types of embedded queries you need to know.
- Those where the result set from the first query is compared to an aggregate function (MAX, MIN, AVG and SUM) in the second query.
- Finding orphaned records. This is where two tables have a relationship but some records do not have a corresponding record in the other table. This is where the first result set is NOT IN the second table.
- Using DISTINCT in the second query to avoid listing duplicates from the first result set.
- When you need to use DISTINCT and an aggregate function together e.g. to avoid countng duplicates
- Using an aggregate function eg COUNT on the returned result set from the first query.
- When using INSERT to create a new record that is based on an existing record in the table
In the grade 12 practical exam you also need to be able to use GROUP BY and HAVING.
Below is a handout covered advanced SQL queries with many different examples.