Content of this section can be applied to any major type of SQL database (such as Oracle, MySQL).
The terms “Select Statement” and “Select Clause” are interchangeable, for purposes of this course.
Separate column names with commas
*) is shorthand for all columns
Indicate table name after
SQL is not case sensitive. Convention for this course is to make keywords uppercase to make code easier to read.
Using asterisk in
SELECT statements is generally not good practice. This is because it may be a very data-intensive command, resulting in the application running slowly.
The top row in the header row of the foregoing image is the column name (“actor_id”). The second row is the datatype (“integer”).
To start a new SQL query in pgAdmin 4, select Tools dropdown menu, then Query Tool.
SELECT statement to grab the first and last names of every customer and their email address.
DISTINCT keyword can be used to return only distinct (different) values.
Challenge: SELECT DISTINCT
SELECT DISTINCT statement to get the distinct rating types our films can have in our database.
WHERE statement allows a user to limit the results to just particular rows from a table.
There are various standard operators to construct the conditions:
|>=||Greater Than or Equal To|
|<=||Less Than or Equal To|
|<> or !=||Not Equal To|
Single quotes are required for comparisons with character varying strings.
Note that the column(s) utilized in the
WHERE clause do not need to be included in the
The order of columns listed after
SELECT should be whichever order in which the user wants the results to be returned.
Challenge: SELECT WHERE
What’s the email for the customer with the name, Nancy Thomas?
What is the description for the movie ‘Outlaw Hanky’?
What is the phone number for the customer who lives at ‘259 Ipoh Drive’?
COUNT allows users to count the rows returned by a
COUNT can also be used with particular columns, only.
COUNT does not count null values in the column.
COUNT can also be used with
DISTINCT. This returns the number of distinct values in a particular column.
LIMIT allows you to limit the number of rows returned from a query.
ASC as well as
ORDER BY uses
ASC by default.
Challenge: ORDER BY
Get the customer ID numbers for the top 10 highest payment amounts.
It is good practice to include the column name used in the
ORDER BY so that other SQL engines like Oracle SQL or mySQL can use the same queries. This is not required in PostgreSQL, however.
Get the titles of the movies for film IDs 1-5.
BETWEEN is used to match a value against a range of values.
BETWEEN is identical to the following.
NOT can be combined with
NOT BETWEEN is logically identical to the following.
BETWEEN can be used with dates
IN operator is used with the
WHERE clause to check if a value matches any value in a list of values.
It is also possible to make the list of values contained in within the
IN clause the results of another
SELECT statement, as shown below.
Also can use
NOT in combination with
IN, as follows.
BETWEEN is best used when the
WHERE clause is comparing to a contiguous list of values.
IN allows the set of parameters to be a list of discrete items, as shown below:
Note that the
WHERE clause in the previous code block is logically equivalent to the following. PostgreSQL will execute the
IN statement version much quicker than the following, however.
SQL allows users to use pattern matching to find data
LIKE some other data.
% in the above
LIKE clause can stand for any characters after the initial
% is known as a pattern or, more commonly, as a wildcard character.
|%||Any Sequence of Characters|
|_||Any Single Character|
Other examples of patterns:
As with the other operators, above,
NOT can be used with
LIKE is normally case-sensitive. PostgreSQL also provides the
ILIKE operator, that is exactly like the
LIKE operator, except it value matches without case-sensitivity.
General Challenge 1
How many payment transactions were greater than $5.00?
How many actors have a first name that starts with the letter P?
How many unique districts are our customers from?
Retrieve the list of names for those distinct districts from the previous question.
How many films have a rating of R and a replacement cost between $5 and $15?
How many films have the word ‘Truman’ somewhere in the title?