--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))