Изучение конструкций и команд языка PL/SQL

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