Question

Modify the single table query, include join statement(s) to complete each question. Write each query twice: once with NATURAL JOIN and second JOIN... USING.

1. List the orderdate, firstname, lastname of customers that have placed orders. Only include those customers that live in Atlanta.
SELECT firstname, city from customers where city = 'ATLANTA';

Answer 1

To list the order date, first name, and last name of customers who have placed orders and live in Atlanta, you can use either a natural join or a join using statement. Both approaches will give the same result, but using join using provides more control over the columns to join on and is considered safer.

Step-by-step explanation:

In this question, we are asked to modify a single table query by including join statements. The goal is to list the order date, first name, and last name of customers who have placed orders and live in Atlanta. We can achieve this by using a join statement to connect the customer and order tables based on the customer's ID or using a natural join. Both approaches can provide the desired result, but there are some differences between them.

With natural join, the common column(s) between the two tables are automatically matched, in this case, 'customerID'. The query can be written as:

SELECT orderdate, firstname, lastname
FROM orders NATURAL JOIN customers
WHERE city = 'Atlanta';

On the other hand, JOIN... USING explicitly specifies the column(s) to join on using the 'USING' keyword. The query can be written as:

SELECT orderdate, firstname, lastname
FROM orders JOIN customers USING (customerId)
WHERE city = 'Atlanta';

Both approaches will give the same result in this case, but using JOIN... USING provides more control over the columns to join on and is generally considered safer as it avoids ambiguity when there are multiple columns with the same name in the two tables.