Методические указания к лабораторным работам по дисциплине "организация баз данных" для студентов специальности Утверждено (стр. 5 из 6)

Логин char(10) primary key, /*Массив символов длины 10, первичный ключ*/

Пароль char(10), /*Массив символов длины 10*/

e_mail char(50) check(e_mail like '%@%'), /*Массив символов длины 50, проверка на присутствие символа @ в строке*/

Остаток decimal(10,2), /*Фиксированное число с плавающей точкой, 2 знака после запятой*/

ДатаРегистрации date); /*Дата*/

Создание таблицы Закачки:

create sequence закачки; /*Создание последовательности закачки*/

create table Закачки(

Id_закачки int primary key default nextval('закачки'), /*Целое число, первичный ключ, по умолчанию значение поля равно следующему элементу последовательности закачки*/

Пользователь char(10) references Пользователи(Логин), /*Строка символов длины 10, ограничение, определяющее, что значение этого поля может быть только такое, которое есть в таблице Пользователи в поле Логин*/

Файл int, /* Целое число, здесь должно было быть references Файлы(Id_файла), но пока оставим эту запись до выхода следующих версий PostgreSQL*/

ВремяНачала timestamp, ), /*Дата и время*/

ВремяКонца timestamp); /*Дата и время*/

Заполнение таблиц:

INSERT INTO "Фильмы" ("Наименование", "Размер", "Цена", "Формат", "Название", "Жанр", "Режиссер", "ГодВыпуска")

VALUES('Movies\ПятыйЭлемент', 700, 20.00, 'avi', 'Пятый элемент', 'Фантастика, Приключения', 'Люк Бессон', '1997');

INSERT INTO "Фильмы" ("Наименование", "Размер", "Цена", "Формат", "Название", "Жанр", "Режиссер", "ГодВыпуска")

VALUES('Movies\ДжейнОстин', 693, 15.00, 'avi', 'Джейн Остин', 'Мелодрама, Драма', 'Джулиан Джаррольд', '2007');

INSERT INTO "Фильмы" ("Наименование", "Размер", "Цена", "Формат", "Название", "Жанр", "Режиссер", "ГодВыпуска")

VALUES('Movies\ПротивостояниеГигантам', 680, 18.00, 'avi', 'Противостояние гигантам', 'Драма', 'Алекс Кендрик', '2006');

INSERT INTO "Фильмы" ("Наименование", "Размер", "Цена", "Формат", "Название", "Жанр", "Режиссер", "ГодВыпуска")

VALUES('Movies\Matrix', 693, 15.00, 'avi', 'Матрица', 'Фентези, Фантастика', 'Энди Вачовски и Ларри Вачовски', '2004');

INSERT INTO "Музыка" ("Наименование", "Размер", "Цена", "Формат", "НаименованиеТрека", "Альбом", "Исполнитель", "ГодВыпуска", "Направление")

VALUES('Music\treck2', 6, 1.50, 'mp3', 'How High', 'Confessions On A Dance Floor', 'Madonna', '2005', 'Pop');

VALUES('Music\treck6', 4, 1.50, 'mp3', 'Hung Up', 'Confessions On A Dance Floor', 'Madonna', '2005', 'Pop');

VALUES('Music\treck8', 5, 1.50, 'mp3', 'Push', 'Confessions On A Dance Floor', 'Madonna', '2005', 'Pop');

VALUES('Music\IWBL', 3, 1.80, 'mp3', 'I wanna be loved', 'Have a nice day', 'Bon Jovi', '2005', 'Rock');

VALUES('Music\Билан_трек2', 4, 1.50, 'mp3', 'Ты должна быть рядом', 'На берегу неба', 'Дима Билан', '2004', 'Pop');

INSERT INTO "Книги" ("Наименование", "Размер", "Цена", "Формат", "Название", "Автор", "Издание", "ГодВыпуска", "КоличествоСтр", "Жанр")

VALUES('Books\ТиПрПБД', 7, 25.00, 'djvu', 'Теория и практика построения баз данных', 'Д.Кренке', 'Питер', 400 , '2003', 'Информатика');

VALUES('Books\ПроектирБД', 6, 15.00, 'djvu', 'Проектирование реляционных баз данных', 'Джен Л.Харрингтон', 'Лори', 241 , '2006', 'Информатика');

VALUES('Books\ПостгресДляПрофессионалов', 8, 21.00, 'pdf', 'PostgreSQL. Для профессионалов', 'Дж.Уорслей, Дж.Дрейк', 'Питер', 498 , '2003', 'Информатика');

INSERT INTO "Пользователи" ("Логин", "Пароль", "e_mail", "Остаток", "ДатаРегистрации")

VALUES('Petya', '12345', 'petya@mail.ru', 200.00, '2007-10-22');

INSERT INTO "Пользователи" ("Логин", "Пароль", "e_mail", "Остаток", "ДатаРегистрации")

VALUES('Vasya', '54321', 'Vasya@mail.ru', 100.00, '2007-10-24');

INSERT INTO "Пользователи" ("Логин", "Пароль", "e_mail", "Остаток", "ДатаРегистрации")

VALUES('Slava', '12345', 'slava@mail.ru', 120.00, '2007-10-30');

INSERT INTO "Закачки" ("Пользователь", "Файл", "ВремяНачала", "ВремяКонца")

VALUES('Petya', 2, '2007-12-03 11:27:23.809', '2007-12-03 11:35:20.000');

INSERT INTO "Закачки" ("Пользователь", "Файл", "ВремяНачала", "ВремяКонца")

VALUES('Slava', 8, '2007-12-03 11:27:23.809', '2007-12-03 11:50:13.609');

Лабораторная работа №3 (ЗАПРОСЫ)

Основные понятия

Примеры

Напишите SQL запросы для манипулирования данными в таблицах, созданных в лабораторной работе №2.

Пример 1

У одного из контрагентов изменился номер телефона и адрес, составить SQL запрос для соответствующих изменений в таблицах.

update Контрагенты set Адрес='ул. Греческая 30, кв 4', Телефон='221234' where Наименование = 'ЧП "Алиса"'

Создайте представление, содержащее информацию о контрагентах, являющихся юридическими лицами.

create view ЮрЛица as

select * from Контрагенты where ВидКонтрагента='Юридическое лицо'

Сколько наименований товаров содержит справочник Товары, у которых единица измерения - штуки.

select count(Наименование) from Товары where ЕдИзмерения='шт'

Какая выручка была получена от продаж в августе 2007 г.

select sum(Цена*Количество) from Продажи where Дата>='2007-08-01' and Дата<='2007-08-31'

Какая выручка была получена в августе 2007 года, за каждый день в отдельности.

select Дата, sum(Цена*Количество) from Продажи where Дата>='2007-08-01' and Дата<='2007-08-31'

group by Дата

select Дата, sum(Цена*Количество) from Продажи

group by Дата having Дата>='2007-08-01' and Дата<='2007-08-31'

Показать продаваемые товары.

select distinct Т.Наименование from Товары Т, Продажи П where П.Товар=Т.Id_товара

Выберете из базы данных информацию о поставках, произошедших 22 августа 2007 г. Создайте представление, в котором необходимо отобразить номер накладной, склад на который пришел товар, наименование товара, цена, количество, сумма.

create view ПриходТовара as

select П.№Накладной, МХ.Наименование as Склад, Т.Наименование as Товар, П.Цена, П.Количество, П.Цена*П.Количество as Сумма from МестаХранения МХ, Товары Т, Поставки П

where П.Склад=МХ.Id_склада and П.Товар=Т.Id_товара and П.Дата='2007-08-22'

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

select distinct К.Наименование from Контрагенты К, Поставки Пост, Продажи Пр

where К.Id_контрагента=Пост.Контрагент and К.Id_контрагента=Пр.Контрагент and Пост.Контрагент=Пр.Контрагент

Пример 2

Пользователь Slava положил на свой счет 50 грн.

update Пользователи set Остаток=Остаток+50 where Логин = 'Slava'

Найти наименования файлов, которые скачал пользователь с логином Petya

select Пользователь, Наименование from Закачки, Файлы where Закачки.Файл=Файлы.id_файла and Пользователь='Petya'

Сколько суммарно места занимают в базе файлы каждого формата, отсортировать в порядке возрастания занимаемого объема.

select Формат, sum(Размер) from Файлы group by Формат order by sum(Размер)

Создать представление Отчет для отображения логина пользователя, его электронного почтового адреса, имени файла, который он закачивал и стоимости закачки этого файла.

create view Отчет as

select П.Логин, П.e_mail, Ф.Наименование, Ф.Цена from Пользователи П, Закачки З, Файлы Ф where П.Логин=З.Пользователь and З.Файл=Ф.id_файла

Найти общее время всех закачек

select sum(ВремяКонца-ВремяНачала) from Закачки

Найти общее время закачек каждого пользователя

select Пользователь, sum(ВремяКонца-ВремяНачала) from Закачки group by Пользователь

Вывести пользователей остаток на счету у которых 0 и которые зарегестрировались до начала 2007 года

select Логин from Пользователи where ДатаРегистрации<'2007-01-01' and Остаток=0

Максимальный файл по размеру, который закачал каждый пользователь

select З.Пользователь, max(Ф.Размер) from Файлы Ф, Закачки З where Ф.id_файла=З.Файл group by З.Пользователь

Лабораторная работа №4 (ПОДЗАПРОСЫ)

Основные понятия

Примеры

Напишите SQL запросы с использованием подзапросов для манипулирования данными в таблицах, созданных в лабораторной работе №2.