RRH9KjpN

1DROP DATABASE IF EXISTS brokers;
2
3CREATE DATABASE brokers;
4USE brokers;
5
6CREATE TABLE brokers(
7	id INTEGER AUTO_INCREMENT PRIMARY KEY,
8    name VARCHAR(25) NOT NULL,
9    phone VARCHAR(20) NOT NULL
10);
11
12CREATE TABLE accomodations(
13	id INTEGER AUTO_INCREMENT PRIMARY KEY,
14	broker_id INTEGER,
15    price DECIMAL NOT NULL,
16    size DECIMAL NOT NULL,
17    location ENUM('Mladost', 'Izgrev', 'Poligona', 'Ovcha kupel'),
18    FOREIGN KEY(broker_id) REFERENCES brokers(id)
19);
20
21INSERT INTO brokers(name, phone) VALUES('Alex', '+359887068601');
22INSERT INTO brokers(name, phone) VALUES('Stamat', '+359887068602');
23INSERT INTO brokers(name, phone) VALUES('Stoimen', '+359887068701');
24
25INSERT INTO accomodations(broker_id, price, size, location) VALUES(1, 350, 80, 'Mladost');
26INSERT INTO accomodations(broker_id, price, size, location) VALUES(1, 550, 55, 'Mladost');
27INSERT INTO accomodations(broker_id, price, size, location) VALUES(1, 600, 65, 'Mladost');
28INSERT INTO accomodations(broker_id, price, size, location) VALUES(1, 350, 70, 'Mladost');
29
30INSERT INTO accomodations(broker_id, price, size, location) VALUES(2, 450, 90, 'Izgrev');
31
32SELECT * FROM accomodations;
33
34SELECT * FROM accomodations a
35RIGHT JOIN brokers br
36ON a.broker_id = br.id
37WHERE br.name = 'Alex';
38
39SELECT br.name as 'Brokers with accomodation in Mladost' FROM accomodations a
40RIGHT JOIN brokers br
41ON a.broker_id = br.id
42WHERE a.location = 'Mladost'
43GROUP BY br.id;
44
45SELECT * FROM accomodations a
46LEFT JOIN brokers br
47ON a.broker_id = br.id
48WHERE a.location = 'Mladost'
49AND a.size >= 50
50AND a.size <= 70
51AND a.price >= 500
52AND a.price <= 700
53ORDER BY a.price;
54
55SELECT * FROM accomodations a
56LEFT JOIN brokers br
57ON a.broker_id = br.id
58WHERE a.location = 'Mladost'
59AND a.size >= 50
60AND a.size <= 70
61AND a.price >= 500
62AND a.price <= 700
63ORDER BY (a.price / a.size);