Defining a New Table

CREATE TABLE members (
  member_id INT PRIMARY KEY AUTO_INCREMENT,
  fullname VARCHAR(50) NOT NULL UNIQUE,
  years_old INT DEFAULT 0,
  contact_email VARCHAR(100),
  joined_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Inserting Records

Single Row Insertion

-- Insert specific fields
INSERT INTO members (fullname, years_old, contact_email)
VALUES ('john_doe', 28, 'john@example.com');

-- Let email be NULL and use default for age
INSERT INTO members (fullname)
VALUES ('jane_smith');

Batch Insertion

-- Insert multiple records with specific fields
INSERT INTO members (fullname, years_old, contact_email)
VALUES 
  ('alice', 25, 'alice@mail.com'),
  ('bob', 30, NULL),
  ('charlie', DEFAULT, 'charlie@mail.com');

-- Insert full records including auto-increment dummy value
INSERT INTO members
VALUES 
  (NULL, 'dave', 22, 'dave@mail.com', '2024-01-15 10:00:00'),
  (NULL, 'eve', DEFAULT, NULL, DEFAULT);

Remoivng Data and Structures

Dropping Tables

-- Safely drop if it exists
DROP TABLE IF EXISTS members;

-- Drop multiple tables simultaneously
DROP TABLE IF EXISTS members, transactions;

Deleting Records

-- Conditional deletion
DELETE FROM members WHERE years_old < 18;

-- Truncate vs Delete
TRUNCATE TABLE members; -- Resets auto-increment, fast, non-rollback
DELETE FROM members;    -- Rollback possible, keeps auto-increment state

Modifying Data and Structures

-- Update specific rows
UPDATE members
SET years_old = 29, contact_email = 'john.new@mail.com'
WHERE fullname = 'john_doe';

-- Update all rows (Caution!)
UPDATE members SET joined_at = CURRENT_TIMESTAMP;

-- Rename table
ALTER TABLE members RENAME TO member_info;

Querying Data

Basic Selection and Aliases

-- Select all columns
SELECT * FROM products;

-- Select specific columns with aliases
SELECT item_name AS product_name, price AS cost FROM products;

Constants and Arithmetic

-- Select literal values
SELECT 100, 'Reading' AS hobby;

-- Calculate derived values
SELECT item_name, price, price * 1.2 AS price_with_tax FROM products;

Filtering with WHERE

-- Comparison operators
SELECT * FROM employees WHERE department = 'Engineering';
SELECT * FROM items WHERE stock_count > 50;
SELECT name FROM students WHERE grade <> 'F';

-- Range and List matching
SELECT * FROM employees WHERE years_old BETWEEN 25 AND 40;
SELECT name FROM students WHERE name IN ('Alice', 'Bob', 'Charlie');

-- Null checks
SELECT * FROM applications WHERE phone_number IS NULL;
SELECT * FROM applications WHERE phone_number IS NOT NULL;

Pattern Matching

SELECT * FROM customers WHERE city LIKE 'New%';
SELECT * FROM customers WHERE address LIKE '%Main%';
SELECT * FROM logs WHERE message NOT LIKE '%Error%';

Logical Operators

SELECT * FROM orders WHERE status = 'Shipped' AND total > 100;
SELECT * FROM orders WHERE status = 'Pending' OR status = 'Processing';
SELECT * FROM users WHERE NOT (active = 0);

Distinct, Ordering, and Pagination

-- Remove duplicates
SELECT DISTINCT city FROM offices;
SELECT DISTINCT city, state FROM offices;

-- Sorting
SELECT name, score FROM results ORDER BY score DESC, name ASC;

-- Limiting results
SELECT product_name FROM inventory LIMIT 5;
SELECT product_name FROM inventory LIMIT 3 OFFSET 2;

Conditional Logic (CASE)

SELECT product_name,
       CASE
           WHEN stock < 10 THEN 'Low Stock'
           WHEN stock BETWEEN 10 AND 50 THEN 'In Stock'
           ELSE 'Overstocked'
       END AS stock_status
FROM inventory;

Date and Time Funcitons

-- Current time variants
SELECT NOW(), CURRENT_DATE(), CURRENT_TIME();

-- Formatting
SELECT DATE_FORMAT(NOW(), '%d/%m/%Y %H:%i') AS formatted_date;

-- Manipulation
SELECT DATE_ADD(NOW(), INTERVAL 3 MONTH);
SELECT DATEDIFF('2025-12-31', CURDATE()) AS days_left;

-- Extracting parts
SELECT YEAR(NOW()), MONTH(NOW()), DAYNAME(NOW());

-- Timestamp conversion
SELECT UNIX_TIMESTAMP(NOW());
SELECT FROM_UNIXTIME(1672531200);

String Functions

SELECT UPPER(title), LOWER(description) FROM articles;
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM persons;
SELECT LENGTH(email) AS email_length FROM subscribers;

Aggregate Functions

SELECT COUNT(*) AS total_items FROM warehouse;
SELECT COUNT(DISTINCT supplier_id) AS unique_suppliers FROM warehouse;
SELECT SUM(amount) AS total_revenue FROM sales;
SELECT AVG(price) AS average_price FROM listings;
SELECT MAX(salary), MIN(salary) FROM staff;

Grouping and Filtering Aggregates

-- Single group
SELECT category, COUNT(*) AS item_count FROM goods GROUP BY category;

-- Multiple groups
SELECT region, city, COUNT(*) AS location_count FROM branches GROUP BY region, city;

-- Filtering groups (HAVING)
SELECT department, SUM(salary) AS payroll
FROM employees
GROUP BY department
HAVING payroll > 50000;

Joins

-- Cross Join (Cartesian product)
SELECT * FROM colors CROSS JOIN sizes;

-- Inner Join
SELECT orders.id, customers.name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id;

-- Outer Joins
SELECT students.name, classes.title
FROM students
LEFT JOIN classes ON students.class_id = classes.id;

SELECT students.name, classes.title
FROM students
RIGHT JOIN classes ON students.class_id = classes.id;

Subquereis

-- Scalar subquery
SELECT name, price FROM products WHERE price > (SELECT AVG(price) FROM products);

-- EXISTS subquery
SELECT * FROM projects
WHERE EXISTS (SELECT 1 FROM tasks WHERE tasks.project_id = projects.id AND status = 'Done');

Set Operations

SELECT username FROM admins UNION SELECT username FROM moderators;
SELECT username FROM admins UNION ALL SELECT username FROM moderators;

Window Functions

-- Basic aggregate window
SELECT employee_name, department, SUM(salary) OVER (PARTITION BY department) AS dept_total
FROM staff;

-- Ranking
SELECT 
  student_name,
  score,
  RANK() OVER (ORDER BY score DESC) AS rank_no,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank_no,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num
FROM exam_results;

-- Running total
SELECT month, revenue, SUM(revenue) OVER (ORDER BY month) AS running_total
FROM finances;

-- Lead and Lag
SELECT 
  sale_date,
  amount,
  LAG(amount, 1) OVER (ORDER BY sale_date) AS prev_sale,
  LEAD(amount, 1) OVER (ORDER BY sale_date) AS next_sale
FROM daily_sales;

-- Sliding window
SELECT 
  day,
  temp,
  AVG(temp) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM weather;

Views and CTEs

-- Create a View
CREATE VIEW high_value_orders AS
SELECT * FROM orders WHERE total_amount > 1000;

-- Common Table Expression (CTE)
WITH recent_logins AS (
  SELECT user_id, MAX(login_time) AS last_seen
  FROM logins
  GROUP BY user_id
)
SELECT * FROM recent_logins;

Transactions and Temporary Objects

-- Transaction example
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Temporary Table
CREATE TEMPORARY TABLE temp_high_scores AS
SELECT user_id, MAX(score) AS max_score FROM game_plays GROUP BY user_id;

Stored Procedures, Triggers, and Cursors

-- Stored Procedure
DELIMITER //
CREATE PROCEDURE GetUserCount(OUT total INT)
BEGIN
  SELECT COUNT(*) INTO total FROM users;
END //
DELIMITER ;
CALL GetUserCount(@user_total);
SELECT @user_total;

-- Trigger
CREATE TRIGGER adjust_inventory AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
  UPDATE products SET stock = stock - NEW.quantity WHERE id = NEW.product_id;
END;

-- Cursor usage inside procedure
DELIMITER //
CREATE PROCEDURE process_slow_users()
BEGIN
  DECLARE uid INT;
  DECLARE done BOOLEAN DEFAULT FALSE;
  DECLARE cur CURSOR FOR SELECT id FROM users WHERE status = 'inactive';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO uid;
    IF done THEN LEAVE read_loop; END IF;
    UPDATE logs SET processed = 1 WHERE user_id = uid;
  END LOOP;
  CLOSE cur;
END //
DELIMITER ;

Indexes

-- Create indexes for performance
CREATE INDEX idx_member_email ON members(contact_email);
CREATE UNIQUE INDEX idx_order_ref ON orders(reference_code);