Файл: Методические указания к практическим занятиям для студентов направления подготовки 230100. 68.pdf

17
(SELECT UserID FROM [Like]
EXCEPT
SELECT UserID FROM [Like] WHERE SongID IN
(SELECT SongID
FROM [Like]
GROUP BY SongID
HAVING COUNT(DISTINCT UserID) > 1))
FirstName LastName
Определить самый популярный музыкальный жанр среди мело- манов из Нью-Йорка.
SELECT TOP 1 WITH TIES
Genre.[Name] AS Genre
FROM Song
LEFT JOIN [Like] ON Song.ID = [Like].SongID
JOIN SongGenre ON Song.ID = SongGenre.SongID
JOIN Genre ON Genre.ID = SongGenre.GenreID 24
WHERE [Like].UserID IN
(SELECT [User].ID
FROM [User] JOIN City ON [User].CityID = City.ID
WHERE City.[Name] = 'New York')
GROUP BY Genre.[Name]
ORDER BY ISNULL(SUM(Score), 0) DESC
Подсчитать, сколько людей из Америки слушают «The Beatles».
SELECT COUNT(DISTINCT [Like].UserID) AS [Count]
FROM [Like] JOIN Song ON [Like].SongID = Song.ID
JOIN SongArtist ON Song.ID = SongArtist.SongID
JOIN Artist ON Artist.ID = SongArtist.ArtistID
WHERE
Artist.[Name] = 'The Beatles' AND
[Like].UserID IN
(SELECT [User].ID
FROM [User]
JOIN City ON [User].CityID = City.ID
JOIN Country ON Country.ID = City.CountryID
WHERE Country.[Name] = 'USA')

18
Определить название самой популярную песни жанра «Rock-n-
Roll» в 2012 году.
WITH SongScores2012 AS
(SELECT Song.Title, SUM(Score) AS TotalScore
FROM Song
LEFT JOIN [Like] ON [Like].SongID = Song.ID
JOIN SongGenre ON Song.ID = SongGenre.SongID
JOIN Genre ON Genre.ID = SongGenre.GenreID
WHERE YEAR([Like].Date) = 2012 AND Genre.[Name] = 'Rock-n-
Roll'
GROUP BY Song.ID, Song.Title)
SELECT Title
FROM SongScores2012
WHERE TotalScore = (SELECT MAX(TotalScore) FROM Song-
Scores2012)
Приведем еще пример выборки, осмысленной для предметной области «Музыкальные предпочтения»: порекомендовать пользовате- лю «john@mail.com» 3 самых популярных песни (с исполнителями) жанра «Heavy Metal», которые он еще не слышал. Подобный запрос, можно составить, например, таким образом:
SELECT TOP 3 WITH TIES
(SELECT Artist.[Name] + '; '
FROM Artist JOIN SongArtist ON Artist.ID = SongArtist.ArtistID
WHERE SongArtist.SongID = S.ID FOR XML PATH('')) AS 'Art- ist(s)',
S.[Title]
FROM Song S LEFT JOIN [Like] ON [Like].SongID = S.ID
WHERE EXISTS(
SELECT * FROM SongGenre JOIN Genre ON SongGenre.GenreID =
Genre.ID 25
WHERE Genre.[Name] = 'Heavy Metal' AND SongGenre.SongID =
S.ID
)
AND S.ID NOT IN
(SELECT SongID FROM [Like]
JOIN [User] ON [Like].UserID = [User].ID
WHERE [User].Email = 'john@mail.com')
GROUP BY S.[Title], S.[ID]

19
ORDER BY ISNULL(SUM(Score), 0) DESC
Artist(s) Title
Рассмотрим теперь примеры запросов для удаления и модифика- ции данных. Например, удалим из таблицы предпочтений строки, набравшие в сумме меньше 3 очков:
DELETE FROM [Like]
WHERE SongID IN
(SELECT SongID FROM [Like]
GROUP BY SongID
HAVING SUM(Score) < 3
)
Напишем теперь запрос на удаление данных, который не сраба- тывает из-за ограничений ссылочной целостности:
DELETE FROM Genre
WHERE [Name] = 'Heavy Metal'
Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint
"FK_SongGenre_Genre". The conflict occurred in database "MusicFans", table "dbo.SongGenre", column 'GenreID'.
The statement has been terminated.
Рассмотрим теперь модификацию данных. В качестве первого примера напишем запрос, который заменит все имена и фамилии пользователей на инициалы:
UPDATE [User] SET
FirstName = SUBSTRING(FirstName, 1, 1),
LastName = SUBSTRING(LastName, 1, 1)
SELECT ID, FirstName, LastName FROM [User]
ID FirstName LastName
Продемонстрировать работу проверочных ограничений можно, например, таким запросом:
UPDATE [Like] SET Score = 6 WHERE SongID = 1 26
Msg 547, Level 16, State 0, Line 1

20
The UPDATE statement conflicted with the CHECK constraint
"CK_Like_Score". The conflict occurred in database "MusicFans", table
"dbo.Like", column 'Score'.
The statement has been terminated.
В качестве последнего примера рассмотрим нарушение ограни- чения уникального ключа:
UPDATE [User] SET Email = 'john@mail.com' WHERE ID = 2
Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.User' with unique index
'IX_User'.
The statement has been terminated.
Индивидуальные задания
Каждое задание содержит 4 выборки, которые необходимо запи- сать в виде SQL-команд
Вариант 1. Предметная область «Библиотека»
1. Выбрать книгу по физике, которая наиболее популярна у чита- телей (максимальное число выдач за год).
2. Выбрать читателей, которые имеют задолженность более 2 ме- сяцев.
3.
Определить книгу, которая была наиболее популярной зимой
2005 года.
4. Определить читателей, у которых на руках находятся книги из- дательства "Наука".
Вариант 2. Предметная область «Университет»
1. Выбрать преподавателей, у которых наибольшее количество пар осенью 2005г.
2.
Определить возможные "накладки" у преподавателей в распи- сании.
3.
Вывести расписание занятий преподавателя 'Половинкин
Е.С.' на ноябрь 2005 г.
4. Определить для каждой группы количество экзаменов и заче- тов.

21
Вариант 3. Предметная область «Отдел продаж»
1. Выбрать покупателей, которые покупают наибольшее число разных товаров.
2. Определить покупателя, который покупает товар 'коврик для мыши' по самой высокой в среднем цене.
3. Вывести названия товаров, цены на которые только росли.
4. Определить, какие товары продаются в этом году хуже, чем в прошлом за то же количество месяцев.
Вариант 4. Предметная область «Производство»
1.
Определить изделие, с наибольшей длительностью производ- ства
2.
Вывести список оборудования, требующего замены в ближай- шие полгода
3. Вывести список изделий, которых получается больше всего из единицы объема стали.
4.
Вывести средний срок годности оборудования.
Вариант 5. Предметная область «Кооперативы»
1. Определить самого старого предпринимателя, владеющего па- ем в кооперативе в районе 'Киевский'.
2. Определить самого молодого пайщика кооператива, у которого максимальный пай вклада.
3. Определить случаи, когда больше 50% уставного капитала ко- оператива внесено предпринимателем, проживающим в этом же рай- оне.
4.
Вывести список профилей кооперативов, которыми владеет предприниматель 'Старусин' в порядке возрастания вложенного в них капитала.
Вариант 6. Предметная область «Автомастерская»
1.
Выбрать фамилию того механика, который чаще всех работает с новыми автомобилями.
2. Выбрать фамилию хозяина, чаще всего посещающего мастер- скую и имеющего автомобиль марки "Мерседес"
3.
Определить тех владельцев автомобилей, которых всегда об- служивает один и тот же механик. Вывести фамилии механика и его постоянного клиента.
4.
Для каждой марки автомобиля выбрать механика, чаще всего обслуживающего эту марку.

22
Вариант 7. Предметная область «Сессия»
1. Выбрать группу с наибольшей продолжительностью сессии.
2. Определить преподавателя, который в сессию принимает заче- ты у наибольшего числа студентов.
3.
Определить, процент сдаваемых группой '473' экзаменов от общего числа предметов для каждой категории.
4. Определить, не сдает ли какая-либо группа два экзамена или зачета в один день
Вариант 8. Предметная область «Управление проектом»
1.
Определить те работы в 2000 г., по которым дата завершения превышает крайний срок, то есть проект просрочен.
2.
Определить максимальное количество проектов у одного ра- ботника в 1999 году.
3. Определить те работы, которые к дате завершения были вы- полнены не более, чем на 50%.
4.
Определить 5 сотрудников, выполняющих наибольший объем работ.
Вариант 9. Предметная область «Поликлиника»
1. Определить те случаи, когда пациент лечился менее месяца.
2. Вывести имена тех врачей, которые работают исключительно с инвалидами.
3. Определить процент смертности от заболевания 'кариес'.
4.
Пациентов, которые лечились у врачей всех специальностей.
Вариант 10. Предметная область «Сотовая связь»
1. Выбрать наиболее популярный тариф для каждого оператора.
2.
Определить суммарную задолженность по каждому из тари- фов.
3. Подсчитать, сколько имеется незарегистрированных номеров у каждого из операторов.
4.
Выбрать список абонентов оператора 'МТС', имеющих задол- женность больше 3000.
Вариант 11. Предметная область «Спортивная статистика»
1. Вывести таблицу распределения мест в соревнова- нии 'открытый чемпионат' в городе 'Васюки' по 'шахматам' в 2000 г.
2.
Определить спортсменов, которые имеют как олимпийский, так и мировой рекорд.

23 3. Вывести список спортсменов, участвующих более чем в 8 со- ревнованиях в году в порядке возрастания среднего места на этих со- ревнованиях.
4. Определить наилучший показатель спортсмена 'Караваев' в ви- де спорта 'тараканьи бега и разницу с мировым и олимпийским рекор- дами.
Вариант 12. Предметная область «Поставки»
1. Вывести предприятия, которые провели в текущем году не бо- лее 1 операции.
2.
Определить, предприятие какого вида деятельности является ведущим в поставке продукта 'банан'.
3.
Определить случаи, когда были проданы товары, которые яв- лялись годными не более недели.
4. Вывести список продуктов, для которых закупочная цена, как правило, ниже себестоимости производителя.
Вариант 13. Предметная область «Городской транспорт»
1.
Определить самое прибыльное средство передвижения (отно- шение средняя плата за проезд/средние затраты на топливо макси- мально)
2. Определить среднее отношение числа троллейбусов на марш- руте к числу остановок.
3. Вывести маршруты машины 'Газель' в порядке убывания их протяженности.
4. Определить предполагаемую прибыль фирмы за день.
Вариант 14. Предметная область «География»
1.
Расположить страны по многонациональности, в порядке убы- вания.
2. Подсчитать численность населения монархий.
3.
Определить тип управления той страны, где проживает более всего представителей национальности 'армяне'.
4. Выбрать список национальностей, проживающих в странах с анархическим строем.
Вариант 15. Предметная область «Домоуправление»
1. Определить сумму месячной оплаты для квартиры №50 в доме
302 бис по Садовой улице.

24 2. Определить задолженность по оплате 'доставка мусо- ра' Константинова Романа Викторовича.
3. Определить общую сумму выплат для квартир по Первомай- ской улице.
4.
Выбрать список владельцев, которые имеют задолженности более года.
Контрольные вопросы
1.
Объяснить, как работают написанные запросы.
2.
Исправить неверно работающий запрос (запросы).
3.
Упростить один или несколько запросов.
4. Написать или модифицировать запрос по сформулированному заданию.

25
П р а к т и ч е с к о е з а н я т и е № 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

26
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 = SongAl- bum.SongID
LEFT JOIN Album ON Album.ID = SongAlbum.AlbumID
GROUP BY Artist.[Name], Artist.ID)
GO

27
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

28
(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. Предметная область «Библиотека»
1.
Тема, название книги, фамилия читателя, дата возврата – для книг, у которых просрочен срок возврата, количество дней просрочки.
2.
ФИО читателей, номер паспорта, количество прочитанных книг – для читателей, которые всегда приносят книги в срок.
3.
Для книги "Русские сказки" показать все ее состояния за 5 по- следних лет.
4. Для каждого издательства подсчитать количество книг в биб- лиотеке и суммарное количество их выдач.
Вариант 2. Предметная область «Университет»
1. Название предмета, количество лекций, семинаров, лаборатор- ных занятий по нему.

29 2. Группа, дата, время, название предмета, преподаватель, ауди- тория. (вид занятия – лекция)
3. Название кафедры, название предмета, количество преподава- телей, с ними связанных.
4.
Группа, предмет, преподаватель, вид проверки.
Вариант 3. Предметная область «Отдел продаж»
1. Покупатель, товар, дата, общая стоимость покупки.
2.
Покупатель, месяц, суммарное число единиц товара, куплен- ных покупателем в этот месяц, суммарная стоимость товаров.
3.
Производитель, месяц, общее количество единиц товаров, про- данных в этот месяц, суммарная стоимость товаров.
4.
Название товара, производитель, средняя цена за последний год.
Вариант 4. Предметная область «Производство»
1.
Название изделия, название материала, количество, единицы измерения.
2. Название оборудования, производитель, название специфика- ции.
3. Название оборудования, количество материала и альтернатив, количество спецификаций, связанных с изделиями, произведенными на этом оборудовании.
4. Название материала, тип, суммарная длительность производ- ства спецификаций изделия.
Вариант 5. Предметная область «Кооперативы»
1. Название, профиль, число работников, имя владельца с макси- мальным вкладом, процент его вклада от общего вклада
2.
Название кооператива, ФИО владельца, размер вклада.
3.
Район проживания, количество кооперативов в этом районе, количество человек из этого района, имеющих пай в каком-либо ко- оперативе
4.
Год, профиль кооператива, количество человек, внесших свой вклад в этот год в кооператив данного профиля.
Вариант 6. Предметная область «Автомастерская»
1. ФИО владельца, марка и год выпуска его автомобиля, стои- мость заказа и имя механика.