Практикум по Базам Данных (Методические материалы) (1127013), страница 5

Просмтор этого файла доступен только зарегистрированным пользователям. Но у нас супер быстрая регистрация: достаточно только электронной почты!

Текст из файла (страница 5)

Операторы манипулирования даннымиязыка SQLПостановка задачиЧетвертое практическое задание посвящено манипулированию данными с помощьюоператоров SQL. В ходе выполнения четвертого практического задания необходимо: Составить SQL-скрипты для выполнения выборок, указанных в индивидуальном варианте.Кроме этого, нужно подготовить еще 3-4 выборки, которые имеют осмысленное значениедля предметной области, и также составить для них SQL-скрипты. Сформулировать 3-4 запроса на изменение и удаление из базы данных.

Запросы должныбыть сформулированы в терминах предметной области. Среди запросов обязательнодолжны быть такие, которые будут вызывать срабатывание ограничений целостности.Составить SQL-скрипты для выполнения этих запросов.Темы для проработкиОператор SELECT.Оператор UPDATE.Оператор DELETE.Декларативные ограничения целостности.ПримерыРассмотрим несколько примеров, показывающих различные способы построения запросов инекоторые полезные возможности MS SQL Server 2005:Вывести имена и фамилии меломанов, которым нравятся только песни, которые не нравятсябольше никому.SELECT FirstName, LastNameFROM [User] WHERE [User].ID IN(SELECT UserID FROM [Like]EXCEPTSELECT UserID FROM [Like] WHERE SongID IN(SELECT SongIDFROM [Like]GROUP BY SongIDHAVING COUNT(DISTINCT UserID) > 1))FirstNameLastName---------------------- ----------------------TommyAndersonОпределить самый популярный музыкальный жанр среди меломанов из Нью-Йорка.SELECT TOP 1 WITH TIESGenre.[Name] AS GenreFROM SongLEFT JOIN [Like] ON Song.ID = [Like].SongIDJOIN SongGenre ON Song.ID = SongGenre.SongIDJOIN Genre ON Genre.ID = SongGenre.GenreID23WHERE [Like].UserID IN(SELECT [User].IDFROM [User] JOIN City ON [User].CityID = City.IDWHERE City.[Name] = 'New York')GROUP BY Genre.[Name]ORDER BY ISNULL(SUM(Score), 0) DESCGenre---------------------------------------Rock-n-RollПодсчитать, сколько людей из Америки слушают «The Beatles».SELECT COUNT(DISTINCT [Like].UserID) AS [Count]FROM [Like] JOIN Song ON [Like].SongID = Song.IDJOIN SongArtist ON Song.ID = SongArtist.SongIDJOIN Artist ON Artist.ID = SongArtist.ArtistIDWHEREArtist.[Name] = 'The Beatles' AND[Like].UserID IN(SELECT [User].IDFROM [User]JOIN City ON [User].CityID = City.IDJOIN Country ON Country.ID = City.CountryIDWHERE Country.[Name] = 'USA')Count----------4Определить название самой популярную песни жанра «Rock-n-Roll» в 2012 году.WITH SongScores2012 AS(SELECT Song.Title, SUM(Score) AS TotalScoreFROM SongLEFT JOIN [Like] ON [Like].SongID = Song.IDJOIN SongGenre ON Song.ID = SongGenre.SongIDJOIN Genre ON Genre.ID = SongGenre.GenreIDWHERE YEAR([Like].Date) = 2012 AND Genre.[Name] = 'Rock-n-Roll'GROUP BY Song.ID, Song.Title)SELECT TitleFROM SongScores2012WHERE TotalScore = (SELECT MAX(TotalScore) FROM SongScores2012)Title--------------------------BoysAnna (Go to Him)Приведем еще пример выборки, осмысленной для предметной области «Музыкальныепредпочтения»: порекомендовать пользователю «john@mail.com» 3 самых популярных песни (сисполнителями) жанра «Heavy Metal», которые он еще не слышал.

Подобный запрос, можносоставить, например, таким образом:SELECT TOP 3 WITH TIES(SELECT Artist.[Name] + '; 'FROM Artist JOIN SongArtist ON Artist.ID = SongArtist.ArtistIDWHERE SongArtist.SongID = S.ID FOR XML PATH('')) AS 'Artist(s)',S.[Title]FROM Song S LEFT JOIN [Like] ON [Like].SongID = S.IDWHERE EXISTS(SELECT * FROM SongGenre JOIN Genre ON SongGenre.GenreID = Genre.ID24WHERE Genre.[Name] = 'Heavy Metal' AND SongGenre.SongID = S.ID)AND S.ID NOT IN(SELECT SongID FROM [Like]JOIN [User] ON [Like].UserID = [User].IDWHERE [User].Email = 'john@mail.com')GROUP BY S.[Title], S.[ID]ORDER BY ISNULL(SUM(Score), 0) DESCArtist(s)----------------------------Queen;The Scorpions;Queen;Title-------------------------------------------------------------Great King RatDark LadyMy Fairy KingРассмотрим теперь примеры запросов для удаления и модификации данных.

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

The conflict occurredin database "MusicFans", table "dbo.Like", column 'Score'.The statement has been terminated.В качестве последнего примера рассмотрим нарушение ограничения уникального ключа:UPDATE [User] SET Email = 'john@mail.com' WHERE ID = 2Msg 2601, Level 14, State 1, Line 1Cannot insert duplicate key row in object 'dbo.User' with unique index 'IX_User'.The statement has been terminated.Примеры вопросов по обязательной части Объяснить, как работают написанные запросы. Примеры вопросов по оператору SELECT см. в задании №1.Примеры дополнительных вопросов Исправить неверно работающий запрос (запросы). Упростить один или несколько запросов. Написать или модифицировать запрос по сформулированному заданию.26Практическое задание №5.

Создание и использованиепредставленийПостановка задачиПятое практическое задание посвящено созданию и использованию представлений: требуетсясоставить SQL-скрипты для создания 4 представлений согласно индивидуальному варианту.Темы для проработки Оператор CREATE VIEW. Вставка и модификация данных через представления.ПримерыСоздадим несколько полезных представлений для нашей базы данных.

Пусть в первомпредставлении требуется показывать следующие данные: название песни, суммарная оценка,количество людей, которым нравится эта песня и средняя оценка.IF EXISTS(SELECT * FROM sys.viewsWHERE [name] = 'SongScores' ANDschema_id = SCHEMA_ID('dbo'))DROP VIEW SongScoresGOCREATE VIEW SongScores AS(SELECTSong.Title,ISNULL(SUM([Like].Score), 0) AS TotalScore,COUNT([Like].UserID) AS Fans,ISNULL(AVG(1.0*[Like].Score), 0) AS AverageScoreFROM SongLEFT JOIN [Like] ON Song.ID = [Like].SongIDGROUP BY Song.ID, Song.Title)GOSELECT * FROM SongScoresTitle-----------------------------Drifting SunFly to the RainbowDark LadyIn TranceLife is Like A RiverI Saw Her Standing ThereMiseryAnna (Go to Him)ChainsBoysGood Times, Bad TimesBabe I’m Gonna Leave YouYou Shook MeDazed And ConfusedYour Time Is Gonna ComeKeep Yourself AliveDoing AlrightGreat King RatMy Fairy KingLiarTotalScore----------40894065055556040950Fans----------10221021011112010210AverageScore-----------------4.0000000.0000004.0000004.5000004.0000000.0000003.0000005.0000000.0000005.0000005.0000005.0000005.0000003.0000000.0000004.0000000.0000004.5000005.0000000.000000В этом примере представление удаляется, если оно существовало, а затем создается, ипроизводится выборка из него, чтобы проверить правильность данных.27Создадим теперь представление, содержащее название исполнителя, количество альбомов сего участием, количество песен с участием этого исполнителя.IF EXISTS(SELECT * FROM sys.viewsWHERE [name] = 'Artists' ANDschema_id = SCHEMA_ID('dbo'))DROP VIEW ArtistsGOCREATE VIEW Artists AS(SELECTArtist.[Name] AS Artist,COUNT(DISTINCT Album.ID) AS AlbumCount,COUNT(DISTINCT SongArtist.SongID) AS SongCountFROM ArtistLEFT JOIN SongArtist ON Artist.ID = SongArtist.ArtistIDLEFT JOIN SongAlbum ON SongArtist.SongID = SongAlbum.SongIDLEFT JOIN Album ON Album.ID = SongAlbum.AlbumIDGROUP BY Artist.[Name], Artist.ID)GOSELECT * FROM ArtistsArtist----------------------------------The ScorpionsThe BeatlesLed ZeppelinQueenBlack Sabbath3 Doors DownThe OffspringMadonnaThe AnimalsPink FloydDeep PurpleJimi HendrixThe WhoAerosmithThe PoliceEaglesSystem Of A DownThe MisfitsElvis PresleyAlbumCount----------2111000000000000000SongCount----------5555000000000000000В качестве следующего представления рассмотрим распределение активности меломанов помесяцам года: требуется вывести год, месяц, когда была какая-то активность и количество песен,которые понравились меломанам за этот месяц.IF EXISTS(SELECT * FROM sys.viewsWHERE [name] = 'Activity' ANDschema_id = SCHEMA_ID('dbo'))DROP VIEW ActivityGOCREATE VIEW Activity AS(SELECTDATENAME(YYYY, [Like].Date) AS 'Year',DATENAME(MM, [Like].Date) AS 'Month', COUNT([Like].SongID) AS SongCountFROM [Like]GROUP BY DATENAME(YYYY, [Like].Date), DATENAME(MM, [Like].Date))GOSELECT * FROM ActivityYear-----------------------------20112010Month-----------------------------AprilAugust28SongCount----------11201020112010201020112012201120122011201220102010201120102011DecemberJanuaryJulyJuneJuneJuneMarchMarchMayMayNovemberOctoberOctoberSeptemberSeptember212112111111111В качестве последнего представления рассмотрим следующее: для каждого пользователявывести имя, фамилию, город и количество меломанов из этого города, которым нравится хотя быодна песня из предпочтений этого пользователя.IF EXISTS(SELECT * FROM sys.viewsWHERE [name] = 'Fans' ANDschema_id = SCHEMA_ID('dbo'))DROP VIEW FansGOCREATE VIEW Fans AS(SELECT FirstName,LastName,Country.[Name] AS Country,City.[Name] AS City,(SELECT COUNT(DISTINCT UserID)FROM [Like]WHERESongID IN(SELECT SongID FROM [Like] WHERE [Like].UserID = U.ID)AND [Like].UserID <> U.ID) AS FansFROM [User] UJOIN City ON U.CityID = City.IDJOIN Country ON City.CountryID = Country.ID)GOSELECT * FROM FansFirstName-------------------JohnMikeAliceMaryJackTommyJanetRickJoannaJillIvanSergeyPetrAnnaKaterinaLastName-------------------SmithJohnsonRobertsonRichardsDareAndersonThomsonJacksonRosenbergJordanPetrovIvanovSidorovIvanovaPetrovaCountry-------------------USAUSAUSAUSAUSAUSAUSAUSAUSAUSARussiaRussiaRussiaRussiaRussiaПримеры вопросов по обязательной части Объяснить, как работают написанные запросы. Рассказать о CHECK OPTION. Рассказать о модификации данных через представления.29City-------------------San-FranciscoWashingtonWashingtonSan-FranciscoNew YorkNew YorkWashingtonNew YorkSan-FranciscoWashingtonMoscowSt.

PetersburgMoscowSt. PetersburgSt. PetersburgFans----------322120000000000 Рассказать о вставке данных через представления. Примеры вопросов по оператору SELECT см. в задании №1.Примеры дополнительных вопросовИсправить неверно работающий запрос (запросы).Упростить один или несколько запросов.Продемонстрировать изменение и вставку данных через представления.Написать или модифицировать запрос по сформулированному заданию.30Практическое задание №6.

Управление транзакциями в MS SQLServerПостановка задачиШестое практическое задание посвящено управлению транзакциями в MS SQL Server:необходимо подготовить SQL-скрипты для проверки наличия аномалий (потерянных изменений,грязных чтений, неповторяющихся чтений, фантомов) при параллельном исполнении транзакцийна различных уровнях изолированности SQL/92 (READ UNCOMMITTED, READ COMMITTED,REPEATABLE READ, SERIALIZABLE).

Характеристики

Список файлов книги