Реализация функциональных задач

1. Оформление заказа № 1:

INSERT INTO Доставка.

VALUES (1, 500, 'Альянс');

INSERT INTO Заказ.

VALUES (1, '03.05.2013', '11.05.2013', 9000, 77, 1);

INSERT INTO Состав_заказа.

VALUES (1, 8, 1);

INSERT INTO Состав_заказа.

VALUES (1, 9, 1);

2. Оформление заказа № 2:

INSERT INTO Доставка.

VALUES (2, 200, 'Автотрейдинг');

INSERT INTO Заказ.

VALUES (2, '03.05.2013', '05.05.2013', 9000, 77, 2);

INSERT INTO Состав_заказа.

VALUES (2, 9, 2);

INSERT INTO Состав_заказа.

VALUES (2, 10, 2);

3. Оформление заказа № 3:

INSERT INTO Доставка.

VALUES (3, 200, 'Автотрейдинг');

INSERT INTO Заказ.

VALUES (3, '17.12.2012', '18.12.2012', 24 000, 337, 3);

INSERT INTO Состав_заказа.

VALUES (3, 8, 1);

INSERT INTO Состав_заказа.

VALUES (3, 9, 1);

INSERT INTO Состав_заказа.

VALUES (3, 10, 1);

4. Отмена заказа № 2:

DELETE FROM Заказ.

WHERE Ном_заказа = 2;

3. Изменить фамилию, имя, отчество, домашний телефон клиента «Рыбаков» :

UPDATE Клиент.

SET Фамилия = 'Раков',.

Имя = 'Петр',.

Отчество = 'Евлампиевич'.

WHERE ID_Клиента = 78;

UPDATE Адрес.

SET Телефон_домашний = '777−25−47'.

WHERE ID_Клиента = 78;

4. Выбрать заказы с периодом доставки меньше 2-х дней и стоимостью ниже 300 рублей:

SELECT DISTINCT Ном_заказа, Дата_оформления, Дата_исполнения, Стоимость_доставки.

FROM Заказ, Доставка.

WHERE Дата_исполнения — Дата_оформления < 2 AND Стоимость_доставки < 300;

5. Добавить в заказ 3 изделия от поставщиков «Балтика», «Кардинал», «Феонит» :

INSERT INTO Состав_заказа.

VALUES (1, 10, 1);

UPDATE Состав_заказа.

SET Количество_изделий_в_заказе = Количество_изделий_в_заказе + 1.

WHERE Ном_заказа = 1;

6. Выдать первых пять заказчиков с наибольшей общей стоимостью заказов за последний месяц:

SELECT ID_Клиента, Стоимость_заказа.

FROM (SELECT ID_Клиента, Стоимость_заказа.

FROM Заказ.

ORDER BY Стоимость_заказа DESC).

WHERE ROWNUM <= 5;

7. Добавить поставщика мебели:

INSERT INTO Поставщик_мебели.

VALUES ('Изумруд', 'Попов', 'Менеджер', '261−44−33');

8. Уменьшить количество изделий «Кухонный гарнитур» на складе на 15%:

UPDATE Изделие.

SET Количество_на_складе = Количество_на_складе — (Количество_на_складе / 100 * 15).

WHERE Артикул_изделия = 11;

9. Уволить агента, оформившего наименьшее количество заказов:

DELETE.

FROM Агент_по_продаже.

WHERE ФИО_агента =.

(SELECT Фамилия_агента.

FROM Заказ.

GROUP BY Фамилия_агента.

HAVING (COUNT (*)) =.

(SELECT MIN (COUNT (*)).

FROM Заказ.

GROUP BY Фамилия_агента));

10. Выдать список заказов с указанием даты совершения заказа, для которых сделки были заключены агентами с фамилиями, начинающимися на «Ба…» :

SELECT Заказ. Ном_заказа, Дата_оформления, ФИО_агента.

FROM Заказ, Агент_по_продаже.

WHERE ФИО_агента LIKE 'Ба %';

11. Определить размер заработной платы агента «Русинова» :

SELECT SUM (Стоимость_заказа * 0.02).

FROM Заказ, Агент_по_продаже.

WHERE ФИО_агента = 'Русинов';

12. Выдать информацию об агенте, совершившем наибольшее количество сделок в «Январе» месяце текущего года:

SELECT Фамилия_агента, count (*).

FROM Заказ.

WHERE Дата_оформления BETWEEN TO_DATE ('01.05.2013','DD/MM/YYYY') AND TO_DATE ('31.05.2013','DD/MM/YYYY').

GROUP BY Фамилия_агента.

HAVING COUNT (*) =.

(SELECT MAX (COUNT (*)).

FROM Заказ.

WHERE Дата_оформления BETWEEN TO_DATE ('01.05.2013','DD/MM/YYYY') AND TO_DATE ('31.05.2013','DD/MM/YYYY').

GROUP BY Фамилия_агента);

13. Выдать общую стоимость заказа с учётом скидки (если она применима к данному заказу) и стоимостью доставки:

SELECT Ном_заказа, Стоимость_заказа — Стоимость_заказа*Скидка + Стоимость_доставки.

FROM Заказ, Доставка.

WHERE Заказ. Ном_договора_доставки = Доставка. Ном_договора_доставки;

14. Подсчитать общий объём продаж фирмы за период с 1 января текущего года по текущую дату:

SELECT SUM (Стоимость_заказа).

FROM Заказ.

WHERE Дата_оформления >= TO_DATE ('01.01.2013','DD/MM/YYYY') AND.

Дата_оформления <= TO_DATE (sysdate);

15. Подсчитать среднемесячный объем продаж фирмы за период с 1 января текущего года по текущую дату:

SELECT AVG (SUM (Стоимость_заказа — Стоимость_заказа*Скидка)).

FROM Заказ.

WHERE Дата_оформления >= TO_DATE ('01.01.2013','DD/MM/YYYY') AND.

Дата_оформления <= TO_DATE (sysdate).

GROUP BY TRUNC (TO_DATE (Дата_оформления),'MONTH');

16. Вывести самого «активного» покупателя (совершившего наибольшее число заказов):

SELECT Фамилия, Имя, Отчество, COUNT (*).

FROM Клиент, Заказ.

WHERE Клиент. ID_Клиента = Заказ. ID_Клиента.

GROUP BY Фамилия, Имя, Отчество.

HAVING COUNT (*) =.

(SELECT MAX (COUNT (*)).

FROM Заказ.

GROUP BY ID_клиента);

17. Вывести информацию о покупателях, заказы которых превысили 20 000 рублей:

SELECT Фамилия, Имя, Отчество, SUM (Стоимость_заказа).

FROM Клиент, Заказ.

WHERE Клиент. ID_Клиента = Заказ. ID_Клиента.

GROUP BY Фамилия, Имя, Отчество.

HAVING SUM (Стоимость_заказа) > 20 000;

18. Составить список адресов доставки транспортной компании на конкретную дату.

SELECT DISTINCT Транспортная_компания, Улица, Дом, Квартира.

FROM Адрес, Заказ, Доставка.

WHERE Адрес. ID_Клиента = Заказ. ID_Клиента AND Заказ. Ном_договора_доставки = Доставка. Ном_договора_доставки AND Дата_исполнения = TO_DATE ('11.05.2013','DD/MM/YYYY');

19. Выдать список заказов, совершенных клиентом «Петровым» за период с «Января» по «Декабрь» прошлого года:

SELECT Ном_заказа.

FROM Заказ, Клиент.

WHERE Фамилия = 'Петров' AND.

Дата_оформления >= TO_DATE ('01.01.2012','DD/MM/YYYY') AND.

Дата_оформления < TO_DATE ('01.12.2012','DD/MM/YYYY');

20. Выдать список агентов по продаже, чей доход за май текущего года превысил средний уровень заработной платы за предыдущий год:

SELECT ФИО_агента.

FROM Заказ, Агент_по_продаже.

WHERE Заказ. Фамилия_агента = Агент_по_продаже. ФИО_агента AND TRUNC (TO_DATE (Дата_оформления),'Month') = TO_DATE ('01.05.2013','DD/MM/YYYY').

GROUP BY ФИО_Агента.

HAVING SUM (Стоимость_заказа)*.

(SELECT Count (*).

FROM Агент_по_продаже) >

(SELECT SUM (Стоимость_заказа)*0.02/(12).

FROM Заказ.

WHERE TRUNC (TO_DATE (Дата_оформления),'YEAR') = TO_DATE ('01.01.2013','DD/MM/YYYY'));

21. Выдать список заказов, доставленных по конкретному адресу с суммой менее 15 000 рублей:

SELECT DISTINCT Ном_заказа.

FROM Заказ, Клиент, Адрес.

WHERE Улица = 'Ленинградский пр.' AND Дом = 27 AND Квартира = 141 AND Стоимость_заказа < 15 000;

22. Выдать список клиентов, не имеющих домашних телефонов, проживающих на улице «Ленинградский проспект» :

SELECT Фамилия, Имя, Отчество, Улица, Телефон_домашний.

FROM Клиент, Адрес.

WHERE Клиент. ID_Клиента = Адрес. ID_Клиента AND Улица LIKE 'Ленинградский %' AND Телефон_домашний IS NULL;

23. Вывести ФИО клиентов, оформивших меньшее количество заказов, чем «Иванов» :

SELECT Фамилия, Имя, Отчество, COUNT (*).

FROM Клиент, Заказ.

WHERE Клиент. ID_Клиента = Заказ. ID_Клиента.

GROUP BY Фамилия, Имя, Отчество.

HAVING COUNT (*) <

(SELECT COUNT (*).

FROM Клиент, Заказ.

WHERE Клиент. ID_Клиента = Заказ. ID_Клиента and Фамилия = 'Иванов');

24. По всем неисполненным заказам вывести подробную информацию о фирмах поставщиках:

SELECT Транспортная_компания.

FROM Заказ, Доставка.

WHERE Дата_исполнения is null;

25. Вычислить среднюю стоимость заказов «Иванова», «Семенова» :

SELECT Фамилия, AVG (Стоимость_заказа).

FROM Заказ, Клиент WHERE Клиент. ID_Клиента = Заказ. ID_Клиента.

AND Фамилия in ('Иванов', 'Семенов').

GROUP BY Фамилия;