ФЕДЕРАЛЬНОЕ АГЕНСТВО ПО ОБРАЗОВАНИЮ РОССИЙСКОЙ ФЕДЕРАЦИИ

Московский государственный институт электроники и математики

(Технический университет)

Изучение языка SQL

Вариант 8

Лабораторная работа по дисциплине

«Базы данных»

Студент:

Сараев Д. А.

Группа: К-71

Преподаватель:

Карпова И. П.

Москва 2011

Л. р. №1. Создание и заполнение отношений БД транспортного предприятия.

1. Отношение Транспортные средства (Transport)

Содержимое поля	Тип	Длина	Дес.	Примечание
Гос. номер	C	9		первичный ключ
Марка	С	20		обязательное поле
Посадочные места	N	3	0	обязательное поле

2. Отношение Водители (Drivers)

Содержимое поля	Тип	Длина	Дес.	Примечание
Идентификатор	N	5	0	первичный ключ
ФИО	С	40		обязательное поле
Класс	N	1	0	1, 2, 3
Транспортное средство	C	9		Внешний ключ (к Транспортные средства) уникальное значение

3. Отношение Маршруты(Routes):

Содержимое поля	Тип	Длина	Дес.	Примечание
Номер маршрута	N	4	0	первичный ключ
Пункт отправления	С	50		обязательное поле
Пункт прибытия	С	50		обязательное поле
Время отправления	T			обязательное поле
Время в пути	N	4	2	обязательное поле
Периодичность рейса	С	10		еж., чт., нч., день недели
Цена билета	N	7	2	больше 0

4. Отношение "Рейсы" (поля "Номер маршрута", "Дата выезда", "Водитель", "Количество проданных билетов").

Содержимое поля	Тип	Длина	Дес.	Примечание
Идентификатор	N	4	0	первичный ключ
Номер маршрута	N	4		Внешний ключ (к Маршруты)
Дата выезда	D			обязательное поле
Водитель	N	5		Внешний ключ (к Водители)
Количество проданных билетов	N	3	0	Больше или равно 0

SQL запросы на создание таблиц.

1. Транспортные средства:

create table transport

( t_number varchar(9) primary key

, t_model varchar(20) not null

, t_seats numeric(3) not null

);

2. Водители:

create table drivers

( d_id numeric(5) primary key

, d_name varchar(40) not null

, d_class numeric(1) not null check (d_class in (1, 2, 3))

, d_transport varchar(9) unique references transport

);

3. Маршруты:

create table routes

( r_number numeric(4) primary key

, r_departure_point varchar(50) not null

, r_arrival_point varchar(50) not null

, r_departure_time time not null

, r_travel_time interval not null

, r_frequency varchar(3) not null

check(r_frequency in ('еж.', 'чт.', 'нч.', 'пн.', 'вт.',

'ср.', 'чт.', 'пт.', 'сб.', 'вс.'))

, r_price numeric(7, 2) not null check (r_price > 0)

);

4. Рейсы:

create table trips

( t_id numeric(6) primary key

, t_route numeric(4) references routes

, t_date date not null

, t_driver numeric(5) references drivers

, t_sold_tickets numeric(3)

);

Заполнение созданных таблиц данными:

insert into transport values ('е176ук77', 'ПАЗ 5297', 47);

insert into transport values ('р231хо197', 'ПАЗ Сити', 25);

insert into transport values ('в128вс197', 'ГолАЗ 6228', 140);

insert into transport values ('о875тс197', 'ЛиАЗ 52937', 100);

insert into transport values ('е637нр177', 'КАвЗ 4239', 89);

insert into transport values ('о762уа197', 'ПАЗ 4238', 39);

insert into transport values ('у379нм197', 'ПАЗ 320412', 60);

insert into transport values ('е265ен177', 'ПАЗ 3237', 56);

insert into transport values ('у726ов199', 'ПАЗ Реал', 29);

insert into transport values ('т834рк77', 'ГолАЗ 6228', 140);

insert into transport values ('у926хн97', 'ЛиАЗ 52937', 100);

insert into transport values ('у634рм199', 'КАвЗ 4239', 89);

insert into transport values ('м735ох197', 'ПАЗ 4238', 39);

insert into transport values ('в963ту197', 'ПАЗ 320412', 60);

insert into drivers values (1, 'Кузнецов Дмитрий Юрьевич', 1, 'е176ук77');

insert into drivers values (2, 'Лобанов Семен Сергеевич', 3, 'р231хо197');

insert into drivers values (3, 'Куликов Илья Романович', 2, 'в128вс197');

insert into drivers values (4, 'Полторак Дмитрий Аркадьевич', 1, 'о875тс197');

insert into drivers values (5, 'Хомяков Роман Витальевич', 1, 'е637нр177');

insert into drivers values (6, 'Устинов Владислав Юрьевич', 3, 'о762уа197');

insert into drivers values (7, 'Носорогов Алексей Александрович', 1, 'у379нм197');

insert into drivers values (8, 'Дронов Федор Игнатович', 1, 'е265ен177');

insert into drivers values (9, 'Самсонов Илья Александрович', 1, 'у726ов199');

insert into drivers values (10, 'Самойлов Дмитрий Иванович', 3, 'т834рк77');

insert into drivers values (11, 'Шашков Леонид Аркадьевич', 2, 'у926хн97');

insert into drivers values (12, 'Куницин Юрий Олегович', 1, 'у634рм199');

insert into drivers values (13, 'Удальцов Сергей Генадьевич', 1, 'м735ох197');

insert into drivers values (14, 'Быков Егор Дмитриевич', 3, 'в963ту197');

insert into routes values(47, 'Москва', 'Наро-фоминск', '8:30', '1:42', 'еж.', 89);

insert into routes values(57, 'Наро-фоминск', 'Москва', '18:20', '1:45', 'еж.', 89);

insert into routes values(12, 'Москва', 'Апрелевка', '9:20', '0:45', 'вт.', 50);

insert into routes values(22, 'Апрелевка', 'Москва', '11:00', '0:45', 'чт.', 50);

insert into routes values(26, 'Москва', 'Бекасово', '18:20', '1:20', 'еж.', 76);

insert into routes values(36, 'Бекасово', 'Москва', '9:30', '1:20', 'вт.', 76);

insert into routes values(100, 'Алабино', 'Москва', '15:30', '1:00', 'пт.', 100);

insert into trips values(1, 47, '2011-08-01', 1, 47);

insert into trips values(2, 57, '2011-08-01', 1, 20);

insert into trips values(3, 47, '2011-08-02', 13, 39);

insert into trips values(4, 57, '2011-08-02', 14, 34);

insert into trips values(5, 47, '2011-08-03', 1, 12);

insert into trips values(6, 57, '2011-08-03', 2, 20);

insert into trips values(7, 47, '2011-08-04', 1, 32);

insert into trips values(8, 57, '2011-08-04', 2, 21);

insert into trips values(9, 47, '2011-08-05', 1, 23);

insert into trips values(10, 57, '2011-08-05', 2, 20);

insert into trips values(11, 47, '2011-08-06', 1, 14);

insert into trips values(12, 57, '2011-08-06', 2, 24);

insert into trips values(13, 47, '2011-08-07', 1, 16);

insert into trips values(14, 57, '2011-08-07', 2, 12);

insert into trips values(15, 12, '2011-08-02', 3, 132);

insert into trips values(16, 22, '2011-08-04', 4, 67);

insert into trips values(17, 26, '2011-08-01', 5, 70);

insert into trips values(18, 26, '2011-08-02', 6, 30);

insert into trips values(19, 26, '2011-08-03', 7, 40);

insert into trips values(20, 26, '2011-08-04', 8, 40);

insert into trips values(21, 26, '2011-08-05', 9, 15);

insert into trips values(22, 26, '2011-08-06', 10, 140);

insert into trips values(23, 26, '2011-08-07', 11, 100);

insert into trips values(24, 36, '2011-08-02', 12, 72);

Л.р. №2. Выборка данных.

1. Проверить, что у одного водителя не более 1-го рейса в день.

select d_name, t_date

from trips, drivers

where t_driver = d_id

group by d_name, t_date

having count(*) > 1;

Результат:

d_name	t_date
Кузнецов Дмитрий Юрьевич	2011-08-01

2. Посчитать общую стоимость проданных билетов на конкретную дату.

select t_date, sum(t_sold_tickets * r_price)

from trips, routes

where (t_route = r_number) and (t_date = '2011-08-02')

group by t_date;

Результат:

t_date	sum
2011-08-02	20849.00

3. Создать упорядоченные списки:

- маршрутов из определенного пункта отправления

select *

from routes

where r_departure_point = 'Москва'

order by r_departure_time;

Результат:

47	Москва	Наро-фоминск	8:30:00	1:42:00	еж.	89.00
12	Москва	Апрелевка	9:20:00	0:45:00	вт.	50.00
26	Москва	Бекасово	18:20:00	1:20:00	еж.	76.00

- рейсов, выполненных определенным водителем

select t_date, t_route, d_name

from trips, drivers

where (d_name = 'Лобанов Семен Сергеевич') and (t_driver = d_id)

order by t_date;

Результат:

t_date	t_route	d_name
2011-08-03	57	Лобанов Семен Сергеевич
2011-08-04	57	Лобанов Семен Сергеевич
2011-08-05	57	Лобанов Семен Сергеевич
2011-08-06	57	Лобанов Семен Сергеевич
2011-08-07	57	Лобанов Семен Сергеевич

- маршрутов, по которым нет рейсов

Первый способ

select *

from routes

where (r_number not in (select t_route from trips))

order by r_number;

Результат:

100

Алабино

Москва

15:30:00

1:00:00

пт.

100.00

Второй способ

select *

from routes

where (select count(*) from trips where (t_route = r_number)) = 0

order by r_number;

Результат:

100

Алабино

Москва

15:30:00

1:00:00

пт.

100.00

Л.р. №3. Работа с представлениями.