CSCI A114 / INFO I111
Lecture Notes Six

First semester 2001-2002

Executing queries in relational databases.

Today we will look at

Here's the text of the lecture from Tuesday.

We will review some and work out the queries by hand.

We start by defining the following operations on sets.

1. SET UNION.

The union of two sets is the set of all elements that belong to either one or the other of the two sets. If an element belongs to both sets it also belongs to the union.

2. INTERSECTION OF SETS.

The intersection of two sets is the set of all elements that belong to both sets. For an element to appear in the intersection it must belong to both the first and the second set.

3. CARTESIAN PRODUCT.

The cartesian product is the set of all possible combinations of sets from the first and the second sets.

Note that, as the previous two (union and intersection), this has been defined as a binary operation. Union, intersection and cartesian products involving more than two sets (operands) are defined in the obvious way. For example here's such a definition for the cartesian product of n sets.

We'll work out some examples.

Then we return to designing a new database for a new application.

We will apply some of these definitions before too long.

Let's design a database for a gradebook.

A GRADEBOOK PROJECT

The initial scenario here is that as a teacher, you have grade-keeping responsibilities.

Here's a set of minimal requirements:

Based on the discussion of last time here's a possible design.

Fields in red and blue are keys.

Let's define the tables, and populate them with some data.

Then we will be looking at how the following queries are executed. 

SELECT * FROM STUDENT;
SELECT Name FROM STUDENT
SELECT Name, Gender, [Student ID] FROM STUDENT
SELECT * FROM TAKES WHERE Score > 95
SELECT COUNT(*) AS [Number of Students] FROM STUDENT;
SELECT COUNT(*) AS [Males] FROM STUDENT WHERE Gender = 'Male';
SELECT Gender, COUNT(*) AS [How Many] FROM STUDENT GROUP BY Gender;
SELECT * FROM TAKES WHERE Date = #9/9/01#;
SELECT   [Event ID], MIN(Score) AS Lowest, MAX(Score) AS Highest 
FROM     TAKES
GROUP BY [Event ID]; 
SELECT [Event ID], MIN(Score)              AS Lowest, 
                   MAX(Score)              AS Highest, 
                   MAX(Score) - MIN(Score) AS Range, 
                   SUM(Score)              AS Total, 
                   AVG(Score)              AS Average,
                   COUNT(Score)            AS Count
FROM TAKES
GROUP BY [Event ID];
SELECT * FROM EVENT, TAKES;
SELECT * FROM EVENT, TAKES WHERE EVENT.[Event ID] = TAKES.[Event ID];
SELECT * 
FROM   EVENT, TAKES, STUDENT;
SELECT      *
FROM        EVENT, TAKES, STUDENT
WHERE       STUDENT.[Student ID] = TAKES.[Student ID] 
       AND  TAKES.[Event ID] = EVENT.[Event ID]; 
SELECT      Name, Date, Score, Type
FROM        EVENT, TAKES, STUDENT
WHERE       STUDENT.[Student ID] = TAKES.[Student ID] 
       AND  TAKES.[Event ID] = EVENT.[Event ID];
SELECT AVG(Score) AS [Exam1 Average]
FROM TAKES
WHERE [Event ID] = 'EXAM1';
SELECT STUDENT.Name, TAKES.Score, Ex1Avg.[Exam Average]
FROM   STUDENT, TAKES, Ex1Avg
WHERE  STUDENT.[Student ID] = TAKES.[Student ID] 
   AND TAKES.[Event ID] = 'EXAM1' 
   AND Ex1Avg.[Exam1 Average] <= [Score]

Last updated: September 13, 2001 by Adrian German for A114/I111