zgZKbwhw

1#12B class, Kaloyan Madjunov, Simeon Chakarov
2DROP DATABASE IF EXISTS Class5;
3CREATE DATABASE Class5;
4USE Class5;
5
6CREATE TABLE broker(
7	id INT PRIMARY KEY AUTO_INCREMENT,
8    name VARCHAR(30) NOT NULL,
9	age INT NOT NULL,
10	phone VARCHAR(15) NOT NULL
11);
12
13CREATE TABLE accommodation(
14	id INT PRIMARY KEY AUTO_INCREMENT,
15    broker_id INT NOT NULL,
16	price NUMERIC(5, 2) NOT NULL,
17    size NUMERIC(5, 2) NOT NULL,
18    location ENUM('Kostinbrod', 'Pavlovo', 'Lulin', 'Druzhba', 'Mladost', 'Izgrev', 'Poligona') NOT NULL,
19    
20    FOREIGN KEY (broker_id) REFERENCES broker(id)
21);
22
23INSERT INTO broker(name, age, phone) VALUES ('Simeon', 19, '0884189789');
24INSERT INTO broker(name, age, phone) VALUES ('Kaloyan', 18, '0888730554');
25INSERT INTO broker(name, age, phone) VALUES ('Bibsis', 17, '0885478952');
26INSERT INTO broker(name, age, phone) VALUES ('Valeri', 19, '0885478596');
27INSERT INTO broker(name, age, phone) VALUES ('Alex', 19, '0881139459');
28INSERT INTO broker(name, age, phone) VALUES ('Betty', 19, '0881345459');
29
30INSERT INTO accommodation(broker_id, price, size, location) VALUES (1, 700, 32, 2);
31INSERT INTO accommodation(broker_id, price, size, location) VALUES (2, 120, 12, 1);
32
33INSERT INTO accommodation(broker_id, price, size, location) VALUES (3, 500, 55, 7);
34INSERT INTO accommodation(broker_id, price, size, location) VALUES (4, 700, 60, 6);
35
36INSERT INTO accommodation(broker_id, price, size, location) VALUES (5, 500, 30, 5);
37INSERT INTO accommodation(broker_id, price, size, location) VALUES (6, 650, 60, 5);
38
39#Task 1
40SELECT b.name, b.phone, a.price, a.size, a.location 
41FROM accommodation AS a
42LEFT JOIN broker AS b
43ON a.broker_id = b.id
44WHERE b.name = 'Alex';
45
46#Task 2
47SELECT b.name, b.phone, a.location
48FROM accommodation AS a
49LEFT JOIN broker AS b
50ON a.broker_id = b.id
51WHERE a.location = 5;
52
53#Task 3
54SELECT b.name, b.phone, a.price, a.size
55FROM accommodation AS a
56LEFT JOIN broker AS b
57ON a.broker_id = b.id
58WHERE a.location = 5
59	AND a.size >= 50 AND a.size <= 70
60	AND a.price >= 500 AND a.price <= 700
61ORDER BY a.price;
62
63#Task 4
64SELECT b.name, b.phone, a.price, a.size
65FROM accommodation AS a
66LEFT JOIN broker AS b
67ON a.broker_id = b.id
68WHERE (a.location = 5 OR a.location = 6 OR a.location = 7)
69	AND a.size >= 50 AND a.size <= 70
70    AND a.price >= 500 AND a.price <= 700
71ORDER BY a.price/a.size;