sql_text_part_1

/* Welcome to the SQL mini project. You will carry out this project partly in
the PHPMyAdmin interface, and partly in Jupyter via a Python connection.

This is Tier 2 of the case study, which means that there'll be less guidance for you about how to setup
your local SQLite connection in PART 2 of the case study. This will make the case study more challenging for you: 
you might need to do some digging, aand revise the Working with Relational Databases in Python chapter in the previous resource.

Otherwise, the questions in the case study are exactly the same as with Tier 1. 

PART 1: PHPMyAdmin
You will complete questions 1-9 below in the PHPMyAdmin interface. 
Log in by pasting the following URL into your browser, and
using the following Username and Password:

URL: https://sql.springboard.com/
Username: student
Password: learn_sql@springboard

The data you need is in the "country_club" database. This database
contains 3 tables:
    i) the "Bookings" table,
    ii) the "Facilities" table, and
    iii) the "Members" table.

In this case study, you'll be asked a series of questions. You can
solve them using the platform, but for the final deliverable,
paste the code for each solution into this script, and upload it
to your GitHub.

Before starting with the questions, feel free to take your time,
exploring the data, and getting acquainted with the 3 tables. */


/* QUESTIONS 
/* Q1: Some of the facilities charge a fee to members, but some do not.
Write a SQL query to produce a list of the names of the facilities that do. */

select name from Facilities where membercost > 0.0;


/* Q2: How many facilities do not charge a fee to members? */

select count(name) from Facilities where membercost = 0.0;


/* Q3: Write an SQL query to show a list of facilities that charge a fee to members,
where the fee is less than 20% of the facility's monthly maintenance cost.
Return the facid, facility name, member cost, and monthly maintenance of the
facilities in question. */

select facid,name,membercost,monthlymaintenance 
from Facilities
where membercost < .2*monthlymaintenance 
and membercost > 0.0


/* Q4: Write an SQL query to retrieve the details of facilities with ID 1 and 5.
Try writing the query without using the OR operator. */

select * from Facilities where facid in (1,5)


/* Q5: Produce a list of facilities, with each labelled as
'cheap' or 'expensive', depending on if their monthly maintenance cost is
more than $100. Return the name and monthly maintenance of the facilities
in question. */

select name, monthlymaintenance, case when monthlymaintenance > 100 then 'expensive'
else 'cheap' end as label from Facilities

/* Q6: You'd like to get the first and last name of the last member(s)
who signed up. Try not to use the LIMIT clause for your solution. */

WITH temp AS 
(select max(joindate) as max_date from Members)
select firstname,surname, joindate
from Members as m
inner join temp as t 
on m.joindate=t.max_date

below query with rank function should work for later versions of mysql (does not work for mysql version 5.5 via phpmyadmin).

WITH temp AS 
(select *,rank() over(order by joindate desc) as startrank
from members)
select firstname,surname, joindate
from temp
where startrank=1



/* Q7: Produce a list of all members who have used a tennis court.
Include in your output the name of the court, and the name of the member
formatted as a single column. Ensure no duplicate data, and order by
the member name. */

SELECT distinct f.name,concat_ws(' ',m.firstname,m.surname) as member_name
from Members as m 
INNER JOIN Bookings as b 
ON m.memid = b.memid
INNER JOIN Facilities as f
ON b.facid=f.facid
WHERE f.facid in (0,1)
ORDER BY member_name



/* Q8: Produce a list of bookings on the day of 2012-09-14 which
will cost the member (or guest) more than $30. Remember that guests have
different costs to members (the listed costs are per half-hour 'slot'), and
the guest user's ID is always 0. Include in your output the name of the
facility, the name of the member formatted as a single column, and the cost.
Order by descending cost, and do not use any subqueries. */

SELECT b.bookid,b.slots, b.starttime,f.name,m.memid,concat_ws(' ',m.firstname, m.surname) as member_name, 
CASE WHEN b.memid=0 THEN f.guestcost
ELSE f.membercost END AS cost_per_slot,
CASE WHEN b.memid=0 THEN f.guestcost*b.slots 
ELSE f.membercost*b.slots END AS total_cost_all_slots
from Members as m 
INNER JOIN Bookings as b 
ON m.memid = b.memid
INNER JOIN Facilities as f
ON b.facid=f.facid
WHERE DATE(b.starttime) = '2012-09-14'
AND ((b.memid=0 AND f.guestcost*b.slots > 30) OR (b.memid!=0 AND f.membercost*b.slots>30))
ORDER BY total_cost_all_slots desc

/* Q9: This time, produce the same result as in Q8, but using a subquery. */

SELECT b.bookid,b.slots, b.starttime,f.name,m.memid,concat_ws(' ',m.firstname, m.surname) as member_name, 
CASE WHEN b.memid=0 THEN f.guestcost
ELSE f.membercost END AS cost_per_slot,
CASE WHEN b.memid=0 THEN f.guestcost*b.slots 
ELSE f.membercost*b.slots END AS total_cost_all_slots
from Members as m 
INNER JOIN (SELECT * from Bookings where DATE(starttime)='2012-09-14') as b
ON m.memid = b.memid
INNER JOIN Facilities as f
ON b.facid=f.facid
AND ((b.memid=0 AND f.guestcost*b.slots > 30) OR (b.memid!=0 AND f.membercost*b.slots>30))
ORDER BY total_cost_all_slots desc

/* PART 2: SQLite

Export the country club data from PHPMyAdmin, and connect to a local SQLite instance from Jupyter notebook 
for the following questions.  

QUESTIONS:
/* Q10: Produce a list of facilities with a total revenue less than 1000.
The output of facility name and total revenue, sorted by revenue. Remember
that there's a different cost for guests and members! */

SELECT f.name, sum(CASE WHEN b.memid=0 THEN f.guestcost
        ELSE f.membercost END) AS cost_per_slot, sum(CASE WHEN b.memid=0 THEN f.guestcost*b.slots 
        ELSE f.membercost*b.slots END) AS total_cost_all_slots
        from Members as m 
        INNER JOIN Bookings as b 
        ON m.memid = b.memid
        INNER JOIN Facilities as f
        ON b.facid=f.facid
        GROUP BY f.name
        HAVING total_cost_all_slots < 1000
        ORDER BY total_cost_all_slots

/* Q11: Produce a report of members and who recommended them in alphabetic surname,firstname order */

SELECT m.surname as member_surname,m.firstname as member_firstname, r.surname as recommender_surname,r.firstname as recommender_firstname
FROM Members as m
INNER JOIN Members as r
ON r.memid=m.recommendedby
ORDER BY m.surname,m.firstname

/* Q12: Find the facilities with their usage by member, but not guests */

SELECT f.name, m.surname,m.firstname,count(b.bookid) as bookings_count,sum(b.slots) as slots_usage
        from Members as m 
        INNER JOIN Bookings as b 
        ON m.memid = b.memid
        INNER JOIN Facilities as f
        ON b.facid=f.facid
        WHERE b.memid!=0                                 
        GROUP BY f.name, m.surname,m.firstname
        ORDER BY slots_usage desc

/* Q13: Find the facilities usage by month, but not guests */

sqlite version
SELECT STRFTIME('%m-%Y', b.starttime) as booking_month_year,f.name,count(b.bookid) as bookings_count,sum(b.slots) as slots_usage
        from Members as m 
        INNER JOIN Bookings as b 
        ON m.memid = b.memid
        INNER JOIN Facilities as f
        ON b.facid=f.facid
        WHERE b.memid!=0                                 
        GROUP BY STRFTIME('%m-%Y', b.starttime),f.name
        ORDER BY booking_month_year

phpmyadmin mysql version
SELECT monthname(b.starttime) AS booking_month,year(b.starttime) as booking_year,f.name,count(b.bookid) as bookings_count,sum(b.slots) as slots_usage
        from Members as m 
        INNER JOIN Bookings as b 
        ON m.memid = b.memid
        INNER JOIN Facilities as f
        ON b.facid=f.facid
        WHERE b.memid!=0                                 
        GROUP BY monthname(b.starttime),year(b.starttime),f.name
        ORDER BY booking_month,booking_year