На практических занятиях слушатели освоят основные приемы использования стандартного языка 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, и, кроме того, привилегию для создания собственной таблицы. Тем самым, все слушатели будут работать с общей базой данных в режиме мультидоступа.
Мы начнем с описания базы данных 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 будут продемонстрированы преподавателем.
Каждый слушатель должен будет создать одну таблицу с именем 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. Единственным ограничением уровня таблицы должно быть следующее: сумма штрафа не превышает удвоенной стоимости книги.
Занесите в таблицу две строки, не нарушая установленных ограничений целостности. Попробуйте занести строки, содержимое которых противоречит определенным ограничениям. Проверьте, что занесенные строки действительно содержатся в таблице.
Поскольку все слушатели работают под одним идентификатором, реально проверить возможности передачи привилегий мы не сможем. Тем не менее, каждый пользователь должен передать право на чтение всех столбцов созданной им таблицы пользователю с идентификатором public.
На этом занятии мы определим только самые простые представления над созданными таблицами. Более сложные возможности отложим до получения навыков составления запросов.
Каждый слушатель должен определить представление my_viewn, где n - номер слушателя с двумя столбцами: col1, соответствующим столбцу book_id таблицы my_lib0, и col2 со значением, равным сумме book_val и pen_val.
Проверьте, что через созданное представление выбираются две строки, соответствующие строкам, ранее занесенным в таблицу my_libn.
Добавьте к таблице my_libn столбец с именем added типа char(5) со значением по умолчанию 'empty'.
Вставьте в таблицу еще две строки, одна из которых содержит значение столбца added, а другая - нет. Просмотрите содержимое таблицы. Просмотрите содержимое представления.
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.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.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.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.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.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'. Почему эти операции не выполняются?