db-sql2.pdf - Databases SQL2 Jianxin Li School of Computer...

This preview shows page 1 - 11 out of 48 pages.

The preview shows page 9 - 11 out of 48 pages.
Databases — SQL2Jianxin LiSchool of Computer Science & Software EngineeringUniversity of Western AustraliaJianxin Li (UWA)SQL21 / 42
This lectureThis lecture introduces the fundamental concept ofSELECTfrom multiple tablesIn order to select from multiple tables, the tables must bejoined— so thislecture is also about the various types ofJOIN.Jianxin Li (UWA)SQL22 / 42
Multiple table selectionsThe real power (and complexity) ofSELECTcomes from the ability torapidly extract data frommore than onetable.A multiple tableSELECTstatement can becomevery complex, and(unfortunately) the syntax can often seem somewhat counterintuitive — this islargely because the lack of general programming constructs in SQL.The key to mentally parsing SQL statements is to keep in mind thefundamental “row-processing loop”Construct rowsaccording to theFROMstatementFilter rowsaccording to theWHEREstatementExtract columnsaccording to theSELECTstatementJianxin Li (UWA)SQL23 / 42
A sample schemaWe use the following sample tables:Student– this stores student numbers and student namesCREATE TABLE Student(id CHAR(8), name VARCHAR(64));Unit– this stores unit codes and unit namesCREATE TABLE Unit(id CHAR(8), name VARCHAR(64));Enrolled– this stores enrolment informationCREATE TABLE Enrolled (sid CHAR(8), uid CHAR(8));Theintentionof this set up is that the tableEnrolledis meant to “connect”the other two tables — later we will see how toenforce thisrule in SQL.Jianxin Li (UWA)SQL24 / 42
An ER diagramidnameStudentidnameUnitEnrolledA diagram like this is called anentity-relationship(or ER) diagram — itshows theentitiesbeing modelled and therelationshipsbetween them.Jianxin Li (UWA)SQL25 / 42
Studentmysql> SELECT*FROM Student;+------+-------+| id| name|+------+-------+| 1| Amy|| 2| Bob|| 3| Chao|| 4| Emily || 5| Fan|+------+-------+5 rows in set (0.00 sec)There are a total of 5 students.Jianxin Li (UWA)SQL26 / 42
Unitmysql> SELECT*FROM Unit;+----------+-------------+| id| name|+----------+-------------+| CITS1401 | Databases|| CITS1402 | Programming || MATH1001 | Maths 1|| MATH1002 | Maths 2|+----------+-------------+4 rows in set (0.00 sec)There are a total of 4 units.Jianxin Li (UWA)SQL27 / 42
Enrolledmysql> SELECT*FROM Enrolled;+------+----------+| sid| uid|+------+----------+| 1| CITS1401 || 2| CITS1401 || 4| CITS1401 || 2| CITS1402 || 3| CITS1402 || 4| CITS1402 || 1| MATH1001 || 2| MATH1001 || 3| MATH1001 |+------+----------+9 rows in set (0.00 sec)There are a total of 9 enrolments.Jianxin Li (UWA)SQL28 / 42
A class listWith these tables, how can we find out who is takingCITS1402?Theenrolment informationis inEnrolledThestudent name informationis inStudentSomehow we have tocombinethese tables to pull out the information.Jianxin Li (UWA)SQL29 / 42
The basic joinmysql> SELECT*FROM Student, Enrolled;+------+-------+------+----------+| id| name| sid| uid|+------+-------+------+----------+| 1| Amy| 1| CITS1401 || 2| Bob| 1| CITS1401 || 3| Chao| 1| CITS1401 || 4| Emily | 1| CITS1401 || 5| Fan| 1| CITS1401 || 1| Amy| 2| CITS1401 || 2| Bob| 2| CITS1401 || 3| Chao| 2| CITS1401 || 4| Emily | 2| CITS1401 || 5| Fan| 2| CITS1401 |....| 4| Emily | 3| MATH1001 || 5| Fan| 3| MATH1001 |+------+-------+------+----------+45 rows in set (0.00 sec)Yikes, why are there 45 rows in this table?

Upload your study docs or become a

Course Hero member to access this document

Upload your study docs or become a

Course Hero member to access this document

End of preview. Want to read all 48 pages?

Upload your study docs or become a

Course Hero member to access this document

Term
One
Professor
N/A
Tags
Cartesian product, Join, Jianxin Li

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture