sql

name	sql
description	SQL patterns for database querying and design
domain	programming-languages
version	1.0.0
tags	["sql","postgresql","mysql","queries","database"]
triggers	{"keywords":{"primary":["sql","query","select","join","database","postgresql","mysql"],"secondary":["index","transaction","cte","window function","stored procedure","view"]},"context_boost":["data","backend","analytics","reporting"],"context_penalty":["frontend","ui","mobile"],"priority":"high"}

SQL

Overview

SQL patterns for querying, data manipulation, and database design.

---

Query Fundamentals

Basic Queries

-- SELECT with filtering
SELECT
    id,
    email,
    name,
    created_at
FROM users
WHERE active = true
  AND created_at >= '2024-01-01'
ORDER BY created_at DESC
LIMIT 10 OFFSET 0;

-- Multiple conditions
SELECT *
FROM orders
WHERE status IN ('pending', 'processing')
  AND total_amount > 100
  AND (priority = 'high' OR customer_type = 'premium');

-- LIKE and pattern matching
SELECT *
FROM products
WHERE name LIKE '%widget%'          -- Contains 'widget'
   OR name LIKE 'Premium%'          -- Starts with 'Premium'
   OR sku SIMILAR TO '[A-Z]{3}-[0-9]{4}';  -- Regex pattern (PostgreSQL)

-- NULL handling
SELECT
    id,
    COALESCE(nickname, name, 'Anonymous') AS display_name,
    NULLIF(discount, 0) AS discount_or_null
FROM users
WHERE deleted_at IS NULL;

-- CASE expressions
SELECT
    id,
    name,
    CASE
        WHEN total >= 1000 THEN 'Gold'
        WHEN total >= 500 THEN 'Silver'
        WHEN total >= 100 THEN 'Bronze'
        ELSE 'Standard'
    END AS tier,
    CASE status
        WHEN 'active' THEN 1
        WHEN 'pending' THEN 2
        ELSE 3
    END AS sort_order
FROM customers
ORDER BY sort_order;

-- Distinct and counting
SELECT DISTINCT category
FROM products;

SELECT
    category,
    COUNT(*) AS product_count,
    COUNT(DISTINCT brand) AS brand_count
FROM products
GROUP BY category;

Joins

-- INNER JOIN (only matching rows)
SELECT
    o.id AS order_id,
    o.total_amount,
    u.name AS customer_name,
    u.email
FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE o.status = 'completed';

-- LEFT JOIN (all from left, matching from right)
SELECT
    u.id,
    u.name,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- Multiple joins
SELECT
    o.id AS order_id,
    u.name AS customer_name,
    p.name AS product_name,
    oi.quantity,
    oi.unit_price
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.created_at >= CURRENT_DATE - INTERVAL '30 days';

-- Self join
SELECT
    e.name  employee,
    m.name  manager
 employees e
  employees m  e.manager_id  m.id;



    p.name  product,
    c.name  color
 products p
  colors c;

Aggregations

-- Basic aggregation
SELECT
    category,
    COUNT(*) AS product_count,
    SUM(price) AS total_value,
    AVG(price) AS avg_price,
    MIN(price) AS min_price,
    MAX(price) AS max_price
FROM products
GROUP BY category
HAVING COUNT(*) >= 5
ORDER BY product_count DESC;

-- Group by multiple columns
SELECT
    EXTRACT(YEAR FROM created_at) AS year,
    EXTRACT(MONTH FROM created_at) AS month,
    status,
    COUNT(*) AS order_count,
    SUM(total_amount) AS revenue
FROM orders
GROUP BY
    EXTRACT(YEAR FROM created_at),
    EXTRACT(MONTH FROM created_at),
    status
ORDER BY year, ;



    (category, )  category,
    (brand, )  brand,
    ()  count,
    (price)  total
 products
   (category, brand);



    category,
    brand,
    (sales)  total_sales
 product_sales
   (category, brand);



    category,
    brand,
    (sales)  total_sales
 product_sales
   (
    (category, brand),
    (category),
    (brand),
    ()
);

---

Advanced Queries

Window Functions

-- Row numbering
SELECT
    id,
    name,
    department,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

-- Running totals
SELECT
    date,
    amount,
    SUM(amount) OVER (ORDER BY date) AS running_total,
    SUM(amount) OVER (
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_7_day
FROM daily_sales;

-- Rank functions
SELECT
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY department   salary )  dense_rank,
    ()  (  salary )  quartile
 employees;



    ,
    revenue,
    (revenue, )  (  )  prev_day,
    (revenue, )  (  )  next_day,
    revenue  (revenue, )  (  )  daily_change,
      (revenue  (revenue, )  (  ))
         (revenue, )  (  )  pct_change
 daily_revenue;



    department,
    employee_name,
    salary,
    (employee_name)  (
          department   salary 
    )  highest_paid,
    (employee_name)  (
          department   salary 
          UNBOUNDED PRECEDING  UNBOUNDED FOLLOWING
    )  lowest_paid
 employees;

Common Table Expressions (CTEs)

-- Basic CTE
WITH active_users AS (
    SELECT *
    FROM users
    WHERE active = true
)
SELECT
    au.name,
    COUNT(o.id) AS order_count
FROM active_users au
LEFT JOIN orders o ON au.id = o.user_id
GROUP BY au.id, au.name;

-- Multiple CTEs
WITH
monthly_sales AS (
    SELECT
        DATE_TRUNC('month', created_at) AS month,
        SUM(total_amount) AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY DATE_TRUNC('month', created_at)
),
growth AS (
    SELECT
        month,
        revenue,
        LAG(revenue) OVER (ORDER BY month) AS prev_revenue
    FROM monthly_sales
)
SELECT
    month,
    revenue,
    prev_revenue,
    100.0 * (revenue - prev_revenue) / prev_revenue  growth_pct
 growth
 prev_revenue  ;


  org_hierarchy  (
    
    
        id,
        name,
        manager_id,
          level,
        [name]  path
     employees
     manager_id  

     

    
    
        e.id,
        e.name,
        e.manager_id,
        oh.level  ,
        oh.path  e.name
     employees e
     org_hierarchy oh  e.manager_id  oh.id
)

    id,
    name,
    level,
    array_to_string(path, )  hierarchy_path
 org_hierarchy
  path;

Subqueries

-- Scalar subquery
SELECT
    name,
    salary,
    salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;

-- IN subquery
SELECT *
FROM products
WHERE category_id IN (
    SELECT id
    FROM categories
    WHERE parent_id IS NULL
);

-- EXISTS subquery
SELECT *
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
      AND o.created_at >= CURRENT_DATE - INTERVAL '30 days'
);

-- Correlated subquery
SELECT
    e.name,
    e.salary,
    e.department,
    (
        SELECT AVG(salary)
        FROM employees e2
        WHERE e2.department = e.department
    ) AS dept_avg
FROM employees e;

-- Lateral join (row-by-row subquery)
SELECT
    u.name,
    recent_orders.order_count,
    recent_orders.total_spent
FROM users u
CROSS JOIN  (
    
        ()  order_count,
        ((total_amount), )  total_spent
     orders o
     o.user_id  u.id
       o.created_at     
) recent_orders;

---

Data Modification

-- INSERT
INSERT INTO users (email, name, created_at)
VALUES ('[e-mail]', 'New User', NOW());

-- INSERT multiple rows
INSERT INTO products (name, price, category)
VALUES
    ('Product A', 19.99, 'Electronics'),
    ('Product B', 29.99, 'Electronics'),
    ('Product C', 9.99, 'Accessories');

-- INSERT from SELECT
INSERT INTO order_archive (id, user_id, total_amount, created_at)
SELECT id, user_id, total_amount, created_at
FROM orders
WHERE created_at < '2023-01-01';

-- INSERT with conflict handling (PostgreSQL)
INSERT INTO users (email, name)
VALUES ('[e-mail]', 'New User')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name;

-- INSERT with conflict (MySQL)
INSERT INTO users (email, name)
VALUES ('[e-mail]', 'New User')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- UPDATE
UPDATE products
SET price = price * 1.1,
    updated_at  NOW()
 category  ;


 orders o
 status  
 users u
 o.user_id  u.id
   u.deleted_at  ;


  sessions
 expires_at  NOW();


  order_items
 order_id  (
     id  orders  status  
);


 page_views (page_id, view_count)
 (, )
 CONFLICT (page_id)
DO   view_count  page_views.view_count  ;

---

Schema Design

-- Create table with constraints
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(50) DEFAULT 'user' CHECK (role IN ('admin', 'user', 'guest')),
    active BOOLEAN DEFAULT true,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Foreign keys
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    status VARCHAR(50) NOT NULL DEFAULT 'pending',
    total_amount DECIMAL(10, 2) ,
    created_at    ZONE  NOW(),

     valid_status  (status  (, , , ))
);


 product_categories (
    product_id UUID  products(id)   CASCADE,
    category_id UUID  categories(id)   CASCADE,
     (product_id, category_id)
);


 INDEX idx_users_email  users (email);
 INDEX idx_orders_user_id  orders (user_id);
 INDEX idx_orders_status  orders (status)  status  ;
 INDEX idx_products_search  products  gin (to_tsvector(, name    description));


  INDEX idx_active_users_email
 users (email)
 active  ;


 products
  search_vector tsvector
GENERATED ALWAYS  (to_tsvector(, name    (description, ))) STORED;


  REPLACE  update_updated_at()
   $$

    NEW.updated_at  NOW();
     ;
;
$$  plpgsql;

  set_updated_at
BEFORE   users
  
  update_updated_at();

---

Performance

-- EXPLAIN ANALYZE
EXPLAIN ANALYZE
SELECT *
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending';

-- Query hints (PostgreSQL)
SET enable_seqscan = off;  -- Force index usage for testing

-- Batch processing
WITH batch AS (
    SELECT id
    FROM large_table
    WHERE processed = false
    ORDER BY id
    LIMIT 1000
    FOR UPDATE SKIP LOCKED
)
UPDATE large_table
SET processed = true
WHERE id IN (SELECT id FROM batch);

-- Pagination with keyset
SELECT *
FROM products
WHERE (created_at, id) < ('2024-01-01', 'abc123')
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Materialized view
CREATE MATERIALIZED VIEW monthly_sales_summary 

    DATE_TRUNC(, created_at)  ,
    ()  order_count,
    (total_amount)  revenue
 orders
 status  
  DATE_TRUNC(, created_at);


REFRESH MATERIALIZED  CONCURRENTLY monthly_sales_summary;

---

Related Skills

• [[database]] - Database design patterns
• [[backend]] - Database integration
• [[performance-optimization]] - Query optimization