35 More on Matching 2 CS 200 Fall 2013 SQL Consider the two tables Students

35 more on matching 2 cs 200 fall 2013 sql consider

This preview shows page 18 - 22 out of 29 pages.

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)
Image of page 18
CS 200 Fall 2013SQLSuppose you said select * fromStudents asS, Register Here’s what that produces — 37S.IDNS.NameR.IDNR.CourseR.TermR.Mark10Aaron10CS20097018910Aaron10Biol45897017510Aaron11CS20097018110Aaron11Econ33597019411Sarah10CS20097018911Sarah10Biol45897017511Sarah11CS20097018111Sarah11Econ33597019412Jose10CS20097018912Jose10Biol45897017512Jose11CS20097018112Jose11Econ33597019413Marie10CS20097018913Marie10Biol45897017513Marie11CS20097018113Marie11Econ335970194Every possible combinationof a Student and a Register record!A model for how the where clause works (2)(Note the convention for definingthe “table aliases” S and R.)More likely what you want is something like select* fromStudents asS, Register aswhereS.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
Image of page 19
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
Image of page 20
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
Image of page 21
Image of page 22

You've reached the end of your free preview.

Want to read all 29 pages?

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture