Лабораторные работы по курсу ИС
.pdfauthors нажмите кнопку Индексы на панели инструментов.
2.Если в первичный ключ должно входить несколько полей или необходимо создать индекс с определенным именем, то используется следующая инструкция:
CREATE TABLE <имя таблицы>
(<имя столбца1> тип столбца NOT NULL, <имя столбца2> тип столбца,
….
CONSTRAINT <имя ограничения> PRIMARY KEY(имя столбца1));
3.Создайте таблицу publishers со своим именем индекса по первичному ключу. Занесите в таблицу две строки, не нарушая установленных ограничений целостности. Попробуйте занести строки, содержимое которых противоречит определенным ограничениям. Проверьте, что занесенные строки действительно содержатся в таблице.
4.Если необходимо создать индекс по какому-либо другому полю (а не только по первичному ключу) таблицы, используется следующая инструкция:
CREATE UNIQUE INDEX <имя индекса>
ON <имя таблицы> (<имя столбца1>, < имя столбца2>, …);
5.Создайте индекс по таблице authors для упорядочивания записей в алфавитном порядке.
6.Для удаления индекса используется следующая инструкция:
DROP INDEX <имя индекса> ON <имя таблицы>;
Задание №4. Создание реляционных связей между таблицами
Наличие реляционной связи между таблицами подразумевает выбор значения поля одной таблицы (в этом случае называемой дочерней) строго из существующих значений какого-либо поля другой таблицы (в этом случае называемой родительской).
1.Создайте таблицу typetitles. Она будет являться родительской по отношению к таблице titles, поскольку в таблице titles поле typetitles надо выбирать из typetitles. Для создания реляционной связи используется следующая инструкция:
CREATE TABLE <имя дочерней таблицы>
(<имя столбца1> тип столбца NOT NULL, <имя столбца2> тип столбца,
….
CONSTRAINT <имя ограничения> FOREIGN KEY (<имя столбца дочерней таблицы>)
REFERENCES <имя родительской таблицы> (<имя столбца родительской
таблицы>);
2.Проследите все реляционные связи таблицы titles с ранее созданными таблицами.
Создайте таблицы titles и titleauthor.
3.Для добавления или удаления ограничения после создания таблицы используются следующие инструкции соответственно:
ALTER TABLE <имя таблицы>
ADD CONSTRAINT <имя ограничения> FOREIGN KEY (<имя столбца дочерней таблицы>)
REFERENCES <имя родительской таблицы> (<имя столбца родительской таблицы>);
ALTER TABLE <имя таблицы>
DROP CONSTRAINT <имя ограничения>;
Задание №5. Добавление, изменение, удаление записей таблицы
1. Для добавления записей:
INSERT INTO <имя таблицы> (<имя столбца1>, <имя столбца2>, … )
VALUES (<значение столбца1>,<значение столбца2>, …);
2.Занесите в typetitles и titles по две строки. Проверьте, что занесенные строки действительно содержатся в таблице.
3.Для изменения записей:
UPDATE <имя таблицы>
SET <имя столбца1>=<новое значение столбца1>
WHERE <имя столбца2>=<значение столбца2, определяющее одну запись или группу записей, которые должны быть изменены>;
Измените фамилию одного какого-либо автора в таблице authors.
4. Для удаления записей:
DELETE FROM <имя таблицы>
WHERE <имя столбца2>=<значение столбца2, определяющее одну запись или группу записей, которые должны быть удалены>;
Задание №6. Использование агрегатных функций и группировки
1.Предложение SELECT определяет, какие столбцы относятся к выходным данным. Может возвращать как сами значения этих столбцов, так и использовать их в составе агрегатных функций и других выражений.
Предложение FROM указывает имена исходных таблиц для запроса.
SELECT <имя столбца1>, <имя столбца2>, <имя столбца3>, …
FROM <имя таблицы>
Пример: показать название издательства, страну, город размещения издательства.
SELECT pubname, country, city
FROM publishers
2.Предложение WHERE определяет критерии отбора записей. Оно содержит одно или несколько логоческих выражений и принимает одно из трех значение: true, false, unknown. Выражения соединяются между собой с помощью булевых операторов AND, OR, NOT.
Пример: показать издательства, находящиеся в Москве.
SELECT pubname
FROM publishers
WHERE city = ‘Москва’
3.Оператор LIKE предназначен для сравнения строк, используется в предложении WHERE. В выражениях LIKE следующие символы имеют особое значение:
*- замещение любого количества символов
? - замещение одного символа
Пример: показать издательства, находящиеся в городах, названия которых начинаются с буквы Р.
SELECT pubname
FROM publishers
WHERE city like ‘Р*’
4.Предложение GROUP BY используется для определения группы выходных строк, к которым могут применяться агрегатные функции (COUNT – нахождение количества элементов группы, MIN – нахождение минимального значения в группе, AVG – нахождение среднего значения по группе, SUM – нахождение суммы значений столбца группы и т.д.)
Пример 1: выдать количество издательств в каждом городе.
SELECT city, count(id)
FROM publishers
GROUP BY city
Пример 2: выдать количество издательств в городах России, с указанием названия города.
SELECT city, count(id)
FROM publishers
WHERE country = ‘Россия’
GROUP BY city
5.Предложение ORDER BY используется для сортировки выходных данных. Строки сортируются в соответствии со значениями столбцов, указанных в списке. Первый столбец имеет наивысший приоритет, второй столбец задает порядок сортировки дублируемых значений первого столбца и т.д. Можно указать параметр сортировки ASC (по возрастанию, используется по умолчанию) и DECS (по убыванию) отдельно для каждого столбца.
Пример: показать издательства, находящиеся в Москве, в алфавитном порядке.
SELECT pubname
FROM publishers
WHERE city = ‘Москва’
ORDER BY pubname
6.Предложение HAVING определяет критерии отбора для групп записей, является аналогом предложения WHERE, которое определяет критерии отбора для индивидуальных строк. В запросе SQL предложение HAVING следует за предложением GROUP BY.
Пример: выдать список городов России, в которых более одного издательства с указанием количества издательств (Сравните с текстом запроса из Примера 2 пункта
6.3).
SELECT city, count(id)
FROM publishers
WHERE country = ‘Россия’
GROUP BY city
HAVING count(id) > 1
Задание №7. Выборка из одной таблицы
(1)Выдать список всех названий изданий и даты издания, отсортированных в порядке убывания даты издания (использовать таблицу titles, инструкцию select, предложение order by языка SQL).
(2)Выдать список всех авторов (фамилия, имя, отчество) в алфавитном порядке фамилий (использовать таблицу authors, инструкцию select, предложение order by языка
SQL).
(3)Выдать общее количество авторов, содержащихся в базе (использовать таблицу authors, инструкцию select, функцию count, предложение order by языка SQL).
(4)Выдать количество издательств по странам в алфавитном порядке названий стран (использовать таблицу publishers, инструкцию select, функцию count, инструкцию group by, предложение order by языка SQL).
(5)Выдать список всех публикаций, названия которых содержат словосочетание «английский язык» (использовать таблицу titles, инструкцию select, предложение where, оператор like языка SQL).
(6)Выдать список всех публикаций, изданных после 2000 года в Ростове. Список отсортировать по дате издания (использовать таблицу titles, инструкцию select, предложение where, предложение order by языка SQL).
(7)Выдать самую позднюю дату издания (использовать таблицу titles, инструкцию select, функцию max языка SQL).
Задание №8. Выборка из нескольких таблиц
(1)Выдать список всех названий изданий, которые изданы в России (использовать таблицы titles, publishers, инструкцию select, операцию inner join, предложение where языка SQL).
(2)Выдать имена и фамилии авторов книг, отнесенных к типам публикаций «статья», «сборник», «словарь» (использовать таблицы authors, titleauthor и titles, инструкцию select, операцию inner join, предложение where, оператор in языка SQL).
(3)Выдать список записей, содержащих название издания, дату издания, название издательства (использовать таблицы titles, publishers инструкцию select, операцию inner join языка SQL).
(4)Выдать список всех авторов, названий публикаций, отсортированный по ФИО авторов (использовать таблицы authors, titles, titleauthors инструкцию select, операцию inner join, предложение order by языка SQL).
(5)Выдать список авторов (указать ФИО) и количество их публикаций (использовать таблицы authors, titleauthors инструкцию select, операцию inner join, операторы count, group by языка SQL).
(6)Выдать список авторов (указать ФИО), у которых есть хотя бы одна публикация (использовать таблицы authors, titleauthors инструкцию select, операцию inner join,
операторы count, group by, having языка SQL).
(7)Выдать список всех авторов, названий публикаций, изданных в г.Москва
(использовать таблицы authors, titles, titleauthors, publishers инструкцию select,
операцию inner join, предложение where языка SQL).
(8)Выдать список всех авторов, названий публикаций, изданных до 2000 года
(использовать таблицы authors, titles, titleauthors, publishers инструкцию select,
операцию inner join, предложение where языка SQL).
(9)Выдать список публикаций заданного автора (использовать таблицы authors, titles, titleauthors инструкцию select, операцию inner join, предложение where языка SQL).
Контрольные вопросы
1.Укажите основные операторы DML и кратко расскажите об их назначении
2.Укажите основные операторы DDL и кратко расскажите об их назначении
3.Основные понятия: таблица, строка, столбец, поле, запись.
4.Расскажите об основных ограничениях, которые могут быть наложены на содержимое таблицы
5.Как добавить в таблицу одну запись?
6.Как добавить в таблицу группу записей?
7.Как удалить из таблицы одну запись?
8.Как удалить из таблицы группу записей?
9.Как изменить значение одного из полей в группе записей?
10.Какие агрегатные функции вам известны?
11.Что такое первичный ключ? Привести примеры.
12.Может ли быть в одной таблице два первичных ключа? Может ли первичный ключ состоять из нескольких полей?
13.Какие поля в таблице Titles могут составлять первичный ключ?
14.Какой запрос нужно выполнить, чтобы создать таблицу с первичным ключом?
15.Как объявить поле ключевым после того, как таблица создана?
16.Что такое внешний ключ? Привести примеры.
17.Как устанавливается реляционная связь между двумя таблицами? Привести примеры.
18.Что можно делать с помощью инструкции CREATE TABLE?
19.Перечислите виды запросов.
20.Для чего предназначены индексы?
21.Для чего предназначена инструкция ALTER TABLE?
22.Таблица «Сотрудники» является дочерней по отношению к таблице «Подразделения», может ли таблица «Сотрудники» быть родительской по отношению к какой-либо другой таблице?
23.Чем отличается оператор DELETE от оператора DROP TABLE?
24.Чем отличается таблица от запроса (VIEW, представления)?
25.Как удалить из таблицы titles публикации, относящиеся к данному типу?