Petand Garden Store Sample SQL Query

Download Solution: Click to Download Solution
Solution File Name: PetandGarden.docx
Unzip Password: prestobear.com


Query 1. List all items that sold for less than cost in the month of December. 
Question1
ItemID	Description	Category	Cost	ListPrice	SalePrice	OrderDate
8776	Wrench	TOOL	$4.75	$5.50	$5.00	12/3/2015
9765	Archway	FLO	$216.00	$275.00	$270.00	12/30/2014
1224	Dog Brush	DOG	$2.75	$3.75	$3.50	12/30/2014


 
Query 2. List the inventory category and total quantity (by each category) ordered in the year 2015 (in descending order of total quantity). 
Question2
Category	SumOfQuantity
CAT	3
DOG	3
GAR	2
TOOL	2

 

Query 3. List the Spring products ordered by florists that had a list price over $200. 
Question3
Category	CategoryDescription	ItemDescription	ListPrice	Season	WhoOrdered
FLO	Florist Supplies	Archway	$275.00	SPRING	FLO


 
Query 4. List the customer ID and total orders for each customer, in descending order of total orders by total number of orders.  
Question41
CID	SumOfQuantity
8	13
3	12
1	8
6	7
4	3
7	2
5	2
2	2


 
Query 5. List the customer ID and total orders for each customer, in descending order of total orders by total dollar amount.  
Question5
CID	TotalUSDAmount
1	299
3	26
8	23.25
6	22.15
7	12
4	6
5	4.5
2	2

 

Query 6. List the item ID and description of each product that has not been ordered during the last quarter of any year. 
Question6
ItemID
1225
1302
1502
1504
3446
8766


 
Query 7. List the names of the employees who did not sell any items from the garden shop during the period from June to July. 
Question7
Name
Kaethe Oswald
Joan Switcher
Katie Hayes
Smitty Clark
John Samual
Carla Jones
Joe Chicken


 
Query 8. List the manager name and total sales revenue attributable to each manager.  
Question8
EID	TotalRevenue
15	276
16	33
10	24.25
14	23
13	15.1
17	11.8
12	8.75
11	3


 
Query 9. List the item ID and description of those items whose price is higher than the average price of item in its category. Display also the average price of the categories and name them CatAvg (so the columns will be: itemID, description, listPrice, CatAvg). 
Question9
ItemID	Description	ListPrice	CatAvg
1224	Dog Brush	$3.75	3.2
1225	Dog Toothbrush	$4.10	3.2
1302	Cat Toy	$3.75	2.625
1504	Rake	$5.99	5.245
8766	Wrench	$4.75	4.375
8776	Wrench	$5.50	4.375


 
10. The ERD 

11. Relational Schema

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.