4UnafFB6

1-- 30.12.18  Вакуленко Владислава БПИ163(1)
2-- Task Set C
3
4DROP TABLE IF EXISTS cars;
5DROP TABLE IF EXISTS customers;
6DROP TABLE IF EXISTS pairs_reserve;
7DROP TABLE IF EXISTS trip;
8
9create table IF NOT EXISTS cars(
10  id_car int primary key,
11  id_customer int,
12  car_model varchar(255),
13  seat_count int,
14  petrol int,
15  city int,
16  baggage_places_count int,
17  under_service boolean
18);
19
20create table IF NOT EXISTS customers(
21  id_customer int primary key,
22  name varchar(255)
23);
24
25create table IF NOT EXISTS pairs_reserve(
26  id_pair int primary key,
27  id_customer int,
28  id_car int,
29  start_data Date,
30  end_data Date,
31  finish_reservation int,
32  foreign key (id_customer) references customers(id_customer),
33  foreign key (id_car) references cars(id_car)
34);
35
36create table IF NOT EXISTS trip(
37  id_trip int primary key,
38  duration int,
39  id_car int,
40  foreign key (id_car) references cars(id_car),
41  city_start varchar(255),
42  city_end varchar(255)
43);
44
45create table service(
46  id_service int primary key ,
47  id_car int,
48  start_time DATE,
49  end_time DATE,
50  foreign key (id_car) references cars(id_car)
51);
52
53/* Task 3 */
54SELECT COUNT(*)
55FROM pairs_reserve
56join trip on pairs_reserve.id = trip.id_trip
57where finish_reservation = 1
58  and start_data is null and pairs_reserve.id_car in
59                                (select id_car from cars where car_model = 'Bentley' and trip.city_start = 'Moscow');
60
61/* Task 4 */
62SELECT ROUND(avg(duration), -1), city_start
63FROM trip
64group by city_start;
65
66/* Task 5 */
67WITH tmp as (
68    SELECT count(id), id_customer
69    from pairs_reserve
70    WHERE start_data < month(now() - 1) and start_data > month(now() - 2)
71    group by id_customer
72    )
73select name
74from tmp
75join customers on tmp.id_customer = customers.id_customer;
76
77/* Task 6 */
78WITH tmp as (SELECT sum(end_data - start_data) as time, id_car
79             FROM pairs_reserve
80             WHERE start_data > sysdate - 3
81             group by id_car
82             HAVING id_car not in (SELECT id_car from service where start_time > sysdate - 3)
83)
84SELECT time, car_model
85FROM tmp join cars on tmp.id_car = cars.car_model
86ORDER BY time desc
87LIMIT 3;