create row type address_t (
	street			varchar(30),
	city			varchar(20),
	state			char(2),
	zipcode			char(5)
);

create row type customer_t (
	ssn			char(9) not null,
	lastname		varchar(20) not null,
	firstname		varchar(20) not null,
	phone			list(char(10) not null),
	address			address_t
);

create row type agent_t under customer_t;

create row type hotel_t (
	name			varchar(20) not null,
	location		varchar(20) not null,
	price			money(6, 2)
);

create row type motel_t (
	plan			set(integer not null)
) under hotel_t;

create row type resort_t (
	rating			integer not null
) under hotel_t;

create row type b_and_b_t (
	lunch_option		boolean not null
) under hotel_t;

-- ######################## Creation of tables ##########################

create table customer of type customer_t (
	primary key (ssn)
);
create table agent of type agent_t (
	primary key (ssn)
) under customer;

create table hotel of type hotel_t (
	primary key (name, location)
);
create table motel of type motel_t under hotel;
create table resort of type resort_t under hotel;
create table b_and_b of type b_and_b_t under hotel;

create table rental (
	name			varchar(20) not null,
	branch_id		char(6) not null,	
	location		address_t,
	primary key (name, branch_id)
);

create table cars (
	license			varchar(15) not null,
	type			varchar(15),
	price			money(6, 2),
	mileage			integer,
	rental_name		varchar(20) not null,
	branch_id		char(6) not null,
	primary key (license),
	foreign key (rental_name, branch_id) references rental (name, branch_id)
);

create table log (
	log_id			char(6) not null,
	agent_ssn		char(9) not null,
	type			varchar(10),
	status			varchar(20),
	date			date,
	primary key (log_id, agent_ssn)
);

create table resv (
	customer_ssn		char(9) not null,
	agent_ssn		char(9) not null,
	hotel_name		varchar(20) not null,
	hotel_loc		varchar(20) not null,
	date			date,
	num_of_days		integer,
	log_id			char(6) not null,
	primary key (customer_ssn, agent_ssn, log_id),
	foreign key (hotel_name, hotel_loc) references hotel (name, location),
	foreign key (log_id, agent_ssn) references log (log_id, agent_ssn),
	foreign key (customer_ssn) references customer (ssn),
	foreign key (agent_ssn) references agent (ssn)
);

create table rent (
	customer_ssn		char(9) not null,
	agent_ssn		char(9) not null,
	license			varchar(15) not null,
	num_of_days		integer,
	date_time		datetime year to minute,
	log_id			char(6) not null,

	primary key (customer_ssn, agent_ssn, log_id),
	foreign key (license) references cars (license),
	foreign key (log_id, agent_ssn) references log (log_id, agent_ssn),
	foreign key (customer_ssn) references customer (ssn),
	foreign key (agent_ssn) references agent (ssn)
);

Q1. Find the maximum, minimum and average price of all motels, resorts and bed & breakfast hotels?
1.select min(price) as hotel_min, max(price) as hotel_max, avg(price) as hotel_avg
from hotel;

Q2. Find all vehicle license numbers rented by all customers who reserved a given motel (Choose a motel name as per your data).
2.select rent.customer_ssn, rent.license 
from rent
where rent.customer_ssn in 
    ( select distinct resv.customer_ssn
      from resv
      where resv.hotel_name = 'King Albert Motel' 
                              and resv.hotel_loc = 'Las Vegas'
    )
order by customer_ssn, license;

Q.3 Find number of customers per car rental branches in a given State (Choose a state name as per your data).
3.select rental.name, rental.branch_id, count(distinct rent.customer_ssn) as cust_count
from rental, cars, rent
where rental.location.state='CA'
                            and rental.name=cars.rental_name
                            and rental.branch_id=cars.branch_id
                            and rent.license=cars.license
group by rental.name, rental.branch_id
order by name, branch_id;

Q.4 Find the names of all agents who have done reservations on all types of hotels.
4.select ssn as agent_ssn, lastname, firstname
from agent
where ssn in
    ( select distinct agent_ssn
      from resv join motel
      on resv.hotel_name = motel.name 
                           and resv.hotel_loc = motel.location
      where agent_ssn in
          ( select distinct agent_ssn
            from resv join resort
            on resv.hotel_name = resort.name
                                 and resv.hotel_loc = resort.location
            where agent_ssn in
                ( select distinct agent_ssn
                  from resv join b_and_b
                  on resv.hotel_name = b_and_b.name 
                                       and resv.hotel_loc = b_and_b.location
                )
          )
    )
order by agent_ssn;

Q.5 Find all customers who have reserved cars from the same agent more than twice.
5.select customer_ssn, agent_ssn, count(*) as num_rented
from rent
group by customer_ssn, agent_ssn
having count(*) > 2
order by customer_ssn, agent_ssn;


Q.6 Given a customer (SSN) find number of cities that the customer has traveled in a given time period (Choose the time period as per your data).
6.select count(*) as city_count
from resv
where resv.customer_ssn = '897634521' 
                     and (resv.date+resv.num_of_days-1) >= date ('03/16/2001') 
                     and resv.date <= date ('03/28/2001'); 

Q.7 Find all agents that have made more than 10 car rentals and more than 5 hotel reservations in a given month.
7.select agent_ssn
from rent
where month(rent.date_time) = 3 
                         and agent_ssn in
                             ( select agent_ssn
                               from resv
                               where month(resv.date) = 3 
                               group by agent_ssn
                               having count(*) > 5
                             )
group by agent_ssn
having count(*) > 10
order by agent_ssn;