PostgreSQL is one of the most powerful, stable, and open-source relational database systems trusted by global giants like Apple, Instagram, and Spotify. Whether you’re building a web application, managing enterprise data, or diving into analytics, understanding PostgreSQL is a skill that sets you apart.
But what if you could master it in just 10 days, in Tamil, with hands-on learning and a guaranteed 5★ rating on HackerRank as your goal?
Sounds exciting? Let’s dive in.
Why This Bootcamp?
This 10-day PostgreSQL Bootcamp in Tamil is designed to take you from absolute beginner to confident practitioner, with a curriculum built around real-world use cases, performance optimization, and daily challenge-driven learning.
Whether you’re a
Student trying to get into backend development
Developer wanting to upskill and crack interviews
Data analyst exploring SQL performance
Tech enthusiast curious about databases
…this bootcamp gives you the structured path you need.
What You’ll Learn
Over 10 days, we’ll cover
PostgreSQL installation & setup
PostgreSQL architecture and internals
Writing efficient SQL queries with proper formatting
Joins, CTEs, subqueries, and advanced querying
Indexing, query plans, and performance tuning
Transactions, isolation levels, and locking mechanisms
Schema design for real-world applications
Debugging techniques, tips, and best practices
Daily HackerRank challenges to track your progress
FROM and/or JOIN WHERE GROUP BY HAVING SELECT DISTINCT ORDER BY LIMIT nad/or OFFSET
Command Types:
References : Aysha Beevi
CAST()
CAST is used to typecast or we can use ::target data type.
SELECT ‘The current date is: ‘ || CURRENT_DATE::TEXT; SELECT ‘2024-12-21’::DATE::TEXT; SELECT CAST(‘2024-12-21’ AS DATE);
|| –> Concatenation operator
DATE functions:
SELECT CURRENT_DATE; — Output: 2024-12-21 SELECT CURRENT_TIME; — Output: 09:15:34.123456+05:30 SELECT NOW(); — Output: 2024-12-21 09:15:34.123456+05:30 SELECT AGE(‘2020-01-01’, ‘2010-01-01’); — Output: 10 years 0 mons 0 days SELECT AGE(‘1990-05-15’); — Output: 34 years 7 mons 6 days (calculated from NOW()) SELECT EXTRACT(YEAR FROM NOW()); — Output: 2024 SELECT EXTRACT(MONTH FROM CURRENT_DATE); — Output: 12 SELECT EXTRACT(DAY FROM TIMESTAMP ‘2024-12-25 10:15:00’); — Output: 25
The DATE_TRUNC() function truncates a date or timestamp to the specified precision. This means it “resets” smaller parts of the date/time to their starting values. SELECT DATE_TRUNC(‘month’, TIMESTAMP ‘2024-12-21 10:45:30’); — Output: 2024-12-01 00:00:00 –> The ‘month’ precision resets the day to the 1st, and the time to 00:00:00. SELECT DATE_TRUNC(‘year’, TIMESTAMP ‘2024-12-21 10:45:30’); — Output: 2024-01-01 00:00:00 SELECT DATE_TRUNC(‘day’, TIMESTAMP ‘2024-12-21 10:45:30’); — Output: 2024-12-21 00:00:00
This is the function that will operate over the specified window. Common window functions include ROW_NUMBER(), RANK(), SUM(), AVG(), etc
.PARTITION BY: (Optional) Divides the result set into partitions to which the window function is applied. Each partition is processed separately.ORDER BY: (Optional) Orders the rows in each partition before the window function is applied.
window_function() OVER (--RANK() or SUM() etc. can come in window_function
PARTITION BY column_name(s)
ORDER BY column_name(s)
);
SELECT
department_id,
employee_id,
salary,
SUM(salary) OVER (PARTITION BY department_id ORDER BY salary) AS running_total
FROM employees;
CURSOR:
DO $$ DECLARE emp_name VARCHAR; emp_salary DECIMAL; emp_cursor CURSOR FOR SELECT name, salary FROM employees; BEGIN OPEN emp_cursor; LOOP FETCH emp_cursor INTO emp_name, emp_salary; EXIT WHEN NOT FOUND; — Exit the loop when no rows are left RAISE NOTICE ‘Employee: %, Salary: %’, emp_name, emp_salary; END LOOP; CLOSE emp_cursor;
Basic Data Types in PostgreSQL
TEXT, VARCHAR, CHAR: Working with strings.
INTEGER, BIGINT, NUMERIC: Handling numbers.
DATE, TIMESTAMP: Date and time handling.
OVER CLAUSE
In PostgreSQL, the OVER() clause is used in window functions to define a window of rows over which a function operates. Just create a serial number (Row_number) from 1 (Rows are already ordered by salary desc) SELECT name, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num FROM employees WHERE row_num <= 5;
RANK()
Parition the table records based on the dept id, then inside each partition order by salary desc with rank 1,2,3… – In RANK() if same salary then RANK repeats.
SELECT department_id, name, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank FROM employees Output: department_id name salary rank 101 Charlie 70,000 1 101 Alice 50,000 2 101 Frank 50,000 2 102 Eve 75,000 1 102 Bob 60,000 2 103 David 55,000 1
Divides employees into 3 equal salary buckets (quartiles). SELECT id, name, salary, NTILE(3) OVER (ORDER BY salary DESC) AS quartile FROM employees; id name salary quartile 5 Eve 75,000 1 3 Charlie 70,000 1 2 Bob 60,000 2 4 David 55,000 2 1 Alice 50,000 3 6 Frank 50,000 3
Retrieves the first name in each department based on descending salary. SELECT department_id, name, salary, FIRST_VALUE(name) OVER (PARTITION BY department_id ORDER BY salary DESC) AS top_earner FROM employees; Output: department_id name salary top_earner 101 Charlie 70,000 Charlie 101 Alice 50,000 Charlie 101 Frank 50,000 Charlie 102 Eve 75,000 Eve 102 Bob 60,000 Eve 103 David 55,000 David
First from table will be taken, then WHERE condition will be applied
In the WHERE clause directly you cannot call the RANK(), it should be stored in result set, from there only we can call it. So only RANK() will get executed ie Windows CTE (Common Table Expression), that’s why first the CTE will get executed and stored in a temp result set, then SELECT from that result set.
Below we gave in the subquery, so it will get executed and then that value is getting used by the outer query.
In each dept top earner name with his name and salary (consider the above table employees) SELECT department_id, name, salary FROM ( SELECT department_id, name, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank FROM employees ) ranked_employees WHERE rank = 1;
department_id name salary 101 Charlie 70,000 102 Eve 75,000 103 David 55,000
Resultset – here RankedSalaries is Resultset
WITH RankedSalaries AS ( SELECT salary, RANK() OVER (ORDER BY salary DESC) AS rank FROM employees ) SELECT salary FROM RankedSalaries WHERE rank = 2;
Here, RankedSalaries is a temporary result set or CTE (Common Table Expression)
Reference: Learnt from ChatGPT and Picture from Ms.Aysha
Login
Why This Bootcamp?