Задания для практических занятий

Практическое задание 1. Выборка строк

1. Дайте ответы на следующие вопросы:
1. Команды SQL всегда хранятся в буфере? (Да/Нет).
2. Команды SQL*Plus помогают запрашивать данные? (Да/Нет).
2. Покажите структуру таблицы S_DEPT.  Выберите всю информацию из таблицы S_DEPT.
3. Покажите структуру таблицы S_CUSTOMER.  Выполните с этой таблицей следующие действия:
1. Получите всю информацию из таблицы S_CUSTOMER.
2. Получите список названий и номеров телефона всех фирм-клиентов.
3. Получите список названий и номеров телефона всех фирм-клиентов; номер телефона должен быть в строке первым.

Практическое задание 2. Использование в запросах однострочных функций

1. Дайте ответы на следующие вопросы:
1. Однострочные функции обрабатывают множество строк для получения единственного результата? (Да/Нет).
2. К значениям дат можно применять любые арифметические операторы? (Да/Нет).
3. Как называется функция, возвращаемая текущую дату?
2. Выведите номер служащего, его фамилию и заработную плату, повышенную на 15 % и округленную до целого.
3. Выведите фамилию каждого служащего и его должность в скобках.
4. Для каждого служащего выведите фамилию, дату найма и дату пересмотра зарплаты, которая приходится на первый понедельник после шести месяцев работы. Формат даты на выводе: «день.месяц.год», например 12.01.2012.
5. Выведите все наименования товаров, включающие слово “ski”.
6. Для каждого служащего вычислите количество месяцев со дня начала работы до настоящего времени. Результаты отсортируйте по количеству отработанных месяцев. Количество месяцев округлите до ближайшего целого.
7. (*) Выведите фамилию каждого служащего и день недели, когда он был нанят на работу. Результаты отсортируйте по дням недели, начиная с понедельника.
8. (*) Составьте запрос для получения следующей информации по каждому служащему: <имя служащего> зарабатывает <зарплата> в месяц, но желает <утроенная зарплата>.  Например: ALLEN зарабатывает 1100 в месяц, но желает 3300.

Практическое задание 3. Ограничение количества выбираемых строк

1. Дайте ответы на следующие вопросы:
1. Сортировка по столбцу, который не был выбран, возможна? (Да/Нет).
2. Следующая команда SELECT будет успешно выполнена? (Да/Нет). 

   

3. Следующая команда SELECT будет успешно выполнена? (Да/Нет).

   

4.  Сколько ошибок содержит следующая команда SELECT?  Укажите их. 

   

2. Выполните следующие действия, пользуясь таблицей S_CUSTOMER.
1. Создайте запрос для вывода названия, номера и кредитного рейтинга всех фирм-клиентов, имеющих торгового представителя под номером 11. Сохраните команду SQL в файле.
2. Выполните запрос из файла.
3. Измените команду присвоив столбцам заголовки Company, Company ID, Rating. Выполните запрос еще раз. Формат вывода показан ниже. Сохраните отредактированный запрос в файле.

Company	Company ID	Rating
Womansport	204	GOOD
Beisbol Si!	209	GOOD
Big John’s Sports Emporium	213	GOOD
Ojibway Retail	214	GOOD

4. Загрузите файл в буфер SQL.  Отсортируйте результат запроса в порядке убывания номеров клиентов. Выполните запрос.
3. Выполните следующие упражнения с таблицей S_EMP.
1. Покажите структуру таблицы.
2. Получите имя пользователя для сотрудника с номером 23.
3. Получите список имен, фамилий и номеров отделов для служащих отделов 10 и 50. Отсортируйте список по фамилиям в алфавитном порядке. Объедините имя с фамилией и назовите столбец “Employees”.
4. Получите информацию по всем служащим, в фамилии которых имеется буква “s”.
5. Выведете имя пользователя и дату начала работы всех служащих, нанятых между 14 мая 1990 года и 26 мая 1991 года. Результаты запроса отсортируйте по убыванию дат начала работы.
6. (*) Напишите запрос для вывода фамилий и заработной платы всех служащих, месячный заработок которых не лежит в интервале от 1000 до 2500.
7. (*) Получите список фамилий и заработной платы всех служащих отделов 31, 42, и 50, зарабатывающих более 1350. Назовите столбец “Employee Name”, а столбец заработной платы – “MONTHLY SALARY”.
8. Получите список фамилий и дат найма всех служащих, пришедших в 1991г.
9. (*) Получите список имен и фамилий всех служащих, не имеющих менеджера.
4. (*) Выполните упражнения, используя таблицу S_PRODUCT.
1. Покажите структуру таблицы.
2. Перечислите в алфавитном порядке все товары, названия которых начинаются с “Pro”.
3. Выведите названия и краткие описания всех продуктов, в описании которых содержится слово “bicycle”.
4. Выведите все краткие описания. Сравните с результатом предыдущего упражнения.  Содержал ли ответ, все описания со словом “bicycle”?

Практическое задание 4. Использование в запросе групповых функций

1. Определите истинность следующих утверждений.
1. Групповые функции обрабатывают большое количество строк для получения одного результата? (Да/Нет).
2. Во время вычисления групповых функций учитываются неопределенные значения? (Да/Нет).
3. Предложение HAVING используется для исключения строк из расчета для группы? (Да/Нет).
4. Предложение HAVING используется для исключения групп из выходных результатов? (Да/Нет).
2. Выведите наибольшую и наименьшую общую сумму заказа из таблицы S_ORD.
3. Составьте запрос для вывода минимальной и максимальной заработной платы по всем должностям в алфавитном порядке.
4. Определите количество менеджеров без вывода информации о них.
5. Выведите номер каждого заказа и количество позиций в нем. Столбец с количеством позиций озаглавьте “Number of Items”.
6. Выведите номер каждого менеджера и заработную плату самого низкооплачиваемого из его подчиненных. Исключите группы с минимальной заработной платой менее 1000. Отсортируйте результаты по размеру заработной платы.
7. Какова разница между самой высокой и самой низкой заработной платой?
8. (*) Для каждого вида товара, заказанного, по крайней мере, три раза, выведите номер этого товара и количество заказов на него. Столбец с количеством заказов на товар озаглавьте “Times Ordered”. Отсортируйте данные по номерам заказанных товаров.
9. (*) Получите список номеров и названий всех регионов с указанием количества отделов в каждом регионе.
10. (*) Для каждого заказа с общим количеством заказанных товаров 100 или более выведите номер заказа и общее количество заказанных товаров в нем. (Если, например, заказ номер 99 содержит заказ на один товар в количестве 30, а на другой – в количестве 75, то общее количество заказанных товаров равно 105).
11. (*) Выведите наименование каждого клиента и количество сделанных им заказов.

Практическое задание 5. Выборка данных из нескольких таблиц

Примечание: Для выполнения следующих упражнений используются таблицы S_EMP, S_DEPT, S_CUSTOMER, S_REGION, S_ORD, S_ITEM, S_PRODUCT.

1. Напишите отчет, содержащий фамилию, номер отдела и название отдела для каждого служащего.
2. Составьте запрос для вывода фамилии, названия отдела и названия региона для всех служащих, получающих комиссионные.
3. Покажите фамилию и название отдела для сотрудника по фамилии “Smith”.
4. Выведите наименование товара, номера товара и заказанное количество по всем позициям заказа номер 106.
5. Выведите номер каждого клиента и фамилию его торгового представителя. Отсортируйте список по фамилиям.
6. (*) Для всех заказчиков и всех их заказов выведите номер заказчика, его наименование и номер заказа. Даже если клиент не делал заказ, его номер и наименование должны быть включены в список.  Примерный вид отчета:

Customer ID	Customer Name	Order ID
201	Unisports	97
202	Simms Atheletics	98
…		…
207	Sweet Rock Sports

7. (*) Выведите фамилии и номера всех служащих вместе с фамилиями и номерами их менеджеров.
8. (*) Для каждого заказчика, общая сумма заказа которого превышает 100 000, выведите наименование заказчика, заказанные им товары, их количество.

Практическое задание 6. Подзапросы

1. Ответьте на следующие вопросы:
1. a.     Если используется подзапрос, то какой запрос выполняется первым?
2. b.     Сколько раз выполняется первый запрос?
3. c.      Если подзапрос возвращает более одного значения, то нельзя использовать оператор (=). (Да/Нет).  Если “Да”, то почему и какой оператор следует использовать?  Если “Нет”, то почему?

Примечание. В следующих упражнениях используются таблицы S_EMP, S_DEPT, S_ORD, S_ITEM, S_PRODUCT.

2. Выведите имя, фамилию и дату начала работы всех служащих, работающих в одном отделе с “Magee”.
3. Выведите номер служащего, имя, фамилию и имя пользователя для всех служащих, заработная плата которых выше средней.
4. Выведите фамилию, номер отдела и должность всех служащих, относящихся к регионам 1 или 2.
5. Выведите фамилию и заработную плату всех подчиненных “LaDoris Ngao”.
6. Выведите номер, имя и фамилию каждого служащего, который получает заработную плату выше средней и работает в одном отделе с любым сотрудником, фамилия которого содержит букву “t”.
7. Выведите номер и наименование клиента, а также кредитный рейтинг и фамилию торгового представителя для всех клиентов, которые расположены в Северной Америке или чьим торговым представителем является “Nguyen”.
8. Выведите наименование и краткое описание каждого товара, который ни разу не был заказан в сентябре 1992 г.
9. (*) Выведите наименование и кредитный рейтинг всех клиентов, чьим торговым представителем является “Andre Dumas”.  Что получилось и почему?
10. (*) Выведите фамилию каждого торгового представителя в регионах 1 и 2, наименования их клиентов и итоговые суммы заказов каждого клиента.

Практическое задание 7. Создание таблиц

1. Правилен ли синтаксис в следующих примерах? Если нет, то почему?
1.      



2.       



2. Создайте две таблицы на основе следующих бланков экземпляра таблицы. Введите команды в командный файл “test1.sql”. Затем выполните этот файл, чтобы создать таблицы. Убедитесь в том, что таблицы созданы.

Имя таблицы: DEPARTMENT

Имя таблицы:EMPLOYEE

Практическое задание 8. Изменение таблиц и ограничений

1. Создайте таблицу WORKER как копию таблицы EMPLOYEE. Просмотрите описание таблицы для проверки ее структуры.
2. Просмотрите ограничения для этой таблицы. Сохраните эту команду в файле “test2.sql”. Запишите типы и имена ограничений.
3. Сравните эти ограничения с ограничениями для таблицы EMPLOYEE. Запишите типы и имена ограничений.
4. Добавьте ограничение PRIMARY KEY из таблицы WORKER, используя столбец ID. Ограничение должно вступить в силу немедленно.
5. Добавьте ограничение типа FOREIGN KEY из таблицы DEPARTMENT на столбец DEPT_ID таблицы WORKER. Убедитесь в том, что ограничения добавлены, выполнив еще раз файл “test2.sql”.
6. Просмотрите имена и типы объектов с помощью представления USER_OBJECTS словаря данных. Для удобства просмотра данных может потребоваться изменение формата столбцов. Обратите внимание на то, что создана новая таблица и новые индексы.

Практическое задание 9. Управление транзакциями

1. 1.      Управление транзакциями с данными на примере таблиц DEPARTMENT и EMPLOYEE.
1. Создайте командный файл “test_add.sql”, добавляющий в таблицу DEPARTMENT следующие строки: отдел “Marketing” (номер 37), отдел “Sales” (номер 54) и отдел “Personnel” (номер 75)).
2. Выполните файл “test_add.sql” для восстановления отдела Sales как отдела номер 54.
3. Проверьте правильность внесенных изменений.
4. Создайте точку сохранения в транзакции.
5. Удалите все данные из таблицы EMPLOYEE.
6. Убедитесь в том, что таблица EMPLOYEE действительно пуста.
7. Отмените результаты последней операции DELETE, сохранив результаты последней операции INSERT.
8. Убедитесь в том, что новая строка в таблице DEPARTMENT цела. Проверьте, содержит ли таблица EMPLOYEE все три строки.
9. Сделайте эти добавления данных постоянными.

Практическое задание 10. Создание последовательностей

1. Создайте последовательность DEPT_ID_SEQ для генерации первичного ключа таблицы DEPARTMENT. Первое число последовательности – 76, максимальное значение 80. Приращение должно быть равным единице.
2. Создайте еще одну последовательность WORKER_ID_SEQ. Она будет использоваться для столбца первичного ключа таблицы WORKER. Начните последовательность со значения 204 – максимальное значение 9999999. Проверьте, что числа увеличиваются на единицу. Задайте кэширование пяти чисел.
3. Напишите командный файл для вывода следующей информации о ваших последовательностях: размер кеша, максимальное значение, шаг приращения и последнее сгенерированное число. Назовите файл “test121.sql”.
4. Напишите интерактивный командный файл для вставки строки в таблицу DEPARTMENT. Назовите его “test122.sql”. Воспользуйтесь последовательностью, созданной вами для столбца ID. Создайте собственное приглашение на ввод названия отдела. Выполните свой файл. Добавьте два отдела – “Education” и “Administration”. Проверьте внесенные изменения.
5. Получите на экране информацию о своих последовательностях с помощью командного файла “test121.sql”. Обратите внимание на то, что последнее число последовательности WORKER_ID_SEQ отличается от самого большого значения первичного ключа в упражнении 2. Почему?
6. Напишите командный файл для вставки двух строк в таблицу WORKER.  Назовите его “test123.sql”. Используйте последовательность, созданную вами для столбца ID. Выполните файл. Добавьте служащего “Tomas Lira” в качестве президента в отдел, который вы только что внесли в таблицу. Второй новый служащий – “Anna Seigher”, вице-президент в отделе “Finance”.
7. Проверьте данные, добавленные в таблицы DEPARTMENT и WORKER. Запишите самые большие значения первичного ключа для каждой из таблиц.

Практическое задание 11. Создание представлений

1. На основе таблицы WORKER создайте представление EMP_VU, включающее номер служащего, фамилию и номер отдела.  Присвойте столбцу с фамилией заголовок EMPLOYEE.
2. Выведите на экран содержимое представления EMP_VU.
3. Напишите скрипт-файл для вывода на экран определения представления.  Передайте скрипт-файлу имя представления.  Сохраните файл под именем “test131.sql”.  Выполните его для вывода определения EMP_VU.
4. В представлении EMP_VU для служащего с фамилией “Smith” смените номер отдела на 37.
5. Проверьте, что “Smith” теперь приписан к отделу 37.
6. На основе таблиц DEPARTMENT и WORKER создайте представление MNS_VU для вывода данных о всех служащих отделов маркетинга и продаж. Выходные данные должны включать номер служащего, полное имя и номер отдела. Сохраните команду в скрипт-файле “test132.sql”.
7. Выведете структуру и содержимое представления MNS_VU.
8. Выведите на экран определение представления MNS_VU, выполнив скрипт-файле “test131.sql”.
9. Выведите на экран название каждого отдела и количество служащих в нем.
10. Измените представление EMP_VU так, чтобы оно содержало данные только о служащих отдела 37. Добавьте ограничение, запрещающее изменять номер отдела.
11. Выведите содержимое представления EMP_VU.
12. В представлении EMP_VU верните служащего с фамилией Smith номер отдела 54. Получилось или нет? Почему?

Практическое задание 12. Создание индексов

1. Могут ли какие-либо из перечисленных индексов использоваться с указанными запросами и почему?
1. Неуникальный индекс по столбцу LAST_NAME. (Да/Нет).



2. Уникальный индекс по столбцу ID и неуникальный индекс по столбцу CUSTOMER_ID. (Да/Нет).



3. Уникальный индекс по столбцу S_DEPT.ID и неуникальный индекс по столбцу S_EMP.DEPT_ID. (Да/Нет).



2. Создайте неуникальный индекс по столбцу внешнего ключа в таблице WORKER.
3. Так как пользователи часто запрашивают данные по фамилии служащих, создайте неуникальный индекс для этого столбца таблицы WORKER.
4. Выведите из словаря данных индексы и информацию об уникальности для таблиц WORKER и DEPARTMENT.
5. Удалите ограничения PRIMARY KEY для таблицы WORKER.
6. Еще раз выведите из словаря данных индексы и информацию об уникальности для таблиц WORKER и DEPARTMENT. Что изменилось и почему?
7. Вновь создайте ограничение PRIMARY KEY для таблицы WORKER.  Убедитесь в том, что ограничение присутствует в словаре данных.  Убедитесь в наличии уникального индекса по словарю данных.
8. Удалите индекс по столбцу фамилий служащих из таблицы WORKER.