Лабораторная работа №4
По курсу СУБД ORACLE
Введение
Данная лабораторная работа ориентированна на изучение следующих конструкций и команд языка PL/SQL:
1. CREATE INDEX – создание индексов по полям (столбцам) таблицы
– простые индексы (по одному полю (столбцу))
– составные индексы (по нескольким полям (столбцам))
2. DROP INDEX – удаление индексов
3. ALTER TABLE – изменение схемы таблицы
– переименование таблицы
– изменение параметров столбцов
– определение первичного ключа таблицы
– добавление и удаление ограничений на значение поля (полей)
– разрешение и запрещение существующих ограничений
– определение внешних ключей таблицы
4. SELECT – выборка данных
– выборка данных из нескольких таблиц
– использование псевдонимов таблиц в запросе
– связь таблиц по значениям полей (соединение таблиц)
Задание
Используя схему таблицы и данные приведенные в примере (см. файл lab4_schema.sql), выполнить следующее:
1. Определить первичные ключи для всех таблиц по полям оканчивающимся на _id (у таблицы purchase определить составной ключ)
2. Определить индексы (в соответствующих таблицах):
– обеспечивающий уникальность комбинации first_name и last_name
– обеспечивающий уникальность поля product_name
– по полю quantity
3. Разработать команды UPDATE, подтверждающие наличие ограничений первичных ключей и индексов
4. Запретить NULL значения для полей purchase_date и quantity в таблице purchase
5. Разрешить NULL значения для поля hire_date в таблице salesperson
6. Определить внешние ключи для таблицы purchase (ссылки на соответствующие поля в salesperson и product)
7. Используя SELECT для выборки данных из нескольких таблиц получить следующие данные:
– Все данные о покупках продукта ‘Sok’
– Все данные о покупках в которых участвовал Ivan Ivanov
– Все данные о покупках продуктов, чья цена выше средней цены продуктов
– Все продукты, проданные продавцом Ivanov в июне 2004 года
– Все продукты, проданные продавцами Pertov и Fedorov c ценой продукта более 50
Пример
SQL> -- СОЗДАНИЕ И ЗАПОЛНЕНИЕ ТАБЛИЦЫ salesperson
SQL> --
SQL> DROP TABLE salesperson;
Table dropped.
SQL> CREATE TABLE salesperson (
2 salesperson_id INT,
3 first_name VARCHAR2(15) NOT NULL,
4 last_name VARCHAR2(20) NOT NULL,
5 hire_date DATE NOT NULL );
Table created.
SQL>
SQL> INSERT INTO salesperson VALUES (10, 'Ivan', 'Ivanov', '2003-02-01');
1 row created.
SQL> INSERT INTO salesperson VALUES (11, 'Peter', 'Petrov', '2003-10-21');
1 row created.
SQL> INSERT INTO salesperson VALUES (12, 'Fedor', 'Fedorov', '2003-06-17');
1 row created.
SQL> INSERT INTO salesperson VALUES (13, 'John', 'Johnson', '2004-09-01');
1 row created.
SQL>
SQL> --
SQL> -- СОЗДАНИЕ И ЗАПОЛНЕНИЕ ТАБЛИЦЫ product
SQL> --
SQL> DROP TABLE product;
Table dropped.
SQL> CREATE TABLE product (
2 product_id INT,
3 product_name VARCHAR2(25) NOT NULL,
4 product_price NUMBER(4,2) NOT NULL,
5 quantity_on_hand NUMBER(5,0) NOT NULL,
6 last_stock_date DATE );
Table created.
SQL>
SQL> INSERT INTO product VALUES (5, 'Moloko', 30.00, 10, '2004-09-15');
1 row created.
SQL> INSERT INTO product VALUES (6, 'Kefir', 41.00, 5, '2004-08-15');
1 row created.
SQL> INSERT INTO product VALUES (7, 'Tvorog', 31.50, 2, '2004-08-01');
1 row created.
SQL> INSERT INTO product VALUES (8, 'Sok', 52.10, 18, TO_DATE('15.10.2004','DD.MM.YYYY'));
1 row created.
SQL> INSERT INTO product VALUES (9, 'Slivki', 32.20, 4, '2004-09-15');
1 row created.
SQL>
SQL> --
SQL> -- СОЗДАНИЕ И ЗАПОЛНЕНИЕ ТАБЛИЦЫ purchase
SQL> --
SQL> DROP TABLE purchase;
Table dropped.
SQL> CREATE TABLE purchase (
2 salesperson_id INT,
3 product_id INT,
4 purchase_date DATE,
5 quantity NUMBER(4,2) );
Table created.
SQL>
SQL> INSERT INTO purchase VALUES (1, 5, '2004-06-15', 9);
1 row created.
SQL> INSERT INTO purchase VALUES (10, 1, '2004-06-15', 9);
1 row created.
SQL> INSERT INTO purchase VALUES (10, 5, '2004-06-15', 1);
1 row created.
SQL> INSERT INTO purchase VALUES (10, 6, '2004-06-16', 2);
1 row created.
SQL> INSERT INTO purchase VALUES (11, 8, '2004-07-14', 3);
1 row created.
SQL> INSERT INTO purchase VALUES (12, 7, '2004-04-07', 5);
1 row created.
SQL> INSERT INTO purchase VALUES (10, 8, '2004-09-25', 1);
1 row created.
SQL> INSERT INTO purchase VALUES (13, 5, '2004-07-08', 7);
1 row created.
SQL> INSERT INTO purchase VALUES (13, 6, '2004-07-08', 1);
1 row created.
SQL> --
SQL> -- ИНДЕКСЫ
SQL> --
SQL>
SQL> -- создать индекс с именем 'salesperson_lastname_idx'
SQL> -- по полю last_name таблицы salesperson
SQL> CREATE INDEX salesperson_lastname_idx
2 ON salesperson (last_name);
Index created.
SQL>
SQL> -- удалить ранее созданный индекс
SQL> DROP INDEX salesperson_lastname_idx;
Index dropped.
SQL>
SQL> -- создать индекс допускающий только уникальные значения
SQL> CREATE UNIQUE INDEX salesperson_lastname_idx
2 ON salesperson (last_name);
Index created.
SQL>
SQL> DROP INDEX salesperson_lastname_idx;
Index dropped.
SQL>
SQL> -- создать индекс который ссылается на значения
SQL> -- в порядке возрастания
SQL> CREATE UNIQUE INDEX salesperson_lastname_idx
2 ON salesperson (last_name ASC);
Index created.
SQL>
SQL> DROP INDEX salesperson_lastname_idx;
Index dropped.
SQL>
SQL> -- создать индекс который ссылается на значения
SQL> -- в порядке убывания
SQL> CREATE UNIQUE INDEX salesperson_lastname_idx
2 ON salesperson (last_name DESC);
Index created.
SQL>
SQL> DROP INDEX salesperson_lastname_idx;
Index dropped.
SQL>
SQL> -- создать составной индекс
SQL> CREATE INDEX salesperson_name_idx
2 ON salesperson (first_name, last_name);
Index created.
SQL>
SQL> DROP INDEX salesperson_name_idx;
Index dropped.
SQL>
SQL>
SQL> --
SQL> -- ИЗМЕНЕНИЕ СХЕМЫ ТАБЛИЦЫ
SQL> --
SQL>
SQL> -- переименование таблицы
SQL> ALTER TABLE salesperson RENAME TO salesperson_new;
Table altered.
SQL> SELECT * FROM salesperson;
SELECT * FROM salesperson
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
SQL> ALTER TABLE salesperson_new RENAME TO salesperson;
Table altered.
SQL>
SQL> -- разрешим NULL значения для поля first_name
SQL> ALTER TABLE salesperson MODIFY (first_name NULL);
Table altered.
SQL>
SQL> -- запретим NULL значения для поля first_name
SQL> ALTER TABLE salesperson MODIFY (first_name NOT NULL);
Table altered.
SQL>
SQL> -- добавим поле salary в таблицу salesperson,
SQL> -- со значением по умолчанию = 100 и запретом на NULL
SQL> ALTER TABLE salesperson ADD (salary NUMBER(6,2) DEFAULT 100 NOT NULL);
Table altered.
SQL> SELECT * FROM salesperson;
SALESPERSON_ID FIRST_NAME LAST_NAME HIRE_DATE SALARY
-------------- --------------- -------------------- ---------- ----------
10 Ivan Ivanov 2003-02-01 100
11 Peter Petrov 2003-10-21 100
12 Fedor Fedorov 2003-06-17 100
13 John Johnson 2004-09-01 100
SQL>
SQL> -- удалим добавленное поле
SQL> ALTER TABLE salesperson DROP COLUMN salary;
Table altered.
SQL> SELECT * FROM salesperson;
SALESPERSON_ID FIRST_NAME LAST_NAME HIRE_DATE
-------------- --------------- -------------------- ----------
10 Ivan Ivanov 2003-02-01
11 Peter Petrov 2003-10-21
12 Fedor Fedorov 2003-06-17
13 John Johnson 2004-09-01
SQL>
SQL> -- определение первичного ключа таблицы по полю salesperson_id
SQL> ALTER TABLE salesperson ADD PRIMARY KEY (salesperson_id);
Table altered.
SQL>
SQL> -- удалиение определения первичного ключа
SQL> ALTER TABLE salesperson DROP PRIMARY KEY;
Table altered.
SQL>
SQL> --
SQL> -- добавление и удаление ограничений на значение поля (полей)
SQL> --
SQL>
SQL> -- потребуем уникальности поля hire_date
SQL> ALTER TABLE salesperson
2 ADD CONSTRAINT salesperson_unique_hire_date UNIQUE (hire_date);
Table altered.
SQL>
SQL> ALTER TABLE salesperson DROP CONSTRAINT salesperson_unique_hire_date;
Table altered.
SQL>
SQL> -- потребуем чтобы значение поля hire_date было более
SQL> -- определенной величины
SQL> ALTER TABLE salesperson
2 ADD CONSTRAINT salesperson_unique_hire_date CHECK (hire_date > TO_DATE('31/12/2002', 'DD/MM/YYYY'));
Table altered.
SQL>
SQL> -- попробуем вставить ошибочные данные
SQL> INSERT INTO salesperson VALUES(100, 'A', 'Smith', TO_DATE('10/01/1999', 'DD/MM/YYYY'));
INSERT INTO salesperson VALUES(100, 'A', 'Smith', TO_DATE('10/01/1999', 'DD/MM/YYYY'))
*
ERROR at line 1:
ORA-02290: check constraint (SYSTEM.SALESPERSON_UNIQUE_HIRE_DATE) violated
SQL>
SQL> -- запрещение существующих ограничений
SQL> ALTER TABLE salesperson DISABLE CONSTRAINT salesperson_unique_hire_date;
Table altered.
SQL>
SQL> -- попробуем вставить ошибочные данные
SQL> INSERT INTO salesperson VALUES(100, 'A', 'Smith', TO_DATE('10/01/1999', 'DD/MM/YYYY'));
1 row created.
SQL>
SQL> -- разрешение существующих ограничений (в таблице есть запись
SQL> -- нарушающая это ограничение!)
SQL> ALTER TABLE salesperson ENABLE CONSTRAINT salesperson_unique_hire_date;
ALTER TABLE salesperson ENABLE CONSTRAINT salesperson_unique_hire_date
*
ERROR at line 1:
ORA-02293: cannot validate (SYSTEM.SALESPERSON_UNIQUE_HIRE_DATE) - check
constraint violated
SQL>
SQL> -- удалим строки с ошибками
SQL> DELETE salesperson WHERE hire_date <= TO_DATE('31/12/2002', 'DD/MM/YYYY');
1 row deleted.
SQL>
SQL> -- снова разрешим ограничение
SQL> ALTER TABLE salesperson ENABLE CONSTRAINT salesperson_unique_hire_date;
Table altered.
SQL>
SQL>
SQL> --
SQL> -- определение внешних ключей таблицы
SQL> --
SQL>
SQL> -- определение первичного ключа таблицы по полю salesperson_id
SQL> ALTER TABLE salesperson ADD PRIMARY KEY (salesperson_id);
Table altered.
SQL>
SQL> -- добавим в таблицу purchase опредение внешнего ключа - ссылку
SQL> -- на поле в таблице salesperson
SQL> ALTER TABLE purchase
2 ADD CONSTRAINT fk_purchase_to_salesperson
3 FOREIGN KEY (salesperson_id)
4 REFERENCES salesperson (salesperson_id);
ADD CONSTRAINT fk_purchase_to_salesperson
*
ERROR at line 2:
ORA-02298: cannot validate (SYSTEM.FK_PURCHASE_TO_SALESPERSON) - parent keys