BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "bid_material" (
"id" INTEGER NOT NULL PRIMARY KEY UNIQUE,
"bid_id" INTEGER NOT NULL,
"material_id" INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS "buyer" (
"id" INTEGER NOT NULL PRIMARY KEY UNIQUE,
"name" TEXT NOT NULL,
"surname" TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS "bid" (
"id" INTEGER NOT NULL PRIMARY KEY UNIQUE,
"min_area" INTEGER NOT NULL,
"max_price" INTEGER NOT NULL,
"city_id" INTEGER NOT NULL,
"buyer_id" INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS "building_material" (
"id" INTEGER NOT NULL PRIMARY KEY UNIQUE,
"building_id" INTEGER NOT NULL,
"material_id" INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS "building" (
"id" INTEGER NOT NULL PRIMARY KEY UNIQUE,
"name" TEXT,
"area" INTEGER NOT NULL,
"price" INTEGER NOT NULL,
"owner_id" INTEGER NOT NULL,
"city_id" INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS "owner" (
"id" INTEGER NOT NULL PRIMARY KEY UNIQUE,
"name" TEXT,
"surname" TEXT
);
CREATE TABLE IF NOT EXISTS "material" (
"id" INTEGER NOT NULL PRIMARY KEY UNIQUE,
"name" TEXT
);
CREATE TABLE IF NOT EXISTS "city" (
"id" INTEGER NOT NULL PRIMARY KEY UNIQUE,
"name" TEXT
);
COMMIT;
SELECT city.name
FROM city JOIN (SELECT city_id, avg(area)
FROM building
GROUP BY city_id
HAVING avg(area) > (SELECT avg(area)
FROM building)) filter
ON city.id = filter.city_id;
---
ΠΠ»Ρ ΠΊΠ°ΠΆΠ΄ΠΎΠ³ΠΎ Π²Π»Π°Π΄Π΅Π»ΡΡΠ° Π½Π΅Π΄Π²ΠΈΠΆΠΈΠΌΠΎΡΡΠΈ Π²ΡΠ²Π΅Π΄ΠΈΡΠ΅ Π΅Π³ΠΎ ΠΈΠΌΡ, ΡΠ°ΠΌΠΈΠ»ΠΈΡ ΠΈ ΡΠ΅Π½Ρ ΡΠ°ΠΌΠΎΠ³ΠΎ Π΄ΠΎΡΠΎΠ³ΠΎΠ³ΠΎ ΠΎΠ±ΡΠ΅ΠΊΡΠ°
ΡΡΠ΅Π΄ΠΈ Π²ΡΠ΅Ρ Π΅Π³ΠΎ ΠΎΠ±ΡΠ΅ΠΊΡΠΎΠ². ΠΡΠ»ΠΈ Ρ Π²Π»Π°Π΄Π΅Π»ΡΡΠ° Π½Π΅Ρ ΠΎΠ±ΡΠ΅ΠΊΡΠΎΠ², Π²ΡΠ²Π΅Π΄ΠΈΡΠ΅ NULL.
6.---
SELECT owner.name, owner.surname, max(building.price)
FROM owner LEFT JOIN building
ON owner.id = building.owner_id
GROUP BY owner.name, owner.surname;
---
ΠΡΠ²Π΅Π΄ΠΈΡΠ΅ ΠΈΠΌΠ΅Π½Π° ΠΈ ΡΠ°ΠΌΠΈΠ»ΠΈΠΈ Π²ΡΠ΅Ρ ΠΏΠΎΠΊΡΠΏΠ°ΡΠ΅Π»Π΅ΠΉ, ΠΊΠΎΡΠΎΡΡΠ΅ Π³ΠΎΡΠΎΠ²Ρ ΠΊΡΠΏΠΈΡΡ Π·Π΄Π°Π½ΠΈΠ΅
ΠΏΠΎΠ΄ ΠΈΠΌΠ΅Π½Π΅ΠΌ "Object 101".
7.---
SELECT buyer.name as cli_name, buyer.surname, obj.*
FROM buyer JOIN (SELECT bid.buyer_id, bid.min_area, building.area,
building.price, bid.max_price, building.city_id, bid.city_id, building.name
FROM bid INNER JOIN building
ON bid.city_id = building.city_id AND building.price <= bid.max_price AND building.area >= bid.min_area
WHERE building.name = "Object 101" AND NOT EXISTS (SELECT bid_material.*, building_material.*
FROM building_material LEFT JOIN bid_material
ON building_material.material_id = bid_material.material_id
WHERE bid_material.bid_id = bid.id
EXCEPT
SELECT bid_material.*, building_material.*
FROM building_material JOIN bid_material
ON building_material.material_id = bid_material.material_id
WHERE bid_material.bid_id = bid.id
)
AND NOT EXISTS (
SELECT bid_material.*, building_material.*
FROM bid_material LEFT JOIN building_material
ON building_material.material_id = bid_material.material_id
WHERE bid_material.bid_id = bid.id
EXCEPT
SELECT bid_material.*, building_material.*
FROM building_material JOIN bid_material
ON building_material.material_id = bid_material.material_id
WHERE bid_material.bid_id = bid.id
)
) obj
ON buyer.id = obj.buyer_id;
Telegram: DB developers channel