Лабораторная работа № 6
ЗНАКОМСТВО С СУБД POSTGRESQL
Цель работы: познакомиться с СУБД PostgreSQL и языком запросов SQL.
Методические указания.
В целях ознакомления с PostgreSQL создайте базу данных, схему, и несколько таблиц
(данный порядок «база данных схема таблицы» частично описывает логическое устройство СУБД PostgreSQL).
Создать базу данных students можно при помощи DDL1-команды, представленной ниже (для выполнения запроса нажмите на кнопку оранжевого цвета, либо выполните сочетание клавиш Ctrl + Enter).
CREATE DATABASE students;
После создания базы данных необходимо установить соединение к ней. Данная процедура проиллюстрирована на рисунках 10 и 11.
Рисунок 10 – Выбор редактора соединений
Рисунок 11 – Настройки соединения
После нажатия на кнопку «ОК» вы будете подключены к базе данных students.
Создайте схему «hr» в базе данных при помощи следующей команды:
CREATE SCHEMA hr;
Для того чтобы таблицы создавались в данной схеме, необходимо для базы данных students указать путь к данной схеме (поскольку схем может быть несколько, «постгресу» нужно знать, в какой именно схеме создавать таблицы):
ALTER DATABASE students SET search_path = hr;
В СУБД может существовать несколько пользователей с разными правами. По умолчанию в СУБД PostgreSQL есть пользователь postgres, который имеет все возможные права. Посмотрим, под каким пользователем мы работаем при помощи запроса:
SELECT current_user;
Создайте таблицу Jobs, которая будет содержать идентификаторы профессий, их названия, минимальную и максимальную зарплату. Обратите внимание на типы данных,
присваеваемые полям.
CREATE TABLE jobs (
job_id varchar(10) PRIMARY KEY, job_title varchar(35) NOT NULL, min_salary integer,
max_salary integer);
2
Теперь, когда таблица создана, заполните её данными. Функцию добавления данных в таблицу выполняет DML2-команда «insert». При добавлении записей в таблицу важно учитывать, что при вставке данных в поля символьного типа их необходимо обрамлять одинарными кавычками.
INSERT INTO jobs
VALUES ('AD_PRES', 'President', 20080, 40000),
('AD_VP', 'Administration Vice President', 15000, 30000), ('AD_ASST', 'Administration Assistant', 3000, 6000), ('FI_MGR', 'Finance Manager', 8200, 16000), ('FI_ACCOUNT', 'Accountant', 4200, 9000),
('SA_MAN', 'Sales Manager', 10000, 20080), ('PU_MAN', 'Purchasing Manager', 8000, 15000), ('PU_CLERK', 'Purchasing Clerk', 2500, 5500), ('ST_MAN', 'Stock Manager', 5500, 8500), ('ST_CLERK', 'Stock Clerk', 2008, 5000), ('IT_PROG', 'Programmer', 4000, 10000), ('MK_MAN', 'Marketing Manager', 9000, 15000),
('HR_REP', 'Human Resources Representative', 4000, 9000);
Создайте таблицу Departments с информацией об отделах и уникальных идентификаторах начальников этих отделов.
CREATE TABLE departments (
department_id integer PRIMARY KEY, department_name varchar(30) NOT NULL, manager_id integer
);
Добавьте в таблицу Departments данные при помощи скрипта, представленного ниже.
INSERT INTO departments VALUES
(10, 'Administration', 51), (20, 'Marketing', 52),
(30, 'Purchasing', 15),
(40, 'Human Resources', 53), (50, 'Shipping', 22),
(60, 'IT', 4), (80, 'Sales', 46),
(90, 'Executive', 1), (100, 'Finance', 9);
Создайте таблицу Employees. В ней будет храниться информация о сотрудниках
(уникальный идентификатор, имя, фамилия, идентификатор должности, зарплата,
идентификатор начальника, идентификатор отдела). Обратите внимание, что полю employee_id был присвоен тип serial (целое число с автоувеличением), который самостоятельно будет генерировать идентификаторы сотрудников с добавлением новых записей в таблицу. При вставке данных в таблицу теперь необязательно указывать поле employee_id.
3
В целях поддержания целостности данных таблицу Employees необходимо связать с таблицами Jobs и Departments, добавив внешние ключи (оператор «references»).
CREATE TABLE employees (
employee_id serial PRIMARY KEY, first_name varchar(20) NOT NULL, last_name varchar(25) NOT NULL,
job_id varchar(10) NOT NULL REFERENCES jobs (job_id), salary integer,
manager_id integer,
department_id integer NOT NULL REFERENCES departments (department_id)
);
Добавьте в таблицу Employees записи при помощи скрипта ниже. Обратите внимание,
что при вставке данных мы не указываем идентификатор сотрудника (employee_id), он будет автоматически создаваться для каждой записи.
INSERT INTO employees (first_name, last_name, job_id, salary, manager_id, department_id)
VALUES
('Steven', 'King', 'AD_PRES', 24000, NULL, 90), ('Neena', 'Kochhar', 'AD_VP', 17000, 1, 90), ('Lex', 'De Haan', 'AD_VP', 17000, 1, 90), ('Alexander', 'Hunold', 'IT_PROG', 9000, 3, 60), ('Bruce', 'Ernst', 'IT_PROG', 6000, 4, 60), ('David', 'Austin', 'IT_PROG', 4800, 4, 60), ('Valli', 'Pataballa', 'IT_PROG', 4800, 4, 60), ('Diana', 'Lorentz', 'IT_PROG', 4200, 4, 60), ('Nancy', 'Greenberg', 'FI_MGR', 12008, 2, 100), ('Daniel', 'Faviet', 'FI_ACCOUNT', 9000, 9, 100), ('John', 'Chen', 'FI_ACCOUNT', 8200, 9, 100), ('Ismael', 'Sciarra', 'FI_ACCOUNT', 7700, 9, 100),
('Jose Manuel', 'Urman', 'FI_ACCOUNT', 7800, 9, 100), ('Luis', 'Popp', 'FI_ACCOUNT', 6900, 9, 100), ('Den', 'Raphaely', 'PU_MAN', 11000, 1, 30), ('Alexander', 'Khoo', 'PU_CLERK', 3100, 15, 30), ('Shelli', 'Baida', 'PU_CLERK', 2900, 15, 30), ('Sigal', 'Tobias', 'PU_CLERK', 2800, 15, 30), ('Guy', 'Himuro', 'PU_CLERK', 2600, 15, 30), ('Karen', 'Colmenares', 'PU_CLERK', 2500, 15, 30), ('Matthew', 'Weiss', 'ST_MAN', 8000, 1, 50),
('Adam', 'Fripp', 'ST_MAN', 8200, 1, 50), ('Payam', 'Kaufling', 'ST_MAN', 7900, 1, 50), ('Shanta', 'Vollman', 'ST_MAN', 6500, 1, 50), ('Kevin', 'Mourgos', 'ST_MAN', 5800, 1, 50), ('Julia', 'Nayer', 'ST_CLERK', 3200, 21, 50),
('Irene', 'Mikkilineni', 'ST_CLERK', 2700, 21, 50), ('James', 'Landry', 'ST_CLERK', 2400, 21, 50), ('Steven', 'Markle', 'ST_CLERK', 2200, 21, 50), ('Laura', 'Bissot', 'ST_CLERK', 3300, 22, 50), ('Mozhe', 'Atkinson', 'ST_CLERK', 2800, 22, 50), ('James', 'Marlow', 'ST_CLERK', 2500, 22, 50), ('TJ', 'Olson', 'ST_CLERK', 2100, 22, 50), ('Jason', 'Mallin', 'ST_CLERK', 3300, 23, 50), ('Michael', 'Rogers', 'ST_CLERK', 2900, 23, 50), ('Ki', 'Gee', 'ST_CLERK', 2400, 23, 50),
('Hazel', 'Philtanker', 'ST_CLERK', 2200, 23, 50),
4
('Renske', 'Ladwig', 'ST_CLERK', 3600, 24, 50), ('Stephen', 'Stiles', 'ST_CLERK', 3200, 24, 50), ('John', 'Seo', 'ST_CLERK', 2700, 24, 50), ('Joshua', 'Patel', 'ST_CLERK', 2500, 24, 50), ('Trenna', 'Rajs', 'ST_CLERK', 3500, 25, 50), ('Curtis', 'Davies', 'ST_CLERK', 3100, 25, 50), ('Randall', 'Matos', 'ST_CLERK', 2600, 25, 50), ('Peter', 'Vargas', 'ST_CLERK', 2500, 25, 50), ('John', 'Russell', 'SA_MAN', 14000, 1, 80), ('Karen', 'Partners', 'SA_MAN', 13500, 1, 80), ('Alberto', 'Errazuriz', 'SA_MAN', 12000, 1, 80), ('Gerald', 'Cambrault', 'SA_MAN', 11000, 1, 80), ('Eleni', 'Zlotkey', 'SA_MAN', 10500, 1, 80), ('Jennifer', 'Whalen', 'AD_ASST', 4400, 2, 10), ('Michael', 'Hartstein', 'MK_MAN', 13000, 1, 20), ('Susan', 'Mavris', 'HR_REP', 6500, 2, 40);
Данные в таблицу employees добавлены, теперь необходимо добавить внешний ключ таблице departments при помощи скрипта, представленного ниже.
ALTER TABLE departments
ADD CONSTRAINT mgr_emp_fkey FOREIGN KEY (manager_id) REFERENCES employees (employee_id);
Теперь, когда таблицы созданы, схему данных можно просмотреть, выбрав пункт
«Просмотреть объект «Таблицы»», кликнув правой кнопкой мыши на объект «Таблицы» в
левом меню (рисунок 12), и далее выбрав «Диаграмма» (рисунок 13). Полученная схема данных представлена на рисунке 14.
Рисунок 12 – Выбор кнопки «Просмотр объекта Таблицы»
5
Рисунок 13 – Просмотр объекта «Таблицы»
Рисунок 14 – Схема данных «hr»
Таблица employees имеет внешние ключи job_id и department_id к таблицам jobs и departments соответственно. Таблица departments имеет внешний ключ manager_id к таблице employees. Таким образом поддерживается целостность данных – пользователь не сможет случайно удалить записи из таблиц jobs и departments, если ключи этих записей содержаться в полях таблицы employees. Также из таблицы departments нельзя будет удалить запись, пока значение поля manager_id присутствует в поле employee_id таблицы employees.
Например, если мы хотим удалить из таблицы jobs строчку, где поле job_id =
‘AD_PRES’, то мы не сможем выполнить данную операцию, пока в таблице employees
имеется запись о Стивене Кинге, который имеет такой же идентификатор.
6
Выполнение запросов:
Теперь, когда таблицы созданы и заполнены записями, предлагается с ними поработать.
1. Зачастую перед началом работы с данными важно на эти данные просто посмотреть, и понять, какова структура таблицы, что из себя эти данные представляют, какие типы данных используются и т. д. Чтобы посмотреть на все данные таблицы employees,
можно использовать следующий запрос:
SELECT * FROM employees;
2.Предположим, что ген. директора компании интересуют только сотрудники отдела
сидентификатором 50. Выведите их, используя запрос:
SELECT * FROM employees WHERE department_id = 50;
3. Требуется отсортировать их по убыванию значений в столбце зарплаты (salary):
SELECT * FROM employees
WHERE department_id = 50
ORDER BY salary DESC;
Примечание: если необходимо отсортировать по возрастанию, то нужно написать
«ORDER BY salary ASC», либо «ORDER BY salary» (по умолчанию данные сортируются по возрастанию).
4. Требуется найти сотрудников, зарплата которых больше 10000, но меньше 15000.
Найти их поможет запрос:
SELECT first_name, last_name, salary
FROM employees
WHERE salary > 10000 AND salary < 15000;
5. Требуется вывести среднюю зарплату сотрудников по каждой должности во всех отделах, причем столбец со средней зарплатой нужно отобразить как «Средняя_зарплата».
Вывести результат можно при помощи запроса ниже.
SELECT department_id, job_id, avg(salary) AS Средняя_зарплата
FROM employees
GROUP BY department_id, job_id
ORDER BY 1, 3 DESC;
Для нахождения средней зарплаты была использована агрегатная функция «avg».
Существуют также «sum», «count», «max», «min» и другие. Использование агрегатной функции с выводом дополнительных полей (таких как, например, department_id и job_id)
обязывает выполнять группировку по этим полям «GROUP BY department_id, job_id». Данные
7
сортируются по полю department_id по возрастанию и по средней зарплате по убыванию. В
конструкции ORDER BY мы указали номера полей, вместо их названий.
Примечание: как правило, в конструкции ORDER BY лучше однозначно указывать названия полей, поскольку количество столбцов в таблице может поменяться, а значит поменяются и их порядковые номера.
6. Поле department_id выглядит не очень информативно, лучше вместо него использовать department_name, которое содержится в таблице departments. Для этого необходимо связать две таблицы при помощи оператора INNER JOIN по полю department_id.
Примечание: слово INNER (внутреннее соединение) можно опустить. Существуют также OUTER JOIN’ы (внешние соединения), такие как LEFT, RIGHT, FULL, и т.д., но об типах соединений будет рассказано в следующих лабораторных работах.
SELECT department_name, job_id, avg(salary) AS Средняя_зарплата
FROM employees e JOIN departments d on e.department_id = d.department_id GROUP BY department_name, job_id
ORDER BY 3 DESC;
Здесь в строчке запроса «FROM employees e JOIN departments d ON e.department_id = d.department_id» использованы так называемые алиасы «e» и «d», чтобы при обращении к столбцам с одинаковыми именами, но принадлежащим двум разным таблицам, не использовать полные имена таблиц. Иначе говоря, строчка «FROM employees JOIN departments
ON employees.department_id = departments.department_id» делает то же самое, что и строчка,
используемая в запросе.
7. Требуется найти количество сотрудников по отделам в диапазоне от 4 до 7. Для выполнения данной задачи можно использовать запрос, представленный ниже.
SELECT department_name, count(employee_id)
FROM employees e JOIN departments d ON e.department_id = d.department_id GROUP BY d.department_name
HAVING count(employee_id) BETWEEN 4 AND 7;
В данном запросе была использована конструкция «HAVING». Она имеет схожее с конструкцией «WHERE» предназначение, однако разница заключается в том, что «WHERE» – это ограничивающее выражение, выполняющееся до того, как будет получен результат.
Конструкция «HAVING» – фильтрующее выражение, которое применяется к результату операции и выполняется уже после того, как результат будет получен. Таким образом,
фильтр по агрегатной функции «count» можно применить лишь в конструкции «HAVING».
8
8. Требуется получить список из 5 сотрудников с самыми высокими зарплатами. Для этого можно использовать следующий запрос:
SELECT employee_id, first_name ||' '||last_name AS employee, salary FROM employees
ORDER BY salary DESC LIMIT 5;
Здесь мы имя и фамилию объединили в одно поле и назвали как «employee». Сначала были отсортированы сотрудники по их зарплате от большей к меньшей, а затем при помощи
«LIMIT» были выведены лишь первые 5 сотрудников. Альтернативой «LIMIT 5» будет строчка
«FETCH FIRST 5 ROWS ONLY». Если бы требовалось найти, например, 4-го сотрудника по величине зарплаты, то следовало бы написать «LIMIT 1 OFFSET 3». При помощи параметра
«OFFSET» можно указать, какое количество строчек необходимо пропустить.
9. В таблице employees практически у каждого сотрудника есть начальник (поле manager_id). Например, посмотрев на данные в таблице, будет видно, что у Steven King нет начальника, а у Neena Kochhar начальником является Steven King, поскольку в поле manager_id указан идентификатор Steven’а.
Требуется узнать, сколько у каждого из начальников в подчинении сотрудников.
Справиться с такой задачей может помочь запрос, представленный ниже.
SELECT first_name ||' '|| last_name AS manager,
(SELECT count(manager_id) FROM employees WHERE manager_id = e.employee_id) FROM employees e
GROUP BY employee_id, manager
ORDER BY 2 DESC;
Для вывода числа подчиненных необходимо посчитать количество идентификаторов в столбце manager_id, значения которых соответствуют идентификатору начальника – эту
функцию выполнит подзапрос (SELECT count(manager_id) FROM employees WHERE manager_id = e.employee_id).
10. Чтобы получить разницу между зарплатой сотрудника определенной профессии (в
нашем случае программиста) и средней зарплатой по этой профессии, можно воспользоваться следующим запросом:
SELECT employee_id, job_id, salary,
salary - (SELECT avg(salary) FROM employees WHERE job_id = e.job_id) AS difference
FROM employees e
9
WHERE job_id = 'IT_PROG'
GROUP BY employee_id, job_id
ORDER BY difference;
Для нахождения разницы из зарплаты сотрудника вычитается средняя зарплата по его профессии, найденная в подзапросе.
11. Требуется найти сотрудников, у которых в названии должности фигурирует слово
«clerk». Запрос для выполнения этой задачи представлен ниже:
SELECT first_name, last_name, job_title
FROM employees e JOIN jobs j ON e.job_id = j.job_id
WHERE job_title ILIKE '%clerk%';
В запросе была использована конструкция ILIKE (также существует конструкция LIKE),
которая проверяет поле «job_title» на соответствие предложенному слову '%clerk%'. Знаки
«%» обозначают, что любое число символов может находиться до и после слова. Если бы мы вместо «ILIKE» написали «LIKE», то запрос бы ничего не вывел. Почему?
Порядок выполнения практической работы
1.Создайте базу данных, схему, таблицы и заполните их данными в соответствии с методическими указаниями.
2.Выполните запросы к таблицам, исходя из варианта, выданного вам преподавателем (Таблица 1).
3.Напишите отчёт о выполненной работе.
Содержание отчета
1.Цель работы.
2.Вариант задания (описать предметную область из методических указаний, указать запросы вашего варианта).
3.Описание процесса создания таблиц и заполнения их данными. Результаты фиксируйте при помощи скриншотов (схема данных, заполненные таблицы).
4.Листинг написанных запросов, описание, и результаты их выполнения,
подкрепленные скриншотами.
5.Расширенные выводы о проделанной работе с описанием проблем, которые возникли в процессе выполнения лабораторной работы, и путей их решения.
6.Список использованных источников.
10