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. For a comprehensive list, google “postgresql datetime.” Some examples of these units include:
For a comprehensive list of built-in mathematical operators, google “postgresql math”.
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.
SQL also has various regular expression functions that can be used for more complex text manipulation and parsing.
Subqueries are queries within the results of another query. They enable construction of very complex and powerful queries.
Find the movies with rental rate higher than the average rental rate.
Find the film_ids and titles of movies that were returned between May 29, 2005 and May 30, 2005.
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 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.
Determine which customers have first names that match last names of another customer.
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.
How can you retrieve all the information from the cd.facilities table?
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?
How can you produce a list of facilities that charge a fee to members?
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.
How can you produce a list of all facilities with the word ‘Tennis’ in their name?
How can you retrieve the details of facilities with ID 1 and 5? Try to do it without using the OR operator.
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.
How can you produce an ordered list of the first 10 surnames in the members table? The list must not contain duplicates.
You’d like to get the signup date of your last member. How can you retrieve this information?
Produce a count of the number of facilities that have a cost to guests of 10 or more.
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.
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.
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.
How can you produce a list of the start times for bookings by members named ‘David Farrell’?