Астахова И.Ф., Толстобров А.П., Мельников В.М. SQL в примерах и задачах
Подождите немного. Документ загружается.
141
Предметный указатель
DBA, 126
DDL, 4, 77
DML, 5
escape-символ, 20, 21
SQL
встроенный, 4
интерактивный, 4
администратор базы данных, 126
база данных учебная, 10
таблица EXAM_MARKS, 12
таблица LECTURER, 10
таблица STUDENT, 10
таблица SUBJ_LECT, 12
таблица SUBJECT, 11
таблица UNIVERSITY, 11
вставка
столбца , 79
строк, 68, 72
декартово произведение, 58
изменение таблицы, 79, 83
индексация, 78
создание индекса , 79
удаление индекса , 79
использование символа *, 14
ключ
внешний (FOREIGN KEY), 3, 60, 84, 91, 92,
93, 94, 95, 98, 100, 101
возможный, 84
первичный (PRIMERY KEY), 2, 83, 85, 86,
90, 96, 97
родительский, 91, 92, 93, 94, 97, 98
уникальный, 84
ключевые слова, 9
команды, 9
ALTER TABLE, 79, 80, 83, 93, 94, 95
ADD, 79
MODIFY, 80
добавление столбца , 79
изменение описания столбцов , 80
синтаксис, 79, 85, 95
COMMIT, 131
CREATE INDEX, 79
CREATE TABLE, 68, 77, 82, 88, 93
синтаксис, 85
CREATE USER, 118, 127
CREATE VIEW, 102
DELETE, 68, 69, 73, 75, 98, 105, 123
ограничение удаления родительского
ключа
CASCADE, 99
NO ACTION, 99
RESTRICT, 99
SET DEFAULT, 99
SET NULL, 99
DROP INDEX, 79
DROP TABLE, 80
DROP USER, 118
DROP VIEW, 112
GRANT, 118, 120, 122, 126
INSERT, 68, 69, 72, 77, 83, 98, 106, 123
VALUES, 68, 72
вставить NULL-значение, 68
REVOKE, 118, 122
ROLLBACK, 131
SELECT, 13, 78, 112
аргументы
ALL, 34
DISTINCT, 3, 15, 34
использование символа *, 14
оператор
JOIN, 59, 60
оператор объединения таблиц
UNION, 13
внешнее объединение, 57
предложения
FROM, 13
FROM:, 13
GROUP BY, 34
GROUP BY, 13
HAVING, 13, 35, 42, 44
ORDER BY, 13, 39, 40, 56
ASC, 39
DESC, 39
WHERE, 13, 16, 19, 70, 72
синтаксис, 13
UPDATE, 68, 70, 75, 98, 104
ограничение модификации
родительского ключа
CASCADE, 99
NO ACTION, 99
RESTRICT, 99
SET NULL, 99
предложение
SET, 70, 71, 116
синтаксис, 70
манипулирование данными , 68
оператор соединения таблиц
JOIN, 58, 61, 62, 63
CROSS, 58
FULL OUTER JOIN, 63
INNER, 59, 61
LEFT OUTER JOIN, 62
RIGHT OUTER JOIN, 62
UNION JOIN, 63
логика трехзначная, 8, 37
манипулирование данными , 68
142
модель данных, 1
обновление, 70
обозначения при описании синтаксиса
команд , 9
ограничения, 81, 85
ALTER TABLE, 93
CHECK, 87, 125, 129
CONSTRAINT, 81, 85
CREATE TABLE, 93
DEFAULT, 83, 88, 89
DELETE, 98
FOREIGN KEY, 93, 94, 95, 98
INSERT, 98
NOT NULL, 81, 82
PRIMARY KEY, 86, 90, 96, 97
UNIQUE, 83, 84, 85, 90
UPDATE, 98
WITH CHECK OPTION, 116, 124, 125, 129
альтернативы для NULL, 89
в командах
ALTER TABLE, 83, 85
CREATE TABLE, 77, 82, 85
INSERT, 83
ввод значений поля, 93
значения по умолчанию, 88
ключ
внешний (FOREIGN KEY), 93, 94, 95, 98
первичный (PRIMERY KEY), 81, 83, 85,
86, 90, 96, 97
родительский, 91, 92, 93, 94, 97, 98, 99
модификация, 98
составной, 86, 92
модификация значений поля, 93
присвоение имен , 85
проверка значений полей , 87
ссылочная целостность, 91, 94, 97, 98
столбца , 81, 95
таблицы, 81, 82, 84, 86, 94, 95
удаление значений поля, 93
удаления и модификации родительского
ключа ON DELETE и ON UPDATE
CASCADE, 99
NO ACTION:, 99
RESTRICT, 99
SET DEFAULT, 99
SET NULL, 99
уникальность, 83, 84, 85, 90
операторы
– (вычитание), 23
* (умножение), 23
/ (деление), 23
|| (конкатенация строк), 23
+ (сложение), 23
ALL, 50
ANY, 50
BETWEEN, 19, 20, 21, 117
COUNT, 52
EXISTS, 46, 50, 52
IN, 19, 42, 43, 48, 49, 73, 74, 75, 76, 115, 125
IS NOT NULL, 8
IS NULL, 8
LIKE, 19, 20, 21
NOT IN, 19, 48
UNION, 54, 56
конкатенация строк, 8
сравнение, 2, 16, 89
отмена привилегий, 122
отношение, 1
атрибут, 1
домен , 2
заголовок, 1
кардинальное число, 2
ключ
внешний (FOREIGN KEY), 3
первичный (PRIMERY KEY), 2
кортеж , 1
свойства, 2
степень, 2
пароль, 118, 127
IDENTIFIED BY, 118, 127
подзапросы, 73
в командах
DELETE, 73
UPDATE, 75
в предложениях
FROM, 74
HAVING, 42
в представлениях , 111
вложенные, 41
связанные, 43, 46
в предложении HAVING, 44
пользователи, 118
создание, 118, 127
удаление, 118
права доступа, см. привилегии, 118
представление (VIEW), 102, 104
агрегированное, 109
вставка строки , 108
других представлений, 116
защита данных, 114
использование
UNION и UNION ALL, 112
использование команды
DELETE, 105
GROUP BY, 109
INSERT, 68, 108, 123
маскирующее, 105
столбцы, 105, 108
модифицирование, 105
строки , 106
143
модифицирование, 106, 108
многих таблиц, 110
модифицирование, 70, 75, 104
использование
DISTINCT, 113
GROUP BY, 113
HAVING, 113
подзапросы, 113
модифицирование значений, 113
не обновляемое, 113, 114
обновляемое, 113, 122
ограничение использования SELECT, 112
подзапросы, 111
создание, 102
столбцов , 104
таблиц, 103
удаление, 112
префикс, 129
привилегии, 118, 120, 125
аргументы
ALL, 121
ALL PRIVILEGES, 121
PUBLIC, 121
базы данных, 126
в базовых таблицах, 121
в представлениях , 121
виды, 119
ALTER, 119
DELETE, 119, 122
EXECUTE, 119
INDEX, 119
INSERT, 119, 122
REFERENCES, 119, 121, 123
SELECT, 119, 120, 123, 124
SYNONYM, 119
UPDATE, 119, 122, 123
виды привилегий, 118
использование представлений, 122
ограничение для строк, 123
отмена, 118
регистрации, 126
системы , 126
CONNECT, 126, 127
DBA, 126, 127
RESOURCE, 126, 127
Администратор Базы Данных, 126
ПОДКЛЮЧИТЬ, 126
РЕСУРС, 126
создавать
базовые таблицы, 126
представления, 126
синонимы , 126
установка , 118, 120, 126
фильтрация, 122
псевдонимы , 64
реляционная модель данных, 1
сбои, 130
символьные константы , 22
синонимы , 128
CREATE SYNONYM, 128
DROP SYNONYM, 130
общего пользования (PUBLIC), 130
создание, 128
удаление, 130
соединение, 60
внешнее, 61, 62
левое, 62
полное, 63
правое, 62
синтаксис ORACLE, 62
внутреннее (INNER), 59, 61
использование псевдонимов , 64
полное (CROSS), 58
эквисоединение, 59
создание
индексов , 79
объектов базы данных, 77
пользователей , 118
представлений, 102
синонимов , 128, 129
таблиц базы данных, 77
сравнение, 2, 16, 89
ссылочная целостность, 3, 60, 91, 92, 93,
94, 95, 97, 98, 99, 100, 101
стандарты ANSI, 77
столбец
добавление, 79
изменение описания, 80
строка
вставка , 68
идентификаторы строк ROWID, 78
удаление, 69, 75, 123
суперпользователь, 126
таблица , 1
базовая, 102
виртуальная, 104, 110
изменение, 79, 83
именованная, 102
родительская, 94
удаление, 80
типы данных, 5, 20, 21, 77
дата и время, 7
пропущенные данные (NULL), 8, 36, 50, 68,
71, 81, 82, 85, 89
строка символов
CHAR, 5, 77
CHARVARYING, 6
CHARACTER, 5, 77
CHARACTER VARYING, 6
144
VARCHAR, 6
числовые типы, 6
DECIMAL, 6, 77
DOUBLE PRECISION, 7
FLOAT, 7, 77
INTEGER, 6, 77
NUMBER, 7
NUMERIC, 7, 77
REAL, 7
SMALLINT, 77
SMOLLINT, 6
транзакция, 130
AUTOCOMMIT, 131
завершение, 131
COMMIT, 131
ROLLBACK, 131
нормальное, 131
откат, 131
удаление
индексов , 79
пользователей , 118
представлений, 112
синонимов , 130
строк, 69, 75, 123
таблиц базы данных, 69, 80
функции
агрегирующие, 33
AVG, 33, 37
COUNT, 33, 36
COUNT(*), 34
MAX, 33
MIN, 33
SUM, 33
встроенные, 22
ABS, 27
CAST, 31
CEIL, 27
COS, 28
COSH, 28
EXP, 28
FLOOR, 27
INITCAP, 24
INSTR, 26
LENGTH, 26
LOWER, 24
LPAD, 24
LTRIM, 25
POWER, 28
ROUND, 27
RPAD, 25
RTRIM, 25
SIGN, 28
SIN, 28
SINH, 28
SQRT, 28
SUBSTR, 25
TAN, 28
TANH, 28
TO_CHAR, 29
TO_DATE, 30
TO_NUMBER, 30
TRUNC, 27
UPPER, 24
преобразование букв , 24
работы с числами , 27
символьные строковые, 24
целостность данных, 131
эквисоединение, 59
язык
обработки данных (DML), 5
определения данных (DDL), 4, 77
145
Приложение 1. Задачи по проектированию БД
В приложении 2 приводятся тексты задач по проектированию баз
данных, относящихся к различным предметным областям. Требуется в
соответствии с условиями задач :
– сформировать структуру таблиц баз данных,
– подобрать подходящие имена таблицам и их полям,
– обеспечить требования нормализации таблиц баз данных (то есть
приведение к пятой нормальной форме),
– сформировать SQL запросы для создания таблиц баз данных с
указанием первичных и внешних ключей и необходимых
ограничений, SQL запросы для добавления, изменения и выборки
необходимых данных.
При решении задач предполагается использование средств ,
позволяющих разрабатывать схемы баз данных, и приложений, работающих
с базами данных (Power Designer, Oracle Developer, ERWin, Power Builder,
Borland Delphi, C++ Builder, и др.)
Задача 1. Летопись острова Санта Белинда
Где-то в великом океане лежит воображаемый остров Санта
Белинда. Вот уже триста лет ведется подробная летопись острова.
В летопись заносятся и данные обо всех людях , хоть какое-то
время проживавших на острове. Записываются их имена, пол, даты
рождения и смерти . Хранятся там и имена их родителей , если
известно, кто они. У некоторых отсутствуют сведения об отце, у
некоторых – о матери, а часть людей , судя по записям, – круглые
сироты . Из летописи можно узнать, когда был построен каждый
дом, стоящий на острове (а если сейчас его уже нет , то когда он
был снесен ), точный адрес и подробный план этого дома , кто и
когда в нем жил.
Точно так же, как и столетия назад, на острове действуют
предприниматели, занимающиеся, в частности, ловлей рыбы,
заготовкой сахарного тростника и табака . Большинство из них все
делают сами , а некоторые нанимают работников , заключая с ними
146
контракты разной продолжительности . Имеются записи и о том,
кто кого нанимал, на какую работу, когда начался и закончился
контракт. Собственно, круг занятий жителей острова крайне
невелик и не меняется веками . Неудивительно поэтому , что в
летописи подробно описывается каждое дело, будь то рыбная
ловля или выпечка хлеба. Все предприниматели – уроженцы
острова. Некоторые объединяются в кооперативы, и по записям
можно установить, кто участвовал в деле, когда вступил и когда
вышел из него , каким паем владел . Имеются краткие описания
деятельности каждого предпринимателя или кооператива,
сообщающие, в том числе, когда было начато дело, когда и почему
прекращено.
Предлагается сформировать систему нормализованных таблиц, в
которых можно было бы хранить всю эту многообразную
информацию. Подыщите выразительные имена для таблиц и
полей , снабдив их при необходимости соответствующими
пояснениями .
Задача 2. База данных “Скачки”.
В информационной системе клуба любителей скачек должна быть
представлена информация об участвующих в скачках лошадях
( кличка , пол, возраст), их владельцах (имя, адрес , телефон) и
жокеях (имя, адрес , возраст, рейтинг). Необходимо сформировать
таблицы для хранения информации по каждому состязанию: дата ,
время и место проведения скачек (ипподром), название состязаний
(если таковое имеется), номера заездов , клички участвующих в
заездах лошадей и имена жокеев , занятые ими места и показанное
в заезде время.
Задача 3. База данных «Хроники восхождений» в альпинистском клубе .
В базе данных должны записываться даты начала и завершения
каждого восхождения, имена и адреса участвовавших в нем
альпинистов , название и высота горы, страна и район, где эта гора
расположена. Присвойте выразительные имена таблицам и полям
для хранения указанной информации. Написать запросы,
осуществляющие следующие операции:
147
1) Для введенного пользователем интервала дат показать список гор
с указанием даты последнего восхождения. Для каждой горы
сформировать в хронологическом порядке список групп,
осуществлявших восхождение.
2) Предоставить возможность добавления новой вершины с
указанием ее названия, высоты и страны местоположения.
3) Предоставить возможность изменения данных о вершине, если на
нее не было восхождения.
4) Показать список альпинистов , осуществлявших восхождение в
указанный интервал дат. Для каждого альпиниста вывести список
гор, на которые он осуществлял восхождения в этот период , с
указанием названия группы и даты восхождения.
5) Предоставить возможность добавления нового альпиниста в
состав указанной группы.
6) Показать информацию о количестве восхождений каждого
альпиниста на каждую гору. При выводе список отсортировать по
количеству восхождений.
7) Показать список восхождений (групп), которые осуществлялись в
указанный пользователем период времени. Для каждой группы
показать ее состав.
8) Предоставить возможность добавления новой группы, указав ее
название, вершину, время начала восхождения.
9) Предоставить информацию о том, сколько альпинистов побывали
на каждой горе. Список отсортировать в алфавитном порядке по
названию вершин.
Задача 4. База данных медицинского кооператива .
Базу данных использует для работы коллектив врачей . В таблицы
должны быть занесены имя, пол, дата рождения и домашний адрес
каждого их пациента . Всякий раз, когда врач осматривает
больного (пришедшего на прием или на дому ), фиксируется дата
и место проведения осмотра, симптомы , диагноз и предписания
больному , проставляется имя пациента и имя врача. Если врач
прописывает больному какое-либо лекарство, в таблицу заносится
148
название лекарства, способ его приема , словесное описание
предполагаемого действия и возможных побочных эффектов .
Задача 5. База данных «Городская Дума».
В базе хранятся имена, адреса , домашние и служебные телефоны
всех членов Думы . В Думе работает порядка сорока комиссий, все
участники которых являются членами Думы . Каждая комиссия
имеет свой профиль, например , вопросы образования, проблемы ,
связанные с жильем, и так далее. Данные по каждой из комиссий
включают : председатель и состав, прежние (за 10 предыдущих лет )
председатели и члены этой комиссии, даты включения и выхода из
состава комиссии, избрания ее председателей . Члены Думы могут
заседать в нескольких комиссиях . В базу заносятся время и место
проведения каждого заседания комиссии с указанием депутатов и
служащих Думы , которые участвуют в его организации.
1) Показать список комиссий, для каждой – ее состав и председателя.
2) Предоставить возможность добавления нового члена комиссии.
3) Для введенного пользователем интервала дат и названия комиссии
показать в хронологическом порядке всех ее председателей .
4) Показать список членов Думы , для каждого из них – список
комиссий, в которых он участвовал и/или был председателем .
5) Предоставить возможность добавления новой комиссии, с
указанием председателя.
6) Для указанного интервала дат и комиссии выдать список членов с
указанием количества пропущенных заседаний.
7) Вывести список заседаний в указанный интервал дат в
хронологическом порядке, для каждого заседания – список
присутствующих.
8) Предоставить возможность добавления нового заседания, с
указанием присутствующих.
9) По каждой комиссии показать количество проведенных заседаний
в указанный период времени.
149
Задача 6. База данных рыболовной фирмы.
Фирме принадлежит небольшая флотилия рыболовных катеров .
Каждый катер имеет “паспорт”, куда занесены его название, тип ,
водоизмещение и дата постройки. Фирма регистрирует каждый
выход на лов , записывая название катера, имена и адреса членов
команды с указанием их должностей (капитан, боцман и т.д.), даты
выхода и возвращения, а также вес пойманной рыбы отдельно по
сортам (например , трески). За время одного рейса катер может
посетить несколько рыболовных мест (банок). Фиксируется дата
прихода на каждую банку и дата отплытия, качество выловленной
рыбы (отличное, хорошее, плохое). На борту улов не
взвешивается. Написать запросы, осуществляющие следующие
операции:
1) По указанному типу и интервалу дат вывести все катера,
осуществлявшие выход в море, указав для каждого в
хронологическом порядке записи о выходе в море и значением
улова.
2) Предоставить возможность добавления выхода катера в море с
указанием команды.
3) Для указанного интервала дат вывести для каждого сорта рыбы
список катеров с наибольшим уловом.
4) Для указанного интервала дат вывести список банок, с
указанием среднего улова за этот период . Для каждой банки
вывести список катеров , осуществлявших лов .
5) Предоставить возможность добавления новой банки с
указанием данных о ней .
6) Для заданной банки вывести список катеров , которые получили
улов выше среднего .
7) Вывести список сортов рыбы и для каждого сорта – список
рейсов с указанием даты выхода и возвращения, величины
улова. При этом список показанных рейсов должен быть
ограничен интервалом дат.
8) Для выбранного пользователем рейса и банки добавить данные
о сорте и количестве пойманной рыбы.
150
9) Предоставить возможность пользователю изменять
характеристики выбранного катера.
10) Для указанного интервала дат вывести в хронологическом
порядке список рейсов за этот период времени, с указанием для
каждого рейса пойманного количества каждого сорта рыбы.
11) Предоставить возможность добавления нового катера.
12) Для указанного сорта рыбы и банки вывести список рейсов с
указанием количества пойманной рыбы. Список должен быть
отсортирован в порядке уменьшения количества пойманной
рыбы.
Задача 7. База данных фирмы, проводящей аукционы .
Фирма занимается продажей с аукциона антикварных изделий и
произведений искусства. Владельцы вещей , выставляемых на
проводимых фирмой аукционах, юридически являются
продавцами . Лица , приобретающие эти вещи, именуются
покупателями . Получив от продавцов партию предметов , фирма
решает , на котором из аукционов выгоднее представить
конкретный предмет . Перед проведением очередного аукциона
каждой из выставляемых на нем вещей присваивается отдельный
номер лота . Две вещи, продаваемые на различных аукционах,
могут иметь одинаковые номера лотов .
В книгах фирмы делается запись о каждом аукционе. Там
отмечаются дата , место и время его проведения, а также
специфика (например , выставляются картины, написанные маслом
и не ранее 1900 г.). Заносятся также сведения о каждом
продаваемом предмете: аукцион, на который он заявлен , номер
лота , продавец , отправная цена и краткое словесное описание.
Продавцу разрешается выставлять любое количество вещей , а
покупатель имеет право приобретать любое количество вещей .
Одно и то же лицо или фирма может выступать и как продавец , и
как покупатель. После аукциона служащие фирмы , проводящей
аукционы, записывают фактическую цену, уплаченную за
проданный предмет , и фиксируют данные покупателя.