DS 1300 - Introductjon to SQL Part 2 Multj-table Queries By - PowerPoint PPT Presentation

DS 1300 - Introductjon to SQL Part 2 – Multj-table Queries By Michael Hahsler based on slides for CS145 Introductjon to Databases (Stanford)

What you will learn about in this sectjon 1. Foreign key constraints 2. Joins: basics 3. Joins: SQL semantjcs 4. Actjvitjes: Multj-table queries 2

Foreign Key Constraints • Suppose we have the following schema: Students(sid : text, name: text , gpa: real ) Students(sid : text, name: text , gpa: real ) Enrolled(student_id: text , cid: text , grade: real ) Enrolled(student_id: text , cid: text , grade: real ) • And we want to impose the following constraint: Note: student_id Note: student_id alone is not a alone is not a – ‘Only existjng students may enroll in courses’ i.e. a key- what is? key- what is? student must appear in the Students table to enroll in a class Students Enrolled sid name gpa student_id cid grade 101 Bob 3.2 123 564 A 123 Mary 3.8 123 537 A+ We say that student_id is a foreign key that refers to Students We say that student_id is a foreign key that refers to Students

Declaring Foreign Keys Students(sid: text , name: text , gpa: real ) Students(sid: text , name: text , gpa: real ) Enrolled(student_id: text , cid: text , grade: text ) Enrolled(student_id: text , cid: text , grade: text ) CREATE TABLE Enrolled( CREATE TABLE Enrolled( student_id CHAR(20), student_id CHAR(20), cid cid CHAR(20), CHAR(20), grade CHAR(10), grade CHAR(10), PRIMARY KEY (student_id, cid), PRIMARY KEY (student_id, cid), FOREIGN KEY (student_id) REFERENCES Students FOREIGN KEY (student_id) REFERENCES Students ) ) Foreign key Primary key

Foreign Keys and Update Operatjons Students(sid : text, name: text , gpa: real ) Students(sid : text, name: text , gpa: real ) Enrolled(student_id: text , cid: text , grade: text ) Enrolled(student_id: text , cid: text , grade: text ) • What if we insert a tuple into Enrolled, but no corresponding student? – INSERT is rejected (foreign keys are SQLite : Enable foreign keys with SQLite : Enable foreign keys with constraints)! PRAGMA foreign_keys = ON; PRAGMA foreign_keys = ON; • What if we delete a student? DB Browser : check “Foreign Keys” DB Browser : check “Foreign Keys” in “Edit Pragma” in “Edit Pragma” 1. Disallow the delete 2. Remove all of the courses for that student 3. SQL allows a third via NULL (not yet covered)

Keys and Foreign Keys Company What is a What is a CName StockPrice Country foreign key foreign key GizmoWorks 25 USA vs. a key vs. a key Canon 65 Japan here? here? Hitachi 15 Japan Product PName Price Category Manufacturer Gizmo $19.99 Gadgets GizmoWorks Powergizmo $29.99 Gadgets GizmoWorks SingleTouch $149.99 Photography Canon 6 MultiTouch $203.99 Household Hitachi

Keys and Foreign Keys Company(CName, StockPrice, Country) Company(CName, StockPrice, Country) Product(PName, Price, Category, Manufacturer) Product(PName, Price, Category, Manufacturer) • This example uses natural keys . • Ofuen surrogate keys are used instead: Company(Cnumber, CName, StockPrice, Country) Company(Cnumber, CName, StockPrice, Country) Product(Pnumber, Pname, Price, Category, ManufNumber) Product(Pnumber, Pname, Price, Category, ManufNumber) • Why? • Why do we use SMU IDs and Social Security Numbers? 7

Joins Product(PName, Price, Category, Manufacturer) Product(PName, Price, Category, Manufacturer) Company(CName, StockPrice, Country) Company(CName, StockPrice, Country) Ex: Find all products under $200 manufactured in Japan; return their names and prices. This will need informatjon from both tables... 8

Joins Product(PName, Price, Category, Manufacturer) Product(PName, Price, Category, Manufacturer) Company(CName, StockPrice, Country) Company(CName, StockPrice, Country) Ex: Find all products under $200 manufactured in Japan; return their names and prices. A join between SELECT PName, Price SELECT PName, Price tables returns all FROM Product, Company FROM Product, Company unique combinatjons WHERE Manufacturer = CName WHERE Manufacturer = CName of their tuples which AND Country=‘Japan’ AND Country=‘Japan’ AND Price <= 200 meet some specifjed AND Price <= 200 join conditjon 9

Joins Product(PName, Price, Category, Manufacturer) Product(PName, Price, Category, Manufacturer) Company(CName, StockPrice, Country) Company(CName, StockPrice, Country) Several equivalent ways to write a basic join in SQL: SELECT PName, Price SELECT PName, Price SELECT PName, Price SELECT PName, Price FROM Product, Company FROM Product FROM Product, Company FROM Product WHERE JOIN Company ON Manufacturer = Cname WHERE JOIN Company ON Manufacturer = Cname WHERE Price <= 200 Manufacturer = CName Manufacturer = CName WHERE Price <= 200 AND Country=‘Japan’ AND Country=‘Japan’ AND Country=‘Japan’ AND Country=‘Japan’ AND Price <= 200 AND Price <= 200 10

Joins Product Company PName Price Category Manuf Cname Stock Country Gizmo $19 Gadgets GWorks GWorks 25 USA Powergizmo $29 Gadgets GWorks Canon 65 Japan SingleTouch $149 Photography Canon Hitachi 15 Japan MultiTouch $203 Household Hitachi SELECT PName, Price SELECT PName, Price FROM Product FROM Product PName Price JOIN Company ON Manufacturer = Cname JOIN Company ON Manufacturer = Cname WHERE Price <= 200 WHERE Price <= 200 SingleTouch $149.99 AND Country=‘Japan’ AND Country=‘Japan’ 11

An Example of SQL Semantjcs A SELECT R.A SELECT R.A R A FROM R, S FROM R, S 3 WHERE R.A = S.B WHERE R.A = S.B 1 3 3 S B C 2 3 3 4 3 5 12

An Example of SQL Semantjcs A SELECT R.A SELECT R.A R A FROM R, S FROM R, S 3 WHERE R.A = S.B WHERE R.A = S.B 1 3 3 R x S A B C Cross Apply S 1 2 3 Apply B C Product Selectjons / Projectjon 1 3 4 2 3 Conditjons 1 3 5 A B C 3 4 3 2 3 3 3 4 3 5 3 3 4 3 3 5 3 3 5 13

Tuple Variable Ambiguity in Multj-Table Person(name, address, Person(name, address, worksfor) worksfor) Company(name, address) Company(name, address) Which “address” Which “address” SELECT DISTINCT name, address SELECT DISTINCT name, address does this refer does this refer FROM Person, Company FROM Person, Company to ? to ? WHERE worksfor = name WHERE worksfor = name Which Which “name”s?? “name”s?? 15

Tuple Variable Ambiguity in Multj-Table Person(name, address, Person(name, address, worksfor) worksfor) Company(name, address) Company(name, address) SELECT DISTINCT Person. name, Person. address SELECT DISTINCT Person. name, Person. address FROM Person, Company FROM Person, Company Both WHERE Person. worksfor = Company. name WHERE Person. worksfor = Company. name equivalent ways to SELECT DISTINCT p. name, p. address SELECT DISTINCT p. name, p. address resolve FROM Person p, Company c FROM Person p, Company c variable WHERE p. worksfor = c. name WHERE p. worksfor = c. name ambiguity 16

A Note on Semantjcs • “semantjcs” is not equal to “executjon order” • The preceding slides show what a join means • Not actually how the DBMS executes it under the covers

Actjvitjes 1. Create the product/company database from the slide set. Add the following relatjon Purchase(id, product, buyer). with the appropriate foreign key constraints and add some data. 2. Find all countries that manufacture some product in the ‘Gadgets’ category (shows each country only once). 3. Find all products that are manufactured in the US sorted by price. 4. For a given buyer, in how many difgerent countries are the products she purchases manufactured? 20