· 9 years ago · Nov 01, 2016, 07: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 Product
73(
74 id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
75 name VARCHAR(30) NOT NULL,
76 flavor VARCHAR(30) NOT NULL,
77 price FLOAT NOT NULL,
78 cost FLOAT NOT NULL
79);
80
81
82CREATE TABLE LineItem
83(
84 purchaseId INT NOT NULL,
85 productId INT NOT NULL,
86 CONSTRAINT FKLineItem_orderId FOREIGN KEY (purchaseId) REFERENCES Purchase (id)
87 ON DELETE CASCADE,
88 CONSTRAINT FKLineItem_productId FOREIGN KEY (productId) REFERENCES Product (id)
89);
90
91
92CREATE TABLE Transaction
93(
94 id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
95 personId INT(11) NOT NULL,
96 amount FLOAT NOT NULL,
97 type INT(11),
98 CONSTRAINT FKTransaction_personId FOREIGN KEY (personId) REFERENCES Person (id)
99
100);
101
102# DATA ENTRY
103
104INSERT INTO Product (name, flavor, price, cost) VALUES
105 ('Coffee', 'Hazelnut', 2.5, 1.5);
106
107INSERT INTO Location (name) VALUES
108 ('Buck'), ('Lowrey'), ('Sylvester'),
109 ('Anderson'), ('Ferguson'), ('Joe McNab'),
110 ('Rackham'), ('School of Government'),
111 ('Library');
112
113INSERT INTO Person (firstName, lastName)
114 VALUE ('Lee', 'Tarnow');
115
116INSERT INTO Customer (lastPurchase, favorites, personId, locationId, roomNum) VALUES
117 (now(), 'Chocolate', 1, 1, 111);
118
119INSERT INTO User (personId, username, email, password, userType, dateRegistered, lastLogin)
120 VALUE (1, 'LeeTarnow', 'eelwonrat@gmail.com', 'obviouspassword', 1, NOW(), NOW()); #Assuming the person is #1
121
122INSERT INTO Product (name, flavor, price, cost) VALUES
123 ('Coffee', 'Pumpkin Spice', 3.25, 2);
124
125INSERT INTO Location (name) VALUES
126 ('Science Center');
127
128
129INSERT INTO Person (firstName, lastName)
130 VALUE ('Robert', 'McAloney');
131
132INSERT INTO Customer (lastPurchase, favorites, personId, locationId, roomNum) VALUES
133 (now(), 'Pumpkin', 2, 5, 210);
134
135
136INSERT INTO User (personId, username, email, password, userType, dateRegistered, lastLogin)
137 VALUE (2, 'rmac', 'rmac@gmail.com', 'hockey', 0, NOW(), NOW());
138
139
140INSERT INTO Product (name, flavor, price, cost) VALUES
141 ('Coffee', 'French Press', 4.5, 3);
142
143
144INSERT INTO Purchase (customerId, locationId, purchaseDate) VALUES (2, 6, now());
145INSERT INTO LineItem (purchaseId, productId) VALUES (1, 1);
146INSERT INTO Transaction (personId, amount, type) VALUES (2, 4.5, 0);
147
148# #########################################################
149# Register a Person/User as they enter the website
150# #########################################################
151# Check to make sure not registered (email)
152SELECT *
153FROM User u
154WHERE u.email = 'ngflanders@gmail.com';
155
156# Check to make sure not registered (username)
157SELECT *
158FROM User u
159WHERE u.username = 'ngflanders';
160
161# Add a person (if previous queries don't return values)
162INSERT INTO Person (firstName, lastName)
163 VALUE ('Nicholas', 'Flanders');
164
165# Add the user that corresponds to the person added
166INSERT INTO User (personId, username, email, password, userType, dateRegistered, lastLogin)
167 VALUE (3, 'ngflanders', 'ngflanders@gmail.com', 'secretpassword', 1, NOW(), NOW()); #Assuming the person is #1
168
169INSERT INTO Customer (lastPurchase, favorites, personId, locationId, roomNum) VALUES
170 (now(), 'Dirt', 3, 5, 123);
171
172# #########################################################
173# User makes a purchase
174# #########################################################
175INSERT INTO Purchase (customerId, locationId, purchaseDate) VALUES (1, 1, now());
176INSERT INTO LineItem (purchaseId, productId) VALUES (2, 3);
177INSERT INTO Transaction (personId, amount, type) VALUES (1, 2.5, 0);
178
179INSERT INTO Purchase (customerId, locationId, purchaseDate) VALUES (2, 5, DATE_ADD(now(), INTERVAL 1 DAY));
180INSERT INTO LineItem (purchaseId, productId) VALUES (3, 1);
181INSERT INTO Transaction (personId, amount, type) VALUES (2, 3.25, 0);
182
183# #########################################################
184# Query to show products as a menu on website
185# #########################################################
186# Filter by Hazelnut flavor
187SELECT
188 name,
189 flavor,
190 price
191FROM Product
192WHERE flavor = 'Hazelnut';
193
194# Get all products
195SELECT
196 name,
197 flavor,
198 price
199FROM Product;
200
201# #########################################################
202# Query to show person's most recently purchased item
203# #########################################################
204SELECT
205 p.name,
206 p.flavor,
207 p.cost
208FROM Product p
209 JOIN Lineitem ON p.id = Lineitem.productId
210 JOIN Purchase ON Lineitem.purchaseId = Purchase.id
211 JOIN Customer ON Purchase.customerId = Customer.id
212WHERE lastPurchase = purchaseDate AND customer.id = 2;
213
214# #########################################################
215# Write a query that tells us how much profit we've made this week
216# #########################################################
217SELECT sum(price) - sum(cost) Profit
218FROM Product p
219 JOIN LineItem li ON p.id = li.productId
220 JOIN Purchase pr ON li.purchaseId = pr.id
221WHERE pr.purchaseDate <= SUBDATE(now(), INTERVAL 1 WEEK);
222
223# #########################################################
224# Find the most profitable location
225# #########################################################
226SELECT
227 max(price - cost),
228 Location.name
229FROM Purchase
230 JOIN LineItem ON Purchase.id = LineItem.purchaseId
231 JOIN Product ON LineItem.productId = Product.id
232 JOIN Location ON Purchase.locationId = Location.id;
233
234# #########################################################
235# Find the most profitable day of the week
236# #########################################################
237SELECT
238 max(price - cost),
239 DAYNAME(purchaseDate)
240FROM Purchase
241 JOIN LineItem ON Purchase.id = LineItem.purchaseId
242 JOIN Product ON LineItem.productId = Product.id;
243
244# #########################################################
245# Biggest Spender
246# #########################################################
247SELECT
248 Person.firstName,
249 lastName,
250 max(price)
251FROM Person
252 JOIN Customer ON Person.id = Customer.personId
253 JOIN purchase ON Customer.id = Purchase.customerId
254 JOIN lineitem ON Purchase.id = LineItem.purchaseId
255 JOIN product ON LineItem.productId = Product.id;
256
257# #########################################################
258# How many new users this month
259# #########################################################
260SELECT count(*) `New Users`
261FROM User u
262WHERE u.dateRegistered > SUBDATE(now(), INTERVAL 1 MONTH);
263
264# #########################################################
265# Show any products never purchased
266# #########################################################
267SELECT *
268FROM Product
269 LEFT JOIN LineItem ON Product.id = LineItem.productId
270WHERE purchaseId IS NULL;
271
272# #########################################################
273# Show least purchased products greater than 0
274# #########################################################
275SELECT
276 count(*),
277 name,
278 flavor,
279 productId
280FROM Product
281 JOIN LineItem ON Product.id = LineItem.productId
282 JOIN Purchase ON LineItem.purchaseId = Purchase.id
283GROUP BY productId
284ORDER BY count(*)
285LIMIT 1;