4956

Освоение SQL и PL/SQL Oracle Лабораторные работы

Книга

Информатика, кибернетика и программирование

Учебное пособие является руководством по выполнению лабораторных работ, целью которых является практическое освоение SQL и PL/SQLOracle. Запросы и другие команды SQL объединены в шесть тем, каждая из которых соответствует одной лабораторной ра...

Русский

2012-11-30

1.76 MB

369 чел.

Учебное пособие является руководством по выполнению лабораторных работ, целью которых является практическое освоение SQL и PL/SQL Oracle. Запросы и другие команды SQL объединены в шесть тем, каждая из которых соответствует одной лабораторной работе. Образец выполнения SQL лабораторных работ представлен для предметной области торговой фирмы. Студенты должны придумать для выдаваемой преподавателем другой предметной области и повторить запросы и команды образца по всем шести темам. Аналогично должны выполняться лабораторные работы по PL/SQL. В качестве образца настоящее пособие ссылается на программные конструкции и предметную область из книги Скотта Урмана «Oracle8. Язык программирования PL/SQL».


Оглавление

Предисловие,,,,,…………………………………………………………………………………4

Глава I.  Общая последовательность этапов выполнения лабораторных работ по освоению

языка SQL Oracle………………………………………………………………………………..4

Глава II.  Образец для выполнения лабораторных работ по освоению SQL Oracle………..5

     II.1. Утилита SQL*Plus, ее настройка и работа с ней при выполнении простых

    запросов  выборки столбцов и строк………………………………………………….……5

         II.1.1. Команды утилиты SQL*Plus и ее настройка…………….………………………5

         II.1.2. Создание пользователя Oracle и таблиц его схемы…………………………….10

         II.1.3. Переменные привязки  в среде SQL*Plus……………...………………………..11

         II.1.4. Переменные подстановки  в среде SQL*Plus……………………....…………...12

         II.1.5. Выборка столбцов таблицы………………….…………………………………..15

         II.1.6. Выборка строк таблицы…………………………….…………………………....19

      II.2. Выборка данных из нескольких таблиц…………………………………………..22

         II.2.1. Подзапросы……………………………………………………………………….22

        II.2.2. Коррелированные подзапросы……………………………………….………..26

        II.2.3. Использование подзапросов во фразе FROM……………………..…...........28

         II.3.4. Операторы над множествами……………………………………………………29

         II.2.5. Объединения (внутренние)………………………………………………………30

         II.2.6. Внешние объединения………………………..…………………………………33

         II.2.7. Иерархические запросы………………………………………………………….36

     II.3. Функции Oracle SQL…………………………………………………..…………...39

         II.3.1.  Некоторые символьные функции……………………………………………….39

         II.3.2.  Некоторые функции даты и времени…………………………………………...43

         II.3.3.  Некоторые функции преобразования…………………………………………..46

         II.3.4.  Функции DECODE, DUMP, ORA_HASH, VSIZE……………………………..51

         II.3.5.  Агрегатные и аналитические функции…………………………………………55

    II.4. Другие команды языка манипулирования данными DМL и  обработка

    транзакций…………………………………………………………………………………...62

         II.4.1. Команды DML insert, update, delete……………………………………………...62

         II.4.2. Обработка транзакций……………………………………………………………67

    II.5. Создание и изменение структуры таблиц средствами DDL………………………...68

    II.6. Другие объекты базы данных…………………………………………………………74

Глава III. Общая последовательность этапов выполнения лабораторных работ по освоению языка PL/SQL Oracle…………………………………………………..……………80

Библиографический список…………….……………………………………………………83

Приложение 1.  Скрипты создания предметной области.. .…………………………………84

Приложение 2.  Содержимое таблиц пользователя DEMO...………………………………..96

Приложение 3.  Решения отдельных задаx SQL олимпиады………………………………107

Предисловие

    Учебные курсы «Базы данных» и «Безопасность систем баз данных I» в качестве практической базы для проведения лабораторных работ    по  освоению SQL и процедурного языка СУБД предполагают использование программного продукта корпорации OracleOracle10g EE. Названный программный продукт получен в 2008 г. МИФИ как институтом  участником программы «ORACLE ACADEMY ADVANCED COMPUTER SCIENCE &BUSINESS». В соответствии с договором между МИФИ и корпорацией Oracle, каждый студент кафедр 29 и 43, выполняющий лабораторные работы по названным выше учебным курсам, может установить на домашнем компьютере Oracle10g EE, полученный  МИФИ от Oracle (взяв у преподавателя соответствующую DVD копию) , но при этом от студента требуется регистрация на сайте http://oai.oracle.com/pls/oai/sel onchange.main?cmd=s set lang. Студенты, не желающие регистрироваться, для выполнения лабораторных работ могут воспользоваться установкой разрешенной к практически неограниченному использованию версией Oracle10g Express Edition, скачав ее с сайта www.oracle.com.

    Следует подчеркнуть важность установки студентом СУБД Oracle на домашнем компьютере для обеспечения возможности самостоятельной работы.  Необходимость самостоятельной работы объясняется  насыщенностью того материала, который должен осваивать студент на занятиях в дисплейном классе. Кто-то из великих ученых сказал: «..понять, значит – привыкнуть». Времени занятий в дисплейном классе, конечно, не хватает для того, чтобы «привыкнуть» к технологиям Oracle. Пожалуй, основная сложность, которую приходится преодолевать при освоении технологий современной СУБД,  такой, как Oracle – гигантская размерность этого программного продукта, интегрирующего практически все современные информационные технологии.  Предлагаемые в настоящем учебно-практическом издании лабораторные работы при их осмысленном выполнении, в сочетании с лекционным материалом  постепенно, шаг за шагом вводят студента в эту гигантскую размерность с тем, чтобы далее студент мог двигаться в интересующем его направлении уже в значительной степени самостоятельно.

Глава I.  Общая последовательность этапов выполнения лабораторных работ по освоению языка SQL

1. Получение от преподавателя схемы (Entity Relationship диаграммы) и скриптов (на языке SQL) индивидуальной предметной области для выполнения лабораторных работ.

2. Ознакомление с примерами выполнения SQL запросов по шести разным темам на предметной области пользователя SCOTT, устанавливаемой при стандартной инсталляции СУБД Oracle.

Примеры выполнения запросов по этим шести темам представлены в главе 2. «Образец для выполнения лабораторных работ по освоению SQL Oracle» настоящего учебного пособия.

Содержание тем:

Тема 1. Утилита SQL*Plus, ее настройка и работа с ней при выполнении простых запросов выборки столбцов и строк. Тема 1 включает инструкцию по созданию пользователя, наделению его необходимыми полномочиями и созданию таблиц предметной области.

Тема 2. Выборка данных из нескольких таблиц.

Тема 3. Функции Oracle SQL.

Тема 4. Другие команды языка манипулирования данными DМL и обработка транзакций.

Тема 5. Создание и изменение таблиц средствами DDL.

Тема 6. Другие объекты базы данных.

3. Выполнение на заданной преподавателем по п.1 предметной области SQL запросов по шести приведенным в п.2. темам. Запросы должны быть такого же типа и с таким же оформлением, как и в лаве 2. «Образец для выполнения лабораторных работ по освоению SQL Oracle» настоящего учебного пособия.

    При выполнении лабораторных работ студенту может потребоваться выполнить корректировку «своей» предметной области (добавить столбец в таблицу, определить его внешним ключем, заполнить его; добавить новую таблицу и заполнить ее; заполнить новыми данными ранеее существовавшую таблицу и т.д.). Все эти корректировки должны быть скомпанованы в отдельном скрипте, который, как и spool –файлы (протоколы выполнения) лабораторных работ, предъявляются преподавателю при сдаче лабораторных работ.

    Лабораторные работы могут выполняться студентом вне дисплейного класса, а на занятиях – предъявляться преподавателю для их сдачи. Лабораторные работы по освоению SQL завершаются в первой половине семестра написанием контрольной работы «SQL запросы»  (на седьмой – восьмой неделях семестра). Во второй половине семестра  выполняются лабораторные работы, связанные с освоением процедурного языка программирования Oracle  PL/SQL.

    Программами учебных курсов «Базы данных» и «Безопасность систем баз данных I» предусмотрено 2 часа аудиторных  лабораторных занятий в неделю – для каждого студента.   В ходе лабораторной работы на начальном ее этапе определенное время тратится на запуск приложений Oracle, подготовку студентом изменений в базе данных, необходимых для демонстрации преподавателю выполненных лабораторных работ (создание пользователя, наделение его необходимыми привилегиями, создание объектов схемы этого пользователя, выполнение скриптов задания). Чтобы уменьшить относительный вес этих накладных расходов, график выполнения лабораторных работ по названным учебным курсам предусматривает  длительность аудиторной лабораторной работы для каждого студента  четыре академических часа раз в две недели. Поэтому  на каждом  из первых трех занятий (шесть первых недель семестра)  студент должен сдавать преподавателю лабораторные работы по двум темам, чтобы к контрольной работе по SQL выполнить все шесть тем.

    В изложении материала по SQL лабораторным будут использованы общая последовательность подачи материала при освоении языка SQL, семантика многих языковых конструкций SQL, представленная в /3/  с корректировками на другую, усложненную предметную область и последующие версии Oracle, а также ссылки на /1/,/2/,/4/.

Глава II.  Образец для выполнения лабораторных работ по освоению SQL Oracle

II.1. Утилита SQL*Plus, ее настройка и работа с ней при выполнении простых запросов выборки столбцов и строк

    II.1. 1. Команды утилиты SQL*Plus и ее настройка

SQL*Plus представляет собой инструмент, с помощью которого можно  интерактивно или в пакетном режиме определять данные, управлять ими в базах данных Oracle, выполнять команды SQL, PL/SQL программы.  

    Алфавитный список команд SQL*Plus включает до 50 команд (file:///D:/Ora10.2_.doc/server.102/b14356/toc.htm#CFAJHBCH)1). В этом разделе мы

1) Эту строку надо вставлять, как URL, в браузере, изменив «D:/Ora10.2_.doc» на путь к документации Oracle10g на своем компьютере»

приведем минимум команд SQL*Plus, необходимых для выполнения лабораторных работ.  

В табл.  II.1 приведены наиболее часто используемые команды SQL*Plus.

Таблица II.1

Команда

Назначение

SPOOL путь и имя файла

Инициализирует запись протокола работы SQL*Plus в текстовый файл, расположенный по указанному пути

SPOOL OFF|ON  

   

OFF ION отключает/возобновляет запись протокола работы SQL*Plus в текстовый файл

DESC[RIBE] имя таблицы

Выводит в окне SQL*Plus структуру таблицы

HOST команда ОС

Выполняет команду операционной системы из SQL*Plus

CONNECT имя/пароль

Соединение с сервером  Oracle под другим именем/паролем

COL[UMN] название_столбца FORMAT An

При выводе столбца символьного типа с указанным именем его выводимый размер примет значение n символов

COL[UMN] название_столбца FORMAT 9--9.9--9

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

ED|IT

Вызов редактора, в котором можно править последнюю  команду в буфере SQL*Plus

EXIT

Выход из SQL*Plus

SET LINESIZE N

Устанавливает длину выводимой строки в n символов

SET PAGESIZE N

Устанавливает размер выводимой страницы, равным n строк

SET SERVEROUTPUT ON

Обеспечивает возможность работы пакета (программного модуля)

DBMS_OUTPUT, обеспечивающего вывод информации при работе программ PL/SQL

SET SQLPROMPT

текст

Выводит подсказку «текст»

SET ECHO {ON | OFF}

Устанавливает или прекращает вывод в окне SQL*Plus выполненяемых команд в sql скриптах

SET TERM[OUT] {ON | OFF}

Устанавливает или прекращает вывод в окне SQL*Plus результатов выполнения команд в sql скриптах

SAVE название скрипта

Запоминание скрипта последнего запроса в файле «название скрипта»

@название SQL скрипта

Запустить на выполнение SQL скрипт

   

     Команды в SQL*Plus пишутся сразу после выводимой подсказки (сразу после установки – это «SQL>»). Регистр написания команд SQL*Plus не важен. Отметим здесь, что команды языка SQL и команды SQL*Plus – не одно и то же. В отдельных частях команд SQL, там, где задаются значения строк или столбцов хранимых таблиц,  регистр важен.     

    Команды SQL, вводимые в окне SQL*Plus, могут занимать несколько строк,  заканчиваются точкой с запятой и выполняются после нажатия клавиши «Enter». Другой способ выполнения: на последней строке SQL команды ввести символ «/» и нажать клавишу «Enter». Последняя введенная команда хранится в буфере SQL*Plus.

    SQL*Plus можно запускать в интерфейсе Windows (D:\oracle\product\10.2.0\db_1\BIN\ sqlplusw.exe)1), а также – в окне DOS (D:\oracle\product\10.2.0\db_1\BIN\ sqlplus.exe).

Ссылка на SQL*Plus в интерфейсе Windows есть в панели задач отдельной строкой, откуда его обычно и запускают. Для запуска SQL*Plus в окне DOS достаточно выполнить в нем комаду sqlplus без указания пути, предварительно выбрав в свойствах окна DOS шрифт TT Lucida Console и изменив кодовую страницу командой «chcp 1251».

В этом случае выводимые на кириллице сообщения будут нормально читаться (обычно названные настройки дают результат для русскоязычной ОС Windows; для некоторых версий ОС Windows могут потребоваться другие языковые настройки). 

    Нижеприводимый раздел настройки SQL*Plus (выделен синим цветом)  выполняется  для Oracle9i на первой лабораторной работе совместно с преподавателем.

    Зачастую при работе с SQL*Plus важно знать, какой пользователь работает с базой данных и с какой базой данных он работает. У автора настоящего пособия на компьютере постоянно находится несколько баз данных с разными версиями Oracle (Oracle8i, Oracle9i, Oracle10g). Для того, чтобы иметь ответы на эти вопросы, достаточно изменить подсказку по умолчанию в окне SQL*Plus (SQL>) на подсказку из двух частей: имени пользователя и имени базы, разделенных знаком, например , «&». Способ такой настройки указан в /1/. Приведем соответствующий текст из /1/ с незначительной корректировкой, связанной с использованием автором разных версий  Oracle.

Для обеспечения запуска sqlplusw.exe или sqlplus.exe с такой подсказкой  создаются

два файла: glogin.sql, connect.sql.

    Скрипт glogin.sql, расположенный в директории D:\oracle\product\10.2.0\db_1\sqlplus\admin автоматически выполняется при каждом запуске SQL*Plus. Вместо скрипта glogin.sql, создаваемого при установке Oracle,  создается и перезаписывается на то же место новый скрипт с тем же именем, имеющий следующий вид:

set serveroutput on size 1000000

set trimspool on

set long 5000

set linesize 100

set pagesize 9999

column global_name new_value gname

set termout off

select lower(user) || ' & ' ||

decode(global_name, ' ORCL.REGRESS.RDBMS.DEV.US.ORACLE.COM', '10g',

'NINE.US.ORACLE.COM', 'NINE', 'EIGH.KAF29.MEPHI.RU','8i', global_name) global_name from global_name;

set sqlprompt '&gname>

set termout on

В этом сценарии:

• SET SERVEROUTPUT ON SIZE 1000000 включает по умолчанию поддержку

пакета DBMS_OUTPUT (чтобы не нужно было набирать эту команду каждый раз).

Устанавливает также максимально возможный размер буфера.

• SET TRIMSPOOL ON гарантирует, что в выдаваемом тексте хвостовые пробелы

будут отсекаться. Если используется стандартное значение этой установки, OFF,

выдаваемые строки будут иметь длину LINESIZE.

• SET LONG 5000 устанавливает стандартное количество байт, выдаваемых при

выборе столбцов типа LONG и CLOB.

1) Здесь и далее указанный путь предполагает, что при установке Oracle в строку «Oracle Home» было введено значение «D:\oracle\product\10.2.0\db_1\». Для другого значения Oracle Home изменится путь к файлам sqlplusw.exe, sqlplus.exe, а также – к некоторым другим файлам, называемым ниже по тексту.

• SET LINESIZE 100 устанавливает длину выдаваемой утилитой SQL*Plus строки

равной 100 символам.

• SET PAGESIZE 9999 задает значение PAGESIZE, определяющее, как часто ути-

лита SQL*Plus выдает заголовки, настолько большим, чтобы на странице было не

более одного заголовка.

column global_name new value gname

Эта директива заставляет утилиту SQL*Plus помещать последнее значение, извлечен-

ное из любого столбца с именем GLOBAL_NAME, в переменную подстановки GNAME.

Затем выполняется следующий запрос:

select lower(user) || ' & ' ||

decode(global_name, ' ORCL.REGRESS.RDBMS.DEV.US.ORACLE.COM', '10g',

'NINE.US.ORACLE.COM', 'NINE', 'EIGH.KAF29.MEPHI.RU','8i', global_name) global_name from global_name;

Он выбирает из базы данных значение GLOBAL_NAME, используя функцию

DECODE для присваивания требуемых имен одному или нескольким обычно исполь-

зуемым экземплярам, а затем конкатенирует его с именем текущего пользователя. На-

конец, мы отображаем эту информацию в приглашении SQL*Plus:

set sqlprompt '&gname> '

Поэтому приглашение для соединяющегося с сервером Oracle9i (если при установке Oracle9i была создана  база данных NINE и другая база данных не создавалась) пользователя scott  будет иметь вид:

scott@NINE>

Таким образом известно, кто работает и с какой базой работает.  Еще один очень полезный скрипт— это скрипт connect.sql, который надо разместить в директории

ORACLE_HOME\BIN (вместо ORACLE_HOME надо поставить действительное значение этого параметра):

set termout off

--для соединения sys надо заключать

--sys/sys as sysdba в апострофы;

--в других случаях этого делать не надо

connect &1

@ORACLE_HOME \sqlplus\admin\glogin.sql  -- вместо ORACLE_HOME надо поставить  

                                                                           --действительное значение этого параметра

set termout on

Утилита SQL*Plus будет автоматически выполнять скрипт glogin.sql при начальном запуске. Но если в окне SQL*Plus с сервером Oracle будет соединяться другой пользователь командой connect имя_пользователя/пароль_пользователя,  скрипт glogin.sql автоматически выполняться не будет.   В окне SQL*Plus после соединения другого пользователя с сервером Oracle останется прежняя подсказка.

Если же вместо connect имя_пользователя/пароль_пользователя ввести

@connect имя_пользователя/пароль_пользователя, будет выполнен скрипт connect.sql, а в нем - команда SQL*Plus connect с параметром имя_пользователя/пароль_пользователя (строка «connect &1»  скрипта connect.sql), а затем -  скрипт glogin.sql (строка @ORACLE_HOME \sqlplus\admin\glogin.sql скрипта connect.sql) и нужная подсказка будет установлена.

Для Oracle10g вышеприводимая настройка упрощается.

     Скрипт glogin.sql, вместо строк:

column global_name new_value gname

set termout off

select lower(user) || ' & ' ||

decode(global_name, ' ORCL.REGRESS.RDBMS.DEV.US.ORACLE.COM', '10g',

global_name) global_name from global_name;

set sqlprompt '&gname>

включает строку:

set sqlprompt "_USER'@'_CONNECT_IDENTIFIER>"

    Здесь _USER и _CONNECT_IDENTIFIER  - предопределенные (predefined) переменные SQL*Plus Release 10.2, первая из которых определяет текущего пользователя, вторая – идентификатор соединения, который обычно совпадает с первой частью (в нашем случае ORCL) глобального имени базы данных (в нашем случае ORCL.REGRESS.RDBMS.DEV.US.ORACLE.COM).

    В число предопределенных переменных SQL*Plus Release10.2, помимо названных, входят:

_DATE

Теущая или определенная пользователем фиксированной строкой дата.

_EDITOR

Определяет редактор, используемый командой  EDIT 

_O_VERSION

Текущая версия установленной базы данных Oracle.

_O_RELEASE

Полный номер версии установленной базы данных  Oracle 

_PRIVILEGE

Уровень привилегий текущего соединения (sysdba,sysoper или обычный пользователь

_SQLPLUS_RELEASE

Полный номер версии SQL*Plus

    Файл glogin.sql приобретает такой, например,  вид:

set serveroutput on size 1000000

set trimspool on

set long 5000

set linesize 200

set pagesize 9999

set sqlprompt "_USER'@'_CONNECT_IDENTIFIER _PRIVILEGE> "

-- Used for the SHOW ERRORS command

column LINE/COL format A8

column ERROR    format A65  WORD_WRAPPED

-- Used for the SHOW SGA command

column name_col_plus_show_sga format a24

-- Defaults for SHOW PARAMETERS

column name_col_plus_show_param format a36 heading NAME

column value_col_plus_show_param format a30 heading VALUE

-- Defaults for SET AUTOTRACE EXPLAIN report

column id_plus_exp format 990 heading i

column parent_id_plus_exp format 990 heading p

column plan_plus_exp format a60

column object_node_plus_exp format a8

column other_tag_plus_exp format a29

column other_plus_exp format a44

set echo on

set termout on

    Для Oracle10g достаточно разместить этот файл в папке ORACLE_HOME\sqlplus\admin

и SQL*Plus будет выдавать требуемую  подсказку без использования файла connect.sql.

II.1. 2. Создание пользователя Oracle и таблиц его схемы

    Большинство примеров по освоению SQL запросов в настоящем пособии «привязаны» к незначительно скорректированной предметной области пользователя DEMO, поставлявшейся в ранних версиях Oracle. ERD диаграмма этой предметной области представлена на рис.1.  Корретировка заключается в сокращении размера нескольких записей в таблице «CUSTOMER», сокращении размера трех столбцов и удалении одного столбца этой таблицы – для обеспечения более удобного для восприятия вывода

          

          Рис.1 Server Model Diagram предметной области, используемой  

                      в этом учебном пособии для освоения SQL


информации  из этой таблицы.  Скрипты создания и наполнения таблиц пользователя DEMO представлены в приложении 1.  

    Создание предметной области состоит из нескольких шагов:

  1.  Создание пользователя
  2.  Назначение привилегий пользователю

      с.  Соединение созданного пользователя с Oracle и запуск на исполнение скрипта,  

    создающего и заполняющего таблицы предметной области

    

    Для создания пользователя DEMO надо «войти» в SQL*Plus пользователем SYSTEM. По умолчанию при установке Oracle этот пользователь имеет пароль manager (отметим

здесь, что регистрозависимый пароль «появился» только в Oracle11g, поэтому пароль, как и имя пользователя, можно вводить на любом регистре). Подсказка «system@10g» появится, если предвариательно создать и разместить в соответствующем месте файл glogin.sql в соотвтетствии с п. II.1.1.

  

system@10g> Create user demo identified by demo

Default tablespace users

Temporary tablespace temp

Quota 1m on users;

Пользователь создан.

system@10g> grant connect, resource, create view to demo;

Привилегии предоставлены.

Эта команда позволит пользователю DEMO соединяться с сервером Oracle и создавать объекты (таблицы, программные объекты, представления и т.д.) базы данных.

system@10g> @connect demo/demo

Эта команда будет выполнена успешно, если предварительно выполнена настройка утилиты SQL*Plus в соответствии с пунктом II.1. 1. (созданы и размещены в соответствующих местах файлы glogin.sql и connect.sql)

demo@10g>

    Пользователь DEMO теперь должен запустить скрипт создания и заполнения таблиц.

Чтобы такой скрипт «появился», скопируйте текст приложения 1 в блокнот (notepad.exe)  и разместите его в папке c:\tmp, например, с именем demo.sql. После этого выполните команду:

demo@10g>@c:\tmp\demo.sql

  II.1.3.Переменные привязки  в среде SQL*Plus

В SQL*Plus можно также выделять именованную область памяти для хранения некоторой информации. Такая область используется внутри программ PL/SQL и SQL-операторов, однако находится вне программных блоков, поэтому можно по очереди выделять ее разным программным блокам и после выполнения каждого из них выводить ее содержимое. Эта именованная область памяти называется переменной привязки /2/ (связываемой переменной /1/) bind variable. Переменная привязки (в приведенном ниже примере «var1») в SQL*Plus выделяется с помощью команды VARIABLE, которая действует только тогда, когда указана в ответ на подсказку SQL*Plus, но не внутри программы PL/SQL. Внутри нее переменная привязки ограничивается двоеточием.

Q1_1  (Query 1 по теме 1)  Создание, обращение к и вывод переменной привязки

DEMO@10G>variable varl number

DEMO@10G>begin

   :varl:=10;

   end;

/

PL/SQL procedure successfully completed.

DEMO@10G>print var1

 VA R1                                                                                           

-----                                                                                           

 10

Использование переменных привязки в работе программных кодов с повторяющимися циклами  способствует эффективному выполнению  этих кодов.

                                                                                      

II.1.4.Переменные подстановки  в среде SQL*Plus

Q1_2 (Query 2 по теме 1)  Использование переменной подстановки («table_name» в данном случае) для имени таблицы и значения столбца («n_dept» в данном случае) DEPARTMENT_ID в ней при извлечении информации . Знак «&» предшествует имени переменной подстановки

demo@10g> demo@10g> select * from &table_name where DEPARTMENT_ID=&n_dept;

Введите значение для table_name: department

Введите значение для n_dept: 10

прежний   1: select * from &table_name where DEPARTMENT_ID=&n_dept

новый   1: select * from department where DEPARTMENT_ID=10

Q1_3 Использование переменной подстановки («1» в данном случае) для значения столбца JOB_ID

demo@10g> select EMPLOYEE_ID,LAST_NAME,SALARY from EMPLOYEE

   where JOB_ID='&1';

Введите значение для 1: 669

прежний   2: where JOB_ID='&1'

новый   2: where JOB_ID='669'

Сохраним последний запрос в файл Q3_1.sql

demo@10g> save Q1_3

Создано file Q1_3.sql

Посмотрим значение параметра SQL*Plus  termout

demo@10g> show termout

termout OFF

Параметр отключен

Посмотрим значение параметра SQL*Plus  echo

demo@10g> show echo

echo OFF

Параметр отключен. Заметим здесь: если в ответ на эту команду результат «echo ON» для совпадения результатов выполнения последующих команд с приведенными в тексте выполните команду «set echo off».

Выполним скрипт Q1_3 с вводимым значением столбца JOB_ID = 669

demo@10g> @Q1_3 669

demo@10g>

Скрипт выполнен, но ни выполняемая команда SQL, ни результат выполнения этой команды SQL*Plus не вывел

Задействуем параметр termout и выполним скрипт Q1_3 с вводимым значением столбца JOB_ID = 669 еще раз

demo@10g> set termout on

demo@10g> @Q1_3 669

прежний   2: where JOB_ID='&1'

новый   2: where JOB_ID='669'

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

Задействуем теперь параметр echo и выполним скрипт Q1_3 с вводимым значением столбца JOB_ID = 669 еще раз

demo@10g> set echo on

demo@10g> @Q1_3 669

demo@10g> select EMPLOYEE_ID,LAST_NAME,SALARY from EMPLOYEE

 2  where JOB_ID='&1'

 3  /

прежний   2: where JOB_ID='&1'

новый   2: where JOB_ID='669'

Выведены SQL команда и результат  ее выполнения

Отметим здесь, что при отключенном параметре  termout вывод полностью отключается вне зависимости от значения параметра echo

demo@10g> set termout off

demo@10g> @Q1_3 669

demo@10g>

Не выводится не только результат выполнения команды, но и сама команда, не смотря на установленный в «ON» параметр echo       

Q1_4 Использование переменных подстановки («loc_number», «reg_group» в данном случае) для вставки значений столбцов LOCATION_ID, REGIONAL_GROUP в таблицу location

demo@10g> insert into location(LOCATION_ID,REGIONAL_GROUP) values(&loc_number,&reg_group);

Введите значение для loc_number: 125

Введите значение для reg_group: 'МОСКВА'

прежний   1: insert into location(LOCATION_ID,REGIONAL_GROUP) values(&loc_number,&reg_group)

новый   1: insert into location(LOCATION_ID,REGIONAL_GROUP) values(125,'МОСКВА')

1 строка создана.

Для возврата содержимого таблицы к исходному состоянию ликвидируем последнюю вставку командой «rollback»

demo@10g>rollback;

откат завершен

Q1_5  Использование двойного знака «&&» для переменной подстановки («table_name» в данном случае). Значение введенной после сдвоенного  знака «&» переменной подстановки запоминается в SQL*Plus,  и в следующий раз при обращении к этой переменной подстановки ее значение вводится автоматически

demo@10g> select * from &&table_name;

Введите значение для table_name: location

прежний   1: select * from &&table_name

новый   1: select * from location

demo@10g> select * from &&table_name;

прежний   1: select * from &&table_name

новый   1: select * from location

Здесь уже отсутствует приглашение для ввода значения table_name.

                                                        

Покажем теперь, как изменить значение для переменной подстановки «table_name».

Сначала выведем текущее значение этой перменной:

demo@10g> define table_name        

DEFINE TABLE_NAME      = "location" (CHAR)

demo@10g>

Теперь присвоим этой перменной подстановки новое значение:

demo@10g> DEFINE TABLE_NAME      = "DEPARTMENT"

demo@10g> select * from &&table_name;

прежний   1: select * from &&table_name

новый   1: select * from DEPARTMENT

II.1.5. Выборка столбцов таблицы

Q1_6  Выборка всех столбцов из таблицы

demo@10g> select * from department;

Q1_7  Использование арифметических выражений.

Зарплата за 12 месяцев

demo@10g> select last_name,salary*12, commission from employee;

Q1_8  Интерпретация NULL-значений столбца (NULL - значение в таблицах Oracle понимается, как отсутствие значения) COMM, как числа  "0"

Вывод годового дохода

demo@10g> select last_name,salary*12+NVL(commission,0) year_sal from employee;

Функция nvl() вернет 0(число «ноль»), если значение в столбце «comission» - NULL и значение этого столбца – в противном случае.

Q1_9  Сцепление столбцов DEPARTMENT_ID и NAME

demo@10g> select department_id||name,location_id from department;

 

Q1_10  Использование литералов (набора символов) для формирования столбца

demo@10g> select ' Department ', name, 'settled in region', location_id from department;

Q1_11 Исключение повторяющихся строк с использованием классификатора DISTINCT

demo@10g> select distinct job_id,salary from employee;

Q1_12  Использование псевдонимов столбцов

demo@10g> select name НАЗВАНИЕ_ОТДЕЛА, location_id РАСПОЛОЖЕНИЕ from department;

Q1_13  Упорядочение строк результата запроса с использованием ORDER BY

demo@10g> select department_id, name from department order by name;

Или иначе:

demo@10g> select department_id, name from department order by 2;

Q1_14 Сортировка в порядке убывания

demo@10g> select department_id, name from department order by 2 desc;

Q1_15 Сортировка по нескольким столбцам

demo@10g> select department_id, name from department order by 2 desc,1;

II.1.6. Выборка строк таблицы

Условия выборки во фразе WHERE

Q1_16  Выборка всех служащих из отдела 10

demo@10g> select * from employee

   where department_id=10;

Q1_17   Выбор сотрудников, у которых нет руководителя

Select * from employee where manager_id is null;

demo@10g> Select * from employee where manager_id is null;

Q1_18   Выбор сотрудников, принятых на работу не в 1985 году

demo@10g> Select * from employee where to_char(hire_date,'yy') !=85;

Q1_19   Выбор сотрудников у которых комиссионные больше оклада

demo@10g> select first_name,last_name,department_id deptno,job_id from employee

       where commission > salary

Q1_20  Выбор сотрудников у которых оклад меньше или равен 1900 и больше или равен 1300

demo@10g> Select first_name, last_name,salary from employee

   Where salary between 1300 and 1900;

Q1_21  Выбор сотрудников , номер отдела которых 10 или 30

demo@10g> Select first_name, last_name,job_id from employee

   where department_id in(10,30);

Q1_22  Выбор сотрудников у которых имя начинается с «С»

demo@10g> Select first_name, last_name,job_id from employee

   where first_name like 'C%';

Q1_23   Выбор сотрудников у которых коммерческие не меньше 500

demo@10g> Select first_name, last_name,job_id,commission from employee

   where not commission < 500;

Q1_24   Выбор сотрудников у которых определено поле COMMISSION ( не NULL) и которые работают в отделе 10 - использование усложненного критерия поиска с объединением AND

demo@10g> Select first_name, last_name,job_id,commission from employee

   where department_id=10 and commission is not null;

Q1_25  Выбор сотрудников у которых определено поле COMMISSION ( не NULL) и которые работают в отделе 30 или 10 - использование усложненного критерия поиска с OR и AND

demo@10g> Select first_name, last_name,job_id,commission from employee

  where (department_id=10 or department_id=30) and commission is not null

Q1_26  Использование select-а в select-е или вложенный подзапрос в select-е.

Вывести номер, имя и название отдела служащего.

demo@10g> select employee_id, last_name, (select name  from department

      where department.department_id=employee.department_id) dname from employee;

II.2. Выборка данных из нескольких таблиц

II.2.1. Подзапросы

Q2_1 Найти служащих , получающих минимальную зарплату

demo@10g> select last_ name,salary from EMPLOYEE

where salary=(select min(salary) from EMPLOYEE);

В запросе использована агрегатная функция min(salary), которая возвращает минимальное значение столбца «salary».

Q2_2  Найти сотрудников отделов продаж (SALES) , занимающих ту же должность , что и служащий SMITH

 select employee_id,last_name,job_id,salary

 from EMPLOYEE

 where job_id=(select job_id

               from EMPLOYEE

               where last_name='SMITH')

               and department_id in (select department_id

                                                   from DEPARTMENT

                                                   where name='SALES');

Q2_3  Найти служащих, получающих минимальный оклад в своем отделе

demo@10g>  select DEPARTMENT_ID dept,last_name name,salary

                      from EMPLOYEE

                      where (salary,DEPARTMENT_ID) in (

                      select min(salary),DEPARTMENT_ID

                      from EMPLOYEE

                      group by DEPARTMENT_ID);

Подзапрос «select min(salary),DEPARTMENT_ID from EMPLOYEE group by DEPARTMENT_ID» рассмотрим отдельно, так как здесь впервые в приводимых примерах  использована опция «group by».

demo@10g> select min(salary),DEPARTMENT_ID

from EMPLOYEE

group by DEPARTMENT_ID ;

MIN(SALARY) DEPARTMENT_ID

----------- -------------

       950            30

       750            13

      1800            14

       800            20

       750            23

      1300            10

       800            12

7 строк выбрано.

Казалось бы, добавить к перечню извлекаемых столбцов «last_name», и - нужный результат будет получен

demo@10g> select last_name, min(salary),DEPARTMENT_ID

from EMPLOYEE

group by DEPARTMENT_ID;

demo@10g> /

select last_name, min(salary),DEPARTMENT_ID

      *

ошибка в строке 1:

ORA-00979: выражение не является выражением GROUP BY

Но получено сообщение об ошибке, так как если требуется выводить минимум значения зарплаты по сгруппированным строкам, в опции GROUP BY надо указывать все столбцы (кроме столбца salary, конечно, по которому ищется минимум), составляющие выводимые строки. Подкорректируем запрос с учетом полученной ошибки

demo@10g> select last_name, min(salary),DEPARTMENT_ID

from EMPLOYEE

group by DEPARTMENT_ID,last_name;

Вот так! Фамилии-то в отделах разные, поэтому мы получили 32 группы строк по одной строке в каждой из них. Для одной строки минимум и выводится, совпадающий со значением зарплаты в этой строке. Изменим значение столбца «last_name», установив в нем одинаковое значение для всех 32-х строк

demo@10g> update employee set last_name='Иванов';

и повторим предыдущий запрос

select last_name, min(salary),DEPARTMENT_ID

from EMPLOYEE

group by DEPARTMENT_ID,last_name;

Вот теперь, так как фамилии в отделах одинаковые, запрос извлекает семь групп

одинаковых строк, «принадлежащих» каждая одному отделу. И уже внутри каждой из таких групп строк ищется min(salary).

!!!Вернем столбцу last_name в таблице  EMPLOYEE прежние значения

demo@10g> rollback;

Q2_4 Получить перечень  сотрудников отдела 14, получающих оклад больше минимального в отделе 12

demo@10g> select last_name,salary,job_id,department_id dept

   from EMPLOYEE

   where department_id=14 and

   salary > (select min(salary) from EMPLOYEE

                 where department_id=12)

   order by salary,last_name;

Q2_5 Получить перечень  сотрудников отдела 12, получающих оклад больше любого в отделе 14

demo@10g> select last_name,salary,job_id,department_id dept

   from EMPLOYEE

   where department_id=12 and

   salary > any(select distinct salary from EMPLOYEE

                 where department_id=14)

   order by salary,last_name;

Часть запроса « …salary > any()…» означает условие: зарплата больше любого значения в скобках.

Q2_6 Получить отделы, в которых число сотрудников больше 2-х и средний оклад больше среднего оклада отдела 14

demo@10g> select department_id dept,round(avg(salary),0) from EMPLOYEE

   group by department_id

   having count(employee_id) > 2

   and avg(salary) > (select avg(salary) from EMPLOYEE

                       where department_id = 14);

Здесь использованы агрегатные функции avg() и count(), а также – опция having, смысл коорой понятен из текста запроса.

Q2_7 Определить год когда в компанию было зачислено наибольшее количество служащих

demo@10g> select to_char(hire_date,'YYYY') year,count(employee_id) num

    from EMPLOYEE

    group by to_char(hire_date,'YYYY')

    having count(employee_id) = (select max(count(employee_id))

                                   from EMPLOYEE

                                   group by to_char(hire_date,'YYYY'));

II.2.2 Коррелированные подзапросы

Q2_8 Получить список сотрудников, зарплата которых превышает  среднюю в отделе, где они работают

demo@10g>select employee_id,last_name name,salary,department_id

   from EMPLOYEE x

   where salary > (select avg(salary) from EMPLOYEE

where department_id = x.department_id)

   order by department_id,salary desc;

В запросе используется таблица EMPLOYEE с алиасом «x» и копия этой таблицы. Последовательность действий Oracle при отработке запроса такова: извлекается первая строка из таблицы с алиасом «x», из этой строки извлекается номер отдела, для этого номера отдела в копии таблицы подсчитывается средняя зарплата и если зарплата в извлеченной строке оказывается выше подсчитанной средней, соответсвующие столбцы из извлеченной строки «укладываются» в результат, вслед за тем извлекается вторая строка из т аблицы с алиасом «x» и т.д. – в цикле.

Q2_9  Получить список сотрудников, имеющих хотя бы одного подчиненного

demo@10g> select EMPLOYEE_ID,last_name,job_id,DEPARTMENT_ID

from EMPLOYEE x

where exists (select 1 from EMPLOYEE

                 where EMPLOYEE.manager_id = x.EMPLOYEE_ID)

order by DEPARTMENT_ID;

Q2_10 Сотрудники, не имеющие подчиненных.

demo@10g> select EMPLOYEE_id,last_name,job_id,Department_id

   from EMPLOYEE

   where EMPLOYEE_id not in (select manager_id from EMPLOYEE);

строки не выбраны

Запрос отработан неверно, так как одно из значений внутреннего запроса – NULL значение, отчего отработка условия «…EMPLOYEE_id not in (select manager_id from EMPLOYEE)» ничего не извлекает для EMPLOYEE_id.  Это касается только сравнения «not  in».  Для «…EMPLOYEE_id  in (select manager_id from EMPLOYEE)» запрос отработал бы верно (смысловое значение запроса в этом случае было бы иное: сотрудники, имеющие подчиненных), несмотря на присутствие NULL значения во внутреннем селекте.

Правильно для «not in» надо сделать так:

demo@10g> select EMPLOYEE_id,last_name,job_id,Department_id

   from EMPLOYEE

   where EMPLOYEE_id not in (select nvl(manager_id,0) from EMPLOYEE);

 

Другой вариант отработки  этого запроса:

   

demo@10g> select EMPLOYEE_ID,last_name,job_id,DEPARTMENT_ID

from EMPLOYEE x

where not exists (select manager_id from EMPLOYEE

                     where manager_id = x.EMPLOYEE_ID)

order by DEPARTMENT_ID;

Q2_11

Коррелированный  подзапрос при использовании оператора UPDATE.

Создадим таблицу EMPLOYEE1:

demo@10g> create table EMPLOYEE1 as select * from EMPLOYEE;

Таблица создана.

Добавим столбец к таблице EMPLOYEE1:

demo@10g> alter table EMPLOYEE1 add(dname varchar2(14));

Таблица изменена.

А теперь заполним значениями новую колонку:

demo@10g> update EMPLOYEE1 x

set dname=(select name from department

where department_id=x.department_id);

32 строк обновлено.

demo@10g> drop table EMPLOYEE1;

Таблица удалена.

2.3. Использование подзапросов во фразе FROM

Q2_12  Сформировать  отчет: год продаж,  относительная (в процентах) доля денежных средств, получаемых фирмой от продаж в этом году по отношению к общей сумме продаж за все годы, относительная (в процентах) доля количества покупок в этом году по отношению к общему количеству покупок за все годы.

demo@10g> select sum_year.year,

round(100* sum_year.sm_year /sum_total.ss_total,2) "% Many",

round(100* count_year.cnt_year / count_all_years.cnt_all_years,2) "% Purchase "

from (select to_char(ship_date,'yyyy') year, sum(total)  sm_year

         from sales_order group by to_char(ship_date,'yyyy') ) sum_year,

        (select sum(total) ss_total from sales_order) sum_total,

        (select to_char(ship_date,'yyyy') year, count(order_id)  cnt_year

         from sales_order group by to_char(ship_date,'yyyy')) count_year,

      (select count(order_id)  cnt_all_years from sales_order) count_all_years

      where sum_year.year= count_year.year;

II.2.4. Операторы над множествами

Q2_13  Пересечение множеств INTERSECT

Определить отделы с одинаковым названием, размещенные в регионах 122, 123.

demo@10g> select name from DEPARTMENT

where location_id=122

intersect

select name from DEPARTMENT

where location_id=123;

NAME

--------------

OPERATIONS

SALES

Q2_14  Объединение множеств UNION.

Определить названия отделов, размещенных в регионах 122 или 123.

demo@10g> select name from DEPARTMENT

where location_id=122

union

select name from DEPARTMENT

where location_id=123;

NAME

------------

ACCOUNTING

OPERATIONS

RESEARCH

SALES

UNION ALL дубликаты не исключаются.

demo@10g> select name from DEPARTMENT

where location_id=122

union all

select name from DEPARTMENT

where location_id=123;

Q2_15  Разность множеств MINUS

Определить названия отделов, размещенных в регионе 122 но не 123.

demo@10g> select name from DEPARTMENT

where location_id=122

minus

select name from DEPARTMENT

where location_id=123;

NAME

-------------

ACCOUNTING

RESEARCH

II.2.5. Объединения (внутренние)

    При внутренних объединениях выводится информация  из извлекаемых столбцов, присутсвующая в таблицах, участвующих в запросе. В  отличие от внутреннего,  при внешнем объединении выводится частичная информация по запросу, даже если в одной из объединяемых таблиц информация отсутствует.  Так, в нижеприводимом запросе  Q2_16  выводится информация, связанная только с  отделом 10, в котором существуют служащие, а информация об отделах 34, 40 в которых служащих нет, не выводится. Внешнее объединение позволяет вывести информацию и об отделах 34, 40, при этом столбцы «last_name», «job_id» будут не заполнены. Как выполнять внешние объединения, мы ознакомимся в разделе II.2.6.

Q2_16  Соединение по равенству таблиц EMPLOYEE, DEPARTMENT по полю DEPARTMENT_ID

demo@10g> select last_name,job_id,name,department.department_id

from EMPLOYEE,DEPARTMENT

where EMPLOYEE.DEPARTMENT_ID=DEPARTMENT.DEPARTMENT_ID

and DEPARTMENT.DEPARTMENT_ID in (10,34,40);

Q2_17  Использование псевдонимов имен таблиц

demo@10g> select last_name,job_id,name

from EMPLOYEE e,DEPARTMENT d

where e.DEPARTMENT_ID=d.DEPARTMENT_ID

and e.DEPARTMENT_ID=10;

Q2_18  Соединения не по равенству

Категория оплаты каждого сотрудника компании

demo@10g> select e.last_name,e.salary,s.grade_id

from EMPLOYEE e,SALARY_GRADE s

where e.department_id=30 and e.salary between s.lower_bound and s.upper_bound;

Q2_19 Соединение таблицы самой с собой

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

demo@10g> select e.last_name name,e.hire_date "date",e.job_id  "job",

m.last_name "boss",m.hire_date "date"

from EMPLOYEE e,EMPLOYEE m

where e.manager_id=m.EMPLOYEE_ID

and e.hire_date < m.hire_date;

Q2_20 Служащие , имеющие оклад больше оклада своего руководителя

demo@10g> select e.last_name name,e.salary sal_emp,

m.last_name boss,m.salary sal_boss

from EMPLOYEE e,EMPLOYEE m

where e.manager_id=m.EMPLOYEE_ID

and e.salary > m.salary;

При использовании традиционного синтаксиса объединения мы указывали в инструкции FROM несколько таблиц, разделяя их запятыми. В новом синтаксисе (соответствующему стандарту ANSI SQL, определенному для SQL/92), начиная с  Oracle9i можно  указывать в

инструкции FROM тип объединения вместе с ключевым словом JOIN. Например, чтобы выполнить внутреннее объединение таблиц DEPARTMENT и LOCATION, можно написать:

       from EMPLOYEE у inner join DEPARTMENT d

Q2_21 Пример использования синтаксиса объединения, использующего JOIN

demo@10g> select l.location_id, d.name, l.regional_group

from DEPARTMENT d inner join LOCATION l

on d.location_id = l.location_id

Другой вариант написания этого же запроса:

demo@10g> select location_id, d.name, l.regional_group

from DEPARTMENT d inner join LOCATION l

using (location_id);

     Следует обратить внимание на то, что к имени location_id извлекаемого общего связывающего таблицы столбца мы уже не добавляем квалификатор («d.» или «l.»).

    Преимущество нового синтаксиса объединения в том, что нет возможности непредумышленно сгенерировать декартово произведение (на контрольных работах студенты нередко допускают эту грубую ошибку). Если указано сово JOIN, необходимо задать условие объединения.  Если декартово произведение действительно необходимо, оно задается явно  (cross join)

Q2_22

demo@10g> select * from location cross join salary_grade;

II.2.6. Внешние объединения

Использование внешнего объединения ( или - соединения) для таблицы EMPLOYEE. Внешнее соединение задается опреатором (+) (символ плюс, заключенный в круглые скобки) и позволяет выбрать строки одной таблицы, для которых в другой таблице нет строк, соответствующих условию соединения. Оператор (+) помещается на той стороне соединяющего условия, которая соответствует таблице с отсутствующими данными. Он предписывает Oracle в случае отсутствия строк, удовлетворяющих условию соединения, возвращать NULL (неопределенное значение) для всех выражений списка выборки, которые содержат имена столбцов таблицы с отсутствующими данными.

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

demo@10g> select d.DEPARTMENT_ID,last_name,job_id,name

from EMPLOYEE e,DEPARTMENT d

where e.DEPARTMENT_ID(+)=d.DEPARTMENT_ID

order by d.DEPARTMENT_ID;

Отделы 34, 40 и их названия представлены, а сотрудников этих отделах в таблице EMPLOYEE нет, как и самих номеров отделов.

Q2_24  В синтаксисе стандарта ANSI SQL, определенного для SQL/92 (начиная с  Oracle9i) можно  указывать в инструкции FROM тип объединения вместе с ключевыми словами LEFT JOIN.

Выполним запрос Q2_16 в варианте внешнего объединения:

demo@10g> select last_name,job_id,name,department_id

from DEPARTMENT left join EMPLOYEE

using (DEPARTMENT_ID)

where department_id in (10,34,40);

Поменяем местами таблицы DEPARTMENT, EMPLOYEE в последнем запросе:

demo@10g> select last_name,job_id,name,department_id

from EMPLOYEE left join DEPARTMENT

using (DEPARTMENT_ID)

where department_id in (10,34,40);

Внешнее объединение не «сработало».  Слева надо вставлять название той таблицы, в которой есть информация (отделы 34, 40). Если мы заменим в последнем запросе «left join» на «right join» внешнее объединение отработает верно:

demo@10g> select last_name,job_id,name,department_id

from EMPLOYEE right join DEPARTMENT

using (DEPARTMENT_ID)

where department_id in (10,34,40);

Если теперь предположить,  что не только есть отделы, в которых нет служащих, но и есть служащие, не прикрепленные ни к какому отделу, то запрос, который бы показал и отделы, в которых нет служащих, и служащих, не прикрепленных ни к какому отделу, должен использовать «full outer join».

Q2_25 Комплексный пример использования объединений (внутренних и внешних).

Извлечь  информацию: фамилия служащего, его должность, фамилия его руководителя, зарплата, категория зарплаты, название отдела

demo@10g> select e.last_name name,function job,m.last_name boss,e.salary sal,s.

grade_id  grade,d.name department

from EMPLOYEE e,EMPLOYEE m,SALARY_GRADE s,DEPARTMENT d,JOB

where e.manager_id = m.EMPLOYEE_ID(+)

and e.salary between s.lower_bound and s.upper_bound

and e.DEPARTMENT_ID = d.DEPARTMENT_ID

and e.job_id=job.job_id

order by s.grade_id desc,e.salary desc,d.DEPARTMENT_ID,e.last_name

II.2.7. Иерархические запросы

Q2_26  Древовидная структура таблицы EMPLOYEE, отражающая подчиненность сотрудников фирмы

demo@10g> select level,DEPARTMENT_ID,EMPLOYEE_ID,last_name,job_id,salary

from EMPLOYEE

connect by prior EMPLOYEE_ID=manager_id

start with manager_id is null;

Где /3/:

CONNECT BY (соединить по) - задает зависимость между родительскими и
дочерними вершинами (строками) иерархии. Если в заданной иерархии
обнаруживается петля, то
Oracle возвращает сообщение об ошибке.

PRIOR (предшествующий) - определяет направление обхода дерева. В примере
задано направление
от родителя к потомку (прочитать эту часть запроса следует так: предшестаующим столбцу EMPLOYEE_id  является столбец MANAGER_ID) . Направлению от потомка к родителю соответствовало бы условие EMPLOYEE_id = PRIOR manager_id или PRIOR manager_id =EMPLOYEE_id.

START WITH (начать с) - если задано, то позволяет указать, с какой вершины
(строки) или вершин (строк) начать построение дерева. То есть задает
корневую (вые) вершины (строки).

         Используя информацию, заданную в этих предложениях, Oracle формирует иерархию следующим образом:

1. Выбирает корневую строку (строки) иерархии в соответствии с условиями
заданными во фразе
START WITH. Если эта фраза опущена, то Oracle  будет
использовать все строки таблицы в качестве корневых (попробуйте). Условие в
START WITH может содержать подзапрос.

2. Для каждой корневой строки выбираются дочерние строки, каждая из
которых должна удовлетворять условию фразы
CONNECT BY по отношению к
одной из корневых строк.

3. Oracle выбирает следующие одно за другим поколения дочерних строк.
Сначала выбираются потомки строк, полученных на этапе 2, затем потомки
этих потомков и так далее.
Oracle  всегда выбирает дочерние строки на
основании условия
CONNECT BY по отношению к текущей родительской
строке.

4. Если запрос содержит фразу WHERE, то Oracle  исключает из иерархии все
строки, которые не удовлетворяют условию, заданному в этой фразе.
5.
Oracle  возвращает результат запроса, в котором выбранные строки
расположены в иерархическом порядке, то есть потомки выводятся после
своих родителей.

Q2_27 Используя   функцию   LPAD   и   псевдостолбец   LEVEL можно     украсить

выводимый результат, предварительно отформатировав солбец «tree» :

demo@10g> col tree format a20

demo@10g> select lpad(' ',(level-1)*3)||level||' '||last_name as tree,

EMPLOYEE_ID,job_id,manager_id from EMPLOYEE

connect by prior EMPLOYEE_ID=manager_id

start with last_name='KING';

Q2_28   Вывести информацию о подчиненных JONES и о нем самом

demo@10g> select lpad(' ',(level-1)*3)||level||' '||last_name as tree,

EMPLOYEE_ID,job_id,manager_id from EMPLOYEE

connect by prior EMPLOYEE_ID=manager_id

start with last_name='JONES';

Q2_29 Вывести информацию о начальниках SMITH-а и о нем самом

demo@10g> select lpad(' ',(level-1)*3)||level||' '||last_name as tree,

EMPLOYEE_ID,job_id,manager_id from EMPLOYEE

connect by EMPLOYEE_ID = prior manager_id

start with last_name='SMITH';

Q2_30  Из дерева иерархии  сотрудников можно исключать отдельные ветви.

Исключим все строки, связанные с «DOYLE»

select lpad(' ',(level-1)*3)||level||' '||last_name as tree,

EMPLOYEE_ID,job_id,manager_id

from EMPLOYEE

connect by prior EMPLOYEE_ID = manager_id and last_name <> 'DOYLE'

start with salary=5000;

II.3. Функции Oracle SQL 

Общее количество SQL функций в Otacle10g – более двухсот (см. раздел документации Oracle10g   D:\Ora10.2_.doc\server.102\b14200\functions001.htm). По типу функции разделяются на числовые, символьные, функции для работы с датами, функции общего сравнения,  функции преобразования, агрегатные функции, аналитические функции,  функции для работы с большими  объектами и др.

    Рассмотрим более подробно некоторые типы функций.

II.3.1.  Некоторые символьные функции

Q3_1 Выведем на нижнем регистре названия отделов

demo@10g> select lower(name) from department;

LOWER(NAME)

--------------

accounting

research

sales

operations

research

sales

operations

sales

operations

operations

sales

11 строк выбрано.

Вывод на верхнем регистре реализуется функцией upper(название столбца или текст)

Q3_2  Выведем названия отделов так, чтобы первая буква названия выводилась на верхнем регистре

demo@10g> select initcap(lower(name)) from department;

INITCAP(LOWER(

--------------

Accounting

Research

Sales

Operations

Research

Sales

Operations

Sales

Operations

Operations

Sales

11 строк выбрано.

Q3_3  Дополним выводимые названия отделов слева символом «_», так, чтобы при этом выводимое название вместе с добавленными символами имело 20 символов

demo@10g> select lpad(name,20,’_’) from department;

Q3_4 Дополним выводимые названия отделов справа символом «*», так, чтобы при этом выводимое название вместе с добавленными символами имело 20 символов

Q3_5  Выведем наряду с описанием продукта усеченное слева описание продукта (удалением из описания продукта начального слова «YELLOW ») –только для продуктов, описание которых начинается словом «YELLOW»

 Q3_6 Выведем наряду с названием отдела усеченное справа название отдела (удалением из названия отдела завершающей части слова «ATIONS ») –только для продуктов, описание которых начинается буквой «O» 

Q3_7  Выведем среднюю часть описания продукта, начинающегося на букву «W»

Функция substr «вырезает» из описания продукта 8 букв, начиная с шестой.

Q3_8  Определим, сколько раз символ «S» встречается в названиях отделов. Сделаем это за несколько шагов.

demo@10g> select name from department

where department_id in(10,20,30,40);

NAME

--------------

ACCOUNTING

RESEARCH

SALES

OPERATIONS

demo@10g> select length(name) from department

where department_id in(10,20,30,40);

LENGTH(NAME)

------------

         10

          8

          5

         10

demo@10g> select translate(name,'AS','A') from department

where department_id in(10,20,30,40);

TRANSLATE(NAME

--------------

ACCOUNTING

REEARCH

ALE

OPERATION

Функция  translate в названии отдела, содержащемся в столбце name, заменяет символы из этого  названия, если они встречаются в списке  'AS' на символы из списка 'A'. При такой замене 'A' "меняется" на  'A', 'S' меняется на ..., а вот для 'S' нет замены, так как

список замен включает только одну букву  'A' и на второй позиции списка замен нет букв. В этом случае (когда нет замены) Oracle  убирает букву 'S' из названия отдела, содержащегося в столбце name.

demo@10g> select length(translate(name,'AS','A')) "Длина без символа S" from department

where department_id in(10,20,30,40);

Длина без символа S

-------------------

                10

                 7

                 3

                 9

11 строк выбрано.

demo@10g> select length(name) - length(translate(name,'AS','A')) as "Число букв S" from department

where department_id in(10,20,30,40);

Число букв S

------------

          0

          1

          2

          1

II.3.2.  Некоторые функции даты и времени

Таблица 2. Наиболее распространенные функции обработки календарных дат.

Функция

Возвращаемое значение

Пример использования

Результат
(зависит от даты вычисления данного примера)

sysdate

Текущие дата и время

select sysdate

 from dual;

28-FEB-99 (при вызове 28 февраля 1999 года)

last_day

Последний день месяца

select last_day(sysdate)

 from dual;

31-MAR-99 (при вызове 12 марта 1999 года)

add_months(d,n)

Добавляет к дате d (или вычитает из нее) n месяцев

select add_months(sysdate,2)

 from dual;

18-MAY-99 (при вызове 18 марта 1999 года)

months_between(f,s)

Число месяцев между датой f и датой s

select months_between(sysdate,

       '12-MAR-99')

 from dual;

13 (при вызове в апреле 1998 года)

next_day(d,day)

Ближайший указанный день недели day после даты d

select next_day(sysdate,'Monday')

 from dual;

05-JAN-98 (при вызове 30 декабря 1997 года)

Для работы с функциями обработки календарных дат надо знать собственно форматы представления дат. Поэтому далее приведем форматы представления дат в Oracle.

Таблица 3. Наиболее распространенные форматы представления дат.

Функция

Возвращаемое значение

Пример использования

Результат
(зависит от даты вычисления данного примера)

Y, YY или YYY

Последние одна, две или три цифры календарного года

select to_char(sysdate,'YYY')

 from dual;

999 (для всех дней 1999 года)

SYEAR или YEAR

Полный номер календарного года; при использовании SYEAR перед датами до н.э. ставится минус

select to_char(sysdate, 'SYEAR')

  from dual;

TWO THOUSAND EIGHT (для вызова

в 2008 г.)

Q

Квартал года (первый квартал продолжается с января по март)

select to_char(sysdate,'Q')  from dual;

2 (для всех дней июня)

MM

Номер месяца (в формате 01-12, где 12 соответствует декабрю)

select to_char(sysdate,'MM')  from dual;

12 (для всех дней декабря)

RM

Номер месяца римскими цифрами

select to_char(sysdate,'RM')  from dual;

IV (для всех дней апреля)

Month

Месяц в виде строки из девяти символов

select to_char(sysdate, 'Month')  from dual;

May и шесть пробелов (для всех дней мая)

WW

Номер недели в году

select to_char(sysdate,'WW')  from dual;

24 (при вызове 13 июня 1998 года)

W

Номер недели в месяце

select to_char(sysdate,'W')  from dual;

1 (при вызове 1 октября 1995 года)

DDD

Порядковый номер дня в году: 1 января день номер 001, 1 февраля — 032 и т.д.

select to_char(sysdate,'DDD')  from dual;

363 (при вызове 29 декабря 1999 года)

DD

День месяца

select to_char(sysdate,'DD')  from dual;

04 (при вызове 4 октября)

D

День недели (в формате 1-7)

select to_char(sysdate,'D')  from dual;

1 (при вызове 14 марта 1999 года)

DY

Сокращенное название дня недели

select to_char(sysdate,'DY')  from dual;

SUN (при вызове 28 марта 1999 года)

НН или НН12

Час по 12-часовой системе

select to_char(sysdate,'HH')  from dual;

02 (в момент времени 02:08, т.е. в 2 часа 8 минут после полуночи)

НН24

Час по 24-часовой системе

select to_char(sysdate,'HH24')  from dual;

14 2 часа 8 минут пополудни, т.е. в 14:08)

Ml

Минуты (0-59)

select to_char(sysdate,'MI')  from dual;

17 16:17, т.е. в 4 часа 17 минут пополудни)

SS

Секунды (0-59)

select to_char(sysdate,'SS')  from dual;

22 (в момент времени 11:03:22)

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

demo@10g> select * from nls_session_parameters

  where parameter in ('NLS_LANGUAGE','NLS_DATE_FORMAT');

PARAMETER                      VALUE

------------------------------ ---------------------------------

NLS_LANGUAGE                   RUSSIAN

NLS_DATE_FORMAT                DD.MM.RR

Такие настройки даты на моем компьютере. В скриптах же заполнения таблиц схемы «DEMO» формат даты «dd-mon-yyyy», причем месяц пишется на английском («JUN», «FEB» и т.д.). Поэтому мне для успешного заполнения таблиц схемы демо необходимо выполнить две команды:

alter session set nls_date_format='dd-mon-yyyy';

alter session set nls_language='AMERICAN';

Эти две команды присутствуют в начале скрипта создания предметной области DEMO.

Q3_9 Определим в днях разницу дат заказа покупки (order_date) покупателем и ее оплаты (ship_date)

demo@10g> Select ship_date - order_date  "Задержка оплаты" from sales_order

where to_char(order_date, 'yy')=89;

Задержка оплаты

---------------

             6

             1

             3

            12

            16

            15

            13

             4

            16

             1

            11

             3

             8

            13

             7

             3

            16

             5

            15

            13

             3

21 строк выбрано.

Q3_10 Определим заказы, перешедшие на другой год

demo@10g> select order_id, customer_id from sales_order

 2  where to_char(ship_date,'yyyy')-to_char(order_date,'yyyy')=1;

 ORDER_ID CUSTOMER_ID

        ----------             -----------

              549                      226

              559                      222

II.3.3.  Некоторые функции преобразования

     В Oracle для таблиц предметной области существует псевдостолбец с именем ROWID, значения которого определяют место хранения строки таблицы с этим значением ROWID (номер файла, номер блока в этом файле, номер записи в этом блоке).

Q3_11  Работа с ROWID, ROWNUM

Извдечение  столбца ROWID:

demo@10g> SELECT rowid,last_name FROM employee;

     Попробуем вывести только одну строку с номером (rownum – номер выводимой запросом строки) «1»      

DEMO@ORCL> SELECT rowid,last_name FROM employee  where rownum=1

ROWID              LAST_NAME

------------------ ---------------

AAANVAAAEAAAAXnAAA KING

     Для rownum=2:

DEMO@ORCL> SELECT rowid,last_name FROM employee  where rownum=2

строки не выбраны

Так для любого rownum, не равного единице.

Это ограничение можно обойти так:

DEMO@ORCL> SELECT rn, ri,last_name

from

(SELECT rownum rn,rowid ri,last_name FROM employee)

where rn=2;

       RN                         RI                                LAST_NAME

----------      ------------------                               ---------------

          2      AAANVAAAEAAAAXnAAB    DOYLE

   Преобразуем rowid в вид restricted, в котором для каждой строки явно указывается номер файла, номер блока в файле ОС и номер строки в этом блоке

DEMO@ORCL> select rn,rnr, last_name,object_id

from

(select rownum rn, last_name,dbms_rowid.rowid_object(rowid) object_id,

dbms_rowid.rowid_to_restricted(rowid,0)  rnr

   from employee)

where rn<5;

        В представленном выводе OBJECT_ID – номер таблицы EMPLOYEE, для столбца RNR число «0004» - идентификатор файла, число «000005E7» - номер блока, оставшаяся часть RNR – номер строки  в блоке.

DEMO@ORCL> select object_name from user_objects

 where object_id=54592;

OBJECT_NAME

--------------------

EMPLOYEE

Запрос для получения номера файла:

DEMO@ORCL> select  DBMS_ROWID.ROWID_TO_absolute_fno(ROWID,'DEMO','EMPLOYEE')  

from employee    where rownum=1;

DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO(ROWID,'DEMO','EMPLOYEE')

-------------------------------------------------------

                                                     4

Если мы пользователем SYS/SYS AS SYSDBA выполним команду:

SYS@ORCL> select file_name

   from dba_data_files

   where file_id=4;

то получим:

FILE_NAME                                                                                                 

------------------------------------------------------------                                         

D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF                     

    Извлечем номер блока, в котором хранятся строки таблицы employee:

DEMO@ORCL> select distinct dbms_rowid.rowid_block_number(rowid) block_number

   from employee

/

BLOCK_NUMBER

------------

       1511

5E716(см. запрос выше) = 5*642 + 14*161 + 7*160 = 1280 + 224 + 7 = 1511

                                           516          E16          7  

    Если мы пользователем sys/sys as sysdba (у пользователя DEMO нет прав на работу с таблицей col$) выполним:

SYS@ORCL> select round(sum(in_block_count_string)/count(*),0) srednee_chislo_strok_v_bloke

from

(select count(DBMS_ROWID.ROWID_block_number(rowid)) in_block_count_string

    from col$

group by DBMS_ROWID.ROWID_block_number(rowid))

/

SREDNEE_CHISLO_STROK_V_BLOKE

----------------------------

                         74

то получим среднее число записей таблицы col$ в одном блоке. Таблица col$ взята здесь для примера таблицы с относительно большим количеством строк; для хранения в ней ~60 тысяч строк требуется  ~700 блоков. Выполнять такой подсчет для таблиц пользователя DEMO нет смысла из-за малого количества строк в таблицах этого пользователя. Все таблицы этого пользователя, кроме ITEM, хранят строки в одном блоке (ITEM – в трех).

Q3_12  Функция NUMTODSINTERVAL преобразования числа в интервал (дней, часов, минут,  секунд).

Перед примером ее использования отформатируем столбцы:

demo@10g> col число_дней_в_дни format a20

demo@10g> col число_часов_в_дни format a20

demo@10g> col число_минут_в_дни format a20

demo@10g> col число_секунд_в_дни format a20

demo@10g> select employee_id,

(NUMTODSINTERVAL(employee_id,'day')) "ЧИСЛО_дней_в_дни",

(NUMTODSINTERVAL(employee_id,'hour')) "число_часов_в_дни",

(NUMTODSINTERVAL(employee_id,'minute')) "Число_минут_в_дни",

(NUMTODSINTERVAL(employee_id,'second')) "Число_секунд_в_дни"

   from employee

   where department_id=10;

Q3_13 Эту функцию можно использовать и для преобразования числа, получаемого из даты  в интервал (дней, часов, минут,  секунд)

demo@10g> demo@10g> select employee_id, hire_date, NUMTODSINTERVAL(to_char(hire_date,'dd'),'day')

   from employee

   where department_id=10;

Q3_14  Можно вычитать и складывать значения, возвращаемые функцией NUMTODSINTERVAL.

select NUMTODSINTERVAL(to_char(ship_date, 'dd'),'day') - NUMTODSINTERVAL(to_char(order_date, 'dd'),'day')   day_diff from sales_order

where to_char(order_date, 'yy')=89;

Q3_15  Функция NUMTOYMINTERVAL преобразует число(лет, месяцев) в интервал

demo@10g>select employee_id, NUMTOYMINTERVAL(employee_id,'year')

from employee

where department_id=10;

demo@10g>select employee_id, NUMTOYMINTERVAL(employee_id,'month')

from employee

where department_id=10;

Q3_16 Функция SCN_TO_TIMESTAMP(ORA_ROWSCN) преобразует  псевдостолбец

ORA_ROWSCN (содержащий номер последней транзакции с таблицей) во временную метку – время выполнения транзакции.

demo@10g> update employee

set salary=3000

where employee_id=7369;

demo@10g> commit;

demo@10g> SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN) FROM employee

WHERE employee_id = 7369;

SCN_TO_TIMESTAMP(ORA_ROWSCN);

----------------------------------

05.08.08 19:56:02,000000000

Через несколько минут:

demo@10g> update employee

set salary=800

where employee_id=7369;

demo@10g> commit;

demo@10g> SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN) FROM employee

WHERE employee_id = 7369;

SCN_TO_TIMESTAMP(ORA_ROWSCN)

----------------------------------

05.08.08 19:59:17,000000000

Q3_17  Использование функции to_char(date).

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

demo@10g> select to_char(ship_date,'DD-MON-YYYY HH24:MI:SS')

from sales_order

where to_char(ship_date,'yyyy')=1989 and

to_char(ship_date,'mm')=11;

Так как в параметрах функции вывод месяцев показан, как «MON», месяц выводится символами «НОЯ» вне зависимости от формата даты в сеансе:

demo@10g> select sysdate from dual;

SYSDATE

--------

06.08.08

Q3_18 Использование функции to_date(строка символов).

Изменить дату оформления некоторых заказов.

demo@10g> update sales_order

set ship_date=to_date('30.11.89')

where to_char(ship_date,'yyyy')=1989 and

to_char(ship_date,'mm')=11;

5 строк обновлено.

Измененение даты представлено символьной строкой '30.11.89', формат которой совпадает с форматом сеанса. Если такого совпадения нет, в функции to_date надо указывать дополнительные параметры.

demo@10g> update sales_order

set ship_date=to_date('30-nov-89','dd-mon-yy','nls_date_language=american')

where to_char(ship_date,'yyyy')=1989 and

to_char(ship_date,'mm')=11;

5 строк обновлено.

!!! Теперь обязательно выполним команду отмены транзакции

demo@10g>rollback;

Откат завершен

II.3.4.  Функции DECODE, CASE, DUMP, ORA_HASH, VSIZE

Q3_19 Формирование отчета, в котором содержимое столбца разворачивается в ряд столбцов отчета. Отчет должен содержать по строкам список сорудников (номера и имена служащих), по столбцам наименования отделов, а на пересечении знак ‘+’, если служащий работает в соответствующем отделе (только для отделов 10,20,30).

demo@10g> select employee_id, last_name,

       (select decode(department_id,10,'     +     ',NULL) from department

       where employee.department_id=department.department_id) acc_new_york,

       (select decode(department_id,20,'     +     ',NULL) from department

       where employee.department_id=department.department_id) res_dallas,

       (select decode(department_id,30,'     +     ',NULL) from department

       where employee.department_id=department.department_id) sal_chicago

       from employee

       where department_id in(10,20,30);

    В этом запросе создается пять столбцов, три последних из которых создаются с помощью функции decode.

    Функция decode в данном запросе обрабатывает значения столбца department_id (первым параметром функции decode является название столбца, для которого эта функция используется).. Если в этом столбце извлекаемой строки для части запроса с алиас «acc_new_york» содержится значение «10» (второй параметр функции decode в данной части запроса имеет значение «10»), функция decode вернет символ «+» (третий параметр функции decode имеет значение «+»), в противном случае – NULL (последний параметр функции decode имеет значение «NULL»). Если в этом столбце извлекаемой строки содержится значение «20»  (для извлекаемой части запроса с алиас «res_dallas»), функция decode вернет символ «+», в противном случае – NULL. Если, наконец, в этом столбце извлекаемой строки содержится значение «30»  (для извлекаемой части запроса с алиас «sal_chicago»), функция decode вернет символ «+», в противном случае – NULL.

Q3_20  А теперь усложним вывод отчета. На пересечении строк (сотрудников) и столбцов(названий отдела) надо выставить зарплату сотрудника в этом отделе (только для отделов 10,20,30).

demo@10g> select employee_id, last_name,

(select decode(department_id,10,salary,NULL) from department

where employee.department_id=department.department_id) acc_new_york,

(select decode(department_id,20,salary,NULL) from department

where employee.department_id=department.department_id) res_dallas,

(select decode(department_id,30,salary,NULL) from department

where employee.department_id=department.department_id) sal_chicago

from employee

where department_id in(10,20,30);

Q3_21  Повторим запросы Q3_19 и Q3_20 в другом варианте с использованием CASE выражения:

demo@10g> SELECT  employee_id, last_name,

(CASE WHEN department_id=10  THEN '     +      ' ELSE NULL END) acc_new_york,

(CASE WHEN department_id=20  THEN '     +      ' ELSE NULL END) res_dallas,

(CASE WHEN department_id=30  THEN '     +      ' ELSE NULL END) sal_chicago

from employee

where department_id in(10,20,30);

demo@10g> SELECT  employee_id, last_name,

(CASE WHEN department_id=10  THEN salary ELSE NULL END) acc_new_york,

(CASE WHEN department_id=20  THEN salary ELSE NULL END) res_dallas,

(CASE WHEN department_id=30  THEN salary ELSE NULL END) sal_chicago

from employee

where department_id in(10,20,30);

Q3_22  С помощью функции DUMP определим код типа данных, длину записанной в ячейке столбца информации, набор символов (CharacterSet), коды последовательности символов (указанной длины), начиная с указанной стартовой позиции.

demo@10g> select dump(last_name,8) from employee

where department_id=10;

Если вместо «8» поставим «10» или «16» получим коды символов в соответствующей системе (десятичной или шестнадцатиричной).

demo@10g> select dump(last_name,10,2,2) from employee

where department_id=10;

demo@10g> select dump(last_name,1016) from employee

where department_id=10;

Добавление 1000 к цифре кодировки (16 в последнем запросе) позволяет вывести  набор

Символов (CharacterSet).

demo@10g> select salary,dump(salary,1016) from employee

where department_id=10;

Q3_23  Функция VSIZE  выводит число байт во внутреннем представлении Oracle

SELECT last_name, VSIZE (last_name) "BYTES"

FROM employee

WHERE department_id = 10;

Q3_24  Функция ORA_HASH вычисляет хэш значение для значений столбца. В нижеприводимом варианте хэш значение разыгрывается в диапазоне чисел 0 – 4294967295.

demo@10g> select ORA_HASH(salary) from employee

where department_id=10;

ORA_HASH(SALARY)

----------------

3983772665

 929675335

4269363183

Можно изменить диапазон (установим, например, 100) и задать начальное значение последовательности случайных чисел (установим, например, 5), которая используется при формировании хэш значения

demo@10g> select ORA_HASH(salary,100,5) from employee

where department_id=10;

ORA_HASH(SALARY,100,5)

----------------------

           97

           79

                         9

II.3.5.  Агрегатные и аналитические функции

    Выше мы уже ознакомились с некоторыми агрегатными функциями (max(),  min(), avg()). Общее число агрегатных и аналитических функций в Oracle10g - более 50. Аналитические  функции, позволяют производить ранжирование (ranking), перенос сводных результатов (moving aggregates), сравнение данных за различные периоды (period comparisons), соотношение итогов (ratio of total), получение совокупных сводных результатов (cumulative aggregates) и другие действия.

Q3_25 Выполним запрос, устанавливающий ранг сотрудникам компании, обслуживающим покупателей (salesperson_id в таблице customer) по суммам продаж «их» покупателям – за все годы продаж.  Максимальной сумме продаж будет соответствовать  ранг «1», следующей за максимальной – ранг «2» и т.д. Если сумма продаж одинаковая, то и ранг должен быть одинаковым.

demo@10g> SELECT salesperson_id, nvl(round(SUM(total),0),0) tot_sales,

RANK() OVER (ORDER BY nvl(SUM(total),0) DESC) sales_rank

FROM customer c, sales_order so

WHERE c.customer_id=so.customer_id(+)

group by salesperson_id

/

    Если бы в компании был сотрудник, принесший ей убыток (tot_sales<0), ранг этого работника был бы установлен «12», так как у двух сотрудников перед тем был установлен одинаковый ранг «10». При использовании функции dense_rank:

demo@10g> SELECT salesperson_id, nvl(round(SUM(total),0),0) tot_sales,

DENSE_RANK() OVER (ORDER BY nvl(SUM(total),0) DESC) sales_rank

FROM customer c, sales_order so

WHERE c.customer_id=so.customer_id(+)

group by salesperson_id

/

Результат получаем такой же, как и предыдущий. Но если в компании был сотрудник, принесший ей убыток, ранг этого работника был бы установлен «11». В этом только отличие DENSE_RANK() от RANK().

Q3_26  Усложним запрос Q3_25. Установим ранг сотрудников внутри отделов, в которых они работают (опять по сумме продаж за все годы):

SELECT department_id,salesperson_id, nvl(round(SUM(total),0),0) tot_sales,

RANK() OVER (PARTITION BY department_id ORDER BY nvl(SUM(total),0) DESC) sales_rank

FROM employee e,customer c, sales_order so

WHERE c.salesperson_id=e.employee_id and

c.customer_id=so.customer_id(+)

group by (department_id,salesperson_id)

/

    В запросе использована совместно с RANK() опция PARTITION BY, в которой указывается, в привязке к какому столбцу идет детализация ранга (в нашем случае к столбцу «идентификатор отдела»).

Q3_27 Выполним запрос: определить, подразделив отчет по группам сотрудников, подчиняющихся своим менеджерам (PARTITION BY manager_id),  нарастающим итогом количество сотрудников, принятых на работу в заданном  временном интервале (100 дней), отсчитываемом от даты принятия на работу под управлением «своего»  менеджера первого сотрудника. Для задания интервала в запросе используется опция «RANGE NUMTODSINTERVAL(100, 'day') preceding». Здесь «RANGE» - диапазон, NUMTODSINTERVAL(100, 'day') – интервал в 100 дней, «preceding» означает разницу даты приема на работу(HIRE_DATE – в нашем запросе) очередного извлекаемого запросом сотрудника с датой первого принятого в этом временном интервале на работу сотрудника (в группе сотрудников под управлением «своего» менеджера – ведь мы используем «PARTITION BY manager_id»). Если в группе сотрудников, подчиненных одному менеджеру, дата приема на работу очередного извлекаемого запросом  сотрудника отличается от даты первого принятого на работу под управлением  этого менеджера сотрудника более, чем на сто дней, отсчет количества принятых на работу сотрудников вновь начинаеися с единицы.

demo@10g>  SELECT manager_id, last_name, hire_date,

COUNT(*) OVER (PARTITION BY manager_id ORDER BY hire_date

RANGE NUMTODSINTERVAL(100, 'day') preceding) AS t_count

FROM employee

where manager_id is not null;

     В отработанном запросе для менеджера с manager_id=7505 к 01 июня 1985г. года на работу было принято 4 сотудника (разница даты приема на работу (четвертым) ROSS-а и даты приема на работу (первым) PETERS-а меньше 100 дней). Следующим под управление этого менеджера был принят на работу JENSEN  - 15 января 1987г. Разница этой даты с датой приема на работу PETERS-а (31 марта 1985г.) больше 100 дней, поэтому счет количества принятых на работу сотрудников под управлением этого менеджера опять начинается с единицы (T_COUNT=1) .

    Если мы выполним тот-же запрос с временным интервалом в 1000 дней, внутри групп отдельных менеджеров у нас уже не будет повторяющихся цифр T_COUNT для строк с одним и тем же менеджером.

Q3_28 Продемонстрируем формирование агрегатных данных опцией ROLLUP

ROLLUP обеспечивает агрегирование на каждом уровне, заданном столбцами в GROUP BY. Это приводит к получению информации, которую нельзя было получить раньше без дополнительного кодирования. Следующий запрос извлекает номер отдела, код должности, сгруппированные по этим двум столбцам суммы зарплат, но также и промежуточные итоги - общую сумму зарплаты по отделам.  

demo@10g> select department_id,job_id,sum(salary) "Total SAL"

from EMPLOYEE

group by rollup (department_id,job_id);

Поменяем порядок столбцов в опции ROLLUP:

demo@10g> select job_id,department_id, sum(salary) "Total SAL"

from EMPLOYEE

group by rollup (job_id, department_id);

Отформатируем вывод:

demo@10g> col Department format a20

demo@10g> break on Department

demo@10g> select nvl(to_char(department_id),'Whole Company') Department,

decode(job_id,NULL,'All Employees') job,sum(salary) "Total SAL"

from EMPLOYEE

group by rollup (department_id,job_id);

Q3_29 Использование GROUP BY с опцией CUBE

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

Выведем столбцы: код должности, номер отдела, суммарная зарплата, а также идентификаторы группирования по этим столбцам.

demo@10g> select job_id, department_id, sum(salary),

grouping(department_id), grouping(job_id)

from EMPLOYEE

group by cube(job_id,department_id);

Наличие «1» в столбце grouping(department_id) означает, что группирование проводится по номерам отделов – сумма зарплаты подсчитывается и выводится для строк с одинаковым номером отдела, «1» в столбце grouping(job_id) – по коду работы.  Если в обоих столбцах значения «0», подсчитывается сумма по зарплате для этого кода работы и номера отдела.

Q3_30  Получим имя работника, департамент и заработную плату; также требуется получить общую сумму заработной платы по департаментам и процент заработной платы конкретного служащего  в сумме департамента и общей сумме (например, работник Х в департаменте Y получает 10% заработной платы от суммы заработной платы его департамента и 1% от заработной платы компании) /4/.

demo@10g>

elect employee.department_id,

     employee.last_name,

     employee.salary,

     sum(employee4.salary) cum_salary,

     round(100*employee.salary/employee2.salary_by_dept,1) pct_dept,

     round(100*employee.salary/employee3.salary_overall,1) pct_overall

from employee,

     (select department_id, sum(salary) salary_by_dept

        from employee

        group by department_id ) employee2,

      (select sum(salary) salary_overall

         from employee ) employee3,

      employee employee4

where employee.department_id = employee2.department_id

  and employee.department_id = employee4.department_id

  and (employee.salary > employee4.salary or

(employee.salary = employee4.salary and employee.last_name >= employee4.last_name))

group by employee.department_id, employee.last_name, employee.salary,

     round(100*employee.salary/employee2.salary_by_dept,1),

     round(100*employee.salary/employee3.salary_overall,1)

order by department_id, salary;

    Теперь отработаем этот запрос с помощью аналитических функций. Сначала отформатируем столбцы,

demo@10g> column pct_dept format 99.9

demo@10g> column pct_overall format 99.9

demo@10g> break on deptno skip 1

а теперь сам запрос:

demo@10g> select department_id,  last_name,  salary,

     sum(salary) over (partition by department_id order by salary,last_name) cum_sal,

     round(100*ratio_to_report(salary) over (partition by department_id), 1 ) pct_dept,

     round(100*ratio_to_report(salary) over () , 1 ) pct_overall

from employee

order by department_id, salary;

Получим такой же точно результат, какой был в предыдущем запросе, который выполнялся  без аналитических функций.  RATIO_TO_REPORT(salary) – краткая и удобная функция получения процентных долей как внутри отделов - over (partition by department_id) так и по всей фирме-  over ().

II.4. Другие команды языка манипулирования данными DМL и  обработка транзакций

II.4.1. Команды DML insert, update, delete

Q4_1  Включение в таблицу одной строки

demo@10g> Insert into department (department_id,name,location_id)

values(77,'Teaching',null);

1 строка создана.

    Напомним здесь, что при создании таблицы DEPARTMENT столбец location_id был объявлен foreign key с допущением null значений. Если бы вставляли новую строку в таблицу EMPLOYEE, мы не смогли бы вставить ее с null значением в столбце внешнего ключа department_id, так как при создании таблицы EMPLOYEE столбцу department_id было задано ограничение not null.

    Вернем таблицу DEPARTMENT к прежнему состоянию:

demo@10g> rollback;

Откат завершен.

    Повторим предыдущую команду немного в другом варианте:

demo@10g> Insert into department values(77,'Teaching',null);

1 строка создана.

    Если вставляются все столбцы, указывать после названия таблицы столбцы необязательно.

    Повторим откат:

demo@10g> rollback;

Откат завершен.

     Выполним вставку только в отдельные столбцы:

demo@10g> Insert into department(department_id,name) values(77,'Teaching');

1 строка создана.

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

Повторим откат:

demo@10g> rollback;

Откат завершен.

    Рассмотрим варианты вставки даты. Приведем структуру таблицы SALES_ORDER:

demo@10g> desc sales_order

Имя                                    Пусто?      Тип

-------------------------       --------           -------------

ORDER_ID                     NOT NULL NUMBER(4)

ORDER_DATE                                    DATE

CUSTOMER_ID                                  NUMBER(6)

SHIP_DATE                                         DATE             

TOTAL                                                 NUMBER(8,2)

    Посмотрим формат и language даты в нашем сеансе (см. раздел II.3.2):

demo@10g> select * from nls_session_parameters

  where parameter in ('NLS_LANGUAGE','NLS_DATE_FORMAT');

    В соответствии с этим форматом и выполним вставку:

demo@10g> insert into sales_order values(900,'21.07.89',208,'23.07.89',2345);

1 строка создана.

Выполним  откат:

demo@10g> rollback;

Откат завершен.

    Воспользуемся при вставке даты функцией to_date():

demo@10g> insert into sales_order

values(900,to_date('21.07.89'),208,to_date('23.07.89'),2345);

1 строка создана.

demo@10g> rollback;

Откат завершен.

    А теперь вставим дату в другом формате:

demo@10g> insert into sales_order(order_id,order_date,customer_id,ship_date,total)

values(900,to_date('21-jul-1989', 'dd-mon-yyyy','nls_date_language=american'),

208,to_date('23-jul-1989', 'dd-mon-yyyy','nls_date_language=american'),2345);

1 строка создана.

    Отметим, что вставленная строка извлекает дату в формате сеанса:

demo@10g> rollback;

Откат завершен.

demo@10g> select * from sales_order where order_id=900;

ORDER_ID ORDER_DA    CUSTOMER_ID    SHIP_DAT       TOTAL

----------        --------              -----------                  --------               ----------

     900         21.07.89                 208                 23.07.89                2345

Q4_2  Создание последовательности

demo@10g> create sequence DEPARTMENTseq start with 60;

Последовательность создана.

Q4_3  Используем последовательность для  генерации значений столбца department_id:

demo@10g> insert into department values(departmentseq.nextval,'training',122);

1 строка создана.

demo@10g> select * from department where name='training';

DEPARTMENT_ID     NAME                LOCATION_ID

-------------                    --------------          -----------

          60                       training               122

demo@10g> rollback;

Откат завершен.

Q4_4   Для вставки копированием с использованием запроса создадим таблицу S_GRADE, которая не будет иметь столбец – PRIMARY KEY:

demo@10g>create table S_GRADE as select * from SALARY_GRADE;

demo@10g>select * from S_GRADE;

demo@10g> insert into s_grade (select * from S_GRADE);

demo@10g>select * from S_GRADE;

При наличии столбца – primary key Oracle не допустил бы вставку повторяющихся по primary key строк. Теперь удалим созданную таблицу S_GRADE:

demo@10g> drop table S_GRADE;

Таблица удалена.

Q4_5   Вставка одной строки с использованием запроса:

demo@10g> insert into (select * from DEPARTMENT)

values(90,'teaching',122);

1 строка создана.

demo@10g> rollback;

Откат завершен.

Q4_6   Иногда требуется обеспечить ввод определенных значений столбца, не допуская другие значения для ввода.   Такой ввод возможен с использованием  WITH CHECK OPTION

demo@10g> insert into (select * from DEPARTMENT

where location_id = 122 with check option)

values(90,'teaching',50);

insert into (select * from DEPARTMENT

*

ошибка в строке 1:

ORA-01402: представление WITH CHECK OPTION не соответствует фразе WHERE

    Ошибка зарегистрирована, так как в опции with check option указано допустимое для ввода значение location_id = 122, а вводимое значение 50.

demo@10g> insert into (select * from DEPARTMENT

where location_id = 122 with check option)

values(90,'teaching',122);

1 строка создана.

demo@10g> rollback;

Откат завершен.

Q4_7  Изменение строк таблицы командой UPDATE

Создадим еще раз таблицу S_GRADE

demo@10g>create table S_GRADE as select * from SALARY_GRADE;

Таблица создана.

demo@10g>select * from S_GRADE;

Изменим в таблице S_GRADE значений столбца lower_bound, установив в этом столбце значения из столбца upper_bound таблицы SALARY_GRADE  

demo@10g> update S_GRADE set lower_bound=(select upper_bound from

SALARY_GRADE sg where sg.grade_id=S_GRADE.grade_id);

5 строк обновлено.

demo@10g>select * from S_GRADE;

demo@10g> rollback;

Откат завершен.

Q4_8  Выполним простое изменение строки таблицы DEPARTMENT

demo@10g> update DEPARTMENT

set name='TEACHING' where department_id=12;

1 строка обновлена.

demo@10g> rollback;

Откат завершен.

Q4_9 Изменить таблицу EMPLOYEE - перевести служащих в должности «CLERK» на должность «SALESPERSON» и при этом установить им 75% средних зарплаты и коммерческих сотрудников, уже работавших в должности «SALESPERSON»

demo@10g> update EMPLOYEE e set job_id = (select job_id from job where function =   

'SALESPERSON'),   (salary,commission)= (select  round(0.75*avg(salary),0),     

round(0.75*avg(commission),0) from EMPLOYEE where job_id= (select job_id from job

 where  function='SALESPERSON'))    

where job_id = (select job_id from job  where function ='CLERK');

demo@10g> rollback;

Откат завершен.

Q4_10  Удаление строк из таблицы командой DELETE. Удаление сведений обо всех отделах, расположенных в BOSTON

demo@10g> Delete from  DEPARTMENT where location_id=

(select location_id from location where regional_group='BOSTON');

demo@10g> rollback;

Откат завершен.

Q4_11  Удаление из таблицы DEPARTMENT ведений об отделах, в которых нет сотрудников

demo@10g> delete from DEPARTMENT x

where not exists (select 1 from EMPLOYEE where

DEPARTMENT_ID=x.DEPARTMENT_ID);   

demo@10g> rollback;

Откат завершен.

Q4_12  Удалим дубликаты  записей из таблицы S_GRADE, предварительно создав их.

demo@10g>insert into S_GRADE (select* from S_GRADE);

demo@10g> select rowid,S_GRADE.* from S_GRADE;

demo@10g> delete from S_GRADE x

   where rowid > (select min(rowid)

                  from S_GRADE y

                  where x.grade_id=y.grade_id);

                    

II.4.2. Обработка транзакций

    Завершим предыдущую транзакцию

demo@10g> commit;

 Q4_13     Начнем новую транзакцию. В ходе ее создадим точку сохранения и вернемся к ней, восстановив изменения после нее.

demo@10g>select * from S_GRADE;

demo@10g>delete from S_GRADE where grade_id=5;

1 строка удалена.

demo@10g>select * from S_GRADE;

Создадим точку 1 сохранения транзакций

demo@10g> savepoint poin1;

Точка отката создана.

demo@10g> delete from S_GRADE where grade_id=4;

1 строка удалена.

demo@10g> select * from S_GRADE;

Создадим точку 2 сохранения транзакций

demo@10g> savepoint poin2;

Точка отката создана.

demo@10g> delete from S_GRADE;

3 строк удалено.

demo@10g> select * from S_GRADE;

Строки не выбраны

Вернемся к точке сохранения т ранзакции

demo@10g> rollback to poin2;

Откат заверщен

demo@10g> select * from S_GRADE;

demo@10g> rollback to poin1;

Откат заверщен

demo@10g> select * from S_GRADE;

demo@10g> commit;

II.5. Создание и изменение структуры таблиц средствами DDL

Q5_1  Создание таблицы DEPARTMENT1 и заполнение ее данными:

demo@10g> CREATE TABLE DEPARTMENT1(

DEPARTMENT_ID NUMBER(2,0) NOT NULL,

NAME VARCHAR2(14) NULL,

LOCATION_ID NUMBER(3,0) NULL,

CHECK (DEPARTMENT_ID IS NOT NULL),  

PRIMARY KEY (DEPARTMENT_ID),  

FOREIGN KEY (LOCATION_ID) REFERENCES LOCATION(LOCATION_ID)

);

Таблица создана.

demo@10g>insert into DEPARTMENT1 (select * from DEPARTMENT);

11 строк создано.

Создание таблицы EMPLOYEE1 заполнение ее данными:

demo@10g> CREATE TABLE EMPLOYEE1(

EMPLOYEE_ID NUMBER(4,0) NOT NULL,

LAST_NAME VARCHAR2(15) ,

FIRST_NAME VARCHAR2(15) ,

MIDDLE_INITIAL VARCHAR2(1),

JOB_ID NUMBER(3,0),

MANAGER_ID NUMBER(4,0),

HIRE_DATE DATE,

SALARY NUMBER(7,2),

COMMISSION    NUMBER(7,2),

DEPARTMENT_ID NUMBER(2,0) NOT NULL,  

CHECK  (DEPARTMENT_ID BETWEEN 10 AND 99) DISABLE,

PRIMARY KEY (EMPLOYEE_ID),  

FOREIGN KEY (JOB_ID) REFERENCES JOB(JOB_ID),  

FOREIGN KEY (MANAGER_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID),  

FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENT1(DEPARTMENT_ID),

CONSTRAINT  LST_NAME_DEPT_ID UNIQUE(LAST_NAME,DEPARTMENT_ID)

);

Таблица создана.

demo@10g>insert into EMPLOYEE1 (select * from EMPLOYEE);

32 строк создано.

    В созданной таблицеодин столбец (employee_id) является primary key и три столбца (job_id, manager_id,department_id) образуют три разных foreign key по одному столбцу в каждом.  Кроме того определено ограничение целостности (constraint) LST_NAME_DEPT_ID, требующий уникальности фамилии служащего в отделе. Столбцы, для которых указано «NOT NULL», должны быть заполнены информацией при вставке строк в таблицу. Ограничение CHECK задает диапазон значений DEPARTMENT_ID от 10 до 99. Это ограничение отключено (…DISABLE).  

Q5_2  Добавим новые столбцы в таблицу EMPLOYEE1

demo@10g>alter table EMPLOYEE1 add birth_day date;

Таблица изменена.

demo@10g>alter table EMPLOYEE1 add E_SEX  char(1) default ('M');

Q5_3  Удалим столбцы из таблицы EMPLOYEE1

demo@10g>alter table EMPLOYEE1 drop column middle_initial;

Таблица изменена.

demo@10g>alter table EMPLOYEE1 drop column commission;

Таблица изменена.

demo@10g> desc employee1

Имя                                          Пусто?                Тип

------------------------------------ --------                  --------------

EMPLOYEE_ID                      NOT NULL       NUMBER(4)

LAST_NAME                                                     VARCHAR2(15)

FIRST_NAME                                                    VARCHAR2(15)

E_SEX                                                                      CHAR(1)

JOB_ID                                                                 NUMBER(3)

MANAGER_ID                                                    NUMBER(4)

HIRE_DATE                                                         DATE

SALARY                                                               NUMBER(7,2)

DEPARTMENT_ID               NOT NULL           NUMBER(2)

BIRTH_DAY                         NOT NULL           DATE

Q5_4  Добавим ограничение целостности к столбцу E_SEX

demo@10g> alter table EMPLOYEE1 add constraint check_sex check(e_sex in ('M','F'));

Таблица изменена.

Q5_5  Задействуем отключенное при создании таблицы EMPLOYEE1 ограничение целостности для столбца department_id, которым устанавливается диапазон значений от 10 до 99. Для такого задействования нам надо определить имя этого ограничения, которое Oracle присваивает автоматически при создании таблицы. Для этого извлечем данные из таблицы словаря  Oracle, предварительно отформатировав один из ее столбцов:

demo@10g> col search_condition format a40

demo@10g> select constraint_name,search_condition,status from user_constraints

where table_name='EMPLOYEE1';

Теперь можно «включить» ограничение SYS_C006046 (как видно из результата запроса, ограничению с таким именем соответствует фраза «DEPARTMENT_ID BETWEEN 10 AND 99»):

demo@10g> alter table employee1 modify constraint SYS_C006046 enable;

Таблица изменена

Чтобы убедиться, что ограничение включено, повторим запрос к словарю:

demo@10g> select constraint_name,search_condition,status from user_constraints

where table_name='EMPLOYEE1';

Q5_6  Удалим добавленное ограничение целостности check_sex

demo@10g> alter table employee1 drop constraint check_sex;

Таблица изменена.

Q5_7  Скорректируем  максимальную длину столбца LAST_NAME

demo@10g> alter table EMPLOYEE1 modify (last_name varchar2(30));

Таблица переименована.

Q5_8 Переименование таблицы

demo@10g> rename EMPLOYEE1 to EMPLOYEE2;

Таблица переименована.

Q5_9  Переименование столбца таблицы EMPLOYEE2

demo@10g> alter table employee2 rename column e_sex to sex;

Таблица изменена.

Q5_10 Удалим таблицы DEPARTMENT1, EMPLOYEE2:

demo@10g>drop table DEPARTMENT1;

drop table DEPARTMENT1

          *

ошибка в строке 1:

ORA-02449: уникальный/первичный ключ в таблице, на которую ссылаются по внешнему ключу

На таблицу DEPARTMENT1 ссылаются записи таблицы EMPLOYEE2 (переименованной таблицы EMPLOYEE1), поэтому команду удаления таблицы DEPARTMENT1 надо писать иначе:

demo@10g>drop table DEPARTMENT1 cascade constraint;

Таблица удалена.

demo@10g>drop table EMPLOYEE2;

Таблица удалена.

Q5_11   Опция EXCEPTION INTO

Создадим таблицу, которую будем использовать для занесения информации о строках, нарушающих ограничение целостности

demo@10g>  create table my_exceptions (

row_id rowid,

owner varchar2(30),

table_name varchar2(30),

constraint varchar2(30));

Нарушать ограничение целостности будем для таблицы S_GRADE. Текущее ее содержимое:

demo@10g> select * from S_GRADE;

Продублируем информацию в ней:

demo@10g>  insert into S_Grade (select * from S_GRADE);

4 строк создано.

demo@10g> select * from S_GRADE;

Видно, что в столбце grade_id значения повторяются.

Скорректируем таблицу S_GRADE, сделаем попытку определить столбец grade_id, как primary key.  Часть команды «…exseption into my_exseption» указывает Oracle, куда помещать информацию о тех строках таблицы, из-за которых корректировка невозможна – не удается столбец grade_id определить, как primary key.

demo@10g> alter table S_GRADE add constraint pk_grade_id primary key(grade_id)

exceptions  into MY_EXCEPTIONS;

alter table S_GRADE add constraint pk_grade_id primary key(grade_id)

                                  *

ошибка в строке 1:

ORA-02437: невозможно подтвердить (DEMO.PK_GRADE_ID) - нарушен первичный ключ

Так как значения столбца grade_id повторяются, выполнить команду невозможно,  Oracle выдает сообщение об ошибке и помещает в таблицу MY_EXSEPTION строки, из-за которых эта ошибка появляется.

demo@10g> select * from my_exceptions;

Q5_12  Тип LONG в документации Oracle определяется устаревшим типом и оставляется для использования для обратной совместимости с прежними версиями Oracle. Таблица user_constraints, из которой мы выше извлекали информацию имеет столбец search_condition типа LONG.

Теперь попробуем извлечь из таблицы user_constraints информацию, воспользовавшись условием where для столбца search_condition типа LONG:

demo@10g> select * from user_constraints

where search_condition like '%DEPARTMENT%';

where search_condition like '%DEPARTMENT%'

         *

ошибка в строке 2:

ORA-00932: несовместимые типы данных: ожидается NUMBER, получено LONG.

Причем функции преобразования типов ( to_lob, to_char и т.д. здесь не помогают).

Прямое преобразование типа LONG в тип CLOB не проходит, так как во-первых user_constraints является представлением словаря, а во-вторых структуру объектов словаря, «хозяйства»  администратора базы данных менять не рекомендуется, да и привилегии на это нужны особые, которых у пользователя demo нет:

demo@10g> alter table user_constraints modify (search_condition CLOB);

alter table user_constraints modify (search_condition CLOB)

*

ошибка в строке 1:

ORA-00942: таблица или представление пользователя не существует

Для того, чтобы запросы вышеприведенного типа обрабатывались  (select * from user_constraints where search_condition like '%DEPARTMENT%';), возможен такой вариант преобразования  типа LONG в тип CLOB:

demo@10g>create table my_user_constraints (

constraint_name  varchar2(30),

table_name  varchar2(30),

search_condition CLOB);

Таблица создана.

demo@10g> insert into my_user_constraints

(select constraint_name, table_name,to_lob(search_condition)

from user_constraints);

83 строк создано.

Без функции преобразования to_lob() Oracle не позволяет переносить тип LONG  в другой столбец такого-же типа или типа CLOB.

demo@10g>

insert into my_user_constraints

(select constraint_name, table_name,search_condition

 from user_constraints)

demo@10g> /

(select constraint_name, table_name,search_condition                                   

                                                           *

ошибка в строке 2:

ORA-00997: неверное использование типа данных LONG

Теперь тот запрос, который на столбце типа LONG выдавал ошибку, со столбцом типа CLOB проходит:

demo@10g>select * from my_user_constraints

where search_condition like '%DEPARTMENT%';

II.6. Другие объекты базы данных

Q6_1. Создание представления.

demo@10g> create or replace view EMP_DEPT as

select employee_id, last_name,department_id,name department

from EMPLOYEE join DEPARTMENT

using (department_id) where department_id=10;

Представление создано.

    Вставка значений в это представление не получится. Нельзя изменить более одной таблицы посредством связанного представления. Если мы попытаемся вставить только один столбец одной таблицы (employee_id), Oracle  потребует вставки foreign key (department_id) и т.д. – для всех вариантов вставки столбцов.  Изменение в одном варианте (update emp_dept set employee_id where employee_id…..) возможно, но только для  строк служашего, у которого нет подчиненных.

Q6_2. Создание представления с опцией read only.

demo@10g> create or replace view EMP_V1 as

select employee_id,last_name, (salary+nvl(commission,0)) "Общая зарплата_отдела",

department_id from employee with read only;

Q6_3. Использование представлений для отработки запроса.  

    Использование представлений для отработки запроса нередко «спасает» студентов на контрольной работе по SQL.  Рассмотрим такой запрос:

    Получите имена всех  заказчиков товара (которые оформляли заказы  два или более раза), год заказа, а также фамилии торговых представителей для этих заказчиков. Выводимый список упорядочте по имени заказчика и году продаж.  

    Посмотрим, как этот запрос можно отработать с использованием представлений.

Имя заказчика – customer.name

Год заказа – to_char(sales_order.ship_date,'yy')

Фамилия торгового представителя – employee.last_name

Сначала найдем имена заказчиков, которые оформляли заказы  два или более раза:

demo@10g>  Create or replace view v1 as

select customer.name name, count(customer.name) count

from customer, sales_order

where customer.customer_id=sales_order.customer_id

group by customer.name;

При создании view v1 Oracle «ругнется», если для count(customer.name) не создать алиас (например, count, как это сделано в нашем случае).

demo@10g> select * from v1;

demo@10g>  create  or replace view v2 as

select NAME from v1 where count>=2;

demo@10g>  select * from v2;

demo@10g> select distinct customer.name, to_char(sales_order.ship_date,'yy') year,employee.last_name

from customer,sales_order,employee

where customer.customer_id=sales_order.customer_id

and customer.salesperson_id=employee.employee_id

and employee.job_id=(select job_id from job where function='SALESPERSON')

and customer.name in

(select * from v2)

order by customer.name,year;

40 строк выбрано

Теперь избавимся от V2 и V1 отдельными шагами:

Сначала избавимся от V2:

demo@10g>  select distinct customer.name, to_char(sales_order.ship_date,'yy') year,employee.last_name

from customer,sales_order,employee

where customer.customer_id=sales_order.customer_id

and customer.salesperson_id=employee.employee_id

and employee.job_id=(select job_id from job where function='SALESPERSON')

and customer.name in

(select * from (select name from v1 where count>=2))

order by customer.name,year;

Вслед за тем подставим в запрос строки для V1:

demo@10g> select distinct customer.name, to_char(sales_order.ship_date,'yy') year,employee.last_name

from customer,sales_order,employee

where customer.customer_id=sales_order.customer_id

and customer.salesperson_id=employee.employee_id

and employee.job_id=(select job_id from job where function='SALESPERSON')

and customer.name in

(select * from (select name from

(select customer.name name, count(customer.name) count

   from customer, sales_order

   where customer.customer_id=sales_order.customer_id

    group by customer.name)

where count>=2))

order by customer.name,year;

Q6_4. Удалим представление EMP_DEPT.

demo@10g>drop view EMP_DEPT;

Представление удалено.

Q6_5. Создадим индекс по столбцу name таблицы DEPARTMENT

demo@10g> create index i_DEPARTMENT_name  on DEPARTMENT(name);

Индекс создан.

Теперь запросы к таблице DEPARTMENT могут выполняться с исплользованием этого индекса, если в условии запроса используется столбец name.

demo@10g> select * from DEPARTMENT  where name like 'O%';

Q6_6.  Удаление индексов

demo@10g>  drop index i_DEPARTMENT_name;

Индекс удален.

Q6.7.  Создадим кластер для таблиц LOCATION, DEPARTMENT

demo@10g> create cluster LOC_DEPT (location_id number(3))

   storage (initial 100k next 50k);

Кластер создан.

demo@10g> create table DEPARTMENT1(

   department_id number(2) not null,

   name varchar2(13),

   location_id number(3),

   constraint department_id_pk primary key (department_id))

   cluster LOC_DEPT(location_id);

Таблица создана.

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

demo@10g> create table LOCATION1(

   location_id number(3) not null,

   regional_group varchar2(20),

   constraint location_id_pk primary key (location_id))

   cluster LOC_DEPT(location_id);

Таблица создана.

    Создаем индекс кластера:

demo@10g> create index idx_loc_dept on cluster loc_dept;

Индекс создан.

    Заполним таблицы данными:

demo@10g>insert into DEPARTMENT1 (select * from DEPARTMENT);

11 строк создано.

demo@10g>insert into LOCATION1 (select * from LOCATION);

4 строк создано.

    

    Теперь при отработке запросов, включающих во фразе «…from» обе таблицы кластера, Oracle будет отрабатывать эти запросы, извлекая данные из струкутуры кластера.

Q6.8.  Удаление кластера, всех таблиц, принадлежащих кластеру и всех ограничений целостности, принадлежащих ему

demo@10g>drop cluster LOC_DEPT

   including tables cascade constraints;

Кластер удален.

Q6.9.   Создадим синоним для таблицы department:

 

demo@10g>create synonym DEPT for DEPARTMENT;

create synonym DEPT for DEPARTMENT

*

ошибка в строке 1:

ORA-01031: привилегий недостаточно

   Сообщение об ошибке указывает об отсутствии у пользователя DEMO привилегии создавать синонимы. Для выдачи такой привилегии пользователю DEMO из другого окна SQL*Plus соединимся пользователем SYSTEM (DBA) и выполним этим пользователем команду:  system@10g> create synonym demo.dpt for demo.department;.       Теперь к таблице DEPARTMENT пользователю DEMO можно обращаться по более короткому имени синонима:

demo@10g>select * from DPT;

    Пользователь DEMO может дать другим пользователям привилегию для работы с таблицей своей схемы:

demo@10g>grant select on EMPLOYEE to scott;

Привилегии предоставлены.

    Теперь пользователь SCOTT (предварительно сединившись с Oracle, конечно) может извлекать данные из таблицы EMPLOYEE пользователя DEMO такой, например,  командой: demo@10g>select * from demo.employee; , указав владельца таблицы и название таблицы. Пользователю SCOTT обращаться к таблице EMPLOYEE пользователя DEMO, каждый раз набирая имя пользователя DEMO, не совсем  удобно (лишние дествия на клавиатуре). Чтобы устранить это неудобство, администратор SYSTEM выполняет команду: system@10g>create public synonym empl for demo.employee;, после которой и пользователь DEMO, и все другие пользователи, у которых есть привилегия извлекать данные из таблицы EMPLOYEE, принадлежащей пользователю DEMO, могут извлекать данные из этой таблицы, обращаясь к ней по имени EMPL:

demo@10g>select * from empl;

scott@10g>select * from empl;

Q6.10.   Создадим снимок для таблицы SALES_ORDER:

demo@10g> create snapshot  SALE_ORD as

select customer_id, to_char(ship_date,'YY') YEAR, sum(total)

from SALES_ORDER

group by (customer_id, to_char(ship_date,'YY'));

from SALES_ORDER

    *

ошибка в строке 3:

ORA-01031: привилегий недостаточно

    Пользователь SYSTEM из своей сессии выполняет команду: system@10g>grant create snapshot to demo;, после чего пользователь DEMO повторяет:

demo@10g> create snapshot  SALE_ORD as

select customer_id, to_char(ship_date,'YY') YEAR, sum(total)

from SALES_ORDER

where to_char(ship_date,'YY')='89'

group by (customer_id, to_char(ship_date,'YY'))

order by customer_id;

Материализованное представление создано.

demo@10g> select * from SALE_ORD;

Q6.11.   Удалим созданный снимок:

demo@10g>drop snapshot SALE_ORD;

Материализованное представление удалено.

III. Общая последовательность этапов выполнения лабораторных работ по освоению языка PL/SQL Oracle

1. Ознакомление с примерами PL/SQL программ по главам 1-3 и 6-10 книги  /2/

Темы этих глав:

Введение в PL/SQL (глава 1)

Основы PL/SQL (глава 2)

Записи и таблицы (глава 3)

Курсоры  (глава 6)

Подпрограммы: процедуры и функции  (глава 7)

Модули   (глава 8)

Триггеры   (глава 9)

Обработка ошибок   (глава 10)

2. Написание, отладка и выполнение программ для заданной преподавателем предметной области (такая предметная область была задана для выполнения  SQL лабораторных работ), соответствующих программам в вышеприведенных главах книги /2/.  Программы из /2/ открыты для общего пользования на сайте издательства. Книга /2/ имеется в учебном фонде МИФИ.

В нижеприводимой таблице 4 уточняется, что студент должен выполнить в соответствии с образцами программ из /2/.

Таблица 4

глава

Названия программ – примеров,

по образцам которых студент должен написать программы

для своей предметной области

Что надо выполнить на лабораторных работах (написать на PL/SQL, отладить и продемонстрировать в работе

1

3GL_4GL.SQL, CURSOR.SQL, SIMPLE.SQL, NUMERIC.SQL

Для одной из таблиц предметной области написать анонимный блок, предусматривающий использование явного курсора, цикла FOR, вывод информации с помощью DBMS_OUTPUT.PUT_LINE, а также занесение информации в служебную таблицу (типа TEMP_TABLE). Служебную таблицу потребуется создать в своей схеме.

2

ALLTHREE.SQL, ALLTHREE1.SQL, COMMENTS1.SQL,

EXITWHEN.SQL,

FORSCOPE.SQL,

GOTO.SQL,

IF1.SQL, If1b.sql,

NULL.SQL, PROC.SQL, TRIGGER.SQL

Для одной из таблиц предметной области написать:

А) хранимую процедуру, использующую курсор, цикл FOR, условие IF…THEN….ELSIF…THEN...ELSE…END IF, вывод информации с помощью DBMS_OUTPUT.PUT_LINE, а также занесение информации в служебную таблицу;

Б) триггер на вставку, проверяющий некоторое ограничение (придумать самостоятельно)  на вставляемую символьную информацию

3

ASSIGN.SQL, COUNT.SQL, DELETE.SQL, EXISTS.SQL, FRSTLAST.SQL, NULLTAB.SQL, NXTPRIOR.SQL, SELECT.SQL, TABREC.SQL

Для одной из таблиц предметной области написать

анонимный блок, предусматривающий работу с записяи и таблицами PL/SQL: заполнение таблиц, извлечение данных из таблиц, удаление строк таблицы, а также использование в работе с таблицами условия IF, простого цикла LOOP и метода NEXT

6

BADFETCH.SQL,

BINDS.SQL,

COMMIT1.SQL,

COMMIT2.SQL,

CUREXAMP.SQL,

CURSOR2.SQL,

FORLOOP.SQL,

FORUPDAT.SQL,

NOMATCH1.SQL,

NOMATCH2.SQL,

SIMPLE.SQL,

WHILE.SQL

Для нескольких  таблиц предметной области написать

анонимный блок и хранимую процедуру, использующие явный и неявный курсор. Эти блок и процедура  должны предусматривать использование связанных переменных в курсоре,  цикл FOR, извлечение и изменение значений таблицы в этом цикле FOR, использование различных курсорных атрибутов (SQL%NOTFOUND, SQL%ROWCOUNT), использование курсорной переменной

7

ADDSTUD.SQL, ALMOSTFL2.SQL, CALLFUNC.SQL,

CALLME.SQL,

CALLPROC.SQL,

CLASINFO.SQL,

DEFAULT.SQL,

ERROR.SQL,

FORWAR.SQL (MUTUAL.SQL по Скотту Урману),

FORWARD.SQL,

LOCAL2.SQL,

LOCAL.SQL,

MODETEST1.SQL,

RFCLASS2.SQL

Для нескольких  таблиц предметной области написать:

А) функцию, возвращающую символьное или числовое значение;

Б) Хранимую процедуру, использующую обращение к функции, явный курсор, цикл FOR, локальную процедуру.

Текст этого задания можно дополнительно уточнить с преподавателем.

8

CLPACK.SQL,

CLLIST.SQL,

My_Studops.sql,

OVERLOAD.SQL,

RANDOM.SQL,

EXPORT.SQL

Скомпилировать модуль export.sql для своей схемы (настройку файла иниц-х параметров- установку параметра utl_file_dir и задействование его, выдачу объектной привилегии execute на sys.utl_file владельцу схемы, из которой будет выполняться экспорт, выполнит преподаватель) и проверить его работу;

Откомпилировать модуль SPELCHEK.SQL из главы 5 и проверить его в работе, для чего предварительно создать и заполнить таблицу s_emp (скрипт s_emp.sql в главе 5).

Примеры для проверки работы взять со стр. 123-125 книги /2/

9

UPDATEMS.SQL,

INSTEAD.SQL,

ORDER.SQL,

PSEUDO.SQL,

STUDID.SQL,

RSCHANGE.SQL,

RSINSERT.SQL,

LIMMAJOR.SQL,

MUTATING.SQL,

Demobld.sql

В своей схеме создать для одной из таблиц триггер на вставку, изменение и удаление данных и продемострировать его в работе. Создать представление для одной из таблиц по типу представления на стр.208 книги /2/ и для этого представления создать триггер instead of. Создать триггер с использованием :new, :old и триггерных предикатов INSERTING, UPDATING, DELETING. Проверить в работе каскадное изменение данных (выполнить скрипт Demobld.sql и проверить его работу для таблицы DEPT по примеру на стр.223 книги /2/)

10

Выполнить все скрипты главы 10 и задать вопросы преподавателю по непонятным моментам

Написать хранимую процедуру с несколькими (!) exseption

    При выполнении лабораторных работ студенту может потребоваться выполнить корректировку «своей» предметной области (добавить столбец/столбцы в таблицу, определить его/их внешним ключем, заполнить его/их; добавить новую таблицу/таблицы и заполнить ее/их; заполнить новыми данными ранеее существовавшую/существовавшие таблицу/таблицы и т.д.). Все эти корректировки должны быть скомпанованы в отдельном скрипте, который, как и spol –файлы лабораторных работ, предъявляются преподавателю при сдаче лабораторных работ. Лабораторные работы могут выполняться студентом вне дисплейного класса, а на занятиях – предъявляться преподавателю для их сдачи. При сдаче преподаватель может предложить студенту скорректировать сдаваемые программы для выполнения предлагаемых преподавателем дополнительных действий по обработке информации предметной области.

    В разделе I уже говрилось о том, график выполнения лабораторных работ по освоению SQL и PL/SQL предусматривает  длительность аудиторной лабораторной работы для каждого студента - четыре академических часа раз в две недели. Поэтому, начиная с девятой недели (мразу после контрольной по SQL)  на каждом  из двух – трех занятий во второй половине семестра  студент должен сдавать преподавателю программы, написанные в соответствии с образцами из трех - пяти глав таблицы 4, чтобы к контрольной работе по PL/SQL в конце семестра выполнить задания по всем десяти главам из таблицы 4.

Библиографический список

  1.  Т. Кайт. Oracle для профессионалов. Книга 1.  Москва●Санкт-Петербург●Киев:  

     DIASOFT, 2004

  1.  Скотт Урман. Программирование на языке PL/SQL. Москва.: ЛОРИ, 2001.    
  2.   Валерий Юринский, Анатолий Бачин, Виктор Абрамов  Oracle 7. Практическое  руководство. SoftService. Москва, 1997.
  3.  Санжей Мишра, Алан Бьюли. Секреты Oracle SQL. Санкт-Петербург● Москва: Символ-

Плюс, 2003

  1.  Документация для Oracle10g

Приложение 1.  Скрипт demo.sql создания предметной области пользователя DEMO

Set echo on

Set termout on

drop table CUSTOMER cascade constraint;

drop table DEPARTMENT cascade constraint;

drop table EMPLOYEE cascade constraint;

drop table ITEM cascade constraint;

drop table JOB cascade constraint;

drop table LOCATION cascade constraint;

drop table PRICE cascade constraint;

drop table PRODUCT cascade constraint;

drop table SALARY_GRADE cascade constraint;

drop table SALES_ORDER cascade constraint;

alter session set nls_date_format='dd-mon-yyyy';

alter session set nls_language='AMERICAN';

CREATE TABLE JOB(

JOB_ID NUMBER(3,0) NOT NULL,

FUNCTION VARCHAR2(30) NULL,

CHECK (JOB_ID IS NOT NULL),  

CHECK (FUNCTION IS NOT NULL),  

PRIMARY KEY (JOB_ID)

);

insert into job values(       667 ,'CLERK');

insert into job values(       668 ,'STAFF');

insert into job values(       669 ,'ANALYST');

insert into job values(       670 ,'SALESPERSON');

insert into job values(       671 ,'MANAGER');

insert into job values(       672 ,'PRESIDENT');

CREATE TABLE LOCATION(

LOCATION_ID NUMBER(3,0) NOT NULL,

REGIONAL_GROUP VARCHAR2(20) NULL,  

CHECK (LOCATION_ID IS NOT NULL),  

PRIMARY KEY (LOCATION_ID)

);

insert into LOCATION values(         122 ,'NEW YORK');

insert into LOCATION values(         124 ,'DALLAS');

insert into LOCATION values(         123 ,'CHICAGO');

insert into LOCATION values(         167 ,'BOSTON');

CREATE TABLE DEPARTMENT(

DEPARTMENT_ID NUMBER(2,0) NOT NULL,

NAME VARCHAR2(14) NULL,

LOCATION_ID NUMBER(3,0) NULL,

CHECK (DEPARTMENT_ID IS NOT NULL),  

PRIMARY KEY (DEPARTMENT_ID),  

FOREIGN KEY (LOCATION_ID) REFERENCES LOCATION(LOCATION_ID)

);

insert into department values(            10 ,'ACCOUNTING',             122 );

insert into department values(            20 ,'RESEARCH',               124 );

insert into department values(            30 ,'SALES',                  123 );

insert into department values(            40 ,'OPERATIONS',             167 );

insert into department values(            12 ,'RESEARCH',               122 );

insert into department values(            13 ,'SALES',                  122 );

insert into department values(            14 ,'OPERATIONS',             122 );

insert into department values(            23 ,'SALES',                  124 );

insert into department values(            24 ,'OPERATIONS',             124 );

insert into department values(            34 ,'OPERATIONS',             123 );

insert into department values(            43 ,'SALES',                  167 );

CREATE TABLE EMPLOYEE(

EMPLOYEE_ID NUMBER(4,0) NOT NULL,

LAST_NAME VARCHAR2(15) NULL,

FIRST_NAME VARCHAR2(15) NULL,

MIDDLE_INITIAL VARCHAR2(1) NULL,

JOB_ID NUMBER(3,0) NULL,

MANAGER_ID NUMBER(4,0) NULL,

HIRE_DATE DATE NULL,

SALARY NUMBER(7,2) NULL,

COMMISSION NUMBER(7,2) NULL,

DEPARTMENT_ID NUMBER(2,0) NULL,  

CHECK (EMPLOYEE_ID IS NOT NULL),  

CHECK (DEPARTMENT_ID IS NOT NULL),  

PRIMARY KEY (EMPLOYEE_ID),  

FOREIGN KEY (JOB_ID) REFERENCES JOB(JOB_ID),  

FOREIGN KEY (MANAGER_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID),  

FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENT(DEPARTMENT_ID)

);

insert into employee values(7839,'KING',   'FRANCIS', 'A',672,NULL,'17-NOV-1985',5000,NULL,10);

insert into employee values(7505,'DOYLE',  'JEAN',    'K',671,7839,'04-APR-1985',2850,0,   13);

insert into employee values(7555,'PETERS', 'DANIEL',  'T',670,7505,'31-MAR-1985',1250,300, 13);

insert into employee values(7557,'SHAW',   'KAREN',   'P',670,7505,'02-APR-1985',1250,1200,13);

insert into employee values(7600,'PORTER', 'RAYMOND', 'Y',670,7505,'15-APR-1985',1250,900, 13);

insert into employee values(7820,'ROSS',   'PAUL',    'S',670,7505,'01-JUN-1985',1300,800, 13);

insert into employee values(7950,'JENSEN', 'ALICE',   'B',667,7505,'15-JAN-1987', 750,0,   13);

insert into employee values(7506,'DENNIS', 'LYNN',    'S',671,7839,'15-MAY-1985',2750,0,   23);

insert into employee values(7560,'DUNCAN', 'SARAH',   'S',670,7506,'31-MAY-1985',1250,0,   23);

insert into employee values(7564,'LANGE',  'GREGORY', 'J',670,7506,'01-JUN-1985',1250,300, 23);

insert into employee values(7789,'WEST',   'LIVIA',   'N',670,7506,'04-APR-1985',1500,1000,23);

insert into employee values(7954,'MURRAY', 'JAMES',   'T',667,7506,'16-JAN-1987', 750,0,   23);

insert into employee values(7507,'BAKER',  'LESLIE',  'D',671,7839,'10-JUN-1985',2200,0,   14);

insert into employee values(7609,'LEWIS',  'RICHARD', 'M',668,7507,'16-APR-1985',1800,0,   14);

insert into employee values(7676,'SOMMERS','DENISE',  'D',668,7507,'19-APR-1985',1850,0,   14);

insert into employee values(7566,'JONES',  'TERRY',   'M',671,7839,'02-APR-1985',2975,0,   20);

insert into employee values(7788,'SCOTT',  'DONALD',  'T',669,7566,'09-DEC-1986',3000,0,   20);

insert into employee values(7876,'ADAMS',  'DIANE',   'G',667,7788,'12-JAN-1987',1100,0,   20);

insert into employee values(7902,'FORD',   'JENNIFER','D',669,7566,'03-DEC-1985',3000,0,   20);

insert into employee values(7369,'SMITH',  'JOHN',    'Q',667,7902,'17-DEC-1984', 800,0,   20);

insert into employee values(7569,'ALBERTS','CHRIS',   'L',671,7839,'06-APR-1985',3000,0,   12);

insert into employee values(7799,'FISHER', 'MATTHEW', 'G',669,7569,'12-DEC-1986',3000,0,   12);

insert into employee values(7919,'DOUGLAS','MICHAEL', 'A',667,7799,'04-JAN-1987', 800,0,   12);

insert into employee values(7916,'ROBERTS','GRACE',   'M',669,7569,'04-JAN-1987',2875,0,   12);

insert into employee values(7698,'BLAKE',  'MARION',  'S',671,7839,'01-MAY-1985',2850,0,   30);

insert into employee values(7499,'ALLEN',  'KEVIN',   'J',670,7698,'20-FEB-1985',1600,300, 30);

insert into employee values(7521,'WARD',   'CYNTHIA', 'D',670,7698,'22-FEB-1985',1250,500, 30);

insert into employee values(7654,'MARTIN', 'KENNETH', 'J',670,7698,'28-SEP-1985',1250,1400,30);

insert into employee values(7844,'TURNER', 'MARY',    'A',670,7698,'08-SEP-1985',1500,0,   30);

insert into employee values(7900,'JAMES',  'FRED',    'S',667,7698,'03-DEC-1985', 950,0,   30);

insert into employee values(7782,'CLARK',  'CAROL',   'F',671,7839,'09-JUN-1985',2450,0,   10);

insert into employee values(7934,'MILLER', 'BARBARA', 'M',667,7782,'23-JAN-1986',1300,0,   10);

CREATE TABLE CUSTOMER(

CUSTOMER_ID NUMBER(6,0) NOT NULL,

NAME VARCHAR2(20) NULL,

ADDRESS VARCHAR2(21) NULL,

CITY VARCHAR2(16) NULL,

STATE VARCHAR2(2) NULL,

ZIP_CODE VARCHAR2(9) NULL,

AREA_CODE NUMBER(3,0) NULL,

PHONE_NUMBER NUMBER(7,0) NULL,

SALESPERSON_ID NUMBER(4,0) NULL,

CREDIT_LIMIT NUMBER(9,2) NULL,

CHECK (CUSTOMER_ID IS NOT NULL),  

CHECK (SALESPERSON_ID IS NOT NULL),  

CHECK (CUSTOMER_ID > 0),  

CHECK (STATE = UPPER(STATE)),  

CHECK (LENGTH(NVL(ZIP_CODE, '99999')) IN (5, 9)),  

PRIMARY KEY (CUSTOMER_ID),  

FOREIGN KEY (SALESPERSON_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID)

);

insert into customer values(100,'JOCKSPORTS',          '345 VIEWRIDGE',       'BELMONT',        'CA','96711',415,5986609,7844,5000);

insert into customer values(101,'TKB SPORT SHOP',      '490 BOLI RD.',        'REDWOOD CITY',   'CA','94061',415,3681223,7521,10000);

insert into customer values(102,'VOLLYRITE',           '9722 HAMILTON',       'BURLINGAME',     'CA','95133',415,6443341,7654,7000);

insert into customer values(103,'JUST TENNIS',         'HILLVIEW MALL',       'BURLINGAME',     'CA','97544',415,6779312,7521,3000);

insert into customer values(104,'EVERY MOUNTAIN',      '574 SURRY RD.',       'CUPERTINO',      'CA','93301',408,9962323,7499,10000);

insert into customer values(105,'K + T SPORTS',        '3476 EL PASEO',       'SANTA CLARA',    'CA','91003',408,3769966,7844,5000);

insert into customer values(106,'SHAPE UP',            '908 SEQUOIA',         'PALO ALTO',      'CA','94301',415,3649777,7521,6000);

insert into customer values(107,'WOMENS SPORTS',       'VALCO VILLAGE',       'SUNNYVALE',      'CA','93301',408,9674398,7499,10000);

insert into customer values(108,'NORTH WOODS HEALTH',  '98 LONE PINE WAY',    'HIBBING',        'MN','55649',612,5669123,7844,8000);

insert into customer values(201,'STADIUM SPORTS',      '47 IRVING PL.',       'NEW YORK',       'NY','10003',212,5555335,7557,10000);

insert into customer values(202,'HOOPS',               '2345 ADAMS AVE.',     'LEICESTER',      'MA','01524',508,5557542,7820,5000);

insert into customer values(203,'REBOUND SPORTS',      '2 E. 14TH ST.',       'NEW YORK',       'NY','10009',212,5555989,7557,10000);

insert into customer values(204,'THE POWER FORWARD',   '1 KNOTS LANDING',     'DALLAS',         'TX','75248',214,5550505,7560,12000);

insert into customer values(205,'POINT GUARD',         '20 THURSTON ST.',     'YONKERS',        'NY','10956',914,5554766,7557,3000);

insert into customer values(206,'THE COLISEUM',        '5678 WILBUR PL.',     'SCARSDALE',      'NY','10583',914,5550217,7557,6000);

insert into customer values(207,'FAST BREAK',          '1000 HERBERT LN.',    'CONCORD',        'MA','01742',508,5551298,7820,7000);

insert into customer values(208,'AL AND BOB''S SPORTS','260 YORKTOWN CT.',    'AUSTIN',         'TX','78731',512,5557631,7560,4000);

insert into customer values(211,'AT BAT',              '234 BEACHEM ST.',     'BROOKLINE',      'MA','02146',617,5557385,7820,8000);

insert into customer values(212,'ALL SPORT',           '1000 38TH ST.',       'BROOKLYN',       'NY','11210',718,5551739,7600,6000);

insert into customer values(213,'GOOD SPORT',          '400 46TH ST.',        'SUNNYSIDE',      'NY','11104',718,5553771,7600,5000);

insert into customer values(214,'AL''S PRO SHOP',      '45 SPRUCE ST.',       'SPRING',         'TX','77388',713,5555172,7564,8000);

insert into customer values(215,'BOB''S FAMILY SPORTS','400 E. 23RD',         'HOUSTON',        'TX','77026',713,5558015,7654,8000);

insert into customer values(216,'THE ALL AMERICAN',    '547 PRENTICE RD.',    'CHELSEA',        'MA','02150',617,5553047,7820,5000);

insert into customer values(217,'HIT, THROW, AND RUN', '333 WOOD COURT',      'GRAPEVINE',      'TX','76051',817,5552352,7564,6000);

insert into customer values(218,'THE OUTFIELD',        '346 GARDEN BLVD.',    'FLUSHING',       'NY','11355',718,5552131,7820,4000);

insert into customer values(221,'WHEELS AND DEALS',    '2 MEMORIAL DRIVE',    'HOUSTON',        'TX','77007',713,5554139,7789,10000);

insert into customer values(222,'JUST BIKES',          '4000 PARKRIDGE BLVD.','DALLAS',         'TX','75205',214,5558735,7789,4000);

insert into customer values(223,'VELO SPORTS',         '23 WHITE ST.',        'MALDEN',         'MA','02148',617,5554983,7820,5000);

insert into customer values(224,'JOE''S BIKE SHOP',    '4500 FOX COURT',      'GRAND PRARIE',   'TX','75051',214,5559834,7789,6000);

insert into customer values(225,'BOB''S SWIM, AND RUN','300 HORSECREEK',      'IRVING',         'TX','75039',214,5558388,7789,7000);

insert into customer values(226,'CENTURY SHOP',        '8 DAGMAR DR.',        'HUNTINGTON',     'NY','11743',516,5553006,7555,4000);

insert into customer values(227,'THE TOUR',            '2500 GARDNER RD.',    'SOMERVILLE',     'MA','02144',617,5556673,7820,5000);

insert into customer values(228 ,'FITNESS FIRST',      '5000 85TH ST.',       'JACKSON HEIGHTS','NY','11372',718,5558710,7555,4000);

CREATE TABLE SALES_ORDER(

ORDER_ID NUMBER(4,0) NOT NULL,

ORDER_DATE DATE NULL,

CUSTOMER_ID NUMBER(6,0) NULL,

SHIP_DATE DATE NULL,

TOTAL NUMBER(8,2) NULL,  

CHECK (ORDER_ID IS NOT NULL),  

CHECK (CUSTOMER_ID IS NOT NULL),  

CHECK (TOTAL >= 0),  

PRIMARY KEY (ORDER_ID),  

FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER(CUSTOMER_ID)

);

insert into sales_order values(       610 , '07-JAN-1991' ,         101 , '08-JAN-1991' ,     101.4 );

insert into sales_order values(       611 , '11-JAN-1991' ,         102 , '11-JAN-1991' ,        45 );

insert into sales_order values(       612 , '15-JAN-1991' ,         104 , '20-JAN-1991' ,      5860 );

insert into sales_order values(       601 , '16-NOV-1990' ,         106 , '30-NOV-1990' ,      60.8 );

insert into sales_order values(       602 , '05-JUN-1990' ,         102 , '20-JUN-1990' ,        56 );

insert into sales_order values(       600 , '01-MAY-1990' ,         103 , '29-MAY-1990' ,        42 );

insert into sales_order values(       604 , '15-JUN-1990' ,         106 , '30-JUN-1990' ,       642 );

insert into sales_order values(       605 , '14-JUL-1990' ,         106 , '30-JUL-1990' ,      8374 );

insert into sales_order values(       606 , '14-JUL-1990' ,         100 , '30-JUL-1990' ,       3.4 );

insert into sales_order values(       609 , '01-AUG-1990' ,         100 , '15-AUG-1990' ,     102.5 );

insert into sales_order values(       607 , '18-JUL-1990' ,         104 , '18-JUL-1990' ,       5.6 );

insert into sales_order values(       608 , '25-JUL-1990' ,         104 , '25-JUL-1990' ,      35.2 );

insert into sales_order values(       603 , '05-JUN-1990' ,         102 , '05-JUN-1990' ,       224 );

insert into sales_order values(       620 , '12-MAR-1991' ,         100 , '12-MAR-1991' ,      4450 );

insert into sales_order values(       613 , '01-FEB-1991' ,         108 , '01-FEB-1991' ,      6400 );

insert into sales_order values(       614 , '01-FEB-1991' ,         102 , '05-FEB-1991' ,     23940 );

insert into sales_order values(       616 , '03-FEB-1991' ,         103 , '10-FEB-1991' ,       764 );

insert into sales_order values(       619 , '22-FEB-1991' ,         104 , '04-MAR-1991' ,      1260 );

insert into sales_order values(       617 , '05-FEB-1991' ,         105 , '03-MAR-1991' ,     46370 );

insert into sales_order values(       615 , '01-FEB-1991' ,         107 , '06-FEB-1991' ,       710 );

insert into sales_order values(       618 , '15-FEB-1991' ,         102 , '06-MAR-1991' ,      3083 );

insert into sales_order values(       621 , '15-MAR-1991' ,         100 , '01-APR-1991' ,       730 );

insert into sales_order values(       509 , '04-JUN-1989' ,         226 , '10-JUN-1989' ,      1174 );

insert into sales_order values(       523 , '13-FEB-1990' ,         226 , '13-FEB-1990' ,      1165 );

insert into sales_order values(       549 , '27-DEC-1990' ,         226 , '14-JAN-1991' ,      1620 );

insert into sales_order values(       507 , '26-MAY-1989' ,         228 , '27-MAY-1989' ,       886 );

insert into sales_order values(       516 , '30-SEP-1989' ,         228 , '03-OCT-1989' ,      1815 );

insert into sales_order values(       553 , '02-FEB-1991' ,         228 , '13-FEB-1991' ,      4400 );

insert into sales_order values(       526 , '04-MAR-1990' ,         221 , '07-MAR-1990' ,      7700 );

insert into sales_order values(       543 , '04-SEP-1990' ,         221 , '07-SEP-1990' ,      8400 );

insert into sales_order values(       555 , '04-MAR-1991' ,         221 , '07-MAR-1991' ,      8540 );

insert into sales_order values(       559 , '16-NOV-1990' ,         222 , '23-APR-1991' ,     387.2 );

insert into sales_order values(       528 , '24-MAR-1990' ,         224 , '24-MAR-1990' ,      3770 );

insert into sales_order values(       531 , '31-MAR-1990' ,         224 , '02-APR-1990' ,      1400 );

insert into sales_order values(       558 , '31-MAR-1991' ,         224 , '02-APR-1991' ,      1700 );

insert into sales_order values(       525 , '28-FEB-1990' ,         225 , '03-MAR-1990' ,       377 );

insert into sales_order values(       552 , '02-FEB-1991' ,         223 , '07-FEB-1991' ,     555.8 );

insert into sales_order values(       556 , '10-MAR-1991' ,         223 , '12-MAR-1991' ,        85 );

insert into sales_order values(       560 , '25-APR-1991' ,         223 , '25-APR-1991' ,        72 );

insert into sales_order values(       565 , '01-JUN-1991' ,         227 , '03-JUN-1991' ,      4900 );

insert into sales_order values(       574 , '10-OCT-1991' ,         201 , '12-OCT-1991' ,      1685 );

insert into sales_order values(       576 , '13-OCT-1991' ,         201 , '18-OCT-1991' ,    2058.9 );

insert into sales_order values(       503 , '25-MAR-1989' ,         201 , '06-APR-1989' ,      1876 );

insert into sales_order values(       518 , '20-OCT-1989' ,         201 , '05-NOV-1989' ,    2932.5 );

insert into sales_order values(       517 , '23-OCT-1989' ,         201 , '07-NOV-1989' ,       784 );

insert into sales_order values(       544 , '11-SEP-1990' ,         202 , '15-SEP-1990' ,      2358 );

insert into sales_order values(       524 , '22-FEB-1990' ,         202 , '04-MAR-1990' ,      1979 );

insert into sales_order values(       502 , '10-FEB-1989' ,         202 , '23-FEB-1989' ,       500 );

insert into sales_order values(       539 , '26-JUN-1990' ,         202 , '03-JUL-1990' ,      1300 );

insert into sales_order values(       511 , '17-AUG-1989' ,         202 , '21-AUG-1989' ,       647 );

insert into sales_order values(       512 , '28-AUG-1989' ,         203 , '13-SEP-1989' ,       428 );

insert into sales_order values(       562 , '04-MAY-1991' ,         203 , '19-MAY-1991' ,    2044.5 );

insert into sales_order values(       529 , '02-APR-1990' ,         203 , '05-APR-1990' ,    1264.7 );

insert into sales_order values(       563 , '25-MAY-1991' ,         204 , '26-MAY-1991' ,       889 );

insert into sales_order values(       568 , '13-JUL-1991' ,         204 , '21-JUL-1991' ,    1217.4 );

insert into sales_order values(       504 , '11-APR-1989' ,         204 , '12-APR-1989' ,    1434.7 );

insert into sales_order values(       538 , '23-JUN-1990' ,         204 , '26-JUN-1990' ,       741 );

insert into sales_order values(       535 , '28-MAY-1990' ,         204 , '09-JUN-1990' ,       810 );

insert into sales_order values(       578 , '19-NOV-1991' ,         204 , '04-DEC-1991' ,    2275.6 );

insert into sales_order values(       534 , '11-MAY-1990' ,         206 , '15-MAY-1990' ,       420 );

insert into sales_order values(       536 , '21-MAY-1990' ,         206 , '06-JUN-1990' ,    2135.6 );

insert into sales_order values(       572 , '19-AUG-1991' ,         206 , '24-AUG-1991' ,    1200.5 );

insert into sales_order values(       514 , '05-SEP-1989' ,         207 , '16-SEP-1989' ,      1140 );

insert into sales_order values(       521 , '20-NOV-1989' ,         207 , '23-NOV-1989' ,     896.9 );

insert into sales_order values(       551 , '22-JAN-1991' ,         208 , '06-FEB-1991' ,      1142 );

insert into sales_order values(       513 , '06-SEP-1989' ,         208 , '14-SEP-1989' ,      1497 );

insert into sales_order values(       508 , '26-MAY-1989' ,         208 , '08-JUN-1989' ,      1080 );

insert into sales_order values(       515 , '19-SEP-1989' ,         208 , '26-SEP-1989' ,    1428.5 );

insert into sales_order values(       542 , '11-AUG-1990' ,         208 , '23-AUG-1990' ,      2409 );

insert into sales_order values(       573 , '10-OCT-1991' ,         201 , '12-OCT-1991' ,      1627 );

insert into sales_order values(       566 , '09-JUN-1991' ,         201 , '12-JUN-1991' ,    3389.2 );

insert into sales_order values(       520 , '16-NOV-1989' ,         201 , '19-NOV-1989' ,     341.6 );

insert into sales_order values(       519 , '20-OCT-1989' ,         201 , '05-NOV-1989' ,       955 );

insert into sales_order values(       575 , '04-OCT-1991' ,         201 , '17-OCT-1991' ,       684 );

insert into sales_order values(       547 , '16-OCT-1990' ,         202 , '27-OCT-1990' ,     984.4 );

insert into sales_order values(       540 , '15-JUL-1990' ,         202 , '17-JUL-1990' ,     861.2 );

insert into sales_order values(       567 , '05-JUL-1991' ,         202 , '07-JUL-1991' ,       200 );

insert into sales_order values(       570 , '17-JUL-1991' ,         202 , '24-JUL-1991' ,     663.6 );

insert into sales_order values(       571 , '02-AUG-1991' ,         202 , '12-AUG-1991' ,    1095.6 );

insert into sales_order values(       541 , '08-AUG-1990' ,         203 , '18-AUG-1990' ,       400 );

insert into sales_order values(       532 , '08-APR-1990' ,         203 , '21-APR-1990' ,    1295.4 );

insert into sales_order values(       527 , '27-FEB-1990' ,         204 , '14-MAR-1990' ,    3054.4 );

insert into sales_order values(       501 , '06-JAN-1989' ,         204 , '11-JAN-1989' ,       216 );

insert into sales_order values(       564 , '25-MAY-1991' ,         204 , '26-MAY-1991' ,     898.8 );

insert into sales_order values(       537 , '14-JUN-1990' ,         204 , '26-JUN-1990' ,     672.9 );

insert into sales_order values(       522 , '07-JAN-1990' ,         204 , '10-JAN-1990' ,    2578.8 );

insert into sales_order values(       554 , '18-FEB-1991' ,         204 , '22-FEB-1991' ,     282.8 );

insert into sales_order values(       569 , '16-JUL-1991' ,         205 , '23-JUL-1991' ,      2073 );

insert into sales_order values(       550 , '27-JAN-1991' ,         205 , '07-FEB-1991' ,      2356 );

insert into sales_order values(       548 , '26-NOV-1990' ,         205 , '12-DEC-1990' ,     666.9 );

insert into sales_order values(       505 , '07-APR-1989' ,         206 , '22-APR-1989' ,       650 );

insert into sales_order values(       577 , '06-OCT-1991' ,         206 , '20-OCT-1991' ,   1265.75 );

insert into sales_order values(       533 , '28-APR-1990' ,         206 , '02-MAY-1990' ,    1122.1 );

insert into sales_order values(       561 , '20-APR-1991' ,         207 , '07-MAY-1991' ,    2558.3 );

insert into sales_order values(       506 , '27-APR-1989' ,         208 , '10-MAY-1989' ,    2600.4 );

insert into sales_order values(       530 , '03-APR-1990' ,         208 , '07-APR-1990' ,    3026.5 );

insert into sales_order values(       545 , '06-OCT-1990' ,         208 , '13-OCT-1990' ,       475 );

insert into sales_order values(       557 , '08-MAR-1991' ,         208 , '12-MAR-1991' ,    2461.8 );

insert into sales_order values(       546 , '17-OCT-1990' ,         208 , '23-OCT-1990' ,      3663 );

insert into sales_order values(       510 , '18-JUL-1989' ,         208 , '21-JUL-1989' ,    1336.6 );

CREATE TABLE PRODUCT(

PRODUCT_ID NUMBER(6,0) NOT NULL,

DESCRIPTION VARCHAR2(30) NULL,  

CHECK (PRODUCT_ID IS NOT NULL),  

PRIMARY KEY (PRODUCT_ID)

);

insert into PRODUCT values(     100860 , 'ACE TENNIS RACKET I'            );

insert into PRODUCT values(     100861 , 'ACE TENNIS RACKET II'           );

insert into PRODUCT values(     100870 , 'ACE TENNIS BALLS-3 PACK'        );

insert into PRODUCT values(     100871 , 'ACE TENNIS BALLS-6 PACK'        );

insert into PRODUCT values(     100890 , 'ACE TENNIS NET'                 );

insert into PRODUCT values(     101860 , 'SP TENNIS RACKET'               );

insert into PRODUCT values(     101863 , 'SP JUNIOR RACKET'               );

insert into PRODUCT values(     102130 , 'RH: "GUIDE TO TENNIS"'          );

insert into PRODUCT values(     200376 , 'SB ENERGY BAR-6 PACK'           );

insert into PRODUCT values(     200380 , 'SB VITA SNACK-6 PACK'           );

insert into PRODUCT values(     103120 , 'WIFF SOFTBALL BAT I'            );

insert into PRODUCT values(     103121 , 'WIFF SOFTBALL BAT II'           );

insert into PRODUCT values(     103130 , 'WIFF SOFTBALL, SMALL'           );

insert into PRODUCT values(     103131 , 'WIFF SOFTBALL, LARGE'           );

insert into PRODUCT values(     103140 , 'WIFF SOFTBALL MITT (LH)'        );

insert into PRODUCT values(     103141 , 'WIFF SOFTBALL MITT (RH)'        );

insert into PRODUCT values(     102132 , 'RH: "GUIDE TO SOFTBALL"'        );

insert into PRODUCT values(     104350 , 'DUNK BASKETBALL INDOOR'         );

insert into PRODUCT values(     104351 , 'DUNK BASKETBALL OUTDOOR'        );

insert into PRODUCT values(     104352 , 'DUNK BASKETBALL PROFESSIONAL'   );

insert into PRODUCT values(     104360 , 'DUNK HOOP'                      );

insert into PRODUCT values(     104361 , 'DUNK HOOP W/FIBERGLASS BOARD'   );

insert into PRODUCT values(     104362 , 'DUNK NETS - RAINBOW'            );

insert into PRODUCT values(     102134 , 'RH: "GUIDE TO BASKETBALL"'      );

insert into PRODUCT values(     105123 , 'YELLOW JERSEY BICYCLE HELMET'   );

insert into PRODUCT values(     105124 , 'YELLOW JERSEY BICYCLE GLOVES'   );

insert into PRODUCT values(     105125 , 'YELLOW JERSEY WATER BOTTLE'     );

insert into PRODUCT values(     105126 , 'YELLOW JERSEY BOTTLE CAGE'      );

insert into PRODUCT values(     105127 , 'YELLOW JERSEY FRAME PUMP'       );

insert into PRODUCT values(     105128 , 'YELLOW JERSEY SADDLE PACK'      );

insert into PRODUCT values(     102136 , 'RH: "GUIDE TO CYCLING"'         );

CREATE TABLE ITEM(

ORDER_ID NUMBER(4,0) NOT NULL,

ITEM_ID NUMBER(4,0) NOT NULL,

PRODUCT_ID NUMBER(6,0) NULL,

ACTUAL_PRICE NUMBER(8,2) NULL,

QUANTITY NUMBER(8,0) NULL,

TOTAL NUMBER(8,2) NULL,  

CHECK (ORDER_ID IS NOT NULL),  

CHECK (ITEM_ID IS NOT NULL),  

PRIMARY KEY (ORDER_ID, ITEM_ID),  

FOREIGN KEY (ORDER_ID) REFERENCES SALES_ORDER(ORDER_ID),  

FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCT(PRODUCT_ID)

);

insert into item values(       600 ,         1 ,     100861 ,           42 ,         1 ,        42 );                                                                                                  

insert into item values(       610 ,         3 ,     100890 ,           58 ,         1 ,        58 );                                                                                                  

insert into item values(       611 ,         1 ,     100861 ,           45 ,         1 ,        45 );                                                                                                  

insert into item values(       612 ,         1 ,     100860 ,           30 ,       100 ,      3000 );                                                                                                  

insert into item values(       601 ,         1 ,     200376 ,          2.4 ,        12 ,      28.8 );                                                                                                  

insert into item values(       601 ,         2 ,     100860 ,           32 ,         1 ,        32 );                                                                                                  

insert into item values(       602 ,         1 ,     100870 ,          2.8 ,        20 ,        56 );                                                                                                  

insert into item values(       604 ,         1 ,     100890 ,           58 ,         3 ,       174 );                                                                                                  

insert into item values(       604 ,         2 ,     100861 ,           42 ,         2 ,        84 );                                                                                                  

insert into item values(       604 ,         3 ,     100860 ,           32 ,        12 ,       384 );                                                                                                  

insert into item values(       603 ,         1 ,     100860 ,           32 ,         7 ,       224 );                                                                                                  

insert into item values(       610 ,         1 ,     100860 ,           35 ,         1 ,        35 );                                                                                                  

insert into item values(       610 ,         2 ,     100870 ,          2.8 ,         3 ,       8.4 );                                                                                                  

insert into item values(       613 ,         4 ,     200376 ,          2.2 ,       200 ,       440 );                                                                                                  

insert into item values(       614 ,         1 ,     100860 ,           35 ,       444 ,     15540 );                                                                                                  

insert into item values(       614 ,         2 ,     100870 ,          2.8 ,      1000 ,      2800 );                                                                                                  

insert into item values(       612 ,         2 ,     100861 ,         40.5 ,        20 ,       810 );                                                                                                  

insert into item values(       612 ,         3 ,     101863 ,           10 ,       150 ,      1500 );                                                                                                  

insert into item values(       620 ,         1 ,     100860 ,           35 ,        10 ,       350 );                                                                                                  

insert into item values(       620 ,         2 ,     200376 ,          2.4 ,      1000 ,      2400 );                                                                                                  

insert into item values(       620 ,         3 ,     102130 ,          3.4 ,       500 ,      1700 );                                                                                                      

insert into item values(       613 ,         1 ,     100871 ,          5.6 ,       100 ,       560 );                                                                                                  

insert into item values(       613 ,         2 ,     101860 ,           24 ,       200 ,      4800 );                                                                                                  

insert into item values(       613 ,         3 ,     200380 ,            4 ,       150 ,       600 );                                                                                                  

insert into item values(       619 ,         3 ,     102130 ,          3.4 ,       100 ,       340 );                                                                                                  

insert into item values(       617 ,         1 ,     100860 ,           35 ,        50 ,      1750 );                                                                                                  

insert into item values(       617 ,         2 ,     100861 ,           45 ,       100 ,      4500 );                                                                                                  

insert into item values(       614 ,         3 ,     100871 ,          5.6 ,      1000 ,      5600 );                                                                                                  

insert into item values(       616 ,         1 ,     100861 ,           45 ,        10 ,       450 );                                                                                                  

insert into item values(       616 ,         2 ,     100870 ,          2.8 ,        50 ,       140 );                                                                                                  

insert into item values(       616 ,         3 ,     100890 ,           58 ,         2 ,       116 );                                                                                                  

insert into item values(       616 ,         4 ,     102130 ,          3.4 ,        10 ,        34 );                                                                                                  

insert into item values(       616 ,         5 ,     200376 ,          2.4 ,        10 ,        24 );                                                                                                  

insert into item values(       619 ,         1 ,     200380 ,            4 ,       100 ,       400 );                                                                                                  

insert into item values(       619 ,         2 ,     200376 ,          2.4 ,       100 ,       240 );                                                                                                  

insert into item values(       615 ,         1 ,     100861 ,           45 ,         4 ,       180 );                                                                                                  

insert into item values(       607 ,         1 ,     100871 ,          5.6 ,         1 ,       5.6 );                                                                                                  

insert into item values(       615 ,         2 ,     100870 ,          2.8 ,       100 ,       280 );                                                                                                  

insert into item values(       617 ,         3 ,     100870 ,          2.8 ,       500 ,      1400 );                                                                                                  

insert into item values(       617 ,         4 ,     100871 ,          5.6 ,       500 ,      2800 );                                                                                                  

insert into item values(       617 ,         5 ,     100890 ,           58 ,       500 ,     29000 );                                                                                                  

insert into item values(       617 ,         6 ,     101860 ,           24 ,       100 ,      2400 );                                                                                                    

insert into item values(       617 ,         7 ,     101863 ,         12.5 ,       200 ,      2500 );                                                                                                  

insert into item values(       617 ,         8 ,     102130 ,          3.4 ,       100 ,       340 );                                                                                                  

insert into item values(       617 ,         9 ,     200376 ,          2.4 ,       200 ,       480 );                                                                                                  

insert into item values(       617 ,        10 ,     200380 ,            4 ,       300 ,      1200 );                                                                                                  

insert into item values(       609 ,         2 ,     100870 ,          2.5 ,         5 ,      12.5 );                                                                                                  

insert into item values(       609 ,         3 ,     100890 ,           50 ,         1 ,        50 );                                                                                                  

insert into item values(       618 ,         1 ,     100860 ,           35 ,        23 ,       805 );                                                                                                  

insert into item values(       618 ,         2 ,     100861 ,           45 ,        50 ,      2250 );                                                                                                  

insert into item values(       618 ,         3 ,     100870 ,          2.8 ,        10 ,        28 );                                                                                                  

insert into item values(       621 ,         1 ,     100861 ,           45 ,        10 ,       450 );                                                                                                  

insert into item values(       621 ,         2 ,     100870 ,          2.8 ,       100 ,       280 );                                                                                                  

insert into item values(       615 ,         3 ,     100871 ,            5 ,        50 ,       250 );                                                                                                  

insert into item values(       608 ,         1 ,     101860 ,           24 ,         1 ,        24 );                                                                                                  

insert into item values(       608 ,         2 ,     100871 ,          5.6 ,         2 ,      11.2 );                                                                                                  

insert into item values(       609 ,         1 ,     100861 ,           40 ,         1 ,        40 );                                                                                                  

insert into item values(       606 ,         1 ,     102130 ,          3.4 ,         1 ,       3.4 );                                                                                                  

insert into item values(       605 ,         1 ,     100861 ,           45 ,       100 ,      4500 );                                                                                                  

insert into item values(       605 ,         2 ,     100870 ,          2.8 ,       500 ,      1400 );                                                                                                  

insert into item values(       605 ,         3 ,     100890 ,           58 ,         5 ,       290 );                                                                                                  

insert into item values(       605 ,         4 ,     101860 ,           24 ,        50 ,      1200 );                                                                                                  

insert into item values(       605 ,         5 ,     101863 ,          9.5 ,       100 ,       950 );                                                                                                   

insert into item values(       605 ,         6 ,     102130 ,          3.4 ,        10 ,        34 );                                                                                                  

insert into item values(       612 ,         4 ,     100871 ,          5.5 ,       100 ,       550 );                                                                                                  

insert into item values(       619 ,         4 ,     100871 ,          5.6 ,        50 ,       280 );                                                                                                  

insert into item values(       509 ,         1 ,     105123 ,           35 ,        30 ,      1050 );                                                                                                  

insert into item values(       509 ,         2 ,     105124 ,            9 ,        10 ,        90 );                                                                                                  

insert into item values(       509 ,         3 ,     102136 ,          3.4 ,        10 ,        34 );                                                                                                  

insert into item values(       523 ,         1 ,     102136 ,          3.4 ,        10 ,        34 );                                                                                                  

insert into item values(       523 ,         2 ,     105123 ,         37.7 ,        30 ,      1131 );                                                                                                  

insert into item values(       549 ,         1 ,     105123 ,           38 ,         5 ,       190 );                                                                                                  

insert into item values(       549 ,         2 ,     105127 ,            6 ,        30 ,       180 );                                                                                                  

insert into item values(       549 ,         3 ,     105125 ,            3 ,       200 ,       600 );                                                                                                  

insert into item values(       549 ,         4 ,     105124 ,           13 ,        50 ,       650 );                                                                                                  

insert into item values(       507 ,         1 ,     105123 ,         35.3 ,        20 ,       706 );                                                                                                  

insert into item values(       507 ,         2 ,     105124 ,            9 ,        20 ,       180 );                                                                                                  

insert into item values(       516 ,         1 ,     105124 ,            9 ,        60 ,       540 );                                                                                                  

insert into item values(       516 ,         2 ,     102136 ,          3.2 ,       250 ,       800 );                                                                                                  

insert into item values(       516 ,         3 ,     105125 ,          1.9 ,       250 ,       475 );                                                                                                  

insert into item values(       553 ,         1 ,     105127 ,            6 ,       500 ,      3000 );                                                                                                  

insert into item values(       553 ,         2 ,     200376 ,         1.75 ,       800 ,      1400 );                                                                                                  

insert into item values(       526 ,         1 ,     105123 ,           32 ,       100 ,      3200 );                                                                                                  

insert into item values(       526 ,         2 ,     105124 ,            9 ,       500 ,      4500 );                                                                                                       

insert into item values(       543 ,         1 ,     105123 ,           34 ,       100 ,      3400 );                                                                                                  

insert into item values(       543 ,         2 ,     105124 ,           10 ,       500 ,      5000 );                                                                                                  

insert into item values(       555 ,         1 ,     105123 ,           34 ,       100 ,      3400 );                                                                                                  

insert into item values(       555 ,         2 ,     105124 ,           10 ,       500 ,      5000 );                                                                                                  

insert into item values(       555 ,         3 ,     102136 ,          2.8 ,        50 ,       140 );                                                                                                  

insert into item values(       559 ,         1 ,     105123 ,           40 ,         5 ,       200 );                                                                                                  

insert into item values(       559 ,         2 ,     105124 ,           15 ,         5 ,        75 );                                                                                                  

insert into item values(       559 ,         3 ,     105127 ,            6 ,         5 ,        30 );                                                                                                  

insert into item values(       559 ,         4 ,     102136 ,          3.4 ,         3 ,      10.2 );                                                                                                  

insert into item values(       559 ,         5 ,     200376 ,          2.4 ,        30 ,        72 );                                                                                                  

insert into item values(       528 ,         1 ,     105123 ,         37.7 ,       100 ,      3770 );                                                                                                  

insert into item values(       531 ,         1 ,     105124 ,           11 ,       100 ,      1100 );                                                                                                  

insert into item values(       531 ,         2 ,     102136 ,            3 ,       100 ,       300 );                                                                                                  

insert into item values(       558 ,         1 ,     105124 ,           14 ,       100 ,      1400 );                                                                                                  

insert into item values(       558 ,         2 ,     102136 ,            3 ,       100 ,       300 );                                                                                                  

insert into item values(       525 ,         1 ,     105123 ,         37.7 ,        10 ,       377 );                                                                                                  

insert into item values(       552 ,         1 ,     105123 ,           40 ,        10 ,       400 );                                                                                                  

insert into item values(       552 ,         2 ,     105124 ,           15 ,        10 ,       150 );                                                                                                  

insert into item values(       552 ,         3 ,     102136 ,          3.4 ,         1 ,       3.4 );                                                                                                  

insert into item values(       552 ,         4 ,     200376 ,          2.4 ,         1 ,       2.4 );                                                                                                  

insert into item values(       556 ,         1 ,     102136 ,          3.4 ,        25 ,        85 );                                                                                                                  

insert into item values(       560 ,         1 ,     200376 ,          2.4 ,        30 ,        72 );                                                                                                  

insert into item values(       565 ,         1 ,     105123 ,           37 ,       100 ,      3700 );                                                                                                  

insert into item values(       565 ,         2 ,     105124 ,           12 ,       100 ,      1200 );                                                                                                  

insert into item values(       574 ,         1 ,     104350 ,         41.8 ,        25 ,      1045 );                                                                                                  

insert into item values(       574 ,         2 ,     200380 ,          3.2 ,       200 ,       640 );                                                                                                  

insert into item values(       576 ,         1 ,     104350 ,           44 ,        10 ,       440 );                                                                                                  

insert into item values(       576 ,         2 ,     104351 ,           26 ,        10 ,       260 );                                                                                                  

insert into item values(       576 ,         3 ,     104362 ,         4.25 ,       170 ,     722.5 );                                                                                                  

insert into item values(       576 ,         4 ,     200376 ,         2.16 ,        90 ,     194.4 );                                                                                                  

insert into item values(       576 ,         5 ,     200380 ,          3.4 ,       130 ,       442 );                                                                                                  

insert into item values(       503 ,         1 ,     104350 ,           38 ,        30 ,      1140 );                                                                                                  

insert into item values(       503 ,         2 ,     104351 ,         23.6 ,        10 ,       236 );                                                                                                  

insert into item values(       503 ,         3 ,     104352 ,           50 ,        10 ,       500 );                                                                                                  

insert into item values(       518 ,         1 ,     104350 ,           38 ,        25 ,       950 );                                                                                                  

insert into item values(       518 ,         2 ,     104351 ,         23.6 ,        20 ,       472 );                                                                                                  

insert into item values(       518 ,         3 ,     104360 ,           36 ,        20 ,       720 );                                                                                                  

insert into item values(       518 ,         4 ,     104362 ,         4.25 ,       170 ,     722.5 );                                                                                                  

insert into item values(       518 ,