Timestamps and the Extract Function
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.
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"
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 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;
RIGHT) self joins can also be performed, by using the more explicit
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;