Display all cities of customers who have ordered from Nancy Davolio and live in the United States or any other North American countries (Canada or Mexico). There should be no duplicate cities in the output and all cities should be displayed in alphabetical order. Do not include any other fields in your output.
Answer
SELECT DISTINCT customers.City
FROM customers
JOIN orders ON orders.CustomerID = customers.CustomerID
...
Must purchase SQL Cram Kit to see the rest!
Explanation
The full video explanation is locked. To view it, you must purchase SQL Cram Kit, Lifetime Access or BUS 104 Cram Kit Bundle. Already purchased? Click here to log in.Step 1: Identify and investigate key terms
What key terms stick out to you in the question?
Display all cities of customers who have ordered from Nancy Davolio and live in the United States or any other North American countries (Canada or Mexico). There should be no duplicate cities in the output and all cities should be displayed in alphabetical order. Do not include any other fields in your output.
Let's break these down one-by-one. Below is the schema for reference.
"cities of customers"
The word "customers" directs us to the Customers table in the schema. Within this table, I notice the City field.
"ordered from Nancy Davolio"
The first thing to notice here is that the word "ordered" means that we'll be using the Orders table in some regard. But, how so?
Upon further inspection of the schema, I can see that the Orders table links the Customers and Employees table, which makes sense. Customers order from employees, and in this case we're looking for customers who ordered from the employee "Nancy Davolio".
To find "Nancy Davolio" orders, we'll utilize the LastName and FirstName fields in the Employees table.
"United States or any other North American countries (Canada or Mexico)"
These are names of countries, and in the context of the problem we're looking for "customers" who live in these countries. That means we'll be referencing the Country field of the Customers table.
It's important that we verify whether or not these countries are displayed as their full names, or abbreviations. Let's run the following query and scan the results for insight.
SELECT DISTINCT customers.Country
FROM customers;
As we can see, Mexico and Canada are displayed as expected...
...however, United States is displayed as "USA"!
This means that when we reference the United States in our query, we must code it as "USA".
Step 2: Construct base-query
What tables will I need for this question?
In Step 1, we found that we'll need the Customers, Orders, and Employees tables.
Are these tables connected to each other? Yes!
We'll just need to make sure to not attempt to join Customers directly to Employees, since they're linked through the bridge table of Orders.
Let's start with our FROM statement and place Customers in it.
SELECT *
FROM customers;
Next, let's JOIN the Orders table to Customers through the CustomerID foreign key in the Orders table and the CustomerID primary key in the Customers table.
SELECT *
FROM customers
JOIN orders ON orders.CustomerID = customers.CustomerID;
PAID CONTENT This is the end of the preview. To unlock the rest, get the SQL Cram Kit, Lifetime Access or BUS 104 Cram Kit Bundle.
Already purchased? Click here to log in.