SQL Queries for Data Analysis and Backend

Data Retrieval

Select Unique Records

SELECT DISTINCT category FROM products;

Join Multiple Tables

SELECT o.id, c.name, p.title
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN products p ON o.product_id = p.id;

Group By and Having

SELECT category, COUNT(*) as total
FROM products
GROUP BY category
HAVING COUNT(*) > 5;

Data Modification

Update with Join

UPDATE products
SET price = price * 1.1
FROM categories
WHERE products.category_id = categories.id
AND categories.name = 'Electronics';

Delete Duplicates

DELETE FROM users
WHERE id NOT IN (
  SELECT MIN(id)
  FROM users
  GROUP BY email
);

Advanced Queries

Find Second Highest Salary

SELECT MAX(salary)
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Window Functions (Rank)

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

Case Statements

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

Coalesce (Null Handling)

SELECT name, COALESCE(phone, 'N/A') as contact
FROM users;

Recursive CTE (Hierarchy)

WITH RECURSIVE subordinates AS (
  SELECT id, name, manager_id
  FROM employees WHERE name = 'Alice'
  UNION ALL
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  JOIN subordinates s ON s.id = e.manager_id
)
SELECT * FROM subordinates;

Database Management

Create Index

CREATE INDEX idx_user_email ON users(email);

Add Column with Default

ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';

Copy Table Structure

CREATE TABLE users_backup AS SELECT * FROM users WHERE 1=0;

Find Table Constraints

SELECT * FROM information_schema.table_constraints
WHERE table_name = 'users';

Check Database Size

SELECT pg_size_pretty(pg_database_size('my_database'));