SELECT House.numberHouse, Apartment.numberApartment, Apartment.rooms, District.NameDistrict
FROM Apartment, House, District
WHERE District.id_District=House.id_District AND
      House.id_House=Apartment.id_House AND
      Apartment.rooms = 1 AND
      House.id_District=1


--квартиры одной улицы, но разных районов
SELECT DISTINCT B.NameDistrict, B.NameStreet, B.numberHouse, Apartment.numberApartment
FROM (
        SELECT H1.id_House, S1.NameStreet, S2.id_District, H1.numberHouse, District.NameDistrict
        FROM Apartment, House H1, House H2, Street S1, Street S2, District
        WHERE District.id_District=H1.id_District
          AND S1.NameStreet=S2.NameStreet
          AND S1.id_District<>S2.id_District
          AND S1.NameCity=S2.NameCity
          AND S1.id_Street = H1.id_Street
          )B, Apartment
WHERE Apartment.id_House=B.id_House
ORDER BY B.numberHouse ASC

--2-3 комнаты с одинаковой площадью
--select DISTINCT A.rooms, A.squareApartment, B.rooms, B.squareApartment
--from  (select Apartment.squareApartment, Apartment.rooms
--              from Apartment
--              where Apartment.squareApartment=Apartment.squareApartment and Apartment.rooms=2)A,
--      (select Apartment.squareApartment, Apartment.rooms
--              from Apartment
--              where Apartment.squareApartment=Apartment.squareApartment and Apartment.rooms=3)B
--where A.squareApartment = B.squareApartment

--—редн€€ цена однокомнатной квартиры в городе ( оличество 1x кв, —умма цен 1x кв)
--select City.NameCity, SUM (Apartment.price)Sum_Price, COUNT (Apartment.rooms)KolichestvoRoms_1, avg(Apartment.price)AveragePrice
--from Apartment, City, District, Street, House
--where City.NameCity=District.NameCity
--  and District.id_District=Street.id_District
--  and Street.id_Street=House.id_Street
--  and House.id_House=Apartment.id_House
--  and Apartment.rooms=1
--group by City.NameCity
--go

--–районы, в которых продается наибольшее число объектов недвижимости
SELECT top 1 District.NameDistrict, COUNT (*) AS '„исло домов'
FROM House, District
WHERE District.id_District=House.id_District
GROUP BY District.NameDistrict
ORDER BY COUNT (*) DESC
GO

--–айоны, в которых минимальна стоимость квадратного метра
SELECT DISTINCT District.NameDistrict, Apartment.priceSquareM
FROM House, Apartment, District
WHERE District.id_District=House.id_District AND House.id_House=Apartment.id_House
  AND Apartment.priceSquareM = (SELECT MIN (Apartment.priceSquareM) FROM Apartment)
GO

--улицы, продолжительность которых ограничиваетс€ только одним районом
--select S1.NameStreet
--from Street S1
--where not exists (
--select *
--from Street S2
--where S1.NameStreet=S2.NameStreet
--  and S1.id_District<>S2.id_District
--  and S1.NameCity=S2.NameCity)

--районы, в которых не продаютс€ однокомнатные квартиры (не продаютс€ в районе 6-severny)
SELECT DISTINCT House.id_District, District.NameDistrict
FROM House, District
WHERE District.id_District=House.id_District
  AND House.id_District NOT IN (
    SELECT DISTINCT House.id_District
    FROM Apartment, House
    WHERE House.id_House=Apartment.id_House
      AND Apartment.rooms IN (1))

--районы, в которых продаются квартиры всех строительных серий
SELECT DISTINCT S.NameDistrict
FROM (
    SELECT DISTINCT District.NameDistrict, House.seriesHouse
    FROM House, District
    WHERE District.id_District=House.id_District)S
    WHERE NOT EXISTS (
                SELECT * FROM (
                        SELECT DISTINCT House.seriesHouse
                        FROM House
                        WHERE House.seriesHouse IN ('P-44','P-44T','I-521A'))Serii
                WHERE NOT EXISTS (SELECT *
                          FROM (
                            SELECT DISTINCT District.NameDistrict, House.seriesHouse
                            FROM House, District
                          WHERE District.id_District=House.id_District)S1
WHERE S.NameDistrict=S1.NameDistrict
  AND S1.seriesHouse=Serii.seriesHouse))