How can you produce a list of facilities that charge a fee to members

21.08.202221.08.2022 admin 0 Comments how, how to made, как, как сделать, как создать, совими руками

How can you produce a list of facilities that charge a fee to members

Practicing on PostgreSQL

What’s this about?

I recently started practicing again on PostgreSQL, and databases in general. I want to get past the beginner/intermediate level where I can say that I have mastered some expert features of SQL.

Learning about databases is

100x easier when working on real data, especially your data, i.e. when you have a project, and a goal to achieve. That’s how I started learning the basics, in order to satisfy the needs of my own projects. But now, wishing to «dive deeper» I thought about exploring some other, unknown dataset. Finally, I hope it will also serve as a a conversation starter in an interview.

Here’s an account of me solving pgexercises.

PosgreSQL exercises aka pgexercises is a website by Alisdair Owens. It provides a way to in-depth practice (Postgres) SQL with a real-like database that is complex enough to be used for both simple SELECT * FROM statements, as well as practice more advanced things such as recursion, dates/tims and aggregation.

The dataset for these exercises is for a newly created country club, with a set of members, facilities such as tennis courts, and booking history for those facilities.

Question :
How can you retrieve all the information from the cd.facilities table?

Question :
You want to print out a list of all of the facilities and their cost to members. How would you retrieve a list of only facility names and costs?

Question :
How can you produce a list of facilities that charge a fee to members?

Question :
How can you produce a list of facilities that charge a fee to members, and that fee is less than 1/50th of the monthly maintenance cost? Return the facid, facility name, member cost, and monthly maintenance of the facilities in question.

Question :
How can you produce a list of all facilities with the word ‘Tennis’ in their name?

Reminder, _ matches single characters, and % matches any strings.

Question :
How can you retrieve the details of facilities with ID 1 and 5? Try to do it without using the OR operator.

The AS operator is very useful in labeling columns or expressions, not only for display purposes, but also for easy reference.

Question :
How can you produce a list of members who joined after the start of September 2012? Return the memid, surname, firstname, and joindate of the members in question.

Question :
How can you produce an ordered list of the first 10 surnames in the members table? The list must not contain duplicates.

Question :
You, for some reason, want a combined list of all surnames and all facility names. Yes, this is a contrived example :-). Produce that list!

UNION combines the results of two queries into a single table. Both results must have the same number of columns and compatible data types. UNION removes duplicate rows, unlike UNION ALL.

Question :
You’d like to get the signup date of your last member. How can you retrieve this information?

I found out that, returning the max(joindate) AS latest and using this reference to filter didn’t work. One other approach would be

Question :
How can you produce a list of the start times for bookings by members named ‘David Farrell’?

Question :
How can you produce a list of the start times for bookings for tennis courts, for the date ‘2012-09-21’? Return a list of start time and facility name pairings, ordered by the time.

Question :
How can you output a list of all members who have recommended another member? Ensure that there are no duplicates in the list, and that results are ordered by (surname, firstname).

Question :
How can you output a list of all members, including the individual who recommended them (if any)? Ensure that results are ordered by (surname, firstname).

Question :
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.

Question :
How can you output a list of all members, including the individual who recommended them (if any), without using any joins? Ensure that there are no duplicates in the list, and that each firstname + surname pairing is formatted as a column and ordered.

Reminder, subquery must return one column, that’s why I concantenated the firstname/surname strings.

Question :
The Produce a list of costly bookings exercise contained some messy logic: we had to calculate the booking cost in both the WHERE clause and the CASE statement. Try to simplify this calculation using subqueries. For reference, the question was:

Question :
In the previous exercise, you learned how to add a facility. Now you’re going to add multiple facilities in one command. Use the following values:
facid: 9, Name: ‘Spa’, membercost: 20, guestcost: 30, initialoutlay: 100000, monthlymaintenance: 800.
facid: 10, Name: ‘Squash Court 2’, membercost: 3.5, guestcost: 17.5, initialoutlay: 5000, monthlymaintenance: 80.

Question :
Let’s try adding the spa to the facilities table again. This time, though, we want to automatically generate the value for the next facid, rather than specifying it as a constant. Use the following values for everything else:
Name: ‘Spa’, membercost: 20, guestcost: 30, initialoutlay: 100000, monthlymaintenance: 800.

Question :
We made a mistake when entering the data for the second tennis court. The initial outlay was 10000 rather than 8000: you need to alter the data to fix the error.

Question :
We want to increase the price of the tennis courts for both members and guests. Update the costs to be 6 for members, and 30 for guests.

Alternatively I could use facid in(0,1)

Question :
We want to alter the price of the second tennis court so that it costs 10% more than the first one. Try to do this without using constant values for the prices, so that we can reuse the statement if we want to.

Question :
As part of a clearout of our database, we want to delete all bookings from the cd.bookings table. How can we accomplish this?

Question :
We want to remove member 37, who has never made a booking, from our database. How can we achieve that?

Question :
In our previous exercises, we deleted a specific member who had never made a booking. How can we make that more general, to delete all members who have never made a booking?

Question :
Produce a count of the number of facilities that have a cost to guests of 10 or more.

One can also use count(*) so as to just count the rows, not based on the presence of a specific value.

Question :
Produce a count of the number of recommendations each member has made. Order by member ID.

Question :
Produce a list of the total number of slots booked per facility. For now, just produce an output table consisting of facility id and slots, sorted by facility id.

Question :
Produce a list of the total number of slots booked per facility in the month of September 2012. Produce an output table consisting of facility id and slots, sorted by the number of slots.

Question :
Produce a list of the total number of slots booked per facility per month in the year of 2012. Produce an output table consisting of facility id and slots, sorted by the id and month.

Question :
Find the total number of members who have made at least one booking.

Question :
Produce a list of facilities with more than 1000 slots booked. Produce an output table consisting of facility id and hours, sorted by facility id.

Question :
Produce a list of facilities along with their total revenue. The output table should consist of facility name and revenue, sorted by revenue. Remember that there’s a different cost for guests and members!

Question :
Produce a list of facilities with a total revenue less than 1000. Produce an output table consisting of facility name and revenue, sorted by revenue. Remember that there’s a different cost for guests and members!

One would try to just use the directly previous statement, plus the HAVING function! Well, this doesn’t work, as the HAVING does not support column names.

Question :
Output the facility id that has the highest number of slots booked. For bonus points, try a version without a LIMIT clause. This version will probably look messy!

I couldn’t get TOP to work, but I didn’t look more into it.

Produce a list of the total number of slots booked per facility per month in the year of 2012. In this version, include output rows containing totals for all months per facility, and a total for all months for all facilities. The output table should consist of facility id, month and slots, sorted by the id and month. When calculating the aggregated values for all months and all facids, return null values in the month and facid columns.

Question :
Produce a list of the total number of hours booked per facility, remembering that a slot lasts half an hour. The output table should consist of the facility id, name, and hours booked, sorted by facility id. Try formatting the hours to two decimal places.

Working with real-world data, you will almost always deal with some kind of date and time data. Such data is also valuable for debugging purposes, and might be life-saving in disaster scenarios.

Question :
Produce a timestamp for 1 a.m. on the 31st of August 2012.

I lie the approach of casting a formatted string to a timestamp this way.
One could also use something simpler like select timestamp ‘2012-08-31 01:00:00’;

kota8 / PostgreSQL-Exercises.md

Retrieve everything from a table

How can you retrieve all the information from the cd.facilities table? Link

Retrieve specific columns from a table

You want to print out a list of all of the facilities and their cost to members. How would you retrieve a list of only facility names and costs? Link

Control which rows are retrieved

You want to print out a list of all of the facilities and their cost to members. How would you retrieve a list of only facility names and costs? Link

How can you produce a list of facilities that charge a fee to members, and that fee is less than 1/50th of the monthly maintenance cost? Return the facid, facility name, member cost, and monthly maintenance of the facilities in question. Link

Basic string searches

How can you produce a list of all facilities with the word ‘Tennis’ in their name? Link

Matching against multiple possible values

How can you retrieve the details of facilities with ID 1 and 5? Try to do it without using the OR operator. Question

Classify results into buckets

Working with dates

How can you produce a list of members who joined after the start of September 2012? Return the memid, surname, firstname, and joindate of the members in question. Link

Removing duplicates, and ordering results

How can you produce an ordered list of the first 10 surnames in the members table? The list must not contain duplicates. Link

Combining results from multiple queries

You, for some reason, want a combined list of all surnames and all facility names. Yes, this is a contrived example :-). Produce that list! Link

You’d like to get the signup date of your last member. How can you retrieve this information? Link

Jois and Subqueries

Retrieve the start times of members’ bookings

How can you produce a list of the start times for bookings by members named ‘David Farrell’? Link

Work out the start times of bookings for tennis courts

How can you produce a list of the start times for bookings for tennis courts, for the date ‘2012-09-21’? Return a list of start time and facility name pairings, ordered by the time. Link

Produce a list of all members who have recommended another member

How can you output a list of all members who have recommended another member? Ensure that there are no duplicates in the list, and that results are ordered by (surname, firstname). Link

Produce a list of all members, along with their recommender

How can you output a list of all members, including the individual who recommended them (if any)? Ensure that results are ordered by (surname, firstname). Link

Produce a list of all members who have used a tennis court

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. Link

Produce a list of costly bookings

A versin with WITH (subquery).

With no subquery.

Produce a list of all members, along with their recommender, using no joins.

How can you output a list of all members, including the individual who recommended them (if any), without using any joins? Ensure that there are no duplicates in the list, and that each firstname + surname pairing is formatted as a column and ordered. Link

First version, implicit join.

Version with subquery.

Produce a list of costly bookings, using a subquery

The Produce a list of costly bookings exercise contained some messy logic: we had to calculate the booking cost in both the WHERE clause and the CASE statement. Try to simplify this calculation using subqueries. For reference, the question was:

Insert some data into a table

Insert multiple rows of data into a table

In the previous exercise, you learned how to add a facility. Now you’re going to add multiple facilities in one command. Use the following values:

Insert calculated data into a table

Let’s try adding the spa to the facilities table again. This time, though, we want to automatically generate the value for the next facid, rather than specifying it as a constant. Use the following values for everything else:

Update some existing data

We made a mistake when entering the data for the second tennis court. The initial outlay was 10000 rather than 8000: you need to alter the data to fix the error. Link

Update multiple rows and columns at the same time

We want to increase the price of the tennis courts for both members and guests. Update the costs to be 6 for members, and 30 for guests. Link

Update a row based on the contents of another row

We want to alter the price of the second tennis court so that it costs 10% more than the first one. Try to do this without using constant values for the prices, so that we can reuse the statement if we want to.

Delete all bookings

As part of a clearout of our database, we want to delete all bookings from the cd.bookings table. How can we accomplish this?

Delete a member from the cd.members table

We want to remove member 37, who has never made a booking, from our database. How can we achieve that?

Delete based on a subquery

In our previous exercises, we deleted a specific member who had never made a booking. How can we make that more general, to delete all members who have never made a booking? Link

Count the number of facilities

Count the number of expensive facilities

Produce a count of the number of facilities that have a cost to guests of 10 or more. Link

Count the number of recommendations each member makes.

Produce a count of the number of recommendations each member has made. Order by member ID. Link

List the total slots booked per facility

Produce a list of the total number of slots booked per facility. For now, just produce an output table consisting of facility id and slots, sorted by facility id. Link

List the total slots booked per facility in a given month

Produce a list of the total number of slots booked per facility in the month of September 2012. Produce an output table consisting of facility id and slots, sorted by the number of slots. Link

List the total slots booked per facility per month

Produce a list of the total number of slots booked per facility per month in the year of 2012. Produce an output table consisting of facility id and slots, sorted by the id and month. Question

Find the count of members who have made at least one booking

Find the total number of members who have made at least one booking. Link

List facilities with more than 1000 slots booked

Produce a list of facilities with more than 1000 slots booked. Produce an output table consisting of facility id and hours, sorted by facility id. Link

Find the total revenue of each facility

Produce a list of facilities along with their total revenue. The output table should consist of facility name and revenue, sorted by revenue. Remember that there’s a different cost for guests and members! Link

Find facilities with a total revenue less than 1000

Produce a list of facilities with a total revenue less than 1000. Produce an output table consisting of facility name and revenue, sorted by revenue. Remember that there’s a different cost for guests and members! Link

Output the facility id that has the highest number of slots booked

Output the facility id that has the highest number of slots booked. For bonus points, try a version without a LIMIT clause. This version will probably look messy! Link

Terrible first attempt:

With WITH clause:

List the total slots booked per facility per month, part 2

List the total hours booked per named facility

Produce a list of the total number of hours booked per facility, remembering that a slot lasts half an hour. The output table should consist of the facility id, name, and hours booked, sorted by facility id. Try formatting the hours to two decimal places. Link

List each member’s first booking after September 1st 2012

Produce a list of each member name, id, and their first booking after September 1st 2012. Order by member ID. Link

Produce a list of member names, with each row containing the total member count

Produce a list of member names, with each row containing the total member count. Order by join date. Link

Produce a numbered list of members

Produce a monotonically increasing numbered list of members, ordered by their date of joining. Remember that member IDs are not guaranteed to be sequential. Link

Output the facility id that has the highest number of slots booked, again

Output the facility id that has the highest number of slots booked. Ensure that in the event of a tie, all tieing results get output. Link

Version that does not work with ties.

Rank members by (rounded) hours used

Produce a list of members, along with the number of hours they’ve booked in facilities, rounded to the nearest ten hours. Rank them by this rounded figure, producing output of first name, surname, rounded hours, rank. Sort by rank, surname, and first name.

Find the top three revenue generating facilities

Produce a list of the top three revenue generating facilities (including ties). Output facility name and rank, sorted by rank and facility name. Link

Classify facilities by value

Classify facilities into equally sized groups of high, average, and low based on their revenue. Order by classification and facility name. Link

Version tried with rank() and count() window function.

Version with ntile() windows function.

Calculate the payback time for each facility

Based on the 3 complete months of data so far, calculate the amount of time each facility will take to repay its cost of ownership. Remember to take into account ongoing monthly maintenance. Output facility name and payback time in months, order by facility name. Don’t worry about differences in month lengths, we’re only looking for a rough value here! Link

Calculate a rolling average of total revenue

For each day in August 2012, calculate a rolling average of total revenue over the previous 15 days. Output should contain date and revenue columns, sorted by the date. Remember to account for the possibility of a day having zero revenue. This one’s a bit tough, so don’t be afraid to check out the hint! Link

Produce a timestamp for 1 a.m. on the 31st of August 2012

Produce a timestamp for 1 a.m. on the 31st of August 2012. Link

Subtract timestamps from each other

Find the result of subtracting the timestamp ‘2012-07-30 01:00:00’ from the timestamp ‘2012-08-31 01:00:00’. Link

Generate a list of all the dates in October 2012

Produce a list of all the dates in October 2012. They can be output as a timestamp (with time set to midnight) or a date. Link

Get the day of the month from a timestamp

Get the day of the month from the timestamp ‘2012-08-31’ as an integer. Link

Work out the number of seconds between timestamps

Work out the number of seconds between the timestamps ‘2012-08-31 01:00:00’ and ‘2012-09-02 00:00:00’. Link

Work out the number of days in each month of 2012

For each month of the year in 2012, output the number of days in that month. Format the output as an integer column containing the month of the year, and a second column containing an interval data type. Link

Work out the number of days remaining in the month

For any given timestamp, work out the number of days remaining in the month. The current day should count as a whole day, regardless of the time. Use ‘2012-02-11 01:00:00’ as an example timestamp for the purposes of making the answer. Format the output as a single interval value. Link

Work out the end time of bookings

Return a list of the start and end time of the last 10 bookings (ordered by the time at which they end, followed by the time at which they start) in the system. Link

Return a count of bookings for each month

Return a count of bookings for each month, sorted by month Link

Work out the utilisation percentage for each facility by month

Work out the utilisation percentage for each facility by month, sorted by name and month, rounded to 1 decimal place. Opening time is 8am, closing time is 8.30pm. You can treat every month as a full month, regardless of if there were some dates the club was not open. Link

Format the names of members

Output the names of all members, formatted as ‘Surname, Firstname’ Link

Find facilities by a name prefix

Find all facilities whose name begins with ‘Tennis’. Retrieve all columns. Link

Perform a case-insensitive search

Perform a case-insensitive search to find all facilities whose name begins with ‘tennis’. Retrieve all columns. Link

Find telephone numbers with parentheses

You’ve noticed that the club’s member table has telephone numbers with very inconsistent formatting. You’d like to find all the telephone numbers that contain parentheses, returning the member ID and telephone number sorted by member ID. Link

Pad zip codes with leading zeroes

The zip codes in our example dataset have had leading zeroes removed from them by virtue of being stored as a numeric type. Retrieve all zip codes from the members table, padding any zip codes less than 5 characters long with leading zeroes. Order by the new zip code. Link

Count the number of members whose surname starts with each letter of the alphabet

You’d like to produce a count of how many members you have whose surname starts with each letter of the alphabet. Sort by the letter, and don’t worry about printing out a letter if the count is 0. Link

Clean up telephone numbers

The telephone numbers in the database are very inconsistently formatted. You’d like to print a list of member ids and numbers that have had ‘-‘,'(‘,’)’, and ‘ ‘ characters removed. Order by member id. Link

Find the upward recommendation chain for member ID 27

Find the downward recommendation chain for member ID 1

Produce a CTE that can return the upward recommendation chain for any member

gourob97 / postgres-excercise Goto Github PK

postgres-excercise’s Introduction

These codes below are actually answers of the pracitice website https://pgexercises.com

The exercises are divided into some increasingly difficult topic. Such as:

a) How can you retrieve all the information from the cd.facilities table?

b)You want to print out a list of all of the facilities and their cost to members. How would you retrieve a list of only facility names and costs?

c) How can you produce a list of facilities that charge a fee to members?

d) How can you produce a list of facilities that charge a fee to members, and that fee is less than 1/50th of the monthly maintenance cost? Return the facid, facility name, member cost, and monthly maintenance of the facilities in question.

e) How can you produce a list of all facilities with the word ‘Tennis’ in their name?

f) How can you retrieve the details of facilities with ID 1 and 5? Try to do it without using the OR operator.

h) How can you produce a list of members who joined after the start of September 2012? Return the memid, surname, firstname, and joindate of the members in question.

i) How can you produce an ordered list of the first 10 surnames in the members table? The list must not contain duplicates.

j) You, for some reason, want a combined list of all surnames and all facility names. Yes, this is a contrived example :-). Produce that list!

k) You’d like to get the signup date of your last member. How can you retrieve this information?

gourob97/postgres-excercise

Use Git or checkout with SVN using the web URL.

Work fast with our official CLI. Learn more.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching Xcode

If nothing happens, download Xcode and try again.

Launching Visual Studio Code

Your codespace will open once ready.

There was a problem preparing your codespace, please try again.

Latest commit

Git stats

Files

Failed to load latest commit information.

readme.md

These codes below are actually answers of the pracitice website https://pgexercises.com

The exercises are divided into some increasingly difficult topic. Such as: