 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
Single Table Queries
Simple Projections
Q:List
the serial number and name of all albums in the database  |
select Sernum, AlbName |
If 2 columns are adjacent to each other, I use a line across the group of columns I wish to display. To illustrate that they make it into the answer table, an arrow leading to the outside world is added.
Note: Students would typically omit all those columns not directly involved in this query (Artist, PDate, RecCo and Media in this query), but would be encouraged to at least lable the table rectangle.
Q:
List the names of all groups in the database without duplicates |
Select
distinct Artist |
Q:
List the name, group and release date of all recordings, arranged
alphabetically on artist with most recent releases first |
Select AlbName, Artist, PDate |
The double line filter acts as the DISTINCT keyword, and is a duplicate filter. Typically, it is assumed that queries result in distinct answer tables, but sometimes it is useful to explicitly state this.
The ORDER BY symbol merely rearranges the rows in the answer table. To indicate both of these types of filters, I place filters across the data streams that emerge from the query to form the answer table. This is inkeeping with the notion that they are largely cosmetic alterations of the answer table, and done just priot to display.
A..Z order is alphabetical, Z..A is reverse alphabetical, 9..1 is descending, 1..9 is ascending. Alternatively, students could write ASC or DESC beside corresponding data streams.
Simple Selections
Selections involve filters with answer tables being populated by a subset of the whole table (those tuples that satisfy the filter clauses). Typically we use a combination of a selection and a projection (column and row subsetting) when formain an answer table.
Q:
List the name and release date of all Pink Floyd albums in reverse
chronological order |
select
AlbName, PDate |
Q:
List an alphabetical listing the media they are owned on of all albums
that were released by either Edgar Froese or Tangarine Dream |
select
AlbName, Media from Albums where Artist = 'Froese, Edgar' or Artist = 'Tangarine Dream' |
OR conditions are placed on subsequent lines close to the related column name. In the example above, we can easily deduce that this must be an OR because the Artist column cannot contain both values simultaneously. AND conditions are drawn on the same line as each other.
This notation is simple for students to draw, and allows quite complex questions to be transcribed quickly, particularly if they omit those columns not involved in the query.
Deriving Information
SQL has many built-in funcitons, and provides operators that allow a full range of derivations on table data.
Q:
List the album name, group that released the album and how many years
ago it was released |
select
AlbName, Artist, sysyear-PDate from Albums where Media = 'CD' |
Q:
List groups and the number of recorded albums there are  |
select
Artist, count(AlbName) from Albums group by Artist |
Q:
List the name and release date of all cassettes where the release
date is recorded but the recording company isn't
|
select
albName, pDate |
Q:
List the names of all groups that there are more than two albums in
the collection of |
select
Artist from Albums group by Artist having count(*) > 2 |
Grouping functions agglomerate data (ie. compress many rows to become one). The double box is related to the double line filter of the distinct. We attach a having clause in much the same way that we attach a derivation box. The '#' symbol is used to indicate a tally or count, the 'backwards E' to stand for has elements (or not null) and the 'crossed-out backwards E' to represent has no elements. The key words null and not null work equally well here.
For the most part, diagramming simple queries is unnecessary. Most students can easily visualise data streams and derivations from single tables. Diagrams are, however, used in class to model these situations in order to learn the symbols used in later diagrams.
