alisdairo / pgexercises Goto Github PK

Exercise: https://www.pgexercises.com/questions/updates/insert3.html

Part of the answer text:

Since the VALUES clause is only used to supply constant data, we need to replace it with a query instead. The SELECT statement is fairly simple: there's an inner subquery that works out the next facid based on the largest current id, and the rest is just constant data. The output of the statement is a row that we insert into the facilities table.

However, a solution with VALUES is accepted by the web application:

INSERT INTO cd.facilities (facid, name, membercost, guestcost, initialoutlay, monthlymaintenance)
VALUES ((SELECT max(facid) FROM cd.facilities) + 1, 'Spa', 20, 30, 100000, 800)

Moreover, the postgresql docs for VALUES includes the following:

Syntactically, VALUES followed by expression lists is treated as equivalent to:

SELECT select_list FROM table_expression
and can appear anywhere a SELECT can. For example, you can use it as part of a UNION, or attach a sort_specification (ORDER BY, LIMIT, and/or OFFSET) to it. VALUES is most commonly used as the data source in an INSERT command, and next most commonly as a subquery.

which, if I understand correctly, isn't consistent with the above-quoted answer text.

If you agree, @AlisdairO, I'm happy to change the exercise text accordingly.

Hi, I've solved this exercise using a sub-query instead of a join:
select distinct cd.members.firstname, cd.members.surname from cd.members where cd.members.memid in (select distinct cd.members.recommendedby from cd.members) order by cd.members.surname;

And I wonder if it makes sense to add it as another option to the explanation, since this this section deals with joins.

For the following exercise:
https://pgexercises.com/questions/basic/unique.html

The solution includes (and introduces) the LIMIT keyword. However, in contrast to other keywords such as SELECT and DISTINCT LIMIT does not receive the blue/purple highlighting that other keywords do.
This seems to indicate a difference between these types of keywords that (as far as I know) doesn't exist.

Currently, an acceptable answer to the simple aggregation exercise is:

SELECT max(joindate)
FROM cd.members;

which notably doesn't include an as latest in the SELECT clause.

I wanted to flag this incase the maintainer wanted to be strict about what exactly constitutes a correct answer. IMO, it's valuable to be strict here considering pgexercises is all about learning new topics.

If interested in correcting, I'm happy to help if pointed in the right direction!

Strange,

I can see you've fixed this, but if I download from either:

• https://raw.githubusercontent.com/AlisdairO/pgexercises/master/site/website/dbfiles/clubdata.sql
• https://www.pgexercises.com/dbfiles/clubdata.sql

I still get that badly formatted line (space separated, not tabs separated)

In this exercise the resulting column is not checked, e.g. the query:

select name, case when monthlymaintenance < 100 THEN 'cheap' else 'expensive' end from cd.facilities

is treated as correct solution.

https://www.pgexercises.com/questions/basic/unique.html
Here is the expected result:

surname
--
Bader
Baker
Boothe
Butters
Coplin
Crumpet
Dare
Farrell
GUEST
Genting

Here is the actual result: (the result is get from your answer)

surname
--
Bader
Baker
Boothe
Butters
Coplin
Crumpet
Dare
Farrell
Genting
GUEST

Just noticed this while looking at issue #16 . Where the query-running shortcuts only need to work inside the text box, help ought to be accessible wherever.

First of all thanks for your work, it's helping a lot :)

On the exercise Threejoin I've submitted a wrong answers (I've forgot to order the results), but the answer has been evaluated as correct.

My answer:

select distinct (m.firstname || ' ' || m.surname ) as member, f.name as facility
from cd.members m join cd.bookings b using(memid) join cd.facilities f using (facid)
where f.name like '%Tennis Court%'

You can see in the screenshot below that my answer and the expected result differ.

Schema&help is very helpful but to look at it one has to open/close it. It could be a foldable element above question, so one can persistantly show it on page.

Should check at least once every few minutes:

• Basic front page reachability
• Ability to perform a read-only query
• Ability to perform a write query

I recently solved https://pgexercises.com/questions/joins/self.html
but from the problem description I thought the solution was

select distinct mems.firstname as firstname, mems.surname as surname
from cd.members mems inner join cd.members recs on mems.recommendedby=recs.memid
order by surname, firstname;

that is, we should return all the members that have been recommended by some other member. Meaning we should be selecting the members name not the recommender's name.

right?

URL: https://www.pgexercises.com/questions/joins/self2.html

Question is 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).

memfname, memsname combinations should be unique by nature of question, but I see this in the correct response:

It'd be really nice if there was a section on GIS on the website, the topic itself is huge and there's a lot of material to cover.

Having exercises to help explain the material would be very nice.

It's quite a huge feature, so I understood it would take time to implement it.

Currently the exercices, explanations and entire website is in english. It would be awesome to set up translation with tools like Weblate or Crowdin (free for open-source project like this one) in order to have a localized website for non-english speaking learners

Postgres-specific functionality like arrays, ranges, JSON types.

I'd like to hide this stuff for the basic exercises to avoid confusion for beginners. I think we could potentially implement a second database for these (will need a separate schema diagram, etc)

https://github.com/AlisdairO/pgexercises/blob/master/questions/date/00050000-interval2.ex

copy pasting the example query into "Your answer" fails to validate as it has decimals while the Expected results do not:

select extract(epoch from (timestamp '2012-09-02 00:00:00' - '2012-08-31 01:00:00'));          

can be fixed by turning into an integer with round:

select round(extract(epoch from (timestamp '2012-09-02 00:00:00' - '2012-08-31 01:00:00')));          

but AFAIK the sample result is from that example query in the first place so not sure why.

Hello.
I want a tool similar to jsfiddle but for sql.

Could you add the ability to hash a sql fragment and provide a url to it?

http://sqlfiddle.com should work like this but in my experience it does not.
I'd also be willing to help make this happen.

This exercise: https://pgexercises.com/questions/aggregates/payback.html

Could use some explanation about why the group by works with the PK and not just all the fields.

I'm curious why the solution uses:

trim(to_char(sum(bks.slots)/2.0, '9999999999999999D99'))

rather than the simpler:

round(sum(bks.slots) / 2.0, 2)

Am I missing some downside?

In popup windows like Hint close button haven't icon (it is very important :) ). Try to load jquery-ui.min.js after bootstrap.min.js

Some tools^† do not support the COPY command;

COPY tablename (columnnames) FROM stdin

I propose the clubdata.sql dump be converted to use simple INSERT statements.

For example,

COPY facilities (facid, name, membercost, guestcost, initialoutlay, monthlymaintenance) FROM stdin;
0   Tennis Court 1  5   25  10000   200
1   Tennis Court 2  5   25  8000    200
...
\.

would become

INSERT INTO facilities (facid, name, membercost, guestcost, initialoutlay, monthlymaintenance) VALUES
    (0, 'Tennis Court 1', 5, 25, 10000, 200),
    (1, 'Tennis Court 2', 5, 25, 8000, 200),
...
;

I appreciate the (minor) added verbosity is unpleasant, but perhaps compatibility should be prioritised.

My use-case was building the schema for sharing on SQL Fiddle which uses PostgreSQL 9.3.

^† When attempting to import the SQL into SQLFiddle.com, the following error was displayed:

The JDBC driver currently does not support COPY operations.

Per HN comment:

After doing a couple exercises what I don't like is the naming convention. Something like "montlymaintenance" or "recommendedby" are just hard to read and type and I'd much rather use snake-case and/or shorter names. Eg. "monthly_maintenance" or "maintenance_per_month" or just "maintenance" if it's explicit enough that it's always per month.

Also to me using shorthands for ids is a bit hmm hmm I mean sure you write them a lot so it's useful to spare your fingers when typing queries but "facid" just seems wrong. Again maybe underscore? "fac_id" or even "facility_id".

This is correct, the schema could use a bunch of fixing up in this regard. Sadly fixing this is a nontrivial change, as all the answers, explanations, and diagrams will need to be updated along with the SQL itself.

These are legacy from when I had separate code for static site generation. We may wish to keep some of these adjustments for the sake of prettiness, but if so may want to consider moving them to front end.

It'd be nice to press 'shift+enter' or similar inside the query textarea to Run the Query.

Formatting is bad/cramped. Design should be more vertical, particularly of the main working/answer area.

Open to suggestions from people who don't suck at webdev as to whether this should be accomplished via a similar-but-separate site interface on mobile devices, or whether I should work on making the existing responsiveness better.

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.

Expected Result
Lists currently "Total Slots"

Problem
Question asks for hours (one slot is apparently 30min, mentioned somewhere else) but answers with slots -> inconsistent

'Introspecting your database'?

A common complain with the site is that viewing the schema is too hard/cluttered. Would probably make sense to make it available inline, in a collapsible form.

Had a request from a user to update the server to 9.5, this is just to remind me to do it :-)

I successfully ran psql -U <username> -f clubdata.sql -d postgres -x -q which populated and created the database. But I don't know how to list any of the tables in psql. Running \d or \dt doesn't list any relations. But I am able to query the tables, for example select * from cd.facilities returns all the facilities.

Thanks in advance!

EDIT
It turns out I need to run \dt cd.* to find all the tables belonging to the schema. I just loves psql's syntax.

Shortcut to run query is great. It will be nice to have one to go to the new exercise.

Thanks for putting this resource together!! It'd be super helpful to have a search capability on the site where I could type in something like limit and find any exercises that would cater to that SQL skill. I'm planning on using this to help my dev team level up on their "raw sql" skills so we can more easily performance tune some of our Rails apps. Alternatively if the exercise descriptions somehow mentioned which operator/skill they address we could use the built in find feature of most browsers to look for what we need. Maybe if there was a separate outline view page with that manually added those annotations to start? I might be able to help contribute to this request as well, but wanted to ping you on here first to see if it's on the roadmap.

http://pgexercises.com/questions/aggregates/fachoursbymonth3.html

This works without specifying the time frame (the year 2012), which I guess might mean that all the data in the db (at least for the bookings table) is within the year 2012.

(With thanks to Shant Nercessian for pointing it out!)

If I am not completely mistaken there should be a build.xml in the SQLForwarder directory (according to avajava).

But I have no experience whatsover with this topic - therefore my opinion is woth .5 cents.

First off, amazingly helpful site.

Here's a small pain point I'm experiencing:

After I get the correct answer, I often like to experiment with the query to find other alternate techniques. If I run another successful query there's no indication that anything happened.

My suggestion:

Clear the output before another query is run.

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

Model Answer
select count(distinct memid) from cd.bookings

Problem
This counts the guest user with memid=0 as a member, this is logically wrong, since a guest is by definition not a member. Either question has to be reworded or model answer updated with where memid != 0

First of all - Great set of exercises, many thanks for that!

I noticed that the solution for 00060000-fachours3.ex uses char formatting (which I did not think of while solving it) and I was wondering if there is a reason for leaving out the (for me) obvious way to apply trunc(<value>, 2).
If not, could this be included? I would also be happy to contribute a PR, just let me know.

For example:

select f.facid, f.name, trunc(sum(b.slots / 2.0), 2)
	from
	cd.facilities f join cd.bookings b
		on b.facid = f.facid
group by 1,2
order by 1;

BTW - for the dataset, trunc and round yield the same results.

Implement DDL exercises. There's some challenges here, particularly in how we validate results and display them back to the user.

There's currently a mishmash of 9.2 and 9.3 links

It says that PG doesn't support rollup, which is no longer true as of 9.5!

There is no info about using trim function in the hint (for Aggregates / fachours3 exercise. Visually, the query

select
	f.facid, f.name,
	to_char(sum(b.slots) * 0.5, '999.99') as hours
from cd.bookings as b
join cd.facilities as f on b.facid = f.facid
group by f.facid, f.name
order by f.facid asc
;

gives an answer that looks like the expected one. But due spaces it is a wrong answer. It took a quite time for me to figure out why is my query wrong. (I gave up and pressed Answer button 😅 )