Методические указания для
Выполнения практических работ
По дисциплине «Разработка и эксплуатация удалённых баз данных»
Для специальности
230105 «Программное обеспечение вычислительной техники и автоматизированных систем»
Содержание
| Введение | |
| Практическая работа № 1 «Создание таблиц базы данных в среде MS SQL Server 2005» | |
| Практическая работа № 2 «Создание таблиц визуальными средствами SQL Server» | |
| Практическая работа № 3 «Структурированный Язык Запросов SQL. Команда SELECT» | |
| Практическая работа № 4 «Управляющие конструкции языка запросов SQL» | |
| Практическая работа № 5 «Групповые операции. Агрегатные функции» | |
| Практическая работа № 6 «Создание многотабличного запроса» | |
| Практическая работа № 7 «Использование подзапросов» | |
| Практическая работа № 8 «Реализация запросов для нахождения минимального и максимального значений» | |
| Практическая работа № 9 «Добавление, изменение, удаление записей в таблицах» | |
| Практическая работа № 10 «Деловая игра по теме ‘Создание запросов на языке SQL’» | |
| Практическая работа № 11 «Создание триггеров в ИС «Учебный процесс» для поддержания целостности данных» | |
| Практическая работа № 12 «Создание триггеров в ИС «Учебный процесс» для поддержания целостности данных» | |
| Практическая работа № 13 «Хранимые процедуры в SQL Server» | |
| Практическая работа № 14 «Создание запросов с использованием внешнего соединения» | |
| Практическая работа № 15 «Транзакции. Уровни изоляций транзакций. Резервирование данных» | |
| Практическая работа № 16 «Обеспечение безопасности пользователей в SQL Server» | |
| Практическая работа № 17 «Создание простого клиента при помощи MS Access» | |
| Приложение А | |
| Приложение Б | |
| Литература |
Введение.
В данном методическом пособии представлены указания для выполнения практических работ по дисциплине «Разработка и эксплуатация удалённых баз данных» для специальности 230105 «Программное обеспечение вычислительной техники и автоматизированных систем».
Цели и задачи предлагаемого пособия:
· получение практических навыков реализации базы данных в среде MS SQL Server 2005;
· формирование навыков построения простых и сложных запросов с помощью языка SQL Transact
· управление транзакциями в базе данных
· получение практических навыков построения процедур и триггеров.
· обеспечение безопасности работы пользователей в архитектуре «клиент - сервер»
· получение практических навыков разработки «клиентской» части приложения в MS Access.
·
Рассматриваемый курс содержит: 17 практических занятия; приложений, списка литературы.
Каждое занятие состоит из следующих структурных частей:
· изучение теоретического материала;
· выполнение заданий.
· выполнение контрольных заданий
Практическая работа №1
«Создание таблиц базы данных в среде MS SQL Server 2005»
Цель: «Сформировать навыки проектирования таблиц в среде MS SQL Server 2005, построить схему данных»
Ход Работы
1. Общий вид оператора CREATE TABLE
Перед созданием таблиц БД необходимо продумать определение всех столбцов таблицы и характеристик каждого столбца (таких, как тип, длина, обязательность для ввода, ограничения, накладываемые на значения и пр.), индексов, ограничений целостности по отношению к другим таблицам.
БД в которую будет добавлена создаваемая таблица, должна быть открыта, т.е. с ней должно быть установлено активное соединение. Создание таблицы БД осуществляется оператором
CREATE TABLE [databasename].Имя Таблицы (<опр_столбца> [, <опр_столбца>| <ограничение>)
<опр_столбца> = _ опр_столбца{тип_данных }
[DEFAULT { литерал NULL |USER}]
[NOT NULL] [<огранич_столбца>]
[COLLATE collation]
DEFАULТ определяет значение, которое по умолчанию заносится в столбец при создании записи таблицы; это значение будет присутствовать в соответствующем столбце данной записи до тех пор, пока пользователь не изменит его каким-либо образом; значения по умолчанию.
<огранич_столбца>- ограничения, накладываемые на значения столбца.
COLLATE collation - определяет порядок сортировки символов
При проектировании базы данных создаётся концептуальная модель, которая преобразуется в реляционную модель. Объектами реляционной модели являются таблицы. Рассмотрим создание базы данных учебного процесса.
Таблицы создаются в среде SQL Server Management Studio. Запустите виртуальную машину. Для запуска программы выберите Пуск | Все программы | Microsoft SQL Server 2005 | SQL Server Management Studio. Появится окно Connect to Server. (Подключение к серверу). Нажмите кнопку Cоnnect. После этого появится окно:
Рис 1. Окно SQL Server Management Studio
Слева находится окно обозревателя объектов Obejct Explorer. В нём отображается дерево объектов SQL сервера. Чтобы создать новую базу данных, выделите мышью Databases и из контекстного меню выберите New Database, появится окно
Рис 2. Окно для создания новой базы.
В строке Database name укажите имя вашей базы, нажмите на OK. В обозревателе объектов появится новая база данных.
Рис. 3. Отображение новой базы данных
Для того, чтобы ввести код по созданию таблиц нужно на панели инструментов нажать кнопку New Query. В редакторе запросов введите операторы по созданию таблиц. После каждой таблице нажмите кнопку 
. Если ошибок нет, то среди списка таблиц Tables появится вновь созданная таблица. Если имеются ошибки, то они будут перечислены в нижней части окна.
Схема данных приведена в приложении А. Первичный ключ задаётся: primary key (ng).
Внешний ключ для связи двух таблиц по ключевому полю ng:
constraint FK_NG foreign key (ng) references gruppa (ng)
Задание 1. Создайте таблицы с использованием операторов Transact SQL
Структуры таблиц приведённые ниже
| Use uch_proz Create table gruppa ( ng varchar(3) not null, kol int, pball float, primary key (ng)) | Use uch_proz Create table student ( ng varchar(3) not null, ns varchar(3) not null, fio varchar(20) not null, pball float, primary key (ng, ns) constraint FK_NG foreign key (ng) references gruppa (ng)) |
| Use uch_proz Create table kafedra ( kkaf varchar(3) not null, namekaf varchar(20) not null, primary key ( kkaf )) | Use uch_proz Create table predmet ( kp varchar(3) not null, np varchar(30) not null, chas int, lek int, pr int, ch int, primary key ( kp )) |
| Use uch_proz Create table prepodavatel ( tabn varchar(3) not null, fio varchar(30) not null, kkaf varchar(3) not null, primary key ( tabn ), constraint FK_prepk foreign key (kkaf) references kafedra (kkaf) ) | Use uch_proz Create table isuchenie ( ng varchar(3) not null kp varchar(3) not null tabn varchar(3) not null, vidz varchar(3) not null, chas int, primary key (ng, tabn, kp, vidz)) |
| Use uch_proz Create table uspevaemost ( ng varchar(3) not null, ns varchar(3) not null, kp varchar(3) not null tabn varchar(3) not null, vidz varchar(3) not null, ozenka int, primary key (ng,ns, tabn, kp, vidz)) |
Задание 2. Построение диаграммы базы данных.
Для установления отношений между таблицами необходимо построить диаграмму.
a) Щёлкните правой кнопкой мыши по Database Diagramm.
b) В появившемся контекстном меню выберите New Database Diagramm. Появится перечень таблиц, которые можно включить в схему. Выберите таблицы и нажмите на Close.
c) Установите отношения между таблицами, переместив ключевые поля с таблиц мощности 1 на поля «многие»
Контрольные задания.
1. Заполните данными построенные таблицы базы «Учебный процесс»
2. Постройте концептуальную модель по выбранной вами предметной области.
3. Преобразуйте концептуальную модель в реляционную.
Практическая работа №2 «Создание таблиц визуальными средствами SQL Server»Цель: «сформировать навыки построения таблиц и диаграммы данных визуальными средствами Sql Serever»
Ход Работы
1. Создание таблицы.
Для создания таблицы в Sql Server Management Studio необходимо выполнить следующие действия:
В окне обозревателя Object Explorer откройте объект Databases, а затем узел базы данных.
Щёлкните правой кнопкой мыши по объекту Tables, в контекстном меню выберите команду New Tables.
Откроется окно конструктора таблиц
Рис 4 Конструктор таблиц
Окно предназначено для ввода сведений о полях таблицы. В конструкторе три колонки: имя поля Column Name, тип данных Data Type и разрешение не вводить значение в поле при добавлении новой записи в таблицу Allow Nulls.
Задание 1.
Спроектируйте концептуальную модель вашей базы данных. Преобразуйте её в реляционную модель. Создайте в среде SQL Server Management Studio вашу базу данных.
Задание 2.
Создайте структуры ваших таблиц в новой базе данных.
- Щёлкните правой кнопкой мыши по объекту Tables, в контекстном меню выберите команду New Tables.
- В колонке Column Name введите имена полей вашей таблицы.
- В колонке Data Type укажите тип каждого поля таблицы.
- Ключевые поля выделите мышью и на панели инструментов нажмите изображение ‘ключ’
- Выберите существующее поле таблицы и в окне Column Properties появятся его свойства.
- Для сохранения таблицы нажмите на дискету. Введите имя таблицы. Закройте структуру созданной таблицы.
Добавить поле в таблицу можно только в окне Table, а корректировать его название и тип данных можно как в этом окне, так и в окне Column Properties. При выборе в качестве типа данных Decimal в окне Column Properties появляются дополнительные строки: точность Precesion и степень Scale, которые содержат максимальное количество десятичных знаков и максимальное количество знаков после десятичной точки.
2. Просмотр информации о таблице
Sql Server Management Studio даёт возможность пользователю получить информацию о таблице. Для этого
1) В окне обозревателя выберите нужную базу данных.
2) Откройте список её объектов, щёлкнув по значку плюс, расположенному слева от названия базы.
3) Откройте узел таблиц Tables
4) Щёлкните правой кнопкой мыши по названию таблицы, информацию о которой нужно получить.
5) В появившемся контекстном меню выберите свойства Properties.
6) Откроется окно «Таблица свойств»
В появившемся окне три вкладки: общие ‘general’, разрешения ‘Permissions’ и расширенные ‘extended Properties’. На этих вкладках содержится:
· Размер таблицы на жёстком диске (Data Spase)
· Размер области, которую занимают индексы (Index Spase)
· Количество строк в таблице (Row Count)
· Дата создания таблицы (Data Created)
· Дата последней модификации (Last Update)
Задание 3
Выберите любую таблицу вашей базы данных и просмотрите её свойства. Опишите свойства таблице в отчёте.
3. Копирование, переименование и удаление таблиц
Для создания копии таблицы лучше всего использовать Transact SQL. На панели нажмите кнопку New Query.
Задание 4.
Выполним копирование таблицы kafedra. Копию таблицы назовём kafedra_copy
use test
select * into kafedra_copy from kafedra
Для удаления или переименования таблицы необходимо выполнить действия:
- Выберите нужную таблицу базы данных из списка Tables. Из контекстного меню выберите команду View Dependencies (Просмотреть зависимости). При удалении таблицы или её переименовании указанные в этом окне зависимости между объектами будут разорваны. Внимательно изучите их.
- Для удаления таблицы щёлкните правой кнопкой мыши по её имени и выберите в контекстном меню команду Delete.
- Для переименования таблицы из контекстного меню выберите Rename.
Задание 5.
Переименуйте таблицу kafedra_copy в таблицу copy. Удалите эту таблицу.
4. Построение диаграммы базы данных.
- Выберите в окне обозревателя Object Explorer базу данных, для которой нужно построить диаграмму данных.
- В базе данных выберите паку Database Diagrams. Из контекстного меню выберите пункт New Database Diagrams
- Появится первое окно мастера. Оно предназначено для выбора таблиц, которые требуется включить в диаграмму.
- После выбора таблиц щелкните на кнопке Close. Запустится процесс построения диаграмм.
- Для установления отношений между таблицами переместите ключевые поля с таблицы мощности «один» на таблицу со стороны «многие». Типы и размер полей связи должны совпадать.
- После установления связей между таблицами диаграмму нужно сохранить.
5. Ввод данных в таблицу, просмотр данных в таблице.
Для того, чтобы ввести данные в таблицу, необходимо выбрать таблицу базы данных из списка Tables, из контекстного меню этой таблицы выбрать Open Table. Откроется окно таблицы, в нижней части которого размещены кнопки для перехода по записям.
Контрольные задания.
1. Создайте структуры таблиц вашей базы данных.
2. Постройте диаграмму данных.
3. Заполните данными построенные таблицы.
4. Просмотрите свойства нескольких таблиц. Опишите свойства таблиц в отчёте.
5. Создайте копию одной из таблиц.
6. Переименуйте скопированную таблицу, удалите её.
Практическая работа №3
«Структурированный Язык Запросов SQL. Команда SELECT»Цель: «Изучить структуру оператора sql, выработать навыки построения запросов на выборку»
Ход Работы
SQL символизирует собой Структурированный Язык Запросов. Запросы - вероятно наиболее часто используемый аспект SQL. Запрос - команда которую вы даете вашей программе базы данных, и которая сообщает ей чтобы она вывела определенную информацию из таблиц в память. Эта информация обычно посылается непосредственно на экран компьютера.
1. Формат команды SELECT
Общий вид команды: SELECT * | { [ DISTINCT | ALL] < value expression >.,..} FROM { < table name > [ < alias > ] }.,.. [ WHERE ] [ GROUP BY { | }.,..] [ HAVING ] [ ORDERBY { | }.,..] В самой простой форме, команда SELECT просто инструктирует базу данных чтобы извлечь информацию из таблицы. Например, вы могли бы вывести таблицу ‘Студент’ напечатав следующее: SELECT ns,fio FROM studentЭта команда просто выводит все данные из таблицы. SELECT Ключевое слово которое сообщает базе данных что эта команда - запрос. Все запросы начинаются этим словом ns, fio Это - список столбцов из таблицы которые выбираются запросом. Любые столбцы не перечисленные здесь не будут включены в вывод команды. Это, конечно, не значит что они будут удалены или их информация будет стерта из таблиц, потому что запрос не воздействует на информацию в таблицах; он только показывает данные/ FROM - ключевое слово, подобно SELECT, которое должно быть представлено в каждом запросе. Оно сопровожда- ется пробелом и затем именем таблицы используемой в качестве источника информации. В данном случае - это таблица studentЕсли вы хотите видеть каждый столбец таблицы, имеется необязательное сокращение которое вы можете использовать. Звездочка (*) может применяться для вывода полного списка столбцов следующим образом: SELECT * FROM student; Это приведет к тому же результату что и наша предыдущая команда. DISTINCT (ОТЛИЧИЕ) - аргумент который обеспечивает Вас способом устранять двойные значения из вашего предложения SELECT. Предположим что вы хотите знать какие студенты получали оценки. SELECT DISTINCT nsFROM uspevaemost Другими словами, DISTINCT следит за тем, какие значения были ранее, так что бы они не были продублированы в списке. Это - полезный способ избежать избыточности данных. DISTINCT опускает строки где все выбранные поля идентичны. Строки в которых некоторые значения одинаковы а некоторые различны - будут сохранены. WHERE - предложение команды SELECT, которое позволяет вам устанавливать предикаты, условие которых может быть или верным или неверным для любой строки таблицы. Команда извлекает только те строки из таблицы для которой такое утверждение верно. Например, предположим вы хотите видеть номера групп с количеством студентов более 20 человек.SELECT ngFROM gruppaWHERE kol > 20 Когда предложение WHERE представлено, программа базы данных просматривает всю таблицу по одной строке и исследует каждую строку чтобы определить верно ли утверждение.2. Реляционные операторы
Реляционный оператор - математический символ который указывает на определенный тип сравнения между двумя значениями. Реляционные операторы которыми располагает SQL: = Равный к > Больше чем < Меньше чем >= Больше чем или равно <= Меньше чем или равно <> Не равно Основные Булевы операторы также распознаются в SQL.. Стандартными операторами Буля распознаваемыми в SQL являются: AND, OR, и NOT Задание 1. Вывести наименования предметов, с количеством часов равным 40 или 50. SELECT npFROM predmetWHERE chas = 40 or chas = 50 Задание 2. Вывести наименования предметов, у которых количество часов не равно сумме лекционных и практических. SELECT npFROM predmetWHERE chas<> lek + pr 3 Использование специальных операторов в условиях. SQL использует специальные операторы IN, BETWEEN, LIKE, и IS NULL. Мы рассмотрим как их использовать и как реляционные операторы позволяют создавать более сложные и мощные предикаты. Обсуждение оператора IS NULL будет включать отсутствие данных и значение NULL, которое указывает на то: что данные отсутствуют. Вы также узнаете о разновидностях использования оператора NOT применяющегося с этими операторами.Оператор IN
Оператор IN определяет набор значений в которое данное значение может или не может быть включено. Задание 3. Вывести номера студентов и номера предметов, по которым получали оценки 3 или 4 SELECT ns, npFROM uspevaemostWHERE ozenka in (3, 4)Оператор BETWEEN
Оператор BETWEEN похож на оператор IN. В отличии от определения по номерам из набора, как это делает IN, BETWEEN определяет диапазон, значения которого должны уменьшаться что делает предикат верным. Вы должны ввести ключевое слово BETWEEN с начальным значением, ключевое AND и конечное значение. В отличие от IN, BETWEEN чувствителен к по-рядку, и первое значение в предложении должно быть первым по алфавитному или числовому порядку. Задание 4. Выведите из таблицы predmet все наименования предметов с количеством часов, принадлежащих интервалу 100 - 140: SELECT *FROM predmetWHERE chas BETWEEN 100 AND 140; Контрольные задания. 1.Из таблицы Успеваемость выведите код предмета, табельный номер преподавателя, номер студента, который получал двойку.2.Выведите на экран количество студентов в ‘101’ группе3.Создайте запрос, который выводит на экран код кафедры ‘математика’4.Из таблицы Преподаватель выведите на экран фамилии и табельные номера преподавателей с кафедры ‘02’5.Из таблицы Изучение выведите на экран номер группы и табельный номер преподавателя, который ведёт предмет с количеством часов (chas) более 50.6.По разработанной базе данных сформулируйте и выполните запросы с использованием операторов: AND, OR, IN, BETWEEN Практическая работа №4 «Управляющие конструкции языка запросов SQL »Цель: «Сформировать навыки и умения реализации запроса с использованием управляющих конструкций»
Ход Работы
1.Операторные скобки Как им все алгоритмические языки Transact-SQL содержит в своём составе операторные скобки. Синтаксис конструкции имеет следующий видBEGIN <команда 1> <команда 2> END Задание 1. Вывести фамилии преподавателей с кафедры ‘02’ и список студентов из группы ‘101’
begin
use uch_proc
select fio as фио_преподавателей from prepodavatel where kkaf = '02'
select fio as фио _студентов from student where ng = '101'
end 2 Операторы ветвления Для организации ветвления используется конструкция IF…ELSE. Синтаксис конструкции: IF <условие> <команда 1>ELSE <команда 2>Если в какой-либо ветке алгоритма необходимо выполнить более чем по одной команде, то следует воспользоваться конструкцией BEGIN.. END Задание 2. Определить содержится ли фамилия ‘Иванов’ в таблице Преподаватель.Код запроса имеет вид:use uch_proc
IF 'Глухов' in (select fio from prepodavatel)
print 'фамилия есть в списке преподавателей'
else
print 'фамилия нет в списке преподавателей'
Если разветвлений в алгоритме много и они одиночные, то для их замены можно воспользоваться конструкцией Case …..EndCASE <входное выражение> WHEN < выражение 1> THEN < выражение результат> WHEN < выражение 2> THEN < выражение результат> ELSE < выражение результат> EndКонструкция фактически представляет собой функцию. У функции имеется один параметр (входное выражение). Он указывается после Case, но не в скобках. Функция возвращает результат, поэтому эта конструкция должна входить в состав какого либо выражения. Если в примере, рассмотренном далее убрать PRINT, то система выдаст сообщение об ошибке, т.к. это будет неверный вызов функции.
Работает конструкция следующим образом. Если значение входного выражения и одного из перечисленных, стоящих после WHEN, совпадают, то возвращается выражение – результат, указанное после соответствующего THEN. Если не одного совпадения не отмечено, то возвращается результат, стоящий после ELSE. Если значение входного выражения совпадает более чем с одним значением выражения, стоящего после WHEN, то выполняется результат, соответствующий первому совпадению.
Задание 3 Вывести на экран значение суммы прописью, использую конструкцию Case …..End
declare @rub char(25)
set @rub = ‘6 руб’
Результат выполнения команды: таких денег в кассе нет
В следующем примере рассмотрим применение конструкции в запросе.
Задание 4 В запросе формируется список номеров студентов, оценок и добавлен столбец, в котором указано одно из значений (двоечник, троечник, хорошист, отличник)
declare @o int
use uch_proc
select ozenka, ns, kp,tit=
case ozenka
when 2 then 'двоечник'
when 3 then 'троечник'
when 4 then 'хорошист'
when 5 then 'отличник'
end
from uspevaemost
3 Операторы цикла
В Transact-SQL имеется конструкция для организации многократных повторений команд в программе: WHELE………CONTINUE. Одна обеспечивает выполнение цикла одного типа. Это цикл с предусловием. Синтаксис конструкции:
WHELE < условие>
{ команда | блок }
[BREAK]
{ команда | блок }
[ CONTINUE ]
Цикл можно завершить принудительно, для этого в нужном месте цикла нужно поместить служебное слово BREAK.
Задание 5 Вычислите и выведите на экран квадраты и кубы чисел от 1 до 5.
declare @r int
PRINT ' Число квадрат куб'
set @r = 1
WHILE @r <= 5
BEGIN
PRINT STR(@r)+ STR(@r*@r)+ STR(@r*@r*@r)
SET @r = @r+1
END
4 Оператор LIKE
LIKE применим только к полям типа CHAR или VARCHAR, с которыми он используется чтобы находить подстроки. Т.е. он ищет поле символа чтобы видеть, совпадает ли с условием часть его строки. Задание 5 Вывести фамилии преподавателей, начинающихся с буквы ‘А’USE uch_proc
SELECT * FROM prepodavatel
where fio like 'А%'
5 Операторы для обработки исключений
В Transact-SQL существует возможность обработки исключений:
BEGIN TRY
----- ЗАПРОС, ВЫЗЫВАЮЩИЙ ОПАСЕНИЯ
END TRY
BEGIN CATCH
----- ОБРАБОТКА ОШИБКИ, КОТОРАЯ МОЖЕТ ВОЗНИКНУТЬ
END CATCH
Контрольные задания.
1. По таблице ‘успеваемость’ определить получал студент с номером ‘01’ оценки. Если получал, то вывести на печать сообщение ‘Студент опрашивался’ в противном случае напечатать ‘Студент не получал оценок’.
2. Если вид занятия ‘лек’, то в сформированном столбце записать «лекционное занятие», если вид занятия ‘пр’, то в сформированном столбце записать «практическое занятие»
3. В индивидуальной базе данных сформулировать и привести пример с использованием оператора LIKE
4. В индивидуальной базе данных сформулировать и привести пример с использованием оператора Case …..End
Практическая работа №5
«Групповые операции. Агрегатные функции »Цель: «Сформировать навыки и умения реализации запроса с использованием групповых операций и агрегатных функций»
Ход Работы
1. Агрегатные функции Запросы могут производить обобщенное групповое значение полей точно также как и значение одного поля. Это делает с помощью агрегатых функций. Агрегатные функции производят одиночное значение для всей группы таблицы. Имеется список этих функций: · COUNT производит номера строк или не-NULL значения полей которые выбрал запрос.· SUM производит арифметическую сумму всех выбранных значений данного поля.· AVG производит усреднение всех выбранных значений данного поля.· MAX производит наибольшее из всех выбранных значений данного поля.· MIN производит наименьшее из всех выбранных значений данного поля. Только числовые поля могут использоваться с SUM и AVG. С COUNT, MAX, и MIN, могут использоваться и числовые или символьные поля. Задание 1. Вывести среднюю оценку по таблице Успеваемость для студента с номером ‘01’
USE uch_proc
SELECT AVG(ozenka) FROM uspevaemost
where ns='01'Функция COUNT несколько отличается от всех. Она считает число значений в данном столбце, или число строк в таблице. Когда она считает значения столбца, она используется с DISTINCT чтобы производить счет чисел различных значений в данном поле.
Задание 2. Определить в таблице Студент количество студентов в таблице
USE uch_proc
SELECT count(ns) FROM student
Чтобы подсчитать общее число строк в таблице, используйте функцию COUNT со звездочкой вместо имени поля, как в следующем примере:
USE uch_proc
SELECT count(*) FROM student
2. Групповые операции
Предложение GROUP BY позволяет вам определять подмножество значений в особом поле в терминах другого поля, и применять функцию агрегата к подмножеству. Это дает вам возможность объединять поля и агрегатные функции в едином предложении SELECT.
Задание 3. Определить для каждого студента по таблице Успеваемость минимальную оценку.
USE uch_proc
SELECT ns, min(ozenka) as 'наихудшая'
FROM uspevaemost
group by ns
GROUP BY применяет агрегатные функции независимо от серий групп которые определяются с помощью значения поля в целом. В этом случае, каждая группа состоит из всех строк с тем же самым значением поля ns, и MIN функция применяется отдельно для каждой такой группы. Это значение поля, к которому применяется GROUP BY, имеет, по определению, только одно значение на группу вывода, также как это делает агрегатная функция. Результатом является совместимость которая позволяет агрегатам и полям объединяться таким образом.Вы можете также использовать GROUP BY с многочисленными полями. Задание 4. Совершенствуя вышеупомянутый пример далее, предположим что вы хотите увидеть наименьшую оценку полученную каждым студентом по каждому предмету. Чтобы сделать это, вы должны сгруппировать таблицу uspevaemost по студентам и кодам предметов, и применить функцию MIN к каждой такой группе, подобно этому:
USE uch_proc
SELECT ns, min(ozenka) as 'наихудшая', kp
FROM uspevaemost
group by ns, kp
3.Предложение HAVING
Предположим, что в предыдущем примере, вы хотели бы увидеть MIN оценку только студента ‘01’ Вы не сможете использовать агрегатную функцию в предложении WHEREВы не сможете сделать что-нибудь подобно следующему:USE uch_proc
SELECT ns, min(ozenka) as 'наихудшая', kp
FROM uspevaemost
group by ns, kp
WHERE ns = ‘01’ Предложение HAVING определяет критерии используемые чтобы удалять определенные группы из вывода, точно также как предложение WHERE делает это для индивидуальных строк.Задание 5. Вывести наименьшую оценку полученную студентом ‘01’ по каждому предмету.
USE uch_proc
SELECT ns, min(ozenka) as 'наихудшая', kp
FROM uspevaemost
group by ns, kp
HAVING ns = ‘01’
4. Упорядочение вывода полей. Команда ORDER BY
Таблицы - это неупорядоченные наборы данных, и данные которые выходят из их, не обязательно появляются в какой-то определенной последовательности. SQL использует команду ORDER BY чтобы позволять вам упорядочивать ваш вывод. Эта команда упорядочивает вывод запроса согласно значениям в том или ином количестве выбранных столбцов. Многочисленные столбцы упорядочиваются один внутри другого, также как с GROUP BY, и вы можете определять возрастание (ASC) или убывание (DESC) для каждого столбца. По умолчанию установлено - возрастание.
Обратите внимание что, во всех случаях, столбцы которые упорядочиваются должны быть указаны в выборе SELECT.
Задание 6. Вывести по алфавиту список фамилий преподавателей
USE uch_proc
SELECT fio
FROM prepodavatel
order by fio
ORDER BY может кроме того, использоваться с GROUP BY для упорядочения групп. Если это так, то ORDER BY всегда приходит последним. Вот пример из Задания 4 с добавлением предложения ORDER BY. Перед группированием вывода, порядок студентов был произвольным.
Задание 7. Вывести по порядку номера студентов в задании 4
USE uch_proc
SELECT ns, min(ozenka) as 'наихудшая', kp
FROM uspevaemost
group by ns, kp
order by ns
Контрольные задания.
1. По таблице Успеваемость определить среднюю оценку в каждой группе.
2. По таблице Успеваемость определить количество оценок в каждой группе.
3. Из таблицы Студент вывести список студентов по алфавиту.
4. По таблице Изучение определить суммарное количество часов по предметам в каждой группе.
5. По таблице Изучение определить суммарное количество часов по предметам с vidz =’пр’ в каждой группе
6. Определить количество групп, изучающих предмет с кодом ‘01’
7. Определите количество преподавателей на каждой кафедре.
8. Сформулируйте и выполните запросы с использованием агрегатных функций в индивидуальной базе данных.
Практическая работа №6
«Создание многотабличного запроса»Цель: «Сформировать навыки и умения реализации запроса, построенного на основе данных из нескольких таблиц»
Ход Работы
Одна из наиболее важных особенностей запросов SQL - это их способность определять связи между многочисленными таблицами и выводить информацию из них в терминах этих связей. Эта особенность часто используется просто для эксплуатации связей встроенных в базу данных. Полное имя столбца таблицы фактически состоит из имени таблицы, сопровождаемого точкой и затем именем столбца. Имеются несколько примеров имен:Student. NsStudent.fioPredmet.npДо этого, вы могли опускать имена таблиц, потому что вы запрашивали только одну таблицу одновременно.
Когда данные выбираются из таблиц, связанных отношением один ко многим, то кроме условия запроса после ключевого слова WHERE записываются связи по ключевым полям. Например, если данные выбираются из таблиц student, gruppa, то необходимо указать равенство по связующим полям:
student.ng= gruppa.ng
Задание 1. Вывести на экран фамилии студентов, номер группы, в которой учится более 30 человек.
USE uch_proc
select student.fio, gruppa.ng, gruppa.kol
from student, gruppa
where student.ng= gruppa.ng and gruppa.kol>30
Если данные выбираются из таблиц, напрямую не связанных между собой, то указываются все таблицы, находящиеся между ними.
Задание 2. Вывести на экран фамилии преподавателей математики.
Таблицы ‘преподаватель’ и ‘предмет’ не связаны между собой. Но они имеют общие связующие поля с таблицей ‘изучение’
Рис 5 схема данных запроса
USE uch_proc
select prepodavatel.fio, predmet.np
from prepodavatel, predmet, isuchenie
where prepodavatel.tabn = isuchenie.tabn and
isuchenie.kp= predmet.kp and predmet.np='математика'
Задание 3 Вывести на экран фамилии преподавателей, количество часов, код предмета, номера групп, в которых ведут преподаватели с кафедры ‘информатика’
Рис 6 схема данных запроса
USE uch_proc
select prepodavatel.fio, isuchenie.ng, isuchenie.chas, isuchenie.kp
from prepodavatel, isuchenie, kafedra
where prepodavatel.tabn = isuchenie.tabn and
prepodavatel.kkaf =kafedra.kkaf and kafedra.namekaf='информатика'
Задание 4 Определите номер и фамилию студентов, которые получали оценку 4
Рис 7 схема данных запроса
USE uch_proc
select student.fio, student.ns, uspevaemost.ozenka
from student, uspevaemost
where student.ng= uspevaemost.ng and student.ns= uspevaemost.ns and uspevaemost.ozenka=4
Задание 5 Определите наименования предметов, по которым студенты получали 2
Рис 8 схема данных запроса
USE uch_proc
select uspevaemost.ozenka, predmet.np
from uspevaemost, isuchenie, predmet
where uspevaemost.ng = isuchenie.ng and uspevaemost.kp = isuchenie.kp and
uspevaemost.tabn = isuchenie.tabn and uspevaemost.vidz = isuchenie.vidz and
isuchenie.kp= predmet. kp and uspevaemost.ozenka=2
Контрольные задания.
- Выведите на экран фамилии студентов, которые получали оценки 3 или 4.
- Определите количество студентов, получивших каждую оценку. (2, 3, 4, 5)
- Выведите на экран оценки, вид занятия, наименование предмета, по которому студент ‘Аристов’ получал оценки
- Выведите номер группы, количество человек в группе, в которой обучается студентка ‘Борисова’
- Укажите фамилии преподавателей и номера групп, в которых изучаются предметы с количеством часов больше 100
- Определите наименования предметов, которые читаются преподавателями с кафедры ‘информатика’
- Выведите на экран фамилии преподавателей, которые ведут предметы в группе ‘102’
- Сформулируйте и создайте несколько многотабличных запросов в индивидуальной базе данных
Практическая работа №7
«Использование подзапросов»Цель: «Сформировать навыки и умения реализации вложенных подзапросов, построенных на основе данных из нескольких таблиц»
Ход Работы
1. Построение подзапросов.
Часто невозможно решить поставленную задачу путем использования одного запроса. Это особенно актуально в тех случаях, когда при использовании условия поиска в предложении WHERE
<сравниваемое значение> <оператор> <значение, с которым сравнивать>
значение, с которым надо сравнивать, заранее не определено и должно быть вычислено в момент выполнения оператора SELECT.
Другой причиной, которая должна побудить к использованию вложенных подзапросов, является то, что во многих случаях значение, с которым надо сравнивать, должно представлять собой не одно, а несколько значений.
Внутренний подзапрос представляет собой также оператор SELECT и кодирование его предложений подчиняется тем же правилам, что и для основного оператора SELECT.
В общем случае оператор SELECT с подзапросом имеет вид
SELECT...
FROM...
WHERE <сравниваемое значение> <оператор> SELECT...
FROM... WHERE...
Задание 1. С помощью подзапроса вывести номера студентов, обучающихся в группе с количеством человек >32
Рис 9 схема данных запроса
use uch_proc
select fio
from student
where ng in (select ng
from gruppa
where kol > 32)
В начале выполняется подзапрос, в котором выбираются все группы с количеством человек более 32. Внешний запрос выбирает запись из таблицы ‘студент’ и определяет входит ли номер группы в список номеро