In my previous article I have explained about different PostgreSQL interview questions. In this article I would like to give you information about different PostgreSQL Queries Examples. In this article I would like to give different Postgres Queries with real life industry examples. The syntax is same as different SQL queries but it has some change while fetching data from database.
Select Statement Postgres queries :
In this section I would like to start with Select Statements in PostgreSQL. There are multiple Postgres queries but the most important queries to fetch the data from database. To fetch the data from database we require select statement with its multiple options. I would like to explain different select statement in this section.
Syntax of Select Statement :
If user wants to fetch specific column data from the table,
SELECT column_No1, column_No2…….column_No’N’ FROM table_name;
If user wants to fetch all column data from the table,
Select * from Table_Name;
Real life Industry examples :
Example 1 : I want to fetch all records from Employee table.
Query :
Select * from Employee;
Example 2 : I want to fetch Employee_No and Employee_name data from Employee table.
Query :
Select Employee_No,Employee_Name From Employee;
Example 3 : I want to fetch Employee records whos name is ‘Amit’.
Query :
Select * from Employees where Upper(name)=upper( ‘Amit’);
Here everyone have question in Mind why upper statement is used to execute postgress queries ; user need to fetch employee records to find out the exact name ‘Amit’ which is not case sensitive. We need to find out all employees whos name is ‘Amit’. To remove case sensitivity we need to use upper function.
Select Statement with Limit Clause :
There are multiple records in each of the table. If we are working on PostgreSQL there is one functionality provided by Postgres queries to fetch the specific number of records from the table.
Example 4 : I want to fetch 10 records from the table named Employee.
Query :
Select * from Employee limit 10;
The above query statement will give you first 10 records. Limit statement works like rownum in oracle. But you need to use rownum with operator.
Select Statement with Null or Not Null Operator:
Example 5 : I want to fetch Employees to whom department is allocated.
Here I would like to analyze this statement with not null statement.
Select * from Employee where department is not null;
Example 6 : Between ..And Operator
I want to fetch Employees whos salary between 10k to 20K.
There are multiple ways to fetch the data from the query but I would like to give you way of fetching data through between. .And operator,
Query :
Select * from Employee where salary between 10000 and 20000;
Example 7 : I want to fetch employees whose department is ‘IT’ and salary is greater than 10000.
Query :
Select * from Employee where Department_name=’IT’ and salary > 10000;
Example 8 : PostgreSQL Order by query
I want to fetch the Employees with minimum salary to maximum salary.
Query :
Select * from Employee order by salary desc;
Example 9 : PostgreSQL Fetch Queries
I want to Fetch first record from the Employee table whose department is ‘IT’.
Query :
Select * from Employee where Department=’IT’ Fetch first row only;
Example 10 : I want to fetch 5 records ascending by salary.
Query :
Select * from Employee Order by salary desc FETCH FIRST 5 ROW ONLY;
Example 11 : I want to fetch the records of employee where name is ‘Amit’, ‘Rahul’,’Bikesh’.
Query :
Select * from Employee where Emp_Name in(‘Amit’,’Rahul’,’Bikesh’);
Example 12 : Union of two tables.
I would like to combine two tables having same columns. What is best possible way to combine that.(Employee1 and Employee2 are table names)
Select * from Employee1;
Union
Select * from Employee2;
The above query will combine the data of Employee1 and Employee2.
Example 13 : Example of Except Operator
The Postgress SQL has another operator named Except operator where The operator returns distinct rows from the Left or First query that are not in the output of the right or second query.
I want to find out the data of Employees whose department is not IT. Department name column is in Department table.
Query :
Select * from Employees
Except
Select department_name from Department where Department_name=’IT’;
Example14 : Examples of Cube
The cube is another fundamental part of PostgreSQL where it is actually the sub clause of group by function. It allows you to generate multiple grouping sets.
Kindly check following table to check and understand example more clearly.
We will use the Policy table created,
Policy Table :
| Companyname | Policy Name | Quantity |
| LIC | Premium | 100 |
| LIC | Basic | 200 |
| UDDI | Premium | 100 |
| UDDI | Basic | 300 |
Check following Query for use of Cube,
Select
Companyname,Policy_Name,Sum(Quantity) from Policy
Group By CUBE(Companyname,Policy_Name)
Order by Companyname,Policy_Name;
Output of the query :
| Companyname | PolicyName | Quantity |
| LIC | Basic | 200 |
| LIC | Premium | 100 |
| LIC | NULL | 300 |
| UDDI | Basic | 300 |
| UDDI | Premium | 100 |
| UDDI | NULL | 400 |
| NULL | Basic | 500 |
| NULL | Premium | 200 |
| NULL | NULL | 700 |
Example 15 : PostgreSQL Subqueries
In this section I would like to give you different subqueries with using In operator,Exists Operator or any other operators.
Query 1 : I want to Fetch the records from Employee table where department is IT from department table.
Select * from Employee Where
Department In (Select Departement_name from Department where Departement_name=’IT’);
Query 2 : I want to fetch all Employees whos department is not assigned from department table.
Select * from Employee Where
Departement Exist (Select department_name from Departement where department_name is NULL);
Query 3 : I want to fetch all employees where department name is IT or BI.
Select * from Employee Where
Deparment =Any( select department_name from Department where department_name in(‘IT’,’BI’);
These are some most important Postgres queries which are used in real time industries. I hope you like this article on Postgres queries with examples. If you like this Postgres queries article or if you have any suggestions with the same kindly comment in comments section.