Лабораторная работа 1 создание баз данных (БД) в microsoft sql server
 Скачать 1.1 Mb.
|
|
Лабораторная работа №2 ИСПОЛЬЗОВАНИЕ ОПЕРАТОРОВ МАНИПУЛИРОВАНИЯ ДАННЫМИ В MICROSOFT SQL SERVER Цель работы –научиться использовать операторы манипулирования дан- ными Select, Insert, Update, Delete. Содержание работы: Создать с помощью приведенных операторов пример базы данных «Книжное дело», описанный в предыдущей лабораторной работе (если БД отсутствует на сервере). С помощью операторов Insert создать программу в SQL Server Man-agement Studio через «Создать запрос» для заполнения таблиц данными (по 3-5 записей). С помощью оператора Select по заданиям выполнить запросы к БД. Пояснения к выполнению работы Вся теория по данной работе представлена в конспекте лекций. Также при необходимости можно воспользоваться справочными материалами MS SQL Server 2005, запустив утилиту Book OnLine. Варианты заданий к лабораторной работе №2 Общие положения Создать новую базу данных с названием DB_Books с помощью опера-тора Create Database, создать в ней перечисленные таблицы c помощью операторов Create table по примеру лабораторной работы №1. Сохранить файл программы с названием ФамилияСтудента_ЛАб_1_DB_Books. В утилите SQL Server Management Studio с помощью кнопки «Создать за- прос» создать отдельные программы по каждому запросу, которые сохранять на диске с названием: ФамилияСтудента_ЛАб_2_№_задания. В сами программы копировать текст задания в виде комментария. Можно сохранять все выполненные запросы в одном файле. Для проверки работы операторов SELECT предварительно создайте программу, которая с помощью операторов INSERT заполнит все таблицы БД DB_Books несколькими записями, сохраните программы с названием ФамилияСтудента_ЛАб_2_Insert. Список вариантов заданий
Сортировка Выбрать все сведения о книгах из таблицы Books и отсортировать результат по коду книги (поле Code_book). 2.Выбрать из таблицы Books коды книг, названия и количество страниц (поля Code_book, Title_book и Pages), отсортировать результат по названиям книг (поле Title_book по возрастанию) и по полю Pages (по убыванию). 3. Выбрать из таблицы Deliveries список поставщиков (поля Name_delivery, Phone и INN), отсортировать результат по полю INN (по убыванию). Изменение порядка следования полей 4. Выбрать все поля из таблицы Deliveries таким образом, чтобы в ре-зультате порядок столбцов был следующим: Name_delivery, INN, Phone, Address, Code_delivery. 5. Выбрать все поля из таблицы Publishing_house таким образом, что-бы в результате порядок столбцов был следующим: Publish, City, Code_publish. Выбор некоторых полей из двух таблиц Выбрать из таблицы Books названия книг и количество страниц (поля Title_book и Pages), а из таблицы Authors выбрать имя соответст-вующего автора книги (поле Name_ author). Выбрать из таблицы Books названия книг и количество страниц (поля Title_book и Pages), а из таблицы Deliveries выбрать имя соответст-вующего поставщика книги (поле Name_delivery). Выбрать из таблицы Books названия книг и количество страниц (поля Title_book и Pages), а из таблицы Publishing_house выбрать название соответствующего издательства и места издания (поля Publish и City). Условие неточного совпадения Выбрать из справочника поставщиков (таблица Deliveries) названия компаний, телефоны и ИНН (поля Name_company, Phone и INN), у кото-рых название компании (поле Name_company) начинается с ‘ОАО’. Выбрать из таблицы Books названия книг и количество страниц (поля Title_book и Pages), а из таблицы Authors выбрать имя соответст-вующего автора книг (поле Name_ author), у которых название книги на-чинается со слова ‘Мемуары’. Выбрать из таблицы Authors фамилии, имена, отчества авторов (поле Name_ author), значения которых начинаются с ‘Иванов’. Точное несовпадение значений одного из полей Вывести список названий издательств (поле Publish) из таблицы Publishing_house, которые не находятся в городе ‘Москва’ (условие по по- лю City). Вывести список названий книг (поле Title_book) из таблицы Books, которые выпущены любыми издательствами, кроме издательства ‘Питер- Софт’ (поле Publish из таблицы Publishing_house). Выбор записей по диапазону значений (Between) 14. Вывести фамилии, имена, отчества авторов (поле Name_author) из таблицы Authors, у которых дата рождения (поле Birthday) находится в диапазоне 01.01.1840 – 01.06.1860. Вывести список названий книг (поле Title_book из таблицы Books) количество экземпляров (поле Amount из таблицы Purchases), которые были закуплены в период с 12.03.2003 по 15.06.2003 (условие по полю Date_order из таблицы Purchases). Вывести список названий книг (поле Title_book) и количество страниц (поле Pages) из таблицы Books, у которых объем в страницах ук-ладывается в диапазон 200 – 300 (условие по полю Pages). Вывести список фамилий, имен, отчеств авторов (поле Name_author) из таблицы Authors, у которых фамилия начинается на одну из букв диапазона ‘В’ – ‘Г’ (условие по полю Name_author). Выбор записей по диапазону значений (In) Вывести список названий книг (поле Title_book из таблицы Books) количество (поле Amount из таблицы Purchases), которые были постав-лены поставщиками с кодами 3, 7, 9, 11 (условие по полю Code_delivery из таблицы Purchases). Вывести список названий книг (поле Title_book) из таблицы Books, которые выпущены следующими издательствами: ‘Питер-Софт’, ‘Альфа’, ‘Наука’ (условие по полю Publish из таблицы Publishing_house). Вывести список названий книг (поле Title_book) из таблицы Books, которые написаны следующими авторами: ‘Толстой Л.Н.’, ‘Достоевский Ф.М.’, ‘Пушкин А.С.’ (условие по полю Name_author из таблицы Authors). Выбор записей с использованием Like Вывести список авторов (поле Name_author) из таблицы Authors, которые начинаются на букву ‘К’. Вывести названия издательств (поле Publish) из таблицы Publishing_house, которые содержат в названии сочетание ‘софт’. Выбрать названия компаний (поле Name_company) из таблицы Deliveries, у которых значение оканчивается на ‘ский’. Выбор записей по нескольким условиям Выбрать коды поставщиков (поле Code_delivery), даты заказов (по-ле Date_order) и названия книг (поле Title_book), если количество книг (поле Amount) в заказе больше 100 или цена (поле Cost) за книгу находится диапазоне от 200 до 500. Выбрать коды авторов (поле Code_author), имена авторов (поле Name_author), названия соответствующих книг (поле Title_book), если код издательства (поле Code_Publish) находится в диапазоне от 10 до 25 и ко-личество страниц (поле Pages) в книге больше 120. 26. Вывести список издательств (поле Publish) из таблицы Publish-ing_house, в которых выпущены книги, названия которых (поле Title_book) начинаются со слова ‘Труды’ и город издания (поле City) – ‘Новосибирск’. Многотабличные запросы (выборка из двух таблиц, выборка из трех таблиц с использованием JOIN) Вывести список названий компаний-поставщиков (поле Name_company) и названия книг (поле Title_book), которые они поставили период с 01.01.2002 по 31.12.2003 (условие по полю Date_order). Вывести список авторов (поле Name_author), книги которых были выпущены в издательстве ‘Мир’ (условие по полю Publish). Вывести список поставщиков (поле Name_company), которые по-ставляют книги издательства ‘Питер’ (условие по полю Publish). Вывести список авторов (поле Name_author) и названия книг (поле Title_book), которые были поставлены поставщиком ‘ОАО Книготорг’ (ус- ловие по полю Name_company). Вычисления Вывести суммарную стоимость партии одноименных книг (исполь-зовать поля Amount и Cost) и название книги (поле Title_book) в каждой поставке. Вывести стоимость одной печатной страницы каждой книги (ис-пользовать поля Cost и Pages) и названия соответствующих книг (поле Title_book). 33. Вывести количество лет с момента рождения авторов (использо-вать поле Birthday) и имена соответствующих авторов (поле Name_author). Вычисление итоговых значений с использованием агрегатных функций Вывести общую сумму поставок книг (использовать поле Cost), выполненных ‘ЗАО Оптторг’ (условие по полю Name_company). Вывести общее количество всех поставок (использовать любое по-ле из таблицы Purchases), выполненных в период с 01.01.2003 по 01.02.2003 (условие по полю Date_order). Вывести среднюю стоимость (использовать поле Cost) и среднее количество экземпляров книг (использовать поле Amount) в одной постав-ке, где автором книги является ‘Акунин’ (условие по полю Name_author). Вывести все сведения о поставке (все поля таблицы Purchases), а также название книги (поле Title_book) с минимальной общей стоимостью (использовать поля Cost и Amount). 17 38. Вывести все сведения о поставке (все поля таблицы Purchases), а также название книги (поле Title_book) с максимальной общей стоимостью (использовать поля Cost и Amount). Изменение наименований полей Вывести название книги (поле Title_book), суммарную стоимость партии одноименных книг (использовать поля Amount и Cost), поместив в результат в поле с названием Itogo, в поставках за период с 01.01.2002 по 01.06.2002 (условие по полю Date_order). Вывести стоимость одной печатной страницы каждой книги (ис-пользовать поля Cost и Pages), поместив результат в поле с названием One_page, и названия соответствующих книг (поле Title_book). Вывести общую сумму поставок книг (использовать поле Cost) и поместить результат в поле с названием Sum_cost, выполненных ‘ОАО Луч’ (условие по полю Name_company). Использование переменных в условии Вывести список сделок (все поля из таблицы Purchases) за послед-ний месяц (условие с использованием поля Date_order). Вывести список авторов (поле Name_author), возраст которых меньше заданного пользователем (условие с использованием поля Birthday). 44. Вывести список книг (поле Title_book), которых закуплено меньше, чем указано в запросе пользователя (условие с использованием поля Amount). Использование переменных вместо названий таблиц Вывести список названий компаний-поставщиков (поле Name_company) и названия книг (поле Title_book), которые они поставили. Вывести список авторов (поле Name_author), книги которых были выпущены в издательствах ‘Мир’, ‘Питер Софт’, ‘Наука’ (условие по полю Publish). 47. Вывести список издательств (поле Name_company), книги которых были поставлены по цене 150 руб. (поле Cost). Выбор результата в курсор Вывести список названий книг (поле Title_book) и количества страниц (поле Pages) в каждой книге и поместить результат в курсор с на-званием Temp1. Вывести список названий компаний-поставщиков (поле Name_company) и поместить результат в курсор с названием Temp2. 18 Вывести список авторов (поле Name_author) и поместить результат курсор с названием Temp3. Использование функций совместно с подзапросом Вывести список книг (поле Title_book), у которых количество стра-ниц (поле Pages) больше среднего количества страниц всех книг в таблице. Вывести список авторов (поле Name_author), возраст которых меньше среднего возраста всех авторов в таблице (условие по полю Birth- day). 53. Вывести список книг (поле Title_book), у которых количество стра-ниц (поле Pages) равно минимальному количеству страниц книг, пред-ставленных в таблице. Использование квантора существования в запросах Вывести список издательств (поле Publish), книги которых были приобретены оптом (‘опт’ из поля Type_Purchase). Вывести список авторов (поле Name_author), книг которых нет в таблице Books. Вывести список книг (поле Title_book), которые были поставлены поставщиком ‘ЗАО Квантор’ (условие по полю Name_company). Оператор обработки данных Update Изменить в таблице Books содержимое поля Pages на 300, если код автора (поле Code_author) =56 и название книги (поле Title_book) =’Мемуары’. Изменить в таблице Deliveries содержимое поля Address на ‘нет сведений’, если значение поля является пустым. Увеличить в таблице Purchases цену (поле Cost) на 20 процентов, если заказы были оформлены в течение последнего месяца (условие по по- лю Date_order). Оператор обработки данных Insert Добавить в таблицу Purchases новую запись, причем так, чтобы код покупки (поле Code_purchase) был автоматически увеличен на единицу, а в тип закупки (поле Type_purchase) внести значение ‘опт’. Добавить в таблицу Books новую запись, причем вместо ключевого поля поставить код (поле Code_book), автоматически увеличенный на еди-ницу от максимального кода в таблице, вместо названия книги (поле Ti-tle_book) написать ‘Наука. Техника. Инновации’. Добавить в таблицу Publish_house новую запись, причем вместо ключевого поля поставить код (поле Code_publish), автоматически увели- 19 ченный на единицу от максимального кода в таблице, вместо названия го-рода – ‘Москва’ (поле City), вместо издательства – ‘Наука’ (поле Publish). Оператор обработки данных Delete Удалить из таблицы Purchases все записи, у которых количество книг в заказе (поле Amount) = 0. Удалить из таблицы Authors все записи, у которых нет имени авто- ра в поле Name_Author. 65. Удалить из таблицы Deliveries все записи, у которых не указан ИНН (поле INN пустое). | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||