Advanced SQL Commands

Timestamps and the Extract Function

The EXTRACT function allows users to extract parts from a timestamp column. The syntax is as follows (lower-case is conventional):

extract(unit from column)

There are many different types of units that can be extracted from a timestamp. For a comprehensive list, google “postgresql datetime.” Some examples of these units include:

  • day, dow, doy, epoch, hour, microseconds, millennium, milliseconds, minute, month, quarter, second, week, and year
SELECT payment_date,
       extract(day from payment_date) AS Day
  FROM payment;
SELECT SUM(amount) "Total Sales",
       extract(month from payment_date) AS month
  FROM payment
 GROUP BY month
 ORDER BY "Total Sales"

Mathematical Functions

For a comprehensive list of built-in mathematical operators, google “postgresql math”.

SELECT customer_id + rental_id new_id
  FROM payment;
SELECT round(AVG(amount),2)
  FROM payment;

String Functions and Operators

For a comprehensive list of built-in string functions google “postgresql string functions”.

Two pipe characters || are short-hand for concatenate. Other examples follow.

SELECT first_name,
       last_name,
       first_name || ' ' || last_name full_name,
       char_length(first_name),
       upper(first_name),
       lower(first_name)
  FROM customer;

SQL also has various regular expression functions that can be used for more complex text manipulation and parsing.

Subquery

Subqueries are queries within the results of another query. They enable construction of very complex and powerful queries.

Subquery Examples

Find the movies with rental rate higher than the average rental rate.

SELECT film_id,
       title,
       rental_rate
  FROM film
 WHERE rental_rate > (SELECT AVG(rental_rate) FROM film);

Find the film_ids and titles of movies that were returned between May 29, 2005 and May 30, 2005.

SELECT film_id,
       title
  FROM film
 WHERE film_id IN

(SELECT inventory.film_id
  FROM rental r
  JOIN inventory i
    ON i.inventory_id = r.inventory_id
 WHERE return_date BETWEEN '2005-05-29' AND '2005-05-30');

Multiple nested subqueries can be difficult to interpret, so developing personal conventions for using whitespace and tabulation become important. It is critical to think about how to organize SQL queries as they become more complex.

Self-Join

A SELF JOIN is when a user combines rows with other rows in the same table. Aliases must be used to help SQL distinguish the left table from the right.

An example might be determining which actors share the same first name as another actor. Note that the words SELF and JOIN do not need to be used when performing a self join, although they can be, for clarity.

SELECT a1.first_name,
       a1.last_name,
       a2.first_name,
       a2.last_name
  FROM actor a1,
       actor a2
 WHERE a1.first_name = a2.first_name
   AND a2.first_name='Julia';

Determine which customers have first names that match last names of another customer.

SELECT a.first_name,
       a.last_name,
       b.first_name,
       b.last_name
  FROM customer a,
       customer b
 WHERE a.first_name = b.last_name;

LEFT (and RIGHT) self joins can also be performed, by using the more explicit JOIN and ON keywords.

A final example often used in SQL interviews. Given the following table, link the name of the employee to the name of the manager in a single results table.

employee_id name manager_id
1 Amie 2
2 Bennie -null-
3 Charlie 1
4 Dilbert 2
5 Ella 1
SELECT e.name e_name,
       m.name m_name
  FROM employee e
  LEFT JOIN employee m
    ON e.manager_id = m.employee_id;
e_name m_name
Amie Bennie
Bennie -null-
Charlie Amie
Dilbert Bennie
Ella Amie

Examples

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

SELECT *
  FROM cd.facilities;

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?

SELECT name,
       membercost
  FROM cd.facilities;

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

SELECT *
  FROM cd.facilities
 WHERE membercost > 0;

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.

SELECT facid,
       name,
       membercost,
       monthlymaintenance
  FROM cd.facilities
 WHERE membercost > 0
   AND membercost < monthlymaintenance / 50.0;

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

SELECT name
  FROM cd.facilities
 WHERE name LIKE '%Tennis%';

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

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

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 cd.members
 WHERE joindate > '2012-09-01';

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 cd.members
 ORDER BY surname
 LIMIT 10;

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

SELECT *
  FROM cd.members
 ORDER BY joindate DESC
 LIMIT 1;

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

SELECT COUNT(facid)
  FROM cd.facilities
 WHERE guestcost >= 10;

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.

SELECT facid,
       SUM(slots) "Total Slots"
  FROM cd.bookings
 WHERE starttime >= '2012-09-01'
   AND starttime < '2012-10-01'
 GROUP BY facid
 ORDER BY "Total Slots";

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

SELECT facid,
       SUM(slots) "Total Slots"
  FROM cd.bookings
 GROUP BY facid
HAVING SUM(slots) > 1000
 ORDER BY facid;

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.

SELECT b.starttime "Start Time",
       f.name "Facility Name"
  FROM cd.bookings b
  JOIN cd.facilities f
    ON b.facid = f.facid
 WHERE date_trunc('day', b.starttime) = '2012-09-21'
   AND f.name ILIKE '%tennis%court%'
 ORDER BY "Start Time";

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

SELECT b.starttime "Start Time",
       m.firstname,
	   m.surname
  FROM cd.bookings b
  JOIN cd.members m
    ON b.memid = m.memid
 WHERE m.firstname = 'David'
   AND m.surname = 'Farrell';

Content for this note is taken from information in “The Complete SQL Bootcamp” course on Udemy. I highly recommend it.