Basics

Statements and Queries

The function of a “SQL statement” is to communicate to the database what you’d like to do with the data. Types of statements include:

• CREATE - Creates a new table in the database.
• DROP TABLE - Removes a table from the database.
• SELECT - Allows you to read data and display it. Select statements are commonly referred to as queries.

There are a few other types of statements related to the creation and removal of data. Most analytical work involves reading and manipulating data (using SELECT) without altering the underlying source.

Query Formatting

SQL is generally not case sensitive, but common formatting convention is to capitalize keywords (IE, SELECT and FROM) while leaving the names of columns and tables lowercase (account). This aids in the legibility of commands, which only becomes more important as the queries become more complex.

Column names should use underscores instead of spaces (web_events, not "web events"). In Postgres, if there is a space in a column or table name, that name needs to be referred to with double quotes around them.

SELECT account
  FROM web_events;

Different SQL environments may or may not require a semicolon at the end of the query. Best practice is to include it whether or not it is required.

SELECT & FROM

Every query contains the SELECT and FROM keywords. The SELECT keyword is always followed by the columns the user wants returned. The FROM keyword is always is always followed by the table from which those columns should be selected.

Unlike many other types of programming languages, the order of SQL statements cannot vary. SELECT is always followed by FROM, and not the other way around, as shown in the following examples. The order of keywords will likewise be critical for the other keywords that will be introduced in this post.

SELECT flight_date,
       origin,
       dest,
       actual_elapsed_time
  FROM tutorial.us_flights

LIMIT

The keyword LIMIT places an upper limit on the number of rows returned by a given query. LIMIT is always the very last keyword included in a query.

The primary usage of this command is to speed up execution of queries that would otherwise take a very long time, due to there being millions of rows. It would be inconvenient to wait several minutes for a query to return especially if the programmer simply wants to quickly get an idea of what the fields contain, for example.

An * (asterisk) is used here to select all available columns. In SQL, * means ‘all.’

SELECT *
  FROM tutorial.city_populations
 LIMIT 3

ORDER BY

The keyword ORDER BY specifies which field the results of the query will be ordered by.

By default, the query returns results from A to Z, lowest to highest, and earliest to latest. This is called “Ascending Order.” In order to reverse this ordering, add the keyword DESC following the column name.

SELECT school,
       student_id,
       sat_writing,
       sat_verbal,
       sat_math
  FROM tutorial.sat_scores
 ORDER BY sat_math DESC

It is also possible to ORDER BY multiple fields. This will result in a query being ordered by the first field, first.

As an example, the following query orders by flight_date, then by actual_elapsed_time from smallest to largest. The latter ordering will be “nested within” the former.

SELECT flight_date,
       origin,
       dest,
       actual_elapsed_time
  FROM tutorial.us_flights
 ORDER BY flight_date,
       actual_elapsed_time

WHERE

A WHERE clause allows you to filter a set of results on the basis of a given criteria. This functionality is similar to Excel’s filter capability.

The following would produce results where all the values in the origin column are equal to ‘LAX.’ In SQL, each row is one datapoint or observation. If a given row has an origin other than ‘LAX,’ it will be excluded in its entirety from the query results.

Whenever using WHERE with non-numeric data, the non-numeric data is placed in single-quotes, as shown below.

SELECT flight_date,
       origin,
       dest,
       actual_elapsed_time
  FROM tutorial.us_flights
 WHERE origin = 'LAX'
 ORDER BY actual_elapsed_time

Comparison operators such as <, >, and the not equal symbol can also be used with WHERE.

SELECT *
  FROM tutorial.city_populations
 WHERE population_estimate_2012 < 500000

SELECT *
  FROM tutorial.city_populations
 WHERE state = 'CA'

Derived Columns

Derived columns are new columns the results of which come from a manipulation of the existing columns in the database.

The following code snippet demonstrates how it is possible to create new columns from the contents of other columns. In this case, the new column is named ‘sat_total’ using the AS keyword.

SELECT school,
       student_id,
       sat_writing,
       sat_verbal,
       sat_math,
       sat_writing + sat_verbal + sat_math AS sat_total
  FROM tutorial.sat_scores
 ORDER BY sat_total DESC

LIKE

LIKE is a keyword used with the WHERE keyword. It enables more flexible searching. The following example returns rows with the word “Life” in the song_name, where “Life” can be followed by any number of characters. This is useful whenever you have lots of similar but slightly different values in a given column.

LIKE requires the use of wildcards. ‘%’ is a wild card that represents a character or any number of characters.

SELECT *
  FROM tutorial.billboard_top_100_year_end
 WHERE song_name LIKE 'Life%'

IN

IN is a keyword that, similar to LIKE, is used in combination with WHERE. The purpose of IN is to allow you to filter data based on several possible values. For example, the following allows you to search for cities in ‘CA,’ ‘TX,’ or ‘NY.’

Any non-numeric data must be placed in single-quotes, as shown, as with comparison operators. Place a comma in between each pair of distinct values.

SELECT *
  FROM tutorial.city_populations
 WHERE state IN ('CA', 'TX', 'NY')

SELECT flight_date,
       origin,
       dest,
       distance,
       actual_elapsed_time
  FROM tutorial.us_flights
 WHERE actual_elapsed_time IN (30, 45)

NOT

NOT provides the inverse results for IN, LIKE, and similar operators.

SELECT *
  FROM tutorial.billboard_top_100_year_end
 WHERE song_name NOT LIKE 'The%'

SELECT *
  FROM tutorial.billboard_top_100_year_end
 WHERE artist NOT IN ('Elvis Presley', 'Nelson Riddle')

AND & BETWEEN

The following example filters for populations less than 500,000 and greater than 450,000. The AND operator allows you to run two complete logical statements; the word ‘population_estimate_2012’ could not be omitted from the second half of the WHERE clause shown below.

SELECT *
  FROM tutorial.city_populations
 WHERE population_estimate_2012 <= 500000
   AND population_estimate_2012 >= 450000

The keyword BETWEEN provides an alternative way of expressing the identical query, above.

SELECT *
  FROM tutorial.city_populations
 WHERE population_estimate_2012 BETWEEN 450000 AND 500000

The commands can be chained, as shown in the examples below.

SELECT school,
       student_id,
       sat_writing,
       sat_verbal,
       sat_math
  FROM tutorial.sat_scores
 WHERE sat_math BETWEEN 700 AND 750
   AND sat_verbal < 300

SELECT *
  FROM tutorial.billboard_top_100_year_end
 WHERE artist = 'Elvis Presley'
   AND year_rank BETWEEN 1 AND 5

OR

OR performs the expected functionality, similar to AND.

SELECT flight_date,
       origin,
       dest,
       distance,
       actual_elapsed_time
  FROM tutorial.us_flights
 WHERE origin = 'LAX' OR origin = 'SFO'
 ORDER BY actual_elapsed_time

AND and OR can also be combined to form more complex queries. Parentheses can be used to group statements as appropriate. See the following code example.

SELECT school,
       student_id,
       sat_writing,
       sat_verbal,
       sat_math
  FROM tutorial.sat_scores
 WHERE sat_math BETWEEN 775 AND 800
    OR sat_verbal BETWEEN 775 AND 800
    OR sat_writing BETWEEN 775 AND 800

HAVING

HAVING is used in conjunction with GROUP BY to filter group rows that do not satisfy a certain condition.

HAVING is analogous to WHERE, except it is used with GROUP BY.

• The HAVING clause sets the condition for groups of rows created by the GROUP BY clause after the GROUP BY clause applies.
• The WHERE clause sets the condition for individual rows before the GROUP BY clause applies.

SELECT column_1,
       aggregate_function(column_2)
  FROM table_name
 GROUP BY column_1
HAVING condition;

SELECT customer_id, SUM(amount)
  FROM payment
 GROUP BY customer_id
HAVING SUM(amount) > 190;

Example using WHERE and HAVING.

SELECT rating,
       AVG(rental_rate)
  FROM film
 WHERE rating IN ('R', 'G', 'PG')
 GROUP BY rating
HAVING AVG(rental_rate) < 3;