SQL Snippets

Common SQL queries and database operations

Basic Queries

sql

Common SQL query patterns

-- Select all columns
SELECT * FROM users;

-- Select specific columns
SELECT name, email FROM users
WHERE age >= 18
ORDER BY name ASC;

-- Count records
SELECT COUNT(*) as total_users
FROM users
WHERE status = 'active';

Joins

sql

Combine data from multiple tables

-- Inner join
SELECT orders.id, users.name, orders.total
FROM orders
INNER JOIN users ON orders.user_id = users.id;

-- Left join
SELECT users.name, orders.id
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

Aggregations

sql

Group and aggregate data

-- Group by with multiple aggregations
SELECT 
  category,
  COUNT(*) as total_products,
  AVG(price) as avg_price,
  MAX(price) as max_price
FROM products
GROUP BY category
HAVING COUNT(*) > 5
ORDER BY total_products DESC;

Subqueries

sql

Use nested queries for complex operations

-- Subquery in WHERE clause
SELECT name, salary
FROM employees
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
);

-- Subquery in FROM clause
SELECT dept_name, avg_salary
FROM (
  SELECT 
    department_id,
    AVG(salary) as avg_salary
  FROM employees
  GROUP BY department_id
) avg_salaries
JOIN departments d ON d.id = avg_salaries.department_id;