Aggregate function in PostgreSQL

Aggregate function:

• An aggregate function in PostgreSQL computes a single result from a set of input values.
• They process multiple rows of data and return a summarized output, often used alongside the GROUP BY clause.

Common Aggregate Functions

COUNT(): Counts rows from above table

select count(salary) from employees;

SUM(): Adds numeric values

select sum(salary) from employees;

MAX(): Finds highest value.

select max(salary) from employees;

MIN(): Finds lowest value.

select min(salary) from employees;

AVG(): to find average

select avg(salary) from employees;

ROUND(): rounds the value

select round(avg(salary),2) from employees;

Sub queries:

queries inside another queries

select name from employees where salary = (select min(salary) from employees);

select name from employees where salary = (select max(salary) from employees) and name like 'A%';

to find second largest salary in the above table

select max(salary) from employees where salary not in (select max(salary) from employees);

to find second smallest salary in the above table

select min(salary) from employees where salary not in (select min(salary) from employees);

Group by clause

select count(name), designation from employees group by designation;

Group By and Order By clause

select count(name), designation from employees group by designation order by count(name);

Having clause:
The HAVING clause in PostgreSQL filters group rows created by the GROUP BY clause based on a specified condition.

select count(name), designation from employees 
group by designation 
having count(name)>=2;

SQL Topics:

1. DDL:
Data Definition Language (DDL) in PostgreSQL consists of SQL commands used to create, modify, and delete database structures (such as tables, indexes, views, and schemas).

Core DDL Commands:(TBD)
CREATE: Builds new database objects.
ALTER: Modifies existing database structures.
DROP: Deletes database objects permanently.
TRUNCATE: Removes all rows from a table quickly.

2. DML:
Data Manipulation Language (DML) in PostgreSQL consists of the SQL commands used to add, modify, delete, and retrieve data within database tables.

Core DML Commands:(TBD)
SELECT: Retrieves records from one or more tables.
INSERT: Adds new rows of data into a table.
UPDATE: Modifies existing data within a table.
DELETE: Removes existing rows from a table.
MERGE: Conditionally inserts, updates, or deletes rows (available in PostgreSQL 15 and newer).

3. TCL:
TCL (Transaction Control Language) in PostgreSQL consists of commands that manage transactions to ensure data integrity and rollback changes if errors occur.

Core TCL Commands:(TBD)
BEGIN (or START TRANSACTION)
COMMIT
ROLLBACK
SAVEPOINT

4. DQL:
Data Query Language (DQL) in PostgreSQL refers to the subset of SQL commands used to retrieve data from a database, which primarily consists of the SELECT statement.

Core Components of a PostgreSQL DQL Query:
SELECT: Specifies the columns to retrieve.
FROM: Identifies the table or tables to query.
WHERE: Filters rows based on specific conditions.
GROUP BY: Groups rows sharing identical data for aggregate functions.
HAVING: Filters groups created by the GROUP BY clause.
ORDER BY: Sorts the final result set (ascending or descending).
LIMIT / OFFSET: Restricts the number of returned rows.