Язык SQL Oracle для хранения, обработки и анализа данных

Практикум 1. Основы SQL

Показывать лекцию целиком

Требуемое программное обеспечение

ОС: Microsoft Windows 10 и выше.

СУБД: Oracle® Database Express Edition 11g Release 2

Прикладное программное обеспечение: Oracle SQL Developer

Подготовительная работа

В связи с возможными ограничениями для пользователе из РФ возможны два варианта установки программного обеспечения. 

Вариант 1.

Скачать с официального сайта компании Oracle свободно распространяемую по лицензии разработчика СУБД Oracle® Database Express Edition 11g.

Сайт www.oracle.com (требуется регистрация), лучше работать на сайте www.oracle.ru.

Установить СУБД на персональный компьютер.

В сети Интернет существует много материалов по теме установки Oracle. Один из них https://info-comp.ru/sisadminst/417-oracle-database-express-edition-11g.html 
содержит подробную инструкцию.

Внимание! Пароль, который Вы задаете при установке, следует запомнить.

Скачать с официального сайта компании Oracle программное обеспечение Oracle SQL Developer (доступна версия 19.1.0).

Установить программное обеспечение на персональный компьютер.

В сети Интернет существует много материалов по теме установки этого продукта. Один из них https://info-comp.ru/programmirovanie/418-installing-oracle-sql-developer-4.html 
содержит подробную инструкцию.

Внимание! Oracle SQL Developer требует при первом запуске установленную на компьютере java-машину (указать путь к файлу java.exe). Возможен конфликт с версией этой машины, установленной на Вашем компьютере. Решение вопроса состоит в переустановке java, инсталляцию которой лучше скачать с официального сайта Oracle.

Скачать с сайта курса файл football2018.sql.

Вариант 2.

Скачайте необходимое программное обеспечение отсюда: https://disk.yandex.ru/d/jEHIupkSQHuiVQ

Выполните все действия, которые указаны в занятии "Установка программного обеспечения" этого курса. 

Следуйте строго инструкциям в видеороликах.

После установки Oracle XE 11g в меню пуск у Вас будет:

Язык SQL для хранения, обработки и анализа данных. Практикум 1

Запустите базу данных.

Язык SQL для хранения, обработки и анализа данных. Практикум 1

Запустите SQL* Plus.

Язык SQL для хранения, обработки и анализа данных. Практикум 1

Подробная инструкция действий представлена в ролике: Настройка БД для работы с практикумом:

Записаться

Введите команду Connect, Пользователь: System, Пароль: который Вы определили при установке.

После соединение с базой данных, введите команду (в качестве password введите свой пароль, например, 123456, и запомните его):

ALTER USER HR ACCOUNT UNLOCK IDENTIFIED BY password;

Этой командой Вы разблокируете учебную базу данных «Отделе кадров» (HR - имя пользователя схемы HR).

По умолчанию предполагается пароль HR, но Вы можете определить свой пароль в этой команде.

Чтобы завершить работу SQL* Plus, введите команду

exit 

После выполнения команды окно должно закрыться.

Запустите приложение Oracle SQL Developer

Язык SQL для хранения, обработки и анализа данных. Практикум 1

И выполните соединение со схемой HR (щелкнуть кнопкой мыши на пиктограмму hr в левом верхнем окне приложения) – введите в поле Username – HR, в поле Password – пароль, который Вы определили для этого пользователя):

Язык SQL для хранения, обработки и анализа данных. Практикум 1

Появиться окно hr.sql, в котором Вам предстоит работать.

Далее приступайте к выполнению заданий Практикума.

Цель практикума: Научиться создавать запросы к таблицам базы данных с использованием основных предложений команды SQL SELECT

В результате выполнения Практикума студенты должны:

Задание 1. Изучение учебной БД «Отдел кадров».

Цель: научиться исследовать структуру (состав колонок) таблиц БД.

Порядок выполнения задания.

Разверните узел дерева в левом верхнем окне, щелкнув кнопкой мыши на «+» перед узлом «Tables..»:

Язык SQL для хранения, обработки и анализа данных. Практикум 1

В результате получите доступ к списку таблиц БД.

Разверните узел дерева в левом верхнем окне, щелкнув кнопкой мыши на «+» перед узлом «Emploeeys..»:

Язык SQL для хранения, обработки и анализа данных. Практикум 1

В результате получите доступ к списку колонок таблицы Emploeeys.

Щелкнув кнопкой мыши на «+» на узле «Emploeeys..» получите доступ к информации об этой таблице (определение колонок, данные в таблице, физическую модель и связи таблицы, ограничения на значения колонок и т. д):

Язык SQL для хранения, обработки и анализа данных. Практикум 1

Задание. Изучить на нескольких таблицах (таблицы Emploeeys, Departnets, Locations) определение колонок, команду которой была создана эта таблица, определить первичный ключ и внешние ключи, ограничения на значения колонок.

Примечание. Если интерес выходит за рамки лекций курса, то документация по SQL доступна по ссылке https://docs.oracle.com/cd/E11882_01/server.112/e41084/toc.htm

Для выполнения дальнейших заданий нужны таблицы Emploeeys, Departnets, Locations.

Изучить определение представления EMP_DETAILS_VIEW.

Пример выполнения задания

Записаться

Задание 2. Работа с простой командой SQL SELECT («Введение в SQL»)

Цель: Получить навыки работы с командой SQL SELECT.

Порядок выполнения.

  1. Выборка данных без условий (SELECT … FROM).

    Составить запрос на выборку всех данных из таблицы Emlpoeeys.

    Составить запрос на выборку определенной колонки из таблицы Emlpoeeys.

  2. Выборка заданных строк (SELECT … FROM … WHERE – AND, NOT, BETWEEN …END, IN, LIKE)

    Составить запрос на выборку списка сотрудников подразделения с номером 70 из таблицы Emlpoeeys.

    Составить запрос на выборку списка сотрудников из отдела информационных технологий с зарплатой превышающей 5000 у.е.

    Составить запрос на выборку списка сотрудников, работающих в подразделении с номером 80 и не имеющих позицию SA_MAN.

    Составить запрос на выборку списка сотрудников, у которых зарплата находится между 5000 и 10000 у.е.

    Составить запрос на выборку списка сотрудников подразделений, номера которых 20, 30, 40.

    Составить запрос на выборку списка сотрудников, имеющих в позициях которых присутствует слово «CLERK».

  3. Упорядочение строк (ORDER BY)

    Составить запрос на выборку списка сотрудников, подразделения с номером 80 и упорядочить его по размеру оклада.

  4. Соединение таблиц

    Составить запрос, который определяет, в каком подразделении работает служащий по фамилии Russell.

    Составить запрос, чтобы получить список должностей, которые имеются только в данных подразделениях

  5. Объединение, вычитание и пересечение таблиц (UNION, INTERSECTOIN, MINUS)

    Составить запрос получить список служащих, работающих в подразделениях 20 и 30.

  6. Использование функций в запросах

    Составить запрос, чтобы получить список служащих, который показывает выплату, которую получил каждый служащий с учетом премий.

    Составить запрос, чтобы получить список имен служащих так, чтобы фамилии всегда начинались с заглавной буквы, а все остальные были прописными.

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

    Составить запрос, чтобы определить даты приема на работу сотрудников

    Составить запрос, чтобы получить список служащих, сведения о дате приема каждого сотрудника на работу и количестве полных месяцев, которое каждый сотрудник отработал по настоящее время.

  7. Агрегатные функции (GROUP BY, HAVING)

    Составить запрос, чтобы найти минимальные и максимальные оклады сотрудников в подразделениях.

    Составить запрос, чтобы узнать, сколько сотрудников работает на каждой должности в каждом подразделении, какова сумма начислений на подразделение и средняя зарплата.

    Составить запрос как в предыдущем примере, но при этом каждая группа сотрудников должна состоять не менее чем из двух сотрудников

Пример выполнения задания

Записаться

Задание 3. Работа командой SQL SELECT.

Цель: Научиться работать с командой SQL SELECT.

Порядок выполнения.

    1. Составить запрос для получения списка подразделений организации. Добавить в список предложения SELECT псевдоколонку ROWNUM. Добавить в запрос сортировку по наименованию подразделения. Добавить предложение WHERE условием ROWNUM < 6.
    2. Составить запрос, который находит сотрудника с минимальным окладом. Составить запрос, который вычисляет список отделов, которых средний оклад меньше чем средний оклад по организации. Составить запрос, который показывает самого низко оплачиваемого сотрудника в каждом подразделении.
    3. Составить запрос, чтобы получить количество сотрудников в организации. Выполнить в предыдущем запросе соединение таблиц EMPLOYEES, DEPARTMENTS. Составить запрос, который показывает руководителей организации и их подчиненных.
    4. Изучить в свойства таблицы REGIONS схемы HR. Составить запрос эквивалентный запросу

      SELECT * FROM regions NATURAL JOIN countries;

    5. Изучить в свойства таблиц JOB и JOB_HISTOTY схемы HR. Составить запросы для вычисления строк в каждой из этих таблиц. Составить запрос, который вычисляет разность этих таблиц. Составить запрос, который вычисляет объединение этих таблиц с опцией и без опции ALL.
HR  - это схема, в которой можно создавать таблицы БД, не сильно связанные смыслом. 
Файл football2018.sql является текстовым файлом. Он содержит набор команд для создания таблиц в схеме HR в ручном режиме, покомандно, т.е. из него нужно брать (копированием, чтобы не набирать) команды и их выполнять последовательно в приложении. Используется, чтобы показать, как создаются таблицы БД без загрузчика и специальных средств. Довольно рутинное задание. Займет до 1 часа.

Пример выполнения задания

Записаться

Задание 4. Работа с командами манипулирования данными.

Цель: Научиться работать с командами манипулирования данными SQL.
Порядок выполнения.
1. Создание таблиц БД «Чемпионат мира по футболу 2018».
В схеме HR создать таблицы БД.  Из файла football2018.sql выполнить команды CREATE для создания таблиц MESTO_B_GRUPPE, IGR, RAZM_STADION. Индексы на первичные ключи будут созданы автоматически. Найти эти индексы в приложении Oracle SQL Developer. Команды создания таблиц следует выполнять отдельно.
2. Добавление строк в таблицы MESTO_B_GRUPPE, IGR, RAZM_STADION. Выполнить команды INSERT из файла football2018.sql. Убедиться, что таблицы заполнены.
Обратите внимание, как добавляются колонки с типом данных Date. Функция TO_CHAR используется, если формат представления даты в БД отличается от представления по умолчанию. Команды добавления записей в таблицы следует выполнять отдельно.
3. Изменение значения колонок. В таблице RAZM_STADION в колонке STADION название города Ростов на Дону написано по-разному (без дефиса и с дефисом). Выполнить команду UPDATE, чтобы все названия в этой колонке были одинаковыми.
 
UPDATE RAZM_STADION
SET STADION = 'Ростов на Дону'
WHERE STADION LIKE 'Ростов-%';
 
Также выполнить команду обновления для названия города Санкт-Петербург.
4. Составление запросов. Теоретико-множественные операции и агрегация.
Составить запрос, чтобы получить перечень всех групп, в которых ни одна игра не проходила в Саратове.
Составить запрос, чтобы найти те команды, которые выступали на играх в качестве гостей.
Составить запрос, чтобы получить максимальные количества забитых мячей в каждой группе, а также вывести среднее по забитым мячам по группе.

Пример выполнения задания

Записаться

Задание 5. Завершить работу.

В меню «File» Oracle SQL Developer выполнить пункт меню «Exit».

В окне приложения Start Database выполнить команду Exit;.

Запустить приложение Stop Database из меню «Пуск». После остановки сервиса выполнить команду Exit;.

Пример выполнения задания

Записаться

Приложения

football2018.sql
Предыдущее занятие Следующее занятие
Вернуться к учебному плану
^