Работа с подзапросами в PostgreSQL. Массивы

Новосибирский государственный технический университет

Кафедра вычислительной техники

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

по дисциплине Базы данных

«Работа с подзапросами в PostgreSQL. Массивы»

Факультет:                АВТ
Группа:                      АМ-809
Студент:                    Малко А.А.
Преподаватель:         Трошина Г.В

Вариант:                    2

Новосибирск 2011

Цель работы. Изучить базовые операции по работе с массивами. Изучить синтаксис команд. Приобрести навыки работы с подзапросами в PostgreSQL.

Задание. Ознакомиться с теоретическими сведениями о создании массивов.  Создать таблицу с полем-массивом, таблицу с полем, содержащим многомерный массив. Выполнить вставку значений в созданные таблицы (минимум по 6 записей в каждой). Выполнить выборку из созданных таблиц (в том числе продемонстрировать предотвращение выборки NULL в массивах). Осуществить выборку с использованием среза. Продемонстрировать работу функции array_dims(). Выполнить обновление данных в созданных таблицах. Осуществить модификацию среза массива, отдельного элемента массива.

Вариант 2 Найти все бакалейные товары и с ценой больше, чем средняя цена бакалейных товаров, поставляемых из Москвы. Найти все молочные продукты, поставляемых из Омска и Томска и с ценой больше, чем средняя цена товаров, поставленных за последние три месяца.

Выполнение работы

//Сведения, содержащиеся в таблицах базы:

1)  Таблица товаров (Название товара, количество на складе, тип оплаты, цена, тип товара)

2)  Таблица производителей (Название производителя, адрес, город)

3)  Таблица работы с товаром( Цена продажи, количество проданных, дата поступления на склад)

Работа с запросами

Запрос № 1.

Найти все бакалейные товары и с ценой больше,

чем средняя цена бакалейных товаров, поставляемых из Москвы.

select avg(t2.cost) from t2 inner join t3 on t3.id=t2.id where (t2.tip_tovar='bakaleya') and (t3.gorod='moskva');

При расчетах вручную получается, что средняя стоимость продукции = 402 денежных единиц. Заказы, попадающие под запрос:

Напишемзапрос:

select t2.id, t2.naz_tovar, t2.tip_tovar, t3.gorod, t2.cost

from

t2 inner join t3 on t3.id=t2.id inner join t4 on t4.id=t3.id

where

(t2.cost > (select avg(t2.cost) from t2 where (t2.tip_tovar='bakaleya') and (t3.gorod='moskva')));

Запрос № 1

Вывод: работает верно!

Запрос № 2. Найти всю продукцию, произведенную для клиентов из Омска и Томска, оплаченную наличным путем и стоимость которой больше, чем средняя стоимость продукции, произведенной за последние полгода

select avg(t2.cost) from t2,t4 where   

                                         t4.data_post>=(now()- interval '6 month')

При расчетах вручную получается, что средняя стоимость продукции = 245,2 ~ 245 денежных единиц. Заказы, попадающие под запрос:

Напишемзапрос:

update t2 set cost=250 where id=7;

select t2.id, t2.naz_tovar, t2.tip_tovar, t2.tip_oplati,t3.gorod, t2.cost

from

t2 inner join t3 on t3.id=t2.id inner join t4 on t4.id=t3.id

where

t2.tip_tovar='moloko'

and

t2.tip_oplati='nal'

and

((t3.gorod='omsk') or (t3.gorod='tomsk'))

and

(t2.cost > (select avg(t2.cost) from t2,t4 where      

                                         t4.data_post>=(now()- interval '6 month')

                    )        

          );

4)  Запрос № 2.

Вывод: работает верно!

Работа с массивами

Создание таблицы, содержащей поле с одномерным массивом тех заказов, которые делал заказчик:

1)  Создание колонки — одномерного массива:

alter table t5 add column arhiv_cen integer[];

2)  Вставка записей в созданную таблицу:

insert into t5 values(2,'{20,130,450}');

insert into t5 values(3,'{20,130,450}');

insert into t5 values(4,'{20,130,450}');

insert into t5 values(5,'{20,130,450}');

insert into t5 values(6,'{20,130,450}');

3)  Выборка из таблицы:

select * from t5;

4)  Выборка первой цены из таблицы t5:

select id,  arhiv_cen[1] from t5;

5)  Модификацияданныхвмассиве:

update t5 set arhiv_cen='{340,23,45,100}' where id=1;

6)  Выборка 4элемента из t5 с предотвращением выборки NULL:

select id,  arhiv_cen[4] from t5 where arhiv_cen[4] is not NULL;

7)  Выборка 4элемента из t5 без предотвращения выборки NULL

select id,  arhiv_cen[4] from t5;

8)  Выборка диапазона данных с использованием среза:

select id,  arhiv_cen[1:2] from t5;

9)  Вычисление количества значений, хранящихся в массиве:

select id,  array_dims(arhiv_cen) from t5;

10)  Модификациясреза:

update t5 set arhiv_cen[2:3]='{999,999}' where id=3;

11)  Модификацияотдельногоэлемента:

update t5 set arhiv_cen[3]='100500' where id=4;

12)  Создание таблицы, содержащей поле многомерный массив:

create table t6(id int, newmas int[][]);

13)  Вставка строк в созданную таблицу:

insert into t6 values(1, '{ {1,2,3,4}, {5,6,7,8}, {9,10,11,12} }');

insert into t6 values(2, '{ {13,14}, {15,16}, {17,18}, {19, 20} }');

insert into t6 values(3, '{ {21,22}, {23,24}, {26,27}, {28, 29} }');

insert into t6 values(4, '{ {30,31,32},{33,34,35}, {36,37,38} }');

select * from t6;

14)  Выборкаизсозданнойтаблицы

select id, newmas[1][2] from t6 where newmas[1][2] is not null;

15)  Выборка диапазона данных с использованием среза:

select id, newmas[2:3][2:3] from t6;

16)  Вычислениеколичестваэлементов:

select array_dims(newmas) from t6;

17)  Модификация данных в массиве:

update t6 set newmas='{{1,1,1},{2,2,2}}' where id=1;

select * from t6;

18)  Модификациясреза:

update t6 set newmas[1][1:2]='{10050,10050}' where id=2;

 select * from t6 where id=2;

19)  Модификация отдельного элемента:

update t6 set newmas[2][2]=100500 where id=3;

select * from t6;

Выводы

В результате выполнения лабораторной работы я познакомился с базовыми операциями по работе с массивами в PostgreSQL, приобрел навыки работы с подзапросами для выборки данных.