35 more on matching 2 cs 200 fall 2013 sql consider

35
More on Matching (2)
CS 200 Fall 2013 SQL
Consider the two tables
Students Register
IDN IDN
Name Course
Term
Mark
with Students data and Register data
(What are the primary keys for these tables? The foreign keys?)
36
IDN Name
10 Aaron
11 Sarah
12 Jose
13 Marie
IDN Course Term Mark
10 CS200 9701 89
10 Biol458 9701 75
11 CS200 9701 81
11 Econ335 9701 94
A model for how the where clause works (1)

CS 200 Fall 2013 SQL
Suppose you said
select * from Students as S , Register
Here’s what that produces —
37
S.IDN S.Name R.IDN R.Course R.Term R.Mark
10 Aaron 10 CS200 9701 89
10 Aaron 10 Biol458 9701 75
10 Aaron 11 CS200 9701 81
10 Aaron 11 Econ335 9701 94
11 Sarah 10 CS200 9701 89
11 Sarah 10 Biol458 9701 75
11 Sarah 11 CS200 9701 81
11 Sarah 11 Econ335 9701 94
12 Jose 10 CS200 9701 89
12 Jose 10 Biol458 9701 75
12 Jose 11 CS200 9701 81
12 Jose 11 Econ335 9701 94
13 Marie 10 CS200 9701 89
13 Marie 10 Biol458 9701 75
13 Marie 11 CS200 9701 81
13 Marie 11 Econ335 9701 94
Every possible combination
of a Student and a Register record!
A model for how the where clause works (2)
(Note the convention for defining
the “ table aliases ” S and R.)
More likely what you want is something like
select * from Students as S, Register as
where S .IDN =
A model for how the where clause works (3)
as R
CS 200 Fall 2013 SQL
R
38
S.Name R.IDN R.Course R.Term R.Mark
10 Aaron 10 CS200 9701 89
10 Aaron 10 Biol458 9701 75
11 Sarah 11 CS200 9701 81
11 Sarah 11 Econ335 9701 94

CS 200 Fall 2013 SQL
Or more elegantly,
select S.IDN, Name, Course from Students as S, Register as R
where S.IDN = R.IDN
which produces
39
S.IDN S.Name R.Course
10 Aaron CS200
10 Aaron Biol458
11 Sarah CS200
11 Sarah Econ335
How the where clause works (4)
CS 200 Fall 2013 SQL
select S.IDN, Name, Course from Students as S, Register as R
where (Name = 'Aaron') and (S.IDN = R.IDN)
which produces
40
S.IDN S.Name R.Course
10 Aaron CS200
10 Aaron Biol458
Just the courses for Aaron

CS 200 Fall 2013 SQL
select Course, R.IDN, Name
from Students as S, Register as R
where (Course = 'CS200') and (R.IDN = S.IDN)
which produces
41
R.Course R.IDN S.Name
CS200 10 Aaron
CS200 11 Sarah
List the students taking CS 200
CS 200 Fall 2013 SQL
Suppose we add a third table
Students Register Courses
IDN IDN Name
Name Course Room
Term Time
Mark Description
NB: Students.Name and Courses.Name hold di erent things
Here’s some data for Courses
What’s the primary key for Courses?
42
Name Room Time Description
CS100 DC1351 M 1230 Introduction to Computer Usage
CS200 MC4060 M 1230 Advanced Concepts for Computer Usage
Biol458 B2 350 MWF 830 Behavioural Ecology
Econ335 ML212 TR 1000 Economic Development