SQL Basics: Database Querying Fundamentals

What is SQL?

SQL (Structured Query Language) is for managing relational databases:

• Query data


• Insert, update, delete records


• Define schema


• Manage access

Basic Queries

SELECT - Reading Data

-- All columns
SELECT * FROM users;

-- Specific columns
SELECT name, email FROM users;

-- With alias
SELECT name AS user_name, email AS contact FROM users;

WHERE - Filtering

-- Equality
SELECT * FROM users WHERE status = 'active';

-- Comparison
SELECT * FROM products WHERE price > 100;
SELECT * FROM orders WHERE created_at >= '2025-01-01';

-- Multiple conditions
SELECT * FROM users 
WHERE status = 'active' AND age > 18;

SELECT * FROM products 
WHERE category = 'electronics' OR category = 'computers';

-- NULL checking
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

-- Pattern matching
SELECT * FROM users WHERE name LIKE 'John%';   -- Starts with
SELECT * FROM users WHERE email LIKE '%@gmail.com'; -- Ends with
SELECT * FROM users WHERE name LIKE '%son%';   -- Contains

-- IN clause
SELECT * FROM orders 
WHERE status IN ('pending', 'processing', 'shipped');

ORDER BY - Sorting

-- Ascending (default)
SELECT * FROM users ORDER BY name;

-- Descending
SELECT * FROM products ORDER BY price DESC;

-- Multiple columns
SELECT * FROM users ORDER BY country, name;

LIMIT - Limiting Results

-- First 10
SELECT * FROM users LIMIT 10;

-- Pagination
SELECT * FROM users LIMIT 10 OFFSET 20; -- Items 21-30

Aggregation

COUNT, SUM, AVG, MIN, MAX

-- Count
SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM users WHERE status = 'active';

-- Sum
SELECT SUM(amount) FROM orders;

-- Average
SELECT AVG(price) FROM products;

-- Min/Max
SELECT MIN(price), MAX(price) FROM products;

GROUP BY

-- Count by category
SELECT category, COUNT(*) as count
FROM products
GROUP BY category;

-- Total sales by month
SELECT 
    DATE_TRUNC('month', created_at) as month,
    SUM(amount) as total
FROM orders
GROUP BY DATE_TRUNC('month', created_at);

-- With HAVING (filter groups)
SELECT category, COUNT(*) as count
FROM products
GROUP BY category
HAVING COUNT(*) > 10;

JOINs

INNER JOIN

Only matching rows from both tables:

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

LEFT JOIN

All rows from left, matching from right:

SELECT users.name, orders.amount
FROM users
LEFT JOIN orders ON users.id = orders.user_id;
-- Users without orders will have NULL for amount

Multiple JOINs

SELECT 
    users.name,
    orders.id as order_id,
    products.name as product_name
FROM users
JOIN orders ON users.id = orders.user_id
JOIN order_items ON orders.id = order_items.order_id
JOIN products ON order_items.product_id = products.id;

Modifying Data

INSERT

-- Single row
INSERT INTO users (name, email, status)
VALUES ('Alice', 'alice@example.com', 'active');

-- Multiple rows
INSERT INTO users (name, email, status)
VALUES 
    ('Bob', 'bob@example.com', 'active'),
    ('Charlie', 'charlie@example.com', 'pending');

-- From query
INSERT INTO archived_users (id, name, email)
SELECT id, name, email FROM users WHERE status = 'deleted';

UPDATE

-- Update single row
UPDATE users 
SET status = 'verified' 
WHERE id = 123;

-- Update multiple rows
UPDATE products 
SET price = price * 1.1 
WHERE category = 'electronics';

-- Update with conditions
UPDATE orders 
SET status = 'cancelled'
WHERE status = 'pending' 
AND created_at < '2025-01-01';

DELETE

-- Delete specific row
DELETE FROM users WHERE id = 123;

-- Delete multiple rows
DELETE FROM sessions WHERE expires_at < NOW();

-- Delete all (be careful!)
DELETE FROM temp_data;

Common Patterns

Existence Check

-- Does user exist?
SELECT EXISTS(SELECT 1 FROM users WHERE email = 'alice@example.com');

-- Users with orders
SELECT * FROM users 
WHERE EXISTS (
    SELECT 1 FROM orders WHERE orders.user_id = users.id
);

Subqueries

-- In WHERE
SELECT * FROM products 
WHERE price > (SELECT AVG(price) FROM products);

-- In FROM
SELECT category, avg_price
FROM (
    SELECT category, AVG(price) as avg_price
    FROM products
    GROUP BY category
) subquery
WHERE avg_price > 100;

CASE Expressions

SELECT 
    name,
    price,
    CASE 
        WHEN price < 10 THEN 'cheap'
        WHEN price < 100 THEN 'moderate'
        ELSE 'expensive'
    END as price_tier
FROM products;

COALESCE (NULL Handling)

SELECT 
    name,
    COALESCE(phone, email, 'No contact') as contact
FROM users;

Table Operations

CREATE TABLE

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE

-- Add column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Drop column
ALTER TABLE users DROP COLUMN phone;

-- Modify column
ALTER TABLE users ALTER COLUMN name TYPE VARCHAR(200);

Indexes

-- Create index
CREATE INDEX idx_users_email ON users(email);

-- Create unique index
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- Drop index
DROP INDEX idx_users_email;

Best Practices

Use Parameterized Queries

# Bad - SQL injection risk
query = f"SELECT * FROM users WHERE id = {user_id}"

# Good - parameterized
query = "SELECT * FROM users WHERE id = %s"
cursor.execute(query, (user_id,))

Select Only What You Need

-- Bad
SELECT * FROM users;

-- Good
SELECT id, name, email FROM users;

Use Indexes Wisely

Create indexes for:

• Frequently queried columns


• JOIN conditions


• WHERE conditions


• ORDER BY columns

Avoid N+1 Queries

-- Bad: Query per user
SELECT * FROM users;
-- Then for each user:
SELECT * FROM orders WHERE user_id = ?;

-- Good: Single JOIN
SELECT users.*, orders.*
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

Safety

Always Use WHERE with UPDATE/DELETE

-- Dangerous!
UPDATE users SET status = 'deleted';  -- Updates ALL users

-- Safe
UPDATE users SET status = 'deleted' WHERE id = 123;

Test with SELECT First

-- First, verify what will be affected
SELECT * FROM orders 
WHERE status = 'pending' AND created_at < '2025-01-01';

-- Then delete
DELETE FROM orders 
WHERE status = 'pending' AND created_at < '2025-01-01';

Use Transactions

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- Or ROLLBACK if something went wrong

Conclusion

SQL fundamentals:

• SELECT for reading


• WHERE for filtering


• JOIN for combining tables


• INSERT/UPDATE/DELETE for modifying


• GROUP BY for aggregation

Practice these patterns and you can handle most database work.

---

Next: Database Design - Schema and modeling