Section5. SQL Statement Fundamentals

https://www.udemy.com/the-complete-sql-bootcamp

 

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.

Answer

 

 

SELECT DISTINCT

Syntax Example

 

Challenge : SELECT DISTINCT

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.

Answer

 

 

SELECT WHERE

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’?

ANSWER

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”?
Answer

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’?
Answer

 

 

 COUNT

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

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.

 

ORDER BY

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

CHALLENGE: ORDER BY
Get the customer ID numbers for the top 10 highest payment amounts
Answer

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

 

BETWEEN

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.

example

 

IN

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

subquery

example

 

LIKE

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.

example

 

General Challenge 1

How many payment transactions were greater than $5.00?
Solution

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

How many unique districts are our customers from?
Solution

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