Queries.txt

#a.Show the reservation number and the location ID of all rentals on 5/20/2015

SELECT reserv_nr, pickuplocation_id
FROM rental
WHERE pickup_date = '2015-05-20';
 
 
#b. Show the first and the last name and the mobile phone number of these customers 
#that have rented a car in the category that has label = 'luxury'

SELECT first_name "First Name", last_name "Last Name", phone_nr "phone numbers of customers in category luxury"
FROM customer as c, rental as r, car, category
WHERE  c.customer_id=r.customer_id and r.VIN=car.VIN and car.categ_id=category.categ_id and category.label='luxury';


#c. Show the total amount of rentals per location ID (pick up)

SELECT pickuplocation_id "location ID", round(sum(amount),2) "Total amount of rentals"
FROM rental
GROUP BY pickuplocation_id;


#d. Show the total amount of rentals per car's category ID and month

SELECT car.categ_id "Category ID",  EXTRACT(YEAR FROM r.pickup_date) "Year", EXTRACT(MONTH FROM r.pickup_date) "Month", ROUND(SUM(amount),2) "Total amount of rentals"
FROM  rental AS r, car 
WHERE r.VIN=car.VIN
GROUP BY car.categ_id, year, month
ORDER BY car.categ_id ASC, year ASC, month ASC;


#e. For each rental’s state (pick up) show the top renting category

DROP VIEW IF EXISTS State;

CREAT VIEW State AS
SELECT state, label, count(reserv_nr) AS rentals
FROM location, category, rental, car  
WHERE rental.pickuplocation_id=location.location_id AND rental.VIN=car.VIN AND category.categ_id=car.categ_id
GROUP BY location.state, category.categ_id
ORDER BY location.state, rentals DESC;

SELECT state, label , MAX(rentals) "Total Rentals for top renting category"
FROM State
GROUP BY state;


#f. Show how many rentals there were in May 2015 in ‘NY’, ‘NJ’ and ‘CA’ (in three columns)

DROP VIEW IF EXISTS may2015;

CREATE VIEW may2015 AS
SELECT state, count(reserv_nr) AS rentals
FROM location, rental
WHERE rental.pickuplocation_id=location.location_id AND 
	(location.state="NY" OR location.state="NJ" OR location.state="CA") AND 
	EXTRACT(year FROM rental.pickup_date)='2015' AND 
	EXTRACT(month FROM rental.pickup_date)='5'
GROUP BY location.state; 

SELECT
  MAX(CASE WHEN state = 'CA' THEN rentals END) AS CA,
  MAX(CASE WHEN state = 'NY' THEN rentals END) AS NY,
  MAX(CASE WHEN state = 'NJ' THEN rentals END) AS NJ
FROM may2015;

        
#g. For each month of 2015, count how many rentals had amount greater than this month's average rental amount

SELECT count(reserv_nr) AS rentals, month(pickup_date) AS month, year(pickup_date) AS year
FROM rental AS r1
WHERE year(r1.pickup_date)=2015 AND r1.amount > (SELECT avg(r2.amount)								                              
                                                 FROM rental AS r2
						 WHERE month(r1.pickup_date)=month(r2.pickup_date) and year(r1.pickup_date)=year(r2.pickup_date) 
                                                 GROUP BY month(r2.pickup_date))
GROUP BY month(r1.pickup_date);


#h. For each month of 2015, show the percentage change of the total amount of rentals over the total amount 
#of rentals of the same month of 2014

SELECT year(pickup_date), month(pickup_date) AS month, 
ROUND((sum(amount)- r2.sum2014) * 100 / sum(amount), 2) AS percentage
FROM rental AS r1
INNER JOIN (SELECT month(pickup_date) month, round(sum(AMOUNT),2) AS sum2014
            FROM rental
	    WHERE year(pickup_date)="2014"
            GROUP BY MONTH(pickup_date)
            ORDER BY month(pickup_date) ASC) as r2
ON month(r1.pickup_date)= r2.month
WHERE year(r1.pickup_date)="2015"
GROUP BY MONTH(r1.pickup_date)
ORDER BY month(r1.pickup_date) ASC;


#i. For each month of 2015, show in three columns: the total rentals’ amount of the previous months, the 
#total rentals’ amount of this month and the total rentals’ amount of the following months

DROP VIEW IF EXISTS V2;

CREATE VIEW V2 AS
SELECT MONTH(pickup_date) AS current_month, SUM(amount) AS current_amount
FROM rental
WHERE YEAR(pickup_date) = '2015'
GROUP BY MONTH(pickup_date)
ORDER BY MONTH(pickup_date);

SELECT i.current_month AS month_number, 
     (SELECT SUM(current_amount) AS sum_amount
      FROM V2
      WHERE current_month < i.current_month) AS Previous_Month, i.current_amount AS Current_Month, (SELECT SUM(current_amount) AS sum_amount
                                                                                                    FROM V2
                                                                                                    WHERE current_month > i.current_month) AS Next_Month
FROM V2 i
ORDER BY month_number asc;