Useful Techniques

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.

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

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.

SELECT e.name e_name,
       m.name m_name
  FROM employee e
  LEFT JOIN employee m
    ON e.manager_id = m.employee_id;