Clauses
PostgreSQL Clauses is defined as a set of rules, that make one to understand the concepts of PostgreSQL commands in Database. PostgreSQL Clauses are the most essential and leading integument commands for programming the database that operates based on the queries related with administration. PostgreSQL Clauses can be classified into the following types.
- Where clause
- Order by clause
- Group by clause
- Having clause
- Distant clause
Description
To specify conditions in postgreSQL command use where clause. i.e, Select "*" from the table name will show all the rows from the table and Where clause will specify the condition for the result set in the table.
Syntax
Select * from <table_name>where <condition> ;
Table_name =>Any accurate table.
condition =>condition is a logic to get a specific record.
Examples
By viewing the below example,The concept of where clause can be easily understand.
[sql]SQLDB=# select * from employee5;
emp_id | ename | sal
---------+--------+--------
1001 | mike | 13000
1002 | rambo | 14000
(2 rows)
SQLDB=# select * from employee5 where ename='rambo';
emp_id | ename | sal
--------+-------+-------
1002 | rambo | 14000
(1 row)
[/sql]
The above example tells that,when where clause operation is performed on a column name like rambo, then it automatically displays the entire details of rambo like emp_id and salary.
Description
To arrange data of a table either in ascending order or descending order based on single column use order by clause.i.e, Select "*" from the table name will show all the rows from the table and order by clause is used to arrange the specific column in ascending or descending order.
Syntax
Select * from <table_name> ORDER BY <column_name> DESC;
Table_name=>Any accurate table.
column_name =>The condition that one can perform on a column by using Order by clause.
Examples
By viewing the below example, The concept of Order by clause can be easily understand.
[sql]SQLDB=# select * from employee5;
emp_id | ename | sal
----------+-------+-------
1001 | mike | 14000
1002 | rambo | 15000
(2 rows)
SQLDB=# select * from employee5 order by sal desc;
emp_id | ename | sal
----------+-------+-------
1002 | rambo | 15000
1001 | mike | 14000
(2 rows)[/sql]
The above example tells that, when order by clause operation is performed on a column name like salary, then it automatically displays the entire details of employees in descending order.
Description
To divide the data of a table into groups based on single column use group by clause.
Syntax
Select <column_name>,Group <function1>,Group <function2> from <table_name> Group by <column_name>
Table_name=>Any accurate table.
column_name =>The condition that one can perform on a column by using Group by clause.
Examples
By viewing the below example, The concept of group by clause can be easily understand.
[sql]SQLDB=# select * from employee5;
emp_id | ename | sal | deptno
---------+-------+-------+--------
1001 | mike | 14000 | 10
1002 | rambo | 15000 | 20
1003 | kate | 16000 | 10
1003 | jeo | 16000 | 20
1003 | finn | 16000 | 30
(5 rows)
SQLDB=# select deptno,max(sal) from employee5 group by deptno;
deptno | max(sal)
----------+----------
10 | 16000
20 | 16000
30 | 16000
(3 rows)[/sql]
The above example tells that, when group by clause operation is performed on a column name like deptno and salary, then it automatically displays the entire details of employees based on maximum salary of employee.
Description
To specify a condition with group by clause use having clause.
Syntax
Select <column_name>,group <function1>,group <function2> from <table_name> group by column name having <condition>;
Table_name=>Any accurate table.
condition =>condition is a logic to get a specific record.
Examples
By viewing the below example, The concept of having clause can be easily understand.
[sql]SQLDB=# select * from employee5;
emp_id | ename | sal | deptno
---------+-------+-------+--------
1001 | mike | 14000 | 10
1002 | rambo | 15000 | 20
1003 | kate | 16000 | 10
1003 | jeo | 16000 | 20
1003 | finn | 16000 | 30
(5 rows)
SQLDB=# select deptno,max(sal) from employee5 group by deptno having max(sal)>15000;
deptno | max(sal)
---------+----------
10 | 16000
20 | 16000
30 | 16000
(3 rows)[/sql]
The above example tells that,when having clause operation is performed on a column name like deptno and salary having salary greater that 15000, then it automatically displays the details of employees based on maximum salary.
Description
To eliminate duplicate values in a column use distinct clause.
Syntax
Select DISTINCT <column_ name> from <table_name>;
Table_name=>Any accurate table.
column_name =>The condition that one can perform on a column by using distinct clause.
Examples
By viewing the below example, The concept of distinct clause can be easily understand.
[sql]SQLDB=# select * from employee5;
emp_id | ename | sal | deptno
---------+-------+-------+--------
1001 | mike | 14000 | 20
1002 | rambo | 15000 | 30
1003 | kate | 16000 | 20
1003 | jeo | 16000 | 20
1003 | finn | 17000 | 40
(5 rows)
SQLDB=# select distinct deptno from employee5;
deptno
--------
20
30
40
--------
(3 rows)[/sql]
The above example tells that,when distinct clause operation is performed on a column deptno, then it displays the values of jeo, finn, and kate as they have the maximum salary and automatically remove duplicate values of mike and rambo as they having minimum salary.
Key Points
- PostgreSQL Clauses - Is set of rule,that make one to understand the concepts of PostgreSQL commands in the Database.
- Where clause - Used to specify condition in select statement.
- Order by clause - Utilised to arrange the table column in ascending or descending order.
- Group by clause - Used to divide the data of a table into groups.
- Having clause - Used to put condition in group by clause.
- Distant clause - Used to eliminate duplicate values.