Запросы с использованием нескольких таблиц

Запросы с использованием нескольких таблиц

Поставки

ПС	ПР	Цена	К-во
		2р.
		3р.
		2р.
		4р.
		2р.
		4р.
		0р.
		0р.
		3р.
		2р.
		2р.
		1р.
		1р.
		5р.
		3р.
		0р.
		1р.
		1р.
		1р.
		1р.
		1р.
		4р.
		4р.
		2р.
		4р.
		2р.
		1р.

Поставщики

ПС	Название	Статус	Город	Адрес	Телефон
	СЫТНЫЙ	рынок	С_Петербург	Сытнинская,3
	ПОРТОС	кооператив	Резекне	Садовая,27
	ШУШАРЫ	совхоз	Пушкин	Новая,17
	ТУЛЬСКИЙ	универсам	С_Петербург	Тульская,3
	УРОЖАЙ	коопторг	Луга	Песчаная,19
	ЛЕТО	агрофирма	С_Петербург	Пулковское ш.8
	ОГУРЕЧИК	ферма	Паневежис	Укмерге,15
	КОРЮШКА	кооператив	Йыхви	Нарвское шоссе,64

Продукты

ПР	Продукт	Белки	Жиры	Углев	К	СА	Na	В2	РР	С
	Говядина							1,5
	Судак							1,1
	Масло							0,1
	Майонез
	Яйца							4,4	1,9
	Сметана
	Молоко							1,3
	Творог							2,7
	Морковь							0,7	9,9
	Лук							0,2
	Помидоры							0,4	5,3
	Зелень							1,2
	Рис							0,4
	Мука							1,2
	Яблоки							0,3
	Сахар
	Кофе							0,3	1,8

Вид_блюд

В	Вид
З	Закуска
С	Суп
Г	Горячее
Д	Десерт
Н	Напиток

Блюда

БЛ	Блюдо	В	Основа	Выход	Труд
	Салат летний	З	Овощи
	Салат мясной	З	Мясо
	Салат витаминный	З	Овощи
	Салат рыбный	З	Рыба
	Паштет из рыбы	З	Рыба
	Мясо с гарниром	З	Мясо
	Сметана	З	Молоко
	Творог	З	Молоко
	Суп харчо	С	Мясо
	Суп-пюре из рыбы	С	Рыба
	Уха из судака	С	Рыба
	Суп молочный	С	Молоко
	Бастурма	Г	Мясо
	Бефстроганов	Г	Мясо
	Судак по польскиГ	Г	Рыба
	Драчена	Г	Яйца
	Морковь с рисом	Г	Овощи
	Сырники	Г	Молоко
	Омлет с луком	Г	Яйца
	Каша рисовая	Г	Крупа
	Пудинг рисовый	Г	Крупа
	Вареники ленивые	Г	Молоко
	Помидоры с луком	Г	Овощи
	Суфле из творога	Г	Молоко
	Рулет с яблоками	Д	Фрукты
	Яблоки печеные	Д	Фрукты
	Суфле яблочное	Д	Фрукты
	Крем творожный	Д	Молоко
	Утро	Н	Фрукты
	Компот	Н	Фрукты
	Молдочны напиток	Н	Молоко
	Кофе черный	Н	Кофе
	Кофе на молоке	Н	Кофе

Выбор

СМ	Т	В	БЛ
		З
		Г
		Н
		З
		С
		Г
		Д
		З
		Г
		Н

Выбрано

СМ	Т	БЛ

Меню

Т	В	БЛ
	З
	З
	Г
	Г
	Н
	Н
	З
	З
	С
	С
	Г
	Г
	Г
	Д
	Д
	З
	З
	Г
	Г
	Н
	Н

Трапезы

Т	Трапеза
	Завтрак
	Обед
	Ужин

Наличие

ПР	К_во	Стоим
		430р.
		0р.
		275р.
		97р.
		112р.
		207р.
		83р.
		83р.
		0р.
		46р.
		52р.
		35р.
		51р.
		44р.
		190р.
		96р.
		167р.

Рецепты

БЛ	Рецепт
	Помидоры
	Вареное….
	Зелень ме…
	Вареные р
	Филе судака
	Мясо вареное
	Сметану п..
	Протертый
	Грудинку
	Филе судака
	Судак очи..
	Промытый
	Мясо нарез..
	Говядину
	Подготовл…
	Сырые яйца
	Нарезать
	В протертую..
	К свежим
	Рис свари..
	Готовую р..
	В протерт..
	Спассеров..
	В протер..
	Очистить
	Не прорез..
	Запечен…
	Яйца разм…
	Очищенную..
	Яблоки очи..
	Яблоки нар..
	Кофевапку..
	Сварить ч…

Состав

БЛ	ПР	Вес

Запросы с использованием нескольких таблиц

SELECT Продукт, Цена, Название, Статус

FROM Продукты, Состав, Блюда, Поставки, Поставщики

WHERE Продукты.ПР=Состав.ПР

AND Состав.БЛ= Блюда.БЛ

AND Поставки.ПР=Состав.ПР

AND Поставки.ПС=Поставщики.ПС

AND Блюдо LIKE 'Сырники'

AND Поставки.Цена IS NOT NULL;

Результат

Продукт	Цена	Название	Статус
Яйца	1.8р.	ПОРТОС	кооператив
Сметана	3.6р	ПОРТОС	кооператив
Сахар	0.94р.	ТУЛЬСКИЙ	универсам
Мука	0.5р.	УРОЖАЙ	коопторг
Сахар	1р.	УРОЖАЙ	коопторг
Масло	4р.	ОГУРЕЧИК	ферма
Сметана	2.2р.	ОГУРЕЧИК	ферма
Яйца	2р.	КОРЮШКА	кооператив

Альтернативный вариант запроса без LIKE

SELECT Продукт, Цена, Название, Статус

FROM Продукты, Состав, Блюда, Поставки, Поставщики

WHERE Продукты.ПР=Состав.ПР

AND Состав.БЛ= Блюда.БЛ

AND Поставки.ПР=Состав.ПР

AND Поставки.ПС=Поставщики.ПС

AND Блюдо= 'Сырники'

AND Поставки.Цена IS NOT NULL;

Результат идентичен

Продукт	Цена	Название	Статус
Яйца	1.8р.	ПОРТОС	кооператив
Сметана	3.6р	ПОРТОС	кооператив
Сахар	0.94р.	ТУЛЬСКИЙ	универсам
Мука	0.5р.	УРОЖАЙ	коопторг
Сахар	1р.	УРОЖАЙ	коопторг
Масло	4р.	ОГУРЕЧИК	ферма
Сметана	2.2р.	ОГУРЕЧИК	ферма
Яйца	2р.	КОРЮШКА	кооператив

Механизм вложенного подзапроса

SELECT Продукт, Цена, Название, Статус

FROM Продукты, Состав, Блюда, Поставки, Поставщики

WHERE Продукты.ПР=Состав.ПР

AND Состав.БЛ= Блюда.БЛ

AND Поставки.ПР=Состав.ПР

AND Поставки.ПС=Поставщики.ПС

AND Блюдо LIKE 'Сырники'

AND Цена = (SELECT MIN(цена)

FROM Поставки Х

WHERE Х.ПР = Поставки.ПР);

Результат

Продукт	Цена	Название	Статус
Яйца	1.8р.	ПОРТОС	кооператив
Сахар	0.94р.	ТУЛЬСКИЙ	универсам
Мука	0.5р.	УРОЖАЙ	коопторг
Сметана	2.2р.	ОГУРЕЧИК	ферма
Масло	4р.	ОГУРЕЧИК	ферма

Запросы использующие соединения

(Соединения- это подмножества декартова произведения. Это - таблица,

содержащая строки,образующие уникальные комбинации строк исходных таблиц)

SELECT Вид_блюд.*, Трапезы.*

FROM Вид_блюд, Трапезы;

Результат

В	Вид	Т	Трапеза
З	Закуска		Завтрак
З	Закуска		Обед
З	Закуска		Ужин
С	Суп		Завтрак
С	Суп		Обед
С	Суп		Ужин
Г	Горячее		Завтрак
Г	Горячее		Обед
Г	Горячее		Ужин
Д	Десерт		Завтрак
Д	Десерт		Обед
Д	Десерт		Ужин
Н	Напиток		Завтрак
Н	Напиток		Обед
Н	Напиток		Ужин

SELECT Вид_блюд.*, Трапезы.*, Блюда.*

FROM Вид_блюд, Трапезы, Блюда

WHERE Блюда.Блюдо LIKE 'Салат р*';

Результат

Вид_блюд.В	Вид	Т	Трапеза	БЛ	Блюдо	Блюда.В	Основа	Выход	Труд
З	Закуска		Завтрак		Салат рыбный	З	Рыба
З	Закуска		Обед		Салат рыбный	З	Рыба
З	Закуска		Ужин		Салат рыбный	З	Рыба
С	Суп		Завтрак		Салат рыбный	З	Рыба
С	Суп		Обед		Салат рыбный	З	Рыба
С	Суп		Ужин		Салат рыбный	З	Рыба
Г	Горячее		Завтрак		Салат рыбный	З	Рыба
Г	Горячее		Обед		Салат рыбный	З	Рыба
Г	Горячее		Ужин		Салат рыбный	З	Рыба
Д	Десерт		Завтрак		Салат рыбный	З	Рыба
Д	Десерт		Обед		Салат рыбный	З	Рыба
Д	Десерт		Ужин		Салат рыбный	З	Рыба
Н	Напиток		Завтрак		Салат рыбный	З	Рыба
Н	Напиток		Обед		Салат рыбный	З	Рыба
Н	Напиток		Ужин		Салат рыбный	З	Рыба

SELECT Трапеза, Вид, Блюдо, Основа, Выход, Труд

FROM Меню, Трапезы, Вид_блюд, Блюда

WHERE Меню.Т = Трапезы.Т

AND Меню.В = Вид_блюд.В

AND Меню.БЛ = Блюда.БЛ;

Результат

Трапеза	Вид	Блюдо	Основа	Выход	Труд
Обед	Закуска	Мясо с гарниром	Мясо
Завтрак	Закуска	Салат витаминный	Овощи
Обед	Закуска	Салат летний	Овощи
Завтрак	Закуска	Мясо с гарниром	Мясо
Ужин	Закуска	Мясо с гарниром	Мясо
Ужин	Закуска	Творог	Молоко
Обед	Суп	Суп харчо	Мясо
Обед	Суп	Суп молочный	Молоко
Обед	Горячее	Сырники	Молоко
Завтрак	Горячее	Омлет с луком	Яйца
Завтрак	Горячее	Пудинг рисовый	Крупа
Обед	Горячее	Драчена	Яйца
Обед	Горячее	Бефстроганов	Мясо
Ужин	Горячее	Драчена	Яйца
Ужин	Горячее	Каша рисовая	Крупа
Обед	Десерт	Крем творожный	Молоко
Обед	Десерт	Яблоки печеные	Фрукты
Ужин	Напиток	Молдочны напиток	Молоко
Завтрак	Напиток	Кофе черный	Кофе
Завтрак	Напиток	Молдочны напиток	Молоко
Ужин	Напиток	Компот	Фрукты

Этот же запрос,но с упорядочиванием выборки по столбцу "Трапеза"

SELECT Трапеза, Вид, Блюдо, Основа, Выход, Труд

FROM Меню, Трапезы, Вид_блюд, Блюда

WHERE Меню.Т = Трапезы.Т

AND Меню.В = Вид_блюд.В

AND Меню.БЛ = Блюда.БЛ

ORDER BY Трапеза;

Результат

Трапеза	Вид	Блюдо	Основа	Выход	Труд
Завтрак	Горячее	Пудинг рисовый	Крупа
Завтрак	Закуска	Салат витаминный	Овощи
Завтрак	Закуска	Мясо с гарниром	Мясо
Завтрак	Горячее	Омлет с луком	Яйца
Завтрак	Напиток	Молдочны напиток	Молоко
Завтрак	Напиток	Кофе черный	Кофе
Обед	Закуска	Мясо с гарниром	Мясо
Обед	Закуска	Салат летний	Овощи
Обед	Суп	Суп харчо	Мясо
Обед	Горячее	Драчена	Яйца
Обед	Горячее	Сырники	Молоко
Обед	Десерт	Яблоки печеные	Фрукты
Обед	Горячее	Бефстроганов	Мясо
Обед	Десерт	Крем творожный	Молоко
Обед	Суп	Суп молочный	Молоко
Ужин	Напиток	Молдочны напиток	Молоко
Ужин	Напиток	Компот	Фрукты
Ужин	Закуска	Творог	Молоко
Ужин	Закуска	Мясо с гарниром	Мясо
Ужин	Горячее	Драчена	Яйца
Ужин	Горячее	Каша рисовая	Крупа

Вложенные подзапросы

SELECT Название, Статус

FROM Поставщики

WHERE ПС IN

(SELECT ПС

FROM Поставки

WHERE ПР = 11);

Название	Статус
СЫТНЫЙ	рынок
УРОЖАЙ	коопторг
ЛЕТО	агрофирма
КОРЮШКА	кооператив

Этот же результат может быть получен с использованием многоуровневой

вложенности (см. ниже)

SELECT Название, Статус

FROM Поставщики

WHERE ПС IN

(SELECT ПС

FROM Поставки

WHERE ПР IN

(SELECT ПР

FROM Продукты

WHERE Продукт = 'Помидоры'));

Вложенный запрос без IN

SELECT ПС, Название, Город

FROM Поставщики

WHERE Город =

(Select Город

From Поставщики

Where ПС= 6);

ПС	Название	Город
	СЫТНЫЙ	С_Петербург
	ТУЛЬСКИЙ	С_Петербург
	ЛЕТО	С_Петербург

Квантор NOT EXISTS (не существует).

Имеет место,если (Select ….) не пустое множество

SELECT Название, Город, Статус

FROM Поставщики

WHERE NOT EXISTS

(Select *

From Поставки

Where ПС= Поставщики.ПС

And ПР = 11);

Название	Город	Статус
ПОРТОС	Резекне	кооператив
ШУШАРЫ	Пушкин	совхоз
ТУЛЬСКИЙ	С_Петербург	универсам
ОГУРЕЧИК	Паневежис	ферма

Использование функции в подзапросе

SELECT Продукт, Цена, Название, Статус

FROM Продукты, Состав, Блюда, Поставки, Поставщики

WHERE Продукты.ПР = Состав.ПР

AND Состав.БЛ = Блюда.БЛ

AND Поставки.ПР = Состав.ПР

AND Поставки.ПС = Поставщики.ПС

AND Блюдо = 'Сырники'

AND Цена = (Select MIN(Цена)

FROM Поставки X

Where X.ПР = Поставки.ПР);

Продукт	Цена	Название	Статус
Яйца	2р.	ПОРТОС	кооператив
Сахар	1р.	ТУЛЬСКИЙ	универсам
Мука	1р.	УРОЖАЙ	коопторг
Сметана	2р.	ОГУРЕЧИК	ферма
Масло	4р.	ОГУРЕЧИК	ферма

Объединение (UNION)

SELECT Продукт

FROM Продукты

WHERE Жиры = 0

UNION (SELECT БЛ

FROM Состав

WHERE БЛ = 1);

Продукт

Зелень

Лук

Помидоры

Сахар

Яблоки

Вопросы

1. Выдать названия всех мясных блюд

2. Выдать количество всех блюд, в состав которых входят "Помидоры"

3. Выдать блюда, продукты для которых поставляет агрофирма "ЛЕТО"

SELECT Вид, Блюдо, ' калорий - ', (Углев+Белки)*4.1+Жиры*9.3

FROM Блюда, Вид_блюд, Продукты, Состав, Нали_ие

WHERE Блюда.БЛ = Состав.БЛ

AND Состав.ПР = Продукты.ПР

AND Состав.ПР = Нали_ие.ПР

AND Блюда.В = Вид_блюд.В

AND Состав.БЛ NOT IN

(SELECT БЛ

FROM Состав

WHERE Состав.ПР IN

(SELECT ПР

FROM Нали_ие

WHERE Нали_ие.К_во = 0))

AND ((Углев+Белки)*4.1+Жиры*9.3)<1400

ORDER BY Вид;

Вид	Блюдо	Expr1002	Expr1003
Горячее	Бастурма	калорий -	459,2
Горячее	Бастурма	калорий -	118,9
Горячее	Бастурма	калорий -	196,8
Горячее	Помидоры с луком	калорий -	196,8
Горячее	Омлет с луком	калорий -	459,2
Горячее	Помидоры с луком	калорий -	459,2
Горячее	Бефстроганов	калорий -	118,9
Горячее	Драчена	калорий -	605,1
Горячее	Бефстроганов	калорий -	605,1
Горячее	Каша рисовая	калорий -	479,7
Горячее	Омлет с луком	калорий -	605,1
Горячее	Суфле из творога	калорий -	605,1
Горячее	Бефстроганов	калорий -	459,2
Горячее	Каша рисовая	калорий -	605,1
Десерт	Суфле яблочное	калорий -	479,7
Десерт	Яблоки печеные	калорий -	479,7
Десерт	Рулет с яблоками	калорий -	479,7
Десерт	Суфле яблочное	калорий -	605,1
Закуска	Мясо с гарниром	калорий -	196,8
Закуска	Салат летний	калорий -	196,8
Закуска	Салат витаминный	калорий -	118,9
Закуска	Салат витаминный	калорий -	479,7
Закуска	Салат витаминный	калорий -	196,8
Закуска	Салат летний	калорий -	479,7
Закуска	Салат витаминный	калорий -	459,2
Закуска	Салат летний	калорий -	118,9
Закуска	Мясо с гарниром	калорий -	118,9
Напиток	Кофе на молоке	калорий -	605,1
Напиток	Молдочны напиток	калорий -	479,7
Напиток	Кофе на молоке	калорий -	892,4
Напиток	Кофе черный	калорий -	892,4
Напиток	Компот	калорий -	479,7
Напиток	Молдочны напиток	калорий -	605,1
Суп	Суп харчо	калорий -	459,2
Суп	Суп харчо	калорий -	196,8
Суп	Суп харчо	калорий -	118,9
Суп	Суп молочный	калорий -	605,1

Реализация функций ЯОД средствами SQL

CREATE TABLE базовая таблица (имя столбца тип_данных [ NOT NULL ] ….)

CREATE TABLE Временная

(Вид CHAR (8),

Блюдо CHAR (8),

Рецепт CHAR (8));

Существующую таблицу можно уничтожить с помощью предложения

DROP TABLE < имя таблицы >

Можно к существующим столбцам таблицы добавить справа новый с помощью

Предложения:

ALTER TABLE базовая таблица (имя столбца тип данных)

INSERT INTO Временная

SELECT Вид AS Вид, Блюдо AS Блюдо, Рецепт AS Рецепт

FROM Блюда, Рецепты, Вид_блюд

WHERE Блюда.БЛ = Рецепты.БЛ

AND Блюда.В = Вид_блюд.В;

Вид	Блюдо	Рецепт
Закуска	Мясо с г	Мясо вар
Закуска	Сметана	Сметану
Закуска	Салат ле	Помидоры
Закуска	Творог	Протерты
Закуска	Салат ры	Вареные
Закуска	Паштет и	Филе суд
Закуска	Салат мя	Вареное…
Закуска	Салат ви	Зелень м
Суп	Суп харч	Грудинку
Суп	Суп-пюре	Филе суд
Суп	Суп моло	Промытый
Суп	Уха из с	Судак оч
Горячее	Сырники	В протер
Горячее	Бастурма	Мясо нар
Горячее	Бефстрог	Говядину
Горячее	Судак по	Подготов
Горячее	Морковь	Нарезать
Горячее	Омлет с	К свежим
Горячее	Каша рис	Рис свар
Горячее	Пудинг р	Готовую
Горячее	Вареники	В протер
Горячее	Помидоры	Спассеро
Горячее	Суфле из	В протер
Горячее	Драчена	Сырые яй
Десерт	Рулет с	Очистить
Десерт	Яблоки п	Не проре
Десерт	Суфле яб	Запечен…
Десерт	Крем тво	Яйца ра
Напиток	Компот	Яблоки о
Напиток	Кофе чер	Кофевапк
Напиток	Утро	Очищенну
Напиток	Кофе на	Сварить
Напиток	Молдочны	Яблоки н

UPDATE Временная SET Вид = 'Зак '

WHERE Вид=ltrim(rtrim('Закуска'));

Вид	Блюдо	Рецепт
Зак	Мясо с г	Мясо вар
Зак	Сметана	Сметану
Зак	Салат ле	Помидоры
Зак	Творог	Протерты
Зак	Салат ры	Вареные
Зак	Паштет и	Филе суд
Зак	Салат мя	Вареное…
Зак	Салат ви	Зелень м
Суп	Суп харч	Грудинку
Суп	Суп-пюре	Филе суд
Суп	Суп моло	Промытый
Суп	Уха из с	Судак оч
Горячее	Сырники	В протер
Горячее	Бастурма	Мясо нар
Горячее	Бефстрог	Говядину
Горячее	Судак по	Подготов
Горячее	Морковь	Нарезать
Горячее	Омлет с	К свежим
Горячее	Каша рис	Рис свар
Горячее	Пудинг р	Готовую
Горячее	Вареники	В протер
Горячее	Помидоры	Спассеро

Дата добавления: 2015-08-29