SQL SELECT Queries - Complete Guide

🎯 Quick Reference

Basic SELECT Statement

-- Select all columns from table
SELECT * FROM students;

-- Select specific columns
SELECT student_id, first_name, last_name FROM students;

-- Select with condition
SELECT * FROM students WHERE grade = 'A';

-- Select with multiple conditions
SELECT * FROM students WHERE grade = 'A' AND age >= 18;

SELECT with WHERE Clause

-- Numeric comparisons
SELECT * FROM products WHERE price > 100;
SELECT * FROM products WHERE price BETWEEN 50 AND 200;

-- String comparisons
SELECT * FROM users WHERE username LIKE 'john%';
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- Date comparisons
SELECT * FROM orders WHERE order_date >= '2024-01-01';
SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

-- NULL checks
SELECT * FROM customers WHERE phone IS NULL;
SELECT * FROM customers WHERE phone IS NOT NULL;

ORDER BY Clause

-- Sort by single column (ascending)
SELECT * FROM students ORDER BY last_name;

-- Sort by single column (descending)
SELECT * FROM students ORDER BY grade DESC;

-- Sort by multiple columns
SELECT * FROM students ORDER BY grade DESC, last_name ASC;

-- Sort by calculated column
SELECT *, (math_score + science_score) AS total_score 
FROM results 
ORDER BY total_score DESC;

LIMIT and OFFSET

-- Limit results (MySQL/PostgreSQL)
SELECT * FROM students LIMIT 10;

-- Limit with offset (pagination)
SELECT * FROM students LIMIT 10 OFFSET 20;

-- SQL Server syntax
SELECT TOP 10 * FROM students;
SELECT * FROM students 
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

Aggregate Functions

-- Count records
SELECT COUNT(*) FROM students;
SELECT COUNT(DISTINCT grade) FROM students;

-- Sum and Average
SELECT SUM(price) FROM products;
SELECT AVG(price) FROM products;

-- Min and Max
SELECT MIN(price), MAX(price) FROM products;

-- Multiple aggregates
SELECT 
    COUNT(*) as total_students,
    AVG(score) as average_score,
    MIN(score) as min_score,
    MAX(score) as max_score
FROM results;

GROUP BY and HAVING

-- Group by single column
SELECT grade, COUNT(*) as student_count 
FROM students 
GROUP BY grade;

-- Group by multiple columns
SELECT grade, age, COUNT(*) as student_count 
FROM students 
GROUP BY grade, age;

-- Having clause (filter groups)
SELECT grade, COUNT(*) as student_count 
FROM students 
GROUP BY grade 
HAVING COUNT(*) > 5;

-- Complex grouping
SELECT grade, 
       AVG(score) as avg_score,
       COUNT(*) as student_count
FROM results
WHERE score >= 60
GROUP BY grade
HAVING AVG(score) >= 75
ORDER BY avg_score DESC;

JOIN Operations

-- Inner Join
SELECT s.student_id, s.first_name, c.course_name
FROM students s
INNER JOIN courses c ON s.course_id = c.course_id;

-- Left Join
SELECT s.student_id, s.first_name, e.enrollment_date
FROM students s
LEFT JOIN enrollments e ON s.student_id = e.student_id;

-- Right Join
SELECT s.student_id, s.first_name, e.enrollment_date
FROM students s
RIGHT JOIN enrollments e ON s.student_id = e.student_id;

-- Full Outer Join
SELECT s.student_id, s.first_name, e.enrollment_date
FROM students s
FULL OUTER JOIN enrollments e ON s.student_id = e.student_id;

Subqueries

-- Subquery in WHERE clause
SELECT * FROM students 
WHERE student_id IN (
    SELECT student_id FROM enrollments 
    WHERE course_id = 101
);

-- Subquery in SELECT clause
SELECT 
    student_id,
    first_name,
    (SELECT COUNT(*) FROM enrollments e 
     WHERE e.student_id = s.student_id) as enrollment_count
FROM students s;

-- Subquery in FROM clause
SELECT grade, student_count
FROM (
    SELECT grade, COUNT(*) as student_count
    FROM students
    GROUP BY grade
) AS grade_summary
WHERE student_count > 10;

Common Patterns

-- Search functionality
SELECT * FROM products 
WHERE product_name LIKE '%laptop%' 
  OR description LIKE '%laptop%';

-- Pagination
SELECT * FROM articles 
ORDER BY published_date DESC 
LIMIT 10 OFFSET 20;

-- Data validation
SELECT * FROM users 
WHERE email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$';

-- Performance optimization
SELECT * FROM large_table 
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
INDEX (created_at);

-- Statistical analysis
SELECT 
    DATE(order_date) as order_date,
    COUNT(*) as order_count,
    SUM(amount) as total_amount
FROM orders
WHERE status = 'completed'
GROUP BY DATE(order_date)
ORDER BY order_date;

---

📚 Learn SQL Theory →