Section5. SQL Statement Fundamentals


SELECT Statement



Challenge : SELECT Task

Situation : we want to send out a promotional email to our existing customers!
Challenge : Use a SELECT statement to grab the first and last names of every cutomer and their email address.





Syntax Example



Situation : We want to know the types of ratings movies can get in the United States (e.g. PG, PG-13, R, etc…) and which ones we have in our database.
Challenge : Use a SELECT DISTINCT statement to get the distinct rating types our films can have in our database.





Syntax Example

The conditions are used to filter the rows returned from the SELECT statement.

Challenge#1 : SELECT WHERE
A customer forget their wallet at our store! We need to track down their email to inform them.
What’s the email for the customer with the name ‘Nancy Thomas’?


Challenge#2 : SELECT WHERE
A customer wants to know what the movie “Outlaw Hanky” is about. Could you give them the description for the movie “Outlaw Hanky”?

Challenge#3 : SELECT WHERE
A customer is late on their movie return. I know their address is ‘259 ipoh Drive’. I want to call them to let them know! Can you get me the phone number for the person who lives at ‘259 ipoh Drive’?




The COUNT function returns the number of input rows that match a specific condition of a query.

The COUNT(*) function returns the number of rows returned by a SELECT clause.
When you apply the COUNT(*) to the entire table, PostgreSQL scans table sequentially.
You can also specify a specific column count for readability.

Similar to the COUNT(*) function, the COUNT(column) function returns the number of rows returned by a SELECT clause.
However, it does not consider NULL values in the column.
We can use COUNT with DISTINCT, for example:



LIMIT allows you to limit the number of rows you get back after a query.
Useful when wanting to get all the columns but not all rows.
Goes at the end of a query.



When you query data from a table, PostgreSQL returns the rows in the order that they were inserted into the table.
In order to sort the result set, you use the ORDER BY clause in the SELECT statement.
The ORDER BY clause allows you to sort the rows returned from the SELECT statement in ascending or descending order based on criteria specified.

If you leave it blank, the ORDER BY clause will use ASC by default.

Ordering by multiple columns

PostgreSQL allows you to query like this at the buttom, other engines such as MYSQL or ORACLE SQL do not allow this.

However, this should be ordered like this

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

GET the titles of the movies with film ids 1-5



we use the BETWEEN  operator to match a value against a range of values.

If we want to check if a value is out of a range, we use NOT BETWEEN operator as follows.

The following expression is equivalent to the expression that uses the NOT BETWEEN operator.




You use the IN operator with the WHERE clause to check if a value mathces any value in a list of values.





You construct a pattern by combining a string with wildcard characters and use the LIKE and NOT LIKE operator to find the matches.
– Percent(%) for matching any sequence of characters.
– Underscore(_) for matching any single character.



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?

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?


댓글 남기기

Close Menu