This Lecture SQL SELECT WHERE Clauses SQL SELECT SELECT from - PDF document

This Lecture • SQL SELECT • WHERE Clauses SQL SELECT • SELECT from multiple tables • JOINs • Further reading Database Systems • The Manga Guide to Databases, Chapter 4 Michael Pound • Database Systems, Chapter 6 SQL SELECT Overview Example Tables Student Grade SELECT ID First Last ID Code Mark [DISTINCT | ALL] <column-list> S103 John Smith S103 DBS 72 S104 Mary Jones S103 IAI 58 FROM <table-names> S105 Jane Brown S104 PR1 68 [WHERE <condition>] S106 Mark Jones S104 IAI 65 [ORDER BY <column-list>] S107 John Brown S106 PR2 43 S107 PR1 76 Course [GROUP BY <column-list>] S107 PR2 60 Code Title [HAVING <condition>] S107 IAI 35 DBS Database Systems PR1 Programming 1 ([] optional , | or ) PR2 Programming 2 IAI Introduction to AI DISTINCT and ALL WHERE Clauses Last • Sometimes you end up SELECT ALL Last • In most cases returning • Example conditions: Smith From Student with duplicate entries all the rows is not • Mark < 40 Jones • Using DISTINCT necessary Brown • First = ‘John’ removes duplicates Jones • A WHERE clause restricts • First <> ‘John’ • Using ALL retains Brown rows that are returned • First = Last duplicates • It takes the form of a • (First = ‘John’) SELECT DISTINCT Last • ALL is used as a default condition – only rows FROM Student AND (Last = that satisfy the condition if neither is supplied ‘Smith’) Last are returned • These will work over Smith • (Mark < 40) OR multiple columns Jones (Mark > 70) Brown 1

WHERE Examples WHERE Examples SELECT * FROM Grade SELECT DISTINCT ID • Given the table: • Write an SQL query to WHERE Mark >= 60 FROM Grade find a list of the ID Grade WHERE Mark >= 60 numbers and Marks for ID Code Mark students who have S103 DBS 72 ID Code Mark passed (scored 40% or S103 IAI 58 ID S103 DBS 72 S104 PR1 68 more) in IAI S104 PR1 68 S103 S104 IAI 65 S104 S104 IAI 65 S106 PR2 43 ID Mark S107 PR1 76 S107 S107 PR1 76 S103 58 S107 PR2 60 S107 PR2 60 S104 65 S107 IAI 35 Solution SELECT from Multiple Tables • Often you need to • If the tables have combine information columns with the same from two or more name, ambiguity will SELECT ID, Mark FROM Grade tables result WHERE (Code = ‘IAI’) • You can produce the • This can be resolved by effect of a Cartesian referencing columns AND (Mark >= 40) product using: with the table name: SELECT * FROM Table1, TableName.ColumnName Table2 SELECT from Multiple Tables SELECT from Multiple Tables SELECT ... FROM Student, Grade WHERE ... SELECT Student ID First Last ID Code Mark First, Last, Mark ID First Last S103 John Smith S103 DBS 72 S103 John Smith S103 John Smith S103 IAI 58 FROM Grade S104 Mary Jones S103 John Smith S104 PR1 68 Student, Grade ID Code Mark S105 Jane Brown S103 John Smith S104 IAI 65 S103 DBS 72 WHERE S106 Mark Jones S103 John Smith S106 PR2 43 S103 IAI 58 (Student.ID = S107 John Brown S103 John Smith S107 PR1 76 S104 PR1 68 (Grade.ID) AND S103 John Smith S107 PR2 60 S104 IAI 65 (Mark >= 40) S103 John Smith S107 IAI 35 S106 PR2 43 S104 Mary Jones S103 DBS 72 S107 PR1 76 S104 Mary Jones S103 IAI 58 S107 PR2 60 S104 Mary Jones S104 PR1 68 S107 IAI 35 S104 Mary Jones S104 IAI 65 2

SELECT from Multiple Tables SELECT from Multiple Tables SELECT ... FROM Student, Grade SELECT ... FROM Student, Grade WHERE (Student.ID = Grade.ID) AND ... WHERE (Student.ID = Grade.ID)AND(Mark >= 40) ID First Last ID Code Mark ID First Last ID Code Mark S103 John Smith S103 DBS 72 S103 John Smith S103 DBS 72 S103 John Smith S103 IAI 58 S103 John Smith S103 IAI 58 S104 Mary Jones S104 PR1 68 S104 Mary Jones S104 PR1 68 S104 Mary Jones S104 IAI 65 S104 Mary Jones S104 IAI 65 S106 Mark Jones S106 PR2 43 S106 Mark Jones S106 PR2 43 S107 John Brown S107 PR1 76 S107 John Brown S107 PR1 76 S107 John Brown S107 PR2 60 S107 John Brown S107 PR2 60 S107 John Brown S107 IAI 35 SELECT from Multiple Tables SELECT from Multiple Tables SELECT First, Last, Mark FROM Student, Grade • When selecting from SELECT * WHERE (Student.ID = Grade.ID)AND(Mark >= 40) From multiple tables, it is First Last Mark Student, Grade, John Smith 72 almost always best to Course John Smith 58 use a WHERE clause to Mary Jones 68 WHERE find common values Mary Jones 65 Student.ID = Mark Jones 43 Grade.ID John Brown 76 AND John Brown 60 Course.Code = Grade.Code SELECT from Multiple Tables Joins • JOINs can be used to A CROSS JOIN B combine tables in a • Returns all pairs of rows Student Grade Course SELECT query from A and B • There are numerous types ID First Last ID Code Mark Code Title of JOIN S103 John Smith S103 DBS 72 DBS Database Systems A INNER JOIN B • CROSS JOIN S103 John Smith S103 IAI 58 IAI Introduction to AI • Returns pairs of rows • INNER JOIN S104 Mary Jones S104 PR1 68 PR1 Programming 1 satisfying a condition • NATURAL JOIN S104 Mary Jones S104 IAI 65 IAI Introduction to AI • OUTER JOIN S106 Mark Jones S106 PR2 43 PR2 Programming 2 A NATURAL JOIN B • OUTER JOIN will be S107 John Brown S107 PR1 76 PR1 Programming 1 discussed later – they are • Returns pairs of rows with S107 John Brown S107 PR2 60 PR2 Programming 2 linked with NULLs common values in identically named columns Student.ID = Grade.ID Course.Code = Grade.Code 3

CROSS JOIN CROSS JOIN Student SELECT * FROM SELECT * FROM • Usually best to use a ID Name Student CROSS JOIN A CROSS JOIN B WHERE clause to avoid Enrolment 123 John huge result sets ID Name ID Code 124 Mary • Without a WHERE 123 John 123 DBS • Is the same as 125 Mark 124 Mary 123 DBS clause, the number of 126 Jane 125 Mark 123 DBS rows produced will be Enrolment SELECT * FROM A, B equal to the number of 126 Jane 123 DBS ID Code rows in A multiplied by 123 John 124 PRG 123 DBS the number of rows in B . 124 Mary 124 PRG 124 PRG 125 Mark 124 PRG 124 DBS 126 Jane 124 PRG 126 PRG 123 John 124 DBS 124 Mary 124 DBS INNER JOIN INNER JOIN Buyer • INNER JOIN specifies • Can also use a USING SELECT * FROM Name Budget Buyer INNER JOIN a condition that pairs of clause that will output Smith 100,000 Property ON rows must satisfy rows with equal values Jones 150,000 Price <= Budget in the specified columns Green 80,000 SELECT * Name Budget Address Price SELECT * FROM A INNER JOIN B FROM A INNER JOIN B Smith 100,000 15 High Street 85,000 Property ON <condition> USING (col1, col2) Jones 150,000 15 High Street 85,000 Address Price Jones 150,000 12 Queen Street 125,000 15 High Street 85,000 • col1 and col2 must 12 Queen Street 125,000 appear in both A and B 87 Oak Lane 175,000 INNER JOIN NATURAL JOIN Student SELECT * FROM • A NATURAL JOIN is SELECT * FROM ID Name Student INNER JOIN A NATURAL JOIN B effectively a special case Enrolment USING (ID) 123 John of an INNER JOIN • Is the same as 124 Mary where the USING clause ID Name Code 125 Mark has specified all 123 John DBS SELECT A.Col1, A.Col2, ... 126 Jane identically named 124 Mary PRG , A.Coln, [and columns columns from B with names 124 Mary DBS Enrolment distinct from those in 126 Jane PRG ID Code A] 123 DBS FROM A, B • A single ID row will be output WHERE A.Col1 = B.Col1 124 PRG representing the equal values AND ... 124 DBS from both Student.ID and AND A.Coln = B.Coln 126 PRG Enrolment.ID 4