Select where group by order by in postgresql

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

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

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

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

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 DSA, DBMS, Competitive Programming, Python, Java, JavaScript, etc. To practice and improve yourself in the interview, you can also check out Top 100 SQL problems, Interview experience, Coding interview questions, and the Ultimate guide path for interviews. Do upvote our blogs to help other ninjas grow. 

Happy Coding!!