Single Table Queries

Simple Projections

Q:List the serial number and name of all albums in the database
a 2 column projection
select Sernum, AlbName
from Albums

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
from Albums

Q: List the name, group and release date of all recordings, arranged alphabetically on artist with most recent releases firstOrder By
Select AlbName, Artist, PDate
from Albums
order by Artist asc, PDate desc

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 orderFilter Conditons

select AlbName, PDate
from Albums
where Artist = 'Pink Floyd'
order by PDate desc

Q: List an alphabetical listing the media they are owned on of all albums that were released by either Edgar Froese or Tangarine Dream
Filter or other Filter
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
A Simple Derivation
select AlbName, Artist, sysyear-PDate
from Albums
where Media = 'CD'
Q: List groups and the number of recorded albums there are
Group by
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
Null and Not Null

select albName, pDate
from Albums
where media = 'CA'
and pDate null
and recCo not null

Q: List the names of all groups that there are more than two albums in the collection of
Group by Having
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.
©Copyright 2006.