CREATE TABLE Customer ( CustomerID int, LastName varchar(255), FirstName varchar

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

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.