Вопросы к базе данных

1) Какие товары имеют продажную цену более 20 000?

select наименование_товара, Продажная_цена.

from Товар

where Продажная_цена>20 000.

2) Какие товары имеют закупочную цену менее 7000?

select наименование_товара, закупочная_цена.

from Товар

where Закупочная_цена<10 000.

3) Какие товары произведены в определенной стране и кто их производители?

select наименование_товара, наименование_производителя, Страна.

from товар, Производитель.

where товар. Код_производителя=Производитель.Код_производителя and Страна='Китай'.

4) Какой торговый агент продал товар стоимостью выше 15 000?

select ФИО_агента, Наименование_товара, Продажная_цена.

from Торговый_агент, Товар, Заказы.

where Торговый_агент.Код_агента=заказы.Код_агента and Заказы. Код_товара=Товар.код_товара and товар. Продажная_цена>30 000.

5) Сколько заказов оформил каждый торговый агент в текущем месяце?

select ФИО_агента, count (номер_заказа) as количество_оформленных_заказов.

from Торговый_агент, заказы.

where Торговый_агент.Код_агента=Заказы.Код_агента and Дата_заказа between '01.10.2014' and '31.10.2014'.

group by ФИО_агента.

6) Определить сколько заказов было совершено в текущем месяце.

select count (Номер_заказа) as кол_заказов_за_месяц.

from Заказы.

where Дата_заказа between '01.09.2014' and '30.09.2014'.

7) Определить на какую сумму было продано каждого товара за месяц.

select наименование_товара, Sum (Сумма_заказа) as общая_сумма.

from Товар, Заказы.

where Товар. код_товара=Заказы.Код_товара and Заказы. Дата_заказа between '01.09.2014' and '30.09.2014'.

group by Наименование_товара.

8) Найти торговых агентов, чьи заказы не оплачены, а также сумму заказа, и клиента который не оплатил заказ, и вывести их телефоны.

select ФИО_агента, номер_заказа, сумма_заказа, Наименование_клиента, телефон_клиента, оплата.

from Заказы, Торговый_агент, Клиенты.

where Торговый_агент.Код_агента=Заказы.Код_агента and клиенты. Код_клиента=Заказы.Код_клиента and Оплата='False'.

Создадим для нашей базы данных процедуры для облегчения обновления данных.

Для начала добавим в таблицу «Товар» столбец остаток, который будет отображать оставшееся количество товара на складе.

alter table товар

add остаток int.

Далее рассчитаем остаток товара на складе. Остаток будет равен разнице между количеством товара на складе и количеством заказанного товара.

update Товар

set остаток=Количество_товара-(select sum (количество) from Заказы.

where товар. код_товара=заказы.Код_товара).

Создадим процедуру обновления остатка товара на складе для удобства.

create procedure остаток.

as update Товар

set остаток=Количество_товара-(select sum (количество) from Заказы.

where товар. код_товара=заказы.Код_товара) При добавлении новых заказов, нам необходимо рассчитывать сумму заказа, для облегчения этой команды создадим процедуру расчета суммы заказа.

create procedure сумма_заказа.

as update Заказы.

set Сумма_заказа=Количество*(select Товар. Продажная_цена from товар where товар. Код_товара=Заказы.Код_товара) В таблице «Товар» отображаются данные о производителе в форме кода производителя, что будет непонятно при просмотре базы данных простым пользователем. Поэтому необходимо отображать наименование производителя. Также данные о закупочной цене являются конфиденциальными и их нельзя просматривать клиентам. Поэтому создадим представление, которое выводило бы таблицу «Товар» в удобной форме для просмотра клиентами.

create view Товар_клиенты.

as select наименование_товара, наименование_производителя, страна, продажная_цена.

from товар, Производитель.

where Производитель. Код_производителя=Товар.Код_производителя.

create view Счет_на_оплату.

as select номер_заказа, Наименование_клиента, Наименование_товара, ФИО_агента, Дата_заказа, Количество, Сумма_заказа, Оплата.

from заказы, клиенты, производитель, товар, торговый_агент.

where Клиенты. Код_клиента=Заказы.Код_клиента and Производитель. Код_производителя=Товар.Код_производителя and.

Товар.код_товара=Заказы.код_товара and Торговый_агент.код_агента=заказы.код_агента Предположим необходимо отслеживать количество товара, которое мы можем продать. Это количество не должно превышать количество товара на складе; если менеджер по ошибке нарушает это условие, система должна выдать сообщение. Для этого создадим триггер, обеспечивающий выполнение данного условия.

create trigger Остаток_товара.

on заказы.

for.

insert as.

declare @остаток1 int, @остаток2 int.

select @остаток1=Заказы.Количество, @остаток2=Товар.Количество_товара.

from Товар, Заказы.

where товар. Код_товара=заказы.Код_товара.

if @остаток1>@остаток2.

begin.

rollback tran.

raiserror ('Недостаточно товара на складе', 16,3).

end.

Проверим работу триггера. Попробуем вставить данные о заказе, где количество заказанного товара превышает количество товара на складе.

При оформлении или редактирования данных в базе, необходимо предусмотреть, чтобы данные о неоплаченных заказах нельзя было удалить. Для этого создадим триггер. Для начала необходимо в таблицу «Заказы» добавить столбец «Оплата», который показывал бы, оплачен ли товар или нет.

alter table заказы.

add Оплата bit.

Далее создаем сам триггер.

create trigger заказ_не_оплачен.

on заказы.

for.

delete as.

declare @номер_заказа int, @код_клиента int, @код_товара int, @код_агента int, @дата_заказа smalldatetime, @количество int, @сумма_заказа money, @оплата bit.

select *.

from Заказы, Товар, Клиенты, Торговый_агент.

where Заказы. Код_товара=Товар.код_товара and Заказы. Код_агента=Торговый_агент.Код_агента and заказы. Код_клиента=Клиенты.Код_клиента.

if @оплата='False'.

begin.

rollback tran.

raiserror ('Данные невозможно удалить, заказ не оплачен', 16,3).

end.