pgexercises

my solutions to pgexercises

exercises

1.basic

• [x] Retrieve everything from a table
• [x] Retrieve specific columns from a table
• [x] Control which rows are retrieved
• [x] Control which rows are retrieved - part 2
• [x] Basic string searches
• [x] Matching against multiple possible values
• [x] Classify results into buckets
• [x] Working with dates
• [x] Removing duplicates, and ordering results
• [x] Combining results from multiple queries
• [x] Simple aggregation
• [x] More aggregation

2.joins and subqueries

• [x] Produce a timestamp for 1 a.m. on the 31st of August 2012
• [x] Subtract timestamps from each other
• [x] Generate a list of all the dates in October 2012
• [x] Get the day of the month from a timestamp
• [x] Work out the number of seconds between timestamps
• [x] Work out the number of days in each month of 2012
• [x] Work out the number of days remaining in the month
• [x] Work out the end time of bookings
• [x] Return a count of bookings for each month
• [x] Work out the utilisation percentage for each facility by month

3. modifying data

• [x] Insert some data into a table
• [x] Insert multiple rows of data into a table
• [x] Insert calculated data into a table
• [x] Update some existing data
• [x] Update multiple rows and columns at the same time
• [x] Update a row based on the contents of another row
• [x] Delete all bookings
• [x] Delete a member from the cd.members table
• [x] Delete based on a subquery

4. aggregate

• [x] Count the number of facilities
• [x] Count the number of expensive facilities
• [x] Count the number of recommendations each member makes.
• [x] List the total slots booked per facility
• [x] List the total slots booked per facility in a given month
• [x] List the total slots booked per facility per month
• [x] Find the count of members who have made at least one booking
• [x] List facilities with more than 1000 slots booked
• [x] Find the total revenue of each facility
• [x] Find facilities with a total revenue less than 1000
• [x] Output the facility id that has the highest number of slots booked
• [x] List the total slots booked per facility per month, part 2
• [x] List the total hours booked per named facility
• [x] List each member's first booking after September 1st 2012
• [x] Produce a list of member names, with each row containing the total member count
• [x] Produce a numbered list of members
• [x] Output the facility id that has the highest number of slots booked, again
• [x] Rank members by (rounded) hours used
• [x] Find the top three revenue generating facilities
• [x] Classify facilities by value
• [x] Calculate the payback time for each facility
• [x] Calculate a rolling average of total revenue

5. date

• [x] Produce a timestamp for 1 a.m. on the 31st of August 2012
• [x] Subtract timestamps from each other
• [x] Generate a list of all the dates in October 2012
• [x] Get the day of the month from a timestamp
• [x] Work out the number of seconds between timestamps
• [x] Work out the number of days in each month of 2012
• [x] Work out the number of days remaining in the month
• [x] Work out the end time of bookings
• [x] Return a count of bookings for each month
• [x] Work out the utilisation percentage for each facility by month

6. string

• [x] Format the names of members
• [x] Find facilities by a name prefix
• [x] Perform a case-insensitive search
• [x] Find telephone numbers with parentheses
• [x] Pad zip codes with leading zeroes
• [x] Count the number of members whose surname starts with each letter of the alphabet
• [x] Clean up telephone numbers

7. recursive

• [x] Find the upward recommendation chain for member ID 27
• [x] Find the downward recommendation chain for member ID 1
• [x] Produce a CTE that can return the upward recommendation chain for any member