D2fPyHkx

1DROP DATABASE IF EXISTS elly_home_library;
2CREATE DATABASE elly_home_library;
3USE elly_home_library;
4
5CREATE TABLE authors (
6	id INT(5) AUTO_INCREMENT PRIMARY KEY, 
7    first_name VARCHAR(30) NOT NULL, 
8	last_name VARCHAR(30) NOT NULL,
9    birth_year DATE,
10    country VARCHAR(30)
11);
12
13CREATE TABLE rooms (
14	id INT AUTO_INCREMENT PRIMARY KEY,
15    name VARCHAR(30) NOT NULL
16);
17
18CREATE TABLE privileges (
19	id INT AUTO_INCREMENT PRIMARY KEY,
20	name VARCHAR(30) NOT NULL
21);
22
23CREATE TABLE users (
24	id INT AUTO_INCREMENT PRIMARY KEY,
25	first_name VARCHAR(30) NOT NULL,
26	last_name VARCHAR(30),
27	pr_id INT,
28    CONSTRAINT FOREIGN KEY (pr_id) REFERENCES privileges (id)
29);
30
31CREATE TABLE books (
32	id INT AUTO_INCREMENT PRIMARY KEY,
33	name VARCHAR(30) NOT NULL,
34    pub_house VARCHAR(30),
35    genre VARCHAR(30) NOT NULL,
36    pub_year YEAR,
37	pr_id INT,
38    CONSTRAINT FOREIGN KEY (pr_id) REFERENCES privileges (id),
39    room_id INT,
40	CONSTRAINT FOREIGN KEY (room_id) REFERENCES rooms (id) 
41);
42
43CREATE TABLE authors_books (
44	author_id INT NOT NULL,
45	book_id INT NOT NULL,
46    PRIMARY KEY (author_id, book_id), 
47    CONSTRAINT FOREIGN KEY (author_id) REFERENCES authors (id),
48    CONSTRAINT FOREIGN KEY (book_id) REFERENCES books (id)
49);
50
51INSERT INTO privileges (name)
52	VALUES ('master'), 
53		   ('family_member'),
54           ('slave');
55INSERT INTO users (first_name, last_name, pr_id)
56	VALUES ('Elena', 'Antonova', 1),
57		   ('Michael', 'Matveeva', 2),
58           ('Ivy', 'Tabutova', 3);
59INSERT INTO rooms (name)
60	VALUES ('Living Room'),
61		   ('bedroom'),
62           ('kitchen'),
63           ('bathroom');
64INSERT INTO authors (first_name, last_name)
65		VALUES ('George', 'Orwell'),
66               ('Adolf', 'Hitler'),
67			   ('Aldous', 'Huxley'),
68               ('Victor', 'Hugo');
69               
70INSERT INTO books (name, pub_house, genre, pub_year, pr_id, room_id) 
71	VALUES ('1984', NULL, 'Dystopia', '1934', 1, 1),
72		   ('Meinkampf', NULL, 'Authobiography', '1942', 3, 1),
73		   ('Brave New World', 'Hermes', 'Dystopia', '1947', 2, 2),
74           ('Notre Dame de Paris', NULL, 'Historical', NULL, 1, 2);
75           #('Bible', 'God', 'Christian', '100', 3, NULL);
76           
77INSERT INTO authors_books (author_id, book_id)
78	VALUES (1, 1),
79	       (2, 2),
80		   (3, 3),
81           (4, 4);
82           
83SELECT name 
84FROM rooms;
85
86SELECT u.first_name
87FROM users AS u;
88
89SELECT name
90FROM privileges;
91
92SELECT name 
93FROM books;
94
95SELECT au.first_name
96FROM authors AS au;
97
98SELECT b.name AS 'Book Name', r.name AS 'Room'
99FROM books AS b
100JOIN rooms AS r
101ON b.room_id = r.id 
102WHERE r.name = 'Living Room';
103
104SELECT u.first_name
105FROM users AS u
106JOIN privileges AS pr
107ON u.pr_id = pr.id
108WHERE pr.name = 'family_member';
109SELECT b.name AS book_name, b.genre, r.name AS room
110FROM books AS b
111RIGHT JOIN rooms AS r
112ON b.room_id = r.id;
113
114SELECT b.name, b.genre, r.name
115FROM rooms AS r
116LEFT JOIN books AS b
117ON b.room_id = r.id;
118
119SELECT DISTINCT a.first_name, b.name AS book_name, b.pub_house
120FROM authors AS a
121JOIN authors_books AS a_b
122ON a_b.author_id = a.id
123JOIN books AS b
124ON a_b.book_id = b.id;
125
126SELECT a.first_name, b.name, b.pub_house
127FROM authors AS a
128JOIN books AS b
129ON b.id IN (
130				SELECT a_b.book_id
131                FROM authors_books AS a_b
132                WHERE a_b.author_id = a.id
133                
134			);
135            
136SELECT pr.name, pr.id
137FROM privileges AS pr
138JOIN users AS u
139ON u.pr_id = pr.id
140WHERE u.last_name = 'Antonova';
141
142SELECT b.name, b.genre, u.first_name
143FROM books AS b
144JOIN users AS u
145ON u.pr_id IN (
146				SELECT pr.id
147                FROM privileges AS pr
148                WHERE pr.id = u.pr_id
149			  );