SQL (Database Queries)

-- Basic select
SELECT * FROM users;
SELECT name, email FROM users;

-- Filtering
SELECT * FROM users WHERE age > 18;
SELECT * FROM users WHERE name LIKE '%Mayank%';
SELECT * FROM users WHERE age BETWEEN 10 AND 20;
SELECT * FROM users WHERE name IN ('Alice', 'Bob');
SELECT * FROM users WHERE email IS NOT NULL;

-- Ordering
SELECT * FROM users ORDER BY age DESC;
SELECT * FROM users ORDER BY name ASC;

-- Limit
SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20;  -- pagination

-- Insert
INSERT INTO users (name, email, age) VALUES ('Mayank', 'mayank@test.com', 15);
INSERT INTO users (name, email, age) VALUES
  ('Alice', 'alice@test.com', 20),
  ('Bob', 'bob@test.com', 12);

-- Update
UPDATE users SET age = 16 WHERE name = 'Mayank';
UPDATE users SET email = 'new@test.com', age = 21 WHERE id = 1;

-- Delete
DELETE FROM users WHERE id = 5;
DELETE FROM users WHERE age < 10;
-- TRUNCATE TABLE users;  -- fast delete all

-- INNER JOIN (only matching rows)
SELECT users.name, orders.product
FROM users
INNER JOIN orders ON users.id = orders.user_id;

-- LEFT JOIN (all from left, matching from right)
SELECT users.name, COALESCE(orders.product, 'None')
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

-- RIGHT JOIN
SELECT users.name, orders.product
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

-- CROSS JOIN (every combination)
SELECT users.name, products.name
FROM users
CROSS JOIN products;

-- Self join
SELECT a.name AS employee, b.name AS manager
FROM employees a
INNER JOIN employees b ON a.manager_id = b.id;

-- Aggregate functions
SELECT COUNT(*) FROM users;
SELECT AVG(age) FROM users;
SELECT MAX(age), MIN(age) FROM users;
SELECT SUM(salary) FROM employees;

-- GROUP BY
SELECT grade, COUNT(*) AS count
FROM students
GROUP BY grade;

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

-- HAVING (filter groups)
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

-- Multiple groups
SELECT grade, gender, COUNT(*)
FROM students
GROUP BY grade, gender
ORDER BY grade;

-- Subquery
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

-- CTE (Common Table Expression)
WITH high_spenders AS (
  SELECT user_id, SUM(amount) AS total
  FROM orders
  GROUP BY user_id
  HAVING SUM(amount) > 1000
)
SELECT users.name, high_spenders.total
FROM users
INNER JOIN high_spenders ON users.id = high_spenders.user_id;

-- Window functions
SELECT name, age,
  ROW_NUMBER() OVER (ORDER BY age DESC) AS rank,
  AVG(age) OVER () AS avg_age,
  age - LAG(age) OVER (ORDER BY age) AS age_diff
FROM users;

-- Create index
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_age ON users(age);

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

-- Unique index
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- Analyze query
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'mayank@test.com';

-- Partial index
CREATE INDEX idx_active_users ON users(id)
WHERE active = true;

-- Drop index
DROP INDEX idx_users_email;

-- Transaction
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- or ROLLBACK on error

-- Constraints
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  age INT CHECK (age >= 0 AND age <= 150),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id) ON DELETE CASCADE,
  amount DECIMAL(10,2) NOT NULL CHECK (amount > 0)
);

-- View
CREATE VIEW active_users AS
SELECT * FROM users WHERE active = true;

SELECT * FROM active_users;

-- Stored procedure (PostgreSQL)
CREATE OR REPLACE FUNCTION get_user_count()
RETURNS INT AS $$
BEGIN
  RETURN (SELECT COUNT(*) FROM users);
END;
$$ LANGUAGE plpgsql;

SELECT get_user_count();

-- Trigger
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_timestamp
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();