Academy-Top-SQL

Экзаменационные работы

ExaminationWork 01

Создать базу данных Книжный магазин (BookShop), описание структуры таблиц.

1. Авторы (Authors)
   • Идентификатор (Id). Уникальный идентификатор автора: тип данных – int, авто приращение, не может содержать null­значения, первичный ключ.
   • Имя (Name). Имя автора: тип данных – nvarchar(max), не может содержать null­значения, не может быть пустым.
   • Фамилия (Surname). Фамилия автора: тип данных – nvarchar(max), не может содержать null­значения, не может быть пустым.
   • Идентификатор страны (CountryId). Страна, из которой родом автор: тип данных – int, не может содержать null­значения, внешний ключ.
2. Книги (Books)
   • Идентификатор (Id). Уникальный идентификатор книги: тип данных – int, авто приращение, не может содержать null­значения, первичный ключ.
   • Название (Name). Название книги: тип данных – nvarchar(max), не может содержать null­значения, не может быть пустым.
   • Страницы (Pages). Количество страниц в книге: тип данных – int, не может содержать null­значения, не может быть меньше либо равно 0.
   • Цена (Price). Цена книги: тип данных – money, не может содержать null­значения, не может быть меньше 0.
   • Дата публикации (PublishDate). Дата публикации книги: тип данных – date, не может содержать null­значения, не может быть больше текущей даты.
   • Идентификатор автора (AuthorId). Автор книги: тип данных – int, не может содержать null­значения, внешний ключ.
   • Идентификатор тематики (ThemeId). Тематика книги: тип данных – int, не может содержать null­значения, внешний ключ.
3. Страны (Countries)
   • Идентификатор (Id). Уникальный идентификатор страны: тип данных – int, авто приращение, не может содержать null­значения, первичный ключ.
   • Название (Name). Название страны: тип данных – nvarchar(50), не может содержать null­значения, не может быть пустым, должно быть уникальным.
4. Продажи (Sales)
   • Идентификатор (Id). Уникальный идентификатор продажи: тип данных – int, авто приращение, не может содержать null­значения, первичный ключ.
   • Цена (Price). Цена продажи одного экземпляра книги: тип данных – money, не может содержать null­значения, не может быть меньше 0.
   • Количество (Quantity). Количество проданных экземпляров книги: тип данных – int, не может содержать null­значения, не может быть меньше либо равно 0.
   • Дата продажи (SaleDate). Дата продажи: тип данных – date, не может содержать null­значения, не может быть больше текущей даты, значение по умолчанию – текущая дата.
   • Идентификатор книги (BookId). Проданная книга: тип данных – int, не может содержать null­значения, внешний ключ.
   • Идентификатор магазина (ShopId). Магазин, в котором была совершена продажа: тип данных – int, не может содержать null­значения, внешний ключ.
5. Магазины (Shops)
   • Идентификатор (Id). Уникальный идентификатор магазина: тип данных – int, авто приращение, не может содержать null­значения, первичный ключ.
   • Название (Name). Название магазина: тип данных – nvarchar(max), не может содержать null­значения, не может быть пустым.
   • Идентификатор страны (CountryId). Страна, в которой находится магазин: тип данных – int, не может содержать null­значения, внешний ключ.
6. Тематики (Themes)
   • Идентификатор (Id). Уникальный идентификатор тематики: тип данных – int, авто приращение, не может содержать null­значения, первичный ключ.
   • Название (Name). Название тематики: тип данных – nvarchar(100), не может содержать null­значения, не может быть пустым, должно быть уникальным.

Необходимо написать следующие запросы к базе данных «Книжный магазин»:

1. Показать все книги, количество страниц в которых больше 500, но меньше 650.
2. Показать все книги, в которых первая буква названия либо «А», либо «З».
3. Показать все книги жанра «Детектив», количество проданных книг более 30 экземпляров.
4. Показать все книги, в названии которых есть слово «Microsoft», но нет слова «Windows».
5. Показать все книги (название, тематика, полное имя автора в одной ячейке), цена одной страницы которых меньше 65 копеек.
6. Показать все книги, название которых состоит из 4 слов.
7. Показать информацию о продажах в следующем виде:
   • Название книги, но, чтобы оно не содержало букву «А».
   • Тематика, но, чтобы не «Программирование».
   • Автор, но, чтобы не «Герберт Шилдт».
   • Цена, но, чтобы в диапазоне от 10 до 20 гривен.
   • Количество продаж, но не менее 8 книг.
   • Название магазина, который продал книгу, но он не должен быть в Украине или России.
8. Показать следующую информацию в два столбца (числа в правом столбце приведены в качестве примера):
   • Количество авторов: 14
   • Количество книг: 47
   • Средняя цена продажи: 85.43 грн.
   • Среднее количество страниц: 650.6.
9. Показать тематики книг и сумму страниц всех книг по каждой из них.
10. Показать количество всех книг и сумму страниц этих книг по каждому из авторов.
11. Показать книгу тематики «Программирование» с наибольшим количеством страниц.
12. Показать среднее количество страниц по каждой тематике, которое не превышает 400.
13. Показать сумму страниц по каждой тематике, учитывая только книги с количеством страниц более 400, и чтобы тематики были Программирование», «Администрирование» и «Дизайн».
14. Показать информацию о работе магазинов: что, где, кем, когда и в каком количестве было продано.
15. Показать самый прибыльный магазин.

ExaminationWork 02

Задание. Для веб-проекта «Афиша событий» необходимо создать базу данных. В базе данных нужно хранить такую информацию:

• Категории событий
  • спектакль
  • концерт
  • выставка
  • цирк
  • спорт
  • семинары и тренинги
  • кино
  • юмор
  • вечеринки
  • детям
  • другое
• Событие
  • название события
  • дата проведения или диапазон дат
  • страна проведения
  • город проведения
  • место проведения
  • время проведения
  • категория события
  • описание события
  • возрастные ограничения
  • изображение для афиши события
  • максимальное количество билетов
  • количество приобретённых билетов
• Клиенты
  • ФИО клиента
  • контактный email клиента
  • дата рождения
  • приобретённые клиентом билеты
  • название события
  • цена билета
• Архив событий.
  При проектировании базы данных обязательно используйте индексы. За отсутствие индексов или неправильное использование экзаменационная оценка может быть уменьшена.
  Продумайте систему безопасности. Обязательные требования к ней:
• Пользователь с полным доступом ко всей информации
• Пользователь с правом только на чтение данных
• Пользователь с правом резервного копирования и восстановления данных
• Пользователь с правом создания и удаления пользователей.
  С помощью представлений, хранимых процедур, пользовательских функций, триггеров реализуйте следующую функциональность:
• Отобразите все актуальные события на конкретную дату. Дата указывается в качестве параметра
• Отобразите все актуальные события из конкретной категории. Категория указывается в качестве параметра
• Отобразите все актуальные события со стопроцентной продажей билетов
• Отобразите топ-3 самых популярных актуальных событий (по количеству приобретённых билетов)
• Отобразите топ-3 самых популярных категорий событий (по количеству всех приобретённых билетов). Архив событий учитывается
• Отобразите самое популярное событие в конкретном городе. Город указывается в качестве параметра
• Покажите информацию о самом активном клиенте (по количеству купленных билетов)
• Покажите информацию о самой непопулярной категории (по количеству событий). Архив событий учитывается
• Отобразите топ-3 набирающих популярность событий (по количеству проданных билетов за 5 дней)
• Покажите все события» которые пройдут сегодня в указанное время. Время передаётся в качестве параметра
• Покажите название городов, в которых сегодня пройдут события
• При вставке нового клиента нужно проверять, нет ли его уже в базе данных. Если такой клиент есть, генерировать ошибку с описанием возникшей проблемы
• При вставке нового события нужно проверять, нет ли его уже в базе данных. Если такое событие есть» генерировать ошибку с описанием возникшей проблемы
• При удалении прошедших событий необходимо их переносить в архив событий
• При попытке покупки билета проверять не достигнуто ли уже максимальное количество билетов. Если максимальное количество достигнуто, генерировать ошибку с информацией о возникшей проблеме
• При попытке покупки билета проверять возрастные ограничения. Если возрастное ограничение нарушено, генерировать ошибку с информацией о возникшей проблеме
• Настроить создание резервных копий с периодичностью раз в день

Решения домашних заданий SQL

Homework 01

Тема: "Основы взаимодействия с MySQL Server"

Задание. Необходимо создать базу данных Академия (Academy), которая будет содержать информацию о сотрудниках и внутреннем устройстве академии. Преподаватели, читающие лекции в академии представлены в виде таблицы.
Преподаватели (Teachers), в которой собрана основная информация, такая как: имя, фамилия, данные о зарплате, а также дата приёма на работу.
Также в базе данных присутствует информация о группах, хранимая в таблице Группы (Groups). Данные об факультетах и кафедрах содержатся в таблицах Факультеты (Faculties) и Кафедры (Departments) соответственно.
Ниже представлено детальное описание структуры каждой таблицы.
A) Группы (Groups)
• Идентификатор (Id) – уникальный идентификатор группы: тип данных – int, авто приращение, не может содержать null-значения, первичный ключ.
• Название (Name) – название группы: тип данных – nvarchar(10), не может содержать null-значения, не может быть пустым, должно быть уникальным.
• Рейтинг (Rating) – рейтинг группы: тип данных – int, не может содержать null-значения, должно быть в диапазоне от 0 до 5.
• Курс (Year) – курс (год) на котором обучается группа: тип данных – int, не может содержать null-значения, должно быть в диапазоне от 1 до 5.
B) Кафедры (Departments)
• Идентификатор (Id) – уникальный идентификатор кафедры: тип данных – int, авто приращение, не может содержать null-значения, первичный ключ.
• Финансирование (Financing) – фонд финансирования кафедры: тип данных – money, не может содержать null-значения, не может быть меньше 0, значение по умолчанию – 0.
• Название (Name) – название кафедры: тип данных – nvarchar(100), не может содержать null-значения, не может быть пустым, должно быть уникальным.
C) Факультеты (Faculties)
• Идентификатор (Id) – уникальный идентификатор факультета: тип данных – int, авто приращение, не может содержать null-значения, первичный ключ.
• Название (Name) – название факультета: тип данных – nvarchar(100), не может содержать null-значения, не может быть пустым, должно быть уникальным.
D) Преподаватели (Teachers)
• Идентификатор (Id) – уникальный идентификатор преподавателя: тип данных – int, авто приращение, не может содержать null-значения, первичный ключ.
• Дата трудоустройства (EmploymentDate) – дата приёма преподавателя на работу: тип данных – date, не может содержать null-значения, не может быть меньше 01.01.1990.
• Имя (Name) – имя преподавателя: тип данных – nvarchar(max), не может содержать null-значения, не может быть пустым.
• Надбавка (Premium) – надбавка преподавателя: тип данных – money, не может содержать null-значения, не может быть меньше 0, значение по умолчанию – 0.
• Ставка (Salary) – ставка преподавателя: тип данных – money, не может содержать null-значения, не может быть меньше либо равно 0.
• Фамилия (Surname) - фамилия преподавателя: тип данных – nvarchar(max), не может содержать null-значения, не может быть пустым.

Homework 02

Тема: "Запросы Select, Insert, Update, Delete"

1. Вывести таблицу кафедр, но расположить её поля в обратном порядке.
2. Вывести названия групп и их рейтинги с уточнением имён полей именем таблицы.
3. Вывести для преподавателей их фамилию, процент ставки по отношению к надбавке и процент ставки по отношению к зарплате (сумма ставки и надбавки).
4. Вывести таблицу факультетов в виде одного поля в следующем формате: "The dean of faculty [faculty] is [dean].".
5. Вывести фамилии преподавателей, которые являются профессорами и ставка которых превышает 1050.
6. Вывести названия кафедр, фонд финансирования которых меньше 11000 или больше 25000.
7. Вывести названия факультетов кроме факультета "Computer Science".
8. Вывести фамилии и должности преподавателей, которые не являются профессорами.
9. Вывести фамилии, должности, ставки и надбавки ассистентов, у которых надбавка в диапазоне от 160 до 550.
10. Вывести фамилии и ставки ассистентов.
11. Вывести фамилии и должности преподавателей, которые были приняты на работу до 01.01.2000.
12. Вывести названия кафедр, которые в алфавитном порядке располагаются до кафедры "Software Development". Выводимое поле должно иметь название "Name of Department".
13. Вывести фамилии ассистентов, имеющих зарплату (сумма ставки и надбавки) не более 1200.
14. Вывести названия групп 5-го курса, имеющих рейтинг в диапазоне от 2 до 4.
15. Вывести фамилии ассистентов со ставкой меньше 550 или надбавкой меньше 200.

Homework 03

Тема: "Многотабличные базы данных"

1. Вывести все возможные пары строк преподавателей и групп.
2. Вывести названия факультетов, фонд финансирования кафедр которых превышает фонд финансирования факультета.
3. Вывести фамилии кураторов групп и названия групп, которые они курируют.
4. Вывести имена и фамилии преподавателей, которые читают лекции у группы "P107".
5. Вывести фамилии преподавателей и названия факультетов на которых они читают лекции.
6. Вывести названия кафедр и названия групп, которые к ним относятся.
7. Вывести названия дисциплин, которые читает преподаватель "Samantha Adams".
8. Вывести названия кафедр, на которых читается дисциплина "Database Theory".
9. Вывести названия групп, которые относятся к факультету "Computer Science".
10. Вывести названия групп 5-го курса, а также название факультетов, к которым они относятся.
11. Вывести полные имена преподавателей и лекции, которые они читают (названия дисциплин и групп), причём отобрать только те лекции, которые читаются в аудитории "B103".

Homework 04

Тема: "Подзапросы"

1. Вывести номера корпусов, если суммарный фонд финансирования расположенных в них кафедр превышает 100000.
2. Вывести названия групп 5-го курса кафедры "Software Development", которые имеют более 10 пар в первую неделю.
3. Вывести названия групп, имеющих рейтинг (средний рейтинг всех студентов группы) больше, чем рейтинг группы "D221".
4. Вывести фамилии и имена преподавателей, ставка которых выше средней ставки профессоров.
5. Вывести названия групп, у которых больше одного куратора.
6. Вывести названия групп, имеющих рейтинг (средний рейтинг всех студентов группы) меньше, чем минимальный рейтинг групп 5-го курса.
7. Вывести названия факультетов, суммарный фонд финансирования кафедр которых больше суммарного фонда финансирования кафедр факультета "Computer Science".
8. Вывести названия дисциплин и полные имена преподавателей, читающих наибольшее количество лекций по ним.
9. Вывести название дисциплины, по которому читается меньше всего лекций.
10. Вывести количество студентов и читаемых дисциплин на кафедре "Software Development".

Homework 05

Тема: "Объединения"

1. Вывести названия аудиторий, в которых читает лекции преподаватель “Edward Hopper”.
2. Вывести фамилии ассистентов, читающих лекции в группе “F505”.
3. Вывести дисциплины, которые читает преподаватель “Alex Carmack” для групп 5-го курса.
4. Вывести фамилии преподавателей, которые не читают лекции по понедельникам.
5. Вывести названия аудиторий, с указанием их корпусов, в которых нет лекций в среду второй недели на третьей паре.
6. Вывести полные имена преподавателей факультета “Computer Science”, которые не курируют группы кафедры “Software Development”.
7. Вывести список номеров всех корпусов, которые имеются в таблицах факультетов, кафедр и аудиторий.
8. Вывести полные имена преподавателей в следующем порядке: деканы факультетов, заведующие кафедрами, преподаватели, кураторы, ассистенты.
9. Вывести дни недели (без повторений), в которые имеются занятия в аудиториях “A311” и “A104” корпуса 6.

Homework 06

Тема: "Работа с таблицами и представлениями"

Задание 1. Все задания необходимо выполнить по отношению к базе данных «Музыкальная коллекция», описанной в практическом задании для этого модуля. Создайте следующие представления:

1. Представление отображает названия всех исполнителей.
2. Представление отображает полную информацию о всех песнях: название песни, название диска, длительность песни, музыкальный стиль песни, исполнитель.
3. Представление отображает информацию о музыкальных дисках конкретной группы. Например, The Beatles.
4. Представление отображает название самого популярного в коллекции исполнителя. Популярность определяется по количеству дисков в коллекции.
5. Представление отображает топ-3 самых популярных в коллекции исполнителей. Популярность определяется по количеству дисков в коллекции.
6. Представление отображает самый долгий по длительности музыкальный альбом.

Задание 2. Все задания необходимо выполнить по отношению к базе данных «Музыкальная коллекция», описанной в практическом задании для этого модуля:

1. Создайте обновляемое представление, которое позволит вставлять новые стили.
2. Создайте обновляемое представление, которое позволит вставлять новые песни.
3. Создайте обновляемое представление, которое позволит обновлять информацию об издателе.
4. Создайте обновляемое представление, которое позволит удалять исполнителей.
5. Создайте обновляемое представление, которое позволит обновлять информацию о конкретном исполнителе. Например, Muse.

Задание 3. Все задания необходимо выполнить по отношению к базе данных «Продажи», описанной в практическом задании для этого модуля:

1. Создайте обновляемое представление, которое отображает информацию о всех продавцах.
2. Создайте обновляемое представление, которое отображает информацию о всех покупателях.
3. Создайте обновляемое представление, которое отображает информацию о всех продажах конкретного товара. Например, яблок.
4. Создайте представление, отображающее все осуществлённые сделки.
5. Создайте представление, отображающее информацию о самом активном продавце. Определяем самого активного продавца по максимальной общей сумме продаж.
6. Создайте представление, отображающее информацию о самом активном покупателе. Определяем самого активного покупателя по максимальной общей сумме покупок.

Используйте опции CHECK OPTION, SCHEMABINDING, ENCRYPTION там, где это необходимо или полезно.

Homework 07

Тема: "Триггеры"

Обращаем ваше внимание, что при именовании баз данных, таблиц, столбцов и других объектов необходимо придерживаться рекомендаций по именованию объектов в базах данных. Наименования объектов в заданиях даны только для объяснения поставленной задачи.

Задание 1. К базе данных «Спортивный магазин» из практического задания к этому модулю создайте следующие триггеры:

1. При добавлении нового товара триггер проверяет его наличие на складе, если такой товар есть и новые данные о товаре совпадают с уже существующими данными, вместо добавления происходит обновление информации о количестве товара.
2. При увольнении сотрудника триггер переносит информацию об уволенном сотруднике в таблицу «Архив сотрудников».
3. Триггер запрещает добавлять нового продавца, если количество существующих продавцов больше 6.

Задание 2. К базе данных «Музыкальная коллекция» из практического задания модуля «Работа с таблицами и представлениями в MS SQL Server» создайте следующие триггеры:

1. Триггер не позволяющий добавить уже существующий в коллекции альбом.
2. Триггер не позволяющий удалять диски группы The Beatles.
3. При удалении диска триггер переносит информацию об удалённом диске в таблицу «Архив».
4. Триггер не позволяющий добавлять в коллекцию диски музыкального стиля «Dark Power Pop».

Задание 3. К базе данных «Продажи» из практического задания модуля «Работа с таблицами и представлениями в MS SQL Server» создайте следующие триггеры:

1. При добавлении нового покупателя триггер проверяет наличие покупателей с такой же фамилией. При нахождении совпадения триггер записывает об этом информацию в специальную таблицу.
2. При удалении информации о покупателе триггер переносит его историю покупок в таблицу «История покупок».
3. При добавлении продавца триггер проверяет есть ли он в таблице покупателей, если запись существует добавление нового продавца отменяется.
4. При добавлении покупателя триггер проверяет есть ли он в таблице продавцов, если запись существует добавление нового покупателя отменяется.
5. Триггер не позволяет вставлять информацию о продаже таких товаров: яблоки, груши, сливы, кинза.

Homework 08

Тема: "Хранимые процедуры"

Обращаем ваше внимание, что при именовании баз данных, таблиц, столбцов и других объектов необходимо придерживаться рекомендаций по именованию объектов в базах данных. Наименования объектов в заданиях даны только для объяснения поставленной задачи.

Задание 1. Для базы данных «Спортивный магазин» из практического задания модуля «Триггеры, хранимые процедуры и пользовательские функции» создайте следующие хранимые процедуры:

1. Хранимая процедура отображает полную информацию о всех товарах.
2. Хранимая процедура показывает полную информацию о товаре конкретного вида. Вид товара передаётся в качестве параметра. Например, если в качестве параметра указана обувь, нужно показать всю обувь, которая есть в наличии.
3. Хранимая процедура показывает топ-3 самых старых клиентов. Топ-3 определяется по дате регистрации.
4. Хранимая процедура показывает информацию о самом успешном продавце. Успешность определяется по общей сумме продаж за всё время.
5. Хранимая процедура проверяет есть ли хоть один товар указанного производителя в наличии. Название производителя передаётся в качестве параметра. По итогам работы хранимая процедура должна вернуть yes в том случае, если товар есть, и no, если товара нет.
6. Хранимая процедура отображает информацию о самом популярном производителе среди покупателей. Популярность среди покупателей определяется по общей сумме продаж.
7. Хранимая процедура удаляет всех клиентов, зарегистрированных после указанной даты. Дата передаётся в качестве параметра. Процедура возвращает количество удалённых записей.

Задание 2. Для базы данных «Музыкальная коллекция» из практического задания модуля «Работа с таблицами и представлениями в MS SQL Server» создайте следующие хранимые процедуры:

1. Хранимая процедура показывает полную информацию о музыкальных дисках.
2. Хранимая процедура показывает полную информацию о всех музыкальных дисках конкретного издателя. Название издателя передаётся в качестве параметра.
3. Хранимая процедура показывает название самого популярного стиля/ Популярность стиля определяется по количеству дисков в коллекции.
4. Хранимая процедура отображает информацию о диске конкретного стиля с наибольшим количеством песен. Название стиля передаётся в качестве параметра, если передано слово all, анализ идёт по всем стилям.
5. Хранимая процедура удаляет все диски заданного стиля. Название стиля передаётся в качестве параметра. Процедура возвращает количество удалённых альбомов.
6. Хранимая процедура отображает информацию о самом «старом» альбом и самом «молодом». Старость и молодость определяются по дате выпуска.
7. Хранимая процедура удаляет все диски в названии которых есть заданное слово. Слово передаётся в качестве параметра. Процедура возвращает количество удалённых альбомов.

Homework 09

Тема: "Финальное практическое задание по модулю"

Обращаем ваше внимание, что при именовании баз данных, таблиц, столбцов и других объектов необходимо придерживаться рекомендаций по именованию объектов в базах данных. Наименования объектов в заданиях даны только для объяснения поставленной задачи.

Задание 1. Используя триггеры, пользовательские функции, хранимые процедуры реализуйте следующую функциональность:

1. Вернуть информацию о барбере, который работает в барбершопе дольше всех.
2. Вернуть информацию о барбере, который обслужил максимальное количество клиентов в указанном диапазоне дат. Даты передаются в качестве параметра.
3. Вернуть информацию о клиенте, который посетил барбершоп максимальное количество раз.
4. Вернуть информацию о клиенте, который потратил в барбершопе максимальное количество денег.
5. Вернуть информацию о самой длинной по времени услуге в барбершопе.

Задание 2. Используя триггеры, пользовательские функции, хранимые процедуры реализуйте следующую функциональность:

1. Вернуть информацию о самом популярном барбере (по количеству клиентов).
2. Вернуть топ-3 барберов за месяц (по сумме денег, потраченной клиентами).
3. Вернуть топ-3 барберов за всё время (по средней оценке). Количество посещений клиентов не меньше 30.
4. Показать расписание на день конкретного барбера. Информация о барбере и дне передаётся в качестве параметра.
5. Показать свободные временные слоты на неделю конкретного барбера. Информация о барбере и дне передаётся в качестве параметра.
6. Перенести в архив информацию о всех уже завершённых услугах (это те услуги, которые произошли в прошлом).
7. Запретить записывать клиента к барберу на уже занятое время и дату.
8. Запретить добавление нового джуниор-барбера, если в салоне уже работают 5 джуниор-барберов.
9. Вернуть информацию о клиентах, которые не поставили ни одного фидбека и ни одной оценки.
   10.Вернуть информацию о клиентах, которые не посещали барбершоп свыше одного года.

Homework 10

Тема: "Индексы"

Обращаем ваше внимание, что при именовании баз данных, таблиц, столбцов и других объектов необходимо придерживаться рекомендаций по именованию объектов в базах данных. Наименования объектов в заданиях даны только для объяснения поставленной задачи.

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

1. Создайте набор clustered (кластеризованных) индексов для тех таблиц, где это необходимо.
2. Создайте набор nonclustered (некластеризованных) индексов для тех таблиц, где это необходимо.
3. Решите нужны ли вам composite (композитные) индексы с учётом структуры базы данных и запросов. Если да, создайте индексы.
4. Решите нужны ли вам indexes with included columns (индексы с включёнными столбцами). Учитывайте структуру базы данных и запросов. Если необходимость есть, создайте индексы.
5. Решите нужны ли вам filtered indexes (отфильтрованные индексы). Учитывайте структуру базы данных и запросов. Если необходимость есть, создайте индексы.
6. Проверьте execution plans (планы выполнения) для наиболее важных запросов с точки зрения частоты их использования. Если найдено слабое место по производительности, попробуйте решить возникшую проблему с помощью создания новых индексов.

Задание 2. Для базы данных «Музыкальная коллекция» из практического задания модуля «Работа с таблицами и представлениями в MS SQL Server» выполните действия:

1. Создайте набор clustered (кластеризованных) индексов для тех таблиц, где это необходимо.
2. Создайте набор nonclustered (некластеризованных) индексов для тех таблиц, где это необходимо.
3. Решите нужны ли вам composite (композитные) индексы с учётом структуры базы данных и запросов. Если да, создайте индексы.
4. Решите нужны ли вам indexes with included columns (индексы с включёнными столбцами). Учитывайте структуру базы данных и запросов. Если необходимость есть, создайте индексы.
5. Решите нужны ли вам filtered indexes (отфильтрованные индексы). Учитывайте структуру базы данных и запросов. Если необходимость есть, создайте индексы.
6. Проверьте execution plans (планы выполнения) для наиболее важных запросов с точки зрения частоты их использования. Если найдено слабое место по производительности, попробуйте решить возникшую проблему с помощью создания новых индексов.

Homework 11

Тема: "Pivot, Unpivot"

Обращаем ваше внимание, что при именовании баз данных, таблиц, столбцов и других объектов необходимо придерживаться рекомендаций по именованию объектов в базах данных. Наименования объектов в заданиях даны только для объяснения поставленной задачи.

Задание 1. Для базы данных «Музыкальная коллекция» выполните следующие задания. Используйте оператор PIVOT:

1. Отобразите информацию об исполнителях и стилях его музыкальных альбомов. Результат должен быть представлен такими столбцами: Исполнитель, Названия стилей (количество столбцов зависит от количества стилей). В столбце Исполнитель должны быть названия исполнителей. В столбцах стилей в качестве значения должно быть количество альбомов исполнителя в том или ином стиле, если альбома в таком стиле нет в столбце будет NULL-значение
2. Отобразите информацию об издателях и стилях изданных музыкальных альбомов. Результат должен быть представлен такими столбцами: Стиль, Названия издателей (количество столбцов зависит от количества издателей). В столбце Стиль должны быть названия стилей. В столбцах издателей в качестве значения должно быть количество альбомов издателя в том или ином стиле, если альбома в таком стиле нет у издателя в столбце будет NULL-значение.

Задание 2. Для базы данных «Музыкальная коллекция» выполните следующие задания. Используйте оператор UNPIVOT по отношению к результатам, полученным в первом задании:

1. Отобразите информацию об исполнителях и стилях его музыкальных альбомов. Результат должен быть представлен такими столбцами: Исполнитель, Стиль, Количество альбомов. В столбце Исполнитель должны быть названия исполнителей. В столбце Стиль названия стилей. В столбце Количество альбомов количество альбомов исполнителя в конкретном стиле
2. Отобразите информацию об издателях и стилях его музыкальных альбомов. Результат должен быть представлен такими столбцами: Издатель, Стиль, Количество альбомов. В столбце Издатель должны быть названия издателей. В столбце Стиль названия стилей. В столбце Количество альбомов количество альбомов издателя в конкретном стиле.

Задание 3. Для базы данных «Спортивный магазин» выполните следующие задания. Используйте оператор PIVOT:

1. Отобразите информацию о производителях и видах товаров. Результат должен быть представлен таким столбцами: Производитель, Вид товаров (количество столбцов зависит от количества видов товаров). В столбце Производитель должны быть названия производителей. В столбцах вид товаров в качестве значения должно быть количество видов товаров в наличии этого конкретного производителя, если вида товара нет в наличии в столбце будет NULL-значение
2. Отобразите информацию о производителях и видах товаров. Результат должен быть представлен таким столбцами: Вид товара, Производитель (количество столбцов зависит от количества производителей). В столбце Вид товара должны быть названия товаров. В столбцах производителей в качестве значения должно быть количество единиц товара в наличии этого конкретного производителя, если вида товара у производителя нет в наличии в столбце будет NULL-значение.

Homework 12

Тема: "Безопасность"

Обращаем ваше внимание, что при именовании баз данных, таблиц, столбцов и других объектов необходимо придерживаться рекомендаций по наименованию объектов в базах данных. Наименования объектов в заданиях даны только для объяснения поставленной задачи.

Задание 1. Для базы данных «Спортивный магазин» из практического задания модуля «Триггеры, хранимые процедуры и пользовательские функции» по указанному ниже описанию выполните настройку безопасности доступа к базе данных:

1. Полный доступ к информации из базы данных «Спортивный магазин» имеет только директор и его заместитель.
2. Продавцы могут получать доступ на чтение/запись только к информации о товаре.

Задание 2. Для базы данных «Музыкальная коллекция» из практического задания модуля «Работа с таблицами и представлениями в MS SQL Server» по указанному ниже описанию выполните настройку безопасности доступа к базе данных:

1. Полный доступ к базе данных «Музыкальная коллекция» имеет только хозяин коллекции.
2. Есть фиксированная группа пользователей, которая может получать информацию из базы данных, без права изменения.
3. Отдельно есть группа пользователей, которая имеет право получать информацию только из таблицы стилей.

Homework 13

Тема: "Резервное копирование и восстановление"

Обращаем ваше внимание, что при именовании баз данных, таблиц, столбцов и других объектов необходимо придерживаться рекомендаций по именованию объектов в базах данных. Наименования объектов в заданиях даны только для объяснения поставленной задачи.

Задание. Для базы данных «Спортивный магазин» из практического задания модуля «Триггеры, хранимые процедуры и пользовательские функции» выполните набор действий. Действия выполните, используя операторы T-SQL:

1. Создайте полную (full backup) резервную копию
2. Выполните операции по вставке, обновлению, удалению данных в разных таблицах базы данных
3. Создайте разностную (differential backup) резервную копию
4. Выполните операции по вставке, обновлению, удалению данных в разных таблицах базы данных
5. Создайте резервную копию журнала транзакций (log backup)
6. Проведите восстановление из полной резервной копии
7. Проведите восстановление из разностной резервной копии
8. Проведите восстановление из резервной копии журнала транзакций.
   После каждого этапа восстановления проверяйте наличие восстановленных данных.

LaboratoryWork 01

Тема: "Работа с таблицами и представлениями"

Обращаем ваше внимание, что при именовании баз данных, таблиц, столбцов и других объектов необходимо придерживаться рекомендаций по именованию объектов в базах данных. Наименования объектов в заданиях даны только для объяснения поставленной задачи.

Задание 1. Создайте базу данных «Телефонный справочник». Эта база данных должна содержать одну таблицу «Люди». В таблице нужно хранить: ФИО человека, дату рождения, пол, телефон, город проживания, страна проживания, домашний адрес. Для создания базы данных используйте запрос CREATE DATABASE. Для создания таблицы используйте запрос CREATE TABLE.

Задание 2. Создайте базу данных «Продажи». База данных должна содержать информацию о продавцах, покупателях, продажах. Необходимо хранить следующую информацию:

1. О продавцах: ФИО, email, контактный телефон.
2. О покупателях: ФИО, email, контактный телефон.
3. О продажах: покупатель, продавец, название товара, цена продажи, дата сделки.

Для создания базы данных используйте запрос CREATE DATABASE. Для создания таблицы используйте запрос CREATE TABLE. Обязательно при создании таблиц задавать связи между ними.

Задание 3. Создайте базу данных «Музыкальная коллекция». База данных должна содержать информацию о музыкальных дисках, исполнителях, стилях. Необходимо хранить следующую информацию:

1. О музыкальном диске: название диска, исполнитель, дата выпуска, стиль, издатель.
2. О стилях: названия стилей.
3. Об исполнителях: название.
4. Об издателях: название, страна.
5. О песнях: название песни, название диска, длительность песни, музыкальный стиль песни, исполнитель.

Продумайте правильную структуру базы данных. Для создания базы данных используйте запрос CREATE DATABASE. Для создания таблицы используйте запрос CREATE TABLE. Обязательно при создании таблиц задавать связи между ними.

Задание 4. Все задания необходимо выполнить по отношению к базе данных из третьего задания:

1. Добавьте к уже существующей таблице с информацией о музыкальном диске столбец с краткой рецензией на него.
2. Добавьте к уже существующей таблице с информацией об издателе столбец с юридическим адресом главного офиса.
3. Измените в уже существующей таблице с информацией о песнях размер поля, хранящий название песни.
4. Удалите из уже существующей таблицы с информацией об издателе столбец с юридическим адресом главного офиса.
5. Удалите связь между таблицами «музыкальных дисков» и «исполнителей».
6. Добавьте связь между таблицами «музыкальных дисков» и «исполнителей».

Задание 5. Создайте следующие представления. В качестве базы данных используйте базу данных из третьего задания:

1. Представление отображает названия всех стилей.
2. Представление отображает названия всех издателей.
3. Представление отображает полную информацию о диске: название диска, исполнитель, дата выпуска, стиль, издатель.

LaboratoryWork 02

Тема: "Триггеры"

Обращаем ваше внимание, что при именовании баз данных, таблиц, столбцов и других объектов необходимо придерживаться рекомендаций по именованию объектов в базах данных. Наименования объектов в заданиях даны только для объяснения поставленной задачи.

Задание 1. Создайте базу данных «Спортивный магазин». Эта база данных должна содержать информацию о товарах, продажах, сотрудниках, клиентах. Необходимо хранить следующую информацию:

1. О товарах: название товара, вид товара (одежда, обувь, и т.д.), количество товара в наличии, себестоимость, производитель, цена продажи.
2. О продажах: название проданного товара, цена продажи, количество, дата продажи, информация о продавце (ФИО сотрудника, выполнившего продажу), информация о покупателе (ФИО покупателя, если купил зарегистрированный покупатель).
3. О сотрудниках: ФИО сотрудника, должность, дата приёма на работу, пол, зарплата.
4. О клиентах: ФИО клиента, email, контактный телефон, пол, история заказов, процент скидки, подписан ли на почтовую рассылку.
   Продумайте правильную структуру базы данных. Для создания базы данных используйте запрос CREATE DATABASE. Для создания таблицы используйте запрос CREATE TABLE. Обязательно при создании таблиц задавать связи между ними.

Задание 2. Для базы данных из первого задания создайте триггеры, которые будут решать задачи ниже:

1. При продаже товара, заносить информацию о продаже в таблицу «История». Таблица «История» используется для дубляжа информации о всех продажах.
2. Если после продажи товара не осталось ни одной единицы данного товара, необходимо перенести информацию о полностью проданном товаре в таблицу «Архив».
3. Не позволять регистрировать уже существующего клиента. При вставке проверять наличие клиента по ФИО и email.
4. Запретить удаление существующих клиентов.
5. Запретить удаление сотрудников, принятых на работу до 2015 года.
6. При новой покупке товара нужно проверять общую сумму покупок клиента. Если сумма превысила 50000 грн, необходимо установить процент скидки в 15%.
7. Запретить добавлять товар конкретной фирмы. Например, товар фирмы «Спорт, солнце и штанга».
8. При продаже проверять количество товара в наличии. Если осталась одна единица товара, необходимо внести информацию об этом товаре в таблицу «Последняя Единица».

LaboratoryWork 03

Тема: "Хранимые процедуры"

Обращаем ваше внимание, что при именовании баз данных, таблиц, столбцов и других объектов необходимо придерживаться рекомендаций по именованию объектов в базах данных. Наименования объектов в заданиях даны только для объяснения поставленной задачи.

Задание 1. Создайте следующие хранимые процедуры:

1. Хранимая процедура выводит «Hello, world!».
2. Хранимая процедура возвращает информацию о текущем времени.
3. Хранимая процедура возвращает информацию о текущей дате.
4. Хранимая процедура принимает три числа и возвращает их сумму.
5. Хранимая процедура принимает три числа и возвращает среднеарифметическое трёх чисел.
6. Хранимая процедура принимает три числа и возвращает максимальное значение.
7. Хранимая процедура принимает три числа и возвращает минимальное значение.
8. Хранимая процедура принимает число и символ. В результате работы хранимой процедуры отображается линия длиной равной числу. Линия построена из символа, указанного во втором параметре. Например, если было передано 5 и #, мы получим линию такого вида #####.
9. Хранимая процедура принимает в качестве параметра число и возвращает его факториал. Формула расчёта факториала: n! = 12…n. Например, 3! = 123 = 6.
   10.Хранимая принимает два числовых параметра. Первый параметр – это число. Второй параметр – это степень. Процедура возвращает число, возведённое в степень. Например, если параметры равны 2 и 3, тогда вернётся 2 в третьей степени, то есть 8.

Задание 2. Для базы данных «Продажи» из практического задания модуля «Работа с таблицами и представлениями в MS SQL Server» создайте следующие хранимые процедуры:

1. Хранимая процедура показывает информацию о всех продавцах.
2. Хранимая процедура показывает информацию о всех покупателях.
3. Хранимая процедура показывает полную информацию о продажах.
4. Хранимая процедура показывает полную информацию о всех продажах в конкретный день. Дата продажи передаётся в качестве параметра.
5. Хранимая процедура показывает полную информацию о всех продажах в некотором временном сегменте. Дата старта и конца сегмента передаётся в качестве параметра.
6. Хранимая процедура отображает информацию о продажах конкретного продавца. ФИО продавца передаётся в качестве параметра хранимой процедуры.
7. Хранимая процедура возвращает среднеарифметическую цену продажи в конкретный год. Год передаётся в качестве параметра.

LaboratoryWork 04

Тема: "Финальное практическое задание по модулю"

Обращаем ваше внимание, что при именовании баз данных, таблиц, столбцов и других объектов необходимо придерживаться рекомендаций по именованию объектов в базах данных. Наименования объектов в заданиях даны только для объяснения поставленной задачи.

Задание 1. Создайте базу данных для барбершопа. Необходимо хранить следующую информацию:

1. Данные барберов:
   • ФИО
   • пол
   • контактный телефон
   • email
   • дата рождения
   • дата приёма на работу
   • позиция в барбершопе: чиф-барбер (только один), синьор-барбер, джуниор-барбер
   • список услуг с ценами и длительностью услуги по времени
   • фидбеки клиентов о барбере
   • оценки барберов от клиентов (очень плохо, плохо, нормально, хорошо, великолепно).
2. Расписание барберов
   • доступность по датам и времени
   • запись на дату и время конкретного клиента.
3. Данные клиентов
   • ФИО
   • контактный телефон
   • email
   • фидбеки клиента о барберах
   • оценки клиентов барберам (очень плохо, плохо, нормально, хорошо, великолепно).
4. Архив посещений клиентов
   • клиент
   • барбер
   • услуга (и)
   • дата
   • общая стоимость
   • оценка
   • фидбек.

Задание 2. Используя триггеры, пользовательские функции, хранимые процедуры реализуйте следующую функциональность:

1. Вернуть ФИО всех барберов салона.
2. Вернуть информацию о всех синьор-барберах.
3. Вернуть информацию о всех барберах, которые могут предоставить услугу традиционного бритья бороды.
4. Вернуть информацию о всех барберах, которые могут предоставить конкретную услугу. Информация о требуемой услуге предоставляется в качестве параметра.
5. Вернуть информацию о всех барберах, которые работают свыше указанного количества лет. Количество лет передаётся в качестве параметра.
6. Вернуть количество синьор-барберов и количество джуниор-барберов.
7. Вернуть информацию о постоянных клиентах. Критерий постоянного клиента: был в салоне заданное количество раз. Количество передаётся в качестве параметра.
8. Запретить возможность удаления информации о чиф-барбере, если не добавлен второй чиф-барбер.
9. Запретить добавлять барберов младше 21 года.

LaboratoryWork 05

Тема: "Индексы"

Обращаем ваше внимание, что при именовании баз данных, таблиц, столбцов и других объектов необходимо придерживаться рекомендаций по именованию объектов в базах данных. Наименования объектов в заданиях даны только для объяснения поставленной задачи.

Задание. Для базы данных «Продажи» из практического задания модуля «Работа с таблицами и представлениями в MS SQL Server» выполните набор действий:

1. Создайте набор clustered (кластеризованных) индексов для тех таблиц, где это необходимо.
2. Создайте набор nonclustered (некластеризованных) индексов для тех таблиц, где это необходимо.
3. Решите нужны ли вам composite (композитные) индексы с учётом структуры базы данных и запросов. Если да, создайте индексы.
4. Решите нужны ли вам indexes with included columns (индексы с включёнными столбцами). Учитывайте структуру базы данных и запросов. Если необходимость есть, создайте индексы.
5. Решите нужны ли вам filtered indexes (отфильтрованные индексы). Учитывайте структуру базы данных и запросов. Если необходимость есть, создайте индексы.
6. Проверьте execution plans (планы выполнения) для наиболее важных запросов с точки зрения частоты их использования. Если найдено слабое место по производительности, попробуйте решить возникшую проблему с помощью создания новых индексов.

LaboratoryWork 06

Тема: "Pivot, Unpivot"

Обращаем ваше внимание, что при именовании баз данных, таблиц, столбцов и других объектов необходимо придерживаться рекомендаций по именованию объектов в базах данных. Наименования объектов в заданиях даны только для объяснения поставленной задачи.

Задание 1. Для базы данных «Телефонный справочник» выполните следующие задания. Используйте оператор PIVOT:

1. Отобразите информацию о количестве представителей разных полов в городах. Результат должен быть представлен такими столбцами: Город, Мужчина, Женщина, Другой. В столбце Город должны быть названия городов. В столбцах Мужчина, Женщина, Другой должна быть информация о количестве людей того или иного пола в городах
2. Отобразите информацию о количестве представителей разных полов в странах. Результат должен быть представлен такими столбцами: Страна, Мужчина, Женщина, Другой. В столбце Страна должны быть названия стран. В столбцах Мужчина, Женщина, Другой должна быть информация о количестве людей того или иного пола в странах
3. Отобразите информацию о количестве представителей разных полов в странах. Результат должен быть представлен такими столбцами: Пол, Названия стран (количество столбцов зависит от количества стран). В столбце пол должны быть значения Мужчина, Женщина, Другой. В столбцах Стран должна быть информация о количестве людей того или иного пола в странах.

Задание 2. Для базы данных «Телефонный справочник» выполните следующие задания. Используйте оператор UNPIVOT по отношению к результатам, полученным в первом задании:

1. Отобразите информацию о количестве представителей разных полов в городах. Результат должен быть представлен такими столбцами: Город, Пол, Количество. В столбце Город должны быть названия городов, в столбце Пол информация о поле, в столбце Количество информация о количестве
2. Отобразите информацию о количестве женщин и мужчин в странах. Результат должен быть представлен такими столбцами: Страна, Пол, Количество. В столбце Страна должны быть названия стран, в столбце Пол информация о поле, в столбце Количество информация о количестве.

Задание 3. Для базы данных «Продажи» выполните следующие задания. Используйте оператор PIVOT:

1. Отобразите информацию о покупателях, товарах, суммах. Результат должен быть представлен такими столбцами: Покупатель (в столбце ФИО покупателя), столбцы с названием существующих товаров. В качестве значения для столбца с товаром должна быть общая сумма затрат на товар (если товар не приобретался, в столбце значение NULL)
2. Отобразите информацию о покупателях, товарах, суммах. Результат должен быть представлен такими столбцами: Товар (в столбце названия товаров), столбцы с ФИО покупателей. В качестве значения для столбца с ФИО покупателя должна быть общая сумма затрат на товар (если товар не приобретался, в столбце значение NULL).

Решения тестов

Тесты

1. Test 01 "Введение в теорию баз данных"
2. Test 02 "Основы взаимодействия с MS SQL Server"