Лабораторная работа №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

not found

SQL>

SQL> -- удалим противоречивые записи из таблицы purchase

SQL> DELETE purchase

2 WHERE salesperson_id NOT IN (SELECT salesperson_id FROM salesperson);

1 row deleted.