MySQL Quries given an ERD The questions are as follows 1.Which of the customers

Question

MySQL Quries given an ERD

The questions are as follows

1.Which of the customers who ordered bracelets have not ordered necklaces?

2. Which customers bought more gold charms than silver ones?

3. Which categories generated the most profit over a parameterized time period?

4. Are expensive charms more profitable than mid-priced or low-priced charms? Hint: Create categories based on the prices.

5. Create a parameterized query to enable Samantha to increase prices of a certain category of charms by a given percentage.

6. Create a new table with SQL and copy into it all of the customers who have not purchased items within the last three months.

7. Delete customers from the new table in the prior exercise who have spent more than $100 in the past year

The ERD

PhoneType CPhoneNum CPhoneID PhoneNum CHAR(10) PK INT PK PhoneTypelD INT PhoneType CHAR(4) FK CustomerlD INT Customer FK Phone lype INT PK CustomerlD INT CharmMaterial Material!D Material VARCHARO IName VARCHARO Charm PKI INT fName VARCHARO PK CharmlD INT CharmName VARCHARO FK MaterialID FK FK ShapelD FK CategorylD INT INT INT INT CharmSize SizelD Inches SaleHeader SizeID PK INT DECIMAL PKI SaleID INT FK Customerl INT Date Time DATETIME CharmShape ShapelD ShapeVARCHARO PK INT PurchaseLineltem INT INT INT INT SMALLMONEY PK PLineltemID Category FK PK CategorylD INT CharmID FK PurchaselD Quantity SaleLineltem CategoryVARCHARO PK SLineltemID FK FK INT INT INT INT UnitCostS CharmID SaleID Quantity UnitCost SMALLMONEY Vendor PK VendorID INT VNameVARCHARO PurchaseHeader VPhoneNum PK PurchaselD INT PK VPhonelD INT FK VendoriD INT PhoneNum Char(10) FK VendorlD FK PhoneTypelD Date Time DATETIME INT INT

Explanation / Answer

1) As not much information is given about the tables, assuming bracelets and necklace ae names of charms. 4 tables are joined and in where clause Charm condition is given.
SELECT c.CustomerID, c.lName, c.fName
FROM Customer c
INNER JOIN SaleHeader sh
ON c.CustomerID = sh.CustomerID
INNER JOIN SaleLineItem sl
ON sl.SaleID = sh.SaleID
INNER JOIN Charm ch
ON ch.CharmID = sl.CharmID
WHERE CharmName = 'bracelets' AND CharmName <> 'necklace';

2) 4 tables are joined and aggreagte function SUM is used to get the quantity by material.

SELECT c.CustomerID, c.lName, c.fName, cm.Material, SUM(sl.Quantity)
FROM Customer c
INNER JOIN SaleHeader sh
ON c.CustomerID = sh.CustomerID
INNER JOIN SaleLineItem sl
ON sl.SaleID = sh.SaleID
INNER JOIN Charm ch
ON ch.CharmID = sl.CharmID
INNER JOIN CharmMaterial cm
ON cm.MaterialID = ch.MaterialID
GROUP BY c.CustomerID, c.lName, cm.Material;

3)
SELECT ca.CategoryID, ca.Category, MAX(sl.UnitCost * sl.Quantity)
FROM Category ca
INNER JOIN Charm ch
ON ch.CategoryID = ca.CategoryID
INNER JOIN SalesLineItem sl
ON sl.CharmID = ch.CharmID
GROUP BY ca.CategoryID, ca.Category;

4)
SELECT ca.CategoryID, ca.Category, SUM(sl.UnitCost * sl.Quantity)
FROM Category ca
INNER JOIN Charm ch
ON ch.CategoryID = ca.CategoryID
INNER JOIN SalesLineItem sl
ON sl.CharmID = ch.CharmID
GROUP BY ca.CategoryID, ca.Category;

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

---

---

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