7rYYy2yA

1SELECT * FROM AUTHORS;
2SELECT * FROM RESERVATION_STATUS;
3SELECT * FROM RENT_STATUS;
4SELECT * FROM EMPLOYEES;
5SELECT * FROM POSITIONS;
6SELECT * FROM BRANCHES;
7SELECT * FROM GENRES;
8SELECT * FROM BOOKS;
9SELECT * FROM PUBLISHERS;
10
11SELECT * FROM LIBRARY_CARDS;
12SELECT * FROM AUTHORS_BOOKS;
13SELECT * FROM GENRES_BOOKS;
14SELECT * FROM RESERVATIONS;
15SELECT * FROM RENTS;
16--Stworz baze
17DROP DATABASE IF EXISTS projekt_jg_mk;
18CREATE DATABASE projekt_jg_mk;
19USE projekt_jg_mk;
20
21--Tworzenie tabel
22CREATE TABLE Reservation_Status ( 
23    ID INT AUTO_INCREMENT, 
24    Name VARCHAR(4000) NOT NULL, 
25    PRIMARY KEY (ID) 
26);
27
28CREATE TABLE Rent_Status ( 
29    ID INT AUTO_INCREMENT, 
30    Name VARCHAR(4000), 
31    PRIMARY KEY(ID) 
32);
33
34CREATE TABLE Library_Cards ( 
35    ID INT AUTO_INCREMENT, 
36    Card_Number VARCHAR(30), 
37    Owner_Last_Name VARCHAR(50), 
38    Owner_Phone_Number INT, 
39    PRIMARY KEY(ID) 
40);
41
42CREATE TABLE Authors ( 
43    ID INT AUTO_INCREMENT, 
44    First_Name VARCHAR(4000), 
45    Last_Name VARCHAR(4000), 
46    PRIMARY KEY(ID) 
47);
48
49CREATE TABLE Genres(
50    ID int NOT NULL AUTO_INCREMENT,
51    Name varchar(4000),
52    PRIMARY KEY (ID)
53);
54 
55CREATE TABLE Branches(
56    ID int NOT NULL AUTO_INCREMENT,
57    Name varchar(4000),
58    Address varchar(4000),
59    PhoneNumber bigint,
60    OpenTime time,
61    CloseTime time,
62    PRIMARY KEY (ID)
63);
64 
65CREATE TABLE Publishers(
66    ID int NOT NULL AUTO_INCREMENT,
67    Name varchar(4000),
68    PRIMARY KEY (ID)
69);
70 
71CREATE TABLE Books(
72    ID int NOT NULL AUTO_INCREMENT,
73    Title varchar(4000),
74    YearOfRelease int,
75    Publishers_ID int,
76    Branches_ID int,
77    PRIMARY KEY (ID),
78    FOREIGN KEY(branches_id)
79        REFERENCES Branches(ID),
80    FOREIGN KEY(publishers_id)
81        REFERENCES Publishers(ID)
82);
83 
84CREATE TABLE genres_books(
85    Genres_ID int,
86    Books_ID int,
87    PRIMARY KEY(Genres_ID, Books_ID),
88    FOREIGN KEY(genres_id)
89        REFERENCES Genres(ID),
90    FOREIGN KEY(books_id)
91        REFERENCES Books(ID)
92);
93 
94CREATE TABLE Positions(
95    ID int NOT NULL AUTO_INCREMENT,
96    PositionName varchar(4000),
97    Salary float,
98    PRIMARY KEY (ID)
99);
100 
101CREATE TABLE Employees(
102    ID int NOT NULL AUTO_INCREMENT,
103    FirstName varchar(4000),
104    LastName varchar(4000),
105    Branches_ID int,
106    Positions_ID int,
107    PRIMARY KEY (ID),
108    FOREIGN KEY(branches_id)
109        REFERENCES Branches(ID),
110    FOREIGN KEY(positions_id)
111        REFERENCES Positions(ID)
112);
113
114CREATE TABLE authors_books ( 
115    authors_id INT NOT NULL, 
116    books_id INT NOT NULL,
117    FOREIGN KEY(authors_id) REFERENCES Authors(ID), 
118    FOREIGN KEY(books_id) REFERENCES Books(ID) 
119);
120
121CREATE TABLE Reservations ( 
122    ID INT AUTO_INCREMENT, 
123    Start_Date DATE, 
124    End_Date DATE, 
125    reservation_status_id INT NOT NULL, 
126    library_card_id INT NOT NULL, 
127    books_id INT NOT NULL, 
128    PRIMARY KEY(ID), 
129    FOREIGN KEY(reservation_status_id) REFERENCES reservation_status(ID), 
130    FOREIGN KEY(library_card_id) REFERENCES Library_cards(ID), 
131    FOREIGN KEY(books_id) REFERENCES books(ID) 
132);
133
134CREATE TABLE Rents ( 
135    ID INT AUTO_INCREMENT, 
136    Start_Date DATE, End_Date DATE, 
137    rent_status_id INT NOT NULL, 
138    library_cards_id INT NOT NULL, 
139    books_id INT NOT NULL, 
140    PRIMARY KEY(ID), 
141    FOREIGN KEY(rent_status_id) REFERENCES Rent_Status(ID), 
142    FOREIGN KEY(library_cards_id) REFERENCES Library_Cards(ID), 
143    FOREIGN KEY(books_id) REFERENCES Books(ID) 
144);
145
146--uzupelnij tabelę reservation_status
147INSERT INTO Reservation_Status(ID,Name) VALUES (NULL,"Waiting");
148INSERT INTO Reservation_Status(ID,Name) VALUES (NULL,"Avaiable");
149INSERT INTO Reservation_Status(ID,Name) VALUES (NULL,"Completed");
150--uzupelnij tabelę rent_status 
151INSERT INTO rent_status(ID,Name) VALUES (NULL,"rented");
152INSERT INTO rent_status(ID,Name) VALUES (NULL,"returned");
153INSERT INTO rent_status(ID,Name) VALUES (NULL,"not returned");
154--Uzupelnij tabelę authors
155INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Adam', 'Mickiewicz');
156INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Jan', 'Kochanowski');
157INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Juliusz', 'Slowacki');
158INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Stephen', 'King');
159INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Cortney', 'Brokenbrow');
160INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Ianthe', 'Chettle');
161INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Hershel', 'Druery');
162INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Xaviera', 'Parres');
163INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Rupert', 'Friend');
164INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Joanne', 'Mailey');
165INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Levon', 'Gudger');
166INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Far', 'Walcar');
167INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Noll', 'Hassen');
168INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Olwen', 'Shotton');
169INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Hynda', 'Madison');
170INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Tatum', 'Flippen');
171INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Hildegarde', 'Suscens');
172INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Clem', 'Danbrook');
173INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Adriana', 'Dubois');
174INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Shelden', 'Davydochkin');
175INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Celine', 'Colloff');
176INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Hillel', 'McDonough');
177INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Dee dee', 'Camier');
178INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Zach', 'Guidotti');
179INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Rolando', 'Silcocks');
180INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Jenilee', 'Friedank');
181INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Ezechiel', 'Origin');
182INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Imojean', 'de Banke');
183INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Fraze', 'Alkin');
184INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Darwin', 'Jacox');
185INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Hatty', 'Dafydd');
186INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Damita', 'Knight');
187INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'April', 'Hollyman');
188INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Micky', 'Froude');
189INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Diarmid', 'Herculson');
190INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Rhodia', 'Sandland');
191INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Petey', 'Jarmyn');
192INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Duncan', 'Anthon');
193INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Launce', 'Nutbeem');
194INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Saxe', 'Blumire');
195INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Ursala', 'Collumbell');
196INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Marge', 'Martignon');
197INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Skipper', 'Tatlowe');
198INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Collette', 'Ilbert');
199INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Norine', 'Cappleman');
200INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Elonore', 'Doul');
201INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Zaneta', 'Von Oertzen');
202INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Idelle', 'Bispham');
203INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Stephenie', 'Marsie');
204INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Nadean', 'Noraway');
205INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Ofelia', 'Worms');
206INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Gabriellia', 'Tallis');
207INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Leigh', 'Pavese');
208INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Addie', 'Tschierasche');
209--Uzupelnij tabele publishers
210INSERT INTO Publishers (ID, Name) VALUES (NULL, 'Huels-Steuber');
211INSERT INTO Publishers (ID, Name) VALUES (NULL, 'Ondricka and Sons');
212INSERT INTO Publishers (ID, Name) VALUES (NULL, 'Bode, Morissette and Kertzmann');
213INSERT INTO Publishers (ID, Name) VALUES (NULL, 'Bogan, Auer and Kutch');
214INSERT INTO Publishers (ID, Name) VALUES (NULL, 'Murphy, Rolfson and Okuneva');
215INSERT INTO Publishers (ID, Name) VALUES (NULL, 'Wiza, Mertz and Huels');
216INSERT INTO Publishers (ID, Name) VALUES (NULL, 'Dibbert-Haag');
217INSERT INTO Publishers (ID, Name) VALUES (NULL, 'Rippin-Koelpin');
218INSERT INTO Publishers (ID, Name) VALUES (NULL, 'Wehner Group');
219INSERT INTO Publishers (ID, Name) VALUES (NULL, 'Feest LLC');
220--Uzupelnij tabele genres
221INSERT INTO Genres (ID, Name) VALUES (NULL, 'Drama');
222INSERT INTO Genres (ID, Name) VALUES (NULL, 'Action');
223INSERT INTO Genres (ID, Name) VALUES (NULL, 'Action|Drama');
224INSERT INTO Genres (ID, Name) VALUES (NULL, 'Adventure|Fantasy');
225INSERT INTO Genres (ID, Name) VALUES (NULL, 'Drama|Mystery|Thriller');
226INSERT INTO Genres (ID, Name) VALUES (NULL, 'Comedy');
227INSERT INTO Genres (ID, Name) VALUES (NULL, 'Comedy');
228INSERT INTO Genres (ID, Name) VALUES (NULL, 'Adventure');
229INSERT INTO Genres (ID, Name) VALUES (NULL, 'Comedy|Crime|Drama|Mystery');
230INSERT INTO Genres (ID, Name) VALUES (NULL, 'Comedy');
231--Uzupelnij tabele branches
232INSERT INTO Branches (ID, Name, Address, PhoneNumber, OpenTime, CloseTime) VALUES (NULL, 'Trailing Windmills', '95912 Kropf Center', '6977585824', '8:20', '19:21');
233INSERT INTO Branches (ID, Name, Address, PhoneNumber, OpenTime, CloseTime) VALUES (NULL, 'Softleaf Sedge', '3 Derek Street', '8751849441', '9:25', '19:59');
234INSERT INTO Branches (ID, Name, Address, PhoneNumber, OpenTime, CloseTime) VALUES (NULL, 'Coastal Serviceberry', '2993 Namekagon Pass', '8308464841', '9:14', '19:21');
235INSERT INTO Branches (ID, Name, Address, PhoneNumber, OpenTime, CloseTime) VALUES (NULL, 'Cup Lichen', '479 Merrick Junction', '4805405334', '8:40', '17:19');
236INSERT INTO Branches (ID, Name, Address, PhoneNumber, OpenTime, CloseTime) VALUES (NULL, 'Spanish Thyme', '227 Lien Circle', '5832028409', '9:51', '19:08');
237INSERT INTO Branches (ID, Name, Address, PhoneNumber, OpenTime, CloseTime) VALUES (NULL, 'Redmahogany', '35 Arrowood Point', '6645419434', '8:19', '19:07');
238INSERT INTO Branches (ID, Name, Address, PhoneNumber, OpenTime, CloseTime) VALUES (NULL, 'Corzo''s Wild Petunia', '811 Vermont Road', '7047380890', '8:38', '18:17');
239INSERT INTO Branches (ID, Name, Address, PhoneNumber, OpenTime, CloseTime) VALUES (NULL, 'Flowers'' Beardtongue', '8572 Ohio Pass', '2433824110', '9:56', '18:58');
240INSERT INTO Branches (ID, Name, Address, PhoneNumber, OpenTime, CloseTime) VALUES (NULL, 'California Black Oak', '82 Mandrake Park', '7062696094', '8:43', '18:24');
241INSERT INTO Branches (ID, Name, Address, PhoneNumber, OpenTime, CloseTime) VALUES (NULL, 'Clamshell Orchid', '7918 Hermina Plaza', '8041386382', '8:17', '17:53');
242 --Uzupelnij tabele positions 
243INSERT INTO Positions (ID, PositionName, Salary) VALUES (NULL, 'Software Test Engineer I', '1685.52');
244INSERT INTO Positions (ID, PositionName, Salary) VALUES (NULL, 'VP Quality Control', '3101.35');
245INSERT INTO Positions (ID, PositionName, Salary) VALUES (NULL, 'Community Outreach Specialist', '5986.27');
246INSERT INTO Positions (ID, PositionName, Salary) VALUES (NULL, 'Product Engineer', '3800.84');
247INSERT INTO Positions (ID, PositionName, Salary) VALUES (NULL, 'Assistant Professor', '2702.78');
248INSERT INTO Positions (ID, PositionName, Salary) VALUES (NULL, 'Cost Accountant', '3618.35');
249INSERT INTO Positions (ID, PositionName, Salary) VALUES (NULL, 'Health Coach III', '3604.60');
250INSERT INTO Positions (ID, PositionName, Salary) VALUES (NULL, 'VP Marketing', '3178.73');
251INSERT INTO Positions (ID, PositionName, Salary) VALUES (NULL, 'Executive Secretary', '6300.33');
252INSERT INTO Positions (ID, PositionName, Salary) VALUES (NULL, 'Research Associate', '5712.31');
253--Uzupelnij tabele employees
254INSERT INTO Employees (ID, FirstName, LastName) VALUES (NULL, 'Reube', 'Bertenshaw');
255INSERT INTO Employees (ID, FirstName, LastName) VALUES (NULL, 'Joannes', 'Riditch');
256INSERT INTO Employees (ID, FirstName, LastName) VALUES (NULL, 'Clea', 'Caddell');
257INSERT INTO Employees (ID, FirstName, LastName) VALUES (NULL, 'Loralee', 'Phinnis');
258INSERT INTO Employees (ID, FirstName, LastName) VALUES (NULL, 'Natasha', 'Crawforth');
259INSERT INTO Employees (ID, FirstName, LastName) VALUES (NULL, 'Engracia', 'Mordin');
260INSERT INTO Employees (ID, FirstName, LastName) VALUES (NULL, 'Vassily', 'Skillett');
261INSERT INTO Employees (ID, FirstName, LastName) VALUES (NULL, 'Bianca', 'Justun');
262INSERT INTO Employees (ID, FirstName, LastName) VALUES (NULL, 'Peyton', 'Riccelli');
263INSERT INTO Employees (ID, FirstName, LastName) VALUES (NULL, 'Cly', 'Owbrick');
264--Uzupelnij tabele books
265INSERT INTO Books (ID, Title, YearOfRelease, Publishers_id, Branches_id) VALUES (NULL, 'Concussion', 1995, 3, 1);
266INSERT INTO Books (ID, Title, YearOfRelease, Publishers_id, Branches_id) VALUES (NULL, 'Human Trafficking', 2012, 6, 1);
267INSERT INTO Books (ID, Title, YearOfRelease, Publishers_id, Branches_id) VALUES (NULL, 'Bonjour tristesse', 1992, 3, 1);
268INSERT INTO Books (ID, Title, YearOfRelease, Publishers_id, Branches_id) VALUES (NULL, 'City Island', 1998, 2, 2);
269INSERT INTO Books (ID, Title, YearOfRelease, Publishers_id, Branches_id) VALUES (NULL, 'Sir Arne''s Treasure', 2002, 2, 6);
270INSERT INTO Books (ID, Title, YearOfRelease, Publishers_id, Branches_id) VALUES (NULL, 'Thomas Crown Affair, The', 1991, 5, 6);
271INSERT INTO Books (ID, Title, YearOfRelease, Publishers_id, Branches_id) VALUES (NULL, 'Territories ', 2012, 4, 7);
272INSERT INTO Books (ID, Title, YearOfRelease, Publishers_id, Branches_id) VALUES (NULL, 'Larry the Cable Guy: Health Inspector', 1998, 4, 8);
273INSERT INTO Books (ID, Title, YearOfRelease, Publishers_id, Branches_id) VALUES (NULL, 'Day of the Falcon', 2001, 4, 9);
274INSERT INTO Books (ID, Title, YearOfRelease, Publishers_id, Branches_id) VALUES (NULL, 'Caesar (Julius Caesar)', 1986, 6, 3);
275--Uzupelnij tabele library_cards