Database Design and Development

Database Design and Development SQL – SELECT (MULTIPLE)

Learning Intention • I will learn how to use SQL to SELECT data from multiple tables.

SELECT (multiple tables) • So far you have only selected fields from one table in an SQL statement. • Need to be able to select fields from two tables.

SELECT (multiple tables) • To select fields from two tables you must include both tables in the FROM clause. e.g. FROM Resort, Hotel

SELECT (multiple tables) • Also need to modify the WHERE clause to link the two tables using the Primary Key and it’s corresponding Foreign Key • This is called an EQUI-JOIN

SELECT (multiple tables) SELECT field1, field2, field3, etc FROM table1, table2 WHERE table1.fieldPK = table2.fieldFK ORDER BY field2 ASC, field4 DESC;

e.g. SELECT hotelName, town FROM Resort, Hotel WHERE Resort.resortID = Hotel.resortID;

SELECT (multiple tables) • If you also need to find specific records then you add an AND after the WHERE. e.g. SELECT hotelName, town FROM Resort, Hotel WHERE Resort.resortID = Hotel.resortID AND starRating >= 4 ORDER BY hotelName ASC;

SELECT (multiple tables) • If you need to find records where you are looking for results from two specific values in the same column then use OR and brackets. • If you need to view the value of the field which is used to join the tables then you need to put the table name in the SELECT too.

SELECT (multiple tables) • e.g. SELECT Resort.resortID, town, hotelName FROM Resort, Hotel WHERE Resort.resortID = Hotel.resortID AND (town = "Ayr" OR town = " Glasgow");

Pupil Task Complete the following: SQL Booklet Tasks 4, 5 and Extension Task 1

Success Criteria • I can create SQL SELECT statements to select data from multiple tables.

Database Design and Development