· 9 years ago · Nov 03, 2016, 06:46 PM
1DROP DATABASE IF EXISTS CollegeGrind;
2CREATE DATABASE CollegeGrind;
3USE CollegeGrind;
4
5
6CREATE TABLE Location
7(
8 id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
9 name VARCHAR(30)
10);
11
12
13CREATE TABLE Person
14(
15 id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
16 firstName VARCHAR(30) NOT NULL,
17 lastName VARCHAR(30) NOT NULL
18);
19
20
21CREATE TABLE User
22(
23 id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
24 personId INT(11) NOT NULL,
25 username VARCHAR(40) NOT NULL,
26 email VARCHAR(50) NOT NULL,
27 password VARCHAR(40) NOT NULL,
28 userType TINYINT(1) NOT NULL,
29 dateRegistered DATETIME NOT NULL,
30 lastLogin DATETIME,
31 UNIQUE KEY `UKusername` (username),
32 UNIQUE KEY `UKemail` (email),
33 CONSTRAINT FKUser_personId FOREIGN KEY (personId) REFERENCES Person (id)
34);
35
36
37CREATE TABLE Customer
38(
39 id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
40 personId INT NOT NULL,
41 lastPurchase DATETIME,
42 favorites VARCHAR(100),
43 locationId INT(11),
44 roomNum INT(11),
45 CONSTRAINT FKPerson_locationId FOREIGN KEY (locationId) REFERENCES Location (id),
46 CONSTRAINT FOREIGN KEY (personId) REFERENCES Person (id)
47);
48
49
50CREATE TABLE InventoryItem
51(
52 id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
53 name VARCHAR(50) NOT NULL,
54 amount INT(11) DEFAULT 0,
55 units VARCHAR(50) NOT NULL,
56 store VARCHAR(50) NOT NULL,
57 cost FLOAT NOT NULL
58);
59
60
61CREATE TABLE Purchase
62(
63 id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
64 customerId INT(11) NOT NULL,
65 locationId INT NOT NULL,
66 purchaseDate DATETIME NOT NULL,
67 CONSTRAINT FKPurchase_customerId FOREIGN KEY (customerId) REFERENCES Customer (id),
68 CONSTRAINT FKPurchase_locationId FOREIGN KEY (locationId) REFERENCES Location (id)
69);
70
71
72CREATE TABLE Transaction
73(
74 id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
75 personId INT(11) NOT NULL,
76 amount FLOAT NOT NULL,
77 type INT(11),
78 CONSTRAINT FKTransaction_personId FOREIGN KEY (personId) REFERENCES Person (id)
79);
80
81
82CREATE TABLE Recipe
83(
84 id INT(11) PRIMARY KEY AUTO_INCREMENT,
85 name VARCHAR(50) NOT NULL,
86 description TEXT NOT NULL,
87 UNIQUE KEY `UKname` (name)
88);
89
90
91CREATE TABLE RecipeIngredient
92(
93 id INT(11) PRIMARY KEY AUTO_INCREMENT,
94 inventoryItemId INT(11) NOT NULL,
95 recipeId INT(11) NOT NULL,
96 amountRequired VARCHAR(11) NOT NULL,
97 CONSTRAINT FKRecipeIngredient_recipeId FOREIGN KEY (recipeId) REFERENCES Recipe (id),
98 CONSTRAINT FKRecipeIngredient_inventoryItemId FOREIGN KEY (inventoryItemId) REFERENCES InventoryItem (id)
99);
100
101
102CREATE TABLE RecipeStep
103(
104 id INT(11) PRIMARY KEY AUTO_INCREMENT,
105 recipeId INT(11) NOT NULL,
106 instructions TEXT NOT NULL,
107 prepTime INT(11) NOT NULL,
108 cookTime INT(11) NOT NULL,
109 CONSTRAINT FKRecipeStep_recipeId FOREIGN KEY (recipeId) REFERENCES Recipe (id)
110);
111
112
113CREATE TABLE Product
114(
115 id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
116 name VARCHAR(30) NOT NULL,
117 flavor VARCHAR(30) NOT NULL,
118 price FLOAT NOT NULL,
119 cost FLOAT NOT NULL,
120 recipeId INT(11),
121 CONSTRAINT FKProduct_recipeId FOREIGN KEY (recipeId) REFERENCES Recipe (id)
122);
123
124
125CREATE TABLE LineItem
126(
127 purchaseId INT NOT NULL,
128 productId INT NOT NULL,
129 CONSTRAINT FKLineItem_orderId FOREIGN KEY (purchaseId) REFERENCES Purchase (id)
130 ON DELETE CASCADE,
131 CONSTRAINT FKLineItem_productId FOREIGN KEY (productId) REFERENCES Product (id)
132);
133
134# DATA ENTRY
135
136INSERT INTO Product (name, flavor, price, cost, recipeId) VALUES
137 ('Coffee', 'Hazelnut', 2.5, 1.5, NULL);
138
139INSERT INTO Location (name) VALUES
140 ('Buck'), ('Lowrey'), ('Sylvester'),
141 ('Anderson'), ('Ferguson'), ('Joe McNab'),
142 ('Rackham'), ('School of Government'),
143 ('Library');
144
145INSERT INTO Person (firstName, lastName)
146 VALUE ('Lee', 'Tarnow');
147
148INSERT INTO Customer (lastPurchase, favorites, personId, locationId, roomNum) VALUES
149 (now(), 'Chocolate', 1, 1, 111);
150
151INSERT INTO User (personId, username, email, password, userType, dateRegistered, lastLogin)
152 VALUE (1, 'LeeTarnow', 'eelwonrat@gmail.com', 'obviouspassword', 1, NOW(), NOW()); #Assuming the person is #1
153
154INSERT INTO Product (name, flavor, price, cost, recipeId) VALUES
155 ('Coffee', 'Pumpkin Spice', 3.25, 2, NULL);
156
157INSERT INTO Location (name) VALUES
158 ('Science Center');
159
160
161INSERT INTO Person (firstName, lastName)
162 VALUE ('Robert', 'McAloney');
163
164INSERT INTO Customer (lastPurchase, favorites, personId, locationId, roomNum) VALUES
165 (now(), 'Pumpkin', 2, 5, 210);
166
167
168INSERT INTO User (personId, username, email, password, userType, dateRegistered, lastLogin)
169 VALUE (2, 'rmac', 'rmac@gmail.com', 'hockey', 0, NOW(), NOW());
170
171
172INSERT INTO Product (name, flavor, price, cost, recipeId) VALUES
173 ('Coffee', 'French Press', 4.5, 3, NULL);
174
175
176INSERT INTO Purchase (customerId, locationId, purchaseDate) VALUES (2, 6, now());
177INSERT INTO LineItem (purchaseId, productId) VALUES (1, 1);
178INSERT INTO Transaction (personId, amount, type) VALUES (2, 4.5, 0);
179
180# #########################################################
181# Register a Person/User as they enter the website
182# #########################################################
183# Check to make sure not registered (email)
184SELECT *
185FROM User u
186WHERE u.email = 'ngflanders@gmail.com';
187
188# Check to make sure not registered (username)
189SELECT *
190FROM User u
191WHERE u.username = 'ngflanders';
192
193# Add a person (if previous queries don't return values)
194INSERT INTO Person (firstName, lastName)
195 VALUE ('Nicholas', 'Flanders');
196
197# Add the user that corresponds to the person added
198INSERT INTO User (personId, username, email, password, userType, dateRegistered, lastLogin)
199 VALUE (3, 'ngflanders', 'ngflanders@gmail.com', 'secretpassword', 1, NOW(), NOW()); #Assuming the person is #1
200
201INSERT INTO Customer (lastPurchase, favorites, personId, locationId, roomNum) VALUES
202 (now(), 'Dirt', 3, 5, 123);
203
204# #########################################################
205# User makes a purchase
206# #########################################################
207INSERT INTO Purchase (customerId, locationId, purchaseDate) VALUES (1, 1, now());
208INSERT INTO LineItem (purchaseId, productId) VALUES (2, 3);
209INSERT INTO Transaction (personId, amount, type) VALUES (1, 2.5, 0);
210
211INSERT INTO Purchase (customerId, locationId, purchaseDate) VALUES (2, 5, DATE_ADD(now(), INTERVAL 1 DAY));
212INSERT INTO LineItem (purchaseId, productId) VALUES (3, 1);
213INSERT INTO Transaction (personId, amount, type) VALUES (2, 3.25, 0);
214
215# #########################################################
216# Query to show products as a menu on website
217# #########################################################
218# Filter by Hazelnut flavor
219SELECT
220 name,
221 flavor,
222 price
223FROM Product
224WHERE flavor = 'Hazelnut';
225
226# Get all products
227SELECT
228 name,
229 flavor,
230 price
231FROM Product;
232
233# #########################################################
234# Query to show person's most recently purchased item
235# #########################################################
236SELECT
237 p.name,
238 p.flavor,
239 p.cost
240FROM Product p
241 JOIN Lineitem ON p.id = Lineitem.productId
242 JOIN Purchase ON Lineitem.purchaseId = Purchase.id
243 JOIN Customer ON Purchase.customerId = Customer.id
244WHERE lastPurchase = purchaseDate AND customer.id = 2;
245
246# #########################################################
247# Write a query that tells us how much profit we've made this week
248# #########################################################
249SELECT sum(price) - sum(cost) Profit
250FROM Product p
251 JOIN LineItem li ON p.id = li.productId
252 JOIN Purchase pr ON li.purchaseId = pr.id
253WHERE pr.purchaseDate <= SUBDATE(now(), INTERVAL 1 WEEK);
254
255# #########################################################
256# Find the most profitable location
257# #########################################################
258SELECT
259 max(price - cost),
260 Location.name
261FROM Purchase
262 JOIN LineItem ON Purchase.id = LineItem.purchaseId
263 JOIN Product ON LineItem.productId = Product.id
264 JOIN Location ON Purchase.locationId = Location.id;
265
266# #########################################################
267# Find the most profitable day of the week
268# #########################################################
269SELECT
270 max(price - cost),
271 DAYNAME(purchaseDate)
272FROM Purchase
273 JOIN LineItem ON Purchase.id = LineItem.purchaseId
274 JOIN Product ON LineItem.productId = Product.id;
275
276# #########################################################
277# Biggest Spender
278# #########################################################
279SELECT
280 Person.firstName,
281 lastName,
282 max(price)
283FROM Person
284 JOIN Customer ON Person.id = Customer.personId
285 JOIN purchase ON Customer.id = Purchase.customerId
286 JOIN lineitem ON Purchase.id = LineItem.purchaseId
287 JOIN product ON LineItem.productId = Product.id;
288
289# #########################################################
290# How many new users this month
291# #########################################################
292SELECT count(*) `New Users`
293FROM User u
294WHERE u.dateRegistered > SUBDATE(now(), INTERVAL 1 MONTH);
295
296# #########################################################
297# Show any products never purchased
298# #########################################################
299SELECT *
300FROM Product
301 LEFT JOIN LineItem ON Product.id = LineItem.productId
302WHERE purchaseId IS NULL;
303
304# #########################################################
305# Show least purchased products greater than 0
306# #########################################################
307SELECT
308 count(*),
309 name,
310 flavor,
311 productId
312FROM Product
313 JOIN LineItem ON Product.id = LineItem.productId
314 JOIN Purchase ON LineItem.purchaseId = Purchase.id
315GROUP BY productId
316ORDER BY count(*)
317LIMIT 1;