Приложение. Практические занятия курса "Введение в стандарты языка баз данных SQL"

На практических занятиях слушатели освоят основные приемы использования стандартного языка SQL в интерактивном режиме. Мы сознательно ограничиваемся этим режимом по следующим соображениям. Во-первых, цель этого курса состоит не в том, чтобы научить разрабатывать информационные приложения, а в том, чтобы показать возможности языка SQL. В этом смысле более важно понять принципы взаимодействия с базами данных. Используя интерактивный режим, такого понимания можно добиться существенно быстрее. Во-вторых, использование интерактивного режима, в частности, демонстрирует один из примеров приложения, которое само разработано на основе динамического SQL. Тем самым, слушатели могут на практике убедиться в том, что язык SQL дает возможность создавать разнообразные приложения, ориентированные на непосредственное общение с пользователем.

В качестве основного инструмента практических занятий будет использоваться MicrosoftSQLServer 6.5. Этот выбор не связан с тем, что данный сервер обладает какими-либо исключительными характеристиками. На самом деле, для наших целей подошла бы любая реляционная СУБД, в достаточной степени поддерживающая стандарт SQL. В частности, можно было бы использовать свободно распространяемую СУБД PostgreSQL или созданный в России сервер GSQL. Но MSSQLServer является исключительно популярным продуктом и работает на распространенных платформах серии Windows. Сервер входит в шестерку наиболее известных и мощных продуктов управления базами данных (остальные пять СУБД включают Informix, Oracle, Sybase, CA-OpenIngres и DB2 компании IBM). Не факт, что MSSQLServer идеально подходит в качестве основы корпоративных информационных приложений, но его возможностей вполне достаточно для демонстрации возможностей стандарта SQL.

Как и все другие производители реляционных СУБД, компания Microsoft внесла ряд расширений в свой вариант SQL. Тем не менее, продукт соответствует стандарту SQL/89 и вводному уровню SQL/92. Естественно, на занятиях мы будем пользоваться только стандартным подмножеством языка.

На практике будет использоваться стандартная демонстрационная база данных pubs, поставляемая вместе с SQL-сервером. В этой базе данных имеются некоторые объекты, выходящие за рамки стандарта SQL. Эти объекты использоваться не будут. Каждый из слушателей получит привилегии доступа по чтению ко всем таблицам, существующим в pubs, и, кроме того, привилегию для создания собственной таблицы. Тем самым, все слушатели будут работать с общей базой данных в режиме мультидоступа.

Занятие 1. Работа с MicrosoftSQLServer с использованием языка SQL в интерактивном режиме

Мы начнем с описания базы данных pubs, которая будет использоваться на практических занятиях. Эта база данных имитирует информационное хранилище издательской компании и состоит из 11 таблиц: authors (авторы), discounts (скидки), employee (служащие), jobs (задания), pub_info (информация об издательствах), publishers (издательства), roysched (авторские гонорары), sales (продажи), stores (магазины), titleauthor (название-автор), titles (названия).

Таблицы определены следующим образом:

Таблица authors

Имя столбца	Тип данных	Возможность содержать NULL	Значение по умолчанию
au_id	id	нет	нет
au_lname	varchar(40)	нет	нет
au_fname	varchar(20)	нет	нет
phone	char(12)	нет	'UNKNOWN'
address	varchar(40)	да	нет
city	varchar(20)	да	нет
state	char(2)	да	нет
zip	char(5)	да	нет
contract	bit	нет	нет

Первичным ключом объявлен столбец au_id. Определены два ограничения целостности на столбцах au_id (au_idLIKE '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]') и zip (zipLIKE '[0‑9][0-9][0-9][0-9][0-9]').

К типу данных id нужно относиться как к типу символьных строк (CHAR(9)). Тип varchar - аналог CHARACTERVARYINGSQL/92. Тип bit - нестандартный, и мы не будем использовать столбец contract в наших примерах.

Таблица discounts

Имя столбца	Тип данных	Возможность содержать NULL	Значение по умолчанию
discounttype	varchar(40)	нет	нет
stor_id	char(4)	да	нет
lowqty	smallint	да	нет
highqty	smallint	да	нет
discount	float	нет	нет

Эта таблица представляет интерес тем, что в ней отсутствует первичный ключ (если внимательно посмотреть на требования стандарта SQL, то можно увидеть, что такая ситуация допускается, хотя на практике встречается очень редко). Для таблицы определено ссылочное ограничение: столбец stor_id ссылается на первичный ключ stor_id таблицы stores.

Таблица employee

Имя столбца	Тип данных	Возможность содержать NULL	Значение по умолчанию
emp_id	empid	нет	нет
fname	varchar(20)	нет	нет
minit	char(1)	да	нет
lname	varchar(30)	нет	нет
job_id	smallint	нет	1
job_lvl	tinyint	нет	10
pub_id	char(4)	нет	'9952'
hire_date	datetime	нет	GETDATE()

Первичным ключом объявлен столбец emp_id. Имеются два ссылочных ограничения: столбец job_id ссылается на первичный ключ job_id таблицы jobs, столбец pub_id ссылается на первичный ключ pub_id таблицы publishers. Определено ограничение для столбца emp_id: (emp_idLIKE '[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' ORemp_idLIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]'). Тем самым, видно, что тип emp_id на самом деле есть varchar(9). Тип данных tinyint является нестандартным, и мы не будем использовать столбец job_lvl. Функция GETDATE() является аналогом стандартной функции CURRENT_DATE, т.е. значением по умолчанию столбца hire_date является текущая дата.

Таблица jobs

Имя столбца	Тип данных	Возможность содержать NULL	Значение по умолчанию
job_id	smallint	нет	IDENTITY(1,1)
job_desc	varchar(50)	нет	'NewPosition - titlenotformalizedyet'
min_lvl	tinyint	нет	нет
max_lvl	tinyint	нет	нет

Первичный ключ - job_id. Значение по умолчанию этого столбца вырабатывается нестандартной функцией IDENTITY, генерирующей уникальные целые значения.

Таблица pub_info

Имя столбца	Тип данных	Возможность содержать NULL	Значение по умолчанию
pub_id	char(4)	нет	нет
logo	image	да	нет
pr_info	text	да	нет

Первичный ключ таблицы - pub_id. Этот же столбец является и внешним ключом и ссылается на первичный ключ pub_id таблицы publishers. Типы данных image и text являются нестандартными, и мы не будем использовать столбцы logo и pr_info.

Таблица publishers

Имя столбца	Тип данных	Возможность содержать NULL	Значение по умолчанию
pub_id	char(4)	нет	нет
pub_name	archar(40)	да	нет
city	varchar(20)	да	нет
state	char(2)	да	нет
country	varchar(30)	да	'USA'

Первичный ключ - pub_id. Для этого столбца, кроме того, определено следующее ограничение: (pub_id = '1622' ORpub_id = '0877' ORpub_id = '0736' ORpub_id = '1389' ORpub_idLIKE '99[0-9][0-0]').

Таблица roysched

Имя столбца	Тип данных	Возможность содержать NULL	Значение по умолчанию
title_id	tid	нет	нет
lorange	int	да	нет
hirange	int	да	нет
royalty	int	да	нет

В этой таблице отсутствует первичный ключ. Объявлено одно ссылочное ограничение: столбец title_id ссылается на первичный ключ title_id таблицы titles. Тип tid - синоним char(6), тип int - синоним стандартного типа INTEGER.

Таблица sales

Имя столбца	Тип данных	Возможность содержать NULL	Значение по умолчанию
stor_id	char(4)	нет	нет
ord_num	varchar(20)	нет	нет
ord_date	datetime	нет	нет
qty	smallint	нет	нет
payterms	varchar(12)	нет	нет
title_id	tid	нет	нет

Первичный ключ таблицы образует комбинация полей stor_id, ord_num, title_id. Определены два внешних ключа: столбец stor_id ссылается на первичный ключ stor_id таблицы stores, а столбец title_id - на первичный ключ title_id таблицы titles.

Таблица stores

Имя столбца	Тип данных	Возможность содержать NULL	Значение по умолчанию
stor_id	char(4)	нет	нет
stor_name	varchar(40)	да	нет
stor_address	varchar(40)	да	нет
city	varchar(20)	да	нет
state	char(2)	да	нет
zip	char(5)	да	нет

Первичным ключом является столбец stor_id.

Таблица titleauthor

Имя столбца	Тип данных	Возможность содержать NULL	Значение по умолчанию
au_id	id	нет	нет
title_id	tid	нет	нет
au_ord	tinyint	да	нет
royaltyper	int	да	нет

Первичный ключ составляет комбинация столбцов au_id, title_id.

Таблица titles

Имя столбца	Тип данных	Возможность содержать NULL	Значение по умолчанию
title_id	tid	нет	нет
title	varchar(80)	нет	нет
type	char(12)	нет	'UNDECIDED'
pub_id	char(4)	да	нет
price	money	да	нет
advance	money	да	нет
royalty	int	да	нет
ytd_sales	int	да	нет
notes	varchar(200)	да	нет
pubdate	datetime	нет	GETDATE()

Первичный ключ - title_id. Внешний ключ pub_id ссылается на первичный ключ pub_id таблицы publishers. Тип money - нестандартный, и мы не будем использовать столбцы price и advance.

Содержимое базы данных можно посмотреть в приложении B руководства по TransactSQL, входящего в оперативно доступную документацию по MicrosoftSQLServer, а также в прилагаемом тексте.

Слушатели будут работать с базой данных pubs с рабочих станций. Для каждого слушателя будет использоваться одно и то же входное имя (admin) и один и тот же пароль (adminnt). Следует учитывать, что пользователь admin обладает привилегиями администратора WindowsNT. Поэтому, чтобы не разрушить систему, слушателям не следует выполнять какие-либо действия, не предусмотренные программой занятий, без согласования с преподавателями.

Для доступа к базе данных будет использоваться программа MicrosoftQuery, позволяющая, в частности, через средства ODBC работать с MSSQLServer. Правила вызова MSQuery с обеспечением общего доступа к базе данных pubs будут продемонстрированы преподавателем.

Занятие 2. Определение и изменение схемы базы данных

5.4. Создание таблицы

Каждый слушатель должен будет создать одну таблицу с именем my_libn (моя библиотека), где n - номер рабочей станции слушателя (номер своей рабочей станции можно найти на корпусе системного блока). Таблица должна включать следующие столбцы: book_no (порядковый номер книги в библиотеке), au_id (идентификатор автора), title_id (идентификатор издания), book_val (ваша оценка стоимости книги), pen_val (ваша оценка величины штрафа, который следует изъять с возможного похитителя книги), book_desc (краткая словесная характеристика книги).

Тип данных столбцов book_id, book_val, pen_val - целые числа, тип данных столбца pen_val - строки символов переменной длины с максимальной длиной 40 символов.

Первичным ключом таблицы должен быть объявлен столбец book_id. Столбец au_id должен быть объявлен внешним ключом, ссылающимся на первичный ключ таблицы authors, а столбец title_id - внешним ключом, ссылающимся на первичный ключ таблицы titles. Кроме того, должны быть объявлены два ограничения на столбцы: значения каждого из столбцов book_val и pen_val не должны превосходить 10.000. Единственным ограничением уровня таблицы должно быть следующее: сумма штрафа не превышает удвоенной стоимости книги.

5.5. Заполнение созданной таблицы

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

5.6. Привилегии

Поскольку все слушатели работают под одним идентификатором, реально проверить возможности передачи привилегий мы не сможем. Тем не менее, каждый пользователь должен передать право на чтение всех столбцов созданной им таблицы пользователю с идентификатором public.

5.7. Определение представления

На этом занятии мы определим только самые простые представления над созданными таблицами. Более сложные возможности отложим до получения навыков составления запросов.

Каждый слушатель должен определить представление my_viewn, где n - номер слушателя с двумя столбцами: col1, соответствующим столбцу book_id таблицы my_lib0, и col2 со значением, равным сумме book_val и pen_val.

Проверьте, что через созданное представление выбираются две строки, соответствующие строкам, ранее занесенным в таблицу my_libn.

5.8. Изменение схемы таблицы

Добавьте к таблице my_libn столбец с именем added типа char(5) со значением по умолчанию 'empty'.

Вставьте в таблицу еще две строки, одна из которых содержит значение столбца added, а другая - нет. Просмотрите содержимое таблицы. Просмотрите содержимое представления.

Занятие 3. Простые операторы выборки из базы данных

3.1.Получить имена и номера телефонов авторов по фамилии Ringer (из таблицы authors)

3.2.Получить идентификаторы заданий, описание которых начинается со слова 'Chief' (из таблицы jobs)

3.3.Получить идентификаторы служащих, имя которых начинается на букву 'P', выполняющих задания со значением идентификатора, большим 12 (из таблицы employee).

3.4.Получить фамилии и имена авторов, не проживающих в Калифорнии, zip-код которых начинается на '4', а также тех, идентификатор автора которых начинается на '998' (из таблицы authors).

3.5.Получить описание заданий, нижний уровень оплаты которых равен верхнему уровню оплаты (из таблицы jobs).

3.6.Получить число различных заданий, минимальное значение минимального уровня оплаты, максимальное значение максимального уровня оплаты и среднее значение суммы минимального и максимального уровне оплаты для заданий с идентификаторами в диапазоне от 8 до 13 (из таблицы jobs).

3.7.Выдать список штатов, в которых проживают авторы; аббревиатура каждого штата должна участвовать в списке только один раз (из таблицы authors).

Занятие 4. Операторы выборки из одной таблицы со вложенными подзапросами

4.1.Выдать имена и фамилии авторов таких, что имеются служащие с такими же именами (из таблицы authors с использованием таблицы employee).

4.2.Выдать имена и фамилии авторов, проживающих в том же штате, что и автор с фамилией White, и имеющих идентификаторы автора, которые начинаются с символа '2' (из таблицы authors).

4.3.Выдать фамилии служащих, выполняющих задания со значениями идентификаторов от 1 до 5, таких, что существуют другие служащие, выполняющие то же задание (из таблицы employee).

4.4.Выдать аббревиатуру названий штатов, в которых проживает только по одному автору (из таблицы authors).

4.5.Выдать фамилии служащих, выполняющих задания с нижним уровнем оплаты большим 175 и таким, что существуют задания с таким же верхнем уровнем оплаты (из таблицы employee с использованием таблицы jobs).

4.6.Выдать общий объем продаж, произведенных магазинами, находящимися в штатах, в которых проживает хотя бы один автор (из таблицы sales с использованием таблиц stores и authors).

4.7.Выдать названия магазинов с общим объемом продаж большим, чем объем продаж магазинов, расположенных в штате Oregon (OR) (из таблицы stores с использованием таблиц sales и stores).

Занятие 5. Запросы к одной таблице с использованием агрегатных функций и раздела GROUPBY

5.1.Выдать аббревиатуру названий штатов, в которых проживает хотя бы один автор (из таблицы authors).

5.2.Выдать идентификаторы магазинов, число их заказов и общий объем продаж (из таблицы sales).

5.3.Для каждого задания со значением идентификатора, большим 5, выдать значение идентификатора задания и число служащих, выполняющих это задание (из таблицы employee).

5.4.Для магазинов, выполнивших заказ в объеме, большем 20, выдать объем заказа, число магазинов, выполняших заказ одного и того же объема, и число заказов одного и того же объема (из таблицы sales).

5.5.Выдать идентификаторы магазинов и суммарный объем продаж для магазинов, выполнивших хотя бы один заказ объемом больше 25 (из таблицы sales).

5.6.Выдать аббревиатуру названий штатов и число магазинов в каждом штате для штатов, в которых живет хотя бы один автор и располагается хотя бы один магазин (из таблицы stores с использованием таблицы authors).

5.7.Для магазинов, находящихся в Калифорнии, выдать идентификатор магазина, число заказов и общий объем продаж магазина с наибольшим общим объемом продаж (из таблицы sales с использованием таблицы stores).

Занятие 6. Запросы с соединениями

6.1.Выдать идентификаторы и названия магазинов, а также номера заказов для магазинов, находящихся в штате Oregon ('OR') (использовать таблицы sales и stores).

6.2.Выдать идентификаторы и названия магазинов, находящихся в штате California ('CA') (использовать таблицы sales и stores).

6.3.Выдать наименования заданий и число служащих, выполняющих каждое задание, для служащих, фамилии которых начинаются на букву от 'A' до 'M', и для заданий, значения идентификаторов которых содержатся в диапазоне от 5 до 11 (из таблиц employee и jobs).

6.4.Выдать названия и адреса магазинов с минимальным объемом заказа большим 10 и максимальным объемом заказа меньшим 50 (использовать таблицы sales и stores).

6.5.Выдать имена и фамилии авторов книг, отнесенных к категории 'business', а также идентификаторы книг (использовать таблицы authors, tittleauthor и titles).

6.6.Выдать имена и фамилии авторов книг, относящихся к категории 'business' и написанных в соавторстве, а также идентификаторы этих книг (использовать таблицы authors, tittleauthor и titles).

6.7.Выдать имена и фамилии авторов, прошивающих не в Калифорнии, книги которых заказываются в магазинах, расположенных не в том штате, где проживает автор, а также получить названия обоих штатов (использовать таблицы authors, stores, titleauthor).

Занятие 7. Дополнительные возможности операторов выборки

7.1.Выдать список фамилий и идентификаторов заданий служащих, выполняющий задания со значениями идентификаторов 10 и 11. Список упорядочить по возрастанию значений идентификаторов, а для каждого идентификатора - по убыванию значений фамилий в лексикографическом порядке (из таблицы employee).

7.2.Выдать список названий калифорнийских магазинов и их общего объема продаж, упорядоченный по значению общего объема продаж (из таблицы sales).

7.3.Выдать аббревиатуру штатов, в которых проживают авторы, а также тех, в которых находятся книжные магазины (использовать таблицы authors и stores).

7.4.Для заданий со значениями идентификатора 9 и 10 выдать значения уровни оплаты сотрудников, выполняющих такие задания, а также минимальный и максимальный уровни оплаты; результат упорядочить по значению уровня оплаты (использовать таблицы employee и jobs).

7.5.Выдать аббревиатуру штатов, в которых проживают авторы книги категории 'popular_comp', а также тех штатов, в которых находятся книжные магазины, продавшие эти книги (использовать таблицы authors, titleauthor, sales и stores).

Занятие 8. Изменение таблицы базы данных

8.1.Для целей этого занятия каждый слушатель должен создать новую таблицу с именем new_libn, где n - номер рабочей станции. Таблицы должна содержать следующие поля: title_id (тип tid), title (тип varchar(80), и pub_id (тип char(4)). Первичным ключом объявите столбец title_id. Внешний ключ - столбец pub_id ссылается на первичный ключ pub_id таблицы publishers; кроме того, столбец title_id также является внешним ключом, ссылающимся на первичный ключ title_id таблицы titles.

8.2.Вставьте в таблицу соответствующие данные из первых двух строк таблицы titles, проверьте правильность выполнения операций.

8.3.Теперь пополним свои библиотеки популярной компьютерной литературой: добавьте к таблице new_libn строки из таблицы titles, для которых значением столбца type является 'popular_comp'. Проверьте, что операция выполнилась правильно.

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

8.5.Удалить из таблицы строку со значением поля title_id равным 'BU1111'; проверить, что удалилась нужная строка.

8.6.Удалить из таблицы new_libn описания книг, относящихся к категории 'business'. Проверить, что удалилась единственная строка со значением столбца title_id равным 'BU1032'.

8.7.Изменить название книги, написанной автором по фамилии Carson, на 'ThisisthebookbyMrCarson'; проверить, что нужная строка изменилась.

8.8.Проверить, что не будут выполняться следующие операции: для строки таблицы со значением столбца title_id равным 'PC8888' поменять значение этого столбца на 'PC1035'; для строки таблицы со значением столбца title_id равным 'PC8888' поменять значение этого столбца на 'ZZ8888'; для строки таблицы со значением столбца title_id равным 'PC8888' поменять значение поля pub_id на '1888'. Почему эти операции не выполняются?