Basic Exercises

This category deals with the basics of SQL. It covers select and where clauses, case expressions, unions, and a few other odds and ends.

Retrieve everything

Retrieve all information from facilities table.

SELECT * FROM facilities

# By default, when no fields are explicitly passed to select(), all fields
# will be selected.
query = Facility.select()

Retrieve specific columns from a table

Retrieve names of facilities and cost to members.

SELECT name, membercost FROM facilities;

query = Facility.select(Facility.name, Facility.membercost)
# To iterate:
for facility in query:
    print(facility.name)

Control which rows are retrieved

Retrieve list of facilities that have a cost to members.

SELECT * FROM facilities WHERE membercost > 0

query = Facility.select().where(Facility.membercost > 0)

Control which rows are retrieved - part 2

Retrieve list of facilities that have a cost to members, and that fee is less than 1/50th of the monthly maintenance cost. Return id, name, cost and monthly-maintenance.

SELECT facid, name, membercost, monthlymaintenance
FROM facilities
WHERE membercost > 0 AND membercost < (monthlymaintenance / 50)

query = (Facility
         .select(Facility.facid, Facility.name, Facility.membercost,
                 Facility.monthlymaintenance)
         .where(
             (Facility.membercost > 0) &
             (Facility.membercost < (Facility.monthlymaintenance / 50))))

Basic string searches

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

SELECT * FROM facilities WHERE name ILIKE '%tennis%';

query = Facility.select().where(Facility.name.contains('tennis'))
# OR use the exponent operator. Note: you must include wildcards here:
query = Facility.select().where(Facility.name ** '%tennis%')

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.

SELECT * FROM facilities WHERE facid IN (1, 5);

query = Facility.select().where(Facility.facid.in_([1, 5]))
# OR:
query = Facility.select().where((Facility.facid == 1) |
                                (Facility.facid == 5))

Classify results into buckets

How can you produce a list of facilities, with each labelled as ‘cheap’ or ‘expensive’ depending on if their monthly maintenance cost is more than $100? Return the name and monthly maintenance of the facilities in question.

SELECT name,
CASE WHEN monthlymaintenance > 100 THEN 'expensive' ELSE 'cheap' END
FROM facilities;

cost = Case(None, [(Facility.monthlymaintenance > 100, 'expensive')], 'cheap')
query = Facility.select(Facility.name, cost.alias('cost'))

Note

See documentation Case for more examples.

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.

SELECT memid, surname, firstname, joindate FROM members
WHERE joindate >= '2012-09-01';

query = (Member
         .select(Member.memid, Member.surname, Member.firstname, Member.joindate)
         .where(Member.joindate >= datetime.date(2012, 9, 1)))

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.

SELECT DISTINCT surname FROM members ORDER BY surname LIMIT 10;

query = (Member
         .select(Member.surname)
         .order_by(Member.surname)
         .limit(10)
         .distinct())

Combining results from multiple queries

You, for some reason, want a combined list of all surnames and all facility names.

SELECT surname FROM members UNION SELECT name FROM facilities;

lhs = Member.select(Member.surname)
rhs = Facility.select(Facility.name)
query = lhs | rhs

Queries can be composed using the following operators:

• | - UNION
• + - UNION ALL
• & - INTERSECT
• - - EXCEPT

Simple aggregation

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

SELECT MAX(join_date) FROM members;

query = Member.select(fn.MAX(Member.joindate))
# To conveniently obtain a single scalar value, use "scalar()":
# max_join_date = query.scalar()

More aggregation

You’d like to get the first and last name of the last member(s) who signed up - not just the date.

SELECT firstname, surname, joindate FROM members
WHERE joindate = (SELECT MAX(joindate) FROM members);

# Use "alias()" to reference the same table multiple times in a query.
MemberAlias = Member.alias()
subq = MemberAlias.select(fn.MAX(MemberAlias.joindate))
query = (Member
         .select(Member.firstname, Member.surname, Member.joindate)
         .where(Member.joindate == subq))