Лабораторная работа №1. Изучение команды SELECT – простые запросы

Цель работы: ознакомиться с программным обеспечением, предназначенным для обращения пользователя к базе данных, приобрести практические навыки по составлению SQL-запросов для получения данных из одного источника, применению основных конструкций команды SELECT и встроенных функций для решения типовых задач.

В лабораторной работе используется база данных сотрудников фирмы. Таблица LOCATION содержит список городов: код города (Location_ID) и его название (Regional_group), в которых располагаются отделения компании. Таблица DEPARTMENT содержит список отделений компании: код отделения (Department_ID), название отделения (Name) и внешний ключ – код города (Location_ID). Таблица JOB содержит список должностей: код должности (Job_ID) и ее название (Function). Список сотрудников – таблица EMPLOYEE содержит список сотрудников компании с указанием фамилии (Last_name), имени (First_name), даты приема сотрудника на работу (Hire_date), зарплаты (Salary) и надбавки-премии (Commission). Первичный ключ таблицы Employee_ID. Таблица также содержит пару внешних ключей, обеспечивающих связь с таблицей должностей (Job_ID) и отделений (Department_ID). А также устанавливается иерархическая связь между сотрудниками – начальник-подчиненный. В данной связи и родительской, и дочерней таблицей выступает таблица Employee, а внешним ключом является поле Manager_ID, указывающее на начальника.

Вторая часть базы данных содержит информацию о проектах, выполняемых фирмой. Таблица PROJECTTYPE содержит список типов, по которым классифицируются проекты, и состоит из кода типа проекта (ProjectType_ID), являющегося первичным ключом и наименования типа (Name). Таблица PROJECT хранит перечень проектов. Для каждого проекта указывается код (Project_ID), являющийся первичным ключом, название проекта (Name), дата начала проекта (Start_Date), дата окончания проекта (Stop_Date) и код типа проекта (ProjectType_ID), являющийся внешним ключом. Наименование проекта, дата начала и код типа проекта являются обязательными для заполнения. Отсутствие значения в поле окончания проекта говорит о том, что проект не закончен и работы по нему еще ведутся.

Между проектами и сотрудниками устанавливается связь посредством таблицы PROJECTTEAM, первичный ключ которой состоит из двух внешних ключей (Employee_ID и Project_ID), а также внешнего ключа (ProjectJob_ID), указывающего роль сотрудника в данном проекте. Сотрудник может участвовать в нескольких проектах с одинаковыми и/или разными ролями или не принимать участие ни в одном из проектов. Таблица PROJECTJOB содержит список ролей, выполняемых сотрудниками в проектах. Первичный ключ – код роли (ProjectJob_ID), неключевое поле – название роли (Name).

На рис. 9 представлен даталогическая схема рассматриваемой базы данных.

Рис. 9. Даталогическая схема базы данных сотрудников
фирмы

Задания для самостоятельной работы

Разработать запросы в соответствии с предложенными в табл. 1 вариантами:

Таблица 1

№ варианта	Номера задач
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

Разработать запрос, результатом которого будет таблица со столбцами...

1. имя, фамилия, оклад.

2. имя, фамилия, дата приема на работу.

3. название проекта, дата начала и дата окончания проекта.

4. фамилия, дата приема на работу. Упорядочить по дате приема на работу.

5. название проекта, дата начала проекта. Упорядочить по дате начала проекта.

6. фамилия, дата приема на работу. В таблицу должны войти только сотрудники, принятые на работу после апреля 1985 года. Упорядочить по убыванию даты приема на работу. (Значение даты записывается в апострофах ‘ ’).

7. название проекта, дата начала проекта. В таблицу должны войти только проекты, начатые до октября 2000 года. Упорядочить по убыванию даты начала проекта. (Значение даты записывается в апострофах ‘ ’).

8. количество сотрудников, принятых на работу в период с мая по сентябрь 1985 года. Чтобы определить количество, воспользуйтесь функцией COUNT.

9. количество проектов, начатых в период с 1 декабря 1998 года по 2 февраля 2001 года. Чтобы определить количество, воспользуйтесь функцией COUNT.

10.количество незавершенных проектов. Чтобы определить количество, воспользуйтесь функцией COUNT.

11.количество завершенных проектов. Чтобы определить количество, воспользуйтесь функцией COUNT.

12.фамилия, комиссионное вознаграждение. В таблицу должны войти только сотрудники, имеющие надбавку.

13.название проекта, дата начала и дата окончания проекта. В таблицу должны войти только завершенные проекты.

14.фамилия, имя. В таблицу должны войти только те сотрудники, фамилии которых окан/чиваются на букву «А», и фамилии которых содержат не более пяти символов. Для определения длины строки, воспользуйтесь функцией LEN.

15.фамилия, имя. В таблицу должны войти только те сотрудники, фамилии которых НЕ содержат букву «А», и фамилии которых содержат не менее четырех символов. Для определения длины строки, воспользуйтесь функцией LEN

16.фамилия, имя. В таблицу должны войти только те сотрудники, имена которых содержат букву «И», и имена которых содержат не менее четырех символов. Для определения длины строки, воспользуйтесь функцией LEN

17.фамилия, имя. В таблицу должны войти только те сотрудники, имена которых начинаются не с буквы «А», или имена которых содержат четырех символов. Для определения длины строки, воспользуйтесь функцией LEN

18.фамилия, имя. В таблицу должны войти только те сотрудники, фамилии которых оканчиваются не на буквы «В», или фамилии которых содержат 7 символов. Для определения длины строки, воспользуйтесь функцией LEN

19.фамилия, сумма, зарабатываемая сотрудником за 5 месяцев с учетом надбавки. Неустановленная надбавка означает отсутствие надбавки.

20.фамилия, сумма, зарабатываемая сотрудником за 5 месяцев с учетом надбавки. Неустановленная надбавка означает, что надбавка составляет 100% от зарплаты.

21.фамилия, процент надбавки к зарплате. Неустановленная надбавка означает отсутствие надбавки.

22.фамилия. В таблицу должны войти сотрудники, которые зарабатывают за 5 месяцев с учетом надбавки больше 4000 и меньше 10000. Решите задачу с использованием between. Неустановленная надбавка означает отсутствие надбавки.

23.фамилия. В таблицу должны войти сотрудники, которые зарабатывают за 5 месяцев с учетом надбавки больше 10000 и меньше 25000. Неустановленная надбавка означает, что надбавка составляет 100% от зарплаты.

24.фамилия. В таблицу должны войти сотрудники, которые имеют процент надбавки к зарплате больше 10 и меньше 80. Решите задачу с использованием between. Неустановленная надбавка означает отсутствие надбавки.

25. имя, количество сотрудников с таким именем.

26. имя, количество сотрудников с таким именем, в таблицу должны попасть только редкие имена, т.е. число сотрудников с таким именем в компании не больше двух.

27. месячный фонд заработной платы с учетом надбавки. Неустановленная надбавка означает отсутствие надбавки.

28. месячный фонд заработной платы с учетом надбавки. Неустановленная надбавка означает, что надбавка составляет 100% от зарплаты.

29. Минимальный ежемесячный доход в компании с учетом надбавки. Неустановленная надбавка означает отсутствие надбавки.

30. Максимальный ежемесячный доход в компании с учетом надбавки. Неустановленная надбавка означает, что надбавка составляет 100% от зарплаты.

Контрольные вопросы

1. Какова последовательность и назначение ключевых полей команды SELECT?

2. Ключевое слово команды SELECT, задающее список источников данных?

3. Ключевое слово команды SELECT, задающее условия отбора строк?

4. Ключевое слово команды SELECT, задающее сортировку строк в результирующей таблице?

5. В каких случаях применяется группировка данных в запросе?

6. Агрегирующие функции, их влияние на результат запроса?

Содержание отчета

Отчет о лабораторной работе должен включать: цель работы, постановку задачи, перечень всех разработанных SQL запросов.