SQL & Relational Databases
Master SQL for querying and managing relational databases - SELECT, JOINs, indexes, and transactions.
0 of 5 concepts completed (0%)
What You'll Learn
Tutorial Contents
1.Core SQL Operations
-- CREATE TABLE with constraints
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
stock INTEGER DEFAULT 0 CHECK (stock >= 0),
category_id INTEGER REFERENCES categories(id) ON DELETE SET NULL,
created_at TIMESTAMPTZ DEFAULT NOW()CREATE INDEX idx_products_category ON products(category_id); CREATE INDEX idx_products_available ON products(price) WHERE stock > 0;
-- SELECT with filtering and pagination SELECT id, name, price, stock FROM products WHERE category_id = 3 AND price BETWEEN 10 AND 100 AND stock > 0 ORDER BY price ASC LIMIT 20 OFFSET 40;
-- INSERT with returning INSERT INTO products (name, price, category_id) VALUES ('Wireless Mouse', 29.99, 3) RETURNING id, name, created_at;
-- UPSERT INSERT INTO user_settings (user_id, theme) VALUES (42, 'dark') ON CONFLICT (user_id) DO UPDATE SET theme = EXCLUDED.theme; ```
2.JOINs & Relationships
-- INNER JOIN only matching rows
SELECT
u.name AS customer,
p.name AS product,
oi.quantity,
oi.quantity * p.price AS line_total
FROM orders o
INNER JOIN users u ON o.user_id = u.id
INNER JOIN order_items oi ON oi.order_id = o.id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.status = 'completed'-- LEFT JOIN all left rows, nulls for unmatched SELECT u.name, COUNT(o.id) AS order_count, COALESCE(SUM(o.total), 0) AS lifetime_value FROM users u LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed' GROUP BY u.id, u.name ORDER BY lifetime_value DESC;
-- Self JOIN SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id; ```
3.Window Functions & CTEs
-- Window functions
SELECT
name, department, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
LAG(salary) OVER (PARTITION BY department ORDER BY hire_date) AS prev_salary,
ROUND(salary / SUM(salary) OVER (PARTITION BY department) * 100, 1) AS pct_of_dept-- CTE for readability WITH monthly_revenue AS ( SELECT DATE_TRUNC('month', created_at) AS month, SUM(total) AS revenue FROM orders WHERE status = 'completed' GROUP BY 1 ), revenue_growth AS ( SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS prev_revenue FROM monthly_revenue ) SELECT to_char(month, 'Mon YYYY') AS period, revenue, ROUND((revenue - prev_revenue) / prev_revenue * 100, 1) AS growth_pct FROM revenue_growth WHERE prev_revenue IS NOT NULL ORDER BY month; ```
Code Example
-- Create table
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INT NOT NULL,
amount DECIMAL(10,2),
created_at TIMESTAMP DEFAULT NOW()
);
-- JOIN query
SELECT u.name, COUNT(o.id) AS order_count, SUM(o.amount) AS total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 0
ORDER BY total DESC;
-- Window function
SELECT name, amount,
RANK() OVER (ORDER BY amount DESC) AS rank
FROM orders;Pro Tips
- 1Never construct SQL queries with string concatenation using user input always use parameterized queries ($1, $2) to prevent SQL injection.
- 2EXPLAIN ANALYZE is your best friend. Run it before and after adding an index to verify the query planner actually uses it.
- 3Composite indexes follow the leftmost prefix rule: (user_id, created_at) helps queries on user_id alone, but NOT created_at alone.
- 4Avoid SELECT * in production. Explicitly naming columns reduces data transfer and prevents breaks when schema changes.
Topics Covered
Continue Learning
Explore the official documentation and deepen your knowledge with real-world practice.
Explore the Full Portfolio
This is the complete portfolio of Sufi Khan Sulaiman, a technology leader specialising in B2B commerce and digital automation. Start from the Home page for the overview, then move through two decades of career experience across FLIR Systems, Lorex Technology, and 1c Platform, and the full catalogue of project case studies spanning headless commerce migrations, AI recommendation engines, and multi-channel fulfilment systems.
The skills and certifications page maps the technical and leadership capabilities behind the work, while the articles and the knowledge base break down the thinking into actionable frameworks. For hands-on learning, the tutorials and applications sections cover practical builds from front-end fundamentals to full-stack web apps.
For consulting engagement, the expertise page outlines service offerings, the ecommerce hub covers platform architecture and automation strategy, and the ecommerce guide (PDF) is a downloadable 55-page field manual. When you are ready to talk, the contact page is the direct line.