SQL
MASTERY

What is SQL?

SQL (Structured Query Language) is the standard language for managing and manipulating relational databases. It allows you to create, read, update, and delete data, as well as manage database structure.

Key Concepts

• Database: A structured collection of data
• Table: A collection of related data in rows and columns
• Row: A single record in a table
• Column: A field in a table
• Primary Key: Unique identifier for each row

Your First Query

-- Select all columns from a table
SELECT * FROM users;

-- Select specific columns
SELECT name, email FROM users;

Quiz

The SELECT Statement

SELECT is used to retrieve data from a database. You can filter, sort, and limit results.

WHERE Clause

-- Filter by condition
SELECT * FROM users WHERE age >= 18;

-- Multiple conditions
SELECT * FROM users WHERE country = 'USA' AND age > 21;

-- OR condition
SELECT * FROM products WHERE price < 10 OR price > 100;

ORDER BY

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

-- Sort descending
SELECT * FROM users ORDER BY created_at DESC;

-- Sort by multiple columns
SELECT * FROM users ORDER BY country ASC, name ASC;

LIMIT

-- Get top 10 results
SELECT * FROM users ORDER BY score DESC LIMIT 10;

Quiz

Common Data Types

• INT/INTEGER: Whole numbers
• VARCHAR(n): Variable-length string
• TEXT: Long text
• DECIMAL(p,s): Exact numeric
• DATE: Date (YYYY-MM-DD)
• TIMESTAMP: Date and time
• BOOLEAN: True/False

CREATE TABLE

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE

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

-- Drop a column
ALTER TABLE users DROP COLUMN phone;

-- Modify a column
ALTER TABLE users MODIFY COLUMN age INT NOT NULL;

Quiz

INSERT - Adding Data

-- Insert a 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 - Modifying Data

-- Update with condition
UPDATE users SET age = 26 WHERE name = 'Alice';

-- Update multiple columns
UPDATE users SET age = 27, city = 'NYC' WHERE id = 1;

DELETE - Removing Data

-- Delete with condition
DELETE FROM users WHERE id = 5;

-- Delete all rows (use with caution!)
DELETE FROM users;

Quiz

What are Joins?

Joins combine data from multiple tables based on related columns.

INNER JOIN

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

LEFT JOIN

-- Includes all users, even without orders
SELECT users.name, orders.total
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

Multiple Joins

SELECT users.name, products.name AS product, orders.quantity
FROM users
JOIN orders ON users.id = orders.user_id
JOIN products ON orders.product_id = products.id;

Quiz

Aggregate Functions

• COUNT(): Count rows
• SUM(): Sum values
• AVG(): Average value
• MIN(): Minimum value
• MAX(): Maximum value

COUNT Examples

-- Count all rows
SELECT COUNT(*) FROM users;

-- Count non-null values
SELECT COUNT(email) FROM users;

GROUP BY

-- Count users per country
SELECT country, COUNT(*) as user_count
FROM users
GROUP BY country;

HAVING

-- Filter after grouping
SELECT country, COUNT(*) as user_count
FROM users
GROUP BY country
HAVING user_count > 10;

Quiz

What are Subqueries?

A subquery is a query nested inside another query. They allow you to use results from one query in another.

Subquery in WHERE

-- Find users who ordered more than average
SELECT name FROM users
WHERE id IN (SELECT user_id FROM orders 
             GROUP BY user_id 
             HAVING SUM(total) > (SELECT AVG(order_total) FROM (SELECT SUM(total) as order_total FROM orders GROUP BY user_id) AS avg_order));

Subquery in FROM

-- Use subquery as a table
SELECT country, avg_age FROM (
    SELECT country, AVG(age) as avg_age
    FROM users
    GROUP BY country
) AS country_stats
WHERE avg_age > 30;

Correlated Subqueries

-- Find each user's latest order
SELECT * FROM orders o1
WHERE order_date = (
    SELECT MAX(order_date) 
    FROM orders o2 
    WHERE o1.user_id = o2.user_id
);

Quiz

What are Indexes?

Indexes are data structures that improve query speed. Think of them like a book index - they help you find information faster.

CREATE INDEX

-- Single column index
CREATE INDEX idx_users_email ON users(email);

-- Composite index
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

-- Unique index
CREATE UNIQUE INDEX idx_users_phone ON users(phone);

When to Index

• Columns used in WHERE clauses
• Columns used in JOIN conditions
• Columns used in ORDER BY
• High-cardinality columns (many unique values)

EXPLAIN

-- See how a query executes
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';

Quiz

Normalization

Normalization organizes data to reduce redundancy and improve integrity.

First Normal Form (1NF)

• Each column contains atomic values
• Each column contains values of a single type
• Each row is unique

Second Normal Form (2NF)

• Must be in 1NF
• No partial dependencies (non-key columns depend on entire primary key)

Third Normal Form (3NF)

• Must be in 2NF
• No transitive dependencies (non-key columns depend only on the primary key)

Example: Denormalized vs Normalized

-- Bad: Repeating data
orders(order_id, customer_name, customer_email, product, price)

-- Good: Normalized
customers(customer_id, name, email)
products(product_id, name, price)
orders(order_id, customer_id, product_id)

Quiz

Types of Constraints

• PRIMARY KEY: Unique identifier, cannot be NULL
• FOREIGN KEY: References another table's PRIMARY KEY
• UNIQUE: All values must be different
• NOT NULL: Column must have a value
• CHECK: Values must meet a condition
• DEFAULT: Value if none provided

PRIMARY KEY

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

FOREIGN KEY

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    total DECIMAL(10,2),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CHECK Constraint

CREATE TABLE products (
    id INT PRIMARY KEY,
    price DECIMAL(10,2) CHECK (price > 0),
    quantity INT CHECK (quantity >= 0)
);

Quiz

ACID Properties

• Atomicity: All succeed or all fail
• Consistency: Data stays valid
• Isolation: Concurrent transactions don't interfere
• Durability: Committed data is saved

Transaction Commands

-- Start transaction
BEGIN TRANSACTION;

-- Make changes
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

-- Save changes
COMMIT;

-- Or undo changes
ROLLBACK;

Savepoints

BEGIN;
UPDATE users SET name = 'Alice' WHERE id = 1;
SAVEPOINT sp1;

UPDATE users SET name = 'Bob' WHERE id = 2;
ROLLBACK TO SAVEPOINT sp1;

COMMIT;

Quiz

Views

A virtual table based on a query. Views simplify complex queries and restrict access to data.

-- Create view
CREATE VIEW active_users AS
SELECT name, email FROM users WHERE active = 1;

-- Use view like a table
SELECT * FROM active_users;

-- Drop view
DROP VIEW active_users;

Stored Procedures

Precompiled SQL code that can be executed as a single unit.

-- Create procedure (MySQL)
DELIMITER //
CREATE PROCEDURE get_user_orders(IN user_id INT)
BEGIN
    SELECT * FROM orders WHERE user_id = user_id;
END //
DELIMITER ;

-- Call procedure
CALL get_user_orders(1);

Quiz

Congratulations!

You've completed the SQL Mastery guide. You now understand:

• Basic queries with SELECT
• Filtering with WHERE
• Creating and modifying tables
• Data manipulation (INSERT, UPDATE, DELETE)
• Joining tables
• Aggregations and grouping
• Subqueries
• Indexes and performance
• Database design and normalization
• Constraints and keys
• Transactions
• Views and stored procedures