generated from streamlit/app-starter-kit
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathdb_commands.txt
355 lines (295 loc) · 16.6 KB
/
db_commands.txt
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
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
-- CREATE DATABASE
USE vg472; -- Enter your UCID here.
-- CREATE TABLES
DROP TABLE IF EXISTS CUSTOMER;
CREATE TABLE CUSTOMER(
CID CHAR(5) NOT NULL,
FName VARCHAR(20) NOT NULL,
LName VARCHAR(20) NOT NULL,
EMail VARCHAR(50) NOT NULL,
Address VARCHAR(250),
Phone VARCHAR(12),
Status VARCHAR(10) CHECK (Status IN ('platinum','gold','silver','bronze')),
PRIMARY KEY (CID),
UNIQUE(EMail));
DROP TABLE IF EXISTS SILVER_AND_ABOVE;
CREATE TABLE SILVER_AND_ABOVE(
CID CHAR(5) NOT NULL,
CreditLine VARCHAR(250),
PRIMARY KEY (CID),
FOREIGN KEY (CID) REFERENCES CUSTOMER(CID));
DROP TABLE IF EXISTS CREDIT_CARD;
CREATE TABLE CREDIT_CARD(
CCNumber VARCHAR(20) NOT NULL,
SecNumber VARCHAR(4) NOT NULL,
OwnerName VARCHAR(20) NOT NULL,
CCType VARCHAR(15),
BilAddrress VARCHAR(250),
EXpDate date,
StoredCardCID CHAR(5),
PRIMARY KEY(CCNumber),
FOREIGN KEY(StoredCardCID) REFERENCES CUSTOMER(CID));
DROP TABLE IF EXISTS SHIPPING_ADDRESS;
CREATE TABLE SHIPPING_ADDRESS(
CID CHAR(5) NOT NULL,
SAName VARCHAR(20) NOT NULL,
RecepientName VARCHAR(25),
SNumber VARCHAR(10),
Street VARCHAR(50),
City VARCHAR(15),
State VARCHAR(20),
Country VARCHAR(20),
Zip VARCHAR(6),
PRIMARY KEY(CID,SAName),
FOREIGN KEY(CID) REFERENCES SILVER_AND_ABOVE(CID));
DROP TABLE IF EXISTS BASKET;
CREATE TABLE BASKET(
BID CHAR(7) NOT NULL,
CID CHAR(5),
PRIMARY KEY(BID),
FOREIGN KEY(CID) REFERENCES CUSTOMER(CID));
DROP TABLE IF EXISTS TRANSACTION;
CREATE TABLE TRANSACTION(
BID CHAR(7) NOT NULL,
CID CHAR(5) NOT NULL,
SAName VARCHAR(20) NOT NULL,
TDate DATE,
CCNumber VARCHAR(20) NOT NULL,
TTag VARCHAR(20),
PRIMARY KEY(BID, CCNumber, CID, SAName),
CONSTRAINT TR_SA_FK
FOREIGN KEY(CID,SAName) REFERENCES SHIPPING_ADDRESS(CID,SAName),
CONSTRAINT TR_CC_FK
FOREIGN KEY(CCNumber) REFERENCES CREDIT_CARD(CCNUMBER),
CONSTRAINT TR_BID_FK
FOREIGN KEY(BID) REFERENCES BASKET(BID));
DROP TABLE IF EXISTS PRODUCT;
CREATE TABLE PRODUCT(
PID CHAR(4) NOT NULL,
PName VARCHAR(30) NOT NULL,
PType VARCHAR(30),
PPrice DECIMAL(10,2),
PQuantity INT DEFAULT(0),
Description VARCHAR(255),
PRIMARY KEY(PID),
UNIQUE(PName));
DROP TABLE IF EXISTS APPEARS_IN;
CREATE TABLE APPEARS_IN(
BID CHAR(7) NOT NULL,
PID CHAR(4) NOT NULL,
Quantity INT,
PriceSold DECIMAL(10,2),
PRIMARY KEY(BID, PID),
FOREIGN KEY(BID) REFERENCES BASKET(BID),
FOREIGN KEY(PID) REFERENCES PRODUCT(PID));
DROP TABLE IF EXISTS OFFER_PRODUCT;
CREATE TABLE OFFER_PRODUCT(
PID CHAR(4) NOT NULL,
OfferPrice DECIMAL(10,2) NOT NULL,
PRIMARY KEY(PID),
FOREIGN KEY(PID) REFERENCES PRODUCT(PID));
DROP TABLE IF EXISTS COMPUTER;
CREATE TABLE COMPUTER(
PID CHAR(4) NOT NULL,
CPUType VARCHAR(30),
PRIMARY KEY(PID),
FOREIGN KEY(PID) REFERENCES PRODUCT(PID));
DROP TABLE IF EXISTS PRINTER;
CREATE TABLE PRINTER(
PID CHAR(4) NOT NULL,
PrinterType VARCHAR(25),
Resolution VARCHAR(10),
PRIMARY KEY(PID),
FOREIGN KEY(PID) REFERENCES PRODUCT(PID));
DROP TABLE IF EXISTS LAPTOP;
CREATE TABLE LAPTOP(
PID CHAR(4) NOT NULL,
BType VARCHAR(25),
Weight DECIMAL(3,2),
PRIMARY KEY(PID),
FOREIGN KEY(PID) REFERENCES COMPUTER(PID));
-- POPULATE THE DATABASE
INSERT INTO CUSTOMER VALUES(10001, 'Vinay', 'Gazula', 'vinayram@gmail.com', '359 Broad Ave', '6404449400', 'platinum');
INSERT INTO CUSTOMER VALUES(10002, 'Ganesh', 'Ravuru','ganesh@gmail.com', '0001 World street','9500000001', 'platinum');
INSERT INTO CUSTOMER VALUES(10003, 'Shivrishvith', 'Palthyavathu','shivrishvith@gmail.com','333 cross street','6123697401', 'platinum');
INSERT INTO CUSTOMER VALUES(10004, 'Manikanta', 'Rayala','manikantar@gmail.com', '456 Central Ave','3338449000', 'platinum');
INSERT INTO CUSTOMER VALUES(10005, 'Rohan', 'Katkam','rohank@gmail.com', '2015 Davis Ave','9908333028', 'bronze');
INSERT INTO CUSTOMER VALUES(10006, 'Virat', 'Kohli','vk18@gmail.com', '099 RCB','9598450018', 'gold');
INSERT INTO CUSTOMER VALUES(10007, 'Mahendhar', 'Dhoni','msd07@gmail.com', '184 Newark Ave','987654007', 'gold');
INSERT INTO CUSTOMER VALUES(10008, 'Stephen', 'Hawking','stephenhawking@gmail.com', '2108 Central Ave','2565552233', 'silver');
INSERT INTO CUSTOMER VALUES(10009, 'Robbert', 'Parker','robbertparker@gmail.com', '155 William', '4567809024', 'silver');
INSERT INTO CUSTOMER VALUES(10010, 'Charles', 'Leclerc','cl16@gmail.com', '132 Hamilton Ave', '6742087421','gold');
INSERT INTO CUSTOMER VALUES(10011, 'Lewis', 'Hamilton','lh44@gmail.com', '112 Hamilton Ave', '6768608742', 'silver');
INSERT INTO CUSTOMER VALUES(10012, 'Sebastian', 'Vettel','sv5@gmail.com', '185 Hamilton Ave', '4568608742', 'bronze');
INSERT INTO CUSTOMER VALUES(10013, 'Charlos', 'Sainz','cs55@gmail.com', '155 Hamilton Ave', '4348608742', 'gold');
INSERT INTO CUSTOMER VALUES(10014, 'Toto', 'Wolf','tw99@gmail.com', '195 Hamilton Ave', '7660874251', 'bronze');
INSERT INTO CUSTOMER VALUES(10016, 'ABC', 'XYZ','abc@gmail.com', '0001 World street','9500000001', 'platinum');
INSERT INTO SILVER_AND_ABOVE VALUES(10001, 700);
INSERT INTO SILVER_AND_ABOVE VALUES(10002, 718);
INSERT INTO SILVER_AND_ABOVE VALUES(10003, 680);
INSERT INTO SILVER_AND_ABOVE VALUES(10004, 696);
INSERT INTO SILVER_AND_ABOVE VALUES(10006, 850);
INSERT INTO SILVER_AND_ABOVE VALUES(10007, 800);
INSERT INTO SILVER_AND_ABOVE VALUES(10008, 767);
INSERT INTO SILVER_AND_ABOVE VALUES(10009, 723);
INSERT INTO SILVER_AND_ABOVE VALUES(10010, 750);
INSERT INTO SILVER_AND_ABOVE VALUES(10011, 703);
INSERT INTO SILVER_AND_ABOVE VALUES(10013, 725);
INSERT INTO CREDIT_CARD VALUES('4233523342018794', '5698', 'Charles','RuPay', '132 Hamilton Ave', '2028-09-14','10010');
INSERT INTO CREDIT_CARD VALUES('4233523302354581', '7894', 'Lewis','Visa', '112 Hamilton Ave', '2028-09-14','10011');
INSERT INTO CREDIT_CARD VALUES('4233523378520078', '1248', 'Sebastian','Visa', '185 Hamilton Ave', '2028-09-14','10012');
INSERT INTO CREDIT_CARD VALUES('4233523312569840', '789', 'Fernando','MasterCard', '199 Hamilton Ave', '2028-09-14','10012');
INSERT INTO CREDIT_CARD VALUES('4233523378950479', '4005', 'Sebastian','Visa', '185 Hamilton Ave', '2028-09-14','10012');
INSERT INTO CREDIT_CARD VALUES('4233523346287910', '745', 'Vinay','MasterCard', '359 Broad Ave', '2028-09-14','10001');
INSERT INTO CREDIT_CARD VALUES('4233523356845978', '4890', 'Ganesh','Visa', '0001 World street', '2028-09-14','10002');
INSERT INTO CREDIT_CARD VALUES('4233523356845788', '890', 'Manikanta','MasterCard', '456 Central Ave', '2028-09-14','10004');
INSERT INTO CREDIT_CARD VALUES('4233523378124566', '123', 'Stephen','MasterCard', '2108 Central Ave', '2028-09-14','10008');
INSERT INTO CREDIT_CARD VALUES('4233523396201485', '996', 'Rohan','MasterCard', '2015 Davis Ave', '2028-09-14','10005');
INSERT INTO CREDIT_CARD VALUES('4233523374259617', '007', 'Toto','MasterCard', '195 Hamilton Ave', '2028-09-14','10014');
INSERT INTO CREDIT_CARD VALUES('4233523359831478', '7849', 'Susie','Visa', '195 Hamilton Ave', '2028-09-14','10014');
INSERT INTO CREDIT_CARD VALUES('4233523343457005', '365', 'Lewis','MasterCard', '112 Hamilton Ave', '2028-09-14','10011');
INSERT INTO CREDIT_CARD VALUES('4233523343785418', '1899', 'Virat','Visa', '009 RCB', '2028-09-14','10006');
INSERT INTO CREDIT_CARD VALUES('4233523343785408', '199', 'Virat','MasterCard', '009 RCB', '2028-09-14','10006');
INSERT INTO CREDIT_CARD VALUES('4233523348794163', '209', 'Charlos','MasterCard', '155 Hamilton Ave', '2028-09-14','10013');
INSERT INTO SHIPPING_ADDRESS VALUES(10013, 'Charlos Home', 'Charlos Sainz', NULL, '185 Hamilton Ave', 'Harrison', 'NJ', 'USA', '07029');
INSERT INTO SHIPPING_ADDRESS VALUES(10013, 'Charlos Office', 'Charlos Sainz', 26, 'MG Ave', 'Nurburg', 'NJ', 'Germany', '156325');
INSERT INTO SHIPPING_ADDRESS VALUES(10001, 'Vinay Home', 'Vinay Ram', '32-8', '01 ABC', 'Vijayawada', 'AP', 'India', '520003');
INSERT INTO SHIPPING_ADDRESS VALUES(10002, 'Ganesh Office', 'Ganesh Sai', 5, '487 ACHYD', 'Hyderabad', 'TS', 'India', '525003');
INSERT INTO SHIPPING_ADDRESS VALUES(10003, 'Shivrishvith Home', 'Shivrishvith', 96, '101 XYz', 'Chennai', 'TN', 'India', '548962');
INSERT INTO SHIPPING_ADDRESS VALUES(10010, 'Charles Home', 'Charles Leclerc', NULL, '132 Hamilton Ave', 'Harrison', 'NJ', 'USA', '07126');
INSERT INTO SHIPPING_ADDRESS VALUES(10011, 'Lewis Home', 'Lewis Hamilton', NULL, '112 Hamilton Ave', 'Harrison', 'NJ', 'USA', '07126');
INSERT INTO SHIPPING_ADDRESS VALUES(10008, 'Hawking Home', 'Stephen Hawking', '5-89', 'Brooks Field', 'Oxford', 'LN', 'UK', '48725');
INSERT INTO SHIPPING_ADDRESS VALUES(10006, 'Virat Home', 'Virat Kohli', 5, '099 RCB', 'Banglore', 'KA', 'India', '523102');
INSERT INTO SHIPPING_ADDRESS VALUES(10006, 'Virat Office', 'Anushka', 89, 'Bandra Kurla', 'Mumbai', 'MH', 'India', '54789');
INSERT INTO SHIPPING_ADDRESS VALUES(10007, 'MSD Home', 'MS Dhoni', 1, 'AXCYU Street', 'Eluru', 'AP', 'India', '521106');
INSERT INTO SHIPPING_ADDRESS VALUES(10004, 'Manikanta Home', 'Manikanta', 189, 'BHUK Street', 'Eluru', 'AP', 'India', '521106');
INSERT INTO BASKET VALUES(1000101, 10005);
INSERT INTO BASKET VALUES(1000102, 10004);
INSERT INTO BASKET VALUES(1000103, 10006);
INSERT INTO BASKET VALUES(1000104, 10013);
INSERT INTO BASKET VALUES(1000105, 10010);
INSERT INTO BASKET VALUES(1000106, 10003);
INSERT INTO BASKET VALUES(1000107, 10001);
INSERT INTO BASKET VALUES(1000108, 10007);
INSERT INTO BASKET VALUES(1000109, 10002);
INSERT INTO BASKET VALUES(1000110, 10008);
INSERT INTO BASKET VALUES(1000111, 10006);
INSERT INTO BASKET VALUES(1000112, 10013);
INSERT INTO BASKET VALUES(1000113, 10013);
INSERT INTO TRANSACTION VALUES(1000102, 10004, 'Manikanta Home', '2023-01-20', '4233523356845788', 'Delivered');
INSERT INTO TRANSACTION VALUES(1000103, 10006, 'Virat Home', '2023-11-20', '4233523343785418', 'Delivered');
INSERT INTO TRANSACTION VALUES(1000111, 10006, 'Virat Office', '2023-09-01', '4233523343785408', 'Not Delivered');
INSERT INTO TRANSACTION VALUES(1000107, 10001, 'Vinay Home', '2023-12-09', '4233523346287910', 'Delivered');
INSERT INTO TRANSACTION VALUES(1000105, 10010, 'Charles Home', '2023-11-30', '4233523342018794', 'Not Delivered');
INSERT INTO TRANSACTION VALUES(1000110, 10008, 'Hawking Home', '2022-01-20', '4233523378124566', 'Delivered');
INSERT INTO TRANSACTION VALUES(1000104, 10013, 'Charlos Home', '2023-06-25', '4233523348794163', 'Delivered');
INSERT INTO TRANSACTION VALUES(1000112, 10013, 'Charlos Office', '2023-12-12', '4233523348794163', 'Not Delivered');
INSERT INTO TRANSACTION VALUES(1000109, 10002, 'Ganesh Office', '2023-12-12', '4233523356845978', 'Failed Transaction');
INSERT INTO PRODUCT VALUES(8732, 'Alienware M15', 'Laptop', 1999.99, 506, 'Super slim gaming laptop from Alienware with Intel i9 processor and Nvidia GPU.');
INSERT INTO PRODUCT VALUES(1267, 'MacBook Air 15', 'Laptop', 1599.99, 260, 'Super light and long lasting laptop from Apple with M2 Chip and 10 core GPU with intergrated MacOS and new 15 inch HDR display.');
INSERT INTO PRODUCT VALUES(7856, 'MacBook Pro 16', 'Laptop', 2999.99, 120, 'New MacBook Pro with M3 Ultra processor for faster processing speeds and comes with built-in HDMI port for productivity.');
INSERT INTO PRODUCT VALUES(3235, 'Lenovo Legion', 'Computer', 2249.99, 70, 'All in one desktop computer with Intel i9 processor and liquid cooling CPU setup.');
INSERT INTO PRODUCT VALUES(5980, 'Dell 8569', 'Computer', 599.99, 86, 'Basic computer with Intel i3 CPU and 8 GB RAM.');
INSERT INTO PRODUCT VALUES(5554, 'HP 8785', 'Computer', 649.99, 100, 'Basic computer with Intel i3 CPU and 8 GB RAM.');
INSERT INTO PRODUCT VALUES(9874, 'Asus 5896', 'Computer', 499.99, 50, 'Basic computer with Intel i3 CPU and 8 GB RAM.');
INSERT INTO PRODUCT VALUES(1112, 'HP Inkjet 5825', 'Printer', 249.99, 30, 'Basic printer.');
INSERT INTO PRODUCT VALUES(1113, 'Epson 5825', 'Printer', 249.99, 30, 'Basic printer.');
INSERT INTO PRODUCT VALUES(1114, 'Canon 9778', 'Printer', 249.99, 30, 'Basic printer.');
INSERT INTO PRODUCT VALUES(8975, 'Dell 9999', 'Desktop', 1649.99, 261, 'Dell All in one desktop with Intel i9, 16 GB DDR5 RAM, NVIDIA 4070 MaxQ GPU and 27 inch QHD display.');
INSERT INTO PRODUCT VALUES(8940, 'HP 9999', 'Desktop', 1749.99, 261, 'HP All in one desktop with Intel i9, 16 GB DDR5 RAM, NVIDIA 4070 MaxQ GPU and 27 inch QHD display.');
INSERT INTO APPEARS_IN VALUES(1000101, 8732, 1, 1999.99);
INSERT INTO APPEARS_IN VALUES(1000103, 5980, 1, 599.99);
INSERT INTO APPEARS_IN VALUES(1000103, 1113, 1, 249.99);
INSERT INTO APPEARS_IN VALUES(1000103, 8975, 1, 1649.99);
INSERT INTO APPEARS_IN VALUES(1000102, 1267, 2, 1599.99);
INSERT INTO APPEARS_IN VALUES(1000102, 7856, 1, 2999.99);
INSERT INTO APPEARS_IN VALUES(1000104, 3235, 1, 2249.99);
INSERT INTO APPEARS_IN VALUES(1000105, 5980, 1, 599.99);
INSERT INTO APPEARS_IN VALUES(1000106, 5554, 1, 649.99);
INSERT INTO APPEARS_IN VALUES(1000107, 8732, 2, 1999.99);
INSERT INTO APPEARS_IN VALUES(1000107, 7856, 2, 2999.99);
INSERT INTO APPEARS_IN VALUES(1000108, 1112, 1, 249.99);
INSERT INTO APPEARS_IN VALUES(1000109, 1113, 3, 249.99);
INSERT INTO APPEARS_IN VALUES(1000109, 7856, 1, 2999.99);
INSERT INTO APPEARS_IN VALUES(1000109, 3235, 2, 2249.99);
INSERT INTO APPEARS_IN VALUES(1000110, 1114, 5, 249.99);
INSERT INTO APPEARS_IN VALUES(1000111, 8975, 1, 1649.99);
INSERT INTO APPEARS_IN VALUES(1000112, 8940, 3, 1749.99);
INSERT INTO APPEARS_IN VALUES(1000113, 8975, 1, 1649.99);
INSERT INTO APPEARS_IN VALUES(1000113, 1112, 1, 249.99);
INSERT INTO APPEARS_IN VALUES(1000113, 3235, 2, 2249.99);
INSERT INTO OFFER_PRODUCT VALUES(8940, 1499.99);
INSERT INTO OFFER_PRODUCT VALUES(1114, 99.99);
INSERT INTO OFFER_PRODUCT VALUES(1112, 99.99);
INSERT INTO OFFER_PRODUCT VALUES(3235, 1999.99);
INSERT INTO OFFER_PRODUCT VALUES(5554, 499.99);
INSERT INTO COMPUTER VALUES(3235, 'Intel i9');
INSERT INTO COMPUTER VALUES(5980, 'Intel i3');
INSERT INTO COMPUTER VALUES(5554, 'Intel i3');
INSERT INTO COMPUTER VALUES(9874, 'Intel i3');
INSERT INTO COMPUTER VALUES(8975, 'Intel i9');
INSERT INTO COMPUTER VALUES(8940, 'Intel i9');
INSERT INTO COMPUTER VALUES(8732, 'Intel i9');
INSERT INTO COMPUTER VALUES(1267, 'M2');
INSERT INTO COMPUTER VALUES(7856, 'M3 Ultra Max');
INSERT INTO PRINTER VALUES(1112, 'A4 Inkjet', 'HD');
INSERT INTO PRINTER VALUES(1113, 'A4 Laser', '4K');
INSERT INTO PRINTER VALUES(1114, 'A3 Laser', 'HD');
INSERT INTO LAPTOP VALUES(8732, 'Lithium Ion', 2.60);
INSERT INTO LAPTOP VALUES(1267, 'Lithium Ion', 1.80);
INSERT INTO LAPTOP VALUES(7856, 'LI-Cad Ion', 2.20);
-- DROP SEQUENCE FOR TABLES
DROP TABLE IF EXISTS LAPTOP;
DROP TABLE IF EXISTS PRINTER;
DROP TABLE IF EXISTS COMPUTER;
DROP TABLE IF EXISTS OFFER_PRODUCT;
DROP TABLE IF EXISTS APPEARS_IN;
DROP TABLE IF EXISTS PRODUCT;
DROP TABLE IF EXISTS TRANSACTION;
DROP TABLE IF EXISTS BASKET;
DROP TABLE IF EXISTS SHIPPING_ADDRESS;
DROP TABLE IF EXISTS CREDIT_CARD;
DROP TABLE IF EXISTS SILVER_AND_ABOVE;
DROP TABLE IF EXISTS CUSTOMER;
-- QUERIES
-- Total amount charged per CCNUMBER
SELECT CCNumber, SUM(PriceSold)
FROM TRANSACTION
NATURAL JOIN APPEARS_IN
GROUP BY CCNumber;
-- Best 10 CUSTOMER
SELECT TRANSACTION.CID,APPEARS_IN.PriceSold
FROM TRANSACTION
JOIN BASKET ON TRANSACTION.BID = BASKET.BID
JOIN APPEARS_IN ON APPEARS_IN.BID = BASKET.BID
JOIN PRODUCT ON PRODUCT.PID = APPEARS_IN.PID
WHERE (TRANSACTION.TDate >= '2022-01-01' )
ORDER BY PriceSold DESC LIMIT 10;
-- Best sold PRODUCT in a time period
SELECT PRODUCT.PID,PRODUCT.PNAME,APPEARS_IN.QUANTITY
FROM TRANSACTION
JOIN BASKET ON TRANSACTION.BID = BASKET.BID
JOIN APPEARS_IN ON APPEARS_IN.BID = BASKET.BID
JOIN PRODUCT ON PRODUCT.PID = APPEARS_IN.PID
WHERE (TRANSACTION.TDate >= '2022-01-01') AND (TRANSACTION.TDate <= '2023-11-30')
GROUP BY PID, APPEARS_IN.QUANTITY
ORDER BY SUM(QUANTITY) DESC LIMIT 10;
-- Most distinct CUSTOMER for a PRODUCT in a given time period
SELECT PID, COUNT(DISTINCT CID) AS 'distinct customers'
FROM TRANSACTION T, APPEARS_IN A
WHERE T.BID =A.BID AND T.TDate BETWEEN '2022-01-01' AND '2023-12-30'
GROUP BY PID;
-- Max BASKET total amount per CCNUMBER in a given time period
SELECT TRANSACTION.CCNUMBER, APPEARS_IN.QUANTITY * APPEARS_IN.PriceSold as TOTAL
FROM TRANSACTION
JOIN BASKET ON TRANSACTION.BID = BASKET.BID
JOIN APPEARS_IN ON APPEARS_IN.BID = BASKET.BID
WHERE (TRANSACTION.TDate >= '2022-01-01' AND TRANSACTION.TDate <= '2023-12-30')
GROUP BY CCNUMBER, APPEARS_IN.QUANTITY, APPEARS_IN.PriceSold ORDER BY MAX(TOTAL) DESC;
-- Avg PriceSold per PType in a given time period
SELECT PRODUCT.PType, AVG(PriceSold)
FROM APPEARS_IN
JOIN PRODUCT ON PRODUCT.PID = APPEARS_IN.PID
JOIN TRANSACTION ON TRANSACTION.BID = APPEARS_IN.BID
WHERE (TRANSACTION.TDate >= '2022-01-01' AND TRANSACTION.TDate <= '2023-12-30') AND PRODUCT.PType IN ("Desktop", "Laptop", "Printer")
GROUP BY PRODUCT.PType;