Ready to master PostgreSQL with hands-on practice? This post brings together 100 PostgreSQL problems with clear, fully explained solutions. You’ll start with simple SQL queries and gradually move to joins, subqueries, window functions, indexing, and even basic database design.
Each challenge is written in plain language so you can understand the task, try writing your own query, and then check the solution. Whether you’re a beginner just getting started with databases or a developer preparing for a data-focused interview, these exercises will help you write better queries and think like a database pro.
Pick a problem and start sharpening your PostgreSQL skills today.
Try it: 100 SQL practice problems with solutions
1. Create a table called “employees” with columns: id (serial primary key), name (varchar(100)), salary (numeric), department_id (integer).
sql
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
salary NUMERIC,
department_id INTEGER
);
2. Insert three rows into employees: (1, ‘Alice’, 50000, 1), (2, ‘Bob’, 60000, 2), (3, ‘Charlie’, 55000, 1).
sql
INSERT INTO employees (id, name, salary, department_id) VALUES (1, 'Alice', 50000, 1), (2, 'Bob', 60000, 2), (3, 'Charlie', 55000, 1);
3. Retrieve all columns from the employees table.
sql
SELECT * FROM employees;
4. Select only the name and salary of employees earning more than 52000.
sql
SELECT name, salary FROM employees WHERE salary > 52000;
5. Update Bob’s salary to 65000.
sql
UPDATE employees SET salary = 65000 WHERE name = 'Bob';
6. Delete the employee named ‘Charlie’.
sql
DELETE FROM employees WHERE name = 'Charlie';
7. Create a table “departments” with columns: id (serial primary key), name (varchar(100)). Insert two departments: (1, ‘HR’), (2, ‘Engineering’).
sql
CREATE TABLE departments (
id SERIAL PRIMARY KEY,
name VARCHAR(100)
);
INSERT INTO departments (id, name) VALUES (1, 'HR'), (2, 'Engineering');
8. Write a query to list employees with their department names using JOIN.
sql
SELECT e.name, e.salary, d.name AS department_name FROM employees e JOIN departments d ON e.department_id = d.id;
9. Find the average salary of employees.
sql
SELECT AVG(salary) FROM employees;
10. Count the number of employees in each department.
sql
SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;
11. Find the highest salary in the employees table.
sql
SELECT MAX(salary) FROM employees;
12. Add a new column “email” (varchar(150)) to the employees table.
sql
ALTER TABLE employees ADD COLUMN email VARCHAR(150);
13. Add a unique constraint on the email column.
sql
ALTER TABLE employees ADD CONSTRAINT unique_email UNIQUE (email);
14. Insert a new employee with id 4, name ‘David’, salary 48000, department_id 1, email ‘david@example.com’.
sql
INSERT INTO employees (id, name, salary, department_id, email) VALUES (4, 'David', 48000, 1, 'david@example.com');
15. Find employees whose names start with ‘A’.
sql
SELECT * FROM employees WHERE name LIKE 'A%';
16. Order employees by salary descending.
sql
SELECT * FROM employees ORDER BY salary DESC;
17. Create an index on the salary column for better performance.
sql
CREATE INDEX idx_salary ON employees(salary);
18. Write a query that uses a subquery to find employees who earn more than the average salary.
sql
SELECT name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
19. Use a CTE (Common Table Expression) to get departments with average salary above 55000.
sql
WITH dept_avg AS (
SELECT department_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY department_id
)
SELECT d.name, da.avg_sal
FROM departments d
JOIN dept_avg da ON d.id = da.department_id
WHERE da.avg_sal > 55000;
20. Create a view named “high_earners” that shows employees with salary > 60000.
sql
CREATE VIEW high_earners AS SELECT * FROM employees WHERE salary > 60000;
21. Write a query using a window function to rank employees by salary within their department.
sql
SELECT name, department_id, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees;
22. Add a foreign key constraint to employees referencing departments(id).
sql
ALTER TABLE employees ADD CONSTRAINT fk_dept FOREIGN KEY (department_id) REFERENCES departments(id);
23. Create a table “projects” with id, name, start_date (DATE). Insert one project.
sql
CREATE TABLE projects (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
start_date DATE
);
INSERT INTO projects (name, start_date) VALUES ('Project X', '2025-01-15');
24. Create a junction table “employee_projects” to model many-to-many relationship between employees and projects.
sql
CREATE TABLE employee_projects (
employee_id INT REFERENCES employees(id),
project_id INT REFERENCES projects(id),
PRIMARY KEY (employee_id, project_id)
);
25. Assign employee Alice (id=1) to project ‘Project X’.
sql
INSERT INTO employee_projects (employee_id, project_id) VALUES (1, 1);
26. List all employees and the projects they work on (left join).
sql
SELECT e.name, p.name AS project_name FROM employees e LEFT JOIN employee_projects ep ON e.id = ep.employee_id LEFT JOIN projects p ON ep.project_id = p.id;
27. Count how many projects each employee is assigned to.
sql
SELECT e.name, COUNT(ep.project_id) AS project_count FROM employees e LEFT JOIN employee_projects ep ON e.id = ep.employee_id GROUP BY e.id, e.name;
28. Remove the projects table and cascade (if constraints).
sql
DROP TABLE projects CASCADE;
29. Create a function that returns the number of employees in a given department (by department_id).
sql
CREATE FUNCTION count_employees(dept_id INTEGER) RETURNS INTEGER AS $$
BEGIN
RETURN (SELECT COUNT(*) FROM employees WHERE department_id = dept_id);
END;
$$ LANGUAGE plpgsql;
30. Call the function for department 1.
sql
SELECT count_employees(1);
31. Create a trigger that automatically updates a “last_modified” column in employees whenever a row is updated.
sql
ALTER TABLE employees ADD COLUMN last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
CREATE OR REPLACE FUNCTION update_last_modified()
RETURNS TRIGGER AS $$
BEGIN
NEW.last_modified = CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER update_employees_timestamp
BEFORE UPDATE ON employees
FOR EACH ROW EXECUTE FUNCTION update_last_modified();
32. Write a query to find duplicate emails (if any).
sql
SELECT email, COUNT(*) FROM employees GROUP BY email HAVING COUNT(*) > 1;
33. Use EXPLAIN to see the query plan for SELECT * FROM employees WHERE salary > 50000.
sql
EXPLAIN SELECT * FROM employees WHERE salary > 50000;
34. Create a partial index on employees who have salary > 70000.
sql
CREATE INDEX idx_high_salary ON employees(salary) WHERE salary > 70000;
35. Write a query that returns the second highest salary.
sql
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;
36. Use COALESCE to replace NULL email with ‘N/A’.
sql
SELECT name, COALESCE(email, 'N/A') AS email FROM employees;
37. Use CASE to create a salary category: ‘Low’ (<50000), ‘Medium’ (50000-60000), ‘High’ (>60000).
sql
SELECT name, salary,
CASE
WHEN salary < 50000 THEN 'Low'
WHEN salary BETWEEN 50000 AND 60000 THEN 'Medium'
ELSE 'High'
END AS salary_category
FROM employees;
38. Calculate the running total of salaries ordered by id.
sql
SELECT id, name, salary,
SUM(salary) OVER (ORDER BY id) AS running_total
FROM employees;
39. Use LAG to find the difference between current and previous salary.
sql
SELECT id, name, salary,
LAG(salary) OVER (ORDER BY id) AS prev_salary,
salary - LAG(salary) OVER (ORDER BY id) AS diff
FROM employees;
40. Create a new table “salaries_audit” to log changes, and a trigger to insert into it when salary is updated.
sql
CREATE TABLE salaries_audit (
employee_id INT,
old_salary NUMERIC,
new_salary NUMERIC,
changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE OR REPLACE FUNCTION audit_salary_change()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.salary <> OLD.salary THEN
INSERT INTO salaries_audit (employee_id, old_salary, new_salary)
VALUES (OLD.id, OLD.salary, NEW.salary);
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER salary_audit_trigger
AFTER UPDATE OF salary ON employees
FOR EACH ROW EXECUTE FUNCTION audit_salary_change();
41. Write a query using EXISTS to find departments that have at least one employee.
sql
SELECT * FROM departments d WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.id);
42. Use ANY to find employees whose salary is greater than the salary of any employee in department 2.
sql
SELECT * FROM employees WHERE salary > ANY (SELECT salary FROM employees WHERE department_id = 2);
43. Create a materialized view that stores department-wise average salary (refresh manually).
sql
CREATE MATERIALIZED VIEW dept_avg_salary AS SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id; REFRESH MATERIALIZED VIEW dept_avg_salary;
44. Write a recursive CTE to generate a sequence of numbers from 1 to 10.
sql
WITH RECURSIVE numbers(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT * FROM numbers;
Try it: 100 MySQL practice problems with solutions
45. Create a schema named “sales” and move the employees table into it.
sql
CREATE SCHEMA sales; ALTER TABLE employees SET SCHEMA sales;
46. Query the employees table from the sales schema.
sql
SELECT * FROM sales.employees;
47. Rename the employees table to “staff”.
sql
ALTER TABLE sales.employees RENAME TO staff;
48. Add a CHECK constraint that salary must be greater than 0.
sql
ALTER TABLE sales.staff ADD CONSTRAINT check_salary_positive CHECK (salary > 0);
49. Insert a row that violates the check constraint (to see error).
sql
INSERT INTO sales.staff (name, salary) VALUES ('Test', -1000); -- will fail
50. Use INSERT ... ON CONFLICT to insert a new employee or update if id exists (upsert).
sql
INSERT INTO sales.staff (id, name, salary, department_id, email) VALUES (1, 'Alice', 52000, 1, 'alice@example.com') ON CONFLICT (id) DO UPDATE SET salary = EXCLUDED.salary, email = EXCLUDED.email;
51. Create a user “app_user” with password ‘pass123’ and grant SELECT on staff to them.
sql
CREATE USER app_user WITH PASSWORD 'pass123'; GRANT SELECT ON sales.staff TO app_user;
52. Revoke SELECT privilege from app_user.
sql
REVOKE SELECT ON sales.staff FROM app_user;
53. Use pg_dump to backup the “sales” schema (command line: not SQL, but explain).
bash
pg_dump --schema=sales mydb > sales_schema.sql
54. Restore the backup into a new database “restoredb”.
bash
createdb restoredb psql restoredb < sales_schema.sql
55. Get the current database name using SQL.
sql
SELECT current_database();
56. List all tables in the current schema.
sql
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
57. View the size of a table (in bytes).
sql
SELECT pg_size_pretty(pg_total_relation_size('sales.staff'));
58. Create a composite index on (department_id, salary).
sql
CREATE INDEX idx_dept_salary ON sales.staff (department_id, salary);
59. Use a full-text search on a text column (add a column bio TEXT, then create GIN index).
sql
ALTER TABLE sales.staff ADD COLUMN bio TEXT;
CREATE INDEX idx_bio_fts ON sales.staff USING GIN (to_tsvector('english', bio));
60. Query using full-text search for word ‘manager’.
sql
SELECT * FROM sales.staff WHERE to_tsvector('english', bio) @@ to_tsquery('manager');
61. Create a role “readonly” that can only SELECT from all tables.
sql
CREATE ROLE readonly; GRANT CONNECT ON DATABASE mydb TO readonly; GRANT USAGE ON SCHEMA sales TO readonly; GRANT SELECT ON ALL TABLES IN SCHEMA sales TO readonly;
62. Assign the readonly role to app_user.
sql
GRANT readonly TO app_user;
63. Use GROUPING SETS to get total, department-wise, and department+salary-level grouping.
sql
SELECT department_id, salary, COUNT(*) FROM sales.staff GROUP BY GROUPING SETS ((department_id), (department_id, salary), ());
64. Write a query using ROLLUP to get salaries sum by department and overall.
sql
SELECT department_id, SUM(salary) FROM sales.staff GROUP BY ROLLUP (department_id);
65. Write a query using CUBE on department_id and salary.
sql
SELECT department_id, salary, COUNT(*) FROM sales.staff GROUP BY CUBE (department_id, salary);
66. Create a sequence for employee IDs (if not using SERIAL).
sql
CREATE SEQUENCE emp_id_seq START 5;
67. Use the sequence to insert a new employee with nextval.
sql
INSERT INTO sales.staff (id, name, salary) VALUES (nextval('emp_id_seq'), 'Eve', 70000);
68. Drop the sequence.
sql
DROP SEQUENCE emp_id_seq;
69. Create a table “logs” with id, message, created_at default now().
sql
CREATE TABLE logs (id SERIAL, message TEXT, created_at TIMESTAMP DEFAULT NOW());
70. Insert a log message.
sql
INSERT INTO logs (message) VALUES ('Application started');
71. Use pg_sleep to pause for 2 seconds and then insert another log.
sql
SELECT pg_sleep(2);
INSERT INTO logs (message) VALUES ('After sleep');
72. Extract year, month, day from a date column (add a hire_date column).
sql
ALTER TABLE sales.staff ADD COLUMN hire_date DATE; UPDATE sales.staff SET hire_date = '2020-01-15' WHERE id = 1; SELECT EXTRACT(YEAR FROM hire_date) AS hire_year, EXTRACT(MONTH FROM hire_date) AS hire_month FROM sales.staff;
73. Use DATE_TRUNC to group by month of hire date.
sql
SELECT DATE_TRUNC('month', hire_date) AS month, COUNT(*) FROM sales.staff GROUP BY month;
74. Create a partitioned table by range on hire_date (partition by year).
sql
CREATE TABLE staff_partitioned (
LIKE sales.staff INCLUDING DEFAULTS INCLUDING CONSTRAINTS
) PARTITION BY RANGE (hire_date);
CREATE TABLE staff_2020 PARTITION OF staff_partitioned FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');
CREATE TABLE staff_2021 PARTITION OF staff_partitioned FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');
75. Insert data into the partitioned table (if data exists).
sql
INSERT INTO staff_partitioned SELECT * FROM sales.staff;
76. Use pg_stat_activity to see current running queries.
sql
SELECT pid, usename, query, state FROM pg_stat_activity;
77. Terminate a specific backend connection by pid (example).
sql
SELECT pg_terminate_backend(12345);
78. Create a role with login and password, then change its password.
sql
CREATE ROLE demo WITH LOGIN PASSWORD 'demo123'; ALTER ROLE demo WITH PASSWORD 'newpass456';
79. Grant usage on a sequence to a user.
sql
GRANT USAGE ON SEQUENCE emp_id_seq TO demo;
80. Use EXPLAIN ANALYZE to measure actual execution time of a query.
sql
EXPLAIN ANALYZE SELECT * FROM sales.staff WHERE salary > 50000;
81. Create a function that returns a table of employees with salary above input threshold.
sql
CREATE FUNCTION get_high_earners(threshold NUMERIC)
RETURNS TABLE(name VARCHAR, salary NUMERIC) AS $$
BEGIN
RETURN QUERY SELECT staff.name, staff.salary FROM sales.staff WHERE staff.salary > threshold;
END;
$$ LANGUAGE plpgsql;
82. Call the function with threshold 55000.
sql
SELECT * FROM get_high_earners(55000);
83. Create a temporary table for testing purposes that disappears after session.
sql
CREATE TEMP TABLE temp_emps AS SELECT * FROM sales.staff;
84. Use LISTEN and NOTIFY to send a notification (example).
sql
LISTEN my_channel; NOTIFY my_channel, 'Hello World!';
85. Create a FOREIGN DATA WRAPPER to connect to another PostgreSQL server (simplified example).
sql
CREATE EXTENSION postgres_fdw; CREATE SERVER remote_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'remotehost', dbname 'remotedb'); CREATE USER MAPPING FOR current_user SERVER remote_server OPTIONS (user 'remoteuser', password 'remotepass'); CREATE FOREIGN TABLE remote_employees (id INT, name TEXT) SERVER remote_server OPTIONS (schema_name 'public', table_name 'employees');
86. Query the foreign table.
sql
SELECT * FROM remote_employees;
87. Create a DO block that prints the number of employees.
sql
DO $$
DECLARE
emp_count INTEGER;
BEGIN
SELECT COUNT(*) INTO emp_count FROM sales.staff;
RAISE NOTICE 'Employee count: %', emp_count;
END;
$$;
88. Use array_agg to get a list of employee names per department.
sql
SELECT department_id, ARRAY_AGG(name) AS employees_list FROM sales.staff GROUP BY department_id;
89. Use string_agg to concatenate employee names with comma.
sql
SELECT department_id, STRING_AGG(name, ', ') FROM sales.staff GROUP BY department_id;
90. Write a query to find employees who have the same salary as someone else (duplicate salaries).
sql
SELECT salary, array_agg(name) FROM sales.staff GROUP BY salary HAVING COUNT(*) > 1;
91. Use DISTINCT ON to get the highest paid employee in each department.
sql
SELECT DISTINCT ON (department_id) id, name, salary, department_id FROM sales.staff ORDER BY department_id, salary DESC;
92. Use GROUP BY with FILTER to count employees with high salary per department.
sql
SELECT department_id,
COUNT(*) AS total,
COUNT(*) FILTER (WHERE salary > 60000) AS high_salary_count
FROM sales.staff
GROUP BY department_id;
93. Use WITH ORDINALITY to add row numbers to a set returned by a function.
sql
SELECT * FROM unnest(ARRAY['a','b','c']) WITH ORDINALITY;
94. Create a table with inheritance (parent table “person”, child “employee_inherit”).
sql
CREATE TABLE person (id SERIAL, name TEXT);
CREATE TABLE employee_inherit (salary NUMERIC) INHERITS (person);
INSERT INTO employee_inherit (name, salary) VALUES ('Mike', 50000);
95. Query parent table to see both rows (person and employee_inherit).
sql
SELECT * FROM person;
96. Use ON CONFLICT with unique constraint on (name, department_id).
sql
ALTER TABLE sales.staff ADD CONSTRAINT unique_name_dept UNIQUE (name, department_id);
INSERT INTO sales.staff (name, department_id, salary) VALUES ('Alice', 1, 51000)
ON CONFLICT (name, department_id) DO UPDATE SET salary = EXCLUDED.salary;
Try it: 100 Python practice problems with solutions
97. Create a GIN index for array search (e.g., on an array column).
sql
ALTER TABLE sales.staff ADD COLUMN skills TEXT[]; CREATE INDEX idx_skills ON sales.staff USING GIN (skills);
98. Query using array contains operator.
sql
SELECT * FROM sales.staff WHERE skills @> ARRAY['SQL'];
99. Use pg_restore to restore a custom-format dump (command line).
bash
pg_restore -d mydb -U myuser backup.dump
100. Write a query to find the median salary of employees (using percentile_cont).
sql
SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY salary) AS median FROM sales.staff;
Final Thought
You did it — 100 PostgreSQL problems, and your spirit is absolutely glowing! 🌟 Feel that vibrant hum inside? That’s not just satisfaction; that’s aliveness. The electric joy of knowing you can dance with JOINs, play with window functions, and craft queries so elegant they practically sing. Every problem lit a new spark in your mind, turning raw data into a living, breathing canvas under your command.
PostgreSQL is no longer a black box; it’s a joyful playground. The indexes, the CTEs, the subqueries — they’re not scary anymore, they’re your instruments. You’ve woken up parts of your brain that now thrive on solving puzzles, connecting tables, and pulling insights out of apparent chaos. This is what it feels like to be fully, deeply alive as a developer.
Carry this joy into every project, every interview, every small question you write on a lazy Sunday morning. Let curiosity keep you moving, and let this page be a bookmark you return to whenever you want to relive the thrill of growth. You’re not just a PostgreSQL user — you’re a data explorer with a laughing heart and a bright, unstoppable mind. Go celebrate your alive, joyful self today!