Oracle - MS Server / Queries.sql

--Начало 1-го
SELECT FLAT.Adr, FLAT.Nrooms, PERSON.FIO, PERSON.Rdate, PERSON.SumD
FROM FLAT INNER JOIN
PERSON ON FLAT.Adr = PERSON.Adr
WHERE (PERSON.SumD > 1300)
UNION
--Начало 2-го
SELECT FLAT_1.Adr, FLAT_1.Nrooms, PERSON_1.FIO, PERSON_1.Rdate, PERSON_1.SumD
FROM FLAT AS FLAT_1 INNER JOIN
PERSON AS PERSON_1 ON FLAT_1.Adr = PERSON_1.Adr
WHERE (PERSON_1.SumD < 500)
UNION
--Начало 3-го
SELECT FLAT_2.Adr, FLAT_2.Nrooms, PERSON_2.FIO, PERSON_2.Rdate, PERSON_2.SumD
FROM FLAT AS FLAT_2 INNER JOIN
PERSON AS PERSON_2 ON FLAT_2.Adr = PERSON_2.Adr
WHERE (PERSON_2.SumD <= 1200) AND (PERSON_2.SumD >= 600)

--7a
SELECT Adr, SUM(SumD)
FROM PERSON
GROUP BY Adr
--7б
SELECT Adr, COUNT(Nom)
FROM PERSON
GROUP BY Adr
--7в
SELECT COUNT(HAVE_D.Id), PERSON.SumD, PERSON.FIO
FROM HAVE_D INNER JOIN
PERSON ON HAVE_D.Nom = PERSON.Nom
GROUP BY PERSON.SumD, PERSON.FIO
HAVING (PERSON.SumD > 0)
--7г
SELECT PERSON.Adr
FROM PERSON INNER JOIN
HAVE_D ON PERSON.Nom = HAVE_D.Nom
GROUP BY PERSON.Adr
--7д
SELECT Adr, FIO, SUM(SumD)
FROM PERSON
GROUP BY Adr, FIO
--7е
SELECT Adr, SUM(SumD), COUNT(Nom)
FROM PERSON
GROUP BY Adr
HAVING (COUNT(Nom) > 1) AND (SUM(SumD) < 2000)

--8а
SELECT FIO, SumD
FROM PERSON
WHERE (SumD >
(SELECT MAX(Moneys) AS Expr1
FROM PROFIT))
--8б
SELECT Adr
FROM FLAT
WHERE (Skv * Skv >
(SELECT SUM(SumD) AS Expr1
FROM PERSON
WHERE (FLAT.Adr = Adr)))
--8в
SELECT Adr, Skv, Nrooms, Kcategory
FROM FLAT
WHERE (Adr NOT IN
(SELECT Adr
FROM TPHONE))
--8г
SELECT Nom, FIO, Rdate, Pol, SumD, Adr
FROM PERSON
WHERE EXISTS
(SELECT PROFIT.Id, PROFIT.Source, PROFIT.Moneys, HAVE_D.Nom, HAVE_D.Id AS Expr1, HAVE_D.Comment
FROM PROFIT INNER JOIN
HAVE_D ON PROFIT.Id = HAVE_D.Id
WHERE (PERSON.Nom = HAVE_D.Nom) AND (PROFIT.Moneys > 400))
--8д
SELECT Nom, FIO, Rdate, Pol, SumD, Adr
FROM PERSON
WHERE EXISTS
(SELECT PROFIT.Id, PROFIT.Source, PROFIT.Moneys, HAVE_D.Nom, HAVE_D.Id AS Expr1, HAVE_D.Comment
FROM PROFIT INNER JOIN
HAVE_D ON PROFIT.Id = HAVE_D.Id
WHERE (PERSON.Nom = HAVE_D.Nom) AND (PROFIT.Moneys > PERSON.SumD / 4))
--8е
SELECT FIO
FROM PERSON
WHERE EXISTS
(SELECT PROFIT.Id, PROFIT.Source, PROFIT.Moneys, HAVE_D.Nom, HAVE_D.Id AS Expr1, HAVE_D.Comment
FROM PROFIT INNER JOIN
HAVE_D ON PROFIT.Id = HAVE_D.Id
WHERE (PERSON.Nom = HAVE_D.Nom) AND ((PERSON.SumD * 0.9) < ALL
(SELECT Moneys
FROM PROFIT AS PROFIT_1
WHERE (PERSON.Nom = HAVE_D.Nom) AND (Id = HAVE_D.Id))))
--8ж
SELECT HAVE_D.Id, PROFIT.Moneys
FROM HAVE_D CROSS JOIN
PROFIT
GROUP BY HAVE_D.Id, PROFIT.Moneys
HAVING (COUNT(HAVE_D.Nom) >= ALL
(SELECT COUNT(Nom) AS Expr1
FROM HAVE_D AS HAVE_D_1
GROUP BY Id))