ZHDLSBWc

1
2DROP DATABASE IF EXISTS projekt_jg_mk;
3CREATE DATABASE projekt_jg_mk;
4USE projekt_jg_mk;
5
6CREATE TABLE Reservation_Status ( 
7    ID INT AUTO_INCREMENT, 
8    Name VARCHAR(4000) NOT NULL, 
9    PRIMARY KEY (ID) 
10);
11
12CREATE TABLE Rent_Status ( 
13    ID INT AUTO_INCREMENT, 
14    Name VARCHAR(4000), 
15    PRIMARY KEY(ID) 
16);
17
18CREATE TABLE Library_Cards ( 
19    ID INT AUTO_INCREMENT, 
20    Card_Number VARCHAR(30), 
21    Owner_Last_Name VARCHAR(50), 
22    Owner_Phone_Number BIGINT, 
23    PRIMARY KEY(ID) 
24);
25
26CREATE TABLE Authors ( 
27    ID INT AUTO_INCREMENT, 
28    First_Name VARCHAR(4000), 
29    Last_Name VARCHAR(4000), 
30    PRIMARY KEY(ID) 
31);
32
33CREATE TABLE Genres(
34    ID int NOT NULL AUTO_INCREMENT,
35    Name varchar(4000),
36    PRIMARY KEY (ID)
37);
38 
39CREATE TABLE Branches(
40    ID int NOT NULL AUTO_INCREMENT,
41    Name varchar(4000),
42    Address varchar(4000),
43    PhoneNumber bigint,
44    OpenTime time,
45    CloseTime time,
46    PRIMARY KEY (ID)
47);
48 
49CREATE TABLE Publishers(
50    ID int NOT NULL AUTO_INCREMENT,
51    Name varchar(4000),
52    PRIMARY KEY (ID)
53);
54 
55CREATE TABLE Books(
56    ID int NOT NULL AUTO_INCREMENT,
57    Title varchar(4000),
58    YearOfRelease int,
59    Publishers_ID int,
60    Branches_ID int,
61    PRIMARY KEY (ID),
62    FOREIGN KEY(branches_id)
63        REFERENCES Branches(ID),
64    FOREIGN KEY(publishers_id)
65        REFERENCES Publishers(ID)
66);
67 
68CREATE TABLE genres_books(
69    Genres_ID int,
70    Books_ID int,
71    PRIMARY KEY(Genres_ID, Books_ID),
72    FOREIGN KEY(genres_id)
73        REFERENCES Genres(ID),
74    FOREIGN KEY(books_id)
75        REFERENCES Books(ID)
76);
77
78CREATE TABLE Positions(
79    ID int NOT NULL AUTO_INCREMENT,
80    PositionName varchar(4000),
81    Salary float,
82    PRIMARY KEY (ID)
83);
84
85CREATE TABLE Employees(
86    ID int NOT NULL AUTO_INCREMENT,
87    FirstName varchar(4000),
88    LastName varchar(4000),
89    Branches_ID int,
90    Positions_ID int,
91    PRIMARY KEY (ID),
92    FOREIGN KEY(branches_id)
93        REFERENCES Branches(ID),
94    FOREIGN KEY(positions_id)
95        REFERENCES Positions(ID)
96);
97
98CREATE TABLE authors_books ( 
99    authors_id INT NOT NULL, 
100    books_id INT NOT NULL,
101    FOREIGN KEY(authors_id) REFERENCES Authors(ID), 
102    FOREIGN KEY(books_id) REFERENCES Books(ID) 
103);
104
105CREATE TABLE Reservations ( 
106    ID INT AUTO_INCREMENT, 
107    Start_Date DATE, 
108    End_Date DATE, 
109    reservation_status_id INT NOT NULL, 
110    library_cards_id INT NOT NULL, 
111    books_id INT NOT NULL, 
112    PRIMARY KEY(ID), 
113    FOREIGN KEY(reservation_status_id) REFERENCES reservation_status(ID), 
114    FOREIGN KEY(library_cards_id) REFERENCES Library_cards(ID), 
115    FOREIGN KEY(books_id) REFERENCES books(ID) 
116);
117
118CREATE TABLE Rents ( 
119    ID INT AUTO_INCREMENT, 
120    Start_Date DATE, End_Date DATE, 
121    rent_status_id INT NOT NULL, 
122    library_cards_id INT NOT NULL, 
123    books_id INT NOT NULL, 
124    PRIMARY KEY(ID), 
125    FOREIGN KEY(rent_status_id) REFERENCES Rent_Status(ID), 
126    FOREIGN KEY(library_cards_id) REFERENCES Library_Cards(ID), 
127    FOREIGN KEY(books_id) REFERENCES Books(ID) 
128);
129
130
131INSERT INTO Reservation_Status(ID,Name) VALUES (NULL,"Waiting");
132INSERT INTO Reservation_Status(ID,Name) VALUES (NULL,"Avaiable");
133INSERT INTO Reservation_Status(ID,Name) VALUES (NULL,"Completed");
134
135INSERT INTO rent_status(ID,Name) VALUES (NULL,"rented");
136INSERT INTO rent_status(ID,Name) VALUES (NULL,"returned");
137INSERT INTO rent_status(ID,Name) VALUES (NULL,"not returned");
138
139INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Adam', 'Mickiewicz');
140INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Jan', 'Kochanowski');
141INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Juliusz', 'Slowacki');
142INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Stephen', 'King');
143INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Cortney', 'Brokenbrow');
144INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Ianthe', 'Chettle');
145INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Hershel', 'Druery');
146INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Xaviera', 'Parres');
147INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Rupert', 'Friend');
148INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Joanne', 'Mailey');
149INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Levon', 'Gudger');
150INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Far', 'Walcar');
151INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Noll', 'Hassen');
152INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Olwen', 'Shotton');
153INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Hynda', 'Madison');
154INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Tatum', 'Flippen');
155INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Hildegarde', 'Suscens');
156INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Clem', 'Danbrook');
157INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Adriana', 'Dubois');
158INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Shelden', 'Davydochkin');
159INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Celine', 'Colloff');
160INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Hillel', 'McDonough');
161INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Dee dee', 'Camier');
162INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Zach', 'Guidotti');
163INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Rolando', 'Silcocks');
164INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Jenilee', 'Friedank');
165INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Ezechiel', 'Origin');
166INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Imojean', 'de Banke');
167INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Fraze', 'Alkin');
168INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Darwin', 'Jacox');
169INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Hatty', 'Dafydd');
170INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Damita', 'Knight');
171INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'April', 'Hollyman');
172INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Micky', 'Froude');
173INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Diarmid', 'Herculson');
174INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Rhodia', 'Sandland');
175INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Petey', 'Jarmyn');
176INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Duncan', 'Anthon');
177INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Launce', 'Nutbeem');
178INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Saxe', 'Blumire');
179INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Ursala', 'Collumbell');
180INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Marge', 'Martignon');
181INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Skipper', 'Tatlowe');
182INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Collette', 'Ilbert');
183INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Norine', 'Cappleman');
184INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Elonore', 'Doul');
185INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Zaneta', 'Von Oertzen');
186INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Idelle', 'Bispham');
187INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Stephenie', 'Marsie');
188INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Nadean', 'Noraway');
189INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Ofelia', 'Worms');
190INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Gabriellia', 'Tallis');
191INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Leigh', 'Pavese');
192INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Addie', 'Tschierasche');
193
194INSERT INTO Publishers (ID, Name) VALUES (NULL, 'Huels-Steuber');
195INSERT INTO Publishers (ID, Name) VALUES (NULL, 'Ondricka and Sons');
196INSERT INTO Publishers (ID, Name) VALUES (NULL, 'Bode, Morissette and Kertzmann');
197INSERT INTO Publishers (ID, Name) VALUES (NULL, 'Bogan, Auer and Kutch');
198INSERT INTO Publishers (ID, Name) VALUES (NULL, 'Murphy, Rolfson and Okuneva');
199INSERT INTO Publishers (ID, Name) VALUES (NULL, 'Wiza, Mertz and Huels');
200INSERT INTO Publishers (ID, Name) VALUES (NULL, 'Dibbert-Haag');
201INSERT INTO Publishers (ID, Name) VALUES (NULL, 'Rippin-Koelpin');
202INSERT INTO Publishers (ID, Name) VALUES (NULL, 'Wehner Group');
203INSERT INTO Publishers (ID, Name) VALUES (NULL, 'Feest LLC');
204
205INSERT INTO Genres (ID, Name) VALUES (NULL, 'Drama');
206INSERT INTO Genres (ID, Name) VALUES (NULL, 'Action');
207INSERT INTO Genres (ID, Name) VALUES (NULL, 'Action|Drama');
208INSERT INTO Genres (ID, Name) VALUES (NULL, 'Adventure|Fantasy');
209INSERT INTO Genres (ID, Name) VALUES (NULL, 'Drama|Mystery|Thriller');
210INSERT INTO Genres (ID, Name) VALUES (NULL, 'Comedy');
211INSERT INTO Genres (ID, Name) VALUES (NULL, 'Comedy');
212INSERT INTO Genres (ID, Name) VALUES (NULL, 'Adventure');
213INSERT INTO Genres (ID, Name) VALUES (NULL, 'Comedy|Crime|Drama|Mystery');
214INSERT INTO Genres (ID, Name) VALUES (NULL, 'Comedy');
215
216INSERT INTO Branches (ID, Name, Address, PhoneNumber, OpenTime, CloseTime) VALUES (NULL, 'Trailing Windmills', '95912 Kropf Center', '6977585824', '8:20', '19:21');
217INSERT INTO Branches (ID, Name, Address, PhoneNumber, OpenTime, CloseTime) VALUES (NULL, 'Softleaf Sedge', '3 Derek Street', '8751849441', '9:25', '19:59');
218INSERT INTO Branches (ID, Name, Address, PhoneNumber, OpenTime, CloseTime) VALUES (NULL, 'Coastal Serviceberry', '2993 Namekagon Pass', '8308464841', '9:14', '19:21');
219INSERT INTO Branches (ID, Name, Address, PhoneNumber, OpenTime, CloseTime) VALUES (NULL, 'Cup Lichen', '479 Merrick Junction', '4805405334', '8:40', '17:19');
220INSERT INTO Branches (ID, Name, Address, PhoneNumber, OpenTime, CloseTime) VALUES (NULL, 'Spanish Thyme', '227 Lien Circle', '5832028409', '9:51', '19:08');
221INSERT INTO Branches (ID, Name, Address, PhoneNumber, OpenTime, CloseTime) VALUES (NULL, 'Redmahogany', '35 Arrowood Point', '6645419434', '8:19', '19:07');
222INSERT INTO Branches (ID, Name, Address, PhoneNumber, OpenTime, CloseTime) VALUES (NULL, 'Corzo''s Wild Petunia', '811 Vermont Road', '7047380890', '8:38', '18:17');
223INSERT INTO Branches (ID, Name, Address, PhoneNumber, OpenTime, CloseTime) VALUES (NULL, 'Flowers'' Beardtongue', '8572 Ohio Pass', '2433824110', '9:56', '18:58');
224INSERT INTO Branches (ID, Name, Address, PhoneNumber, OpenTime, CloseTime) VALUES (NULL, 'California Black Oak', '82 Mandrake Park', '7062696094', '8:43', '18:24');
225INSERT INTO Branches (ID, Name, Address, PhoneNumber, OpenTime, CloseTime) VALUES (NULL, 'Clamshell Orchid', '7918 Hermina Plaza', '8041386382', '8:17', '17:53');
226 
227INSERT INTO Positions (ID, PositionName, Salary) VALUES (NULL, 'Software Test Engineer I', '1685.52');
228INSERT INTO Positions (ID, PositionName, Salary) VALUES (NULL, 'VP Quality Control', '3101.35');
229INSERT INTO Positions (ID, PositionName, Salary) VALUES (NULL, 'Community Outreach Specialist', '5986.27');
230INSERT INTO Positions (ID, PositionName, Salary) VALUES (NULL, 'Product Engineer', '3800.84');
231INSERT INTO Positions (ID, PositionName, Salary) VALUES (NULL, 'Assistant Professor', '2702.78');
232INSERT INTO Positions (ID, PositionName, Salary) VALUES (NULL, 'Cost Accountant', '3618.35');
233INSERT INTO Positions (ID, PositionName, Salary) VALUES (NULL, 'Health Coach III', '3604.60');
234INSERT INTO Positions (ID, PositionName, Salary) VALUES (NULL, 'VP Marketing', '3178.73');
235INSERT INTO Positions (ID, PositionName, Salary) VALUES (NULL, 'Executive Secretary', '6300.33');
236INSERT INTO Positions (ID, PositionName, Salary) VALUES (NULL, 'Research Associate', '5712.31');
237
238INSERT INTO Employees (ID, FirstName, LastName, Branches_id, Positions_id) VALUES (NULL, 'Reube', 'Bertenshaw', 1, 2);
239INSERT INTO Employees (ID, FirstName, LastName, Branches_id, Positions_id) VALUES (NULL, 'Joannes', 'Riditch', 1, 4);
240INSERT INTO Employees (ID, FirstName, LastName, Branches_id, Positions_id) VALUES (NULL, 'Clea', 'Caddell', 1, 9);
241INSERT INTO Employees (ID, FirstName, LastName, Branches_id, Positions_id) VALUES (NULL, 'Loralee', 'Phinnis', 2, 5);
242INSERT INTO Employees (ID, FirstName, LastName, Branches_id, Positions_id) VALUES (NULL, 'Natasha', 'Crawforth', 3, 5);
243INSERT INTO Employees (ID, FirstName, LastName, Branches_id, Positions_id) VALUES (NULL, 'Engracia', 'Mordin',4 ,5);
244INSERT INTO Employees (ID, FirstName, LastName, Branches_id, Positions_id) VALUES (NULL, 'Vassily', 'Skillett', 5, 7);
245INSERT INTO Employees (ID, FirstName, LastName, Branches_id, Positions_id) VALUES (NULL, 'Bianca', 'Justun', 6, 8);
246INSERT INTO Employees (ID, FirstName, LastName, Branches_id, Positions_id) VALUES (NULL, 'Peyton', 'Riccelli', 8, 1);
247INSERT INTO Employees (ID, FirstName, LastName, Branches_id, Positions_id) VALUES (NULL, 'Cly', 'Owbrick', 10, 6);
248
249INSERT INTO Books (ID, Title, YearOfRelease, Publishers_id, Branches_id) VALUES (NULL, 'Concussion', 1995, 3, 1);
250INSERT INTO Books (ID, Title, YearOfRelease, Publishers_id, Branches_id) VALUES (NULL, 'Human Trafficking', 2012, 6, 1);
251INSERT INTO Books (ID, Title, YearOfRelease, Publishers_id, Branches_id) VALUES (NULL, 'Bonjour tristesse', 1992, 3, 1);
252INSERT INTO Books (ID, Title, YearOfRelease, Publishers_id, Branches_id) VALUES (NULL, 'City Island', 1998, 2, 2);
253INSERT INTO Books (ID, Title, YearOfRelease, Publishers_id, Branches_id) VALUES (NULL, 'Sir Arne''s Treasure', 2002, 2, 6);
254INSERT INTO Books (ID, Title, YearOfRelease, Publishers_id, Branches_id) VALUES (NULL, 'Thomas Crown Affair, The', 1991, 5, 6);
255INSERT INTO Books (ID, Title, YearOfRelease, Publishers_id, Branches_id) VALUES (NULL, 'Territories ', 2012, 4, 7);
256INSERT INTO Books (ID, Title, YearOfRelease, Publishers_id, Branches_id) VALUES (NULL, 'Larry the Cable Guy: Health Inspector', 1998, 4, 8);
257INSERT INTO Books (ID, Title, YearOfRelease, Publishers_id, Branches_id) VALUES (NULL, 'Day of the Falcon', 2001, 4, 9);
258INSERT INTO Books (ID, Title, YearOfRelease, Publishers_id, Branches_id) VALUES (NULL, 'Caesar (Julius Caesar)', 1986, 6, 3);
259
260INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'kRJH-CzZi-J4aa', 'Bendel', '1723115335');
261INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'zTb1-kvE8-zOSY', 'McEllen', '4238682589');
262INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, '9DJm-cdAy-y8Bk', 'Kelledy', '5589959021');
263INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, '3EFt-Igg8-Xim5', 'Scrowby', '2969494584');
264INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'Lnj0-ojUw-rFI6', 'Baggaley', '2382664387');
265INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'DAF0-XhEk-KSW8', 'Retallack', '8416886734');
266INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'w4Jc-gojI-B54j', 'Kigelman', '7859945478');
267INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'Hd0I-lvyG-CBe9', 'Affuso', '8181971986');
268INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'fp8F-xLEA-3ARH', 'Tabbernor', '5025235234');
269INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'e7PC-KweV-jkZy', 'Calcraft', '6459852554');
270INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'FmB3-5QRu-eIBr', 'Janiak', '4081744606');
271INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, '6kEq-ZmlU-gopQ', 'Kimberley', '2656671222');
272INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'G7zm-EB5G-kjrW', 'Rutigliano', '3047256848');
273INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'AbdT-CaJZ-0j5s', 'Kleeborn', '8157272467');
274INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'nmwG-MQKD-37ML', 'Burnapp', '5192217949');
275INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, '0GdF-thZ7-TeSQ', 'Blagden', '5501849359');
276INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'WOCO-DT18-5yhD', 'Caulier', '4222965267');
277INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, '6vKo-7vjA-2d4r', 'Perren', '2933766466');
278INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'NDtl-FwLh-pcrT', 'Brugden', '7555154982');
279INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, '5Opp-BACv-NVG2', 'Foat', '9602934014');
280INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, '0udC-bLRE-npDy', 'Halladey', '5845724399');
281INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'aLBl-971J-AJ2q', 'Dietsche', '7049331297');
282INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'UueW-PZW2-SQpB', 'De Bischof', '6348152598');
283INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'LUNo-sOUp-yWPF', 'Hegges', '1765496197');
284INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, '2n8y-GqQb-udEC', 'Isenor', '2147434845');
285INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, '3I7B-sYf5-CP7X', 'Rickert', '2638160105');
286INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'tPvT-fUQU-nzkv', 'Bessey', '6867330055');
287INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'r3mt-H1hs-a0Ji', 'Ismirnioglou', '8808013610');
288INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, '0uz3-53mL-Jefv', 'Durrans', '2961741616');
289INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'QR3M-QFlk-CGt1', 'Aldgate', '2392374465');
290INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'nJDG-0bRK-8DyC', 'Catcherside', '2565830742');
291INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'brPw-6ShG-Eis5', 'Belasco', '6013413554');
292INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 's2en-hi9Z-r7Yl', 'Clawley', '9575631113');
293INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'k41o-qKgQ-L8Qi', 'Whereat', '1934727371');
294INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'qJVv-xsDC-xgwa', 'Bertholin', '6536349578');
295INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'zSWv-ZsoR-sfqn', 'Gypps', '8737099761');
296INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'n3ld-LBGO-5LB6', 'Dericot', '7332328816');
297INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'q8vO-nRze-GIfx', 'Crimin', '9583617056');
298INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'eDkc-8dHP-Cioa', 'Pendrigh', '9853280818');
299INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'K9aN-dsbX-EA6V', 'Peasey', '9716206115');
300INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'HNGb-Rbjr-oHYG', 'Grishankov', '1784309857');
301INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, '7tMo-KUlf-BRlS', 'Brearley', '1105250054');
302INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'dXMJ-leGP-mg01', 'Whiteoak', '6997653858');
303INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'gY1M-lxMz-VOXZ', 'Sworder', '6606458688');
304INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'MHCl-VL6m-zvyV', 'Lomath', '5244750576');
305INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'Ruzy-m7Of-D1f3', 'Shore', '1098033036');
306INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'OGc9-Ywus-Rgke', 'Burree', '2103919293');
307INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'sPWx-xpl8-TbrN', 'Lenard', '5734700231');
308INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'SYVc-HrUj-3XDd', 'Maffeo', '5058614244');
309INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, '7ZnK-DV4Y-n806', 'Alldis', '3128625724');
310
311INSERT INTO authors_books (authors_id, books_id) VALUES (2, 8);
312INSERT INTO authors_books (authors_id, books_id) VALUES (10, 2);
313INSERT INTO authors_books (authors_id, books_id) VALUES (9, 7);
314INSERT INTO authors_books (authors_id, books_id) VALUES (6, 6);
315INSERT INTO authors_books (authors_id, books_id) VALUES (9, 8);
316INSERT INTO authors_books (authors_id, books_id) VALUES (5, 10);
317INSERT INTO authors_books (authors_id, books_id) VALUES (10, 5);
318INSERT INTO authors_books (authors_id, books_id) VALUES (5, 3);
319INSERT INTO authors_books (authors_id, books_id) VALUES (3, 3);
320INSERT INTO authors_books (authors_id, books_id) VALUES (10, 1);
321INSERT INTO authors_books (authors_id, books_id) VALUES (5, 9);
322INSERT INTO authors_books (authors_id, books_id) VALUES (8, 10);
323INSERT INTO authors_books (authors_id, books_id) VALUES (5, 4);
324INSERT INTO authors_books (authors_id, books_id) VALUES (1, 5);
325INSERT INTO authors_books (authors_id, books_id) VALUES (1, 2);
326
327INSERT INTO genres_books (genres_id, books_id) VALUES (7, 10);
328INSERT INTO genres_books (genres_id, books_id) VALUES (8, 7);
329INSERT INTO genres_books (genres_id, books_id) VALUES (10, 7);
330INSERT INTO genres_books (genres_id, books_id) VALUES (9, 4);
331INSERT INTO genres_books (genres_id, books_id) VALUES (6, 8);
332INSERT INTO genres_books (genres_id, books_id) VALUES (9, 9);
333INSERT INTO genres_books (genres_id, books_id) VALUES (9, 1);
334INSERT INTO genres_books (genres_id, books_id) VALUES (4, 7);
335INSERT INTO genres_books (genres_id, books_id) VALUES (1, 3);
336INSERT INTO genres_books (genres_id, books_id) VALUES (1, 6);
337INSERT INTO genres_books (genres_id, books_id) VALUES (1, 5);
338INSERT INTO genres_books (genres_id, books_id) VALUES (4, 2);
339
340insert into reservations (id, start_date, end_date, reservation_status_id, library_cards_id, books_id) values (null, '2019-06-05', '2019-07-05', 1, 3, 5);
341insert into reservations (id, start_date, end_date, reservation_status_id, library_cards_id, books_id) values (null, '2019-08-29', '2019-09-29', 3, 1, 1);
342insert into reservations (id, start_date, end_date, reservation_status_id, library_cards_id, books_id) values (null, '2019-08-08', '2019-09-08', 1, 3, 4);
343insert into reservations (id, start_date, end_date, reservation_status_id, library_cards_id, books_id) values (null, '2019-08-21', '2019-09-21', 2, 1, 3);
344insert into reservations (id, start_date, end_date, reservation_status_id, library_cards_id, books_id) values (null, '2019-07-02', '2019-08-02', 3, 2, 6);
345insert into reservations (id, start_date, end_date, reservation_status_id, library_cards_id, books_id) values (null, '2019-10-05', '2019-11-05', 3, 3, 3);
346insert into reservations (id, start_date, end_date, reservation_status_id, library_cards_id, books_id) values (null, '2019-05-04', '2019-06-04', 1, 3, 6);
347insert into reservations (id, start_date, end_date, reservation_status_id, library_cards_id, books_id) values (null, '2019-07-16', '2019-08-16', 1, 1, 2);
348insert into reservations (id, start_date, end_date, reservation_status_id, library_cards_id, books_id) values (null, '2019-05-02', '2019-06-02', 2, 1, 7);
349insert into reservations (id, start_date, end_date, reservation_status_id, library_cards_id, books_id) values (null, '2019-09-11', '2019-10-11', 3, 3, 8);
350insert into reservations (id, start_date, end_date, reservation_status_id, library_cards_id, books_id) values (null, '2019-11-10', '2019-12-10', 3, 1, 3);
351insert into reservations (id, start_date, end_date, reservation_status_id, library_cards_id, books_id) values (null, '2019-08-23', '2019-09-23', 2, 2, 9);
352insert into reservations (id, start_date, end_date, reservation_status_id, library_cards_id, books_id) values (null, '2019-10-08', '2019-11-08', 3, 3, 4);
353insert into reservations (id, start_date, end_date, reservation_status_id, library_cards_id, books_id) values (null, '2019-10-26', '2019-11-26', 2, 1, 2);
354insert into reservations (id, start_date, end_date, reservation_status_id, library_cards_id, books_id) values (null, '2019-05-22', '2019-06-22', 1, 1, 3);
355
356insert into rents (id, start_date, end_date, rent_status_id, library_cards_id, books_id) values (null, '2019-07-01', '2019-08-01', 3, 2, 6);
357insert into rents (id, start_date, end_date, rent_status_id, library_cards_id, books_id) values (null, '2019-08-04', '2019-09-04', 3, 2, 8);
358insert into rents (id, start_date, end_date, rent_status_id, library_cards_id, books_id) values (null, '2019-07-01', '2019-08-01', 2, 1, 10);
359insert into rents (id, start_date, end_date, rent_status_id, library_cards_id, books_id) values (null, '2019-05-11', '2019-06-11', 3, 1, 5);
360insert into rents (id, start_date, end_date, rent_status_id, library_cards_id, books_id) values (null, '2019-05-17', '2019-06-17', 1, 3, 6);
361insert into rents (id, start_date, end_date, rent_status_id, library_cards_id, books_id) values (null, '2019-03-06', '2019-04-06', 3, 3, 4);
362insert into rents (id, start_date, end_date, rent_status_id, library_cards_id, books_id) values (null, '2019-05-13', '2019-06-13', 3, 1, 6);
363insert into rents (id, start_date, end_date, rent_status_id, library_cards_id, books_id) values (null, '2019-07-16', '2019-08-16', 2, 1, 2);
364insert into rents (id, start_date, end_date, rent_status_id, library_cards_id, books_id) values (null, '2019-06-30', '2019-07-30', 1, 2, 1);
365insert into rents (id, start_date, end_date, rent_status_id, library_cards_id, books_id) values (null, '2019-06-29', '2019-07-29', 2, 3, 9);
366insert into rents (id, start_date, end_date, rent_status_id, library_cards_id, books_id) values (null, '2019-04-05', '2019-05-05', 1, 2, 9);
367insert into rents (id, start_date, end_date, rent_status_id, library_cards_id, books_id) values (null, '2019-04-29', '2019-05-29', 2, 1, 9);
368insert into rents (id, start_date, end_date, rent_status_id, library_cards_id, books_id) values (null, '2019-05-06', '2019-06-06', 1, 2, 5);
369insert into rents (id, start_date, end_date, rent_status_id, library_cards_id, books_id) values (null, '2019-07-30', '2019-08-30', 3, 3, 3);
370insert into rents (id, start_date, end_date, rent_status_id, library_cards_id, books_id) values (null, '2019-07-05', '2019-08-05', 1, 3, 9);
371
372CREATE OR REPLACE VIEW Catalogue_Trailing_WIndmills AS
373SELECT books.Title, books.YearOfRelease, GROUP_CONCAT(authors.first_name,' ', authors.last_name) AS Authors, publishers.Name AS Publisher, GROUP_CONCAT(genres.Name) AS Genres FROM Books
374JOIN authors_books ON authors_books.books_id = books.id
375JOIN authors ON authors_books.authors_id  = authors.id 
376JOIN publishers ON books.publishers_id = publishers.id
377JOIN genres_books ON genres_books.books_id = books.id
378JOIN genres ON genres_books.genres_id = genres.id
379WHERE books.branches_id = 1
380GROUP BY books.Title;
381
382CREATE OR REPLACE VIEW Catalogue_Softleaf_Sedge AS
383SELECT books.Title, books.YearOfRelease, GROUP_CONCAT(authors.first_name,' ', authors.last_name) AS Authors, publishers.Name AS Publisher, GROUP_CONCAT(genres.Name) AS Genres FROM Books
384JOIN authors_books ON authors_books.books_id = books.id
385JOIN authors ON authors_books.authors_id  = authors.id 
386JOIN publishers ON books.publishers_id = publishers.id
387JOIN genres_books ON genres_books.books_id = books.id
388JOIN genres ON genres_books.genres_id = genres.id
389WHERE books.branches_id = 2
390GROUP BY books.Title;
391
392CREATE OR REPLACE VIEW Catalogue_Coastal_Serviceberry AS
393SELECT books.Title, books.YearOfRelease, GROUP_CONCAT(authors.first_name,' ', authors.last_name) AS Authors, publishers.Name AS Publisher, GROUP_CONCAT(genres.Name) AS Genres FROM Books
394JOIN authors_books ON authors_books.books_id = books.id
395JOIN authors ON authors_books.authors_id  = authors.id 
396JOIN publishers ON books.publishers_id = publishers.id
397JOIN genres_books ON genres_books.books_id = books.id
398JOIN genres ON genres_books.genres_id = genres.id
399WHERE books.branches_id = 3
400GROUP BY books.Title;
401
402CREATE OR REPLACE VIEW Catalogue_Cup_Lichen AS
403SELECT books.Title, books.YearOfRelease, GROUP_CONCAT(authors.first_name,' ', authors.last_name) AS Authors, publishers.Name AS Publisher, GROUP_CONCAT(genres.Name) AS Genres FROM Books
404JOIN authors_books ON authors_books.books_id = books.id
405JOIN authors ON authors_books.authors_id  = authors.id 
406JOIN publishers ON books.publishers_id = publishers.id
407JOIN genres_books ON genres_books.books_id = books.id
408JOIN genres ON genres_books.genres_id = genres.id
409WHERE books.branches_id = 4
410GROUP BY books.Title;
411
412CREATE OR REPLACE VIEW Catalogue_Spanish_Thyme AS
413SELECT books.Title, books.YearOfRelease, GROUP_CONCAT(authors.first_name,' ', authors.last_name) AS Authors, publishers.Name AS Publisher, GROUP_CONCAT(genres.Name) AS Genres FROM Books
414JOIN authors_books ON authors_books.books_id = books.id
415JOIN authors ON authors_books.authors_id  = authors.id 
416JOIN publishers ON books.publishers_id = publishers.id
417JOIN genres_books ON genres_books.books_id = books.id
418JOIN genres ON genres_books.genres_id = genres.id
419WHERE books.branches_id = 5
420GROUP BY books.Title;
421
422CREATE OR REPLACE VIEW Catalogue_Redmahogany AS
423SELECT books.Title, books.YearOfRelease, GROUP_CONCAT(authors.first_name,' ', authors.last_name) AS Authors, publishers.Name AS Publisher, GROUP_CONCAT(genres.Name) AS Genres FROM Books
424JOIN authors_books ON authors_books.books_id = books.id
425JOIN authors ON authors_books.authors_id  = authors.id 
426JOIN publishers ON books.publishers_id = publishers.id
427JOIN genres_books ON genres_books.books_id = books.id
428JOIN genres ON genres_books.genres_id = genres.id
429WHERE books.branches_id = 6
430GROUP BY books.Title;
431
432CREATE OR REPLACE VIEW Catalogue_Corzos_Wild AS
433SELECT books.Title, books.YearOfRelease, GROUP_CONCAT(authors.first_name,' ', authors.last_name) AS Authors, publishers.Name AS Publisher, GROUP_CONCAT(genres.Name) AS Genres FROM Books
434JOIN authors_books ON authors_books.books_id = books.id
435JOIN authors ON authors_books.authors_id  = authors.id 
436JOIN publishers ON books.publishers_id = publishers.id
437JOIN genres_books ON genres_books.books_id = books.id
438JOIN genres ON genres_books.genres_id = genres.id
439WHERE books.branches_id = 7
440GROUP BY books.Title;
441
442CREATE OR REPLACE VIEW Catalogue_Flowers_Beardtongue AS
443SELECT books.Title, books.YearOfRelease, GROUP_CONCAT(authors.first_name,' ', authors.last_name) AS Authors, publishers.Name AS Publisher, GROUP_CONCAT(genres.Name) AS Genres FROM Books
444JOIN authors_books ON authors_books.books_id = books.id
445JOIN authors ON authors_books.authors_id  = authors.id 
446JOIN publishers ON books.publishers_id = publishers.id
447JOIN genres_books ON genres_books.books_id = books.id
448JOIN genres ON genres_books.genres_id = genres.id
449WHERE books.branches_id = 8
450GROUP BY books.Title;
451
452CREATE OR REPLACE VIEW Catalogue_California_Black AS
453SELECT books.Title, books.YearOfRelease, GROUP_CONCAT(authors.first_name,' ', authors.last_name) AS Authors, publishers.Name AS Publisher, GROUP_CONCAT(genres.Name) AS Genres FROM Books
454JOIN authors_books ON authors_books.books_id = books.id
455JOIN authors ON authors_books.authors_id  = authors.id 
456JOIN publishers ON books.publishers_id = publishers.id
457JOIN genres_books ON genres_books.books_id = books.id
458JOIN genres ON genres_books.genres_id = genres.id
459WHERE books.branches_id = 9
460GROUP BY books.Title;
461
462CREATE OR REPLACE VIEW Catalogue_Clamshell_Orchid AS
463SELECT books.Title, books.YearOfRelease, GROUP_CONCAT(authors.first_name,' ', authors.last_name) AS Authors, publishers.Name AS Publisher, GROUP_CONCAT(genres.Name) AS Genres FROM Books
464JOIN authors_books ON authors_books.books_id = books.id
465JOIN authors ON authors_books.authors_id  = authors.id 
466JOIN publishers ON books.publishers_id = publishers.id
467JOIN genres_books ON genres_books.books_id = books.id
468JOIN genres ON genres_books.genres_id = genres.id
469WHERE books.branches_id = 10
470GROUP BY books.Title;
471
472DROP FUNCTION IF EXISTS employeeCounter;
473DELIMITER $$
474CREATE FUNCTION employeeCounter(position INT)
475RETURNS INT DETERMINISTIC
476RETURN (SELECT COUNT(employees.Positions_ID) as Number_of_employees
477        FROM employees
478        WHERE employees.Positions_ID = position
479        )
480$$
481DELIMITER ;
482SELECT employeeCounter(5) as 'Number of employees' ;