Работа с подзапросами в PostgreSQL. Изучение базовых операций по работе с массивами

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

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

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

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

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

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

Вариант:                    1

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

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

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

Вариант 1. Найти всех нападающих с правым хватом клюшки и в возрасте больше, чем средний возраст нападающих с левым хватом клюшки. Найти всех защитников, набравших более 5 очков и с ростом больше, чем средний рост защитников в возрасте до 25 лет.

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

//Структура базы данных var1v3:

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

//Нахождение всех нападающих с правым хватом клюшки и в возрасте больше, чем //средний возраст нападающих с левым хватом клюшки

select humans.id, humans.name, surname, bday, position.name as position, grip

from humans inner join position on humans.posid=position.id

where grip='right' and position.name='forward'

and extract(year from now()) - extract(year from bday) >

(select avg(extract(year from now()) - extract(year from bday))

from humans inner join position on humans.posid=position.id

where grip='left' and position.name='forward')

order by humans.id;

//Нахождение всех защитников, набравших более 5 очков и с ростом больше, чем //средний рост защитников в возрасте до 25 лет

select humans.id, humans.name, surname, position.name as position,

h as height, sumgoals, sumassist

from humans inner join position on humans.posid=position.id

inner join (select idhuman, sum(goals) as sumgoals, sum(assist) as sumassist

from games group by idhuman) as t on humans.id=t.idhuman

where position.name='defender' and sumgoals + sumassist > 5

and h > (select avg(h) from humans inner join position on humans.posid=position.id

where position.name='defender' and extract(year from now()) - extract(year from bday) < 25)

order by humans.id;

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

var1v3=> create table phones(idhuman int, numbers varchar(12)[]);

CREATE TABLE

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

var1v3=> insert into phones values(0, '{"+79231018808","+79134880654"}');

INSERT 0 1

var1v3=> insert into phones values(2, '{"+79130011234","+79131234567","+79521234567"}');

INSERT 0 1

var1v3=> insert into phones values(3, NULL);

INSERT 0 1

var1v3=> insert into phones values(4, '{"+79130028888","+79139876543","+79527654321"}');

INSERT 0 1

var1v3=> insert into phones values(1,NULL);

INSERT 0 1

var1v3=> insert into phones values(5,'{"+79234561234"}');

INSERT 0 1

//Выборка из таблицы phones:

var1v3=> select * from phones order by idhuman;

//Выборка первого номера телефона из таблицы phones:

var1v3=> select idhuman, numbers[1] from phones order by idhuman;

//Выборка второго номера телефона из phones с предотвращением выборки NULL:

var1v3=> select idhuman, numbers[2] from phones where numbers[2] is not null

var1v3-> order by idhuman;

//Выборка диапазона телефонов с использованием среза:

var1v3=> select idhuman, numbers[2:3] from phones order by idhuman;

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

var1v3=> select array_dims(numbers) from phones;

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

var1v3=> update phones set numbers='{"+79230000000","+79001112345"}' where idhuman=5;

UPDATE 1

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

var1v3=> update phones set numbers[1:2]='{"+79239231234","+79139131234"}' where idhuman=4;

UPDATE 1

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

var1v3=> update phones set numbers[3]='+70000000000' where idhuman=4;

UPDATE 1

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

var1v3=> create table t(id int, val int[][]);

CREATE TABLE

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

var1v3=> insert into t values(1,'{{1,2,3},{4,5,6},{7,8,9},{10,11,12},{13,14,15}}');

INSERT 0 1

var1v3=> insert into t values(2,'{{1,2,3,4,5},{6,7,8,9,10},{11,12,13,14,15}}');

INSERT 0 1

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

INSERT 0 1

var1v3=> insert into t values(4,'{{1,2,3,4,5,6,7,8},{8,7,6,5,4,3,2,1}}');

INSERT 0 1

var1v3=> insert into t values(5,'{}');

INSERT 0 1

var1v3=> insert into t values(0,'{{1,2,3,4,5}}');

INSERT 0 1

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

var1v3=> select * from t;

var1v3=> select id, val[2][1] from t where val[2][1] is not null order by id;

var1v3=> select id, val[2:3][1:2] from t order by id;

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

var1v3=> select array_dims(val) from t;

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

var1v3=> update t set val='{{55,44,33,22,11}}' where id=0;

UPDATE 1

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

var1v3=> update t set val[1][1:2]='{11,22}' where id=1;

UPDATE 1

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

var1v3=> update t set val[1][1]=100500 where id=0;

UPDATE 1

Выводы

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