SQLタイピングのコツ：データベースクエリ構文をマスターして開発を高速化

SELECT * FROM users;

SELECT id, name, email FROM users WHERE active = 1;

SELECT name, COUNT(*) FROM orders GROUP BY name;

SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

SELECT a.name, b.value
FROM table_a a
LEFT JOIN table_b b ON a.id = b.a_id;

SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 100);

SELECT category, SUM(amount), AVG(price)
FROM products
GROUP BY category
HAVING SUM(amount) > 1000;

SELECT name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) as rank
FROM employees;

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

SELECT date, revenue,
  LAG(revenue, 1) OVER (ORDER BY date) as prev_day,
  LEAD(revenue, 1) OVER (ORDER BY date) as next_day
FROM sales;

WITH active_users AS (
  SELECT * FROM users WHERE status = 'active'
)
SELECT * FROM active_users WHERE created_at > '2024-01-01';

WITH RECURSIVE hierarchy AS (
  SELECT id, name, parent_id, 1 as level
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, h.level + 1
  FROM categories c
  INNER JOIN hierarchy h ON c.parent_id = h.id
)
SELECT * FROM hierarchy;

SELECT name,
  CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    WHEN score >= 70 THEN 'C'
    ELSE 'F'
  END as grade
FROM students;

SELECT
  SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed,
  SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending
FROM orders;

SELECT name, email FROM customers
UNION
SELECT name, email FROM suppliers;

SELECT id FROM table_a
INTERSECT
SELECT id FROM table_b;

SELECT id FROM table_a
EXCEPT
SELECT id FROM table_b;

UPDATE orders o
SET status = 'archived'
FROM customers c
WHERE o.customer_id = c.id AND c.deleted_at IS NOT NULL;

DELETE FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE status = 'inactive'
);

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

SELECT name, COALESCE(phone, email, 'No contact') as contact
FROM users;

SELECT * FROM users WHERE deleted_at IS NULL;

SELECT * FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);

ALTER TABLE users ADD COLUMN phone VARCHAR(20);