Лабораторные работы БД. Отчет по лабораторной работе. 2 Используя ms access перенести полученную модель в бд, используя таблицы и схему данных
 Скачать 5.14 Mb.
|
|
Тема: Cоздание баз данных и использование операторов манипулирова- ния данными в microsoft sql server. (Можно скачать с http://www.mi- crosoft.com/ru-ru/download/details.aspx?id=43351 ) Цель: С помощью операторов языка Transact SQL научиться создавать базы данных и совокупность связанных таблиц, принадлежащих указанной базе данных,использовать операторы манипулирования данными Select, In- sert, Update, Delete. Содержание работы: 1. Познакомиться с набором утилит, входящих в состав MS SQL Server. 2. Познакомиться с работой утилиты SQL Server Management Studio. ( http://www.microsoft.com/ru-RU/download/details.aspx?id=22985 ) 3. Создать с помощью приведенных операторов пример базы данных «Книжное дело». 4. По выданным вариантам создать персональную базу данных с набо- ром связанных таблиц. 5. Выполнить запросы к БД «Книжное дело» по выданным вариантам. Пояснения к выполнению работы В качестве примера базы данных, которая будет создана программно с помощью операторов языка Transact SQL, выберем БД «Книжное дело» (рис. 1). Структура таблиц данной БД представлена в табл. 1-5. Рис. 1. Фрагмент базы данных «Книжное дело» Таблица 1 Покупки (название таблицы Purchases) Название поля Тип поля Описание поля Code_book Int Код закупаемой книги Date_order DateTime Дата заказа книги Code_delivery Int Код поставщика Type_purchase Bit Тип закупки (опт/ розница) Cost Money Стоимость единицы товара Amount Int Количество экземпляров Code_purchase Int Код покупки Таблица 2 Справочник книг (название таблицы Books) Название поля Тип поля Описание поля Code_book Int Код книги Title_book Char Название книги Code_author Int Код автора Pages Int Количество страниц Code_publish Int Код издательства Таблица 3 Справочник авторов (название таблицы Authors) Название поля Тип поля Описание поля Code_author Int Код автора Name_author Char Фамилия, имя, отчество автора Birthday DateTime Дата рождения Таблица 4 Справочник поставщиков (название таблицы Deliveries) Название поля Тип поля Описание поля Code_delivery Int Код поставщика Name_delivery Char Фамилия, и., о. ответственного лица Name_company Char Название компании-поставщика Address Char Юридический адрес Phone Numeric Телефон контактный INN Char ИНН Таблица 5 Справочник издательств (название таблицы Publishing_house) Название поля Тип поля Описание поля Code_publish Int Код издательства Publish Char Издательство City Char Город Запустить SQL Server Management Studio, проверить включение сер- вера. Для написания программного кода в SQL Server Management Studio нужно нажать кнопку «Создать запрос» («New query») на панели инстру- ментов «Стандартная» («Standart»). Создать новую базу данных с названием DB_Books с помощью ко- манды: CREATE DATABASE DB_BOOKS Для выполнения команды нажать F5. Открыть утилиту SQL Server Management Studio. Проверить наличие БД DB_Books, если ее не видно в разделе DataBases, то нажать F5 для об- новления. Cоздать в ней перечисленные таблицы с помощью следующих команд (для создания новой страницы для кода в SQL Server Management Studio нажать кнопку «Создать запрос»): use DB_BOOKS CREATE TABLE Authors(Code_author INT PRIMARY KEY, name_author CHAR(30), Birthday DATETIME) CREATE TABLE Publishing_house(Code_publish INT PRIMARY KEY, Publish CHAR(30), City CHAR(20)) CREATE TABLE Books(Code_book INT PRIMARY KEY, Title_book CHAR(40), Code_author INT FOREIGN KEY REFERENCES Authors(Code_author), Pages INT, Code_publish INT FOREIGN KEY REFERENCES Publishing_house(Code_publish)) CREATE TABLE Deliveries(Code_delivery INT PRIMARY KEY, Name_delivery CHAR(30), Name_company CHAR(20), Address VARCHAR(100), Phone BIGINT, INN CHAR(13)) CREATE TABLE Purchases(Code_purchase INT PRIMARY KEY, Code_book INT FOREIGN KEY REFERENCES Books(Code_book), Date_order SMALLDATETIME, Code_delivery INT FOREIGN KEY REFERENCES Deliveries(Code_delivery), Type_purchase BIT, Cost FLOAT, Amount INT) Запустить команду клавишей F5. В утилите SQL Server Management Studio проверить наличие БД DB_Books и таблиц в ней. В разделе диаграмм создать новую диаграмму, в которую добавить из списка пять наших таблиц, проверить связи между таблицами. Рис. 1.3. Результат создания диаграммы Таблица 6. Соответствие типов данных Microsoft Access и Microsoft SQL № Тип данных Microsoft Access Тип данных Microsoft SQL Описание типа данных Microsoft SQL 1 Текстовый nvarchar Тип данных для хранения текста до 4000 симво- лов 2 Поле МЕМО ntext Тип данных для хранения символов в кодировке Unicode до 1 073 741 823 символов 3 Числовой int Численные значения (целые) в диапазоне от -2 147 483 648 до +2 147 483 647 4 Дата/время smalldatetime Дата и время от 1 января 1900 г. до 6 июня 2079 года с точностью до одной минуты 5 Денежный money Денежный тип данных, значения которого лежат в диапазоне от -922 337 203 685 477.5808 до +922 337 203 685 477.5807, с точностью до од- ной десятитысячной 6 Счетчик int См. пункт 3 7 Логический bit Переменная, способная принимать только два значения - 0 или 1 8 Поле объекта OLE image Переменная для хранения массива байтов от 0 до 2 147 483 647 байт 9 Гиперссылка ntext См. пункт 2 10 Мастер подстановок nvarchar См. пункт 1 Варианты заданий 1. Создать новую базу данных по своей предметной области, создать в ней перечисленные таблицы. Для этого в утилите SQL Server Management Studio создать отдельные скрипты по каждому запросу В сами скрипты копировать текст задания в виде комментария. Можно сохранять все выполненные запросы в одном файле. 2. В созданной базе данных DB_Books создать отдельные скрипты по каж- дому запросу. В сами скрипты копировать текст задания в виде комментария. Можно сохранять все выполненные запросы в одном файле. Для проверки работы операторов SELECT предварительно создайте скрипт, который с помощью операторов INSERT заполнит все таблицы БД DB_Books несколькими записями. Список вариантов заданий Вариант Список номеров упражнений 1 1 6 11 16 21 26 31 36 41 46 51 56 61 2 2 7 12 17 22 27 32 37 42 47 52 57 62 3 3 8 13 18 23 28 33 38 43 48 53 58 63 4 4 9 14 19 24 29 34 39 44 49 54 59 64 5 5 10 15 20 25 30 35 40 45 50 55 60 65 6 6 11 16 21 26 31 36 41 46 51 56 61 1 7 7 12 17 22 27 32 37 42 47 52 57 62 2 8 8 13 18 23 28 33 38 43 48 53 58 63 3 9 9 14 19 24 29 34 39 44 49 54 59 64 4 10 10 15 20 25 30 35 40 45 50 55 60 65 5 11 2 6 12 16 22 26 32 36 42 46 52 56 62 12 1 5 11 15 21 25 31 35 41 45 51 55 61 13 3 7 13 17 23 27 33 37 43 47 53 57 63 Сортировка 1. Выбрать все сведения о книгах из таблицы Books и отсортировать результат по коду книги (поле Code_book). 2. Выбрать из таблицы Books коды книг, названия и количество стра- ниц (поля Code_book, Title_book и Pages), отсортировать результат по названиям книг (поле Title_book по возрастанию) и по полю Pages (по убыванию). 3. Выбрать из таблицы Deliveries список поставщиков (поля Name_de- livery, Phone и INN), отсортировать результат по полю INN (по убыванию). Изменение порядка следования полей 4. Выбрать все поля из таблицы Deliveries таким образом, чтобы в ре- зультате порядок столбцов был следующим: Name_delivery, INN, Phone, Address, Code_delivery. 5. Выбрать все поля из таблицы Publishing_house таким образом, чтобы в результате порядок столбцов был следующим: Publish, City, Code_publish. Выбор некоторых полей из двух таблиц 6. Выбрать из таблицы Books названия книг и количество страниц (поля Title_book и Pages), а из таблицы Authors выбрать имя соответствую- щего автора книги (поле Name_ author). 7. Выбрать из таблицы Books названия книг и количество страниц (поля Title_book и Pages), а из таблицы Deliveries выбрать имя соответству- ющего поставщика книги (поле Name_delivery). 8. Выбрать из таблицы Books названия книг и количество страниц (поля Title_book и Pages), а из таблицы Publishing_house выбрать название соответствующего издательства и места издания (поля Publish и City). Условие неточного совпадения 9. Выбрать из справочника поставщиков (таблица Deliveries) названия компаний, телефоны и ИНН (поля Name_company, Phone и INN), у которых название компании (поле Name_company) начинается с ‘ОАО’. 10. Выбрать из таблицы Books названия книг и количество страниц (поля Title_book и Pages), а из таблицы Authors выбрать имя соответствую- щего автора книг (поле Name_ author), у которых название книги начина- ется со слова ‘Мемуары’. 11. Выбрать из таблицы Authors фамилии, имена, отчества авторов (поле Name_ author), значения которых начинаются с ‘Иванов’. Точное несовпадение значений одного из полей 12. Вывести список названий издательств (поле Publish) из таблицы Publishing_house, которые не находятся в городе ‘Москва’ (условие по полю City). 13. Вывести список названий книг (поле Title_book) из таблицы Books, которые выпущены любыми издательствами, кроме издательства ‘Питер- Софт’ (поле Publish из таблицы Publishing_house). Выбор записей по диапазону значений (Between) 14. Вывести фамилии, имена, отчества авторов (поле Name_author) из таблицы Authors, у которых дата рождения (поле Birthday) находится в диа- пазоне 01.01.1840 – 01.06.1860. 15. Вывести список названий книг (поле Title_book из таблицы Books) и количество экземпляров (поле Amount из таблицы Purchases), которые были закуплены в период с 12.03.2003 по 15.06.2003 (условие по полю Date_order из таблицы Purchases). 16. Вывести список названий книг (поле Title_book) и количество стра- ниц (поле Pages) из таблицы Books, у которых объем в страницах укладыва- ется в диапазон 200 – 300 (условие по полю Pages). 17. Вывести список фамилий, имен, отчеств авторов (поле Name_au- thor) из таблицы Authors, у которых фамилия начинается на одну из букв диапазона ‘В’ – ‘Г’ (условие по полю Name_author). Выбор записей по диапазону значений (In) 18. Вывести список названий книг (поле Title_book из таблицы Books) и количество (поле Amount из таблицы Purchases), которые были постав- лены поставщиками с кодами 3, 7, 9, 11 (условие по полю Code_delivery из таблицы Purchases). 19. Вывести список названий книг (поле Title_book) из таблицы Books, которые выпущены следующими издательствами: ‘Питер-Софт’, ‘Альфа’, ‘Наука’ (условие по полю Publish из таблицы Publishing_house). 20. Вывести список названий книг (поле Title_book) из таблицы Books, которые написаны следующими авторами: ‘Толстой Л.Н.’, ‘Достоевский Ф.М.’, ‘Пушкин А.С.’ (условие по полю Name_author из таблицы Authors). Выбор записей с использованием Like 21. Вывести список авторов (поле Name_author) из таблицы Authors, которые начинаются на букву ‘К’. 22. Вывести названия издательств (поле Publish) из таблицы Publishing_house, которые содержат в названии сочетание ‘софт’. 23. Выбрать названия компаний (поле Name_company) из таблицы Deliveries, у которых значение оканчивается на ‘ский’. Выбор записей по нескольким условиям 24. Выбрать коды поставщиков (поле Code_delivery), даты заказов (поле Date_order) и названия книг (поле Title_book), если количество книг (поле Amount) в заказе больше 100 или цена (поле Cost) за книгу находится в диапазоне от 200 до 500. 25. Выбрать коды авторов (поле Code_author), имена авторов (поле Name_author), названия соответствующих книг (поле Title_book), если код издательства (поле Code_Publish) находится в диапазоне от 10 до 25 и ко- личество страниц (поле Pages) в книге больше 120. 26. Вывести список издательств (поле Publish) из таблицы Publishing_house, в которых выпущены книги, названия которых (поле Title_book) начинаются со слова ‘Труды’ и город издания (поле City) – ‘Новосибирск’. Многотабличные запросы (выборка из двух таблиц, выборка из трех таб- лиц с использованием JOIN) 27. Вывести список названий компаний-поставщиков (поле Name_com- pany) и названия книг (поле Title_book), которые они поставили в период с 01.01.2002 по 31.12.2003 (условие по полю Date_order). 28. Вывести список авторов (поле Name_author), книги которых были выпущены в издательстве ‘Мир’ (условие по полю Publish). 29. Вывести список поставщиков (поле Name_company), которые по- ставляют книги издательства ‘Питер’ (условие по полю Publish). 30. Вывести список авторов (поле Name_author) и названия книг (поле Title_book), которые были поставлены поставщиком ‘ОАО Книготорг’ (условие по полю Name_company). Вычисления 31. Вывести суммарную стоимость партии одноименных книг (исполь- зовать поля Amount и Cost) и название книги (поле Title_book) в каждой по- ставке. 32. Вывести стоимость одной печатной страницы каждой книги (ис- пользовать поля Cost и Pages) и названия соответствующих книг (поле Ti- tle_book). 33. Вывести количество лет с момента рождения авторов (использо- вать поле Birthday) и имена соответствующих авторов (поле Name_author). Вычисление итоговых значений с использованием агрегатных функций 34. Вывести общую сумму поставок книг (использовать поле Cost), выполненных ‘ЗАО Оптторг’ (условие по полю Name_company). 35. Вывести общее количество всех поставок (использовать любое поле из таблицы Purchases), выполненных в период с 01.01.2003 по 01.02.2003 (условие по полю Date_order). 36. Вывести среднюю стоимость (использовать поле Cost) и среднее ко- личество экземпляров книг (использовать поле Amount) в одной поставке, где автором книги является ‘Акунин’ (условие по полю Name_author). 37. Вывести все сведения о поставке (все поля таблицы Purchases), а также название книги (поле Title_book) с минимальной общей стоимостью (использовать поля Cost и Amount). 38. Вывести все сведения о поставке (все поля таблицы Purchases), а также название книги (поле Title_book) с максимальной общей стоимостью (использовать поля Cost и Amount). Изменение наименований полей 39. Вывести название книги (поле Title_book), суммарную стоимость партии одноименных книг (использовать поля Amount и Cost), поместив в результат в поле с названием Itogo, в поставках за период с 01.01.2002 по 01.06.2002 (условие по полю Date_order). 40. Вывести стоимость одной печатной страницы каждой книги (ис- пользовать поля Cost и Pages), поместив результат в поле с названием One_page, и названия соответствующих книг (поле Title_book). 41. Вывести общую сумму поставок книг (использовать поле Cost) и поместить результат в поле с названием Sum_cost, выполненных ‘ОАО Луч’ (условие по полю Name_company). Использование переменных в условии 42. Вывести список сделок (все поля из таблицы Purchases) за послед- ний месяц (условие с использованием поля Date_order). 43. Вывести список авторов (поле Name_author), возраст которых меньше заданного пользователем (условие с использованием поля Birth- day). 44. Вывести список книг (поле Title_book), которых закуплено меньше, чем указано в запросе пользователя (условие с использованием поля Amount). Использование переменных вместо названий таблиц 45. Вывести список названий компаний-поставщиков (поле Name_com- pany) и названия книг (поле Title_book), которые они поставили. 46. Вывести список авторов (поле Name_author), книги которых были выпущены в издательствах ‘Мир’, ‘Питер Софт’, ‘Наука’ (условие по полю Publish). 47. Вывести список издательств (поле Name_company), книги которых были поставлены по цене 150 руб. (поле Cost). Выбор результата 48. Вывести список названий книг (поле Title_book) и количества стра- ниц (поле Pages) в каждой книге. 49. Вывести список названий компаний-поставщиков (поле Name_com- pany). 50. Вывести список авторов (поле Name_author). Использование функций совместно с подзапросом 51. Вывести список книг (поле Title_book), у которых количество стра- ниц (поле Pages) больше среднего количества страниц всех книг в таблице. 52. Вывести список авторов (поле Name_author), возраст которых меньше среднего возраста всех авторов в таблице (условие по полю Birth- day). 53. Вывести список книг (поле Title_book), у которых количество стра- ниц (поле Pages) равно минимальному количеству страниц книг, представ- ленных в таблице. Использование квантора существования в запросах 54. Вывести список издательств (поле Publish), книги которых были приобретены оптом (‘опт’ из поля Type_Purchase). 55. Вывести список авторов (поле Name_author), книг которых нет в таблице Books. 56. Вывести список книг (поле Title_book), которые были поставлены поставщиком ‘ЗАО Квантор’ (условие по полю Name_company). Оператор обработки данных Update 57. Изменить в таблице Books содержимое поля Pages на 300, если код автора (поле Code_author) =56 и название книги (поле Title_book) =’Мемуары’. 58. Изменить в таблице Deliveries содержимое поля Address на ‘нет све- дений’, если значение поля является пустым. 59. Увеличить в таблице Purchases цену (поле Cost) на 20 процентов, если заказы были оформлены в течение последнего месяца (условие по полю Date_order). Оператор обработки данных Insert 60. Добавить в таблицу Purchases новую запись, причем так, чтобы код покупки (поле Code_purchase) был автоматически увеличен на единицу, а в тип закупки (поле Type_purchase) внести значение ‘опт’. 61. Добавить в таблицу Books новую запись, причем вместо ключевого поля поставить код (поле Code_book), автоматически увеличенный на еди- ницу от максимального кода в таблице, вместо названия книги (поле Ti- tle_book) написать ‘Наука. Техника. Инновации’. 62. Добавить в таблицу Publish_house новую запись, причем вместо ключевого поля поставить код (поле Code_publish), автоматически увели- ченный на единицу от максимального кода в таблице, вместо названия го- рода – ‘Москва’ (поле City), вместо издательства – ‘Наука’ (поле Publish). Оператор обработки данных Delete 63. Удалить из таблицы Purchases все записи, у которых количество книг в заказе (поле Amount) = 0. 64. Удалить из таблицы Authors все записи, у которых нет имени автора в поле Name_Author. 65. Удалить из таблицы Deliveries все записи, у которых не указан ИНН (поле INN пустое). |