автор: Хрущева Лариса Гавриловна
Преподаватель ГАПОУ «Международный центр компетенций — Казанский техникум информационных технологий и связи»
Методические рекомендации для выполнения проектной работы по дисциплине ОП.08 «Основы проектирования баз данных»
Методические рекомендации для выполнения проектной работы по дисциплине ОП.08 «Основы проектирования баз данных» для студентов специальности 09.02.07 «Информационные системы и программирование»
Автор: преподаватель ГАПОУ «МЦК-КТИТС»
Хрущева Лариса Гавриловна
Аннотация: Методические рекомендации составлены для помощи студентам в составлении отчета по дисциплине 08 «Основы проектирования баз данных». Отчетом является проектная работа, которая выполняется по этапам в течении всего семестра. Основой ее являются практические работы. Цель проектной работы заключается в оформлении отчета о спроектированной базе данных по вариантам. В методических рекомендациях есть план выполнения проекта, варианты и пример проекта, выполненный преподавателем. Защита проектной работы может быть использована как промежуточный контроль по дисциплине.
Задание. Необходимо составить отчет о проделанной работе по проектированию базы данных.
Работа должна содержать:
- Описание предметной области
- Таблица с объектами предметной области, Таблица со связями между
- ER-диаграмма
- Словарь данных
- Исходные данные
- Скриншоты таблиц баз данных, созданные в SQL Server
- Схема БД, созданная в SQL Server
- Словесные запросы и запросы на T-SQL из всех практических работ
- Скриншоты с запросами и ответами (по 2 запроса наиболее сложных из каждой работы. Всего 10)
Пример выполнения проектной работы
- Описание предметной области
Производство молочных продуктов
В городе существует несколько предприятий, которые выпускают разные молочные продукты разных видов (например, кефир, ряженка, сметана) в соответствии с планом выпуска. Для производства молочных продуктов требуется различное сырье. На предприятиях работают сотрудники, которые имеют разные должности. На одном предприятии работает много сотрудников, но один сотрудник может работать только на одном предприятии.
- Таблицы «Объекты и их характеристики» и «Связи между объектами»
На основании анализа предметной области «Производство молочных продуктов» в ней были выделены объекты, их характеристики (Таблица 1) и определены связи (Таблица 2) между объектами 1:М и М:М. Для связей М:М определен дополнительный объект, которого нет в таблице 1.
Таблица 1
Объекты и их характеристики
| Название объекта | Атрибуты объекта | Первичный ключ |
| Предприятие | №Пред, наименование, рейтинг, город | №Пред |
| Молочный продукт | №Прод, название, цена, №вида | №Прод |
| Сырье | №Сырья, название | №Сырья |
| Сотрудник | Таб. Номер, Фамилия, Имя, Отчество, Оклад, Надбавка, №Пред, №Должности | Таб. Номер |
| Вид продукта | №Вида, название | №Вида |
| Должность | №Должности, наименование, РейтингДолжности | №Должности |
| ПланВыпуска | №Договора, №Пред, №Прод, количество, дата заключения | №Договора |
| Изготовление | №Прод, №Сырья | (№Прод, №Сырья |
Таблица 2
Связи между объектами
| Номер | Объект1 | Объект2 | Словесное описание | Тип связи | Доп. объект |
| 1 | Предприятие | План выпуска | У одного предприятия много планов выпуска | 1:М | |
| 2 | Предприятие | Сотрудник | На одном предприятии работает много сотрудников, но один сотрудник может работать только на одном предприятии. | 1:М | |
| 3 | Сотрудник | Должность | Сотрудник имеет одну должность, но на одной должности может быть много сотрудников | 1:М | |
| 4 | Продукт | План выпуска | Продукт может выпускаться по многим планам выпуска | 1:М | |
| 5 | Продукт | Вид | Продукт может быть одного вида, но существует множество продуктов одного вида | 1:М | |
| 6 | Продукт | Сырье | Продукт состоит из нескольких видов сырья, и одно сырье используется для производства многих продуктов | М:М | Сырье_Продукт (Изготовление) |
- ER-диаграмма
ER- диаграмма составлена в программе VISIO. В ней выделены все сущности, их реквизиты, первичные и внешние ключи, указаны все связи.
- Схема НБД
Предприятие (№Пред, Название, Рейтинг, Город)
Продукт (№Прод, Название, цена, №вида)
Вид (№Вида, Наименование)
Сотрудник (ТабНом, Фамилия, Имя, Отчество, оклад, надбавка, №Пред, №Должности)
Должность (№Должности, наименование, РейтингДолжности)
ПланВыпуска (№Договора, №Пред, №Прод, количество, дата заключения)
Изготовление (№Прод, №Сырья)
Сырье (№Сырья, название)
Составленная схема базы данных находится в 3НФ, так как:
— все реквизиты являются атомарными, семантическими и скалярными
— присутствуют только полные функциональные зависимости
— отсутствуют транзитивные зависимости
- Словарь данных
В словаре данных указаны названия всех таблиц, Имя столбца, Тип данных, Значение NULL для данных, первичный и внешние ключи
Таблица Company
| № | Название | Идентификатор | Тип данных | Не пусто | Ограничение |
| 1 | № Предприятия | Number_Company | int | Да | PK |
| 2 | Название предприятия | Name_Company | Varchar(40) | Нет | |
| 3 | Рейтинг | Rating_Company | int | Нет | |
| 4 | Город | City | Varchar(40) | Нет |
Таблица Employee
| № | Название | Идентификатор | Тип данных | Не пусто | Ограничение |
| 1 | Таб. Номер | Id_Employee | int | Да | PK |
| 2 | Фамилия | LastName_Employee | Varchar(40) | Нет | |
| 3 | Имя | Name_Employee | Varchar(40) | Нет | |
| 4 | Отчество | MiddleName_Employee | Varchar(40) | Нет | |
| 5 | Оклад | Salary | Decimal(7,2) | Нет | |
| 6 | Надбавка | Premium | Decimal(6,2) | Нет | |
| 7 | №Пред | Number_Company | int | Да | FK |
| 8 | №Должности | Number_Position | int | Да | FK |
Таблица Position
| № | Название | Идентификатор | Тип данных | Не пусто | Ограничение |
| 1 | №Должности | Number_Position | int | Да | PK |
| 2 | Наименование | Name_Position | Varchar(40) | Нет | |
| 3 | Рейтинг | Rating_Position | int | Нет |
Таблица Realisation
| № | Название | Идентификатор | Тип данных | Не пусто | Ограничение |
| 1 | №Договора | Number_Contract | int | Да | PK |
| 2 | №Пред | Number_Company | int | Да | FK |
| 3 | №Прод | Number_Product | int | Да | FK |
| 4 | Количество | Amount | int | Нет | |
| 5 | Дата заключения | ConclusionDate | Дата | Нет |
Таблица Type
| № | Название | Идентификатор | Тип данных | Не пусто | Ограничение |
| 1 | №Вида | Number_Type | int | Да | PK |
| 2 | Название | Name_Type | Varchar(40) | Нет |
Таблица Product
| № | Название | Идентификатор | Тип данных | Не пусто | Ограничение |
| 1 | №Прод | Number_Product | int | Да | PK |
| 2 | Название | Name_Product | Varchar(40) | Нет | |
| 3 | Цена | Price_Product | Decimial(7, 2) | Нет | |
| 4 | №Вида | Number_Type | int | Да | FK |
Таблица Production
| № | Название | Идентификатор | Тип данных | Не пусто | Ограничение |
| 1 | №Прод | Number_Product | Int | Да | PK,FK |
| 2 | №Сырья | Number_Materials | int | Да | PK,FK |
Таблица Materials
| № | Название | Идентификатор | Тип данных | Не пусто | Ограничение |
| 1 | №Сырья | Number_Materials | int | Да | PK |
| 2 | Название | Name_Materials | Varchar(40) | Нет |
- Исходные данные для таблиц
Таблица Company
| Number_Company | Name_Company | Rating_Company | City |
| 1 | Нева Милк | 9 | Санкт-Петербург |
| 2 | Фудлэнд | 6 | Москва |
| 3 | Космос групп | 5 | Казань |
| 4 | Рязанский | 30 | Рязань |
| 5 | Сыр Стародубский | 29 | Брянск |
| 6 | Барнаульский молочный комбинат | 23 | Барнаул |
| 7 | РОСМОЛ | 20 | Челябинск |
Таблица Employee
| Id_Employee | LastName_Employee | Name_Employee | MiddleName_Employee | Salary | Premium | Number_Company | Number_Position |
| 1 | Андреев | Виктор | Адольфович | 55 000 | 2 000 | 3 | 1 |
| 2 | Андреев | Григорий | Якунович | 63 000 | 2 300 | 2 | 4 |
| 3 | Суханов | Юстин | Константинович | 43 800 | 4 200 | 5 | 4 |
| 4 | Гришина | Патрисия | Антониновна | 35 500 | 5 000 | 1 | 3 |
| 5 | Сорокин | Эльдар | Денисович | 42 700 | 1 400 | 4 | 5 |
| 6 | Белякова | Лариса | Пантелеймоновна | 63 800 | 2 800 | 6 | 5 |
| 7 | Исакова | Калерия | Якововна | 48 900 | 4 700 | 3 | 3 |
| 8 | Щербаков | Трофим | Львович | 29 800 | 3 500 | 7 | 1 |
| 9 | Сысоев | Иван | Витальевич | 18 700 | 2 700 | 7 | 2 |
| 10 | Лазарев | Адольф | Дмитрьевич | 23 400 | 3 900 | 4 | 3 |
| 11 | Дорофеев | Евгений | Артемович | 22 500 | 6 700 | 5 | 2 |
Таблица Position
| Number_Position | Name_Position | Rating_Position |
| 1 | Технолог | 3 |
| 2 | Маркетолог | 2 |
| 3 | Администратор | 1 |
| 4 | Наладчик | 4 |
| 5 | Оператор | 5 |
Таблица Realisation
| Number_Contract | Number_Company | Number_Product | Amount | ConclusionDate |
| 1 | 3 | 3 | 450 | 15.04.21 |
| 2 | 5 | 7 | 560 | 17.04.21 |
| 3 | 1 | 1 | 300 | 21.04.21 |
| 4 | 2 | 8 | 860 | 21.05.21 |
| 5 | 3 | 5 | 550 | 22.05.21 |
| 6 | 4 | 4 | 750 | 24.06.21 |
| 9 | 6 | 2 | 780 | 27.06.21 |
| 10 | 1 | 6 | 920 | 28.07.21 |
| 11 | 3 | 9 | 450 | 28.08.21 |
Таблица Type
| Number_Type | Name_Type |
| 1 | Кефир |
| 2 | Ряженка |
| 3 | Сметана |
| 4 | Творог |
| 5 | Сыр |
| 6 | Масло |
Таблица Product
| Number_Product | Name_Product | Price_Product | Number_Type |
| 1 | Тысяча Озер | 140 | 5 |
| 2 | Село Зеленое | 125 | 3 |
| 3 | Молочная сказка | 60 | 1 |
| 4 | ПервыйВкус | 85 | 2 |
| 5 | Боровический | 68 | 2 |
| 6 | Тысяча Озер | 215 | 6 |
| 7 | Сливочный | 170 | 5 |
| 8 | Творожный сыр | 210 | 5 |
| 9 | Топтыжка | 92 | 2 |
Таблица Production
| Number_Product | Number_Materials |
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 6 | 1 |
| 6 | 2 |
| 7 | 1 |
| 7 | 2 |
| 7 | 4 |
| 8 | 1 |
| 8 | 4 |
| 9 | 1 |
Таблица Materials
| Number_Materials | Name_Materials |
| 1 | Молоко |
| 2 | Пальмовое масло |
| 3 | Виноград сушеный |
| 4 | Пахта |
- Скриншоты таблиц и исходных данных
— Проекты таблиц
— Данные таблиц
- Схема БД, созданная в SQL Server
- Словесные запросы и запросы на T-SQL из всех практических работ
—ПРАКТИКА 16
—1. 3 простейших запроса с использованием операторов сравнения
—1а. Вывести названия компаний, у которых рейтинг выше 9
SELECT c.Name_Company AS ‘Название компании’, c.Rating_Company FROM dbo.Company c WHERE c.Rating_Company > 9
—1б. Вывести фамилии сотрудников, которые получают зарплату выше 15000
SELECT e.LastName_Employee AS ‘Фамилия’, e.Salary FROM dbo.Employee e WHERE e.Salary > 30000
—1в. Вывести табельные номера сотрудников, работающих в компаниях с номером 3
SELECT e.Id_Employee AS ‘Табельный номер’, e.Number_Company FROM dbo.Employee e WHERE e.Number_Company = 3
—2. 3 запроса с использованием логических операторов AND, OR и NOT;
—2а. Вывести фамилии сотрудников, которые получают зарплату выше 30000 и премию ниже 3000
SELECT e.LastName_Employee AS ‘Фамилия’, e.Salary AS ‘Зарплата’, e.Premium AS ‘Премия’ FROM dbo.Employee e WHERE (e.Salary > 30000 AND e.Premium < 3000)
—2б. Вывести табельные номера сотрудников, работающих в компаниях с номером 3 или 5
SELECT e.Id_Employee AS ‘Таб номер’, e.LastName_Employee AS ‘Фамилия’, e.Number_Company AS ‘Номер компании’ FROM dbo.Employee e WHERE (e.Number_Company = 3 OR e.Number_Company = 5)
—2в. Вывести названия компаний, которые расположены не в городе «Казань»
SELECT c.Name_Company AS ‘Название’, c.City AS ‘Город’ FROM dbo.Company c WHERE c.City NOT LIKE ‘Казань’
—3. 1 запрос на использование комбинации логических операторов;
—3а. Вывести фамилии сотрудников, которые получают зарплату выше 20000, но не работающих на позиции под номером 5
SELECT e.LastName_Employee AS ‘Фамилия’, e.Salary AS ‘Зарплата’, e.Number_Position AS ‘Позиция должности’ FROM dbo.Employee e WHERE (e.Salary > 20000 AND e.Number_Position NOT LIKE 5)
—4. 1 запрос на использование выражений над столбцами;
—4а. Вывести фамилии сорудников, их заработную плату вместе с премией
SELECT e.LastName_Employee AS ‘Фамилия’, e.Salary, e.Premium, e.Salary + e.Premium AS ‘Сумма’ FROM dbo.Employee e
—5. 2 запроса с проверкой на принадлежность множеству;
—5а. Вывести названия компаний, которые расположены в городах Казань и Брянск
SELECT c.Name_Company AS ‘Название компании’, c.City AS ‘Город’ FROM dbo.Company c WHERE c.City IN (‘Казань’, ‘Брянск’)
—5б. Вывести фамилии сотрудников, не работающих на позициях 4, 3
SELECT e.LastName_Employee AS ‘Фамилия’, e.Number_Position FROM dbo.Employee e WHERE e.Number_Position NOT IN (4,3)
—6. 2 запроса с проверкой на принадлежность диапазону значений;
—6а. Вывести фамилии сотрудников, получающие зарплату не в диапазоне от 35000 до 62000
SELECT e.LastName_Employee AS ‘Фамилия’, e.Salary AS ‘Зарплата’ FROM dbo.Employee e WHERE (e.Salary NOT BETWEEN 35000 AND 62000)
—6б. Вывести номера договоров выпуска продукции, заключенных с 4.04.21 по 25.05.21
SELECT r.Number_Contract AS ‘Договор’, r.ConclusionDate AS ‘Дата заключения’ FROM dbo.Realisation r WHERE (r.ConclusionDate BETWEEN ‘04.04.21’ AND ‘25.05.21’)
—7. 2 запроса с проверкой на соответствие шаблону;
—7а. Вывести фамилии сотрудников начинающихся не на «а»
SELECT e.LastName_Employee AS ‘Фамилия’ FROM dbo.Employee e WHERE e.LastName_Employee NOT LIKE ‘А%’
—7б. Вывести Фамилию и Имя сотрудников, чьи имена заканчиваются на «ия»
SELECT e.LastName_Employee AS ‘Фамилия’, e.Name_Employee AS ‘Имя’ FROM dbo.Employee e WHERE e.Name_Employee LIKE ‘%ия’
—8. 1 запрос с проверкой на неопределенное значение.
—8а. Вывести названия молочных продуктов, цена которых NULL
SELECT p.Name_Product AS ‘Название’, p.Price_Product FROM dbo.Product p WHERE p.Price_Product IS NULL
—ПРАКТИКА 17
—1. 2 запроса на заданное количество строк
—1а. Вывести первые 5 фамилий сотрудников из таблицы
SELECT TOP 5 e.LastName_Employee AS ‘Фамилия’ FROM dbo.Employee e WHERE e.Salary > 25000
—1б. Вывести первые три договора, заключенных раньше всего
SELECT TOP 3 r.* FROM dbo.Realisation r ORDER BY r.ConclusionDate
—2. 4 запроса на сортировку по одному или нескольким полям с различными условиями
—2а. Вывести товары, цена которых больше 50 рублей, отсортировав по названию в алфавитном порядке
SELECT * FROM Product WHERE Price_Product > 50 ORDER BY Name_Product
—2б. Вывести номера товаров, производящихся в компании под номером 3, отсортировав по количеству продукции
SELECT Number_Contract, Number_Product, dbo.Realisation.Number_Company, dbo.Realisation.Amount FROM Realisation WHERE Number_Company = 3 ORDER BY Amount
—2в. Вывести фамилии сотрудников, получающие зарплату более 20000, отсортировав по номеру компании и номеру позиции должности
SELECT LastName_Employee, Name_Employee, Salary, Number_Company, Number_Position FROM Employee WHERE Salary > 20000 ORDER BY Number_Company, Number_Position
—2г. Вывести сотрудников, чьи фамилии заканчиваются на А, отсортировав по зарплате и имени
SELECT LastName_Employee, Name_Employee, MiddleName_Employee, Salary, Premium FROM Employee WHERE LastName_Employee LIKE ‘%а’ ORDER BY Name_Employee, Salary
—3. 1 запрос на нахождение строк, находящихся в диапазоне значений
—3а. Вывести сотрудников, чья зарплата находится в диапазоне между 30 000 до 50 000
SELECT LastName_Employee, Name_Employee, Salary FROM Employee WHERE Salary BETWEEN 30000 AND 50000
—4. 3 запроса на использование подстановочных символов
—4а. Вывести фамилии сотрудников, чьи имена начинаются на А
SELECT e.LastName_Employee AS ‘Фамилия’, e.Name_Employee AS ‘Имя’ FROM dbo.Employee e WHERE e.Name_Employee LIKE ‘А%’
—4б. Вывести названия компаний, в которых есть буквы АР
SELECT Name_Company FROM Company WHERE Name_Company LIKE ‘%ар%’
—4в. Вывести названия молочных продуктов, названия которых начинаются с Т и заканчиваются на Р
SELECT Name_Product FROM Product WHERE Name_Product LIKE ‘Т%р’ GROUP BY Name_Product
—5. 2 запроса на множественный выбор (простое и поисковое выражение CASE)
—5а. Прибавить к рейтингу компании определенное число в зависимости от расположения
SELECT CASE
WHEN City = ‘Казань’ THEN Rating_Company + 2
WHEN City = ‘Москва’ THEN Rating_Company + 8
WHEN City = ‘Рязань’ THEN Rating_Company + 3
ELSE ‘ ‘
END AS Rating_Company,
Name_Company, City
FROM Company
—5б. Вывести фамилии сотрудников, если их должность равна или 5, или 3, или 2
SELECT CASE Number_Position
WHEN 5 THEN LastName_Employee
WHEN 3 THEN LastName_Employee
WHEN 2 THEN LastName_Employee
ELSE ‘NOT’
END AS Result,
Number_Position
FROM Employee
—6. 1 запрос на формирование временной таблицы
—6а. Вывести во временную таблицу сотрудников, работающих на должности под номером 4
SELECT LastName_Employee, Name_Employee, Number_Position
INTO #tabTable
FROM Employee
WHERE Number_Position = 4
SELECT * FROM #tabTable
DROP TABLE #tabTable
—7. 1 запрос с использованием функции IIF
—7а. Если сотрудник зарабатывает более 30 000 рублей, вывести его фамилию, иначе имя
SELECT Id_Employee, Salary,
IIF (Salary > 30000, LastName_Employee, Name_Employee) AS Result
FROM Employee
—8. 1 запрос на группировку с условием
—8а. Вывести номера договоров, сгруппированных по компаниям
SELECT Number_Company
FROM Realisation
GROUP BY Number_Company
—Практика 18
—1. 1 запрос на функцию SUM
—1а. Вывести сумму зарплат, которую получают сотрудники
SELECT Number_Company, SUM(Salary) AS ‘Зарплата’ FROM Employee GROUP BY dbo.Employee.Number_Company
—2. 1 запрос на функцию SUM с условием;
—2а. Вывести сумму продуктов, произведенных компанией под номером 2
SELECT SUM(Amount) AS ‘Количество продукции’ FROM Realisation WHERE Number_Company = 2
—3. 1 запрос на функцию SUM с группировкой и условием;
—3а. Вывести сумму зарплат, которую получают сотрудники из компаний под номером 3 и 5
SELECT Number_Company, SUM(Salary) AS ‘Зарплата’
FROM Employee
WHERE Number_Company IN (3,5)
GROUP BY Number_Company
—4. 4 запроса на функции MIN и MAX с использованием условий и группировок
—4а. Вывести минимальную и максимальную заработную плату сотрудников
SELECT MAX(Salary) AS ‘Макс. зарплата’, MIN(Salary) AS ‘Мин. зарплата’ FROM Employee
—4б. Вывести договор и номер продукта, который производился компанией под номером 3 меньше всего
SELECT r.Number_Contract, MIN(r.Number_Product) AS ‘Номер продукта’
FROM dbo.Realisation r
WHERE r.Number_Company = 3
GROUP BY r.Number_Contract
—4в. Вывести максимальное количество изготовленной продукции
SELECT MAX(Amount) AS ‘Количество продукции’ FROM dbo.Realisation r
—4г. Вывести минимальное количество продукции под номером 6 или 3
SELECT MIN(r.Amount) AS ‘Количество продукции’
FROM dbo.Realisation r
WHERE r.Number_Product = 6 OR r.Number_Product = 3
—5. 2 запроса на функцию AVG с использованием условий и группировок;
—7а. Вывести среднее число всех сотрудников
SELECT AVG(Id_Employee) AS ‘Количество сотрудников’ FROM Employee
GROUP BY dbo.Employee.Number_Company
—7б. Найти средний рейтинг компаний, расположенных в Казани
SELECT AVG(Rating_Company) AS ‘Средний рейтинг’
FROM Company WHERE City = ‘Казань’
—7в. Найти среднее количество продукции, сгруппировав по компаниям
SELECT Number_Company, AVG(AMOUNT) AS ‘Среднее количество продукции’
FROM dbo.Realisation r
WHERE Number_Company IS NOT NULL
GROUP BY Number_Company
—6. 3 запроса на функцию COUNT с использованием условий и группировок;
—6а. Вывести количество договоров, заключенных компаниями
SELECT Number_Company, COUNT(Number_Contract) AS ‘Количество контрактов’
FROM Realisation
WHERE Number_Company IS NOT NULL
GROUP BY Number_Company
—6б. Вывести количество сотрудников в каждой компании
SELECT Number_Company, COUNT(Id_Employee) AS ‘Количество сотрудников’
FROM Employee
GROUP BY Number_Company
—6в. Вывести количество сотрудников, чья зарплата больше 20000 рублей, сгруппировав по компаниям
SELECT Number_Company, COUNT(Id_Employee) AS ‘Количество сотрудников’
FROM Employee WHERE Salary > 20000
GROUP BY Number_Company
—7. 3 запроса с использованием нескольких функций и добавлением разных параметров (WHERE, GROUP BY, HAVING);
—7а. Вывести максимальное, среднее и минимальное количество продукции, сгруппировав по компаниям
SELECT Number_Company, MAX(Amount) AS ‘Макс количество продукции’, AVG(Amount) AS ‘Среднее количество продукции’,
MIN(Amount) AS ‘Минимальное количество продукции’
FROM dbo.Realisation r
GROUP BY r.Number_Company
—7б. Вывести общую сумму продукции произведенной с 21.05.21 по 26.06.21
SELECT SUM(r.Amount) AS ‘Общая сумма’ FROM dbo.Realisation r
WHERE (r.ConclusionDate BETWEEN ‘21.05.21’ AND ‘26.06.21’)
—7в. Вывести количество сотрудников и среднюю заработную плату в компании под номером 7
SELECT COUNT(e.Id_Employee) AS ‘Количество сотрудников’, AVG(e.Salary) AS ‘Средняя заработная плата’
FROM dbo.Employee e
WHERE e.Number_Company = 7
—ПРАКТИКА 19
—1. 1 запрос с использованием декартового произведения двух таблиц;
—1а. Вывести полную информацию о компании и подписанных договорах
SELECT * FROM Company, Realisation
—2. 1 запрос с использованием соединения двух таблиц по равенству;
—2а. Вывести компании, подписавшие договор по плану выпуска продукции
SELECT DISTINCT Company.Name_Company AS ‘Название компании’ FROM Company, Realisation
WHERE Company.Number_Company = Realisation.Number_Company
—3. 3 запроса с использованием соединения двух таблиц по равенству и с синонимами;
—3а. Вывести продукты и его тип
SELECT Name_Product AS ‘Наименование продукта’, Name_Type FROM Product, Type
WHERE Product.Number_Type = Type.Number_Type
—3б. Вывести название компании и общую сумму производящихся там продуктов
SELECT Name_Company AS ‘Название компании’, SUM(Amount) AS ‘Сумма’ FROM Company, Realisation
WHERE Company.Number_Company = Realisation.Number_Company
GROUP BY Name_Company
—3в. Вывести названия продуктов и количество материалов, из которых они изготовлены
SELECT Name_Product AS ‘Наименование продукта’, COUNT(Number_Materials) AS ‘Количество’
FROM Product, Production
WHERE Product.Number_Product = Production.Number_Product
GROUP BY Name_Product
—4. 2 запрос с использованием соединения двух таблиц по равенству и условием отбора;
—4а. Вывести суммарное количество всех продуктов, которые производились в компании Нева Милк
SELECT Name_Company AS ‘Название компании’, SUM(Amount) AS ‘Сумма’ FROM Company, Realisation
WHERE Company.Number_Company = Realisation.Number_Company
GROUP BY Name_Company
HAVING Name_Company = ‘Нева Милк’
—4б. Вывести названия произведенных продуктов типа СЫР
SELECT p.Name_Product AS ‘Наименование продукта’, t.Name_Type
FROM dbo.Product p, dbo.Type t
WHERE p.Number_Type = t.Number_Type AND t.Name_Type = ‘Сыр’
—5. 2 запрос с использованием соединения трех таблиц по равенству и условием отбора;
—5а. Вывести сотрудников компании РОСМОЛ и их рейтинг согласно должности
SELECT LastName_Employee AS ‘Фамилия’, Name_Employee AS ‘Имя’, Rating_Position AS ‘Рейтинг’,
Name_Company AS ‘Название компании’ FROM Employee, Company, Position
WHERE Employee.Number_Company = Company.Number_Company AND
Employee.Number_Position = Position.Number_Position AND Name_Company = ‘РОСМОЛ’
—5б. Вывести названия продуктов, производящихся в Космос Групп
SELECT Name_Product AS ‘Наименование продукта’, Name_Company AS ‘Название компании’
FROM Company, Realisation, Product
WHERE Company.Number_Company = Realisation.Number_Company AND
Realisation.Number_Product = Product.Number_Product AND Name_Company = ‘Космос Групп’
—6. 1 запрос с использованием симметричного соединения и удаление избыточности.
—6а. Вывести фамилии сотрудников, работающих в одной компании
SELECT e1.LastName_Employee AS ‘Фамилия’, e2.LastName_Employee AS ‘Фамилия’,
e2.Number_Company AS ‘Номер компании’, e1.Number_Company AS ‘Номер компании’
FROM Employee e1, Employee e2
WHERE e1.Id_employee>e2.Id_employee and e1.Number_Company=e2.Number_Company
—7. 1 запрос с внешним соединением
—7а. Вывести список всех сотрудников, название компании, в которой они работают
SELECT e.LastName_Employee AS ‘Фамилия’, e.Name_Employee AS ‘Имя’, c.Name_Company AS ‘Название компании’
FROM dbo.Employee e JOIN dbo.Company c ON e.Number_Company = c.Number_Company
—8. 1 запрос с использованием левого внешнего соединения;
—8a. Найдите договор, у которого нет компании
SELECT Number_Contract AS ‘Номер контракта’, dbo.Company.Name_Company AS ‘Название компании’
FROM Realisation LEFT JOIN Company ON Realisation.Number_Company = Company.Number_Company
WHERE dbo.Company.Name_Company IS NULL
—9. 1 запрос с использованием правого внешнего соединения;
—9а. Вывести должности, на которых не зарегестрированы сотрудники
SELECT dbo.Position.Name_Position AS ‘Название должности’, dbo.Employee.Name_Employee AS ‘Имя’
FROM Employee RIGHT JOIN Position ON dbo.Employee.Number_Position = dbo.Position.Number_Position
WHERE dbo.Employee.Id_Employee IS NULL
—10. 1 запрос с использованием полного внешнего соединения;
—10а. Вывести всех сотрудников и их должности
SELECT e.LastName_Employee AS ‘Фамилия’, p.Name_Position
FROM dbo.Employee e FULL JOIN dbo.Position p ON e.Number_Position = p.Number_Position
—11. 1 запрос с использованием перекресного внешнего соединения;
—11а. Вывести полную информацию о сотрудниках и должности
SELECT * FROM dbo.Employee e CROSS JOIN dbo.Position p
—ПРАКТИКА 20
—1. 2 вложенных запроса
—1а. Определите сотрудников, работающих в компании РОСМОЛ
SELECT LastName_Employee AS ‘Фамилия’, Name_Employee AS ‘Имя’, Company.Name_Company AS ‘Наименование компании’ FROM Employee, Company
WHERE Employee.Number_Company =
(SELECT Number_Company FROM Company WHERE Name_Company = ‘РОСМОЛ’)
AND Employee.Number_Company = Company.Number_Company
—1б. Определить сотрудников, работающих на той же позиции, что и ‘Щербаков Трофим Львович’
SELECT LastName_Employee AS ‘Фамилия’, Name_Employee AS ‘Имя’, Name_Position AS ‘Наименование должности’ FROM Employee, Position
WHERE Employee.Number_Position =
(SELECT Number_Position FROM Employee WHERE LastName_Employee = ‘Щербаков’
AND Name_Employee = ‘Трофим’ AND MiddleName_Employee = ‘Львович’)
AND Employee.Number_Position = Position.Number_Position
—2. 3 вложенных запроса с агрегатными функциями
—2а. Вывести наименование продукции, которую производили больше всего
SELECT Name_Product AS ‘Наименование продукции’ FROM Product WHERE Number_Product = ANY
(SELECT Number_Product FROM Realisation WHERE AMOUNT =
(SELECT MAX(Amount) FROM Realisation))
—2б. Вывести работников, работающих на должности с максимальным рейтингом
SELECT LastName_Employee AS ‘Фамилия’, Name_Employee AS ‘Имя’ FROM Employee WHERE Number_Position = ANY
(SELECT Number_Position FROM Position WHERE Rating_Position =
(SELECT MAX(Rating_Position) FROM Position))
—2в. Вывести наименование продукции, которую производили меньше среднего количества всей продукции
SELECT Name_Product AS ‘Наименование продукции’, Amount AS ‘Количество продукции’ FROM Realisation, Product
WHERE Amount > (SELECT AVG(Amount) FROM Realisation)
AND Realisation.Number_Product = Product.Number_Product
—3. 3 вложенных запроса с использованием ALL и ANY
—3а. Вывести наименования компаний, занимающихся выпуском продукции «Молочная сказка»
SELECT Name_Company AS ‘Название компании’ FROM Company
WHERE Number_Company = ANY
(SELECT Number_Company FROM Realisation
WHERE Number_Product =
(SELECT Number_Product FROM Product
WHERE Name_Product = ‘Молочная сказка’))
—3б. Вывести сотрудников, работающих в компаниях, производящей сыр
SELECT LastName_Employee AS ‘Фамилия’, Name_Employee AS ‘Имя’ FROM Employee WHERE Number_Company = ANY
(SELECT Number_Company FROM Realisation WHERE Number_Product =
ANY(SELECT Number_Product From Product WHERE Number_Type =
(SELECT Number_Type FROM Type WHERE Name_Type = ‘Сыр’)))
—3в. Вывести компании, в которых пустует должность Маркетолог
SELECT * FROM Company WHERE Number_Company != ALL
(SELECT Number_Company FROM Employee WHERE Number_Position =
(SELECT Number_Position FROM Position WHERE Name_Position = ‘Маркетолог’))
—4. 2 сложных запроса с использование соединений и вложенных
—4а. Вывести наименование продукции, в которую добавляют сахар или подсластители
SELECT Name_Product AS ‘Наименование продукции’, Type.Name_Type AS ‘Вид’ FROM Production, Product, Type
WHERE Production.Number_Materials = ANY
(SELECT Number_Materials FROM Materials WHERE Name_Materials = ‘Сахар’ OR Name_Materials = ‘Подсластитель’)
AND Production.Number_Product = Product.Number_Product
AND Type.Number_Type = Product.Number_Type
—4б. Вывести наименование компаний, производящих ту же продукцию, что и «Нева Милк»
SELECT Name_Company AS ‘Название компании’ FROM Realisation, Company
WHERE Number_Product = ANY (SELECT Number_Product FROM Realisation
WHERE Number_Company = (SELECT Number_Company FROM Company
WHERE Name_Company = ‘Нева Милк’)) AND Realisation.Number_Company
= Company.Number_Company AND Name_Company != ‘Нева Милк’
- Скриншоты запросов и ответов (по 2 запроса наиболее сложных из каждой работы. Всего 10)
Варианты
Вариант 1.
Предметная область: Налоговая инспекция 6
В налоговой инспекции зарегистрированы предприятия с разными формами собственности и организационными структурами. Одна форма собственности и одна организационная структура может быть у разных предприятий. У предприятия может быть несколько учредителей (собственников). У каждого предприятия может быть несколько видов деятельности и один вид деятельности может быть у разных предприятий.
Вариант 2
Предметная область: Кинотеатр 6
В кинотеатре есть несколько залов, в которых идут фильмы. Фильмы заказывают в кинокомпаниях. Одна компания может поставлять несколько фильмов. У фильмов могут быть разные возрастные категории и жанры. В кинотеатре есть расписание, в котором указано какие фильмы в каких залах идут, в какой день недели и в какое время.
Вариант 3
Предметная область: Турагентство 6
В турагентстве работают менеджеры с туристами. Менеджеры продают путевки разным туристам в разные страны. Один менеджер работает с разными туристами. В стране прибытия для туриста забронирована гостиница. В одной стране может быть много гостиниц. Гостиницы имеют разные категории. До страны назначения можно добраться разным транспортом.
Вариант 4
Предметная область: Ремонт дорог 7
В городе существуют дороги, которые нуждаются в ремонте. Дороги имеют разный вид покрытия. Один вид покрытия может быть у разных дорог. Ремонтом дорог занимаются бригады, которые ремонтируют разные дороги. Для ремонта дорог требуется техника, которая принадлежит определенной бригаде. Для ремонта разных дорог требуется разный материал.
Вариант 5
Предметная область: Риэлтерское агентство 6
В риэлтерском агентстве работают риелторы по продаже недвижимости. Каждый риелтор может работать с несколькими владельцами недвижимости (продавец). У одного владельца может быть разная недвижимость. Недвижимость может быть разного типа (например: дом, квартира, участок). Также риелтор может работать с несколькими покупателями недвижимости и покупатель недвижимости может работать с несколькими риелторами.
Вариант 6
Предметная область: Соревнования 6
В городе регулярно проводятся соревнования по бегу. В каждом соревновании участвует несколько команд. Одна команда может участвовать в нескольких соревнованиях. В состав команд входят спортсмены. В одной команде несколько спортсменов. Каждый спортсмен имеет звание (например: мастер спорта). У каждого спортсмена есть рейтинг, в котором указывается в каком соревновании он участвовал и какое место занял
Вариант 7
Предметная область: Музей 6
В музее находится несколько залов, в которых выставлены разные картины. В одном зале выставлено насколько картин. Каждая картина имеет название, автора и исполнение (например: карандаш, масляные краски, акварель, гуашь и прочее). Один автор может написать много картин. Картины могут отправлять на выставки, о чем хранится информации в истории. Разные картины могут участвовать в разных выставках.
Вариант 8
Предметная область: Больница 7
В больнице есть несколько отделений. В каждом отделении несколько палат, в которых лежат пациенты. В одной палате лежит несколько пациентов, у которых может быть разные диагнозы. В каждом отделение работают врачи с разной категорией. В одном отделении много врачей. Один врач может быть лечащим врачом в нескольких палатах и в одной палате может быть несколько лечащих врачей.
Вариант 9
Предметная область: Готовые блюда 6
Столовая готовит разные блюда из разных продуктов. Продукты и готовые блюда имеют определенный срок годности (например: неделя, два дня, один час и прочее). Один и тот же срок годности может быть у разных продуктов и готовых блюд. Продукты поставляют поставщики по накладным. Один поставщик делает поставки по разным накладным. По одной накладной могут быть поставлены разные продукты. Один и тот же продукт могут поставлять по разным накладным.
Вариант 10
Предметная область: Библиотека 7
В библиотеке хранятся книги. Каждая книга хранится в определенном отделе (например: художественный, научный и прочее). Книги выдают читателям. На каждого читателя открывают абонемент, в который записывают какие книги выданы . Читателям могут выдать разные книги. Книги выдают сотрудники. Работа у сотрудников посменная. В разные смены могут работать разные сотрудники.
Вариант 11
Предметная область: Концертный зал 6
В концертном зале проводятся разные виды концертов (например: спектакль, джаз, балет и прочее). В одном концерте могут участвовать разные артисты. Одни и те же артисты могут участвовать в разных концертах. Все артисты имеют свое амплуа (например: певец, музыкант). Одно и то же амплуа может быть у разных артистов. На один концерт продают много билетов.
Вариант 12
Предметная область: Детский сад 7
В детском саду есть несколько групп, в которых находятся дети. Дети могут ходить в кружки. В разные кружки могут ходить разные дети. Каждый кружок проводит открытые мероприятия. Их может быть много. Кружки работают по расписанию, в котором указано какой кружок в какой день недели работает и в какое время.
