Here are some of my notes to the answers in pgexercises

pgexercises-basic-notes.md

Basic

Retrieve everything from a table

select * from cd.facilities;

Retrieve specific columns from the table

select name, membercost from cd.facilities;

Control which rows are retrieved - part 1

select * from cd.facilities where membercost > 0;

Control which rows are retrieved - part 2

Notice the grouping for the last statement. Divisor is float. Use AND, OR, NOT

select facid, name, membercost, monthlymaintenance from cd.facilities where membercost > 0 and (membercost < monthlymaintenance/50.0);

Basic string searches

select * from cd.facilities where name like '%Tennis%';

Date comparison

There should be a space between DATE and the single quoted date literal in the format of 'YYYY-MM-DD' select memid, surname, firstname, joindate from cd.members where joindate > DATE '2012-09-01';

Distinct, limit

I still can't figure out how to correctly indent the SQL statements to make it more readable. In code, it's easy, just line up the brackets and your done. In SQL there's no hard and fast rules how to format them to become more readable.

select distinct surname from cd.members order by surname limit 10;

Union

This is like a set operation union. Combines the results from the two select statements.

select surname from cd.members UNION select name from cd.facilities;

Simple Aggregation

Simple use of the MAX function to retrieve the maximum value in the column.

select MAX(joindate) from cd.members as latest;

More Aggregation

select firstname, surname, joindate from cd.members order by joindate desc limit 1;

OR

select firstname, surname, joindate where joindate = (select MAX(joindate) from cd.members);