СУБД MS SQL Server: создание БД, работа с таблицами, представлениями, использование языка Transact-SQL

МИНИСТЕРСТВО ОБРАЗОВАНИЯ РЕСПУБЛИКИ БЕЛАРУСЬ

УЧРЕЖДЕНИЕ ОБРАЗОВАНИЯ

ГОМЕЛЬСКИЙ ГОСУДАРСТВЕННЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ ИМЕНИ П. О. СУХОГО

Факультет автоматизированных и информационных систем

Кафедра «Информационные технологии»

ОТЧЕТ   ПО   ЛАБОРАТОРНОЙ   РАБОТЕ   №6

по дисциплине «Базы данных»

на тему:     «СУБД MS SQL Server: создание БД, работа с таблицами, представлениями, использование языка Transact-SQL»

Выполнила:     студентка гр. ИТ-32

Принял:            

Дата сдачи отчета:         _____________________

Дата допуска к защите: _____________________

Дата защиты:                  _____________________

Гомель 2013

Цель: изучение основ работы с СУБД MS SQL Server  с помощью программы SQL Management Studio.

Задание по работе

1) Изучить пользовательский интерфейс программы SQL Management Studio, выполнение с ее помощью типовых операций по разработке объектов БД.

2) Создать БД, аналогичную БД "Платежи студентов" (см. лаб. работы 1, 2), средствами СУБД MS SQL Server (используя SQL Management Studio).

2.1) Создать все таблицы, типы данных для таблиц подобрать самостоятельно исходя из заносимых данных; задать ключевые поля (первичные ключи) в таблицах на стороне «один» отношений «один-ко-многим».

2.2) Создать диаграмму данных (аналог схемы данных в Access), связать таблицы БД.

2.3) Заполнить БД данными. При этом заполнение осуществлять в следующем порядке: сначала вносятся данные в таблицы, стоящие на стороне отношения «один» (таблицы-справочники), потом – в таблицы, стоящие на стороне отношения «многие» (оперативные таблицы). Данные для заполнения таблиц подобрать самостоятельно. Таблица «Студенты» должна содержать не менее 10 записей, таблицы «Города», «Улицы», «Факультеты», «НазначенияОплат» - не менее 5 записей, таблица «Платежи» - не менее 20 записей (по 2 платежа на каждого студента).

3) Создать представления (аналог запросов на выборку Access), требования по обработке данных описаны в лаб. работе 2 (см. раздел 2 «Создание простых запросов» файла Laborat.doc), включая контрольные задания по своему варианту из раздела 2 файла Laborat.doc.

4) Изучить все инструкции на языке Transact-SQL, полученные при создании объектов БД.

Выполнение:

Создание базы данных:

Из контекстного меню выбрать «Создать базу данных». В появившемся диалоговом окне задать имя базы данных и ее владельца.

Создание новой таблицы:

Из контекстного меню выбрать «Создать таблицу». Далее необходимо задать имена столбцов и типы данных значений.

  

Создание представлений:

Из контекстного меню выбрать «Создать представление». Затем добавить необходимые таблицы. Мышью перетащить необходимые столбцы в нижнюю часть бланка представления.

  

Полученная диаграмма данных:

Создать простой многотабличный запрос, позволяющий редактировать данные в таблице «Платежи» с одновременным выводом данных о студенте. Запрос должен содержать поля КодСтудента, ФИО взятые из таблицы «Студенты», и поля ДатаОплаты, СуммаОплаты, ЦельОплаты, взятые из таблицы «Платежи»:

SELECT      dbo.Студенты.КодСтудента, dbo.Студенты.ФИО, dbo.Платежи.ДатаОплаты, dbo.Платежи.СуммаОплаты, dbo.Платежи.Цель

FROM          dbo.Студенты INNER JOIN

dbo.Платежи ON dbo.Студенты.КодСтудента = dbo.Платежи.КодСтудента

Отбор данных происходит из таблиц «Студенты» и «Платежи». Операция INNER JOIN объединяет записи из двух таблиц, если связующие поля «КодСтудента» таблиц «Студенты» и «Платежи» содержат одинаковые  значения,  остальные  записи  в  выборке  не  участвуют.

Создать простой многотабличный запрос для вывода списка улиц города Гомеля. Запрос должен содержать поля: Название (таблица «Города») и Название (таблица «Улицы»). Связь между таблицами установить локально (в пределах данного запроса) по полю КодГорода. В бланке запроса для поля Название (таблица «Города») в строку Условия отбора внести - Гомель.

SELECT      dbo.Города.Название, dbo.Улицы.НазваниеУлицы

FROM          dbo.Города INNER JOIN

dbo.Улицы ON dbo.Города.КодГорода = dbo.Улицы.КодГорода

WHERE      (dbo.Города.Название = N'Гомель')

После ключевого слова указан псевдоним «Expr1». При выводе таблицы он будет стоять в заголовке столбца.

Создать многотабличный запрос с именем «Сумма_1» для подсчета общих сумм оплат студентов за весь период оплат. Запрос должен содержать поля ФИО, взятые из таблицы «Студенты», и поле СуммаОплаты. При создании запроса использовать команду Вид  Групповые операции. В качестве групповой операции для поля СуммаОплаты в раскрывающемся списке указать Sum (суммирование), а для остальных полей - Группировка.

SELECT      dbo.Студенты.ФИО, SUM(dbo.Платежи.СуммаОплаты) AS Sum

FROM          dbo.Студенты INNER JOIN

dbo.Платежи ON dbo.Студенты.КодСтудента = dbo.Платежи.КодСтудента

GROUP BY dbo.Студенты.ФИО

Создается новый столбец «Sum». Группировка записей осуществляется по полям указанным после ключевых слов GROUP BY.

Создать многотабличный запрос с именем «Сумма_2» путем копирования и последующего редактирования запроса «Сумма_1». Выполнить следующие изменения: 1) добавить в бланк запроса поле Группа; 2) удалить из бланка запроса поля Фамилия, Имя, Отчество; 3) выполнить для поля Группа установку: Групповая операция - Группировка.

SELECT      SUM(dbo.Сумма1.Sum) AS Expr1, dbo.Студенты.Группа

FROM          dbo.Сумма1 CROSS JOIN

dbo.Студенты

GROUP BY dbo.Студенты.Группа

Создать многотабличный запрос с именем «Сумма_3» путем копирования и последующего редактирования запроса «Сумма_2». Выполнить следующие изменения установок для поля Группа: Групповая операция - Условие; Вывод на экран – нет (снять флажок); Условие отбора: указать номер группы из имеющихся в базе данных.

SELECT      SUM(Expr1) AS Expr1, Группа

FROM          dbo.Сумма2

WHERE      (Группа = N'11')

GROUP BY Группа

Создать многотабличный запрос для вывода следующих сведений о студентах (для факультета выводить полное название):

1)  фамилия, имя, отчество, полный адрес;

SELECT      dbo.Студенты.ФИО, dbo.Города.Название, dbo.Улицы.НазваниеУлицы

FROM          dbo.Студенты INNER JOIN

dbo.Факультеты ON dbo.Студенты.СокрНазФак = dbo.Факультеты.СокрНазвФак INNER JOIN

dbo.Улицы ON dbo.Студенты.КодУлицы = dbo.Улицы.КодУлицы INNER JOIN

dbo.Города ON dbo.Студенты.КодГорода = dbo.Города.КодГорода AND dbo.Улицы.КодГорода = dbo.Города.КодГорода

Создать запрос для нахождения следующих итоговых показателей, вычисляемых по группам записей (используются группировка данных в запросах и итоговые функции Sum, Avg, Min, Max, Count, First, Last):

1)  среднее значение суммы оплаты при одном платеже для каждого студента;

SELECT      dbo.Студенты.ФИО, SUM(dbo.Платежи.СуммаОплаты) AS Sum, COUNT(DISTINCT dbo.Платежи.СуммаОплаты) AS Count,

AVG(dbo.Платежи.СуммаОплаты) AS Average

FROM          dbo.Студенты INNER JOIN

dbo.Платежи ON dbo.Студенты.КодСтудента = dbo.Платежи.КодСтудента

GROUP BY dbo.Студенты.ФИО

Вывод:изучили основы работы с СУБД MS SQL Server 2012  с помощью программы SQL Management Studio.