CREATE TABLE Customer ( CustomerID int, LastName varchar(255), FirstName varchar
ID: 3667715 • Letter: C
Question
CREATE TABLE Customer
(
CustomerID int,
LastName varchar(255),
FirstName varchar(255),
State varchar(255),
City varchar(255)
);
CREATE TABLE Jewlery
(
JewleryID int,
Type varchar(255),
Material varchar(255),
);
CREATE TABLE Sale
(
SaleID int,
CustomerID int,
JewleryID int,
TimneID int,
Income int,
Discount int,
);
CREATE TABLE Time
(
TimeID int,
Year int,
Month int,
Event varchar(255),
);
Problem 3:
Construct an SQL query that will find the quantity, the total income and discount for each city, type of jewelry, and the month.
Problem 3:
Construct an SQL query that will find the average quantity, income and discount with respect to each event, jewelry materials and month.
Problem 4:
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
-- There is no quantity columnm anywhere. Please explain the tables so I can figure out how to get quantity!
Problem 3:
Construct an SQL query that will find the quantity, the total income and discount for each city, type of jewelry, and the month.
SELECT Customer.city,
Jewlery.Type,
Time.Month,
SUM(Sale.Income),
SUM(Sale.discount),
FROM Sale
INNER JOIN Jewlery ON (Sale.JewleryID = Jewlery.JewleryID)
INNER JOIN Time ON (Time.TimeID = Sale.TimneID)
INNER JOIN Customer ON (Sale.CustomerID = Customer.CustomerID)
GROUP BY Customer.city, Jewlery.Type, Time.Month
Problem 3:
Construct an SQL query that will find the average quantity, income and discount with respect to each event, jewelry materials and month.
SELECT AVG(Sale.Income),
AVG(Sale.discount)
FROM Sale
INNER JOIN Jewlery ON (Sale.JewleryID = Jewlery.JewleryID)
INNER JOIN Time ON (Time.TimeID = Sale.TimneID)
INNER JOIN Customer ON (Sale.CustomerID = Customer.CustomerID)
GROUP BY Time.Event, Jewlery.Material, Time.month
Problem 4:
Construct an SQL query that will determine the five most sold (in quantities sold) jewelry pieces during the “Christmas town” event at Williamsburg, VA.
-- Needs explanation of quantity