Final answer:
The question involves writing a SQL query to join four tables: Customers, Orders, OrderItems, and Products, and return specific columns, sorted by LastName, OrderDate, and ProductName.
Step-by-step explanation:
The question pertains to using SQL to join multiple tables in a database and retrieve specific data, followed by sorting the results. We will write a SELECT statement that joins the Customers, Orders, OrderItems, and Products tables from the Createguitarshop database. Here's a step-by-step explanation of the SQL statement:
- Assign aliases to tables for easier referencing.
- Use the SELECT statement to specify the columns of interest.
- Perform INNER JOINs between the tables based on foreign key relationships.
- Sort the final result set using ORDER BY with the specified columns.
Here is the SQL statement that fulfills the requirements:
SELECT
c.LastName AS LastName,
c.FirstName AS FirstName,
o.OrderDate AS OrderDate,
p.ProductName AS ProductName,
oi.ItemPrice AS ItemPrice,
oi.DiscountAmount AS DiscountAmount,
oi.Quantity AS Quantity
FROM
Customers AS c
INNER JOIN Orders AS o ON c.CustomerID = o.CustomerID
INNER JOIN OrderItems AS oi ON o.OrderID = oi.OrderID
INNER JOIN Products AS p ON oi.ProductID = p.ProductID
ORDER BY
c.LastName, o.OrderDate, p.ProductName;
This SQL query will return a list of orders with customer names, product names, prices, discounts, and quantities, sorted by LastName, OrderDate, and ProductName.