SQL (Structured Query Language) is used to manage relational databases. A database is a structured collection of data organized in tables with rows and columns.

Key Concepts:

Table: Collection of related data organized in rows and columns
Row: A single record/entry in a table
Column: A field in a table (e.g., name, email)
Primary Key: Unique identifier for each row
Foreign Key: Links to primary key in another table
Index: Speeds up data retrieval

Popular Databases:

MySQL: Open-source, widely used, easy to learn
PostgreSQL: Powerful, advanced features, reliable
SQLite: Lightweight, file-based, great for learning
Oracle: Enterprise-grade, expensive
SQL Server: Microsoft's database platform

Creating Databases

-- Create database
CREATE DATABASE college;
USE college;

-- Create table
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT CHECK (age >= 18),
    gpa FLOAT DEFAULT 3.0,
    enrollment_date DATE
);

-- Create another table
CREATE TABLE courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(100) NOT NULL,
    credits INT,
    professor_id INT,
    FOREIGN KEY (professor_id) REFERENCES professors(id)
);

-- Create junction table for many-to-many
CREATE TABLE student_courses (
    student_id INT,
    course_id INT,
    grade CHAR(1),
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

-- Alter table
ALTER TABLE students ADD COLUMN phone VARCHAR(20);
ALTER TABLE students DROP COLUMN phone;

-- Drop table
DROP TABLE students;

SELECT Queries

-- Basic SELECT
SELECT * FROM students;
SELECT name, email FROM students;

-- WHERE clause
SELECT * FROM students WHERE age > 20;
SELECT * FROM students WHERE name LIKE 'J%';  -- Starts with J
SELECT * FROM students WHERE gpa >= 3.5;

-- AND, OR, NOT
SELECT * FROM students WHERE age > 20 AND gpa > 3.0;
SELECT * FROM students WHERE age > 30 OR gpa > 3.8;
SELECT * FROM students WHERE NOT age < 18;

-- ORDER BY
SELECT * FROM students ORDER BY name;        -- Ascending
SELECT * FROM students ORDER BY gpa DESC;    -- Descending

-- LIMIT and OFFSET
SELECT * FROM students LIMIT 10;
SELECT * FROM students LIMIT 10 OFFSET 5;    -- Skip first 5

-- DISTINCT
SELECT DISTINCT age FROM students;

-- BETWEEN
SELECT * FROM students WHERE age BETWEEN 18 AND 25;

-- IN
SELECT * FROM students WHERE id IN (1, 3, 5);

-- NULL check
SELECT * FROM students WHERE email IS NULL;
SELECT * FROM students WHERE email IS NOT NULL;

INSERT, UPDATE, DELETE

-- INSERT single row
INSERT INTO students (name, email, age, gpa)
VALUES ('John Doe', 'john@example.com', 20, 3.5);

-- INSERT multiple rows
INSERT INTO students (name, email, age, gpa) VALUES
('Alice', 'alice@example.com', 21, 3.8),
('Bob', 'bob@example.com', 19, 3.2),
('Carol', 'carol@example.com', 22, 3.9);

-- UPDATE
UPDATE students SET gpa = 3.7 WHERE id = 1;
UPDATE students SET age = age + 1;  -- Increment all ages

-- UPDATE with condition
UPDATE students SET gpa = 4.0 WHERE name = 'John Doe';

-- DELETE
DELETE FROM students WHERE id = 5;
DELETE FROM students WHERE age < 18;

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

-- TRUNCATE (faster than DELETE)
TRUNCATE TABLE students;

Joins

-- INNER JOIN (only matching rows)
SELECT students.name, courses.course_name
FROM students
INNER JOIN student_courses ON students.id = student_courses.student_id
INNER JOIN courses ON student_courses.course_id = courses.course_id;

-- LEFT JOIN (all from left table)
SELECT s.name, c.course_name
FROM students s
LEFT JOIN student_courses sc ON s.id = sc.student_id
LEFT JOIN courses c ON sc.course_id = c.course_id;

-- RIGHT JOIN (all from right table)
SELECT s.name, c.course_name
FROM students s
RIGHT JOIN student_courses sc ON s.id = sc.student_id;

-- FULL JOIN (all from both tables)
SELECT s.name, c.course_name
FROM students s
FULL JOIN student_courses sc ON s.id = sc.student_id
FULL JOIN courses c ON sc.course_id = c.course_id;

-- CROSS JOIN (all combinations)
SELECT s.name, c.course_name
FROM students s
CROSS JOIN courses c;

-- Self JOIN
SELECT a.name AS student1, b.name AS student2
FROM students a
JOIN students b ON a.age = b.age AND a.id < b.id;

Aggregation Functions

-- COUNT
SELECT COUNT(*) FROM students;
SELECT COUNT(DISTINCT age) FROM students;

-- SUM, AVG, MAX, MIN
SELECT AVG(gpa) FROM students;
SELECT MAX(gpa), MIN(gpa) FROM students;
SELECT SUM(credits) FROM courses;

-- GROUP BY
SELECT age, COUNT(*) FROM students GROUP BY age;
SELECT age, AVG(gpa) FROM students GROUP BY age;

-- HAVING (filter groups)
SELECT age, COUNT(*) as count
FROM students
GROUP BY age
HAVING COUNT(*) > 2;

-- GROUP BY with JOIN
SELECT c.course_name, COUNT(sc.student_id) as enrollment
FROM courses c
LEFT JOIN student_courses sc ON c.course_id = sc.course_id
GROUP BY c.course_id, c.course_name;

-- Complex aggregation
SELECT 
    age,
    COUNT(*) as total_students,
    AVG(gpa) as avg_gpa,
    MAX(gpa) as highest_gpa
FROM students
GROUP BY age
ORDER BY avg_gpa DESC;

Subqueries

-- Subquery in WHERE
SELECT * FROM students
WHERE gpa > (SELECT AVG(gpa) FROM students);

-- Subquery with IN
SELECT * FROM students
WHERE age IN (SELECT age FROM students WHERE gpa > 3.5);

-- Subquery in FROM
SELECT age, avg_gpa FROM (
    SELECT age, AVG(gpa) as avg_gpa
    FROM students
    GROUP BY age
) as age_stats
WHERE avg_gpa > 3.0;

-- Correlated subquery
SELECT * FROM students s
WHERE gpa > (
    SELECT AVG(gpa) FROM students
    WHERE age = s.age
);

-- EXISTS
SELECT * FROM students s
WHERE EXISTS (
    SELECT 1 FROM student_courses sc
    WHERE sc.student_id = s.id
);

Indexes & Performance

-- Create index
CREATE INDEX idx_email ON students(email);
CREATE INDEX idx_name ON students(name);

-- Create composite index
CREATE INDEX idx_age_gpa ON students(age, gpa);

-- Create unique index
CREATE UNIQUE INDEX idx_unique_email ON students(email);

-- Drop index
DROP INDEX idx_email ON students;

-- View indexes
SHOW INDEXES FROM students;

-- EXPLAIN plan (check query performance)
EXPLAIN SELECT * FROM students WHERE email = 'john@example.com';

-- Query optimization tips:
-- 1. Use indexes on frequently searched columns
-- 2. Avoid SELECT * - select only needed columns
-- 3. Use LIMIT to reduce returned rows
-- 4. Avoid functions on indexed columns
-- 5. Use joins instead of multiple queries

Transactions

-- Start transaction
START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Commit (save changes)
COMMIT;

-- Or rollback (undo changes)
ROLLBACK;

-- Set isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Savepoint
START TRANSACTION;
UPDATE students SET gpa = 3.5 WHERE id = 1;
SAVEPOINT after_update;
UPDATE students SET gpa = 3.9 WHERE id = 2;
ROLLBACK TO SAVEPOINT after_update;  -- Undo only second update
COMMIT;

Projects & Practice

Beginner Projects:

Student Management System
Library Management System
Employee Database
E-commerce Product Database
Hospital Patient Records

Intermediate Projects:

Bank Management System
Online Course Platform
Blog with Comments System
Social Media Network
Hotel Booking System

Advanced Projects:

Data Warehouse Design
Analytics Dashboard
Real-time Transaction System
Multi-tenant SaaS Database
Large-scale Data Management