Sub-Queries

One of the mainstays of multi-table querying is the subquery. Students quickly learn the ease by which data from one query can be used as a filter for another query. One of many difficulties they face is the mapping of data streams from one table to the next, and ensuring that columns involved are compatible.

The symbols used in subquery diagrams appears to work well with students. By having a subquery pass it's data to a parent query, the filter is implicit even if the path contains many such tables and is convoluted. The direction of the arrow is consistent with single table queries projecting their data to the outside world, and originally based on the symbols used in NIAM subtype diagrams to map inheritance.

Intersection Queries

Typically in an intersection query, we collect a set of values (a single column projection) and then use this set to filter another type compatible set of values - being part of both sets qualifies the data for projection to the outside world.

Q: List the album name and artist of the CD that features the song titled 'blister in the sun' Intersection suq-query

select AlbName, Artist
where Media = 'CD'
and SerNum in

(select SerNum
from Tracks
where song = 'Blister In The Sun')

Intersection queries can be used between two different (but related) tables, or between copies of the same table.

Students find that if they start from what they are given, it is often easier to work towards what they have to find out. In the example above, typically students would draw the subquery (tracks) before they draw the parent query (albums). Reading the diagram, on the other hand, occurs from the outside-in. In the example above, the subquery is written last.

This query can also be solved using a JOIN (natural inner join) as well, and intersection queries in general are interchangable with natural inner joins (the symbology used is similar).

Difference Queries

Using one query as a reject filter for another is the basic premise behind a difference query. Values that are elements of one set and not elements of another provide us with a useful tool.

To differentiate the in query from the not in query, a cross across the data stream is used. This symbolism is useful to sudents as the overall query is very similar in structure to an intersection query

Q: List the names of people who do not play bass.
Difference = not in

select Artist
from Performers
where Artist not in

(select Artist
from Performers
where Instrument = 'Bass')

This is the most common malformed solution to the request to list the names of people who do not play bass Incorrect 'not bass players' query

select Artist
from Performers
where Instrument <> 'Bass'

note: if an artist plays bass as well as other instruments then they will appear in this answer table, and they shouldn't.

Q: List the name of the LPs released through EMI some time between 1990 and 1995 that features a song with the word 'buffalo' in it's title, yet does not feature anyone playing the piano accordian. More complex sub-query

select AlbName
from Albums
where PDate between 1990 and 1995
and RecCo = 'EMI'
and Media = 'LP'
and SerNum in

(select SerNum
from Tracks
where Song like '%buffalo%')

and SerNum not in

(select SerNum
from Performers
where Instrument = 'Piano Accordian')

One of the most challenging things students must master is the extraction from the question what is actually required. Identifying what columns from which tables is involved in the solution represents the bulk of the cognitive load of a query question. Diagramming can ease that load by providing students with a concrete model on which to plan.

More Complex Sub-Queries

Q: List the album name and artist of albums that feature instruments similar to those instruments played by David Gilmore on the Pink Floyd album called Dark Side of the Moon.
complex intersection

select AlbName, Artist
from Albums
where AlbName <> 'Dark Side of the Moon'
and Artist <> 'Pink Floyd'
and SerNum in

(select SerNum
from Performers
where Instrument in

(select Instrument
from Performers
where Artist = 'David Gilmore'
and SerNum in

(select SerNum
from Albums
where AlbName = 'Dark Side of the moon'
and Artist = 'Pink Floyd')))

As a teacher, when I plan the hard questions in exercises and exams I find that diagramming the solution before writing the question often results in a much better expressed question. As we all know, writing a question does not always guarantee that the answer is actually possible, particularly if there are formation errors in the database in the first place.

When marking queries, I often mark from a diagram rather than the specific syntax of the query. Students are extremely talented at identifying ingenious alternate solutions to our most carefully phrased questions. I find a diagram invaluable in evaluating their correctness, with more general parts of the diagram being allocated marks as opposed to specific syntactic constructs. Indeed, I supply my students with an SQL syntax dictionary during exams as syntax is rarely the focus of the exam.