Skip to main content

Posts

Showing posts from August, 2019

HAVING clause in PostgreSQL

In this article, we will learn about HAVING clause, its proper use on the scenarios based, how to eliminate groups of rows that do not satisfy a specified condition, etc. The HAVING clause used with GROUP BY clause and it behaves like where clause but applies only to groups as a whole. The HAVING clause is applied to the rows in the result set, means first data is fetched from memory to result set then apply HAVING clause on the row of the result set. We can apply a HAVING clause only to columns that also appear in the GROUP BY clause or in an aggregate function.

Syntax: PostgreSQL HAVING clauseSELECT col_1, aggregate_function (col_2) FROM table_name GROUP BY col_1 HAVING condition;

Some scenarios where we need to apply the having clause Suppose, we have a table where we manage the monthly salary data of employees, for practice please execute the following PostgreSQL statement.
CREATE TABLE public."Salary" ( "Id" bigserial NOT NULL , "EmpId" bigint NOT NULL, "Net…

GROUP BY clause in PostgreSQL

With the assistance of some functions, the GROUP BY statement in SQL is used to organize identical data into groups. i.e. if a specific column has the same values in distinct rows, then these rows will be arranged in a group.  Syntax: the syntax of the GROUP BY clause is given below.
SELECT <Col_1>, <Col_2>, <aggregate_function(Col_3)>....<Col_n> FROM <table_name> WHERE <conditions> GROUP BY <Col_1>, <Col_2>....<Col_N>

For each the group you can apply an aggregate function like SUM(), COUNT(), etc. The GROUP BY clause appears after FROM or WHERE clause in a SELECT statement. PostgreSQL GROUP BY clause examplesSuppose we have the following table. You can create an Employee table using the below PostgreSQL statement.
CREATE TABLE public."Employee" (     "Id" bigserial NOT NULL,     "FName" text COLLATE pg_catalog."default",     "LName" text COLLATE pg_catalog."default",     "UserN…

ORDER BY clause in PostgreSQL

ORDER BY clause used to sort the result set return from the SELECT statement in ascending or descending order based on the specified criteria. Without using ORDER BY clause, when you query data from the table, PostgreSQL returns the rows in the order that they were inserted into the table. In many scenarios, we need to show the result set based on the specified column(s) ascending or descending, suppose I have an Employee table and want to see the employee's records based on their FirstName ( ascending or descending), in that type of cases ORDER BY clause help us to achieve the goal.
PostgreSQL - ORDER BY clause syntax:
SELECT Col_1, Col_2, Col_3, ..... FROM table_name ORDER BY Col_1 ASC, Col_2 DESC;

Suppose we have the following table. You can create an Employee table using the below PostgreSQL statement.
CREATE TABLE public."Employee" ( "Id" bigserial NOT NULL, "FName" text COLLATE pg_catalog."default", "LName" text COLLATE pg_catalog."…

UPDATE in PostgreSQL

Using the UPDATE statement, we can change the value of the existing records in the table. Sometimes we need to change the value of the column(s) as per requirements. Suppose we have the "Employee" table and we need to update the salary of a particular employee, it means we will update the salary which already exists in the "Employee" table. But do it carefully because it is very critical to update any records into the table, you must apply where clause in update statement because if you don't use WHERE clause, then it will update all records of your table which may create a big issue for you. So please be careful before running the UPDATE statement. PostgreSQL UPDATE syntax
UPDATE table_name SET col1 = val1, col2 = val2 ,... WHERE condition;
table_name is the name of the table which records you want to update, and it comes after UPDATE keywords.

col1,col1 is the column name of the table which particular value want to change. It comes after the SET keyword, and if you…

WHERE Clause in PostgreSQL

WHERE clause used into PostgreSQL statement to specify/apply any condition while retrieving, updating or deleting data from a table.
Syntax: PostgreSQL statement using WHERE clause
SELECT <Column Name1>,<Column Name2>... FROM table_name WHERE <condition>;

The following table illustrates the standard comparison operators. OperatorDescription =Equal >Greater than <Less than >=Greater than or equal <=Less than or equal <> or !=Not equal ANDLogical operator AND ORLogical operator OR
PostgreSQL WHERE clause example Suppose we have a table "Employee" so first we create a table using the following query.
CREATE TABLE public."Employee" ( "Id" bigserial NOT NULL, "FName" text COLLATE pg_catalog."default", "LName" text COLLATE pg_catalog."default", "UserName" text COLLATE pg_catalog."default", "Password" text COLLATE pg_catalog."default", "Contact"…

What is an Operator in PostgreSQL

An operator is a reserved symbol or word/letter used to do logical or mathematical operations in the PostgreSQL statement.
Types of Operators in PostgreSQL: 1- Arithmetic operators 2- Comparison operators 3- Logical operators 4- Bitwise operators
Arithmetic operators: +:Addition, Ex: a+b -:Subtraction, Ex: a-b *: Multiplication, Ex: a*b /: Division, Ex: a/b %: Modulus, Ex: a%b ^: Exponentiation, Ex: a^b |/:square root, Ex: |/a ||/:Cube root, Ex: ||/a !:factorial, Ex: !a !!:factorial, Ex: !!a
Comparison operators =:Equal to operator, Ex:(a = b). !=:Not Equal to,Ex: (a != b). <>:Not Equal, Ex: (a <> b). >:Greater Then, Ex: (a > b). <:Less Then, Ex:(a < b). >=:Greater Then Equal To, Ex:(a >= b). <=:Less Then Equal To, Ex: (a <= b).
Logical operators AND:Allows the multiple conditions in a PostgresSQL statement. NOT: This is negate operator. NOT EXISTS, NOT BETWEEN, NOT IN etc. OR: Allows to combining multiple conditions in a PostgresSQL statement.