PostgreSQL & SQL Queries Step by Step
PostgreSQL is a powerful relational database. SQL is the language you use to talk to it. Strong SQL is one of the most valuable backend skills in interviews.
By Learn Refill Mentors · Updated
Why learn PostgreSQL & SQL?
- Relational databases power banking, e-commerce and most business apps
- SQL questions appear in almost every backend interview
- PostgreSQL is free, fast and widely used
1Create tables with relations
Each table has a primary key. A foreign key links rows between tables.
CREATE TABLE students (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL
);
CREATE TABLE payments (
id SERIAL PRIMARY KEY,
student_id INT REFERENCES students(id),
amount INT NOT NULL,
paid_on DATE NOT NULL
);2Insert and read data
INSERT adds rows; SELECT reads them. WHERE filters and ORDER BY sorts.
INSERT INTO students (name, email) VALUES ('Riya', 'riya@mail.com');
SELECT name, email
FROM students
WHERE name LIKE 'R%'
ORDER BY name;3Combine tables with JOIN
JOIN matches rows from two tables using the relation between them.
SELECT s.name, p.amount, p.paid_on
FROM payments p
JOIN students s ON s.id = p.student_id;4Build reports with GROUP BY
Aggregate functions like SUM, COUNT and AVG summarise data per group.
SELECT s.name, SUM(p.amount) AS total_paid, COUNT(*) AS payments
FROM payments p
JOIN students s ON s.id = p.student_id
GROUP BY s.name
HAVING SUM(p.amount) > 10000;5Make queries fast with indexes
An index works like a book index. Use EXPLAIN to see how PostgreSQL runs a query.
CREATE INDEX idx_payments_student ON payments(student_id);
EXPLAIN ANALYZE
SELECT * FROM payments WHERE student_id = 42;Common mistakes
- Running UPDATE or DELETE without a WHERE clause
- Using SELECT * everywhere in production code
- Building SQL strings with user input (SQL injection) — always use parameters
Practice task
Design tables for courses, students and enrollments, then write a query that shows how many students joined each course.
Frequently asked questions
What is the difference between WHERE and HAVING?
WHERE filters rows before grouping. HAVING filters groups after GROUP BY.
Is PostgreSQL the same as SQL?
SQL is the language. PostgreSQL is a database that understands SQL (plus extra features).