# 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;
payment_date day
2007-02-15 22:25:46 15
SELECT SUM(amount) "Total Sales",
extract(month from payment_date) AS month
FROM payment
GROUP BY month
ORDER BY "Total Sales"
Total Sales month
28559.46 4

## 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;
first_name last_name full_name char_length upper lower
Jared Ely Jared Ely 5 JARED jared

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.

employee_id name manager_id
1 Amie 2
2 Bennie
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
Charlie Amie
Dilbert Bennie
Ella Amie