cmpt354: databasesystemi · sql provides pattern matching support with the like operator and two...
TRANSCRIPT
![Page 1: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/1.jpg)
CMPT 354:Database System I
Lecture 3. SQL Basics
1
![Page 2: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/2.jpg)
Announcements!
• About Piazza• 97 enrolled (as of today)• Postsareanonymous to classmates
• You should have starteddoingA1• Please come to office hours if you need any help
2
![Page 3: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/3.jpg)
SQLMotivation• Darktimesin2000s• Arerelationaldatabasesdead?
• Now,asbefore:everyonesellsSQL• Pig,Hive,Impala• SparkSQL
• NoSQL• “NonSQL”• “Not-Only-SQL”• “Not-Yet-SQL”
3
![Page 4: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/4.jpg)
SQL:Introduction
• “S.Q.L.”or“sequel”• Supportedbyallmajorcommercialdatabasesystems• Standardized– manynewfeaturesovertime• Declarativelanguage
4
![Page 5: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/5.jpg)
5
SQLisa…
• DataDefinitionLanguage(DDL)• Definerelationalschema• Create/alter/deletetablesandtheirattributes
• DataManipulationLanguage(DML)• Insert/delete/modifytuplesintables• Queryoneormoretables– discussednext!
![Page 6: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/6.jpg)
Outline
• Single-table Queries• The SFW query• Usefuloperators:DISTINCT,ORDER BY,LIKE• Handlemissingvalues:NULLs
• Multiple-table Queries• Foreign key constraints• Joins: basics• Joins: SQL semantics
6
![Page 7: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/7.jpg)
• To write the query, ask yourself three questions:• Which table are you interested in?• Which rows are you interested in?• Which columns are you interested in?
The SFWQuery
7
SELECT <columns>FROM <table name>WHERE <conditions>
![Page 8: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/8.jpg)
Conditions
• Which rows are you interested in?• WHERE gpa > 3.5• WHERE school = ‘SFU’ AND gpa > 3.5• WHERE (school = ‘SFU’ OR school = ‘UBC’) AND gpa > 3.5• WHERE age *365 > 7500
8
SELECT <columns>FROM <table name>WHERE <conditions>
![Page 9: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/9.jpg)
Columns
• Which columns are you interested in?• SELECT *• SELECT name, age• SELECT name as studentName, age• SELECT name, age *365 as ageDay
9
SELECT <columns>FROM <table name>WHERE <conditions>
![Page 10: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/10.jpg)
AFewDetails
• SQLcommands arecaseinsensitive:• Same:SELECT,Select,select• Same:Student,student• Same:gpa, GPA
• Values arenot:• Different: 'SFU','sfu'
• SQLstringsareenclosedinsinglequotes• e.g.name='Mike’• Singlequotesinastringcanbespecifiedusinganinitialsinglequotecharacterasanescape• author='ShaqO''Neal'
• Stringscanbecomparedalphabetically withthecomparisonoperators• e.g.'fodder'<'foo'isTRUE
10
![Page 11: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/11.jpg)
11
DISTINCT:EliminatingDuplicates
SELECT DISTINCT SchoolFROM Students
Versus
SELECT SchoolFROM Students
School
SFU
UBC
UT
School
SFU
SFU
UBC
UT
UT
![Page 12: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/12.jpg)
12
ORDERBY:SortingtheResults
SELECT name, gpa, ageFROM StudentsWHERE school = 'SFU'ORDER BY gpa DESC, age ASC
• TheoutputofanSQLquerycanbeordered• Byanynumberofattributes,and• Ineitherascendingordescendingorder
• Thedefaultistouseascendingorder,thekeywordsASCandDESC,followingthecolumnname,setstheorder
![Page 13: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/13.jpg)
13
LIKE:SimpleStringPatternMatching
SELECT *FROM StudentsWHERE name LIKE ' Sm_t%'
SQLprovidespatternmatchingsupportwiththeLIKEoperatorandtwosymbols• The% symbolstandsforzeroormorearbitrarycharacters• The_ symbolstandsforexactlyonearbitrarycharacter• The% and _ characterscanbeescapedwith\
• E.g., nameLIKE ’Michael\_Jordan'
![Page 14: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/14.jpg)
Exercise - 1
• Which names will be returned?
14
SELECT *FROM StudentsWHERE name LIKE 'Sm_t%'
1. Smit2. SMIT3. Smart4. Smith5. Smythe6. Smut7. Smeath8. Smt
![Page 15: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/15.jpg)
Exercise - 1
• Which names will be returned?
15
SELECT *FROM StudentsWHERE name LIKE 'Sm_t%'
1. Smit2. SMIT3. Smart4. Smith5. Smythe6. Smut7. Smeath8. Smt
1, 4, 5, 6
![Page 16: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/16.jpg)
16
NULLSinSQL
• Wheneverwedon’thaveavalue,wecanputaNULL• Canmeanmanythings:
• Valuedoesnotexists• Valueexistsbutisunknown• Valuenotapplicable• Etc.
• NULLconstraints
CREATE TABLE Students (name CHAR(20) NOT NULL,age CHAR(20) NOT NULL,gpa FLOAT
)
![Page 17: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/17.jpg)
What will happen?
name age gpa
Mike 20 4.0
Joe 18 NULL
Alice 21 3.8
17
1. SELECT gpa*100 FROM students2. SELECT name FROM students WHERE gpa > 3.53. SELECT name FROM students WHERE age > 15 OR gpa > 3.5
![Page 18: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/18.jpg)
Two Important Rules
• Arithmeticoperations (+, -, *, /) onnullsreturnNULL• NULL *100
• NULL• NULL*0
• NULL
• ComparisonswithnullsevaluatetoUNKNOWN• NULL > 3.5
• UNKNOWN• NULL=NULL
• UNKNOWN
18
1. SELECT gpa*100 FROM students
2. SELECT gpa*0 FROM students
3. SELECT name FROM students WHERE gpa > 3.5
4. SELECT name FROM students WHERE gpa = NULL
![Page 19: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/19.jpg)
Combinations of true, false, unknown
• Truthvaluesforunknown results• true OR unknown =true,• falseOR unknown= unknown,• unknown OR unknown =unknown ,• true AND unknown =unknown,• false AND unknown =false,• unknown AND unknown =unknown
• TheresultofaWHERE clauseistreatedasfalse ifitevaluatestounknown• WHERE unknownà false
19
SELECT *FROM students WHEREage > 15 OR gpa > 3.5
SELECT *FROM students WHEREage > 15 AND gpa > 3.5
![Page 20: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/20.jpg)
What will happen?
name age gpa
Mike 20 4.0
Joe 18 NULL
Alice 21 3.8
20
1. SELECT gpa*100 FROM students2. SELECT name FROM students WHERE gpa > 3.53. SELECT name FROM students WHERE age > 15 OR gpa > 3.5
gpa
400
NULL
380
name
Mike
Alice
name
Mike
Joe
Alice
![Page 21: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/21.jpg)
21
Exercise - 2
• Will it return all students?
SELECT *FROM StudentsWHERE age < 25 OR age >= 25
![Page 22: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/22.jpg)
22
Exercise - 2
• Will it return all students?
SELECT *FROM StudentsWHERE age < 25 OR age >= 25
OR age is NULL
Therearespecialoperatorstotestfornullvalues• ISNULLtestsforthepresenceofnullsand• ISNOTNULL testsfortheabsenceofnulls
![Page 23: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/23.jpg)
Outline
• Single-table Queries• The SFW query• Other useful operators: DISTINCT, LIKE, ORDER BY• NULLs
• Multiple-table Queries• Foreign key constraints• Joins: basics• Joins: SQL semantics
23
![Page 24: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/24.jpg)
• Foreign-keyconstraint:• student_id referencessid
ForeignKeyconstraints
sid name gpa101 Bob 3.2123 Mary 3.8
student_id cid grade123 354 A123 454 A+156 354 A
Students Enrolled
24
![Page 25: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/25.jpg)
• Foreign-keyconstraint:• student_id referencessid
ForeignKeyconstraints
sid name gpa101 Bob 3.2123 Mary 3.8156 Mike 3.7
Students Enrolledstudent_id cid grade
123 354 A123 454 A+156 354 A
25
![Page 26: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/26.jpg)
DeclaringForeignKeys
CREATE TABLE Enrolled(student_id CHAR(20),cid CHAR(20),grade CHAR(10),PRIMARY KEY (student_id, cid),FOREIGN KEY (student_id) REFERENCES Students(sid)
)
student_id cid grade123 354 A123 454 A+156 354 A
26
![Page 27: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/27.jpg)
Insertoperations• WhatifweinsertatupleintoEnrolled,butnocorrespondingstudent?• INSERTisrejected
27
sid name gpa123 Mary 3.8156 Mike 3.7
Students Enrolledstudent_id cid grade
123 354 A123 454 A+156 354 A190 354 A
![Page 28: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/28.jpg)
Delete operations• Whatifwedeleteastudent,whohasenrolledcourses?• Disallowthedelete(ONDELETERESTRICT)
28
sid name gpa123 Mary 3.8
Students Enrolledstudent_id cid grade
123 354 A123 454 A+156 354 A
156 Mike 3.7
![Page 29: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/29.jpg)
ONDELETERESTRICT
CREATE TABLE Enrolled(student_id CHAR(20),cid CHAR(20),grade CHAR(10),PRIMARY KEY (student_id, cid),FOREIGN KEY (student_id) REFERENCES Students(sid)ON DELETE RESTRICT
)
student_id cid grade123 354 A123 454 A+156 354 A
29
![Page 30: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/30.jpg)
Delete operations• Whatifwedeleteastudent,whohasenrolledcourses?• Removeallofthecoursesforthatstudent(ONDELETE
CASCADE)
30
sid name gpa123 Mary 3.8
Students Enrolledstudent_id cid grade
123 354 A123 454 A+156 Mike 3.7156 354 A
![Page 31: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/31.jpg)
ONDELETECASCADE
CREATE TABLE Enrolled(student_id CHAR(20),cid CHAR(20),grade CHAR(10),PRIMARY KEY (student_id, cid),FOREIGN KEY (student_id) REFERENCES Students(sid)ON DELETE CASCADE
)
student_id cid grade123 354 A123 454 A+156 354 A
31
![Page 32: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/32.jpg)
Delete operations• Whatifwedeleteastudent,whohasenrolledcourses?• Set Foreign Key to NULL (ONDELETESETNULL)
32
sid name gpa123 Mary 3.8
Students Enrolledstudent_id cid grade
123 354 A123 454 A+156 354 A
156 Mike 3.7NULL 354 A
Interestingly, although it satisfies the foreign-key constraint,it violates the primary-key constraint, thus the deletionoperation is disallowed.
![Page 33: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/33.jpg)
ONDELETESETNULL
CREATE TABLE Enrolled(student_id CHAR(20),cid CHAR(20),grade CHAR(10),PRIMARY KEY (student_id, cid),FOREIGN KEY (student_id) REFERENCES Students(sid)ON DELETE SET NULL
)
student_id cid grade123 354 A123 454 A+156 354 A
33
![Page 34: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/34.jpg)
Outline
• Single-table Queries• The SFW query• Other useful operators: DISTINCT, LIKE, ORDER BY• NULLs
• Multiple-table Queries• Foreign key constraints• Joins: basics• Joins: SQL semantics• SetOperators
34
![Page 35: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/35.jpg)
Why do we havemultiple tables?
35
sid name gpa123 Mary 3.8
Students Enrolledstudent_id cid grade
123 354 A123 454 A+156 Mike 3.7156 354 A
EnrolledStudents
student_id name gpa cid grade123 Mary 3.8 354 A123 Mary 3.8 454 A+156 Mike 3.7 354 A
VS.
![Page 36: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/36.jpg)
• Multiple tables• Data updating is easier (e.g., update Mary’s gpa to 3.9)• Queryingeachindividualtableis faster (e.g., retrieveMary’s gpa)
• Asingle table• Data exchange is easier (e.g., share your data withothers)• Avoid the cost of joining multiple tables (e.g., retrievalall the courses that Mary has taken)
36
Storedataintomultiple tablesvs.singletable
![Page 37: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/37.jpg)
Joins
37
SELECT <columns>FROM <table name>WHERE <conditions>
SELECT <columns>FROM <table names>WHERE <conditions>
TheSFWqueryoverasingletable
TheSFWqueryovermultipletables
Whichrowsareyouinterestedin?
Whichrowsareyouinterestedin?
Howtojointhemultipletables?
![Page 38: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/38.jpg)
Joins:Example
38
SELECT name
FROM Students, Enrolled
WHERE sid = student_id AND
cid = 354 AND grad = ‘A+’Whichrowsareyouinterestedin?
Howtojointhetwotables?
sid name gpa123 Mary 3.8
Students Enrolledstudent_id cid grade
123 354 A+123 454 A+156 Mike 3.7156 354 A
FindallstudentwhohavegotanA+in354;returntheirnamesandgpas
![Page 39: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/39.jpg)
Otherwaystowritejoins
39
SELECT nameFROM Students, EnrolledWHERE sid = student_id AND
cid = 354 AND grad = ‘A+’
SELECT nameFROM StudentsJOIN Enrolled ON sid = student_id
AND cid = 354 AND grad = ‘A+’
SELECT nameFROM StudentsJOIN Enrolled ON sid = student_idWHERE cid = 354 AND grad = ‘A+’
![Page 40: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/40.jpg)
The Need fo TupleVariable
40
sid name gpa123 Mary 3.8
Students Enrolledstudent_id cid name grade123 354 DBI A+123 454 DBII A+156 354 DBI A
156 Mike 3.7
SELECT name
FROM Students, Enrolled
WHERE sid = student_id
Whichname?
![Page 41: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/41.jpg)
TupleVariable
41
sid name gpa123 Mary 3.8
Students Enrolledstudent_id cid name grade123 354 DBI A+123 454 DBII A+156 354 DBI A
156 Mike 3.7
SELECT Students.nameFROM Students, EnrolledWHERE sid = student_id
SELECT S.nameFROM Students S, EnrolledWHERE sid = student_id
![Page 42: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/42.jpg)
Outline
• Single-table Queries• The SFW query• Other useful operators: DISTINCT, LIKE, ORDER BY• NULLs
• Multiple-table Queries• Foreign key constraints• Joins: basics• Joins: SQL semantics• SetOperators
42
![Page 43: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/43.jpg)
Meaning(Semantics)ofJoinQueries
43
SELECT x1.a1, x1.a2, …, xn.akFROM R1 AS x1, R2 AS x2, …, Rn AS xnWHERE Conditions(x1,…, xn)
Answer={}for x1 in R1 do
for x2 in R2 do…..
for xn in Rn doif Conditions(x1,…,xn)
then Answer=AnswerÈ {(x1.a1,x1.a2,…,xn.ak)}return Answer
Thisiscallednestedloopsemantics sinceweareinterpretingwhatajoinmeansusinganestedloop
Note:this isamultisetunion
![Page 44: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/44.jpg)
Threesteps
1. Takecrossproduct• R1 × R2 × … × Rn
2. Applyconditions• Conditions(x1,…, xn)
3. Applyprojections• x1.a1, x1.a2, …, xn.ak
44
SELECT x1.a1, x1.a2, …, xn.akFROM R1 AS x1, R2 AS x2, …, Rn AS xnWHERE Conditions(x1,…, xn)
Note:ThisisNOThowtheDBMSexecutesthequery.
![Page 45: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/45.jpg)
Exercise
45
SELECT nameFROM Students, EnrolledWHERE sid = student_id AND grade >= ‘A’
sid name gpa123 Mary 3.8
Students Enrolledstudent_id cid grade
123 354 A+123 454 A+156 Mike 3.7156 354 A
nameMary
nameMaryMike
nameMaryMikeMary
nameMaryMaryMike
Whichone(s)arecorrect?
(A) (B) (C) (D)
![Page 46: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/46.jpg)
Outline
• Single-table Queries• The SFW query• Other useful operators: DISTINCT, LIKE, ORDER BY• NULLs
• Multiple-table Queries• Foreign key constraints• Joins: basics• Joins: SQL semantics• SetOperators
46
![Page 47: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/47.jpg)
SetOperations
• SQLsupportsunion,intersectionandsetdifferenceoperations• CalledUNION,INTERSECT,andEXCEPT• Theseoperationsmustbeperformedonunioncompatibletables
• AlthoughtheseoperationsaresupportedintheSQLstandard,implementationsmayvary• EXCEPTmaynotbeimplemented
• Whenitis,itissometimescalledMINUS
47
![Page 48: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/48.jpg)
OneofTwoCourses
• Findallstudentswhohavetakeneither354or454
48
sid name gpa123 Mary 3.8
Students Enrolledstudent_id cid grade
123 354 A+123 454 A+156 Mike 3.7156 354 A
SELECT nameFROM Students, EnrolledWHERE sid = student_id AND (cid = 354 OR cid = 454)
![Page 49: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/49.jpg)
OneofTwoCourses- UNION
• Findallstudentswhohavetakeneither354or454
49
sid name gpa123 Mary 3.8
Students Enrolledstudent_id cid grade
123 354 A+123 454 A+156 Mike 3.7156 354 A
SELECT nameFROM Students, EnrolledWHERE sid = student_id AND cid = 354UNIONSELECT nameFROM Students, EnrolledWHERE sid = student_id AND cid = 454
![Page 50: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/50.jpg)
BothCourses
• Findallstudentswhohavetakenboth354and454
50
sid name gpa123 Mary 3.8
Students Enrolledstudent_id cid grade
123 354 A+123 454 A+156 Mike 3.7156 354 A
SELECT nameFROM Students S, Enrolled EWHERE sid = student_id AND
(E.cid = 354 AND E.cid = 454)
![Page 51: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/51.jpg)
BothCoursesAgain
• Findallstudentswhohavetakenboth354and454
51
sid name gpa123 Mary 3.8
Students Enrolledstudent_id cid grade
123 354 A+123 454 A+156 Mike 3.7156 354 A
SELECT nameFROM Students S, Enrolled E1, Enrolled E2WHERE S.sid = E1.student_id AND S.sid = E2.student_id
AND (E1.cid = 354 AND E2.cid = 454)
![Page 52: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/52.jpg)
BothCourses- INTERSECT
• Findallstudentswhohavetakenboth354and454
52
sid name gpa123 Mary 3.8
Students Enrolledstudent_id cid grade
123 354 A+123 454 A+156 Mike 3.7156 354 A
SELECT nameFROM Students, Enrolled WHERE sid = student_id AND cid = 354INTERSECTSELECT nameFROM Students, EnrolledWHERE sid = student_id AND cid = 454
![Page 53: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/53.jpg)
OneCourseButNotTheOther
• Findallstudentswhohavetaken354butnot454
53
sid name gpa123 Mary 3.8
Students Enrolledstudent_id cid grade
123 354 A+123 454 A+156 Mike 3.7156 354 A
SELECT nameFROM Students, Enrolled WHERE sid = student_id AND cid = 354EXCEPTSELECT nameFROM Students, EnrolledWHERE sid = student_id AND cid = 454
![Page 54: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/54.jpg)
SetOperationsandDuplicates
• UnlikeotherSQLoperations,UNION,INTERSECT,andEXCEPT querieseliminateduplicatesbydefault• SQLallowsduplicatestoberetainedinthesethreeoperationsusingtheALL keyword(i.e.,multi-setoperations)
54
SELECT nameFROM Students, Enrolled WHERE sid = student_id AND cid = 354INTERSECT ALLSELECT nameFROM Students, EnrolledWHERE sid = student_id AND cid = 454
![Page 55: CMPT354: DatabaseSystemI · SQL provides pattern matching support with the LIKE operator and two symbols • The %symbol stands for zero or more arbitrary characters • The _symbol](https://reader033.vdocuments.us/reader033/viewer/2022042313/5edd3f57ad6a402d666845ea/html5/thumbnails/55.jpg)
Acknowledge
• Some lecture slides were copied from or inspired by thefollowing course materials• “W4111: Introduction to databases” by Eugene Wu atColumbia University• “CSE344: IntroductiontoDataManagement” by Dan Suciu atUniversityof Washington• “CMPT354: Database System I” by JohnEdgar at Simon FraserUniversity• “CS186: Introduction to Database Systems” by Joe Hellersteinat UC Berkeley• “CS145: IntroductiontoDatabases” by Peter Bailis at Stanford• “CS348:IntroductiontoDatabaseManagement” by GrantWeddell at University of Waterloo
55