Clauses and filters:
Like clause:
|
1 2 3 4 5 6 7 |
college=# select * from report where temp_hi:: text like '4%'; place | temp_lo | temp_hi | date | humidity | precp ----------------------------------------------------------- VZG | 32 | 43 | 2023-05-10 | 21 | HYD | 32 | 43 | 2020-07-19 | 24 | BZA | 32 | 43 | 2023-05-09 | 23 | (3 rows) |
LIMIT Clause:
The LIMIT clause is used to restrict the number of rows returned in the result set. It is useful when you want to retrieve only a certain number of rows.
|
1 2 3 4 5 6 7 8 |
college=# select * from report LIMIT 4; place | temp_lo | temp_hi | date | humidity | precp ----------------------------------------------------------- VZG | 32 | 43 | 2023-05-10 | 21 | HYD | 32 | 43 | 2020-07-19 | 24 | BZA | 32 | 43 | 2023-05-09 | 23 | Delhi | 23 | 34 | 2023-05-15 | 24 | (4 rows) |
OFFSET Clause:
It is used in conjunction with the LIMIT clause to skip a specified number of rows before starting to return the result set. It is useful for Implementing pagination.
|
1 2 3 4 5 6 |
college=# SELECT * FROM report LIMIT 2 OFFSET 2; place | temp_lo | temp_hi | date | humidity | precp ----------------------------------------------------------- BZA | 32 | 43 | 2023-05-09 | 23 | Delhi | 23 | 34 | 2023-05-15 | 24 | (2 rows) |
BETWEEN Clause:
The BETWEEN clause is used to filter rows based on a range of values. It allows you to specify a range by providing a lower and upper boundary, and rows within that range are Included in the result set.
|
1 2 3 4 5 6 7 8 |
college=# SELECT * FROM report ORDER BY DATE DESC; place | temp_lo | temp_hi | date | humidity | precp ----------------------------------------------------------- HYD | 32 | 43 | 2020-07-19 | 24 | BZA | 32 | 43 | 2023-05-09 | 23 | Delhi | 23 | 34 | 2023-05-15 | 24 | Pune | 23 | 34 | 2023-05-15 | 24 | (4 rows) |
|
1 2 3 4 5 |
college=# SELECT * FROM report WHERE humidity NOT BETWEEN 22 AND 24; place | temp_lo | temp_hi | date | humidity | precp -------+---------+---------+------------+----------+------- VZG | 32 | 43 | 2023-05-10 | 21 | (1 row) |
DISTINCT clause:
The DISTINCT clause is used to retrieve unique values from a column or a combination of columns. It eliminates duplicate values and returns only distinct values in the result set.
|
1 2 3 4 5 6 7 |
college=# SELECT DISTINCT humidity FROM report; humidity ------------------------------------ 21 24 23 (3 rows) |
ORDER BY Clause:
The ORDER BY clause is used to sort the result set based on one or more columns in ascending (default) or descending order.
|
1 2 3 4 5 6 7 8 9 10 |
college=# SELECT * FROM report WHERE humidity BETWEEN 22 AND 24; place | temp_lo | temp_hi | date | humidity | precp ----------------------------------------------------------- Delhi | 23 | 34 | 2023-05-15 | 24 | Pune | 23 | 34 | 2023-05-15 | 24 | VZA | 32 | 43 | 2023-05-10 | 21 | HYD | 32 | 43 | 2020-07-19 | 24 | BZA | 32 | 43 | 2023-05-09 | 23 | (5 rows) |
GROUP BY Clause:
The GROUP BY clause is used to group rows based on one or more columns. It is often used in conjunction with aggregate functions (e.g., SUM, COUNT) to perform calculations on groups of data.
|
1 2 3 4 5 6 7 |
college=# SELECT humidity, COUNT(*) AS count FROM report college-# GROUP BY humidity; humidity | count ------------------------ 21 | 1 24 | 3 23 | 1 (3 rows) |
Having Clause:
The HAVING clause is used to filter groups in the result set based on a condition. It is similar to the WHERE clause but is applied after the GROUP BY clause.
|
1 2 3 4 5 6 7 |
college=# SELECT humidity, COUNT(*) AS count FROM report college-# GROUP BY humidity college-# HAVING humidity>21; humidity | count ------------------------ 23 | 1 24 | 3 (2 rows) |
UNION Clause:
The UNION clause is used to combine the result sets of two or more SELECT statements into a single result set. It merges rows from different SELECT queries, eliminating duplicates.
|
1 2 3 4 5 6 7 8 9 |
postgres=# select * from register UNION postgres-# select * from register1; name | year ------------------------------ James | 1974 Leo | 2023 Mick | 2023 Martin | 1972 Lucifer | 1957 (5 rows) |
UNION ALL Clause:
It is similar to the UNION clause, but unlike UNION, it does not eliminate duplicate rows from the result set.
|
1 2 3 4 5 6 7 8 9 10 |
postgres=# select * from register UNION ALL postgres-# select * from register1; name | year ------------------------------ James | 1974 Leo | 2023 Mick | 2023 Martin | 1972 Lucifer | 1957 Lucifer | 1957 (6 rows) |
Author : Prudhvi Teja |
LinkedIn : http://linkedin.com/in/prudhvi-teja-nagabhyru-715052224
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates
KTExperts is always active on social media platforms.
Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Note: Please test scripts in Non Prod before trying in Production.
Loading...
