Exercises from http://www.pgexercises.com/ for W3D1 of lighthouselabs.com web development bootcamp

answers.sql

-- Basic
-- Retrieve everything from a table
SELECT * FROM cd.facilities;

-- Retrieve specific columns from a table
SELECT name, membercost FROM cd.facilities;

-- Control which rows are retrieved
SELECT * FROM cd.facilities WHERE membercost > 0;

-- Control which rows are retrieved - part 2
SELECT facid, name, membercost, monthlymaintenance FROM cd.facilities 
  WHERE membercost > 0 AND membercost < monthlymaintenance/50;

-- 
-- Basic string searches
SELECT b.starttime FROM cd.bookings AS b
  JOIN (SELECT memid FROM cd.members WHERE firstname = 'David' AND surname= 'Farrell') AS ids
  ON ids.memid = b.memid;
  
  
SELECT * FROM cd.facilities WHERE name LIKE '%Tennis%';

-- Matching against multiple possible values
SELECT * FROM cd.facilities WHERE facid = ANY('{1,5}'::int[]);

-- Classify results into buckets
SELECT name,
 CASE WHEN monthlymaintenance > 100 THEN 'expensive'
 ELSE 'cheap'
 END AS cost
FROM cd.facilities;

-- Working with dates
SELECT memid, surname, firstname, joindate FROM cd.members WHERE joindate >= '2012-09-01';

-- Removing duplicates, and ordering results
SELECT DISTINCT surname FROM cd.members ORDER BY surname ASC LIMIT 10;

-- Combining results from multiple queries
SELECT surname FROM cd.members UNION select name FROM cd.facilities;

-- Simple aggregation
SELECT max(joindate) AS latest FROM cd.members;
	
-- More aggregation
SELECT firstname, surname, joindate FROM cd.members ORDER BY joindate DESC LIMIT 1;

-- Joins and Subqueries
-- Retrieve the start times of members' bookings
SELECT b.starttime FROM cd.bookings AS b
  JOIN (SELECT memid FROM cd.members WHERE firstname = 'David' AND surname= 'Farrell') AS ids
  ON ids.memid = b.memid;
  
-- Work out the start times of bookings for tennis courts
SELECT s.starttime, f.name FROM
  (SELECT starttime, facid FROM cd.bookings 
   WHERE starttime >= '2012-09-21' AND starttime < '2012-09-22') AS s
  JOIN 
  (SELECT name, facid FROM cd.facilities 
   WHERE name LIKE 'Tennis%') AS f 
  ON f.facid = s.facid;

-- Produce a list of all members who have recommended another member
SELECT DISTINCT m.firstname, m.surname FROM cd.members AS m
  JOIN cd.members AS r ON r.recommendedby = m.memid
  ORDER BY surname, firstname ASC;
  
-- Produce a list of all members, along with their recommender
SELECT DISTINCT m.firstname AS memfname, m.surname AS memsname,
	        r.firstname AS recfname, r.surname AS recsname
  FROM cd.members AS m
  LEFT JOIN cd.members AS r ON m.recommendedby = r.memid
  ORDER BY memsname, memfname;
  
-- Aggregates
-- Count the number of facilities
SELECT count(facid) FROM cd.facilities;

-- Count the number of expensive facilities
SELECT count(*) FROM cd.facilities WHERE guestcost >= 10;

-- Count the number of recommendations each member makes.
SELECT recommendedby, count(*) 
  FROM cd.members
  WHERE recommendedby IS NOT null
  GROUP BY recommendedby
ORDER BY recommendedby;

-- Find the count of members who have made at least one booking
SELECT count(DISTINCT memid) FROM cd.bookings;

-- List facilities with more than 1000 slots booked
SELECT facid, sum(slots) as "Total Slots" FROM cd.bookings 
  GROUP BY facid
  HAVING sum(slots) >= 1000
  ORDER BY facid;
  
-- Strings
-- Format the names of members
SELECT (surname || ', ' || firstname) AS name FROM cd.members;

-- Find facilities by a name prefix
SELECT * FROM cd.facilities WHERE name LIKE 'Tennis%';