Sample SQL Queries 07061055

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

1. List all items that sold for less than cost in the month of December. Hint: Use the function MONTH(date) to return the month of a date. 
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

 
2. List the inventory category and total quantity (by each category) ordered in the year 2015 (in descending order of total quantity). Hint: Use the function YEAR(date) similar to MOMTH(date).
Question2
Category	SumOfQuantity
CAT	3
DOG	3
GAR	2
TOOL	2

 
3. List the Spring products ordered by florists that had a list price over $200. 
[Watch: 
(1) Join MANY tables!! 
(2) same field name appear in multiple tables; 
(3) same field name in multiple tables could mean different things!!]
Question3
Category	CategoryDescription	ItemDescription	ListPrice	Season	WhoOrdered
FLO	Florist Supplies	Archway	$275.00	SPRING	FLO

 
4. [This problem has TWO questions] 
List the customer ID and total orders for each customer, in descending order of total orders.  
(Compute once for total number of orders, and once for total dollar amount – so two queries)
Question41
CID	SumOfQuantity
8	13
3	12
1	8
6	7
4	3
7	2
5	2
2	2

 

Question42
CID	TotalUSDAmount
1	299
3	26
8	23.25
6	22.15
7	12
4	6
5	4.5
2	2

 
5. List the item ID and description of each product that has not been ordered during the last quarter of any year. 
[Please be EXACT in implementing the logic AS IS STATED – do NOT do your “substitution” to anything that is not EXACTLY stated in the problem; i.e., “not been ordered during the last quarter” EXACTLY means it states; it is NOT “those ordered outside last quarter” – because those ordered outside Q4 could also be ordered in Q4]
Question5
ItemID
1225
1302
1502
1504
3446
8766

 
6. List the names of the employees who did not sell any items from the garden shop during the period from June to July. [Note: Same note as that for the previous question]
Question6
Name
Kaethe Oswald
Joan Switcher
Katie Hayes
Smitty Clark
John Samual
Carla Jones
Joe Chicken

 
7. List the manager name and total sales revenue attributable to each manager.  (The list should be in descending order by total sales revenue.) 【Self-join!】
Question7
EID	TotalRevenue
15	276
16	33
10	24.25
14	23
13	15.1
17	11.8
12	8.75
11	3

 
8. (Brand-new) 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). 
Hint: The same logic as the Week 15 demo “List restaurants whose sales higher than the average sales in its own city” – passing a parameter from outer query into the inner query.
Question8
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


 
9. Use the “Restaurants” database, write ONE query (with subquery) to list 
a. Restaurant’s ID, state, Type of service, annual sales;
b. Average sales of the restaurant’s state and type (simultaneously, i.e., the average of a specific state and specific type);
c. For those restaurants whose sales are greater than that in “b”
Hint: Also need to pass parameters into subquery.
Question9
RestaurantID	State	TypeofService	AnnualSales	AvgSalesOfState	AvgSaleOfService
R0003	FL	Table Service	$750,000.00	$531,428.57	$671,833.33
R0005	FL	Table & Take-out	$800,000.00	$531,428.57	$613,000.00
R0009	FL	Take-out	$700,000.00	$531,428.57	$368,000.00
R0010	FL	Table Service	$750,000.00	$531,428.57	$671,833.33
R0011	FL	Table & Take-out	$675,000.00	$531,428.57	$613,000.00
R0016	CA	Table Service	$876,000.00	$658,000.00	$671,833.33
R0017	CA	Table & Take-out	$888,000.00	$658,000.00	$613,000.00
R0019	CA	Table & Take-out	$765,000.00	$658,000.00	$613,000.00

Add Comment