Файл: Учебное пособие основы проектирования баз данных. Практические работы для специальности спо 09. 02. 04 Информационные системы (по отраслям).doc

SELECT Группа, Count(Группа) AS [Count-Группа]

FROM Анкета

GROUP BY Группа;

8. Использование условия Having

Устраним из предыдущего запроса те группы, в которых имеют только одного обучающегося:

SELECT Группа, Count(Группа) AS [Count-Группа]

FROM Анкета

GROUP BY Группа

HAVING COUNT(*)<>1;

9. Операция объединения.

В SQL предусмотрена возможность выполнения операции реляционной алгебры "ОБЪЕДИНЕНИЕ" (UNION) над отношениями, являющимися результатами оператора SELECT.

9.1. Построить таблицу Анкета1, добавить в нее 2 записи

CREATE TABLE Анкета1([№] integer,

[Группа] text,
[Фамилия] text NOT NULL,
[Имя] text,

[Отчество] text,

[Адрес] text,

[ДомашнийТелефон] text,
[Дата рождения] date,
CONSTRAINT [Индекс1] PRIMARY KEY ([№]));
9.2. Осуществить операцию объединения таблиц

SELECT №,Фамилия,Имя,Отчество FROM Анкета

UNION

SELECT №,Фамилия,Имя,Отчество FROM Анкета1;

Содержание отчета

Название ПР, цель, содержание
Команды SQL для выполнения заданий
Выводы по ПР.

Контрольные вопросы

Укажите операторы добавления и удаления записей из таблиц.
Назовите команду для модификации данных таблицы.
Охарактеризуйте параметры команды формирования структуры таблицы
Укажите операторы для изменения структуры базы данных.
Охарактеризуйте р инструкции SELECT.
Назовите операторы, входящие в условное выражение
Назовите параметры группировки
Каким образом производится сортировка данных?
Как осуществить операцию объединения таблиц?

Практическая работа 12

Тема: «Вычисления внутри SELECT»

Цель работы: изучение вычислительных операций SQL

Материально-техническое обеспечение: OC Windows, MS Access

Порядок выполнения работы

Открыть базу данных TEST с помощью команды USE TEST;
Создать таблицы, содержащие информацию о библиотечном фонде и читателях

2.1. Таблица books содержит информацию: Код книги, Автор, Название, Количество страниц, год
издания

CREATE TABLE books(kod INT PRIMARY KEY, autor VARCHAR(15), nazvanie VARCHAR(20), kol INT, god_pub INT, zena DECIMAL(3,2));

Таблица publichers содержит сведения об издательстве: Код книги, Издательство, Город:
CREATE TABLE publichers (kod INT PRIMARY KEY, publisher VARCHAR(IO), gorod
VARCHAR(IO));
Таблица klients содержит информацию о читателях: Номер, Фамилия, Город, Улица, Дом,
Кодкниги, Дата выдачи книги, Дата возврата книги

3. Создание вычисляемых полей

3.1. Создать в таблице books вычисляемое поле, в котором показано количество лет с момента
публикации книг

SELECT kod, autor, nazvanie, 2006-god_pub FROM books;

3.2. Использование строковых констант в запросе

Добавить в предыдущее выражение строковую константу «Количество лет» SELECT kod, autor, nazvanie,"Количество лет", 2006-god_pub FROM books;

3.3. Использование условий при создании вычисляемых полей в запросе

Создать в таблице books вычисляемое поле, в котором показано количество лет с момента публикации книг, изданных в XX веке

4. Вычисляемые поля в связанных таблицах

Показать информацию о читателях, название и автора книг, а также сроки задержки книг для каждого читателя

SELECT books.autor, books.nazvanie, klients.n, klients.fam, klients.gorod, klients.ul, klients.dom, klients.kod, klients. Data2 klients.datal FROM books, klients WHERE books.kod=j(Slients.kod;

Использование функции AVG- среднее по всем значениям данного поля
Найти среднюю цену книг в таблице books, изданных до 2003 г.
SELECT AVG(zena) FROM books WHERE god pub<2003 ;
Использование функции COUNT - число записей

Найти количество книг, выпущенных в издательстве «Мир» (по таблице publichers)

Использование функции МАХ - максимальное из всех значений данного поля Найти название
самой дорогой книги
Использование функции MIN - минимальное из всех значений данного поля Найти автора и
название самой новой книги.
Использование функции SUM - сумма всех значений данного поля Подсчитать общую цену всех
книг

Использование функций в связанных таблицах Подсчитать общую цену книг, выпущенных в
издательстве «Мир».
Вычисления в групповых запросах

11.1. Найти количество книг каждого автора

SELECT autor, count(nazvanie) FROM books GROUP BY autor;

11.2. Найти количество книг, выпущенных в каждом издательстве
SELECT publishers.publisher, count(books.hazvanie)

FROM books,publishers WHERE books.kod=publishers.kod GROUP BY publisher;

Оформите отчет по работе

Содержание отчета

Название ПР, цель, содержание
Команды SQL для выполнения заданий
Выводы по ПР.

Контрольные вопросы

Укажите и объясните назначение основных агрегирующих функций.
Как выполнить запрос по связанным таблицам.
Охарактеризуйте параметры команды формирования структуры таблицы
Укажите операторы объединения таблиц.
Охарактеризуйте инструкции SELECT.

Практическая работа 13

Тема: «Использование переменных на языке SQL. Ограничение доступа к данным таблиц.»

Цель работы: изучение возможностей работы с переменными на языке SQL, ограничения доступа к данным таблиц и изучение механизма транзакций.

Материально-техническое обеспечение: OC Windows, MS Access

Порядок выполнения работы

Для использования транзакций необходимо работать с таблицами специального типа InnoDB. Для их использования нужно создать или отредактировать файл my.cnf.

Зайдите в каталог BIN и запустите файл mysqld-max
Создайте базу данных

mysql> CREATE DATABASE pl17;

Откройте базу данных с помощью команды

mysql> USE pl17;

Создайте таблицу PR1 используя команду SQL CREATE TABLE, где указаны номер, фамилия, имя учащихся и их специальность, и определите ее тип - InnoDB.

CREATE TABLE pr1(n INT PRIMARY KEY, fam VARCHAR(10), imja VARCHAR(10),

spez VARCHAR(15)) TYPE=InnoDB;

Создайте таблицу PR2 используя команду SQL CREATE TABLE, в соответствии с предложенной структурой – указана оценка за защиту дипломной работы и дата защиты, и определите ее тип - InnoDB.

CREATE TABLE pr2(n INT PRIMARY KEY, ocenka int, data VARCHAR(10),

spez VARCHAR(15)) TYPE=InnoDB;

Выполните заполнение базы данных (3 записи).

INSERT INTO PR1 VALUES ("Значение поля1","Значение поля2",…,"Значение поляN");

Осуществите просмотр базы данных как транзакцию

Begin;

Select * from pr1, pr2 where pr1.n=pr2.n;

Commit;

Убедитесь в положительном выполнении команды Commit.

10. Добавьте в таблицы pr1 и pr2 еще по одной записи. Выполните действие как транзакцию:

Begin;

INSERT INTO PR1 VALUES ("Значение поля1","Значение поля2",…,"Значение поляN");

INSERT INTO PR2 VALUES ("Значение поля1","Значение поля2",…,"Значение поляN");

Select * from pr1, pr2 where pr1.n=pr2.n;

Commit;

11. Убедитесь, что транзакция выполнена.

12. Отмените действие транзакции: добавьте в таблицу pr1 новую запись и отмените добавление.

Begin;

INSERT INTO PR1 VALUES ("Значение поля1","Значение поля2",…,"Значение поляN");

INSERT INTO PR2 VALUES ("Значение поля1","Значение поля2",…,"Значение поляN");

Select * from pr1, pr2 where pr1.n=pr2.n;

Rollback;

13. Выполните просмотр таблиц Select * from pr1, pr2 where pr1.n=pr2.n;

и убедитесь, что новая запись отсутствует.

14. Замените оценку, полученную учащимся с номером 3 на «5». Зафиксируйте транзакцию оператором commit

15. Выполните замену фамилии учащегося с номером 1 на «Иванов», а затем произведите откат транзакции

16. Таблицы, которые не являются таблицами InnoDB, нельзя восстановить с помощью отката транзакции, но можно защитить от нежелательных изменений с помощью блокировки.

16.1. Заблокируйте таблицу books от изменений