2wPF4tKB

1DROP DATABASE IF EXISTS DB;
2CREATE DATABASE DB;
3USE DB;
4
5CREATE TABLE brockers(
6	Id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
7    name VARCHAR(100) NOT NULL,
8    age INT NOT NULL,
9    phone VARCHAR(15) NOT NULL
10);
11
12CREATE TABLE accommodations(
13	Id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
14	brockerId INT NOT NULL,
15    price FLOAT NOT NULL,
16    size FLOAT NOT NULL,
17    location ENUM ('Peturch', 'Mladost', 'Suhata Reka', 'Kostinbrod','Poligona','Izgrev') NOT NULL,
18    
19    FOREIGN KEY (brockerId) REFERENCES brockers(Id)
20);
21
22INSERT INTO brockers(name, age, phone) VALUES('Roberta', 18, '0890909387');
23INSERT INTO brockers(name, age, phone) VALUES('Iliqn',19, '0890909387');
24INSERT INTO brockers(name, age, phone) VALUES('Todor', 18, '0890909387');
25INSERT INTO brockers(name, age, phone) VALUES('Pesho', 300, '0890909387');
26
27INSERT INTO accommodations(brockerId, price, size, location) 
28VALUES(1, 520, 60, 'Mladost');
29INSERT INTO accommodations(brockerId, price, size, location) 
30VALUES(3, 501, 70, 'Mladost');
31INSERT INTO accommodations(brockerId, price, size, location) 
32VALUES(1, 555, 50, 'Suhata Reka');
33INSERT INTO accommodations(brockerId, price, size, location) 
34VALUES(2, 1, 1, 'Kostinbrod');
35INSERT INTO accommodations(brockerId, price, size, location) 
36VALUES(3, 699, 51, 'Izgrev');
37INSERT INTO accommodations(brockerId, price, size, location) 
38VALUES(4, 699, 50, 'Poligona');
39
40SELECT * FROM brockers;
41SELECT * FROM accommodations;
42
43SELECT * FROM brockers as b 
44LEFT JOIN accommodations as a ON b.Id = a.brockerId 
45WHERE b.name LIKE 'Roberta';  
46
47SELECT b.name, b.phone, a.location FROM brockers as b 
48LEFT JOIN accommodations as a ON b.Id = a.brockerId
49WHERE a.location LIKE 'Mladost';
50
51SELECT b.name, b.phone, a.price, a.size FROM brockers as b 
52LEFT JOIN accommodations as a ON b.Id = a.brockerId
53WHERE (a.price >= 500 AND a.price<= 700) AND 
54(a.size >= 50 AND a.size <= 70) AND
55a.location LIKE 'Mladost'
56ORDER BY a.price ASC;
57
58SELECT b.name, b.phone, a.price, a.size FROM brockers as b 
59LEFT JOIN accommodations as a ON b.Id = a.brockerId
60WHERE (a.price >= 500 AND a.price<= 700) AND (
61	a.size >= 50 AND a.size <= 70) AND (
62	a.location LIKE 'Mladost' OR
63	a.location LIKE 'Poligona' OR 
64	a.location LIKE 'Izgrev' )
65ORDER BY (a.Size / a.Price) ASC;