SQL Basics: A Practical Guide for Beginners

SQL (Structured Query Language) is essential for working with data. This guide covers the fundamental operations you need to query and manipulate data in any relational database.

The SELECT Statement

SELECT retrieves data from one or more tables:

-- Get all columns from users
SELECT * FROM users;

-- Get specific columns
SELECT id, name, email FROM users;

-- Rename columns in output
SELECT name AS "User Name", email AS "Email Address" FROM users;

Filtering with WHERE

Filter results with conditions:

SELECT * FROM users WHERE age >= 18;
SELECT * FROM products WHERE price > 100 AND category = 'Electronics';
SELECT * FROM orders WHERE status = 'pending' OR status = 'processing';

Common operators:

• = Equal
• != or <> Not equal
• >, <, >=, <= Comparison
• LIKE Pattern matching
• IN Match any value in list
• BETWEEN Range check

-- Pattern matching with LIKE
SELECT * FROM users WHERE name LIKE 'A%';      -- names starting with A
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- IN clause
SELECT * FROM products WHERE category IN ('Electronics', 'Books', 'Clothing');

-- BETWEEN
SELECT * FROM orders WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

Sorting and Limiting

-- Sort by name ascending (default)
SELECT * FROM users ORDER BY name;

-- Sort by age descending
SELECT * FROM users ORDER BY age DESC;

-- Multiple columns
SELECT * FROM products ORDER BY category, price DESC;

-- Limit results
SELECT * FROM products ORDER BY price ASC LIMIT 10;  -- 10 cheapest

Aggregation with GROUP BY

Aggregate functions calculate summary statistics:

-- Count users by city
SELECT city, COUNT(*) as user_count
FROM users
GROUP BY city;

-- Average order value by customer
SELECT customer_id, AVG(total) as avg_order_value
FROM orders
GROUP BY customer_id;

Common aggregate functions:

• COUNT() — Count rows
• SUM() — Add values
• AVG() — Average value
• MIN() — Minimum value
• MAX() — Maximum value

Filter aggregated results with HAVING (WHERE doesn't work with aggregates):

SELECT city, COUNT(*) as count
FROM users
GROUP BY city
HAVING count > 100;  -- only cities with over 100 users

Joins: Combining Tables

INNER JOIN

Returns only rows with matches in both tables:

SELECT orders.id, users.name, orders.total
FROM orders
INNER JOIN users ON orders.user_id = users.id;

LEFT JOIN

Returns all rows from the left table, matching rows from right (or NULL if no match):

SELECT users.name, orders.total
FROM users
LEFT JOIN orders ON users.id = orders.user_id;
-- Shows all users, even those with no orders (orders.total = NULL)

Multiple Joins

SELECT o.id, u.name, p.title as product_name, oi.quantity
FROM orders o
INNER JOIN users u ON o.user_id = u.id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;

Inserting, Updating, Deleting

INSERT

-- Insert single row
INSERT INTO users (name, email, age) VALUES ('Alice', 'alice@example.com', 25);

-- Insert multiple rows
INSERT INTO users (name, email) VALUES
  ('Bob', 'bob@example.com'),
  ('Charlie', 'charlie@example.com');

UPDATE

-- Update single row
UPDATE users SET email = 'newemail@example.com' WHERE id = 1;

-- Update multiple columns
UPDATE users SET age = 26, city = 'New York' WHERE id = 1;

-- Update based on condition
UPDATE orders SET status = 'cancelled' WHERE created_at < '2023-01-01';

DELETE

-- Delete specific rows
DELETE FROM users WHERE id = 1;

-- Delete all rows (careful!)
DELETE FROM users;

Always include WHERE in UPDATE and DELETE statements. Missing it affects ALL rows.

Practical Examples

Find top 5 customers by total spending

SELECT u.name, SUM(o.total) as total_spent
FROM users u
INNER JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
ORDER BY total_spent DESC
LIMIT 5;

Find orders with their item count

SELECT o.id, o.created_at, COUNT(oi.id) as item_count
FROM orders o
LEFT JOIN order_items oi ON o.id = oi.order_id
GROUP BY o.id, o.created_at;