KcLWZzpv

1DROP DATABASE IF EXISTS ime;
2CREATE DATABASE ime;
3
4USE ime;
5
6CREATE TABLE Brockers(
7	Id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
8    BrockerName VARCHAR(100) NOT NULL,
9    Age INT NOT NULL,
10    Phone VARCHAR(15) NOT NULL
11);
12
13CREATE TABLE Accommodations(
14	Id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
15	BrockerId INT NOT NULL,
16    Price FLOAT NOT NULL,
17    Size FLOAT NOT NULL,
18    Location ENUM ('Lulin', 'Mlados', 'Izgrev', 'Poligona') NOT NULL,
19    
20    FOREIGN KEY (BrockerId) REFERENCES Brockers(Id)
21);
22
23INSERT INTO Brockers VALUES(1, 'aziskata', 50, '69696969');
24INSERT INTO Brockers VALUES(2, 'tonkata', 68, '5632531');
25
26INSERT INTO Accommodations VALUES(1, 1, 500, 1, 'Mlados');
27INSERT INTO Accommodations VALUES(2, 1, 555, 50, 'Mlados');
28INSERT INTO Accommodations VALUES(3, 2, 1, 1, 'Lulin');
29INSERT INTO Accommodations VALUES(4, 2, 699, 50, 'Mlados');
30
31SELECT b.BrockerName, b.Phone, a.Price, a.Size, a.Location
32FROM Accommodations a LEFT JOIN Brockers b
33ON a.BrockerId = b.Id
34WHERE b.BrockerName = 'aziskata';
35
36SELECT b.BrockerName, b.Phone, a.Location
37FROM Accommodations a LEFT JOIN Brockers b
38ON a.BrockerId = b.Id
39WHERE a.Location = 'Mlados';
40
41SELECT b.BrockerName, b.Phone, a.Price, a.Size
42FROM Accommodations a LEFT JOIN Brockers b
43ON a.BrockerId = b.Id
44WHERE a.Location = 'Mlados' 
45AND a.Size >= 50 AND a.Size <= 70
46AND a.Price >= 500 AND a.Price <= 700
47ORDER BY a.Price;
48
49SELECT b.BrockerName, b.Phone, a.Price, a.Size
50FROM Accommodations a LEFT JOIN Brockers b
51ON a.BrockerId = b.Id
52WHERE (a.Location = 'Mlados' OR a.Location = 'Izgrev' OR a.Location = 'Poligona') 
53AND a.Size >= 50 AND a.Size <= 70
54AND a.Price >= 500 AND a.Price <= 700
55ORDER BY (a.Size / a.Price);