Методические указания к выполнению лабораторных и курсовой работ ЙошкарОла 2007
 Скачать 0.8 Mb.
|
|
Лабораторные задания типа B Составить SQL-запросы по их заданному содержательному описанию, выполнить SQL-запросы на SQL-сервере с использованием клиентских утилит Query Analyzer или SQL-EM, проинтерпретировать результаты выполнения запросов. 1) Выбрать имена и фамилии авторов книг. 2) Выбрать имена и фамилии авторов, проживающих в Калифорнии. 3) Выбрать информацию о книгах, объеме (стоимость) продаж которых в текущем году меньше стоимости предварительной продажи. Информация о книгах должна включать тип книги, идентификатор и цену книги. 4) Выбрать информацию об авторах, проживающих в штате Калифорния или в городе Salt Lake City. Информация об авторах должна включать идентификатор автора, город и штат проживания. 5) Выбрать все идентификаторы и цены книг, причем цена книги должна лежать в диапазоне от 5 до 10 долларов. В SQL запросе использовать предикат BETWEEN. 6) Выбрать все идентификаторы и цены книг по современной и традиционной кулинарии и по бизнесу. В запросе использовать предикат IN. 7) Выбрать информацию об авторах, проживающих в городах, название которых начинается со строки “spring”. Информация об авторах должна включать имя и фамилию автора, а также штат и город проживания. 8) Выбрать информацию о книгах, идентификаторы которых начинаются буквой “B”, а кончаются строкой “1342”. Информация о книгах должна включать тип, идентификатор и цену книги. 9) Выбрать информацию о книгах, идентификаторы которых начинаются буквой “B”, заканчиваются строкой “1342”, а вторым символом идентификатора являются буквы “A”, “U” или “N”. Информация о книгах должна включать тип, идентификатор и цену книги. 10) Выбрать имена и фамилии всех авторов, упорядоченные по возрастанию фамилий авторов. 11) Выбрать имена и фамилии всех авторов, упорядоченные в первую очередь по возрастанию фамилий и, во вторую очередь, по возрастанию имен. 12) Выбрать информацию о книгах, упорядоченную по возрастанию объема продаж (по стоимости). Информация о книгах должна включать идентификатор, цену, объем продаж (по количеству) и объем продаж (по стоимости). 13) То же, что 12, но использовать упорядочение по убыванию. 14) Выбрать информацию о всех книгах, упорядоченную по убыванию типа книги и числа проданных книг. Информация о книгах должна включать идентификатор и тип книги, а также число проданных книг. 15) Определить среднюю цену книги. 16) Определить среднюю цену книг по бизнесу. 17) Определить среднюю цену и стоимость всех книг по бизнесу и современной кулинарии 18) Определить число авторов, проживающих в Калифорнии. 19) Определить среднюю цену и сумму цен на книги по бизнесу и современной кулинарии отдельно для каждого типа книги. 20) Определить среднюю цену и сумму цен на книги по бизнесу и современной кулинарии для каждой комбинации типа книги и идентификатора издателя. 21) Выбрать те типы книг, средняя цена дорогих экземпляров (стоимостью более 10 долларов) которых превышает 20 долларов. В выбираемые данные помимо типа книги включить и среднюю цену дорогих экземпляров. 22) Подсчитать число строк в таблице authors, включающих одинаковые идентификаторы авторов. В выбираемые данные включить идентификатор автора и соответствующее ему число повторяющихся строк. 23) Выбрать названия книг и имена выпустивших их издателей. 24) То же, что и 23, но в разделе FROM запроса использовать операцию соединения JOIN. 25) Произвести проекцию на столбцы title и pub_name декартова произведения таблиц titles и publishers. 26) Определить среднюю цену выпускаемых каждым издателем книг. В выбираемые данные включить имя издателя и среднюю цену книги. 27) То же, что и 26, но в разделе FROM запроса использовать операцию соединения JOIN. 28) Определить, кто из авторов написал какую книгу по психологии. В выбираемые данные включить имя и фамилию автора, а также название книги. 29) То же, что и 28, но в разделе FROM запроса использовать операцию соединению JOIN. 30) Выбрать все столбцы результата эквисоединения таблиц titles publishers по идентификатору издателя. 31) Выбрать все столбцы таблицы titles и столбец pub_name таблицы publishers результата эквисоединения данных таблиц по идентификатору издателя. 32) Выбрать все книги издательства Algodata Infosysytems. В запросе использовать подзапрос для определения нужного идентификатора издателя. В условии поиска использовать предикат “=”. В выбираемые данные включить название книги. 33) Выбрать всех издателей литературы по бизнесу. В запросе использовать подзапрос для выборки нужных идентификаторов издателей. В условии поиска использовать предикат IN. В выбираемые данные включить имя издателя. 34) Выбрать всех издателей литературы по бизнесу. В запросе использовать подзапрос, формирующий промежуточную таблицу, в которую включаются те строки из таблицы titles, которые могут “экви- соединиться” по идентификатору издателя со строками из таблицы publishers и которые представляют тип книг по бизнесу. В условии поиска основного запроса использовать предикат EXISTS. В выбираемые данные включить имя издателя. 35) Выбрать издателей, не выпускающих книг по бизнесу. Дополнительные условия формирования запроса взять из варианта 34. 36) Выбрать издателей, не выпускающих книг по бизнесу. Дополнительные условия формирования запроса взять из варианта 33. 37) Выбрать тип и цену для всех книг, цена которых не превышает средней. В запросе использовать подзапрос, определяющий среднюю цену книг. 38) Выбрать тип и среднюю цену книг данного типа, причем эта средняя цена должна быть меньше средней цены всех книг. В запросе использовать подзапрос, определяющий среднюю цену всех книг. 39) Определить города и штаты проживания каждого из авторов и издателей в виде одной результирующей таблицы. 40) Определить все типы книг. Типы книг в результирующей таблице не должны повторяться. Вывести типы книг в порядке возрастания. 41) Определить все города, в которых проживают авторы. Названия городов в результирующей таблице не должны повторяться. Вывести названия городов в порядке убывания. 42) Определить все штаты, в которых проживают авторы. Названия штатов в результирующей таблице не должны повторяться. Вывести названия штатов в порядке возрастания. 43) Определить страны, в которых расположены издательства книг. Названия стран в результирующей таблице не должны повторяться. Вывести названия стран в порядке убывания. 44) Определить все города, в которых проживают авторы и находятся издательства. Названия городов в результирующей таблице не должны повторяться. Вывести названия городов в порядке возрастания. 45) Определить все штаты, в которых проживают авторы и находятся издательства. Названия штатов в результирующей таблице не должны повторяться. Вывести названия штатов в порядке убывания. 46) Определить города и штаты совместного проживания авторов и издателей. (В запросе неявно реализуется операцию пересечения). 47) Определить города и штаты проживания авторов, в которых нет издательств. (В запросе неявно реализуется операция разности). 48) Определить города и штаты нахождения издательств, в которых не проживают авторы. (В запросе неявно реализуется операция разности). 49) Определить, какой город в каком штате находится. Вывести названия городов в порядке возрастания. 50) Определить число книг, название которых начинается со строки “The” и заканчивается буквой “e”. 51) Определить авторов на букву “G”, проживающих в штатах Теннесси, Иллинойс, Канзас, Орегон или Калифорния, которые опубликовали книги, в которых есть слово из трех букв, причем средней буквой является буква “a”. 52) Определить минимальную, максимальную и среднюю цену для каждого из типов книг. Выводимые данные должны быть упорядочены по убыванию типа книг. 53) Определить минимальную и максимальную цену для каждого из типов книг. В результирующую таблицу не включать те типы книг, для которых разность между максимальной и средней ценой меньше 7 долларов. 54) Вычислить среднюю цену всех книг и медиану цены. Под медианой понимается среднее значение всех различных цен всех книг. 55) Определить, какие авторы в каких издательствах опубликовали сколько книг. 56) Определить книги, авторы и издатели которых живут в одном городе. 57) Определить для каждого штата минимальную, максимальную и среднюю цену книг авторов, проживающих в одном штате (кроме штата Калифорния). 58) Определить, какие авторы опубликовали какие книги в США по традиционной кулинарии или в Германии по компьютерам. 59) Найти цену самой дешевой книги (книг), вышедшей в США. В запросе использовать операцию группирования. 60) Найти авторов самых дорогих книг, вышедших в США. В запросе использовать подзапрос и операцию группирования. 61) Найти авторов, у которых вышли самые нераспродаваемые книги. 62) Найти цену самой дорогой книги (книг), вышедшей в США. В запросе использовать подзапрос. 63) Определить число книг по компьютерам, выпущенных каждым издательством. 64) Определить авторов из городов, начинающихся с букв “A”, “B” или “C” или имеющих в своем составе слово “Salt”, и написавших книги, в названии которых есть определенный или неопределенный артикль английского языка. 65) Определить города и штаты проживания авторов и издателей, за исключением городов и штатов их совместного проживания. (В запросе неявно реализуется операция симметрической разности). 66) Определить названия и цену самых дешевых книг, вышедших в США. (Самые дешевые книги имеют минимальную цену). 67) Определить издательство, в котором опубликовано меньше всего книг. 68) Найти книги, цена которых меньше цены каждой из книг по традиционной кулинарии. 69) Определить местонахождение издательств, цена каждой книги которых меньше 22 долларов. В запросе использовать подзапросы и предикат с квантором. 70) Определить штаты (кроме штатов Индиана, Канзас, Юта), в которых сумма цен выпущенных в них книг больше суммы цен книг, выпущенных в городе Вашингтон. 71) Найти издательство, выпустившее свою самую дорогую книгу с наиболее низкой ценой среди всех издательств. В запросе использовать подзапрос, определяющий максимальные цены книг, выпущенные каждым издательством. 72) Определить полную информацию об издателях книг по компьютерам, авторы которых живут в США (за исключением штата Юта). В запросе использовать подзапросы. 73) Определить книги, стоимости которых составляют не более средней стоимости по издательству, где издавались эти книги. 74) Определить для каждого штата число находящихся в нем издательств. 75) Определить число городов, в которых выпускается литература по компьютерам. В запросе использовать подзапрос. 76) Определить авторов, хотя бы одна книга которых была опубликована в штате Массачусетс. В запросе использовать подзапросы и предикат с квантором. 77) Найти издательства, среди изданных книг которых найдется хоть одна книга по компьютерам стоимостью более двух долларов. В запросе использовать подзапрос и предикат с квантором. 78) Определить штаты, во всех издательствах которых все изданные книги имеют цену более 10 долларов. В запросе использовать подзапросы и предикат с квантором. 79) Определить издательства, для каждой книги которых выполняется условие: “Если книга выпущена в данном издательстве, то хотя бы один из авторов книги проживает в штате, в котором находится издательство, некоторые выпущенные книги которого посвящены компьютерам”. 80) Выбрать все столбцы таблицы titles. 81) Выбрать все столбцы декартова произведения таблиц titles и publishers. 82) Определить книги, число продаж для которых неопределено. 83) Определить минимальную и максимальную цену книг, выпущенных издательствами. 84) Определить авторов, хотя бы одна книга которых была опубликована в штате Массачусетс. В запросе не использовать предикаты с квантором. 85) Найти издательства, среди изданных книг которых найдется хоть одна книга по традиционной кулинарии стоимостью от 12 до 16 долларов. В запросе не использовать предикаты с квантором. 86) Определить для каждого издательства число изданных им дешевых книг (ценой менее 13 долларов). 87) Определить для штатов число издательств, в которых выпускаются только книги ценой более 7 долларов. В запросе использовать подзапросы и предикат с квантором. 88) Определить, сколько авторов имеет каждая изданная книга. 89) Определить штаты и число находящихся в них издательств, выпустивших книги. 90) Определить издательства, не выпустившие книг. 91) Определить неопубликованные в издательствах книги. 92) Определить авторов, работающих по контракту и имеющих телефон с кодом города 415 (первые три цифры номера телефона). 93) Определить номера телефонов авторов, проживающих на Седьмой Авеню (Seventh Av.) 94) Определить книги, выпущенные в период с 1 июля 1991 г. по 30 октября 1991 г. (По умолчанию сервер работает с датами в формате xx/yy/zz как с последовательностями месяц/день/год). 95) Вычислить для каждого типа книг среднее арифметическое минимальной и максимальной цены. Результат упорядочить по убыванию значений. 96) Определить временные интервалы, в рамках которых опубликованы книги разных типов. Примечания: 1. При упорядочении фамилий и имен авторов, городов, штатов, типов книг используется лексикографический порядок.2. “Издатель” и “издательство” являются в данном случае синонимами. Соответственно этому синонимами являются “имя издателя” и “название издательства”. Варианты лабораторных заданий
Лабораторная работа N 2 Создание, модификация и удаление объектов базы данных с использованием SQL Цель работы: изучить языки определения и манипулирования данными SQL, получить практические навыки составления SQL- запросов для работы с таблицами, индексами, представлениями, курсорами, хранимыми процедурами, триггерами и пользовательскими функциями, а также их выполнения на SQL-сервере с использованием клиентских утилит. Порядок выполнения работы Изучение языка баз данных 1. Изучить язык определения данных SQL, включая операторы создания таблиц (CREATE TABLE), создания представлений (CREATE VIEW), модификации таблиц (ALTER TABLE), удаления таблиц (DROP TABLE), удаления представлений (DROP VIEW), а также операторы создания индексов (CREATE INDEX) и удаления индексов (DROP INDEX). Изучить типы ограничений и способы их представления на языке SQL. 2. Изучить операторы манипулирования данными, связанные с курсором, включая оператор объявления курсора (DECLARE CURSOR), оператор открытия курсора (OPEN), оператор чтения очередной строки курсора (FETCH), оператор позиционного удаления (DELETE), оператор позиционной модификации (UPDATE), оператор закрытия курсора (CLOSE). 3. Изучить одиночные операторы манипулирования данными, включая) операторы поискового удаления (DELETE) и поисковой модификации (UPDATE), а также оператор включения (INSERT). 4. Изучить операторы создания, использования и выполнения хранимых процедур, триггеров и пользовательских функций. 5. Изучить интегрированную утилиту SQL Enterprise Manager, а также системные хранимые процедуры с точки зрения их использования для просмотра объектов базы данных, создания и удаления таблиц. В частности, изучить работу с системной хранимой процедурой sp_help, используемой для получения информации о базе данных и объектах базы данных. Работа непосредственно с таблицей 1. В соответствии с вариантом задания разработать точную структуру строк (записей) таблицы, включая выбор типов данных для каждого поля строки. 2. Составить оператор создания таблицы с учетом приведенных в задании ограничений, выполнить данный оператор на SQL-сервере с использованием клиентской утилиты. Просмотреть результат выполнения данного оператора с помощью системной хранимой процедуры sp_help или клиентской утилиты SQL-EM. 3. Заполнить созданную таблицу данными с использованием оператора включения. Просмотреть заполненную таблицу. 4. Изменить одну или несколько строк таблицы с использованием оператора поисковой модификации. Просмотреть измененную таблицу. 5. Удалить одну или несколько строк из таблицы с использованием оператора поискового удаления. Просмотреть измененную таблицу. 6. Добавить столбец в таблицу с использованием оператора модификации таблицы. Просмотреть измененную таблицу. 7. Удалить ограничение из таблицы с использованием оператора модификации таблицы. Просмотреть результат выполнения оператора. Работа с курсором 1. Объявить скроллируемый курсор в соответствии со спецификацией курсора из лабораторного задания. 2. Открыть курсор. 3. Просмотреть первую, j-ю и последнюю строки результирующей таблицы, а также последовательно всю результирующую таблицу от начала и до конца и от конца до начала (число j задается преподавателем). 4. Удалить n-ю строку результирующей таблицы с использованием оператора позиционного удаления (число n задается преподавателем). Просмотреть базовую таблицу и сравнить ее c предыдущим вариантом. 5. Модифицировать k-ю строку результирующей таблицы, изменив значение одного или нескольких полей, с использованием оператора позиционной модификации (число k задается преподавателем). Просмотреть базовую таблицу и сравнить ее c предыдущим вариантом. 6. Закрыть курсор. Работа с представлением 1. В соответствии с заданием составить оператор создания представления, выполнить данный оператор на SQL-сервере с использованием клиентской утилиты. Просмотреть результат выполнения данного оператора с помощью системной хранимой процедуры sp_help или клиентской утилиты SQL-EM. Просмотреть представляемую таблицу. 2. Включить несколько записей в представляемую таблицу с использованием оператора включения. Просмотреть представляемую и базовую таблицы и сравнить их с предыдущими вариантами этих таблиц. 3. Изменить несколько строк представляемой таблицы с использованием оператора поисковой модификации. Просмотреть представляемую и базовую таблицы и сравнить их с предыдущими вариантами этих таблиц. 4. Удалить несколько строк из представляемой таблицы с использованием оператора поискового удаления. Просмотреть представляемую и базовую таблицы и сравнить их с предыдущими вариантами этих таблиц. 5. Удалить представление с использованием оператора удаления представления. Работа с индексами 1. Создать индекс, который бы позволял быстрый поиск по первичному ключу, содержащему столбцы уникальности. 2. Составить и выполнить какой-нибудь запрос к индексированной таблице. 3. Удалить индекс. 4. Удалить базовую таблицу с использованием оператора удаления таблицы. Работа с хранимыми процедурами 1. В соответствии с вариантом задания создать хранимую процедуру. 2. Выполнить созданную хранимую процедуру и просмотреть результаты. Работа с триггерами 1. Создать триггер AFTER и триггер INSTEAD OF для команд (команды распределены согласно варианту). При этом использовать таблицы вставки и удаления.
2. Составить и выполнить какой-нибудь запрос, приводящий в действие триггер и просмотреть результаты его работы. Работа с пользовательскими функциями 1. Cоздать пользовательскую функцию. При нечетном номере варианта создать табличную функцию, а при четном - скалярную. 2. Выполнить созданную пользовательскую функцию и просмотреть результаты. |