PostgreSQL SELECT Statement: Clauses and Examples, Assignments of Data Mining

Download PostgreSQL SELECT Statement: Clauses and Examples and more Assignments Data Mining in PDF only on Docsity!

Udemy Course Notes

Complete SQL

Bootcamp

Table of Contents

SELECT

SELECT DISTINCT

WHERE

PostgreSQL WHERE examples

LIMIT

IN Operator

PostgreSQL IN operator examples

NOT IN Operator

ORDER BY

PostgreSQL ORDER BY examples

BETWEEN

PostgreSQL BETWEEN operator examples

LIKE

GROUP BY

PostgreSQL GROUP BY with SUM function example

HAVING

Example

JOINS

SUBQUERY

CREATE TABLE and Constraints

PostgreSQL column constraints

PostgreSQL table constraints

SELECT

One of the most common tasks, when you work with PostgreSQL, is to query data from tables by using the SELECT statement. The SELECT statement is one of the most complex statements in PostgreSQL. It has many clauses that you can combine to form a powerful query.

Because of its complexity, we divide the PostgreSQL SELECT statement tutorial into many short tutorials so that you can learn each clause of the SELECT statement easier. The following are the clauses that appear in the SELECT statement:

● Select distinct rows by using DISTINCT operator. ● Filter rows by using WHERE clause. ● Sort rows by using the ORDER BY clause. ● Select rows based on various operators such as BETWEEN, IN and LIKE. ● Group rows into groups by using GROUP BY clause ● Apply condition for groups by using HAVING clause. ● Join to another table by using INNER JOIN, LEFT JOIN, RIGHT JOIN clauses.

Let’s start with a basic form of the SELECT statement to query data from a table. The following illustrates the syntax of the SELECT statement:

1 2

SELECT column_ 1 , column_ 2 , FROM table_name

Let’s examine the SELECT statement in more detail:

● First, you specify a list of columns in the table from which you want to query data in the SELECT clause. You use a comma between each column in case you want to query data from multiple columns. If you want to query data from all column, you can use an asterisk (*) as the shorthand for all columns. ● Second, you indicate the table name after the FROM keyword

Notice that SQL language is case insensitive. It means if you use SELECT or select the effect is the same. By convention, we will use SQL keywords in uppercase to make the code easier to read and stand out clearly.

always use the ORDER BY clause with the DISTINCT ON(expression) to make the result obvious.

Notice that the DISTINCT ON expression must match the leftmost expression in the ORDER BY clause.

WHERE

The syntax of the PostgreSQL WHERE clause is as follows:

1 2 3

SELECT column_ 1 , column_2 … column_n FROM table_name WHERE conditions;

The WHERE clause appears right after the FROM clause of the SELECT statement. The conditions are used to filter the rows returned from the SELECT statement. PostgreSQL provides you with various standard operators to construct the conditions.

The following table illustrates the standard comparison operators.

OPERATOR DESCRIPTION

= Equal

> Greater than

< Less than

>= Greater than or equal

WHERE first_name = 'Jamie' AND last_name = 'Rice';

If you want to know who paid the rental with amount is either less than 1USD or greater than 8USD, you can use the following query with OR operator:

1 2 3

SELECT customer_id,amount,payment_date FROM payment WHERE amount <= 1 OR amount >= 8;

LIMIT

PostgreSQL LIMIT is used in the SELECT statement to get a subset of rows returned by the query. The following is the common syntax of the LIMIT clause:

1 2 3

SELECT *

FROM TABLE

LIMIT n;

PostgreSQL returns n number of rows generated by the query. If n is zero or NULL, it produces the result that is same as omitting the LIMIT clause.

In case you want to skip a number of rows before returning n rows, you use OFFSET clause followed by the LIMIT clause as follows:

1 2

SELECT * FROM table LIMIT n OFFSET m;

PostgreSQL first skips m rows before returning n rows generated by the query. If m is zero, PostgreSQL will behave like without the OFFSET clause.

Because the order of the rows in the database table is unknown and unpredictable, when you use the LIMIT clause, you should always use the ORDER BY clause to control the order of rows. If you don’t do so, you will get an unpredictable result.

4 ORDER BY return_date DESC;

NOT IN Operator

You can combine the IN operator with the NOT operator to select rows whose values do not match the values in the list. The following statement selects rentals of customers whose customer id is not 1 or 2.

1 2 3

SELECT customer_id, rental_id, return_date FROM rental WHERE customer_id NOT IN ( 1 , 2);

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 by different criteria.

The following illustrates the syntax of the ORDER BY clause:

1 2 3

SELECT column_ 1 ,column_ 2 FROM tbl_name ORDER BY column_1 ASC, column_2 DESC;

Let’s examine the syntax of the ORDER BY clause in more detail:

● Specify the column that you want to sort in the ORDER BY clause. If you sort the result set by multiple columns, use a comma to separate between two columns. ● Use ASC to sort the result set in ascending order and DESC to sort the result set in descending order. If you leave it blank, the ORDER BY clause will use ASC by default.

BETWEEN

We use the BETWEEN operator to match a value against a range of values. The following illustrates the syntax of the BETWEEN operator:

(^1) value BETWEEN low AND high;

If the value is greater than or equal to the low value and less than or equal to the high value, the expression returns true, or vice versa.

We can rewrite the BETWEEN operator by using the greater than or equal ( >=) or less than or equal (<=) operators as the following statement:

(^1) value >= low and value <= high

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

1 value NOT BETWEEN low AND high;

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

1 value < low OR value > high

We often use the BETWEEN operator in the WHERE clause of a SELECT, INSERT, UPDATE or DELETE statement.

PostgreSQL BETWEEN operator examples Let’s take a look at the payment table in the sample database.

The following query selects any payment whose amount is between 8 and 9:

1 2 3

SELECT customer_id, payment_id,amount FROM payment WHERE amount BETWEEN 8 AND 9;

You construct a pattern by combining a string with wildcard characters and use the LIKE or NOT LIKE operator to find the matches. PostgreSQL provides two wildcard characters:

● Percent ( %) for matching any sequence of characters. ● Underscore ( _) for matching any single character.

GROUP BY

The GROUP BY clause divides the rows returned from the SELECT statement into groups. For each group, you can apply an aggregate function e.g., to calculate the sum of items or count the number of items in the groups.

The following statement illustrates the syntax of the GROUP BY clause:

1 2 3

SELECT column_ 1 , aggregate_function(column_ 2 ) FROM tbl_name GROUP BY column_ 1 ;

The GROUP BY clause must appear right after the FROM or WHERE clause. Followed by the GROUP BY clause is one column or a list of comma separated columns. You can also put an expression in the GROUP BY clause.

PostgreSQL GROUP BY with SUM function example The GROUP BY clause is useful when it is used in conjunction with an aggregate function. For example, to get how much a customer has been paid, you use the GROUP BY clause to divide the payments table into groups; for each group, you calculate the total amounts of money by using the SUM function as the following query: