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

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

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

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

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

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

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

Вариант:                    8

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

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

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

Вариант 8. Найти всех студентов, проживающих во 2 и 6 общежитии, обучающихся в 4 и 7 корпусах и получающих стипендию больше, чем средняя стипендия студентов факультетов ФЭН и АВТФ. Найти всех студентов, обучающихся на 2 и 3 курсах факультетов ФЭН и ФБ, и получающих стипендию больше, чем средняя стипендия студентов из Омска и Томска.

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

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

Рис.1. Таблица дела студента (имя, фамилия, пол, курс, стипендия, дата рождения)

Рис.2. Таблица личной информации о студентах (имя, фамилия, улица, дом, пол, дата рождения, город)

Рис.3. Таблица по факультетам (факультет, группа, курс, стипендия)

Рис.4. Таблица общежития (корпус факультета, комната, номер общежития)

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

Запрос № 1. Найти всех студентов, проживающих во 2 и 6 общежитии, обучающихся в 4 и 7 корпусах и получающих стипендию больше, чем средняя стипендия студентов факультетов ФЭН и АВТФ.

При расчетах вручную получается, что средняя стипендия студентов ФЭНа и АВТФа = 1410 денежных единиц, а студенты, попадающие под запрос: 1) Степа Ивин, 4) Катя Воротова, 5) Влад Корнев

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

select delo.id, delo.name, delo.famil, fac.stip, fac.dep, obsh.corp, obsh.obshaga from delo inner join fac on delo.id=fac.id inner join obsh on delo.id=

obsh.id where (obsh.obshaga =2 or obsh.obshaga =6) and (obsh.corp=4 or obsh.corp =7) and (fac.stip > (select avg(fac.stip) from fac where fac.dep='FEN' or fac.dep='AVTF')) order by delo.id;

Рис.5. Запрос № 1

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

Запрос № 2. Найти всех студентов, обучающихся на 2 и 3 курсах факультетов ФЭН и ФБ, и получающих стипендию больше, чем средняя стипендия студентов из Омска и Томска.

При расчетах вручную получается, что средняя стипендия студентов из Омска и Томска = 1139 денежных единиц, и есть только один студент, попадающий под запрос: 10) Таня Гилева

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

select chel.name, chel.famil, fac.dep, fac.year, fac.stip, chel.city from chel inner join fac on chel.id = fac.id where (fac.year = 2 or fac.year=3) and

 (fac.dep='FEN' or fac.dep= 'FB') and (fac.stip > (select avg(fac.stip) from fac

 inner join chel on fac.id = chel.id where chel.city = 'Omsk' or chel.city='Toms

k'));

Рис.6. Запрос № 2.

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

Найти всех студентов факультетов АВТФ и ФБ, чья стипендия больше чем средняя стипендия студентов 2 и 4 курсов, обучающихся в аудиториях с 301 по 320.

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

select delo.id, delo.name, delo.famil, fac.stip, fac.dep, fac.year, obsh

.audi from delo inner join fac on delo.id=fac.id inner join obsh on delo.id=obsh

.id where (fac.dep='AVTF' or fac.dep='FB') and (fac.stip > (select avg(fac.stip)

 from fac inner join obsh on fac.id=obsh.id where (fac.year = 2 or fac.year=4) a

nd obsh.audi>301 and obsh.audi<320 )) order by delo.id;

Сначала сделаем запрос, выясняющий среднюю стипендию.

Рис.7. Запрос № 3

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

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

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

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

dalli=# insert into discip values(7, '{"maths", "chemistry", "physics"}');

INSERT 0 1

dalli=# insert into discip values(8, '{"maths", "chemistry", "physics", "IT"}');

INSERT 0 1

dalli=# insert into discip values(9, '{"maths", "chemistry", "physics", "IT", "p

ravo", "database"}');

INSERT 0 1

dalli=# insert into discip values(10, '{"maths", "english", "physics", "IT", "da

tabase"}');

INSERT 0 1

dalli=# insert into discip values(11, '{"IT"}');

INSERT 0 1

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

dalli=# select * from discip order by idxx;

//Выборка первого предмета из таблицы discip:

dalli=# select idxx, lect[1] from discip order by idxx;

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

dalli=# select idxx, lect[4] from discip where lect[4] is not null order by idxx;

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

dalli=# select idxx, lect[3:4] from discip order by idxx;

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

dalli=# select array_dims(lect) from discip;

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

dalli=# update discip set lect='{"IT”,"english", “database”, “history”}' where idxx=6;

UPDATE 1

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

dalli=# update discip set lect[2:3]='{"history","english"}' where idxx=9;

UPDATE 1

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

var1v3=> update discip set lect[2]='history' where idxx=5;

UPDATE 1

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

dalli=# create table multi(idm int, mark int[][]);

CREATE TABLE

//Вставка строк в созданную таблицу (оценки по каждому изучаемому предмету за курсы):

dalli=# insert into multi values(3, '{ {3,4,5}, {3,3,4}, {5,5,4}}');

//т.е. оценки по 3м предметам за 3 курса

INSERT 0 1

dalli=# insert into multi values(4, '{ {4}, {4}, {4}, {5}}');

//т.е. оценки по 4м предметам за 1 курс

INSERT 0 1

dalli=# insert into multi values(5, '{ {3,4,5,3}, {4,3,3,4}, {5,5,4,5}}');

//т.е. оценки по 3м предметам за 4 курса

INSERT 0 1

dalli=# select* from multi order by idm;

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

dalli=# select idm, mark[3][2] from multi where mark[3][2] is not null order by

idm;

dalli=# select idm, mark[1:3][3:4] from multi order by idm;

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

dalli=# select array_dims(mark) from multi;

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

dalli=# update multi set mark='{{2,2,2,2},{2,2,2,2},{2,2,2,2}}' where idm=5;

UPDATE 1

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

dalli=# update multi set mark[1][1:2]='{2,2}' where idm=3;

UPDATE 1

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

dalli=# update multi set mark[2][3]=2 where idm=13;

UPDATE 1

Выводы

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