⭐ Complete SQL Commands Reference (All Commands)

-- Create a new database
CREATE DATABASE school_db;

-- Select / switch to that database
USE school_db;

-- List all databases
SHOW DATABASES;

-- Check which database is active
SELECT DATABASE();

CREATE TABLE students (
    roll_no   INT           PRIMARY KEY AUTO_INCREMENT,
    name      VARCHAR(80)   NOT NULL,
    class     INT           DEFAULT 10,
    marks     DECIMAL(5,2)  DEFAULT 0,
    dob       DATE,
    email     VARCHAR(100)  UNIQUE
);

-- See all tables in current database
SHOW TABLES;

-- See table structure
DESCRIBE students;

-- Insert one row
INSERT INTO students (name, class, marks, dob, email)
VALUES ('Aman Sharma', 10, 88.50, '2009-04-15', 'aman@mail.com');

-- Insert multiple rows at once
INSERT INTO students (name, class, marks, dob, email) VALUES
  ('Priya Gupta',  10, 92.00, '2009-07-22', 'priya@mail.com'),
  ('Ravi Kumar',   11, 76.50, '2008-03-10', 'ravi@mail.com'),
  ('Sneha Singh',  11, 85.25, '2008-11-05', 'sneha@mail.com'),
  ('Arjun Patel',  12, 91.75, '2007-08-30', 'arjun@mail.com');

-- Show all rows and columns
SELECT * FROM students;

-- Show only specific columns
SELECT name, marks FROM students;

-- Show with an alias (rename column in output)
SELECT name AS 'Student Name', marks AS 'Total Marks' FROM students;

-- Specific columns only
SELECT name, class, marks FROM students;

-- Specific row (filter by roll_no)
SELECT * FROM students WHERE roll_no = 2;

-- Specific column of a specific row
SELECT name FROM students WHERE roll_no = 2;

-- Students in class 10
SELECT * FROM students WHERE class = 10;

-- Marks greater than 80
SELECT name, marks FROM students WHERE marks > 80;

-- Class 10 AND marks > 85
SELECT * FROM students WHERE class = 10 AND marks > 85;

-- Class 10 OR class 11
SELECT * FROM students WHERE class = 10 OR class = 11;

-- NOT class 12
SELECT * FROM students WHERE NOT class = 12;

-- All distinct class values (no repeats)
SELECT DISTINCT class FROM students;

-- Distinct name + class combinations
SELECT DISTINCT name, class FROM students;

-- Sort by marks ascending (lowest first)
SELECT name, marks FROM students ORDER BY marks ASC;

-- Sort by marks descending (highest/topper first)
SELECT name, marks FROM students ORDER BY marks DESC;

-- Find the topper
SELECT name, marks FROM students ORDER BY marks DESC LIMIT 1;

-- Show only first 3 rows
SELECT * FROM students LIMIT 3;

-- Skip 2 rows, then show next 3 (pagination)
SELECT * FROM students LIMIT 2, 3;

-- Update one column
UPDATE students SET marks = 95.00 WHERE roll_no = 1;

-- Update multiple columns
UPDATE students SET marks = 90.00, class = 11 WHERE name = 'Aman Sharma';

-- Increase all marks by 5 (bonus marks)
UPDATE students SET marks = marks + 5;

-- Delete one row
DELETE FROM students WHERE roll_no = 3;

-- Delete all class 12 students
DELETE FROM students WHERE class = 12;

-- Delete all rows (keep table structure)
DELETE FROM students;

-- TRUNCATE: faster way to delete all rows
TRUNCATE TABLE students;

-- Add a new column
ALTER TABLE students ADD phone VARCHAR(15);

-- Add column at a specific position
ALTER TABLE students ADD address TEXT AFTER name;

-- Change column data type (MODIFY)
ALTER TABLE students MODIFY marks DECIMAL(6,2);

-- Rename a column (CHANGE)
ALTER TABLE students CHANGE phone mobile VARCHAR(15);

-- Drop (remove) a column
ALTER TABLE students DROP COLUMN address;

-- Rename the table
ALTER TABLE students RENAME TO student_info;

-- Drop a table
DROP TABLE students;

-- Drop only if it exists (no error if not found)
DROP TABLE IF EXISTS students;

-- Drop the entire database
DROP DATABASE school_db;

-- Drop database if it exists
DROP DATABASE IF EXISTS school_db;

-- Inline (at column level)
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    name       VARCHAR(100) NOT NULL
);

-- Table level (for composite key)
CREATE TABLE order_items (
    order_id   INT,
    product_id INT,
    PRIMARY KEY (order_id, product_id)
);

CREATE TABLE orders (
    order_id   INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT,
    item       VARCHAR(100),
    FOREIGN KEY (student_id) REFERENCES students(roll_no)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE teachers (
    id    INT PRIMARY KEY,
    name  VARCHAR(80) NOT NULL,   -- cannot be empty
    email VARCHAR(100)            -- can be NULL
);

CREATE TABLE users (
    id       INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,   -- no two users same username
    email    VARCHAR(100) UNIQUE
);

CREATE TABLE employees (
    id     INT PRIMARY KEY,
    name   VARCHAR(80),
    salary DECIMAL(10,2) CHECK (salary > 0),
    age    INT CHECK (age >= 18 AND age <= 65)
);

CREATE TABLE posts (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    title      VARCHAR(200) NOT NULL,
    status     VARCHAR(20)  DEFAULT 'draft',    -- 'draft' if not given
    created_at DATETIME     DEFAULT NOW()        -- auto timestamp
);

-- COUNT: number of rows
SELECT COUNT(*) AS total_students FROM students;
SELECT COUNT(*) FROM students WHERE class = 10;

-- SUM: total of marks
SELECT SUM(marks) AS total_marks FROM students;

-- AVG: average marks
SELECT AVG(marks) AS average FROM students;
SELECT AVG(marks) FROM students WHERE class = 10;

-- MIN and MAX
SELECT MIN(marks) AS lowest,  MAX(marks) AS highest FROM students;

-- All in one query
SELECT
    COUNT(*)    AS total,
    SUM(marks)  AS sum_marks,
    AVG(marks)  AS avg_marks,
    MIN(marks)  AS min_marks,
    MAX(marks)  AS max_marks
FROM students;

-- Average marks per class
SELECT class, AVG(marks) AS avg_marks
FROM students
GROUP BY class;

-- Count students per class
SELECT class, COUNT(*) AS num_students
FROM students
GROUP BY class;

-- Classes where average marks > 80  (HAVING filters groups)
SELECT class, AVG(marks) AS avg_marks
FROM students
GROUP BY class
HAVING AVG(marks) > 80;

-- Names starting with 'A'
SELECT * FROM students WHERE name LIKE 'A%';

-- Names ending with 'a'
SELECT * FROM students WHERE name LIKE '%a';

-- Names containing 'Kumar'
SELECT * FROM students WHERE name LIKE '%Kumar%';

-- Names with exactly 10 characters
SELECT * FROM students WHERE name LIKE '__________';

-- Emails from gmail.com
SELECT * FROM students WHERE email LIKE '%@gmail.com';

-- NOT LIKE: names NOT starting with 'A'
SELECT * FROM students WHERE name NOT LIKE 'A%';

-- Marks between 75 and 90 (includes 75 and 90)
SELECT * FROM students WHERE marks BETWEEN 75 AND 90;

-- Born between two dates
SELECT * FROM students WHERE dob BETWEEN '2008-01-01' AND '2009-12-31';

-- NOT BETWEEN: marks outside range
SELECT * FROM students WHERE marks NOT BETWEEN 75 AND 90;

-- Students in class 10 or 12
SELECT * FROM students WHERE class IN (10, 12);

-- Specific students by name
SELECT * FROM students WHERE name IN ('Aman Sharma', 'Priya Gupta');

-- NOT IN: exclude classes 10 and 11
SELECT * FROM students WHERE class NOT IN (10, 11);

-- IN with subquery (students who placed orders)
SELECT * FROM students WHERE roll_no IN (SELECT student_id FROM orders);

-- INNER JOIN: only matching rows in both tables
SELECT students.name, orders.item
FROM students
INNER JOIN orders ON students.roll_no = orders.student_id;

-- LEFT JOIN: all students + their orders (NULL if no order)
SELECT students.name, orders.item
FROM students
LEFT JOIN orders ON students.roll_no = orders.student_id;

-- RIGHT JOIN: all orders + matching students
SELECT students.name, orders.item
FROM students
RIGHT JOIN orders ON students.roll_no = orders.student_id;