CS 317: Fun with SQL

"???"

Syllabus
Assignments

Changed: 6/6/2002

Here are some sample SQL queries that may be useful to you.

Using TOP to limit the number of results returned

A number of students have asked how we can get Access to return only the best 5 to 10 results. For example, most groups' recommendation queries potentially return hundreds of CDs. It is not actually a big problem in most database applications--it is easy to ignore the extra values--but, if you want to trim your results, you can use TOP to do so. Add "TOP 5" immediately after the SELECT and Access will return the first 5 tuples in the sorted results. One note: in case of ties, the query may return more than 5 tuples.

Here are queries that return the first three (alphabetically and chronologically) CDs by U2. Note that the second query returns four CDs because U2 released two CDs in 1983.

SELECT TOP 3 title, bandName, year
FROM CD
WHERE bandName='U2'
ORDER BY title;

SELECT TOP 3 title, bandName, year
FROM CD
WHERE bandName='U2'
ORDER BY year;