Author: admin

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…

Select Data To retrieve data from a data base, we use the SELECT statement. Specify Columns By specifying the column names, we can choose which columns to select: Example Run Example » Return ALL Columns Specify a * instead of the column names to select all columns: Example Run Example » PostgreSQL Exercises Test Yourself With Exercises Exercise: Write the correct…

Operators in the WHERE clause We can operate with different operators in the WHERE clause: = Equal to < Less than > Greater than <= Less than or equal to >= Greater than or equal to <> Not equal to != Not equal to LIKE Check if a value matches a pattern (case sensitive) ILIKE Check if…