Лабораторная работа по "Информатике"

ПОВОЛЖСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ ТЕЛЕКОММУНИКАЦИЙ И ИНФОРМАТИКИ

Лабораторная работа №4

по курсу СУБД ORACLE

Выполнили: студентки

гр. УИТС-91

Базаркина Д.,

Гурьянова П.

Проверил: Лемжин М.И.

САМАРА 2012

Таблица salesperson

CREATE TABLE salesperson (salesperson_id INT,              first_name VARCHAR2(15) NOT NULL, last_name VARCHAR2(20) NOT NULL, hire_date DATE NOT NULL );

begin

INSERT INTO salesperson VALUES (10, 'Ivan', 'Ivanov', '01-02-2003');

INSERT INTO salesperson VALUES (11, 'Peter', 'Petrov', '21-10-2003');

INSERT INTO salesperson VALUES (12, 'Fedor', 'Fedorov', '17-06-2003');

INSERT INTO salesperson VALUES (13, 'John', 'Johnson', '01-09-2004');

End

Таблица product

CREATE TABLE product (product_id INT, product_name VARCHAR2(25) NOT NULL, product_price NUMBER(4,2) NOT NULL, quantity_on_hand NUMBER(5,0) NOT NULL, last_stock_date DATE );

begin

INSERT INTO product VALUES (5, 'Moloko', 30.00, 10, '15-09-2004');

INSERT INTO product VALUES (6, 'Kefir', 41.00, 5, '15-08-2004');

INSERT INTO product VALUES (7, 'Tvorog', 31.50, 2, '01-08-2004');

INSERT INTO product VALUES (8, 'Sok', 52.10, 18, TO_DATE('15.10.2004','DD.MM.YYYY'));

INSERT INTO product VALUES (9, 'Slivki', 32.20, 4, '15-09-2004');

End

Таблица purchase

CREATE TABLE purchase ( salesperson_id  INT, product_id              INT, purchase_date DATE, quantity NUMBER(4,2) );

begin

INSERT INTO purchase VALUES (1, 5,  '15-06-2004', 9);

INSERT INTO purchase VALUES (10, 1, '15-06-2004', 9);

INSERT INTO purchase VALUES (10, 5, '15-06-2004', 1);

INSERT INTO purchase VALUES (10, 6, '16-06-2004', 2);

INSERT INTO purchase VALUES (11, 8, '14-07-2004', 3);

INSERT INTO purchase VALUES (12, 7, '07-04-2004', 5);

INSERT INTO purchase VALUES (10, 8, '25-09-2004', 1);

INSERT INTO purchase VALUES (13, 5, '08-07-2004', 7);

INSERT INTO purchase VALUES (13, 6, '08-07-2004', 1);

End

              Определить первичные ключи для всех таблиц по полям оканчивающимся на _id (у таблицы purchase определить составной ключ)

ALTER TABLE salesperson ADD PRIMARY KEY (salesperson_id)

ALTER TABLE product ADD PRIMARY KEY (product_id)

ALTER TABLE purchase ADD PRIMARY KEY (salesperson_id, product_id)

Определить индексы (в соответствующих таблицах):

обеспечивающий уникальность комбинации first_name и last_name

CREATE UNIQUE INDEX salesperson_name_idx ON salesperson (last_name, first_name)

обеспечивающий уникальность поля product_name

CREATE UNIQUE INDEX product_name_idx ON product (product_name)

по полю quantity

CREATE INDEX quantity_idx ON purchase (quantity)

Запретить NULL значения для полей purchase_date и quantity в таблице purchase

ALTER TABLE purchase MODIFY (purchase_date NOT NULL)

ALTER TABLE purchase MODIFY (quantity NOT NULL)

Разрешить NULL значения для поля hire_date в таблице salesperson

ALTER TABLE salesperson MODIFY (hire_date NULL)

Определить внешние ключи для таблицы purchase (ссылки на соответствующие поля в salesperson и product)

DELETE purchase

WHERE salesperson_id NOT IN (SELECT salesperson_id FROM salesperson)

ALTER TABLE purchase

ADD CONSTRAINT fk_purchase_to_salesperson

              FOREIGN KEY (salesperson_id)

              REFERENCES salesperson (salesperson_id)

DELETE purchase

WHERE product_id NOT IN (SELECT product_id FROM product)

ALTER TABLE purchase

ADD CONSTRAINT fk_purchase_to_product

              FOREIGN KEY (product_id)

              REFERENCES product (product_id)

Используя SELECT для выборки данных из нескольких таблиц получить следующие данные:

Все данные о покупках продукта ‘Sok’

SELECT * FROM product, purchase, salesperson  WHERE product_name LIKE 'Sok'

Все данные о покупках в которых участвовал Ivan Ivanov

SELECT * FROM salesperson, product, purchase WHERE last_name LIKE 'Ivanov'

Все данные о покупках продуктов, чья цена выше средней цены продуктов

SELECT * FROM product, salesperson, purchase WHERE product_price > (SELECT AVG(product_price) FROM product)

Все продукты, проданные продавцом Ivanov в июне 2004 года

SELECT * FROM salesperson, product, purchase WHERE last_name LIKE 'Ivanov' AND purchase_date LIKE '%06.04'

Все продукты, проданные продавцами Pertov и Fedorov c ценой продукта более 50

SELECT * FROM salesperson, product, purchase WHERE last_name LIKE '%rov' AND product_price > 50