- •Оглавление
- •1) Постановка задачи:
- •Vipolneno
- •2) Описание модели данных и таблиц исходных и справочных данных:
- •Vipolneno
- •3) Скрипт бд
- •Vipolneno:
- •4) Диаграмма, демонстрирующая связи между таблицами:
- •5) Таблицы базы данных
- •Vipolneno
- •6) Запросы бд
- •7) Триггеры
- •If exists (select * from otmena o
Vipolneno
N_reis |
Data_otp |
Kolvo_prod_mest |
Summa |
Time_otp |
601 |
20.12.2012 |
9 |
5584 |
18:10:00 |
613 |
25.12.2012 |
0 |
0 |
12:05:00 |
625 |
25.12.2012 |
0 |
0 |
18:40:00 |
637 |
05.01.2013 |
1 |
485 |
12:30:00 |
649 |
16.01.2013 |
1 |
450 |
12:20:00 |
661 |
02.02.2013 |
3 |
2550 |
21:10:00 |
673 |
07.02.2013 |
8 |
3600 |
13:00:00 |
685 |
12.12.2012 |
11 |
5335 |
15:30:00 |
697 |
02.01.2013 |
1 |
666 |
13:00:00 |
709 |
06.01.2013 |
3 |
1350 |
13:30:00 |
721 |
05.01.2013 |
1 |
325 |
18:40:00 |
733 |
27.12.2012 |
1 |
485 |
13:50:00 |
745 |
16.12.2012 |
4 |
1800 |
13:50:00 |
757 |
23.12.2012 |
2 |
1332 |
13:00:00 |
6) Запросы бд
1) Вывести самые популярные рейсы
select top 3 with ties v.N_reis, marshr, kolvo_prod_mest as 'Кол-во пассажиров'
from vipolneno v join adress_shem a on a.n_reis=v.n_reis
order by 3 desc
Номер рейса |
Маршрут |
Количество проданных мест |
685 |
Новосибирск-Кемерово |
11 |
601 |
Новосибирск-Бийск |
9 |
673 |
Новосибирск-Барнаул |
8 |
2) Вывести самые не популярные рейсы
select top 3 with ties v.N_reis,marshr, kolvo_prod_mest from vipolneno v join adress_shem a on a.n_reis=v.n_reis order by 3
Номер рейса |
Маршрут |
Количество проданных мест |
613 |
Новосибирск-Бердск |
0 |
625 |
Новосибирск-Горный |
0 |
637 |
Новосибирск-Кемерово |
1 |
649 |
Новосибирск-Барнаул |
1 |
697 |
Новосибирск-Бийск |
1 |
721 |
Новосибирск-Юрга |
1 |
733 |
Новосибирск-Кемерово |
1 |
757 |
Новосибирск-Бийск |
1 |
613 |
Новосибирск-Бердск |
0 |
625 |
Новосибирск-Горный |
0 |
637 |
Новосибирск-Кемерово |
1 |
3) Вывести общую сумму за каждый месяц
select case DATEPART(M,data_otp)
when 1 then 'Январь'
when 2 then 'Февраль'
when 3 then 'Март'
when 4 then 'Апрель'
when 5 then 'Май'
when 6 then 'Июнь'
when 7 then 'Июль'
when 8 then 'Август'
when 9 then 'Сентябрь'
when 10 then 'Октябрь'
when 11 then 'Ноябрь'
when 12 then 'Декабрь'
end AS месяц,
sum(summa) as 'сумма'
from vipolneno
group by DATEPART(M,data_otp)
Месяц |
Сумма за месяц |
Январь |
3276 |
Февраль |
6150 |
Декабрь |
14536 |
4) Вывести список автобусов, их количество и вместимость.
select tip,kolvo_mest, count(tip) as kol from tip_avto t join reis r on r.N_reis=t.N_reis group by tip,kolvo_mest
Марка автобуса |
Количество мест |
Количество в автопарке |
Nissan |
15 |
1 |
Ikarus |
23 |
1 |
ПАЗ |
23 |
5 |
Ikarus |
25 |
2 |
ПАЗ |
25 |
2 |
Daewoo |
30 |
1 |
Ikarus |
30 |
1 |
BMW |
32 |
1 |
Daewoo |
38 |
1 |
Mersedes |
40 |
1 |
5) Вывести расписание рейсов
SELECT r.N_reis AS [Номер рейса], a.Marshr AS Маршрут, a.Dni_otp AS [Дни отправления], r.Time_otp AS Время
FROM dbo.Reis r INNER JOIN
dbo.Adress_shem a ON a.N_reis = r.N_reis
Номер рейса |
Маршрут |
Дни отправления |
Время |
601 |
Новосибирск-Бийск |
вт-пт |
18:10:00 |
613 |
Новосибирск-Бердск |
вт-ср |
12:05:00 |
625 |
Новосибирск-Горный |
пн-ср |
18:40:00 |
637 |
Новосибирск-Кемерово |
сб |
12:30:00 |
649 |
Новосибирск-Барнаул |
пн-ср |
12:20:00 |
649 |
Новосибирск-Барнаул |
пн-ср |
14:20:00 |
661 |
Новосибирск-Омск |
сб |
21:10:00 |
673 |
Новосибирск-Барнаул |
чт |
13:00:00 |
685 |
Новосибирск-Кемерово |
пн-ср |
15:30:00 |
697 |
Новосибирск-Бийск |
ср |
13:00:00 |
709 |
Новосибирск-Барнаул |
вс |
13:30:00 |
721 |
Новосибирск-Юрга |
пн-вс |
18:40:00 |
733 |
Новосибирск-Кемерово |
чт |
13:50:00 |
745 |
Новосибирск-Барнаул |
вс |
13:50:00 |
757 |
Новосибирск-Бийск |
вс |
13:00:00 |
757 |
Новосибирск-Бийск |
вс |
16:00:00 |