Master SQL Database Queries

Complete guide to SQL from basics to advanced optimization

Section 1: SQL Fundamentals

Understanding SQL

SQL (Structured Query Language) is the standard language for managing relational databases. It allows you to query, insert, update, and delete data.

Basic SELECT Queries

Start with simple SELECT statements to retrieve data:

-- Select all columns from a table
SELECT * FROM users;

-- Select specific columns
SELECT id, name, email FROM users;

-- Select with conditions
SELECT * FROM users WHERE age > 18;

-- Select with multiple conditions
SELECT * FROM users 
WHERE age > 18 AND status = 'active';

-- Select with ordering
SELECT * FROM users 
ORDER BY created_at DESC;

-- Select with limit
SELECT * FROM users 
LIMIT 10;

INSERT, UPDATE, DELETE

Learn to modify data in your database:

-- Insert new record
INSERT INTO users (name, email, age)
VALUES ('John Doe', 'john@example.com', 25);

-- Update existing record
UPDATE users 
SET email = 'newemail@example.com'
WHERE id = 1;

-- Delete record
DELETE FROM users 
WHERE id = 1;

Section 2: Advanced JOINs

INNER JOIN

Combine rows from two tables where there is a match:

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

LEFT JOIN

Return all rows from the left table, even if there's no match:

SELECT users.name, orders.total
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

Multiple JOINs

Join multiple tables in a single query:

SELECT 
  users.name,
  products.name AS product_name,
  order_items.quantity
FROM users
INNER JOIN orders ON users.id = orders.user_id
INNER JOIN order_items ON orders.id = order_items.order_id
INNER JOIN products ON order_items.product_id = products.id;

Section 3: Subqueries

Subquery in WHERE Clause

Use subqueries to filter results based on another query:

-- Find users who have placed orders
SELECT * FROM users
WHERE id IN (
  SELECT DISTINCT user_id FROM orders
);

-- Find users with above-average age
SELECT * FROM users
WHERE age > (SELECT AVG(age) FROM users);

Correlated Subqueries

Subqueries that reference the outer query:

-- Find users with their latest order
SELECT 
  u.name,
  (SELECT MAX(created_at) 
   FROM orders o 
   WHERE o.user_id = u.id) AS last_order_date
FROM users u;

Section 4: Aggregate Functions

GROUP BY and Aggregate Functions

Group data and calculate aggregates:

-- Count orders per user
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id;

-- Calculate total sales per product
SELECT 
  product_id,
  SUM(quantity * price) AS total_sales
FROM order_items
GROUP BY product_id;

-- Average age by city
SELECT 
  city,
  AVG(age) AS avg_age,
  COUNT(*) AS user_count
FROM users
GROUP BY city
HAVING COUNT(*) > 10;

Section 5: Database Optimization

Creating Indexes

Indexes improve query performance by allowing faster data retrieval:

-- Create single column index
CREATE INDEX idx_user_email ON users(email);

-- Create composite index
CREATE INDEX idx_user_name_email ON users(name, email);

-- Create unique index
CREATE UNIQUE INDEX idx_user_email_unique ON users(email);

Query Optimization Tips

Use indexes on frequently queried columns
Avoid SELECT * - only select needed columns
Use WHERE clauses to filter early
Limit result sets when possible
Use JOINs instead of subqueries when possible

Section 6: Transactions and ACID

Transaction Management

Ensure data integrity with transactions:

-- Start a transaction
BEGIN TRANSACTION;

-- Perform multiple operations
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Commit if successful
COMMIT;

-- Or rollback on error
ROLLBACK;

ACID Properties

Atomicity: All operations succeed or fail together
Consistency: Database remains in valid state
Isolation: Concurrent transactions don't interfere
Durability: Committed changes persist

← Back to Overview