Лабораторная работа № 3. Основы структурированного языка запросов (SQL)
Цель работы: получить практический опыт написания SQL запросов.
Задачи:
· изучить предложенную структуру БД;
· написать 5 запросов, демонстрирующих знание SQL.
Требования:
· СУБД – Oracle;
· инструментарий PL/SQL Developer, SQL Plus;
· из 5 запросов SQL: должны иметь подзапросы не менее 2 запросов, с функциями агрегирования не менее 2 запросов.
Варианты задания по лабораторной работе № 3
Структура таблиц
ЛАБОРАТОРИИ
(Код лаборатории: Текстовый,
Наименование лаборатории: Текстовый,
Код руководителя: Текстовый,
Дата организации лаборатории: Дата,
Дата закрытия лаборатории: Дата)
СПЕЦИАЛЬНОСТИ
(Код специальности: Текстовый,
Наименование специальности: Текстовый
Дата открытия специальности: Дата,
Дата закрытия специальности: Дата)
СПИСОК СЛУЖАЩИХ
(Табельный номер: Текстовый,
Фамилия: Текстовый,
Имя: Текстовый,
Отчество: Текстовый,
Пол: Текстовый (возможные значения М, Ж),
Семейное положение: (возможные значения Ж, Х, Р, З),
Код лаборатории: Текстовый,
Телефон: Текстовый,
Код специальности: Текстовый,
Оклад: Числовой,
День рождения: Дата,
Адрес: Текстовый,
Характеристика: Текстовый)
ПРЕМИИ
(Табельный номер: Текстовый,
Размер премии: Числовой,
Номер приказа: Текстовый,
Дата приказа: Дата)
ДЕТИ СОТРУДНИКОВ
(Табельный номер: Текстовый,
Фамилия ребенка: Текстовый,
Имя ребенка: Текстовый,
Дата рождения:Дата)
Варианты возможных SQL-запросов
1. Список сотрудников, работающих в действующей лаборатории с минимальным размером фонда заработной платы по лаборатории.
2. Список всех служащих с максимального для сотрудника размера премии, если служащий не получал премий, то значение NULL.
3. Список руководителей действующих лабораторий с указанием числа служащих в лабораториях
4. Список сотрудников, работающих в действующих лабораториях, где число служащих превышает 10 человек.
5. Список сотрудников, работающих по специальностям, по которым число служащих не превышает 5 человек.
6. Список сотрудников, имеющих максимальный общий объем премий.
7. Создать запрос, позволяющий получить следующую информацию о сотруднике: ФИО, Дата рождения, Оклад, Надбавка (для родившихся до 1950 г. – 20% от оклада, после – 15% оклада). Данные упорядочить по полю Фамилия.
8. Список всех служащих с указанием количества детей, если служащий не имеет детей, то количество детей NULL.
9. Список сотрудников, работающих в действующей лаборатории, в которой наибольший размер средней заработной платы по лаборатории в целом.
10. Список руководителей лабораторий с указанием количества детей для каждого, если детей нет, то выводить NULL.
11. Список всех служащих с указанием размеров премий, получаемых ими, если служащий не получал премию ни разу, то размер его премии указать как NULL.
12. Список сотрудников, получающих оклад больше среднего по организации в целом.
13. Список лаборатории с указанием количества служащих в каждой.
14. Список действующих лабораторий с указанием объема премии, полученной каждой лабораторией.
15. Список руководителей лабораторий с указанием лаборатории.
16. Список лабораторий с указанием средней, максимальной и минимальной заработной платы по каждой лаборатории.
17. Найти самого молодого руководителя действующей лаборатории.
18. Найти самого молодого сотрудника, имеющего детей.
19. Найти сотрудника с максимальным объемом премии.
20. Список детей, у которых родители получают заработную плату ниже среднего по организации в целом.
21. Список сотрудников ни разу не получавших премии.
22. Список сотрудников имеющих более 3 детей и получающих заработную плату ниже среднего по организации в целом.
23. Создать запрос, позволяющий получить следующую информацию по детям: ФИО ребенка, Дата рождения, ФИО одного из родителей. Информацию выводить по детям, родившимся с 1990 по 2006 года. Данные упорядочить по полю Фамилии родителя.
24. Список разведенных служащих с указанием количества детей.
25. Список служащих с указанием суммарного размера премии сотрудника, полученного им за весь период работы, и отклонения суммарного размера премии сотрудника от максимального суммарного размера премии для сотрудников по организации в целом.
Методические рекомендации
по выполнению лабораторной работы № 1