Home Online Database ERD Activity 1 Activity 2 Activity 3 Activity 4 Activity 5 Activity 6 Activity 7

Activity 3: Complex queries: Selecting and combining data from multiple tables.

Investigate

To join linked records from two tables we need to use the primary key and matching foreign key in the linked table. Read through the following pages: Should you want to learn more about different types of JOIN you may also want to read through these pages:

Alternative Approach:

An atlernative approach to join tables in a SQL query is to use the WHERE clause. The syntax is very similar as to using a JOIN clause. See example below:



This method is an equivalent to using an INNER JOIN.

For the practice queries below we will be using this method (using a WHERE clause).

Practice

Use the Online Database to perform the following queries:

DescriptionSQL QueryResults
Select all the products supplied by Leka Trading
Select all the orders raised by a customer called "Island Trading"
Select all the products supplied by UK based suppliers
Show all the products supplied by suppliers based in North America (USA or Canada)
Show all the products ordered by a customer called "Island Trading"
Use a query to find a list of shipping companies which have already been used to ship orders to customers based in Argentina.
List the company name, firstname and lastname of contact for the customers who have had an order managed by the employee Robert King. Sort this list of customers by contact.
Explain why it would have been a better idea to use two fields, contactFirstname and contatLastname to store the name of the contact in the customers table.