1520094343_sql_project_Z.Wen_updated.sql

/* Welcome to the SQL mini project. For this project, you will use
Springboard' online SQL platform, which you can log into through the
following link:

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.

Note that, if you need to, you can also download these tables locally.

In the mini project, 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. */



/* Q1: Some of the facilities charge a fee to members, but some do not.
Please list the names of the facilities that do. */

SELECT * 
FROM  `Facilities` 
WHERE membercost >0

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

SELECT COUNT(*) FROM `Facilities` WHERE membercost=0

/* Q3: How can you produce 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,
        membercost,
        monthlymaintenance
  FROM Facilities
HAVING membercost < monthlymaintenance * 0.2

/* Q4: How can you retrieve the details of facilities with ID 1 and 5?
Write the query without using the OR operator. */

SELECT * 
FROM  `Facilities` 
WHERE facid =1
OR facid =5

/* Q5: How can you 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'
            WHEN monthlymaintenance <= 100 THEN 'Cheap'
            END AS cost_group
FROM `Facilities` 

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

select firstname,
       surname,
       joindate
FROM `Members`
WHERE joindate=(SELECT MAX(joindate) FROM  `Members`)


## Other option:

SELECT firstname,
       surname,
       memid
FROM  `Members` 
WHERE  memid = (SELECT MAX(memid) FROM  `Members`)

	

/* Q7: How can you 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 
             book.memid,
             book.facid,
             CASE WHEN book.facid= 0 THEN 'Tennis Court1'
                  WHEN book.facid = 1 THEN 'Tennis Court2'
                  END As Court_name,
             CASE WHEN  mem.surname = 'GUEST' THEN  'GUEST'
		  ELSE  CONCAT(mem.firstname, " ", mem.surname) 
                  END AS Member_name
FROM `Bookings` book
JOIN `Members` mem
ON book.memid = mem.memid
WHERE book.facid=0 OR book.facid=1
ORDER BY mem.surname, mem.firstname


/* Q8: How can you 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 fac.name as Facility_name, 
	CASE WHEN  mem.surname = 'GUEST' THEN 'Guest'
             ELSE  CONCAT( mem.firstname," ", mem.surname)
	     END AS Member_name,
	CASE WHEN  mem.surname = 'GUEST' THEN  fac.guestcost * book.slots
	     ELSE  fac.membercost * book.slots
	     END AS Cost
FROM `Bookings` book
INNER JOIN `Facilities` fac
ON book.facid = fac.facid
INNER JOIN `Members` mem
ON book.memid = mem.memid
WHERE book.starttime BETWEEN '2012-09-14' AND '2012-09-15'
HAVING Cost > 30
ORDER BY 3 DESC

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

SELECT sub.*
From (SELECT fac.name as Facility_name, 
	CASE WHEN  mem.surname = 'GUEST' THEN 'Guest'
             ELSE  CONCAT( mem.firstname," ", mem.surname)
	     END AS Member_name,
	CASE WHEN  mem.surname = 'GUEST' THEN  fac.guestcost * book.slots
	     ELSE  fac.membercost * book.slots
	     END AS Cost
      FROM `Bookings` book
      INNER JOIN `Facilities` fac
      ON book.facid = fac.facid
      INNER JOIN `Members` mem
      ON book.memid = mem.memid
      WHERE book.starttime BETWEEN '2012-09-14' AND '2012-09-15') sub
WHERE sub.Cost>30
ORDER BY 3 DESC

/* 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 fac.name as Facility_name, 
	SUM(CASE WHEN  book.memid = 0 THEN  fac.guestcost * book.slots
	     ELSE  fac.membercost * book.slots END) AS Total_Revenue
FROM `Bookings` book
INNER JOIN `Facilities` fac
ON book.facid = fac.facid
GROUP BY Facility_name
Having Total_Revenue < 1000
ORDER BY 1