Learn SQL - Interactive Tutorial

🗄️ Learn SQL

Master database queries step by step with interactive examples

1. What is SQL? 🤔

SQL (Structured Query Language) is the standard language for managing and querying relational databases. Think of it as a way to ask questions to your data and get structured answers.

Real-world examples:
  • Finding all customers from a specific city
  • Calculating total sales for the month
  • Getting the top 10 best-selling products
  • Updating customer information

📊 Sample Database - Online Store

We'll use these tables to practice SQL queries:

customer_id name email city age
1 John Smith john@email.com New York 28
2 Sarah Johnson sarah@email.com Los Angeles 34
3 Mike Wilson mike@email.com Chicago 29
4 Lisa Brown lisa@email.com New York 31
5 David Davis david@email.com Seattle 26
product_id name category price stock
1 Laptop Electronics 999.99 50
2 T-Shirt Clothing 19.99 100
3 Coffee Mug Kitchen 12.99 75
4 Smartphone Electronics 699.99 30
5 Book Education 24.99 200
order_id customer_id product_id quantity order_date
1 1 1 1 2024-01-15
2 2 2 3 2024-01-16
3 1 3 2 2024-01-17
4 3 4 1 2024-01-18
5 4 5 2 2024-01-19

2. SELECT - The Foundation 📋

SELECT is the most important SQL command. It retrieves data from tables.

Basic SELECT Syntax

SELECT column1, column2, column3 FROM table_name; SELECT * FROM table_name; -- Select all columns

Examples

-- Get all customer information SELECT * FROM customers; -- Get only names and cities SELECT name, city FROM customers; -- Get product names and prices SELECT name, price FROM products;

🎮 Interactive SQL Playground

Try different SQL queries and see the results!

3. WHERE - Filtering Data 🔍

WHERE clause filters rows based on conditions. It's like asking "Show me only the data that meets these criteria."

WHERE with Comparison Operators

SELECT * FROM customers WHERE age > 30; SELECT * FROM products WHERE price <= 50; SELECT * FROM customers WHERE city = 'New York';

WHERE with Multiple Conditions

-- AND: Both conditions must be true SELECT * FROM customers WHERE age > 25 AND city = 'New York'; -- OR: At least one condition must be true SELECT * FROM products WHERE category = 'Electronics' OR price < 20;

4. ORDER BY - Sorting Results 📈

ORDER BY sorts your results in ascending (ASC) or descending (DESC) order.

-- Sort by age (ascending by default) SELECT * FROM customers ORDER BY age; -- Sort by price (descending) SELECT * FROM products ORDER BY price DESC; -- Sort by multiple columns SELECT * FROM customers ORDER BY city, age DESC;

5. LIMIT - Controlling Result Size 🎯

LIMIT restricts how many rows are returned. Perfect for getting "top N" results.

-- Get top 3 most expensive products SELECT * FROM products ORDER BY price DESC LIMIT 3; -- Get youngest 2 customers SELECT * FROM customers ORDER BY age LIMIT 2;

🏆 Challenge Time!

Exercise 1: Find all customers from New York, sorted by age (youngest first)

6. Aggregate Functions - Math with Data 🧮

Aggregate functions perform calculations on multiple rows and return a single result.

COUNT() - Count rows
SUM() - Add up values
AVG() - Average value
MAX() - Highest value
MIN() - Lowest value
-- Count total customers SELECT COUNT(*) FROM customers; -- Average age of customers SELECT AVG(age) FROM customers; -- Most expensive product SELECT MAX(price) FROM products; -- Total value of all products in stock SELECT SUM(price * stock) FROM products;

7. GROUP BY - Grouping Data 📊

GROUP BY groups rows that have the same values and allows you to use aggregate functions on each group.

-- Count customers by city SELECT city, COUNT(*) as customer_count FROM customers GROUP BY city; -- Average price by category SELECT category, AVG(price) as avg_price FROM products GROUP BY category;

🏆 Challenge Time!

Exercise 2: Find the total quantity ordered for each product (hint: use orders table and GROUP BY product_id)

8. JOIN - Connecting Tables 🔗

JOINs are like creating a bridge between tables to combine related information. Think of it as "stitching" tables together using common columns.

🤔 Why Do We Need JOINs?

Look at our orders table - it has customer_id (like 1, 2, 3) and product_id (like 1, 2, 3). But what if we want to see the actual customer names and product names instead of just numbers? That's where JOINs come in!

Without JOIN: "Customer 1 ordered Product 1"
With JOIN: "John Smith ordered a Laptop"

8.1. Understanding Table Relationships 🏗️

Before we JOIN, let's understand how our tables connect:

The Connection Points:

customers.customer_id ↔ orders.customer_id
products.product_id ↔ orders.product_id

Think of these connections like:

  • customers ↔ orders: "Which customer placed which order?"
  • products ↔ orders: "What product was ordered in each order?"

8.2. Your First JOIN - Step by Step 👶

Let's start with the simplest possible JOIN.

Step 1: The Problem

We want to see orders with customer names instead of customer IDs.

Step 2: Identify What We Need

  • Data from orders table (order_date, quantity)
  • Data from customers table (name)
  • Connection: customer_id (exists in both tables)

Step 3: Write the JOIN

SELECT customers.name, orders.order_date, orders.quantity FROM customers JOIN orders ON customers.customer_id = orders.customer_id;

Step 4: What This Does

SQL looks at each row in orders, finds the matching customer_id in customers, and combines the information into one result row.

8.3. Breaking Down JOIN Syntax 🔍

The Anatomy of a JOIN

SELECT [columns you want] FROM [first_table] JOIN [second_table] ON [connection_condition];
SELECT: Choose columns from ANY of the joined tables
FROM: Your "main" or "left" table
JOIN: The second table you want to connect
ON: How the tables connect (the matching condition)

8.4. Table Aliases - Making Life Easier 📝

Instead of writing full table names, we can use short aliases:

Without Aliases (Verbose)

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

With Aliases (Clean & Easy)

SELECT c.name, o.order_date FROM customers c JOIN orders o ON c.customer_id = o.customer_id;

Pro Tip: Use meaningful aliases like 'c' for customers, 'p' for products, 'o' for orders. Much cleaner!

🎮 JOIN Practice Playground

Try these JOIN examples step by step:

Example 1: Orders with Customer Names

Example 2: Orders with Product Names

8.5. The Triple JOIN - Connecting Three Tables 🎯

Now for the really powerful stuff - let's connect all three tables!

The Challenge

We want to see: Customer Name + Product Name + Order Details

The Solution

SELECT c.name as customer_name, p.name as product_name, o.quantity, o.order_date FROM customers c JOIN orders o ON c.customer_id = o.customer_id JOIN products p ON o.product_id = p.product_id;

How It Works

Step 1: Start with customers table
Step 2: Join orders (connect via customer_id)
Step 3: Join products (connect via product_id)
Result: One big table with all related information!

8.6. Different Types of JOINs 🎭

So far we've used INNER JOIN (the default). Let's explore the family:

INNER JOIN
Only shows matches from both tables
LEFT JOIN
Shows all from left table + matches from right
RIGHT JOIN
Shows all from right table + matches from left
FULL OUTER JOIN
Shows everything from both tables

🍎 Real-World Analogy

Imagine you have a list of students and a list of their grades:

  • INNER JOIN: Show only students who have grades
  • LEFT JOIN: Show all students, even if they don't have grades yet
  • RIGHT JOIN: Show all grades, even if student info is missing
  • FULL OUTER JOIN: Show everything - all students and all grades

🏆 JOIN Challenge!

Exercise 3: Create a query that shows customer names along with the product names they ordered. Use aliases to make it clean!

8.7. JOIN + WHERE + ORDER BY 🎪

JOINs become really powerful when combined with other SQL features:

Example: Find all electronics orders by customers from New York

SELECT c.name as customer, p.name as product, o.order_date FROM customers c JOIN orders o ON c.customer_id = o.customer_id JOIN products p ON o.product_id = p.product_id WHERE c.city = 'New York' AND p.category = 'Electronics' ORDER BY o.order_date DESC;

Reading this query: "Show me customer and product names for electronics orders placed by New York customers, with the most recent orders first."

8.8. Common JOIN Mistakes & How to Avoid Them ⚠️

❌ Mistake 1: Forgetting the ON clause

-- WRONG - This will create a cartesian product (every row matched with every row) SELECT * FROM customers, orders; -- RIGHT - Always specify how tables connect SELECT * FROM customers c JOIN orders o ON c.customer_id = o.customer_id;

❌ Mistake 2: Ambiguous column names

-- WRONG - SQL doesn't know which table's 'name' you want SELECT name FROM customers c JOIN products p ON ...; -- RIGHT - Be specific with table aliases SELECT c.name FROM customers c JOIN products p ON ...;

✅ Pro Tips

  • Always use aliases - makes queries cleaner and less error-prone
  • Start simple - join two tables first, then add more
  • Check your ON conditions - make sure they make logical sense
  • Use meaningful column names - like 'customer_name' instead of just 'name'

📚 Quick Reference

SELECT - Retrieve data
FROM - Specify table
WHERE - Filter rows
ORDER BY - Sort results
LIMIT - Limit results
GROUP BY - Group rows
JOIN - Connect tables
COUNT/SUM/AVG - Aggregate functions

🎯 SQL Query Order:

SELECT → FROM → JOIN → WHERE → GROUP BY → ORDER BY → LIMIT

9. Production-Ready SQL for Express.js + PostgreSQL 🚀

Ready to build real applications? Here's what you need to know for production APIs:

9.1. SQL Injection Prevention 🛡️

THE MOST IMPORTANT CONCEPT: Never trust user input in SQL queries!

❌ DANGEROUS - Never Do This!

// DON'T DO THIS - Vulnerable to SQL injection const userId = req.params.id; // Could be: "1; DROP TABLE users; --" const query = `SELECT * FROM users WHERE id = ${userId}`; db.query(query); // 💥 Your database could be destroyed!

✅ SAFE - Always Use Parameterized Queries

// SAFE - PostgreSQL with pg library const userId = req.params.id; const query = 'SELECT * FROM users WHERE id = $1'; const result = await db.query(query, [userId]); // SAFE - Using an ORM like Prisma const user = await prisma.user.findUnique({ where: { id: parseInt(userId) } });
⚠️ Security Rule #1: ALWAYS use parameterized queries ($1, $2, etc.) or ORMs. Never concatenate user input directly into SQL strings!

9.2. Database Connections & Connection Pooling 🏊‍♂️

Managing database connections efficiently is crucial for performance.

Connection Pool Setup (pg library)

const { Pool } = require('pg'); const pool = new Pool({ host: process.env.DB_HOST, port: process.env.DB_PORT, database: process.env.DB_NAME, user: process.env.DB_USER, password: process.env.DB_PASSWORD, max: 20, // Maximum number of connections idleTimeoutMillis: 30000, connectionTimeoutMillis: 2000, }); // Using the pool const getUsers = async () => { try { const result = await pool.query('SELECT * FROM users'); return result.rows; } catch (error) { console.error('Database error:', error); throw error; } };
Why Connection Pooling?
• Reuses existing connections instead of creating new ones
• Prevents "too many connections" errors
• Much better performance under load

9.3. Transactions - All or Nothing 💳

When you need multiple queries to succeed together (like transferring money).

Example: Creating an Order with Items

const createOrderWithItems = async (customerId, items) => { const client = await pool.connect(); try { await client.query('BEGIN'); // Start transaction // Create the order const orderResult = await client.query( 'INSERT INTO orders (customer_id, order_date) VALUES ($1, NOW()) RETURNING id', [customerId] ); const orderId = orderResult.rows[0].id; // Add each item for (const item of items) { await client.query( 'INSERT INTO order_items (order_id, product_id, quantity) VALUES ($1, $2, $3)', [orderId, item.product_id, item.quantity] ); // Update product stock await client.query( 'UPDATE products SET stock = stock - $1 WHERE id = $2', [item.quantity, item.product_id] ); } await client.query('COMMIT'); // Save all changes return orderId; } catch (error) { await client.query('ROLLBACK'); // Undo all changes throw error; } finally { client.release(); // Return connection to pool } };

Transactions ensure: Either ALL queries succeed, or NONE of them do. No half-completed operations!

9.4. Database Indexes - Making Queries Fast ⚡

Indexes are like a book's table of contents - they help find data quickly.

Creating Indexes

-- Create index on frequently queried columns CREATE INDEX idx_customers_email ON customers(email); CREATE INDEX idx_orders_customer_id ON orders(customer_id); CREATE INDEX idx_orders_date ON orders(order_date); -- Composite index for multiple column searches CREATE INDEX idx_products_category_price ON products(category, price);

When to Use Indexes

  • ✅ Columns used in WHERE clauses frequently
  • ✅ Foreign key columns (customer_id, product_id)
  • ✅ Columns used for JOINs
  • ❌ Don't over-index - they slow down INSERT/UPDATE

9.5. Pagination - Handling Large Results 📄

Never return thousands of rows at once. Use pagination!

LIMIT + OFFSET Pagination

// GET /api/users?page=2&limit=10 const getUsers = async (page = 1, limit = 10) => { const offset = (page - 1) * limit; const result = await pool.query( 'SELECT * FROM users ORDER BY created_at DESC LIMIT $1 OFFSET $2', [limit, offset] ); const countResult = await pool.query('SELECT COUNT(*) FROM users'); const total = parseInt(countResult.rows[0].count); return { users: result.rows, pagination: { page, limit, total, totalPages: Math.ceil(total / limit) } }; };

Cursor-Based Pagination (Better for Performance)

// For large datasets, use cursor-based pagination const getUsersAfter = async (cursor = null, limit = 10) => { let query = 'SELECT * FROM users'; let params = [limit]; if (cursor) { query += ' WHERE id > $2'; params.push(cursor); } query += ' ORDER BY id LIMIT $1'; const result = await pool.query(query, params); return result.rows; };

9.6. Data Validation & Sanitization 🧹

Always validate data before it reaches your database.

Using Joi for Validation

const Joi = require('joi'); const userSchema = Joi.object({ name: Joi.string().min(2).max(50).required(), email: Joi.string().email().required(), age: Joi.number().integer().min(1).max(150) }); // In your Express route app.post('/api/users', async (req, res) => { try { // Validate input const { error, value } = userSchema.validate(req.body); if (error) { return res.status(400).json({ error: error.details[0].message }); } // Safe to insert into database const result = await pool.query( 'INSERT INTO users (name, email, age) VALUES ($1, $2, $3) RETURNING *', [value.name, value.email, value.age] ); res.json(result.rows[0]); } catch (error) { res.status(500).json({ error: 'Internal server error' }); } });

9.7. Error Handling & Logging 📝

Proper error handling is essential for debugging and user experience.

Comprehensive Error Handling

const getUser = async (req, res) => { try { const userId = parseInt(req.params.id); // Validate ID if (isNaN(userId)) { return res.status(400).json({ error: 'Invalid user ID' }); } const result = await pool.query( 'SELECT * FROM users WHERE id = $1', [userId] ); if (result.rows.length === 0) { return res.status(404).json({ error: 'User not found' }); } res.json(result.rows[0]); } catch (error) { console.error('Error fetching user:', error); // Don't expose internal errors to clients res.status(500).json({ error: 'Internal server error', ...(process.env.NODE_ENV === 'development' && { details: error.message }) }); } };

9.8. Database Migrations 🔄

Version control for your database schema changes.

Example Migration Files

-- migrations/001_create_users_table.sql CREATE TABLE users ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(255) UNIQUE NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- migrations/002_add_user_age.sql ALTER TABLE users ADD COLUMN age INTEGER; CREATE INDEX idx_users_age ON users(age); -- migrations/003_create_orders_table.sql CREATE TABLE orders ( id SERIAL PRIMARY KEY, user_id INTEGER REFERENCES users(id), total DECIMAL(10,2) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
Migration Tools:
• node-pg-migrate: Simple SQL migrations
• Knex.js: Query builder with migrations
• Prisma: Modern ORM with built-in migrations

🏆 Production Checklist!

Before deploying your Express.js + PostgreSQL API:

✅ Security
  • Use parameterized queries
  • Validate all input data
  • Use HTTPS in production
  • Environment variables for secrets
✅ Performance
  • Connection pooling setup
  • Database indexes created
  • Pagination implemented
  • Query optimization
✅ Reliability
  • Transaction handling
  • Proper error handling
  • Database migrations
  • Logging setup
✅ Monitoring
  • Query performance monitoring
  • Error tracking
  • Database health checks
  • Backup strategy

9.9. Recommended Libraries & Tools 🛠️

pg
PostgreSQL client for Node.js
Prisma
Modern ORM with type safety
Joi / Yup
Data validation libraries
node-pg-migrate
Database migrations
Winston
Logging library
Helmet
Security middleware
🎯 Next Steps:
1. Set up a simple Express.js app with PostgreSQL
2. Implement one CRUD endpoint with proper validation
3. Add connection pooling and error handling
4. Create your first database migration
5. Write tests for your endpoints!