Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
SELECT Statement
2.1.
Syntax
2.1.1.
Example
2.2.
Syntax
3.
WHERE Clause
3.1.
Syntax
3.1.1.
Example of WHERE clause using Select
3.1.2.
Example of WHERE clause using Update
3.1.3.
Example of WHERE clause using Delete
4.
ORDER BY Clause
4.1.
Syntax 
4.1.1.
Example of ORDER BY clause using ASC
4.1.2.
Example of ORDER BY clause using DESC
5.
GROUP BY Clause
5.1.
Syntax
5.1.1.
Example of GROUP BY without an aggregate function
5.1.2.
Example of GROUP BY clause on multiple columns
5.1.3.
Example of GROUP BY clause with Sum() function
6.
Frequently Asked Questions
6.1.
Is PostgreSQL a SQL or NoSQL?
6.2.
What are the advantages of PostgreSQL?
6.3.
What are the limitations of PostgreSQL?
6.4.
Why do people prefer PostgreSQL?
7.
Conclusion
Last Updated: Mar 27, 2024
Easy

Select where group by order by in postgresql

Author Urwashi Priya
0 upvote
Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

Introduction

Postgresql, a modified and advanced version of SQL, is widely used in making web applications. Postgresql is a relational database written in C language. Complex applications comprising a large set of data are generally built using PostgreSQL. 

select, where, group by and order by

In this article, we will discuss select, where, group by and order by clause in detail in PostgreSQL.

SELECT Statement

The SELECT Statement is used to acquire or retrieve the required information from the set of information present in the database. This statement makes the query more informative by adding some additional Clauses in Postgresql. Some of the clauses additionally added are WHERE, GROUP BY, ORDER BY and many more. We will discuss them further in the blog.

Syntax

SELECT
	List of columns
FROM
	Table Name;

 

Example

First, create a table,

CREATE TABLE employee (
	employee_id INT PRIMARY KEY,
	employeename VARCHAR(50),
	email VARCHAR(255),
	salary INT
);

 

INSERT INTO employee (employee_id, employeename, email, salary)
VALUES (1, 'Urwashi', 'urwashi.priya@codingninjas.com', 70000);


INSERT INTO employee (employee_id, employeename, email, salary)
VALUES (2, 'Aditya', 'aditya.kumar@codingninjas.com', 60000);


INSERT INTO employee (employee_id, employeename, email, salary)
VALUES (3, 'Aniket', 'aniket.verma@codingninjas.com', 80000);

Now, listing the items in the table,

SELECT employeename from employee;

 

Output

output

If the table consists of any duplicates and we require to obtain only the distinct data, then we use the SELECT statement with the DISTINCT clause in PostgreSQL.

Syntax

SELECT DISTINCT
	List of columns
FROM
	Table Name;
Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

WHERE Clause

The WHERE clause filters the output when returned by the Select, Update or Delete Statement. It appears right after the FROM clause of the Select, Update or Delete statement.

Syntax

SELECT list of columns 
FROM Table Name 
WHERE condition; 

 

The outcome of the condition can be true, false, or unknown. With the Where Clause, we can also use boolean expressions and combinations of the boolean expressions using the AND and OR operators to define the condition.

Apart from AND, OR, and NOT operators, the WHERE clause can also be combined with these operators-
 

Operator 

Description

Equal

Greater than

Less than

>= 

Greater than or equal

<= 

Less than or equal

<> 

Not equal. Note: In some versions of SQL this operator may be written as !=

 

Example of WHERE clause using Select

In the Select command, the Where condition filters the rows returned after applying the conditions to the SELECT statement. 
 

SELECT * FROM employee 
WHERE salary=70000; 

 

Output

output

In this example, we retrieve the details of all the customers whose salary=70000. Similarly, other conditions can also be applied after the Where clause to filter the data.

 

Example of WHERE clause using Update

The WHERE clause can be used with the UPDATE statement to specify rows to be updated. The UPDATE statement is used to modify the existing records in a table. 
 

UPDATE employee 
SET salary= 75000 
WHERE employee_id = 1;

 

In this example, we have updated the salary of the employee whose employee id is 1.

Now displaying the same table.

Output

output

Example of WHERE clause using Delete

The WHERE clause can also be used to delete existing records in a table with the DELETE statement.
 

DELETE FROM employee WHERE employeename='Aditya'; 

In the above example, we are trying to remove the row where the employeename is Aditya.

 

ORDER BY Clause

The ORDER BY keyword is used to sort the rows returned by the SELECT statement in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default.
 

Syntax 

SELECT List of columns 
FROM Table name 
ORDER BY List of columns
ASC|DESC; 

The ORDER BY clause cannot be directly used in an UPDATE or DELETE statement, however, it can be part of the sub-select that is in an UPDATE or DELETE statement.

Example of ORDER BY clause using ASC

SELECT * FROM employee 
ORDER BY salary ASC; 

Output

output

In this example, the Order By clause sorts the result set based on the column salary in ascending order. Even if we do not explicitly mention the ASC keyword, the salary will be sorted in ascending order after the ORDER BY clause.

Example of ORDER BY clause using DESC

To sort the records in descending order, use the DESC keyword. 

 

SELECT * FROM employee 
ORDER BY salary DESC;


Output

output

In this example, the Order By clause sorts the result set based on the column salary in descending order because of the explicit mention of the keyword DESC, after the ORDER BY clause.

GROUP BY Clause

The PostgreSQL GROUP BY clause uses SELECT statements to accumulate the same values into summary rows. It reduces the redundancy in the result. 

The GROUP BY statement works with aggregates functions, like SUM(), MAX(), MIN(), COUNT(), AVG(), etc., to group the result set by one or more columns.

This statement is also often used without aggregate functions. 

Syntax

SELECT list of columns  
FROM Table name 
WHERE condition 
GROUP BY list of columns 
ORDER BY list of columns  ; 

The GROUP BY clause comes after the WHERE clause and before the ORDER BY clause.

 

Example of GROUP BY without an aggregate function

SELECT Country 
FROM Customers
GROUP BY Country;

Here we will query for data from the Customers table, remove the other columns and group the result by Country from our sample database's “Customers” table.

 

Example of GROUP BY clause on multiple columns

SELECT Country, EmailAdress, ItemsBought 
FROM Customers
GROUP BY Country, EmailAdress, ItemsBought;

This time we will query for data from the Customers table, remove the other columns and group the result by Country, EmailAdress, ItemsBought from the Customers table of our sample database.

Example of GROUP BY clause with Sum() function

SELECT
    customer_id,
    SUM (amount)
FROM
    Customers
GROUP BY
    customer_id;

Here we will query to get the amount that each customer has paid for the product and use an aggregate function (i.e. SUM()) to do so and group them by customer_id from the “Customer” table of the sample database.

Frequently Asked Questions

Is PostgreSQL a SQL or NoSQL?

Postgresql is a relational database SQL written in C language.

What are the advantages of PostgreSQL?

It has an open-source development and community. It has a Robust feature, ACID compliance and well-explained documentation.

What are the limitations of PostgreSQL?

Postgresql is slower compared to SQL.

Why do people prefer PostgreSQL?

People prefer PostgreSQL because it supports large and complex databases. Operation on the complex database can easily be made.

Conclusion

In this article, we have discussed the details of the select, where, group by, and order by in PostgreSQL.

We hope that the blog has helped you enhance your knowledge of select, where, group by, and order by with PostgreSQL. You can refer to our guided paths on the Coding Ninjas Studio platform to learn more about DSADBMSCompetitive ProgrammingPythonJavaJavaScript, etc. To practice and improve yourself in the interview, you can also check out Top 100 SQL problemsInterview experienceCoding interview questions, and the Ultimate guide path for interviews. Do upvote our blogs to help other ninjas grow. 

Happy Coding!!

Previous article
Concept of Inheritance with PostgreSQL
Next article
Conditionals and Control Flow in PostgreSQL
Live masterclass