5
DISTINCT and ALL Sometimes you end up with duplicate entries Using DISTINCT removes duplicates Using ALL retains them - this is the default SELECT ALL Last FROM Student SELECT DISTINCT Last FROM Student Last Smith Jones Brown Jones Brown Last Smith Jones Brown

6
WHERE Clauses Usually you don’t want all the rows A WHERE clause restricts the rows that are returned It takes the form of a condition - only those rows that satisfy the condition are returned Example conditions: Mark < 40 First = ‘John’ First <> ‘John’ First = Last (First = ‘John’) AND (Last = ‘Smith’) (Mark 70)

8
WHERE Example Given the table Write an SQL query to find a list of the ID numbers and marks in IAI of students who have passed (scored 40 or higher) IAI Grade IDCodeMark S103DBS72 S103IAI58 S104PR168 S104IAI65 S106PR243 S107PR176 S107PR260 S107IAI35 IDMark S10358 S10465

9
One Solution SELECT ID, Mark FROM Grade WHERE (Code = ‘IAI’) AND (Mark >= 40) We only want the ID and Mark, not the Code We’re only interested in IAI Single quotes around the string We’re looking for entries with pass marks

10
SELECT from Multiple Tables Often you need to combine information from two or more tables You can get the effect of a product by using SELECT * FROM Table1, Table2... If the tables have columns with the same name ambiguity results You resolve this by referencing columns with the table name TableName.Column

12
And then with the second… and so on Are matched with the first entry from the Student table... All of the entries from the Grade table SELECT from Multiple Tables IDFirstLastIDCodeMark S103JohnSmithS103DBS72 S103JohnSmithS103IAI58 S103JohnSmithS104PR168 S103JohnSmithS104IAI65 S103JohnSmithS106PR243 S103JohnSmithS107PR176 S103JohnSmithS107PR260 S103JohnSmithS107IAI35 S104MaryJonesS103DBS72 S104Mary Jones S103IAI58 S104Mary Jones S104PR168 S104Mary Jones S104IAI65 S104Mary Jones S106PR243 SELECT... FROM Student, Grade WHERE...

16
SELECT from Multiple Tables When selecting from multiple tables you almost always use a WHERE clause to find entries with common values SELECT * FROM Student, Grade, Course WHERE Student.ID = Grade.ID AND Course.Code = Grade.Code

18
JOINs JOINs can be used to combine tables There are many types of JOIN CROSS JOIN INNER JOIN NATURAL JOIN OUTER JOIN OUTER JOIN s are linked with NULL s - more later A CROSS JOIN B returns all pairs of rows from A and B A NATURAL JOIN B returns pairs of rows with common values for identically named columns and without duplicating columns A INNER JOIN B returns pairs of rows satisfying a condition

21
CROSS and NATURAL JOIN SELECT * FROM A CROSS JOIN B is the same as SELECT * FROM A, B SELECT * FROM A NATURAL JOIN B is the same as SELECT A.col1,… A.coln, [and all other columns apart from B.col1,…B.coln] FROM A, B WHERE A.col1 = B.col1 AND A.col2 = B.col2...AND A.coln = B.col.n (this assumes that col1… coln in A and B have common names)

22
INNER JOIN INNER JOIN s specify a condition which the pairs of rows satisfy SELECT * FROM A INNER JOIN B ON Can also use SELECT * FROM A INNER JOIN B USING (col1, col2,…) Chooses rows where the given columns are equal

25
INNER JOIN SELECT * FROM A INNER JOIN B ON is the same as SELECT * FROM A, B WHERE SELECT * FROM A INNER JOIN B USING(col1, col2,...) is the same as SELECT * FROM A, B WHERE A.col1 = B.col1 AND A.col2 = B.col2 AND...

26
JOINs vs WHERE Clauses JOINs (so far) are not needed You can have the same effect by selecting from multiple tables with an appropriate WHERE clause So should you use JOINs or not? Yes, because They often lead to concise queries NATURAL JOINs are very common No, because Support for JOINs varies a fair bit among SQL dialects

27
Writing Queries When writing queries There are often many ways to write the query You should worry about being correct, clear, and concise in that order Don’t worry about being clever or efficient Most DBMSs have query optimisers These take a user’s query and figure out how to efficiently execute it A simple query is easier to optimise We’ll look at some ways to improve efficiency later

29
This Lecture in Exams Find a list of all the CD titles. (1 mark) Find a list of the titles of tracks that are more than 300 seconds long. (2 marks) Find a list of the names of those artists who have a track on the CD with the title “Compilation”. (4 marks)