CREATE TABLE Customer ( CustomerID int, LastName varchar(255), FirstName varchar
ID: 3667939 • Letter: C
Question
CREATE TABLE Customer
(
CustomerID int,
LastName varchar(255),
FirstName varchar(255),
State varchar(255),
City varchar(255),
Quantity int
);
CREATE TABLE Jewlery
(
JewleryID int,
Type varchar(255),
Material varchar(255),
);
CREATE TABLE Sale
(
SaleID int,
CustomerID int,
JewleryID int,
TimneID int,
Income int,
Quantity int,
Discount int,
);
CREATE TABLE Time
(
TimeID int,
Year int,
Month int,
Event varchar(255),
);
Construct an SQL query that will determine the five most sold (in quantities sold) jewelry pieces during the “Christmas town” event at Williamsburg, VA.
Explanation / Answer
SELECT
Jewlery.Type
FROM Sale
INNER JOIN Jewlery ON (Jewlery.JewleryID = Sale.JewleryID)
INNER JOIN Time ON (Time.TimeID = Sale.TimneID)
INNER JOIN Customer ON (Customer.CustomerID = Sale.CustomerID)
WHERE Customer.City = "Williamsburg"
AND Customer.State = "VA"
ORDER BY Sale.Quantity DESC
LIMIT 5;