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

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

Введение

Данная лабораторная работа ориентированна на изучение команд языка PL/SQL:

1.      SELECT

—   форматирование результатов работы SELECT

—   выборка данных (строк) из таблицы соответствующих некому критерию

—   упорядочивание получаемых данных (строк)

—   исключение повторяющихся значений

—   использование функций агрегирования (групповых функций)

—   группировка значений

—   применение простых вложенных запросов (подзапросов)

2. UPDATE

—   обновление данных (строк) в таблице

—   обновление данных (строк) в таблице соответствующих некому критерию

3. DELETE

—   удаление данных (строк) из таблицы соответствующих некому критерию

Задание

1.      Создать таблицу product со следующей схемой:

Поле (столбец)	Тип данных
product_id	INT
name	VARCHAR2(25)
price	NUMBER(7,2)
vat_tax	NUMBER(2,2)
quantity	INT

2.      Заполнить таблицу следующими данными:

product_id	name	price	vat_tax	quantity
1	CD-RW Disk	20.0	0.18	100
2	CD-R Disk	15.0	0.18	200
3	3.5" Floppy Disk	10.0	0.18	30
4	DVD-R Disk	80.0	0.18	10
5	DVD+R Disk	80.0	0.18	10
6	DVD-RW Disk	300.0	0.18	5
7	DVD+RW Disk	300.0	0.18	5
8	Red Ink Marker	60.0	0.18	10
9	Green Ink Marker	60.0	0.18	10
10	Blue Ink Marker	60.0	0.18	10
11	Blue Ink Marker	80.0	0.18	NULL
12	Milk 1 lt.	20.0	0.10	NULL
13	Milk 3 lt.	60.0	0.10	NULL
14	Milk 3 lt.	65.0	0.10	5

3.      Составить и выполнить команды SELECT

—   Вывести название и цену всех DVD дисков, задать псевдоним столбца ‘DVD disks’

—   Вывести название и цену всех CD дисков полная цена которых (с учетом НДС) более 15 единиц, упорядочив строки по убыванию полной цены

—   Вывести название и цену всех товаров с ценой в диапазоне

—   Вывести название и цену всех перезаписываемых (RW) дисков с ценой в диапазоне (20, 300]

—   Вывести названия товаров со словом ‘Marker’, исключив повторения

—   Рассчитать общую суммарную стоимость всех товаров со словом ‘Disk’ в названии

—   Рассчитать среднюю цену товара со словом ‘Marker’ в названии, используя товары чье количество не равно NULL

—   Рассчитать среднюю величину НДС для дисков DVD+ и Floppy

—   Вывести названия и цены DVD дисков, цена на которые выше средней цены всех DVD дисков

—   Вывести товары, чья цена равна цене на какой либо CD или DVD диск

—   Вывести среднюю, максимальную и минимальную цену товара для товаров (каждой группы) величина НДС у которых одинакова

—   Вывести количество товаров величина НДС у которых одинакова (для каждой величины НДС)

—   Вывести название, среднюю цену и количество товаров, чья общая суммарная стоимость выше, чем общая суммарная стоимость товаров со словом ‘Marker’ в названии

4.      Составить и выполнить команды UPDATE

—   Увеличить цену всех товаров на 3%

—   Для CD дисков заменить слово ‘CD’ в названии на ‘TDK CD’ и увеличить их цену на 10%

—   Снизить цену на 7% у всех товаров чья цена выше средней (используя простой подзапрос в предложении WHERE)

—   Для товаров с product_id равным 5,7,10 установить ставку HДС на уровне 14%

—   Для таблицы, содержащей исходные данные, выполнить: для товаров чья цена менее 60 увеличить цену на 50%, а для товаров чья цена более 80 уменьшить цену на 50%

5.      Составить и выполнить команды DELETE

—   Удалить все товары, чья цена меньше 12 или их количество равно 0 (или NULL)

—   Удалить все товары, чья цена равна минимальной цене

—   Удалить товары количество которых менее 10% от среднего количества товаров с такой же величиной НДС

6.      Удалить таблицу

Примеры

SQL> -- СОЗДАНИЕ ТАБЛИЦЫ

SQL> --

SQL> CREATE TABLE product (

2 product_id INT,

3 name VARCHAR2(25),

4 price NUMBER(6,2),

5 vat_tax NUMBER(2,2),

6 quantity INT);

Table created.

SQL>

SQL> --

SQL> -- ВСТАВКА ДАННЫХ

SQL> --

SQL> INSERT INTO product VALUES (1, 'CD-RW Disk', 20.00, 0.20, 100);

1 row created.

SQL> INSERT INTO product VALUES (2, 'CD-R Disk', 15.00, 0.20, 200);

1 row created.

SQL> INSERT INTO product VALUES (3, '3.5" Floppy Disk', 10.00, 0.20, 30);

1 row created.

SQL> INSERT INTO product VALUES (4, 'DVD-R Disk', 80.00, 0.20, 10);

1 row created.

SQL> INSERT INTO product VALUES (5, 'DVD+R Disk', 80.00, 0.20, 10);

1 row created.

SQL> INSERT INTO product VALUES (6, 'DVD-RW Disk', 300.00, 0.20, 5);

1 row created.

SQL> INSERT INTO product VALUES (7, 'DVD+RW Disk', 300.00, 0.20, 5);

1 row created.

SQL> INSERT INTO product VALUES (8, 'Red Ink Marker', 60.00, 0.20, 10);

1 row created.

SQL> INSERT INTO product VALUES (9, 'Green Ink Marker', 60.00, 0.20, 10);

1 row created.

SQL> INSERT INTO product VALUES (10, 'Blue Ink Marker', 60.00, 0.20, 10);

1 row created.

SQL> INSERT INTO product VALUES (11, 'Blue Ink Marker', 80.00, 0.20, NULL);

1 row created.

SQL> INSERT INTO product VALUES (12, 'Milk 1 lt.', 20.00, 0.12, NULL);

1 row created.

SQL> INSERT INTO product VALUES (13, 'Milk 3 lt.', 60.00, 0.12, NULL);

1 row created.

SQL> INSERT INTO product VALUES (14, 'Milk 3 lt.', 65.00, 0.12, 5);

1 row created.

SQL>

SQL> --

SQL> -- ФОРМАТИРОВАНИЕ РЕЗУЛЬТАТОВ РАБОТЫ SELECT

SQL> --

SQL>

SQL> -- использование констант для задания значений столбцов

SQL> SELECT name, '$', 10 FROM product;

NAME ' 10

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

CD-RW Disk $ 10

CD-R Disk $ 10

3.5" Floppy Disk $ 10

DVD-R Disk $ 10

DVD+R Disk $ 10

DVD-RW Disk $ 10

DVD+RW Disk $ 10

Red Ink Marker $ 10

Green Ink Marker $ 10

Blue Ink Marker $ 10

Blue Ink Marker $ 10

NAME ' 10

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

Milk 1 lt. $ 10

Milk 3 lt. $ 10

Milk 3 lt. $ 10

14 rows selected.

SQL>

SQL> -- задание псевдонимов столбцов

SQL> SELECT name "Product Name", price * (1 + vat_tax) "Full Price" FROM product;

Product Name Full Price

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

CD-RW Disk 24

CD-R Disk 18

3.5" Floppy Disk 12

DVD-R Disk 96

DVD+R Disk 96

DVD-RW Disk 360

DVD+RW Disk 360

Red Ink Marker 72

Green Ink Marker 72

Blue Ink Marker 72

Blue Ink Marker 96

Product Name Full Price

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

Milk 1 lt. 22.4

Milk 3 lt. 67.2

Milk 3 lt. 72.8

14 rows selected.

SQL>

SQL> -- конкатенация (сцепление) нескольких значений в один столбец

SQL> SELECT name || '-' || name, price FROM product;

NAME||'-'||NAME PRICE

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

CD-RW Disk-CD-RW Disk 20

CD-R Disk-CD-R Disk 15

3.5" Floppy Disk-3.5" Floppy Disk 10

DVD-R Disk-DVD-R Disk 80

DVD+R Disk-DVD+R Disk 80

DVD-RW Disk-DVD-RW Disk 300

DVD+RW Disk-DVD+RW Disk 300

Red Ink Marker-Red Ink Marker 60

Green Ink Marker-Green Ink Marker 60

Blue Ink Marker-Blue Ink Marker 60

Blue Ink Marker-Blue Ink Marker 80

NAME||'-'||NAME PRICE

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

Milk 1 lt.-Milk 1 lt. 20

Milk 3 lt.-Milk 3 lt. 60

Milk 3 lt.-Milk 3 lt. 65

14 rows selected.

SQL> SELECT name || '-' || name "Product-Product", price FROM product;

Product-Product PRICE

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

CD-RW Disk-CD-RW Disk 20

CD-R Disk-CD-R Disk 15

3.5" Floppy Disk-3.5" Floppy Disk 10

DVD-R Disk-DVD-R Disk 80

DVD+R Disk-DVD+R Disk 80

DVD-RW Disk-DVD-RW Disk 300

DVD+RW Disk-DVD+RW Disk 300

Red Ink Marker-Red Ink Marker 60

Green Ink Marker-Green Ink Marker 60

Blue Ink Marker-Blue Ink Marker 60

Blue Ink Marker-Blue Ink Marker 80

Product-Product PRICE

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

Milk 1 lt.-Milk 1 lt. 20

Milk 3 lt.-Milk 3 lt. 60

Milk 3 lt.-Milk 3 lt. 65

14 rows selected.

SQL> --

SQL> -- ВЫБОРКА ДАННЫХ (СТРОК) ИЗ ТАБЛИЦЫ СООТВЕТСТВУЮЩИХ НЕКОМУ КРИТЕРИЮ

SQL> --

SQL>

SQL> -- выборка строк по значению одного столбца

SQL> SELECT name, price FROM product WHERE price=10;

NAME PRICE

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

3.5" Floppy Disk 10

SQL> SELECT name, price FROM product WHERE price<10;

no rows selected

SQL> SELECT name, price FROM product WHERE price>10;

NAME PRICE

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

CD-RW Disk 20

CD-R Disk 15

DVD-R Disk 80

DVD+R Disk 80

DVD-RW Disk 300

DVD+RW Disk 300

Red Ink Marker 60

Green Ink Marker 60

Blue Ink Marker 60

Blue Ink Marker 80

Milk 1 lt. 20

NAME PRICE

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

Milk 3 lt. 60

Milk 3 lt. 65

13 rows selected.

SQL> SELECT name, price FROM product WHERE price<>10;

NAME PRICE

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

CD-RW Disk 20

CD-R Disk 15

DVD-R Disk 80

DVD+R Disk 80

DVD-RW Disk 300

DVD+RW Disk 300

Red Ink Marker 60

Green Ink Marker 60

Blue Ink Marker 60

Blue Ink Marker 80

Milk 1 lt. 20

NAME PRICE

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

Milk 3 lt. 60

Milk 3 lt. 65

13 rows selected.

SQL> SELECT name, price FROM product WHERE price!=10;

NAME PRICE

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

CD-RW Disk 20

CD-R Disk 15

DVD-R Disk 80

DVD+R Disk 80

DVD-RW Disk 300

DVD+RW Disk 300

Red Ink Marker 60

Green Ink Marker 60

Blue Ink Marker 60

Blue Ink Marker 80

Milk 1 lt. 20

NAME PRICE

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

Milk 3 lt. 60

Milk 3 lt. 65

13 rows selected.

SQL> SELECT name, price FROM product WHERE name='CD-R Disk';

NAME PRICE

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

CD-R Disk 15

SQL>

SQL> -- использование логических операций в условии WHERE