Приложение. Практические занятия курса "Введение в стандарты языка баз данных SQL"
На практических занятиях слушатели освоят основные приемы использования стандартного языка SQL в интерактивном режиме. Мы сознательно ограничиваемся этим режимом по следующим соображениям. Во-первых, цель этого курса состоит не в том, чтобы научить разрабатывать информационные приложения, а в том, чтобы показать возможности языка SQL. В этом смысле более важно понять принципы взаимодействия с базами данных. Используя интерактивный режим, такого понимания можно добиться существенно быстрее. Во-вторых, использование интерактивного режима, в частности, демонстрирует один из примеров приложения, которое само разработано на основе динамического SQL. Тем самым, слушатели могут на практике убедиться в том, что язык SQL дает возможность создавать разнообразные приложения, ориентированные на непосредственное общение с пользователем.
В качестве основной СУБД на практических занятиях будет использоваться PostgreSQL. Этот выбор не связан с какими-либо исключительными особенностями данного сервера: для наших целей подошла бы любая современная реляционная СУБД, в разумной степени поддерживающая стандарт SQL. Однако PostgreSQL является свободно распространяемой системой, работает на разных платформах, широко применяется как в учебной, так и в промышленной среде и при этом достаточно близка к стандарту SQL, чтобы на ее основе удобно демонстрировать основные возможности языка.
Как и любая реальная СУБД, PostgreSQL содержит ряд расширений по отношению к стандарту SQL. Тем не менее, на занятиях мы будем по возможности придерживаться стандартного подмножества языка и использовать нестандартные средства только там, где без этого трудно обойтись по техническим причинам.
На практике будет использоваться учебная база данных pubs, перенесенная в PostgreSQL из классического демонстрационного набора и приведенная к более современным типам данных. При этом сохраняются те же таблицы, связи и учебная логика, что и в предыдущей редакции практикума. Каждый из слушателей получит право чтения всех таблиц базы pubs и, кроме того, право на создание собственных таблиц. Тем самым, все слушатели будут работать с общей базой данных в многопользовательском режиме.
Занятие 1. Работа с PostgreSQL с использованием языка SQL в интерактивном режиме
Мы начнем с описания базы данных pubs, которая будет использоваться на практических занятиях. Эта база данных имитирует информационное хранилище издательской компании и состоит из 11 таблиц: authors (авторы), discounts (скидки), employee (служащие), jobs (задания), pub_info (информация об издательствах), publishers (издательства), roysched (авторские гонорары), sales (продажи), stores (магазины), titleauthor (название-автор), titles (названия).
Таблицы определены следующим образом:
Примечание. Ниже приведена современная версия схемы, сохраняющая состав таблиц, их ключи и смысл данных исходного практикума, но использующая более привычные сегодня типы данных и обозначения.
Таблица authors
| Имя столбца | Тип данных | Возможность содержать NULL | Значение по умолчанию |
| au_id | char(11) | нет | нет |
| 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 | boolean | нет | нет |
Первичным ключом объявлен столбец au_id. Для столбцов au_id и zip определены ограничения целостности, задающие допустимый формат идентификатора автора и почтового индекса.
Столбец au_id удобно рассматривать как символьное поле фиксированной длины char(11). Тип varchar соответствует стандартному character varying. Столбец contract имеет тип boolean; в наших примерах он использоваться не будет.
Таблица 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 | varchar(9) | нет | нет |
| fname | varchar(20) | нет | нет |
| minit | char(1) | да | нет |
| lname | varchar(30) | нет | нет |
| job_id | smallint | нет | 1 |
| job_lvl | smallint | нет | 10 |
| pub_id | char(4) | нет | '9952' |
| hire_date | date | нет | CURRENT_DATE |
Первичным ключом объявлен столбец emp_id. Имеются два ссылочных ограничения: столбец job_id ссылается на первичный ключ job_id таблицы jobs, столбец pub_id ссылается на первичный ключ pub_id таблицы publishers. Для столбца emp_id задано ограничение, определяющее допустимый формат идентификатора сотрудника. С практической точки зрения emp_id следует рассматривать как символьный идентификатор типа varchar(9). Столбец job_lvl в современной версии практикума будем считать обычным целочисленным полем небольшого диапазона. Значением по умолчанию столбца hire_date является текущая дата (CURRENT_DATE).
Таблица jobs
| Имя столбца | Тип данных | Возможность содержать NULL | Значение по умолчанию |
| job_id | integer | нет | GENERATED BY DEFAULT AS IDENTITY |
| job_desc | varchar(50) | нет | 'NewPosition - titlenotformalizedyet' |
| min_lvl | smallint | нет | нет |
| max_lvl | smallint | нет | нет |
Первичный ключ - job_id. Значения этого столбца генерируются автоматически.
Таблица pub_info
| Имя столбца | Тип данных | Возможность содержать NULL | Значение по умолчанию |
| pub_id | char(4) | нет | нет |
| logo | bytea | да | нет |
| pr_info | text | да | нет |
Первичный ключ таблицы - pub_id. Этот же столбец является и внешним ключом и ссылается на первичный ключ pub_id таблицы publishers. Для двоичных данных используется тип bytea, а для больших текстовых фрагментов - тип text. В наших примерах столбцы logo и pr_info использоваться не будут.
Таблица publishers
| Имя столбца | Тип данных | Возможность содержать NULL | Значение по умолчанию |
| pub_id | char(4) | нет | нет |
| pub_name | varchar(40) | да | нет |
| city | varchar(20) | да | нет |
| state | char(2) | да | нет |
| country | varchar(30) | да | 'USA' |
Первичный ключ - pub_id. Для этого столбца, кроме того, определено ограничение, задающее допустимые значения идентификаторов издательств.
Таблица roysched
| Имя столбца | Тип данных | Возможность содержать NULL | Значение по умолчанию |
| title_id | char(6) | нет | нет |
| lorange | int | да | нет |
| hirange | int | да | нет |
| royalty | int | да | нет |
В этой таблице отсутствует первичный ключ. Объявлено одно ссылочное ограничение: столбец title_id ссылается на первичный ключ title_id таблицы titles. Столбец title_id следует рассматривать как символьный идентификатор длины 6, а тип int является синонимом стандартного типа integer.
Таблица sales
| Имя столбца | Тип данных | Возможность содержать NULL | Значение по умолчанию |
| stor_id | char(4) | нет | нет |
| ord_num | varchar(20) | нет | нет |
| ord_date | date | нет | нет |
| qty | smallint | нет | нет |
| payterms | varchar(12) | нет | нет |
| title_id | char(6) | нет | нет |
Первичный ключ таблицы образует комбинация полей 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 | char(11) | нет | нет |
| title_id | char(6) | нет | нет |
| au_ord | smallint | да | нет |
| royaltyper | int | да | нет |
Первичный ключ составляет комбинация столбцов au_id, title_id.
Таблица titles
| Имя столбца | Тип данных | Возможность содержать NULL | Значение по умолчанию |
| title_id | char(6) | нет | нет |
| title | varchar(80) | нет | нет |
| type | char(12) | нет | 'UNDECIDED' |
| pub_id | char(4) | да | нет |
| price | numeric(10,2) | да | нет |
| advance | numeric(10,2) | да | нет |
| royalty | int | да | нет |
| ytd_sales | int | да | нет |
| notes | varchar(200) | да | нет |
| pubdate | date | нет | CURRENT_DATE |
Первичный ключ - title_id. Внешний ключ pub_id ссылается на первичный ключ pub_id таблицы publishers. Денежные значения в современной версии практикума представлены типом numeric(10,2); в наших примерах столбцы price и advance использоваться не будут.
Содержимое базы данных можно посмотреть в прилагаемом тексте, а также с помощью стандартных средств просмотра схемы и данных в выбранном клиентском приложении.
Слушатели будут работать с базой данных pubs со своих рабочих мест. Для каждого слушателя преподаватель создаст отдельную учетную запись либо выдаст общие параметры подключения с ограниченным набором прав. В любом случае не следует выполнять действия, не предусмотренные программой занятий, без согласования с преподавателем.
Для доступа к базе данных можно использовать любой удобный SQL-клиент, например psql, pgAdmin или DBeaver. Правила подключения к учебной базе pubs и выполнения первых запросов будут продемонстрированы преподавателем.
Занятие 2. Определение и изменение схемы базы данных
5.4. Создание таблицы
Каждый слушатель должен будет создать одну таблицу с именем my_libn (моя библиотека), где n - номер рабочего места или иной выданный преподавателем индивидуальный номер. Таблица должна включать следующие столбцы: book_id (порядковый номер книги в библиотеке), au_id (идентификатор автора), title_id (идентификатор издания), book_val (ваша оценка стоимости книги), pen_val (ваша оценка величины штрафа, который следует изъять с возможного похитителя книги), book_desc (краткая словесная характеристика книги).
Тип данных столбцов book_id, book_val, pen_val - целые числа; тип данных столбца book_desc - строка символов переменной длины с максимальной длиной 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_libn, и 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. Запросы к одной таблице с использованием агрегатных функций и раздела GROUP BY
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, titleauthor и titles).
6.6. Выдать имена и фамилии авторов книг, относящихся к категории 'business' и написанных в соавторстве, а также идентификаторы этих книг (использовать таблицы authors, titleauthor и titles).
6.7. Выдать имена и фамилии авторов, проживающих не в Калифорнии, книги которых заказываются в магазинах, расположенных не в том штате, где проживает автор, а также получить названия обоих штатов (использовать таблицы authors, stores, titleauthor и sales).
Занятие 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'. Почему эти операции не выполняются?