Category: Postgresql

BETWEEN The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates. The BETWEEN operator is inclusive: begin and end values are included. Example Select all products with a price between 10 and 15: Run Example » BETWEEN Text Values The BETWEEN operator can also be used on text values. The result returns all records…

IN The IN operator allows you to specify a list of possible values in the WHERE clause. The IN operator is a shorthand for multiple OR conditions. Example Return all customers from ‘Germany’, France’ or ‘UK’: Run Example » NOT IN By using the NOT keyword in front of the IN operator, you return all records that are NOT any of the values in the…

LIKE The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator: Starts with To return records that starts with a specific letter or phrase, add the % at the end of the letter or phrase. Example Return all customers with a name that…

AVG The AVG() function returns the average value of a numeric column. Example Return the average price of all the products in the products table: Note: NULL values are ignored. With 2 Decimals The above example returned the average price of all products, the result was 28.8663636363636364. We can use the ::NUMERIC operator to round the average price to a number with 2…

SUM The SUM() function returns the total sum of a numeric column. The following SQL statement finds the sum of the quantity fields in the order_details table: Example Return the total amount of ordered items: Note: NULL values are ignored. PostgreSQL Exercises Test Yourself With Exercises Exercise: Use the correct function to return the total sum of all values of the field price in…

COUNT The COUNT() function returns the number of rows that matches a specified criterion. If the specified criterion is a column name, the COUNT() function returns the number of columns with that name. Example Return the number of customers from the customers table: Note: NULL values are not counted. By specifying a WHERE clause, you can e.g. return the number of customers that comes…

MIN The MIN() function returns the smallest value of the selected column. Example Return the lowest price in the products table: MAX The MAX() function returns the largest value of the selected column. Example Return the highest price in the products table: Set Column Name When you use MIN() or MAX(), the returned column will be named min or max by default. To give the column a new name, use…

The LIMIT Clause The LIMIT clause is used to limit the maximum number of records to return. Example Return only the 20 first records from the customers table: The OFFSET Clause The OFFSET clause is used to specify where to start selecting the records to return. If you want to return 20 records, but start at number 40, you can use…

Sort Data The ORDER BY keyword is used to sort the result in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword. Example Sort the table by price: Run Example » DESC The ORDER BY keyword sorts the records in ascending order by default. To…

The SELECT DISTINCT Statement The SELECT DISTINCT statement is used to return only distinct (different) values. Inside a table, a column often contains many duplicate values and sometimes you only want to list the different (distinct) values. Example Select only the DISTINCT values from the country column in the customers table: Even though the customers table has 91 records, it only…