Master Database
Management

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) UNIQUE NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

SELECT
  u.username,
  COUNT(o.id) as total_orders,
  SUM(o.total_amount) as total_spent,
  AVG(o.total_amount) as avg_order_value,
  ROW_NUMBER() OVER (ORDER BY SUM(o.total_amount) DESC) as rank
...

-- Before Normalization (1NF violation)
CREATE TABLE orders (
  order_id INT,
  customer_name VARCHAR(100),
  customer_address VARCHAR(200),
  product_names VARCHAR(500), -- Multiple values in one field
...

-- Analyze slow query
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 12345
  AND order_date BETWEEN '2024-01-01' AND '2024-12-31'
ORDER BY total_amount DESC;
...

-- Create roles with minimal privileges
CREATE ROLE app_user LOGIN PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE ecommerce TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;

-- Grant specific table permissions
...

-- Fact Table: Sales Facts
CREATE TABLE fact_sales (
  sales_key SERIAL PRIMARY KEY,
  date_key INTEGER REFERENCES dim_date(date_key),
  customer_key INTEGER REFERENCES dim_customer(customer_key),
  product_key INTEGER REFERENCES dim_product(product_key),
...

// Complex aggregation with multiple stages
db.orders.aggregate([
  // Stage 1: Filter orders from 2024
  {
    $match: {
      orderDate: {
...

#!/bin/bash
# Automated PostgreSQL backup script

BACKUP_DIR="/var/backups/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="ecommerce"
...

-- Customer churn prediction using ML functions
WITH customer_features AS (
  SELECT
    customer_id,
    -- Recency: days since last order
    DATEDIFF(CURVE, MAX(order_date), CURRENT_DATE) as recency,
...