· 6 years ago · Nov 26, 2019, 04:56 PM
1-- Create database
2DROP DATABASE IF EXISTS JungleBooks;
3CREATE DATABASE JungleBooks;
4USE JungleBooks;
5
6-- Create tables
7CREATE TABLE Author
8(
9 AuthorID INT(11) NOT NULL AUTO_INCREMENT,
10 FirstName VARCHAR(32) NOT NULL,
11 LastName VARCHAR(32) NOT NULL,
12 PRIMARY KEY (AuthorID)
13);
14CREATE TABLE AuthorBook
15(
16 AuthorID INT(11) NOT NULL,
17 ISBN VARCHAR(13) NOT NULL,
18 PRIMARY KEY (AuthorID, ISBN)
19);
20CREATE TABLE Book
21(
22 ISBN VARCHAR(13) NOT NULL,
23 Title VARCHAR(80) NOT NULL,
24 Summary VARCHAR(255) NOT NULL,
25 Price DOUBLE NOT NULL,
26 PRIMARY KEY (ISBN)
27);
28CREATE TABLE BookCategory
29(
30 ISBN VARCHAR(13) NOT NULL,
31 CategoryID INT(11) NOT NULL,
32 PRIMARY KEY (ISBN, CategoryID)
33);
34CREATE TABLE BookOrder
35(
36 ISBN VARCHAR(13) NOT NULL,
37 OrderID INT(11) NOT NULL,
38 Quantity INT(11) NOT NULL,
39 Subtotal DOUBLE NOT NULL,
40 PRIMARY KEY (ISBN, OrderID)
41);
42CREATE TABLE Category
43(
44 CategoryID INT(11) NOT NULL AUTO_INCREMENT,
45 Name VARCHAR(80) NOT NULL,
46 PRIMARY KEY (CategoryID)
47);
48CREATE TABLE Customer
49(
50 CustomerID INT(11) NOT NULL AUTO_INCREMENT,
51 FirstName VARCHAR(32) NOT NULL,
52 LastName VARCHAR(32) NOT NULL,
53 BillingAddress VARCHAR(80) NOT NULL,
54 ShippingAddress VARCHAR(80), -- NULL for same as 'BillingAddress'
55 PhoneNumber VARCHAR(14) NOT NULL,
56 Email VARCHAR(80) NOT NULL,
57 Username VARCHAR(80) NOT NULL,
58 Password VARCHAR(80) NOT NULL,
59 PRIMARY KEY (CustomerID)
60);
61CREATE TABLE `Order`
62(
63 OrderID INT(11) NOT NULL AUTO_INCREMENT,
64 OrderDate DATETIME NOT NULL,
65 ShippingDate DATETIME NULL,
66 Status VARCHAR(32) NOT NULL,
67 Subtotal DOUBLE NOT NULL, -- Sum of all book prices
68 Taxes DOUBLE NOT NULL, -- 15% of Subtotal
69 Total DOUBLE NOT NULL, -- Subtotal + taxes
70 CustomerID INT(11) NOT NULL,
71 PRIMARY KEY (OrderID)
72);
73
74-- Add table foreign keys
75ALTER TABLE AuthorBook
76 ADD CONSTRAINT FK__AuthorBook__Author__AuthorID FOREIGN KEY (AuthorID) REFERENCES Author (AuthorID);
77ALTER TABLE AuthorBook
78 ADD CONSTRAINT FK__AuthorBook__Book__ISBN FOREIGN KEY (ISBN) REFERENCES Book (ISBN);
79ALTER TABLE `Order`
80 ADD CONSTRAINT FK__Order__Customer__CustomerID FOREIGN KEY (CustomerID) REFERENCES Customer (CustomerID);
81ALTER TABLE BookCategory
82 ADD CONSTRAINT FK__BookCategory__Book__ISBN FOREIGN KEY (ISBN) REFERENCES Book (ISBN);
83ALTER TABLE BookCategory
84 ADD CONSTRAINT FK__BookCategory__Category__CategoryID FOREIGN KEY (CategoryID) REFERENCES Category (CategoryID);
85ALTER TABLE BookOrder
86 ADD CONSTRAINT FK__BookOrder__Book__ISBN FOREIGN KEY (ISBN) REFERENCES Book (ISBN);
87ALTER TABLE BookOrder
88 ADD CONSTRAINT FK__BookOrder__Order__OrderID FOREIGN KEY (OrderID) REFERENCES `Order` (OrderID);
89
90-- Create book categories
91INSERT INTO Category(CategoryID, Name) VALUES (1, 'Action');
92INSERT INTO Category(CategoryID, Name) VALUES (2, 'Fantasy');
93INSERT INTO Category(CategoryID, Name) VALUES (3, 'Romance');
94INSERT INTO Category(CategoryID, Name) VALUES (4, 'Adventure');
95INSERT INTO Category(CategoryID, Name) VALUES (5, 'Religious');
96INSERT INTO Category(CategoryID, Name) VALUES (6, 'Fiction');
97INSERT INTO Category(CategoryID, Name) VALUES (7, 'Non-Fiction');
98INSERT INTO Category(CategoryID, Name) VALUES (8, 'Historical');
99INSERT INTO Category(CategoryID, Name) VALUES (9, 'Horror');
100INSERT INTO Category(CategoryID, Name) VALUES (10, 'Humor');
101
102-- Create books
103INSERT INTO Book(ISBN, Title, Summary, Price)
104VALUES (9788466647946, 'The Way of Kings',
105 'The Way of Kings is an epic fantasy'
106 'novel written by American author Brandon'
107 'Sanderson and the first book in'
108 'The Stormlight Archive series.',
109 11.93);
110
111INSERT INTO BookCategory(ISBN, CategoryID)
112VALUES (9788466647946, 2);
113INSERT INTO BookCategory(ISBN, CategoryID)
114VALUES (9788466647946, 4);
115INSERT INTO BookCategory(ISBN, CategoryID)
116VALUES (9788466647946, 6);
117
118INSERT INTO Author(AuthorID, FirstName, LastName) VALUES (1, 'Brandon', 'Sanderson');
119INSERT INTO AuthorBook(AuthorID, ISBN) VALUES (1, 9788466647946);
120
121INSERT INTO Book(ISBN, Title, Summary, Price)
122VALUES (9781627792127, 'Six of Crows',
123 'Six of Crows is a fantasy novel written'
124 'by Leigh Bardugo. Six of Crows takes place'
125 'in the Amsterdam-inspired city of Ketterdam.'
126 'The new series takes place in the same world'
127 'as the Grisha books but is set in a different'
128 'location and time frame.', 16.33);
129
130INSERT INTO BookCategory(ISBN, CategoryID)
131VALUES (9781627792127, 2);
132INSERT INTO BookCategory(ISBN, CategoryID)
133VALUES (9781627792127, 4);
134INSERT INTO BookCategory(ISBN, CategoryID)
135VALUES (9781627792127, 6);
136
137INSERT INTO Author(AuthorID, FirstName, LastName) VALUES (2, 'Leigh', 'Bardugo');
138INSERT INTO AuthorBook(AuthorID, ISBN) VALUES (2, 9781627792127);
139
140INSERT INTO Book(ISBN, Title, Summary, Price)
141VALUES (9780434714049, 'Gold Mine',
142 'Wilbur Smith''s brilliant, exciting reconstruction'
143 'of life in the gold mine and the irresistible'
144 'magnetism of the gold itself', 12.99);
145
146INSERT INTO BookCategory(ISBN, CategoryID)
147VALUES (9780434714049, 4);
148INSERT INTO BookCategory(ISBN, CategoryID)
149VALUES (9780434714049, 8);
150
151INSERT INTO Author(AuthorID, FirstName, LastName) VALUES (3, 'Wilbur', 'Smith');
152INSERT INTO AuthorBook(AuthorID, ISBN) VALUES (3, 9780434714049);
153
154INSERT INTO Customer(CustomerID, FirstName, LastName, BillingAddress, ShippingAddress, PhoneNumber, Email, Username, Password)
155VALUES (1, 'Gabrijel', 'Lukačić', '2-5433 Clarenworth Drive, Bordinvale AR', NULL, '(301) 343-3526', 'gabrijel.lukacic@fyremail.ar', 'GabrijelL', 'geordy437');
156
157INSERT INTO Customer(CustomerID, FirstName, LastName, BillingAddress, ShippingAddress, PhoneNumber, Email, Username, Password)
158VALUES (2, 'Michael', 'Granholm', '238 Makewild Street, Dublin CR', NULL, '(901) 753-5460', 'michael1996@maker.xyz', 'Mitch', 'blackcat7');
159
160INSERT INTO Customer(CustomerID, FirstName, LastName, BillingAddress, ShippingAddress, PhoneNumber, Email, Username, Password)
161VALUES (3, 'Jasper', 'Irvin', '165 Anyway Avenue, Bordinvale AR', NULL, '(123) 434-9766', 'jasper@fyremail.ar', 'Jasper22', 'purplebean');
162
163INSERT INTO Customer(CustomerID, FirstName, LastName, BillingAddress, ShippingAddress, PhoneNumber, Email, Username, Password)
164VALUES (4, 'Goran', 'Kusic', '65 Harker Cresent, Dublin CR', NULL, '(433) 642-5412', 'goran@maker.xyz', 'Goran', 'supersecretpassword123');
165
166INSERT INTO `Order`(OrderID, OrderDate, ShippingDate, Status, Subtotal, Taxes, Total, CustomerID) VALUES (1, '2019-11-26 10:36:05', NULL, 'Incomplete', 0, 0, 0, 1);
167INSERT INTO BookOrder(ISBN, OrderID, Quantity, Subtotal) VALUES (9788466647946, 1, 1, 11.93);
168INSERT INTO BookOrder(ISBN, OrderID, Quantity, Subtotal) VALUES (9781627792127, 1, 1, 16.33);
169INSERT INTO BookOrder(ISBN, OrderID, Quantity, Subtotal) VALUES (9780434714049, 1, 1, 12.99);
170
171INSERT INTO `Order`(OrderID, OrderDate, ShippingDate, Status, Subtotal, Taxes, Total, CustomerID) VALUES (2, '2019-10-12 10:36:05', '2019-11-24 04:13:00', 'Complete', 0, 0, 0, 2);
172INSERT INTO BookOrder(ISBN, OrderID, Quantity, Subtotal) VALUES (9781627792127, 2, 1, 32.66);
173
174INSERT INTO `Order`(OrderID, OrderDate, ShippingDate, Status, Subtotal, Taxes, Total, CustomerID) VALUES (3, '2019-11-20 03:02:43', NULL, 'Incomplete', 0, 0, 0, 3);
175INSERT INTO BookOrder(ISBN, OrderID, Quantity, Subtotal) VALUES (9788466647946, 3, 5, 59.65);
176INSERT INTO BookOrder(ISBN, OrderID, Quantity, Subtotal) VALUES (9781627792127, 3, 2, 32.66);
177INSERT INTO BookOrder(ISBN, OrderID, Quantity, Subtotal) VALUES (9780434714049, 3, 10, 129.90);
178
179DELETE FROM BookOrder WHERE OrderID = 2;
180DELETE FROM `Order` WHERE OrderID = 2;
181
182UPDATE `Order` SET ShippingDate = '2019-11-27 8:02:32', Status = 'Complete' WHERE OrderID = 1;
183UPDATE BookOrder SET Quantity = 3, Subtotal = 48.99 WHERE OrderID = 2;
184
185SELECT Customer.*, OrderID FROM Customer LEFT OUTER JOIN `Order` ON Customer.CustomerID = `Order`.CustomerID WHERE OrderID IS NULL;
186
187SELECT Book.ISBN, Title, FirstName, LastName FROM Book
188 INNER JOIN AuthorBook ON Book.ISBN = AuthorBook.ISBN
189 INNER JOIN Author ON AuthorBook.AuthorID = Author.AuthorID
190 INNER JOIN BookCategory ON Book.ISBN = BookCategory.ISBN
191 INNER JOIN Category ON BookCategory.CategoryID = Category.CategoryID
192 WHERE Category.Name = 'Fantasy';
193
194SELECT Email FROM Customer INNER JOIN `Order` ON Customer.CustomerID = `Order`.CustomerID WHERE ShippingDate is NULL;
195
196SELECT `Order`.*, SUM(BookOrder.Quantity) AS BooksSold FROM `Order` INNER JOIN BookOrder ON `Order`.OrderID = BookOrder.OrderID GROUP BY `BookOrder`.OrderID HAVING BooksSold > 0;
197
198SELECT `Order`.OrderID, SUM(BookOrder.Quantity) FROM `Order` INNER JOIN BookOrder ON `Order`.OrderID = BookOrder.OrderID GROUP BY `Order`.OrderID;
199
200SELECT `Order`.OrderID, Customer.FirstName, Customer.LastName, SUM(BookOrder.Quantity * Book.Price) FROM `Order`
201 INNER JOIN Customer ON `Order`.CustomerID = Customer.CustomerID
202 INNER JOIN BookOrder ON `Order`.OrderID = BookOrder.OrderID
203 INNER JOIN Book ON BookOrder.ISBN = BOOK.ISBN GROUP BY `Order`.OrderID;