SQL Tutorial

-- Select all columns
SELECT * FROM customers;

-- Select specific columns
SELECT name, email, city FROM customers;

-- Alias
SELECT name AS customer_name,
       price * 1.1 AS price_with_tax
FROM products;

-- DISTINCT (remove duplicates)
SELECT DISTINCT country FROM customers;

-- Limit results
SELECT * FROM products LIMIT 10;           -- MySQL/PostgreSQL
SELECT TOP 10 * FROM products;             -- SQL Server

-- Skip rows (pagination)
SELECT * FROM products LIMIT 10 OFFSET 20; -- skip 20, get next 10

-- Comparison operators
SELECT * FROM products WHERE price > 50;
SELECT * FROM products WHERE price BETWEEN 10 AND 100;
SELECT * FROM customers WHERE country = 'India';
SELECT * FROM customers WHERE country != 'USA';

-- Logical operators
SELECT * FROM products WHERE price > 50 AND category = 'Electronics';
SELECT * FROM products WHERE category = 'Books' OR category = 'Games';
SELECT * FROM customers WHERE NOT country = 'USA';

-- IN / NOT IN
SELECT * FROM customers WHERE country IN ('India', 'USA', 'UK');
SELECT * FROM products WHERE category NOT IN ('Expired', 'Discontinued');

-- LIKE (pattern matching)
SELECT * FROM customers WHERE name LIKE 'A%';   -- starts with A
SELECT * FROM customers WHERE email LIKE '%@gmail.com';  -- ends with
SELECT * FROM products WHERE name LIKE '_phone';  -- _ = 1 char

-- IS NULL / IS NOT NULL
SELECT * FROM customers WHERE phone IS NULL;
SELECT * FROM orders WHERE shipped_date IS NOT NULL;

-- Insert one row
INSERT INTO customers (name, email, city, country)
VALUES ('Alice Smith', 'alice@example.com', 'Mumbai', 'India');

-- Insert multiple rows
INSERT INTO products (name, price, category) VALUES
  ('Laptop',  80000, 'Electronics'),
  ('Keyboard', 2500, 'Electronics'),
  ('Python Book', 599, 'Books');

-- Insert from SELECT
INSERT INTO archive_orders
SELECT * FROM orders WHERE order_date < '2023-01-01';

-- Update specific rows (always use WHERE to avoid updating all!)
UPDATE customers
SET city = 'Delhi', country = 'India'
WHERE id = 42;

-- Update multiple columns
UPDATE products
SET price = price * 1.1,    -- 10% price increase
    updated_at = NOW()
WHERE category = 'Electronics';

-- Update with subquery
UPDATE orders
SET status = 'Shipped'
WHERE customer_id IN (
  SELECT id FROM customers WHERE country = 'India'
);

-- Delete specific rows
DELETE FROM orders WHERE status = 'Cancelled';

-- Delete by date
DELETE FROM logs WHERE created_at < '2023-01-01';

-- Delete all rows (keeps table structure)
DELETE FROM temp_data;

-- TRUNCATE (faster, resets auto-increment, can't rollback easily)
TRUNCATE TABLE temp_data;

CREATE TABLE customers (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    name        VARCHAR(100) NOT NULL,
    email       VARCHAR(255) UNIQUE NOT NULL,
    phone       VARCHAR(20),
    city        VARCHAR(100),
    country     VARCHAR(50) DEFAULT 'India',
    created_at  DATETIME    DEFAULT CURRENT_TIMESTAMP,
    is_active   BOOLEAN     DEFAULT TRUE
);

CREATE TABLE orders (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    product_id  INT NOT NULL,
    quantity    INT DEFAULT 1,
    total_price DECIMAL(10, 2) NOT NULL,
    order_date  DATE NOT NULL,
    status      ENUM('Pending','Processing','Shipped','Delivered','Cancelled') DEFAULT 'Pending',
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (product_id)  REFERENCES products(id)
);

-- INNER JOIN (only matching rows in both tables)
SELECT o.id, c.name, o.total_price
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

-- LEFT JOIN (all rows from left table + matches from right)
SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;
-- Customers with no orders will have NULL for order_id

-- RIGHT JOIN (all rows from right table + matches from left)
SELECT c.name, o.id
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;

-- FULL OUTER JOIN (all rows from both tables)
SELECT c.name, o.id
FROM customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id;

-- CROSS JOIN (every combination - cartesian product)
SELECT c.name, p.name
FROM customers c
CROSS JOIN products p;

-- SELF JOIN (join table with itself)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Multiple JOINs
SELECT o.id, c.name, p.name AS product, o.quantity
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN products p  ON o.product_id  = p.id
WHERE o.status = 'Delivered'
ORDER BY o.order_date DESC;

-- COUNT
SELECT COUNT(*) FROM customers;              -- all rows
SELECT COUNT(phone) FROM customers;          -- non-NULL values only
SELECT COUNT(DISTINCT country) FROM customers; -- unique countries

-- SUM, AVG, MIN, MAX
SELECT
    SUM(quantity)          AS total_items_sold,
    AVG(total_price)       AS average_order_value,
    MIN(total_price)       AS cheapest_order,
    MAX(total_price)       AS most_expensive_order
FROM orders
WHERE status = 'Delivered';

-- GROUP BY with aggregates
SELECT category, COUNT(*) AS product_count, AVG(price) AS avg_price
FROM products
GROUP BY category
ORDER BY product_count DESC;

-- HAVING (filter on aggregated results)
SELECT customer_id, COUNT(*) AS order_count, SUM(total_price) AS total_spent
FROM orders
GROUP BY customer_id
HAVING total_spent > 10000
ORDER BY total_spent DESC;

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

-- Subquery in FROM (derived table)
SELECT city, AVG(order_count) AS avg_orders
FROM (
    SELECT c.city, COUNT(o.id) AS order_count
    FROM customers c
    LEFT JOIN orders o ON c.id = o.customer_id
    GROUP BY c.id
) AS city_stats
GROUP BY city;

-- Correlated subquery (references outer query)
SELECT name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS total_orders
FROM customers c;

-- EXISTS / NOT EXISTS
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id AND o.total_price > 5000
);

-- CTE (Common Table Expression) - WITH clause
WITH top_customers AS (
    SELECT customer_id, SUM(total_price) AS total
    FROM orders
    GROUP BY customer_id
    HAVING total > 50000
)
SELECT c.name, tc.total
FROM customers c
JOIN top_customers tc ON c.id = tc.customer_id;

-- Create a view (saved query)
CREATE VIEW order_summary AS
SELECT o.id, c.name AS customer, p.name AS product,
       o.quantity, o.total_price, o.order_date
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN products  p ON o.product_id  = p.id;

-- Use the view like a table
SELECT * FROM order_summary WHERE customer = 'Alice';

-- Update view
CREATE OR REPLACE VIEW order_summary AS ...;

-- Drop view
DROP VIEW IF EXISTS order_summary;

-- Transaction: group multiple statements atomically
START TRANSACTION;

UPDATE accounts SET balance = balance - 1000 WHERE id = 1;  -- debit
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;  -- credit

-- If all OK:
COMMIT;

-- If something went wrong:
ROLLBACK;

-- Savepoints (partial rollback)
START TRANSACTION;
INSERT INTO orders ...;
SAVEPOINT after_insert;
UPDATE products SET stock = stock - 1 ...;
-- Something failed:
ROLLBACK TO SAVEPOINT after_insert;
-- only the UPDATE is rolled back, INSERT remains
COMMIT;

WITH high_value_orders AS (
  SELECT customer_id, total_amount
  FROM orders
  WHERE total_amount > 10000
)
SELECT c.name, h.total_amount
FROM high_value_orders h
JOIN customers c ON c.id = h.customer_id
ORDER BY h.total_amount DESC;

SELECT
  employee_name,
  department,
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;