Практическое занятие № 3
Создание и использование представлений
Цель работы: приобретение навыков работы с представлениями.
Темы для предварительной проработки
Оператор создания представления языка SQL.
Выполнение работы
Подготовить SQL-скрипт для создания представлений, заданных в Вашем варианте индивидуального задания..
Сформулировать 3-4 запроса на выполнение выборок из представлений и составить соответствующие SQL-скрипты.
Постановка задачи
Практическое задание посвящено созданию и использованию представлений: требуется составить SQL-скрипты для создания 4 представлений согласно индивидуальному варианту.
Темы для проработки
Оператор CREATE VIEW.
Вставка и модификация данных через представления.
Примеры
Создадим несколько полезных представлений для нашей базы данных. Пусть в первом представлении требуется показывать следующие данные: название песни, суммарная оценка, количество людей, которым нравится эта песня и средняя оценка.
IF EXISTS(
SELECT * FROM sys.views
WHERE [name] = 'SongScores' AND
schema_id = SCHEMA_ID('dbo'))
DROP VIEW SongScores
GO
CREATE VIEW SongScores AS
(SELECT
Song.Title,
ISNULL(SUM([Like].Score), 0) AS TotalScore,
COUNT([Like].UserID) AS Fans,
ISNULL(AVG(1.0*[Like].Score), 0) AS AverageScore
FROM Song
LEFT JOIN [Like] ON Song.ID = [Like].SongID
GROUP BY Song.ID, Song.Title)
GO
SELECT * FROM SongScores
Title TotalScore Fans AverageScore
В этом примере представление удаляется, если оно существовало, а затем создается, и производится выборка из него, чтобы проверить правильность данных.
Создадим теперь представление, содержащее название исполнителя, количество альбомов с его участием, количество песен с участием этого исполнителя.
IF EXISTS(
SELECT * FROM sys.views
WHERE [name] = 'Artists' AND
schema_id = SCHEMA_ID('dbo'))
DROP VIEW Artists
GO
CREATE VIEW Artists AS
(SELECT
Artist.[Name] AS Artist,
COUNT(DISTINCT Album.ID) AS AlbumCount,
COUNT(DISTINCT SongArtist.SongID) AS SongCount
FROM Artist
LEFT JOIN SongArtist ON Artist.ID = SongArtist.ArtistID
LEFT JOIN SongAlbum ON SongArtist.SongID = SongAlbum.SongID
LEFT JOIN Album ON Album.ID = SongAlbum.AlbumID
GROUP BY Artist.[Name], Artist.ID)
GO
SELECT * FROM Artists
Artist AlbumCount SongCount
В качестве следующего представления рассмотрим распределение активности меломанов по месяцам года: требуется вывести год, месяц, когда была какая-то активность и количество песен, которые понравились меломанам за этот месяц.
IF EXISTS(
SELECT * FROM sys.views
WHERE [name] = 'Activity' AND
schema_id = SCHEMA_ID('dbo'))
DROP VIEW Activity
GO
CREATE VIEW Activity AS
(SELECT
DATENAME(YYYY, [Like].Date) AS 'Year',
DATENAME(MM, [Like].Date) AS 'Month', COUNT([Like].SongID) AS SongCount
FROM [Like]
GROUP BY DATENAME(YYYY, [Like].Date), DATENAME(MM, [Like].Date))
GO
SELECT * FROM Activity
Year Month SongCount
В качестве последнего представления рассмотрим следующее: для каждого пользователя вывести имя, фамилию, город и количество меломанов из этого города, которым нравится хотя бы одна песня из предпочтений этого пользователя.
IF EXISTS(
SELECT * FROM sys.views
WHERE [name] = 'Fans' AND
schema_id = SCHEMA_ID('dbo'))
DROP VIEW Fans
GO
CREATE VIEW Fans AS
(SELECT FirstName,
LastName,
Country.[Name] AS Country,
City.[Name] AS City,
(SELECT COUNT(DISTINCT UserID)
FROM [Like]
WHERE
SongID IN
(SELECT SongID FROM [Like] WHERE [Like].UserID = U.ID)
AND [Like].UserID <> U.ID
) AS Fans
FROM [User] U
JOIN City ON U.CityID = City.ID
JOIN Country ON City.CountryID = Country.ID)
GO
SELECT * FROM Fans
FirstName LastName Country City Fans
Индивидуальные задания
Требуется составить SQL-скрипты для создания 4 представлений согласно индивидуальному варианту.
Дополнительно написать команды по модификации данных из представлений с проверкой правильности операции.
Вариант 1. Предметная область «Библиотека»
Тема, название книги, фамилия читателя, дата возврата – для книг, у которых просрочен срок возврата, количество дней просрочки.
ФИО читателей, номер паспорта, количество прочитанных книг – для читателей, которые всегда приносят книги в срок.
Для книги "Русские сказки" показать все ее состояния за 5 последних лет.
Для каждого издательства подсчитать количество книг в библиотеке и суммарное количество их выдач.
Вариант 2. Предметная область «Университет»
Название предмета, количество лекций, семинаров, лабораторных занятий по нему.
Группа, дата, время, название предмета, преподаватель, аудитория. (вид занятия – лекция)
Название кафедры, название предмета, количество преподавателей, с ними связанных.
Группа, предмет, преподаватель, вид проверки.
Вариант 3. Предметная область «Отдел продаж»
Покупатель, товар, дата, общая стоимость покупки.
Покупатель, месяц, суммарное число единиц товара, купленных покупателем в этот месяц, суммарная стоимость товаров.
Производитель, месяц, общее количество единиц товаров, проданных в этот месяц, суммарная стоимость товаров.
Название товара, производитель, средняя цена за последний год.
Вариант 4. Предметная область «Производство»
Название изделия, название материала, количество, единицы измерения.
Название оборудования, производитель, название спецификации.
Название оборудования, количество материала и альтернатив, количество спецификаций, связанных с изделиями, произведенными на этом оборудовании.
Название материала, тип, суммарная длительность производства спецификаций изделия.
Вариант 5. Предметная область «Кооперативы»
Название, профиль, число работников, имя владельца с максимальным вкладом, процент его вклада от общего вклада
Название кооператива, ФИО владельца, размер вклада.
Район проживания, количество кооперативов в этом районе, количество человек из этого района, имеющих пай в каком-либо кооперативе
Год, профиль кооператива, количество человек, внесших свой вклад в этот год в кооператив данного профиля.
Вариант 6. Предметная область «Автомастерская»
ФИО владельца, марка и год выпуска его автомобиля, стоимость заказа и имя механика.
Владелец, марки его автомобилей, суммарная стоимость и время ремонта.
Автомеханик, разряд, суммарное время работы за месяц, суммарная стоимость заказов.
Марка автомобиля, число раз ремонта автомобилей в этой автомастерской, когда время ремонта превышало день.
Вариант 7. Предметная область «Сессия»
Факультет, курс, количество зачетов, количество экзаменов в летнюю сессию.
Группа, название предмета, даты всех экзаменов и зачетов по этому предмету.
ФИО преподавателя, название предмета, количество видов контроля по этому предмету для этого преподавателя.
Название предмета, кафедра, общее количество студентов, сдающих этот предмет в зимнюю сессию.
Вариант 8. Предметная область «Управление проектом»
Проект, сотрудник, трудоемкость, дата выдачи, трудоемкость проекта.
Проект, ФИО работника, трудоемкость, процентное отношение трудоемкости работника к общей трудоемкости проекта.
ФИО работника, проекты за последний месяц, трудоемкость для каждого проекта, время выполнения.
Название проекта, суммарное время работы над проектом за год – для проектов у которых не истек крайний срок в течении года.
Вариант 9. Предметная область «Поликлиника»
Врач, пациент, время лечения – только для вылечившихся пациентов.
Социальный статус, месяц, диагноз, количество человек, вылечившихся за этот месяц.
Врач, количество пациентов за год, лечение которых закончилось успешно, общее количество пациентов за год.
Год рождения, количество пациентов этого года рождения, чаще всего встречающийся у них диагноз.
Вариант 10. Предметная область «Сотовая связь»
Оператор, тариф, количество зарегистрированных абонентов для этого тарифа.
Название оператора, тарифный план, месяц, число подключений в этот месяц.
ФИО абонента, количество тарифных планов, любимый оператор связи.
Оператор связи, тариф, общая задолженность по тарифу за год, количество проданных за год тарифов.
Вариант 11. Предметная область «Спортивная статистика»
Вид спорта, соревнование, место, дата, мировой рекорд, который был установлен.
Вид спорта, количество соревнований за год, количество попаданий спортсменов из 'России' в призеры.
Страна, количество спортсменов этой страны, общее количество побед спортсменов этой страны за год на соревнованиях, в названиях которых входит слово 'плавание'.
Вид спорта, рекорды, установленные в этом виде спорта за последние пять лет, место соревнований, на которых установлены рекорды.
Вариант 12. Предметная область «Поставки»
Предприятие, продукция, год, объем поставок в этом году
Предприятие, месяц, объем продаж за этот месяц, суммарная цена продаж.
Название продукции, предприятие, занимающееся продажей этой продукции, объем продаж за год.
Вид деятельности предприятия, количество предприятий, годовой бюджет отрасли.
Вариант 13. Предметная область «Городской транспорт»
Вид транспорта, № маршрута, начальный пункт, конечный пункт.
Вид транспорта, количество машин на маршрутах, суммарный доход, средний доход на одну машину.
Маршрут, начальный пункт, конечный пункт, все возможные способы добраться не более чем с двумя пересадками в конечных пунктах.
Вид транспорта, среднее количество пассажиров в одной машине, если за день машина делает 20 рейсов, средний доход от одного рейса.
Вариант 14. Предметная область «География»
Государство, национальность, численность, тип управления.
Государство, язык, количество населения, говорящего на этом языке, процент от всего общего количества населения.
Государство, количество мужского населения, количество женского населения, преобладающий язык, преобладающая национальность.
Национальность, изменение численности населения за год, преобладающая страна проживания.
Вариант 15. Предметная область «Домоуправление»
Год, дом, квартира, задолженность по оплате.
Вид услуг, наличие счетчика, внесенная сумма оплаты за год, количество человек, оплативших услугу.
Владелец, услуга, затраты на услугу в год, средняя сумма оплаты за услугу в месяц.
Улица, номер дома, количество услуг, доступных жильцам, суммарная стоимость за месяц.
Контрольные вопросы
Объяснить, как работают написанные запросы.
Рассказать о CHECK OPTION.
Рассказать о модификации данных через представления.
Рассказать о вставке данных через представления.
Упростить один или несколько запросов.
Продемонстрировать изменение и вставку данных через представления.
Написать или модифицировать запрос по сформулированному заданию
|
