· 9 years ago · Oct 28, 2016, 02:06 AM
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 lastLogin DATETIME,
30 UNIQUE KEY `UKusername` (username),
31 UNIQUE KEY `UKemail` (email),
32 CONSTRAINT FKUser_personId FOREIGN KEY (personId) REFERENCES Person (id)
33);
34
35
36CREATE TABLE Customer
37(
38 id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
39 lastPurchase DATETIME,
40 favorites VARCHAR(100),
41 personId INT NOT NULL,
42 locationId INT(11) NOT NULL,
43 roomNum INT(11) NOT NULL,
44 CONSTRAINT FKPerson_locationId FOREIGN KEY (locationId) REFERENCES Location (id),
45 CONSTRAINT FOREIGN KEY (personId) REFERENCES Person (id)
46);
47
48
49CREATE TABLE Inventory
50(
51 id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
52 name VARCHAR(50) NOT NULL,
53 amount INT(11) NOT NULL,
54 units VARCHAR(50) NOT NULL,
55 store VARCHAR(50) NOT NULL,
56 cost FLOAT NOT NULL
57);
58
59
60CREATE TABLE Purchase
61(
62 id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
63 customerId INT(11) NOT NULL,
64 locationId INT NOT NULL,
65 purchaseDate DATETIME NOT NULL,
66 CONSTRAINT FKPurchase_customerId FOREIGN KEY (customerId) REFERENCES Customer (id),
67 CONSTRAINT FKPurchase_locationId FOREIGN KEY (locationId) REFERENCES Location (id)
68);
69
70
71CREATE TABLE Product
72(
73 id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
74 name VARCHAR(30) NOT NULL,
75 flavor VARCHAR(30) NOT NULL,
76 price FLOAT NOT NULL,
77 cost FLOAT NOT NULL
78);
79
80
81CREATE TABLE LineItem
82(
83 purchaseId INT NOT NULL,
84 productId INT NOT NULL,
85 CONSTRAINT FKLineItem_orderId FOREIGN KEY (purchaseId) REFERENCES Purchase (id)
86 ON DELETE CASCADE,
87 CONSTRAINT FKLineItem_productId FOREIGN KEY (productId) REFERENCES Product (id)
88);
89
90
91CREATE TABLE Transaction
92(
93 id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
94 personId INT(11) NOT NULL,
95 amount FLOAT NOT NULL,
96 type INT(11),
97 CONSTRAINT FKTransaction_personId FOREIGN KEY (personId) REFERENCES Person (id)
98
99);
100
101# #########################################################
102# Register a Person/User as they enter the website
103# #########################################################
104# Check to make sure not registered (email)
105SELECT *
106FROM User u
107WHERE u.email = 'ngflanders@gmail.com';
108
109# Check to make sure not registered (username)
110SELECT *
111FROM User u
112WHERE u.username = 'ngflanders';
113
114# Add a person (if previous queries don't return values)
115INSERT INTO Person (firstName, lastName)
116 VALUE ('Nicholas', 'Flanders');
117
118# Add the user that corresponds to the person added
119INSERT INTO User (personId, username, email, password, userType, lastLogin)
120 VALUE (1, 'ngflanders', 'ngflanders@gmail.com', 'secretpassword', 1, NOW()); #Assuming the person is #1
121
122
123# #########################################################
124# Query to show products as a menu on website
125# #########################################################
126# Filter by Hazelnut flavor
127SELECT
128 name,
129 flavor,
130 price
131FROM Product
132WHERE flavor = 'Hazelnut';
133
134# Get all products
135SELECT
136 name,
137 flavor,
138 price
139FROM Product;
140
141# #########################################################
142# Query to show top favorite products or most recently purchased
143# #########################################################
144SELECT
145 p.name,
146 p.flavor,
147 p.cost
148FROM Product p
149 JOIN Lineitem ON p.id = Lineitem.productId
150 JOIN Purchase ON Lineitem.purchaseId = Purchase.id
151 JOIN Customer ON Purchase.customerId = Customer.id
152WHERE lastPurchase = purchaseDate;
153
154# Set up
155INSERT INTO Product (name, flavor, price, cost) VALUES
156 ('Coffee', 'Hazelnut', 2.5, 1.5);
157
158INSERT INTO Location (name) VALUES
159 ('Buck'), ('Lowrey'), ('Sylvester'),
160 ('Anderson'), ('Ferguson'), ('Joe McNab'),
161 ('Rackham'), ('School of Government'),
162 ('Library');
163
164INSERT INTO Customer (lastPurchase, favorites, personId, locationId, roomNum) VALUES
165 (now(), 'Chocolate', 1, 1, 111);
166
167# #########################################################
168# User makes a purchase
169# #########################################################
170INSERT INTO Purchase (customerId, locationId, purchaseDate) VALUES (1, 1, now());
171INSERT INTO LineItem VALUES (1, 1);
172INSERT INTO Transaction (personId, amount, type) VALUES (1, 2.5, 0);
173
174# #########################################################
175# Write a query that tells us how much profit we've made this week
176# #########################################################
177SELECT sum(cost)
178FROM Product p
179 JOIN LineItem li ON p.id = li.productId
180 JOIN Purchase pr ON li.purchaseId = pr.id
181WHERE pr.purchaseDate <= SUBDATE(now(), INTERVAL 1 WEEK);