The-Project-Box/spec.sql at main · AvigailGellis/The-Project-Box · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
/*
I own a business called The Project Box. I sell project kits in bulk to organizations such as schools and camps.
I need my bookkeeping to be organized properly. For each order I need to know the organization ordering, which project kit was ordered,
order amount, order date, delivery date, paid or not, and the total order price. I also ask each organization how they heard aboutThe Project Box.
After each order is processed and delivered, I follow up with a short survey about their overall satisfaction.
The customer chooses between extremely unsatisfied,  partially satisfied, satisfied, very satisfied, and extremely satisfied.
Be aware that not every customer completes the survey.

Pricing and delivery time depend on the order amount.
Orders 50 and less cost an additional $2/piece. Delivery time promised to the customer is 7 days.
Orders between 51 and 200 pieces cost the basic price. Delivery time promised to the customer is 7 - 14 days.
Orders between 201 and 350 pieces cost $1.50 less/piece of prices shown. Delivery time is 14 – 21 days.
Orders over 350 pieces cost $2.25 less/piece of prices shown. Delivery time is 21 – 31 days.


Basic Pricing:
Candy pillow kit – $5.00
Felt doll kit – $7.00
Throw blanket kit – $6.50
Felt Travel game kit – $5.50
Stuffed initial kit – $4.75


Questions and Answers:
Q: What is smallest possible order? What is the biggest possible order? A: The minimum order is 15 pieces. The max possible order is 1,000 pieces.
Q: What is the difference between “no rating” and “not rated yet”? A: “No rating” means that the customer didn’t respond to our survey. “Not rated yet” means that the order is not complete. An order is complete when the item is delivered.
Q: Are order or delivery dates entered in advance? A: No
Q: What’s the earliest possible order date? A: The project box was opened on 2/5/2010.
Q: Does the customer need to pay for the order to be placed? A: Yes, no order is processed without payment.

Sample Data - Organization, project kit, order amount, order date, delivery date, marketing, survey
Bnos Yisroel, Candy pillow kit, 351, 3/4/2017, 3/25/2017, yes,  Phonebook, Extremely satisfied
 Hillel Academy of Thorndale, Throw blanket kit, 57, 5/12/2019, 5/22/2019, online, partially satisfied
 Camp Funtime, Felt Travel game kit, 150, 4/15/2021, 4/30/2021, word of mouth, satisfied
 Camp Twirl, Stuffed initial kit, 300, 5/10/2019, 5/30/2019, magazine ad, extremely satisfied
 BYD, Candy pillow kit, 100, 10/17/2020, 10/21/2020, online, satisfied
 Bais Chinuch, Felt doll kit, 350, 11/21/2021, 12/09/21, word of mouth, partially satisfied
 Camp Rina, Throw blanket kit, 570, 08/01/2017, 08/20/2017, magazine ad, very satisfied
 Camp SummerTime, Felt Travel game kit, 75, 04/25/2012, 5/07/2012,  online, very satisfied
 Camp CK, Stuffed initial kit, 35, 05/25/2020, 7/31/2020, word of mouth, extremely unsatisfied
 Camp Candy Land, Candy pillow kit, 20, 08/25/2021, 08/31/2021, other, satisfied
 French School, Stuffed initial kit, 200, 01/17/2020, 01/29/2020, Phonebook, very satisfied
 Camp Sparkle, Throw blanket kit, 45, 02/15/2019, 2/19/2019, online, extremely satisfied
 The Dullis County Middle School, Felt Travel game kit, 250, 08/21/2022, not delivered yet, online, not rated yet
 CherryHill High, Candy pillow kit, 250, 05/23/2015, 06/12/2015, online, extremely satisfied
 Camp Oodle, Stuffed initial kit, 400, 04/26/2020, 05/20/2020, magazine ad, very satisfied
 Camp Dazzle, Candy pillow kit, 30,06/15/2022, 06/20/2022, word of mouth, very satisfied
 Best Travel Camp Ever, Felt Travel game kit, 25, 9/1/22, not delivered yet, online, not rated yet
 Camp BVC, Stuffed initial kit, 100, 1/1/2021, 1/12/2021, phonebook, no rating
 Bigs and Little Organization, Candy pillow kit, 500, 10/1/22, not delivered yet,  online, not rated yet

Reports:
1.	Please show a list of undelivered orders. I want to see the oldest orders first.
2.	Please show the most and lease effective marketing strategy.
3.	Please show a list of orders where the delivery time was longer than promised to the customer. This list should be ordered by order size, from least to greatest. This is something I always need to know.
4.	Please show how many of each rating we received. I also need a list of orders that were rated below satisfied.
5.	Please show the number of kits we sold per type of kit. Show the least selling kit first.


*/