sql_tasks

1.sql

SELECT Клиент.ФИО, SUM(ТоварыЗаказа.Цена)
FROM Клиент
INNER JOIN Заказ ON Клиент.КлиентID = Заказ.КлиентID
INNER JOIN ТоварыЗаказа ON ТоварыЗаказа.ЗаказID = Заказ.ЗаказID
WHERE Заказ.Дата >= '2013-12-01' AND
      Заказ.Дата <= '2013-12-31'
GROUP BY Клиент.КлиентID
HAVING SUM(ТоварыЗаказа.Цена) >= 10000;

2.sql

SELECT A.адрес, A.дом, A.квартира
FROM A
INNER JOIN B ON (A.адрес LIKE '%телефон: ' || B.телефон || '%') AND
                (B.телефон LIKE '+7495%890');

3.sql

SELECT *
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY (ФИО, ДатаРождения, Паспорт) ORDER BY ДатаЗанесения DESC) AS row
      FROM Клиент) dups
WHERE dups.row > 1;

4.sql

CREATE FUNCTION date_diff(date, date) returns integer as $$
SELECT $1 - $2; $$ language sql;

SELECT *
FROM (SELECT *, date_diff(date, min(Date_Visit) OVER (PARTITION BY Name ORDER BY Date_Visit)) AS since_first_day
      FROM Посещения) people
WHERE people.since_first_day > 90;