InitHEADmain

1 files changed, 191 insertions, 0 deletions

diff --git a/Homework/cs5800/final/sql/insert.sql b/Homework/cs5800/final/sql/insert.sql
new file mode 100644
index 0000000..bc6ab18
--- /dev/null
+++ b/Homework/cs5800/final/sql/insert.sql
@@ -0,0 +1,191 @@
+USE LibraryMiniWorld;
+
+INSERT INTO Person (PersonID, Name, Address, Phone, Email) VALUES
+(1, 'John Pi', '314 Main St Applewood CA 31415', '314-159-2565', 'johnpi@bruh.com'),
+(2, 'Leonhard Euler', '271 Fake Cr Logan UT 27183', '271-828-1828', 'euler@math.org'),
+(3, 'Ada Lovelace', '422 Ada Ave Salt Lake City UT 38382', '121-232-3443', 'ada@programming.edu'),
+(4, 'Alan Turing', '314 Turing Rd Reno NV 89123', '123-571-1131', 'alan@computerscience.com'),
+(5, 'Grace Hopper', '771 Hopper Ct Seattle WA 22331', '223-414-4488', 'grace@computerscience.com'),
+(6, 'Charles Babbage', '314 Babbage Rd Reno NV 89123', '881-222-1818', 'bbage @computerscience.com'),
+(7, 'John von Neumann', '442 Neumann St Idaho Falls ID 48323', '881-222-1818', 'john@neumann.com'),
+(8, 'John Conway', '888 Conway Rd Reno NV 89123', '102-103-1004', 'game@oflife.fun'),
+(9, 'Stephen Hawking', '111 Hawking Ave Seattle WA 12523', '101-102-1010', 'hawking@radiation.com');
+
+INSERT INTO Employee (PersonID, StartDate, Salary, EmployeeType) VALUES
+(1, '2001-04-02', 40000, 'Manager'),
+(2, '2001-04-02', 41000, 'Manager'),
+(3, '2001-04-02', 30000, 'Librarian'),
+(4, '2010-06-25', 29000, 'Librarian'),
+(5, '2022-12-08', 25000, 'Librarian'),
+(6, '2022-11-08', 26000, 'Librarian');
+
+INSERT IGNORE INTO Manager (EmployeeID)
+SELECT PersonID
+FROM Employee
+WHERE EmployeeType = 'Manager';
+
+INSERT IGNORE INTO Librarian (EmployeeID)
+SELECT PersonID
+FROM Employee
+WHERE EmployeeType = 'Librarian';
+
+INSERT INTO Manages(ManagerID, LibrarianID) VALUES
+(1, 3),
+(2, 4),
+(2, 5),
+(2, 6);
+
+INSERT INTO Customer (PersonID, JoinDate) VALUES
+(1, '2001-12-12'),
+(3, '2011-10-25'),
+(7, '2012-10-25'),
+(8, '2013-10-25'),
+(9, '2022-12-08');
+
+INSERT INTO LibraryCard (CardNo, CustomerID, IssueDate, ExpiryDate) VALUES
+(1, 1, '2021-12-12', '2022-12-12'),
+(2, 3, '2020-12-25', '2021-12-25'),
+(3, 7, '2020-09-15', '2022-09-25'),
+(4, 8, '2022-01-25', '2023-01-25'),
+(5, 9, '2015-12-25', '2023-01-01'),
+(6, 3, '2021-12-26', '2022-12-26');
+
+-- SET sql_safe_updates=0;
+-- 
+-- UPDATE Customer INNER JOIN LibraryCard 
+-- AS lc ON lc.CustomerID = Customer.PERSONID 
+-- AND lc.ExpiryDate = 
+--   (SELECT MAX(ExpiryDate) FROM LibraryCard 
+--    GROUP BY CustomerID HAVING CustomerID=lc.CustomerID) 
+-- SET Customer.CardNo=lc.CardNo;
+-- 
+-- SET sql_safe_updates=1; (commented out because we no longer store card number on the customer)
+
+INSERT INTO Creator (CreatorID, Name) VALUES
+(1, 'Fyodor Doestoyevsky'),
+(2, 'J.K. Rowling'),
+(3, 'Brandon Sanderson'),
+(4, 'Christopher Paolini'),
+(5, 'James Clear'),
+(6, 'William Shakespeare'),
+(7, 'Jim Dale'),
+(8, 'Stefen Fangmeier'),
+(9, 'Jennifer Nielsen');
+
+INSERT INTO Media (MediaID, DeweyNumber, Name, MediaType, CreatedYear) VALUES
+(1, '891.73','Crime and Punishment', 'Text', 2003),
+(2, '822.33','Hamlet', 'Text', 2003),
+(3, '823.91','Harry Potter and the Sorcerers Stone', 'Text', 1997),
+(4, '823.91','Harry Potter and the Sorcerers Stone', 'Audio', 1997),
+(5, '813.91','Eragon', 'Text', 2002),
+(6, '813.91','Eragon', 'Video', 2006),
+(7, '813.6','Elantris', 'Text', 2005),
+(8, '155.2','Atomic Habits', 'Audio', 2018),
+(9, '813.6', 'Lines of Courage', 'Text', 2022);
+
+INSERT INTO Text (MediaID, Pages) VALUES
+(1, 492),
+(2, 104),
+(3, 223),
+(5, 509),
+(7, 496),
+(9, 400);
+
+INSERT INTO Audio (MediaID, Playtime) VALUES
+(4, 29880),
+(8, 13380);
+
+INSERT INTO Video (MediaID, Runtime) VALUES
+(6, 6240);
+
+
+INSERT INTO Genre (GenreID, Name) VALUES
+(1, 'Fantasy'),
+(2, 'Science Fiction'),
+(3, 'Romance'),
+(4, 'Drama'),
+(5, 'Non-Fiction'),
+(6, 'Fiction'),
+(7, 'War Story');
+
+INSERT INTO MediaGenre (MediaID, GenreID) VALUES
+(1, 4),
+(2, 4),
+(3, 1),
+(4, 1),
+(5, 2),
+(6, 2),
+(7, 2),
+(8, 5),
+(9, 6),
+(9, 7);
+
+INSERT INTO Tag (TagID, Name) VALUES
+(1, 'Fun'),
+(2, 'Good for Kids'),
+(3, 'Young Adult'),
+(4, 'Sad'),
+(5, 'Educational'),
+(6, 'Classic'),
+(7, 'Popular'),
+(8, 'New'),
+(9, 'Powerful');
+
+INSERT INTO MediaTag (MediaID, TagID) VALUES
+(1, 6),
+(3, 1),
+(3, 2),
+(3, 3),
+(3, 7),
+(7, 3),
+(8, 5),
+(8, 8),
+(9, 8),
+(9, 9);
+
+
+INSERT INTO MediaCreator (MediaID, CreatorID) VALUES
+(1, 1),
+(2, 6),
+(3, 2),
+(4, 2),
+(4, 7),
+(5, 4),
+(6, 4),
+(6, 8),
+(7, 3),
+(8, 5),
+(9, 9);
+
+INSERT INTO MediaInstance (InstanceID, LibrarianID, MediaID, DateAdded) VALUES
+(1,  4, 3, '2004-05-08'),
+(2,  4, 4, '2004-05-08'),
+(3,  4, 5, '2004-05-08'),
+(4,  4, 5, '2004-05-08'),
+(5,  4, 5, '2004-05-08'),
+(6,  5, 6, '2011-03-31'),
+(7,  4, 3, '2012-05-08'),
+(8,  5, 4, '2018-10-07'),
+(9,  5, 5, '2019-02-18'),
+(10, 4, 6, '2020-01-20'),
+(11, 4, 6, '2020-01-20'),
+(12, 4, 6, '2020-01-20'),
+(13, 4, 7, '2021-01-20'),
+(14, 6, 8, '2021-03-20'),
+(15, 6, 9, '2022-05-20'),
+(16, 6, 9, '2022-05-20'),
+(17, 5, 4, '2009-05-08');
+
+INSERT INTO Borrow (LibraryCardNo, InstanceID, LibrarianID, IsReturned, BorrowDate, ReturnDate) VALUES
+(1, 1, 3, 0, CURDATE(), '2022-12-24'),
+(1, 2, 3, 0, CURDATE(), '2022-12-24'),
+(2, 10, 5, 1, '2021-10-01', '2022-10-24'),
+(3, 1, 5, 1, '2022-01-01', '2022-01-24'),
+(3, 2, 6, 1, '2022-01-01', '2022-01-24'),
+(4, 15, 4, 0, '2022-10-01', '2022-10-24'),
+(4, 6, 4, 1, '2021-12-01', '2022-12-07');
+
+INSERT INTO CustomerRate (CustomerID, MediaID, Stars, Review) VALUES
+(1, 3, 5, 'This book was great!'),
+(1, 4, 5, 'The audiobook rendition was perfect too!'),
+(8, 6, 2, 'The movie did not live up to the book in my opinion.');