PostgreSQL: Querying and Data Manipulation

SQL queries is the art of getting data from a database without accidentally triggering a full-table scan that brings the server to its knees. If you’ve ever felt like your queries are taking longer than your coffee break, this guide is for you.

Advanced SELECT Queries#

Selecting specific columns vs. selecting all columns#

Avoid SELECT * unless you enjoy performance nightmares.

-- Bad (because it fetches everything, even columns you don't need)
SELECT * FROM customers;

-- Good (select only what you actually need)
SELECT id, name, email FROM customers;

Using column aliases for readability#

SELECT first_name AS "First Name", last_name AS "Last Name" FROM employees;

Tip: Use aliases wisely; don’t name columns something misleading like salary AS rent_money (unless you like chaos).

Expressions and calculations in SELECT statements#

SELECT name, price, price * 0.9 AS discounted_price FROM products;

Because who doesn’t love a good discount, especially when it’s calculated on the fly?

Filtering Data with WHERE, GROUP BY, and HAVING#

Using WHERE to filter records#

SELECT * FROM orders WHERE order_status = 'shipped';

Grouping data using GROUP BY#

SELECT category, COUNT(*) FROM products GROUP BY category;

Filtering grouped results using HAVING#

SELECT category, COUNT(*) FROM products GROUP BY category HAVING COUNT(*) > 5;

Warning: HAVING is like WHERE, but for grouped data. Don’t mix them up, or you’ll end up debugging for hours.

Sorting and Limiting Results#

Ordering results with ORDER BY#

SELECT name, price FROM products ORDER BY price DESC;

Using LIMIT and OFFSET for pagination#

SELECT * FROM customers ORDER BY id LIMIT 10 OFFSET 20;

Because fetching a million rows in one go is a fantastic way to make your database cry.

JOIN Operations#

Ah, joins—the SQL equivalent of a complicated relationship. Let’s break it down.

INNER JOIN#

SELECT orders.id, customers.name FROM orders INNER JOIN customers ON orders.customer_id = customers.id;

LEFT JOIN (aka: Give me everything from the left, even if there’s no match on the right)#

SELECT customers.name, orders.id FROM customers LEFT JOIN orders ON customers.id = orders.customer_id;

RIGHT JOIN#

SELECT customers.name, orders.id FROM customers RIGHT JOIN orders ON customers.id = orders.customer_id;

FULL JOIN (because sometimes you just need everything)#

SELECT customers.name, orders.id FROM customers FULL JOIN orders ON customers.id = orders.customer_id;

Tip: Use joins wisely—joining five tables at once can feel like summoning a SQL demon.

Subqueries and Common Table Expressions (CTEs)#

Writing subqueries inside SELECT, FROM, and WHERE#

SELECT name, (SELECT AVG(price) FROM products) AS avg_price FROM products;

Understanding correlated vs. non-correlated subqueries#

-- Correlated subquery
SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees WHERE department = employees.department);

Using WITH for Common Table Expressions (CTEs)#

WITH recent_orders AS (
    SELECT * FROM orders WHERE order_date > CURRENT_DATE - INTERVAL '30 days'
)
SELECT * FROM recent_orders;

Recursive CTEs for hierarchical data#

WITH RECURSIVE employee_hierarchy AS (
    SELECT id, name, manager_id FROM employees WHERE id = 1
    UNION ALL
    SELECT e.id, e.name, e.manager_id FROM employees e INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;

Because sometimes you need to fetch your boss’s boss’s boss (probably to figure out who to blame for the latest deadline change).

Hands-On Exercise#

1. Execute complex SELECT queries: