База данных ассоциации шеф-поваров

 

select dbo.product.name_product, count(dbo.product.name_product)'продуктов за промежуток'

from product, Zakaz3, recept, recept_product, bludo1, klient_bludo1

where dbo.product.product_id=dbo.recept_product.product_id

and dbo.recept_product.recept_id=dbo.recept.recept_id

and dbo.recept_product.recept_product_id=dbo.bludo1.recept_product_id

and dbo.bludo1.bludo_id=dbo.klient_bludo1.bludo_id

and dbo.klient_bludo1.klient_bludo_id=dbo.zakaz3.klient_bludo_id

and dbo.zakaz3.data_zakaza between '2013-05-01' AND '2013-05-05'

group by dbo.product.name_product

 

Рисунок 3.16 - Запрос 16

 

2. Запросы с использование функции

 

1. Вывести список блюд, приготовление которых медленнее средней скорости приготовления всех блюд

 

select name_bludo, time_prigotovlenia_min

from dbo.bludo1

where time_prigotovlenia_min > (select AVG(time_prigotovlenia_min) from bludo1)

Group by name_bludo, time_prigotovlenia_min

 

Рисунок 3.17 - Запрос 17

 

2. Вывести минимальную скорость приготовления блюд

 

select MIN(time_prigotovlenia_min) 'Минимальная скорость приготовления'

from bludo1

 

Рисунок 3.18 - Запрос 18

 

3. Продукты срок годности, которых по 43 дня

 

select name_product

from product

where srok_dney Like 43

 

Рисунок 3.19 - Запрос 19

 

 

4. Количество полок на складе

 

select COUNT(sklad_id)'Количество полок'

from sklad

 

Рисунок 3.20 - Запрос 20

 

5. Вывести список блюд, с максимальной скоростью приготовления

 

select name_bludo, time_prigotovlenia_min

from dbo.bludo1

where time_prigotovlenia_min = (select MAX(time_prigotovlenia_min) from bludo1)

Order by time_prigotovlenia_min

 

 

Рисунок 3.21 - Запрос 21

 

6. Вывести список продуктов, срок годности которых меньше среднего срока годности всех продуктов

 

select name_product, srok_dney

from product

where srok_dney < (select avg(srok_dney) from product)

Group by name_product, srok_dney

 

Рисунок 3.22 - Запрос 22

 

7. Количество заказов в промежуток

 

select  COUNT(dbo.Zakaz3.zakaz_id)' Количество заказов'

from dbo.Zakaz3

where dbo.zakaz3.data_zakaza BETWEEN '2013-05-02' AND '2013-05-03'

 

Рисунок 3.23 - Запрос 23

 

8. Сумма заказов по дате

 

select Sum(dbo.Zakaz3.cena_bluda_za_zakaz_rub)' Сумма заказов'

from dbo.Zakaz3

where dbo.zakaz3.data_zakaza BETWEEN '2013-05-05' AND '2013-05-07'

 

Рисунок 3.24 - Запрос 24

 

9. Количество продуктов, место на складе 1

 

select  count(dbo.product.product_id)'Количество телефонов'

from dbo.product

where dbo.product.mesto_na_sklade = '1'

 

Рисунок 3.25 - Запрос 25

 

10. Сумма всех заказов

 

select SUM(dbo.Zakaz3.cena_bluda_za_zakaz_rub)'сумма всех заказов'

from klient_bludo1, zakaz3, klient

where dbo.zakaz3.zakaz_id=dbo.klient_bludo1.klient_bludo_id and dbo.klient_bludo1.klient_bludo_id=dbo.klient.klient_id

 

Рисунок 3.26 - Запрос 26

 

11. Количество полок, номер которых оканчивается на 1

 

select COUNT(sklad_id)'Количество полок'

from sklad

where sklad_id like '%1'

 

Рисунок 3.27 - Запрос 27

 

12. Максимальная скорость приготовления блюда в номер, которого оканчивается на 5

 

select MAX(time_prigotovlenia_min) 'Максимальная скорость приготовления'

from bludo1

where bludo_id like '%5'

 

Рисунок 3.28 - Запрос 28

 

13.  Количество клиентов по адресу Комбинтская

 

select  count(dbo.klient.klient_id)'Количество клиентов'

from dbo.klient

where dbo.klient.adres_dostavki like 'Комбинтская%'

 

Рисунок 3.29 - Запрос 29

 

14.  Количество поваров, которые готовят блюда с определенной скоростью

 

select time_prigotovlenia_min, count(dbo.povar.povar_id)'кол-во поваров'

from bludo1, povar, recept, recept_product, povar_recept

where dbo.recept_product.recept_id=dbo.recept.recept_id

and dbo.recept_product.recept_product_id=dbo.bludo1.recept_product_id

and dbo.povar.povar_id=dbo.povar_recept.povar_id

and dbo.povar_recept.recept_id=dbo.recept.recept_id

group by time_prigotovlenia_min

 

Рисунок 3.30 - Запрос 30

 

15. Количество всех заказов

 

select count(dbo.zakaz3.zakaz_id) 'Количество заказов'

from Zakaz3

 

Рисунок 3.31 - Запрос 31

 

3. Запросы с Having

 

1. Количество поваров, которые готовят первое блюдо

 

select dbo.bludo1.tip, count(dbo.povar.povar_id)'количество поваров'

from dbo.bludo1, dbo.povar, dbo.recept, dbo.recept_product, dbo.povar_recept

where dbo.bludo1.recept_product_id =  dbo.recept_product.recept_product_id

AND dbo.recept.recept_id =  dbo.recept_product.recept_id

AND dbo.povar.povar_id = dbo.povar_recept.povar_id

and dbo.povar_recept.recept_id =dbo.recept.recept_id

group by dbo.bludo1.tip

HAVING dbo.bludo1.tip='Первое блюдо'

 

Рисунок 3.32 - Запрос 32

 

2. Количество заказанных роллов Филадельфия

 

select dbo.bludo1.name_bludo, sum(dbo.Zakaz3.cena_bluda_za_zakaz_rub) 'Сумма всех заказанных салатов'

from dbo.bludo1, dbo.zakaz3, dbo.klient, dbo.klient_bludo1

where dbo.klient_bludo1.klient_id = dbo.klient.klient_id

AND dbo.Zakaz3.klient_bludo_id = dbo.klient_bludo1.klient_bludo_id

AND dbo.bludo1.bludo_id = dbo.klient_bludo1.bludo_id

group by dbo.bludo1.name_bludo

HAVING dbo.bludo1.name_bludo='Салат Оливье'

 

Рисунок 3.33 - Запрос 33

 

3. Количество заказов в 2013 году

 

select data_zakaza, COUNT(dbo.Zakaz3.zakaz_id)'Кол-во заказов'

from dbo.zakaz3

group by data_zakaza

HAVING dbo.Zakaz3.data_zakaza LIKE '2013%'

 

Рисунок 3.34 - Запрос 34

 

4. Количество продуктов количество срока дней, которых оканчивается на 4

 

select srok_dney, COUNT(dbo.product.product_id)'Количество продуктов'

from product

group by srok_dney

having srok_dney like '%4'

 

Рисунок 3.35 - Запрос 35

 

5. Сумма заказов за 2013 год

select data_zakaza, sum(dbo.Zakaz3.cena_bluda_za_zakaz_rub)'Сумма заказов'

from dbo.zakaz3

group by data_zakaza

HAVING dbo.Zakaz3.data_zakaza LIKE '2013-05-03'

 

Рисунок 3.35 - Запрос 35

 

4. Запросы с выбором текста

 

1. Показать адрес клиента с номером 6

 

select adres_dostavki

from dbo.klient

where klient_id = 6;

 

Рисунок 3.36 - Запрос 36

 

2. Показать код заказа и цену заказа у клиента под номером 3

 

select zakaz_id, cena_bluda_za_zakaz_rub

from dbo.zakaz3, dbo.klient, dbo.klient_bludo1

where dbo.klient.klient_id=3 and dbo.klient.klient_id=dbo.klient_bludo1.klient_id and dbo.klient_bludo1.klient_bludo_id=dbo.zakaz3.klient_bludo_id

 

Рисунок 3.37 - Запрос 37

 

3. Имя повара блюда салат греческий

 

select name_povar

from dbo.povar, dbo.recept_product, dbo.bludo1, dbo.povar_recept, dbo.recept

where dbo.bludo1.name_bludo='Салат греческий' and dbo.povar.povar_id=dbo.povar_recept.povar_id and dbo.recept.recept_id=dbo.povar_recept.recept_id and dbo.bludo1.recept_product_id=dbo.recept_product.recept_product_id and dbo.recept_product.recept_id=dbo.recept.recept_id

 

Рисунок 3.38 - Запрос 38

 

4. Выбор названия ассоциации где все поставки

 

select dbo.assocciacia_shef_povarov.name_asp, dbo.Partner.uslugi

from dbo.Partner, dbo.assocciacia_shef_povarov

where dbo.Partner.asp_id = dbo.assocciacia_shef_povarov.asp_id

AND dbo.Partner.uslugi LIKE 'Поставка%'

ORDER BY name_asp;

 

Рисунок 3.39 - Запрос 39

 

5. Выбор заказов, которые были составлены между 1-м мая 2013г. и 3-м мая 2013г и подсчет их количества

 

select dbo.zakaz3.zakaz_id, dbo.zakaz3.data_zakaza

from dbo.zakaz3

where dbo.zakaz3.data_zakaza BETWEEN '2013-05-01' AND '2013-05-03'

 

Рисунок 3.40 - Запрос 40

 

6. Блюдо и дата заказа по номеру блюда

 

select dbo.bludo1.name_bludo, dbo.zakaz3.data_zakaza

from dbo.bludo1, dbo.zakaz3, dbo.klient, dbo.klient_bludo1

where dbo.bludo1.bludo_id = '5'

AND dbo.klient_bludo1.klient_id = dbo.klient.klient_id

AND dbo.Zakaz3.klient_bludo_id = dbo.klient_bludo1.klient_bludo_id

AND dbo.bludo1.bludo_id = dbo.klient_bludo1.bludo_id

ORDER BY name_bludo

 

Рисунок 3.41 - Запрос 41

 

7. Дата заказа роллов Филадельфия и салата Оливье

 

select dbo.bludo1.name_bludo, dbo.zakaz3.data_zakaza

from dbo.bludo1, dbo.zakaz3, dbo.klient, dbo.klient_bludo1

where (dbo.bludo1.name_bludo='роллы Филадельфия' or dbo.bludo1.name_bludo='Салат оливье')

AND dbo.klient_bludo1.klient_id = dbo.klient.klient_id

AND dbo.Zakaz3.klient_bludo_id = dbo.klient_bludo1.klient_bludo_id

AND dbo.bludo1.bludo_id = dbo.klient_bludo1.bludo_id

 

Рисунок 3.42 - Запрос 42

 

 

 

8. Имена поваров по типу блюда

 

select dbo.bludo1.tip, dbo.povar.name_povar

from dbo.bludo1, dbo.povar, dbo.recept, dbo.recept_product, dbo.povar_recept

where dbo.bludo1.tip='Второе блюдо'

AND dbo.bludo1.recept_product_id =  dbo.recept_product.recept_product_id

AND dbo.recept.recept_id =  dbo.recept_product.recept_id

AND dbo.povar.povar_id = dbo.povar_recept.povar_id

and dbo.povar_recept.recept_id =dbo.recept.recept_id

 

Рисунок 3.44 -  Запрос 44

 

9. Имя повара, который готовит вторые блюда со временем приготовления 40 минут

 

select dbo.povar.name_povar

from dbo.bludo1, dbo.povar, dbo.recept, dbo.recept_product, dbo.povar_recept

where dbo.bludo1.tip='Второе блюдо' and dbo.bludo1.time_prigotovlenia_min='40'

AND dbo.bludo1.recept_product_id =  dbo.recept_product.recept_product_id

AND dbo.recept.recept_id =  dbo.recept_product.recept_id

AND dbo.povar.povar_id = dbo.povar_recept.povar_id

and dbo.povar_recept.recept_id =dbo.recept.recept_id

 

Рисунок 3.45 - Запрос 45

 

1.4.10.Название ассоциации с поваром Иванова Кристина Николаевна

 

select dbo.assocciacia_shef_povarov.name_asp

from dbo.assocciacia_shef_povarov, dbo.povar, dbo.shef

where dbo.povar.name_povar='Иванова Кристина Николаевна'

AND dbo.assocciacia_shef_povarov.asp_id =  dbo.shef.asp_id

AND dbo.shef.shef_id =  dbo.povar.shef_id

 

Рисунок 3.46 - Запрос 46

Представления.

 

5. Создается представление, которое содержит часть информации о продукте, который хранится на полке 4

 

go

create view dbo.OProducte as

select dbo.product.product_id, dbo.product.srok_dney

from dbo.product, dbo.sklad, dbo.product_sklad

where dbo.product.product_id = dbo.product_sklad.product_id

AND dbo.sklad.sklad_id = dbo.product_sklad.sklad_id

AND dbo.sklad.polka_na_sklade = '4';

 

6. Создается представление, которое содержит часть информации о ассоциации шеф-поваров

go

create view dbo.InfaOassociacii_ as

select dbo.assocciacia_shef_povarov.name_asp,name_shef, fio, uslugi

from dbo.shef, dbo.assocciacia_shef_povarov, dbo.partner

where dbo.shef.asp_id = dbo.assocciacia_shef_povarov.asp_id

AND dbo.assocciacia_shef_povarov.asp_id = dbo.partner.asp_id;

 

7. Создается представление, которое содержит часть информации о каждом клиенте

 

go

create view dbo.InfaOKliente1 as

select dbo.klient.klient_id, adres_dostavki ,name_bludo, data_zakaza

from dbo.klient, dbo.bludo1, dbo.klient_bludo1, dbo.zakaz3

where dbo.klient.klient_id = dbo.klient_bludo1.klient_id

AND dbo.klient_bludo1.bludo_id = dbo.bludo1.bludo_id

AND dbo.klient_bludo1.klient_bludo_id = dbo.zakaz3.klient_bludo_id;

Процедуры

8. Процедура выводит наименование блюда по коду

 

go

create procedure NameBludoPoKODy4 @nomer int  as

select bludo_id, name_bludo

from bludo1

where bludo_id = @nomer;

go

exec NameBludoPoKODy4 @nomer = 2;

 

Рисунок 5.1 - Процедура 1

 

9. Процедура выводит код рецепта по его названию

 

go

create procedure dbo.KodReceptaPoNaime @Name varchar(max) as

select dbo.recept.recept_id, dbo.recept.name_recept

from dbo.recept

where dbo.recept.name_recept = @Name;

go

exec dbo.KodReceptaPoNaime @Name = 'Рецепт Салат греческий';

 

Рисунок 5.2 - Процедура 2

 

10. Процедура выводит по коду блюда всех клиентов заказавших его

 

go

create procedure dbo.KodBludaPoKlientam @Kod tinyint as

select dbo.bludo1.bludo_id, dbo.klient.adres_dostavki

from dbo.klient, dbo.bludo1, dbo.klient_bludo1

where dbo.klient_bludo1.bludo_id=dbo.bludo1.bludo_id

AND dbo.klient_bludo1.klient_id=dbo.klient.klient_id

AND dbo.bludo1.bludo_id = @Kod

ORDER BY bludo_id;

go

exec dbo.KodBludaPoKlientam @Kod = 8;

 

Рисунок 5.3 - Процедура 3

 

11. Процедура выводит по адресу заказа все блюда

 

create procedure dbo.BludoPoAdresaZakaza1 @Adres varchar(max) as

select dbo.bludo1.name_bludo, dbo.klient.adres_dostavki

from dbo.bludo1, dbo.klient, dbo.klient_bludo1

where

dbo.bludo1.bludo_id = dbo.klient_bludo1.bludo_id

AND dbo.klient_bludo1.klient_id = dbo.klient.klient_id

AND dbo.klient.adres_dostavki = @Adres

ORDER BY name_bludo;

go

exec dbo.BludoPoAdresaZakaza1 @Adres = 'Комбинтская,3';

 

Рисунок 5.4 - Процедура 4

 

12. Процедура выводит всю информацию о конкретной ассоциации dbo.InfaOassociacii (представление)

 

go

create procedure dbo.InfaOassociacii2 @name_asp varchar(max) as

select *

from dbo.InfaOassociacii_

where name_asp = @name_asp;

go

exec dbo.InfaOassociacii2 @name_asp = 'Новосибирская ассоциация';

 

Рисунок 5.5 - Процедура 5

 

13. Процедура выводит всю информацию о конкретном клиенте dbo.InfaOKliente11 (представление)

 

go

create procedure dbo.InfaOKliente11 @kliente int as

select *

from dbo.InfaOKliente1

where klient_id = @kliente;

go

exec dbo.InfaOKliente11 @kliente = '1';