jvvmgxuX

1DROP DATABASE IF EXISTS real_estate;
2CREATE DATABASE real_estate;
3
4USE real_estate;
5
6CREATE TABLE Brokers(
7	id INTEGER NOT NULL,
8    name VARCHAR(50) NOT NULL,
9    age INTEGER NOT NULL,
10    phone VARCHAR(16) NOT NULL,
11    
12    PRIMARY KEY(id)
13);
14
15CREATE TABLE Accommodations(
16	id INTEGER AUTO_INCREMENT NOT NULL,
17    broker_id INTEGER NOT NULL,
18    price INTEGER NOT NULL,
19    size INTEGER NOT NULL,
20    location ENUM('Lulin', 'Mladost', 'Lozenets', 'Centre', 'Poligona'),
21    
22    PRIMARY KEY(id),
23    FOREIGN KEY(broker_id) REFERENCES Brokers(id)
24);
25
26INSERT INTO Brokers VALUES (1, 'Peshislav', 23, '+359 88 1234 432');
27INSERT INTO Brokers VALUES (2, 'Iva', 52, '+359 87 8888 777');
28INSERT INTO Brokers VALUES (3, 'Alexo', 19, '+359 88 4567 765');
29
30INSERT INTO Accommodations VALUES (1, 1, 1356, 200, 'Lulin');
31INSERT INTO Accommodations VALUES (2, 2, 222, 300, 'Mladost');
32INSERT INTO Accommodations VALUES (3, 3, 401, 150, 'Lozenets');
33INSERT INTO Accommodations VALUES (4, 1, 536, 52, 'Centre');
34INSERT INTO Accommodations VALUES (5, 2, 324, 400, 'Poligona');
35INSERT INTO Accommodations VALUES (6, 3, 606, 65, 'Mladost');
36INSERT INTO Accommodations VALUES (7, 2, 666, 69, 'Poligona');
37
38SELECT name, phone, price, size, location FROM Brokers
39LEFT JOIN Accommodations ON broker_id = Brokers.id
40WHERE name LIKE 'Alex%';
41
42SELECT name, phone, location FROM Brokers
43LEFT JOIN Accommodations ON broker_id = Brokers.id
44WHERE location = 'Mladost';
45
46SELECT name, phone, price, size FROM Brokers
47LEFT JOIN Accommodations ON broker_id = Brokers.id
48WHERE size BETWEEN 50 AND 70 AND price BETWEEN 500 AND 700
49ORDER BY price;
50
51SELECT name, phone, price, size FROM Brokers
52LEFT JOIN Accommodations ON broker_id = Brokers.id
53WHERE size BETWEEN 50 AND 70 AND price BETWEEN 500 AND 700 
54		AND (location = 'Mladost' OR location = 'Izgrev' OR location = 'Poligona')
55ORDER BY price/size;