SQL Statement Fundamentals

SELECT

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.

SELECT column1,
       column2
  FROM table;

Separate column names with commas Asterisk (*) is shorthand for all columns Indicate table name after FROM

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.

SELECT * FROM actor;
Result of first query

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.

Challenge: SELECT

Use a SELECT statement to grab the first and last names of every customer and their email address.

SELECT first_name, last_name, email
  FROM customer;

SELECT DISTINCT

DISTINCT keyword can be used to return only distinct (different) values.

SELECT DISTINCT column_1,
       column_2
  FROM table_name;

Challenge: SELECT DISTINCT

Use a SELECT DISTINCT statement to get the distinct rating types our films can have in our database.

SELECT DISTINCT rating
  FROM film;

SELECT WHERE

The WHERE statement allows a user to limit the results to just particular rows from a table.

SELECT column_1, column_2, ..., column_n
  FROM table_name
 WHERE conditions;

There are various standard operators to construct the conditions:

Operator Description
= Equal
> Greater Than
< Less Than
>= Greater Than or Equal To
<= Less Than or Equal To
<> or != Not Equal To
AND Logical AND
OR Logical OR
SELECT first_name, last_name, email
  FROM customer
 WHERE first_name = 'Jamie'
   AND last_name = 'Rice';

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 SELECT clause.

The order of columns listed after SELECT should be whichever order in which the user wants the results to be returned.

SELECT *
  FROM payment
 WHERE amount = 7.99;

Challenge: SELECT WHERE

What’s the email for the customer with the name, Nancy Thomas?

SELECT email
  FROM customer
 WHERE first_name = 'Nancy'
   AND last_name = 'Thomas';

What is the description for the movie ‘Outlaw Hanky’?

SELECT description
  FROM film
 WHERE title = 'Outlaw Hanky';

What is the phone number for the customer who lives at ‘259 Ipoh Drive’?

SELECT phone
  FROM address
 WHERE address = '259 Ipoh Drive';

COUNT

COUNT allows users to count the rows returned by a SELECT query.

SELECT COUNT(*)
  FROM table

COUNT can also be used with particular columns, only. COUNT does not count null values in the column.

SELECT COUNT(column)
  FROM table

COUNT can also be used with DISTINCT. This returns the number of distinct values in a particular column.

SELECT COUNT(DISTINCT column)
  FROM table

LIMIT

LIMIT allows you to limit the number of rows returned from a query.

SELECT *
  FROM customer
 LIMIT 5;

ORDER BY

Can use ASC as well as DESC. ORDER BY uses ASC by default.

Challenge: ORDER BY

Get the customer ID numbers for the top 10 highest payment amounts.

SELECT customer_id
  FROM payment
 ORDER BY amount DESC
 LIMIT 10;

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.

SELECT film_id, title
  FROM film
 ORDER BY film_id ASC
 LIMIT 5;

BETWEEN

BETWEEN is used to match a value against a range of values.

value BETWEEN low AND high

BETWEEN is identical to the following.

value >= low AND value <= high

NOT can be combined with BETWEEN.

value NOT BETWEEN low AND high

NOT BETWEEN is logically identical to the following.

value < low OR value > high

BETWEEN can be used with dates

SELECT amount
  FROM payment
 WHERE payment_date BETWEEN '2007-02-07' AND '2008-02-15';

IN

The IN operator is used with the WHERE clause to check if a value matches any value in a list of values.

value IN (value1, value2, ...)

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.

value IN (SELECT value FROM tbl_name)

Also can use NOT in combination with IN, as follows.

value NOT IN (value1, value2, ...)

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:

SELECT customer_id, rental_id, return_date
FROM rental
WHERE customer_id IN (7,13,10);

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.

WHERE customer_id = 7
   OR customer_id = 13
   OR customer_id = 10

LIKE

SQL allows users to use pattern matching to find data LIKE some other data.

SELECT first_name, last_name
  FROM customer
 WHERE first_name LIKE 'Jen%';

% in the above LIKE clause can stand for any characters after the initial JEN string. % is known as a pattern or, more commonly, as a wildcard character.

Wildcard Character Matches
% Any Sequence of Characters
_ Any Single Character

Other examples of patterns: %er%, _en, and %ryl.

As with the other operators, above, NOT can be used with LIKE.

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.

SELECT first_name, last_name
  FROM customer
 WHERE first_name ILIKE 'BAR%';

General Challenge 1

How many payment transactions were greater than $5.00?

SELECT COUNT(amount)
  FROM payment
 WHERE amount > 5;

How many actors have a first name that starts with the letter P?

SELECT COUNT(first_name)
  FROM actor
 WHERE first_name LIKE 'P%';

How many unique districts are our customers from?

SELECT COUNT(DISTINCT(district))
  FROM address;

Retrieve the list of names for those distinct districts from the previous question.

SELECT DISTINCT(district)
  FROM address;

How many films have a rating of R and a replacement cost between $5 and $15?

SELECT COUNT(title)
  FROM film
 WHERE replacement_cost BETWEEN 5 AND 15
   AND rating = 'R';

How many films have the word ‘Truman’ somewhere in the title?

SELECT COUNT(title)
  FROM film
 WHERE title LIKE '%Truman%';

Content for this note is taken from information in “The Complete SQL Bootcamp” course on Udemy. I highly recommend it.