Tech Courses & Job Updates

Date and time functions are essential in PostgreSQL for analyzing daily sales, monthly revenue, customer activity, employee attendance, and business trends. In this tutorial, you will learn how to extract dates, calculate date differences, group data by month, and filter records using practical SQL examples. 1. Understanding Date and Timestamp Data Types PostgreSQL supports several date and time data types. Data Type Description Example DATE Stores a calendar date 2026-10-28 TIME Stores a time of day 14:30:00 TIMESTAMP Stores date and time without a time zone 2026-10-28 14:30:00 TIMESTAMPTZ Stores a time-zone-aware instant 2026-10-28 14:30:00+05:30 Use DATE for dates such as order dates and TIMESTAMPTZ when the time zone matters, such as recording events across different regions. 2. Get the Current Date and Time PostgreSQL provides functions for retrieving the current date and time. SELECT CURRENT_DATE; Returns the current date according to the...

  In PostgreSQL, CASE , COALESCE , and NULL-handling functions help you manage missing values, create conditional results, categorize data, and build meaningful business reports. These concepts are useful for data analysts, SQL developers, reporting, and technical interviews. What Is CASE in PostgreSQL? The CASE expression works like an IF-THEN-ELSE statement. It checks conditions and returns a result based on the first matching condition. Syntax: SELECT column_name, CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE default_result END AS result_column FROM table_name; 1. Categorize Employees Based on Salary Consider an employees table with these columns: employee_id , employee_name , department , and salary . SELECT employee_name, salary, CASE WHEN salary >= 70000 THEN 'High Salary' WHEN salary >= 50000 THEN 'Medium Salary' ELSE 'Low Salary' END AS sala...

  Window functions are powerful PostgreSQL features used to calculate rankings, running totals, moving averages, and comparisons between rows without combining them into a single row. They are widely used in data analytics, business reporting, SQL interviews, and real-world database applications. Sample Table Consider an employees table with the following records. employee_id employee_name department salary 1 Rahul IT 60000 2 Priya HR 40000 3 Arjun IT 75000 4 Sneha Finance 50000 5 Kiran HR 45000 6 Divya Finance 65000 7 Ravi IT 60000 What Is a Window Function? A window function performs a calculation across a set of related rows while keeping each original row in the result. The OVER() clause defines the window of rows used for the calculation. General syntax: function_name() OVER ( PARTITION BY column_name ORDER BY column_name ) PARTITION BY divides rows into groups. ORDER BY defines the order used by the...

Subqueries and Common Table Expressions (CTEs) are important PostgreSQL concepts used to write readable queries, filter data based on other query results, and solve real-world business problems. In this tutorial, you will learn how to use subqueries, correlated subqueries, and CTEs with practical SQL examples. Sample Table We will use an employees table with the following data: employee_id employee_name department salary 1 Rahul IT 60000 2 Priya HR 40000 3 Arjun IT 75000 4 Sneha Finance 50000 5 Kiran HR 45000 6 Divya Finance 65000 What Is a Subquery? A subquery is a SQL query written inside another SQL query. It is also called an inner query or nested query. Subqueries can be used inside SELECT , FROM , WHERE , and HAVING clauses. Example 1: Find Employees Earning More Than the Average Salary SELECT employee_name, salary FROM employees WHERE salary > ( SELECT AVG(salary) FROM employees ); The inner query calculates the average salary. The outer query returns employees whos...

 These concepts are useful for SQL Developers, Data Analysts, and technical interviews. What You Will Learn What is a SELF JOIN? What is a CROSS JOIN? Difference between UNION and UNION ALL Practical SQL examples Common mistakes and interview questions 1. What Is a SELF JOIN? A SELF JOIN joins a table with itself. It is useful when records in the same table are related to each other. For example, an employee table may contain both employees and their managers. Create an Employee Table CREATE TABLE company_employees ( employee_id INT PRIMARY KEY, employee_name VARCHAR(50), manager_id INT ); INSERT INTO company_employees VALUES (1, 'Rahul', NULL), (2, 'Priya', 1), (3, 'Arjun', 1), (4, 'Sneha', 2), (5, 'Kiran', 2); Here, manager_id refers to another employee_id in the same table. Example: Display Employees and Their Managers SELECT e.employee_name AS employee, m.employee_name AS manager FROM company_employees e LEFT JOIN company...

 PostgreSQL JOINs help you retrieve related data from two or more tables. They are essential for SQL developers, Data Analysts, and anyone working with relational databases. For example, a company may store employee information in one table and department information in another. JOINs allow you to combine these tables and generate meaningful reports. What You Will Learn What is a JOIN in PostgreSQL? INNER JOIN LEFT JOIN RIGHT JOIN FULL OUTER JOIN JOIN conditions and common mistakes Practical exercises and interview questions 1. Create Sample Tables We will use two tables: employees and departments. Departments table CREATE TABLE departments ( department_id INT PRIMARY KEY, department_name VARCHAR(50) ); INSERT INTO departments VALUES (1, 'Analytics'), (2, 'Finance'), (3, 'IT'), (4, 'HR'); Employees table CREATE TABLE employees ( employee_id INT PRIMARY KEY, employee_name VARCHAR(50), department_id INT ); INSERT INTO employees VALUES...

In this tutorial, you will learn how to use PostgreSQL aggregate functions, GROUP BY, and HAVING with practical SQL examples. These concepts are useful for database reporting, data analytics, and SQL interviews. What You Will Learn COUNT(), SUM(), AVG(), MIN(), and MAX() GROUP BY with practical examples WHERE vs HAVING Common SQL mistakes Practice questions and interview questions 1. What Are Aggregate Functions in PostgreSQL? Aggregate functions perform calculations on multiple rows and return a single result. They help calculate totals, averages, counts, minimum values, and maximum values. For example, a company may want to calculate its total salary expense or find the average salary of employees. 2. Create a Sample Table We will use an employees table for all examples in this tutorial. CREATE TABLE employees ( employee_id INT PRIMARY KEY, employee_name VARCHAR(50), department VARCHAR(30), salary NUMERIC(10,2) ); INSERT INTO employees (employee_id, employee_name, de...