SQL provides a robust set of constructs for retrieving, filtering, transforming, and modifying relational data. Below are foundational patterns used across database systems—with syntax adapted for portability and clarity.

1. Basic Column Selection

Retrieve specific columns instead of all fields to improve performance and readability:

SELECT title, author, published_year 
FROM books;

Omitting * avoids unnecessary I/O and makes intent explicit.

2. Eliminating Duplicates

Use DISTINCT to collapse redundant rows in result sets:

SELECT DISTINCT genre 
FROM books 
WHERE published_year > 2015;

3. Conditional Filtering with WHERE

Apply precise filters—strings require single quotes; numbers do not:

SELECT title, isbn 
FROM books 
WHERE genre = 'Science Fiction' AND rating >= 4.2;

4. Compound Conditions

Chain criteria using AND, OR, or nested logic with parentheses:

SELECT title, publisher 
FROM books 
WHERE (genre = 'Mystery' OR genre = 'Thriller') 
  AND stock_quantity > 0;

5. Sorting Results

Order output using ORDER BY; specify direction explicitly:

SELECT title, rating, published_year 
FROM books 
ORDER BY rating DESC, published_year ASC;

6. Limiting Result Size

Cap returned rows—syntax varies by dialect (LIMIT in PostgreSQL/MySQL, TOP in SQL Server):

-- PostgreSQL / MySQL
SELECT title, rating FROM books ORDER BY rating DESC LIMIT 10;

-- SQL Server
SELECT TOP 10 title, rating FROM books ORDER BY rating DESC;

7. Pattern Matching with LIKE

Match partial strings using wildcards: % (zero or more chars), _ (exactly one char):

SELECT title FROM books 
WHERE title LIKE 'The % Guide%';

SELECT isbn FROM books 
WHERE isbn LIKE '978-1-%';

8. Multi-Value Filtering

Replace repeated OR conditions with IN:

SELECT title, genre 
FROM books 
WHERE genre IN ('Biography', 'History', 'Philosophy');

9. Range-Based Filtering

BETWEEN is inclusive and supports numeric, textual, and temporal ranges:

SELECT title, published_year 
FROM books 
WHERE published_year BETWEEN 2010 AND 2020;

SELECT title 
FROM books 
WHERE title BETWEEN 'A' AND 'Dz';

10. Aliasing Columns and Tables

Improve readability and enable complex expressions:

-- Column alias
SELECT 
  UPPER(SUBSTRING(title, 1, 20)) AS truncated_title,
  ROUND(rating, 1) AS rounded_rating
FROM books;

-- Table alias
SELECT b.title, a.review_text
FROM books b
INNER JOIN book_reviews a ON b.isbn = a.book_isbn
WHERE b.genre = 'Fantasy';

11. Combining Data Across Tables

Join related tables using shared keys. Key types include:

• INNER JOIN: Returns only matching rows from both sides.
• LEFT JOIN: Preserves all left-side rows—even without matches on the right.
• RIGHT JOIN: Analogous but prioritizes the right table (less common).

SELECT 
  b.title,
  COUNT(r.id) AS review_count,
  AVG(r.rating) AS avg_review_score
FROM books b
LEFT JOIN book_reviews r ON b.isbn = r.book_isbn
GROUP BY b.isbn, b.title;

12. Merging Result Sets

UNION combines results vertically—removes duplicates; UNION ALL retains them:

SELECT 'book' AS source_type, title FROM books
UNION ALL
SELECT 'ebook' AS source_type, title FROM ebooks
ORDER BY title;

13. Modifying Existing Data

Udpate records selectively—always test with a SELECT first:

UPDATE books 
SET rating = rating + 0.1, last_updated = CURRENT_DATE
WHERE genre = 'Classics' AND rating < 4.5;

14. Removing Records Safely

Prevent accidental mass deletion by validating conditions beforehand:

DELETE FROM book_reviews 
WHERE rating < 2.0 
  AND review_date < '2020-01-01';

15. Inserting New Rows

Insert into specific columns—omitted columns use defaults or accept NULLs:

INSERT INTO books (title, author, isbn, genre, published_year)
VALUES 
  ('Data Structures Decoded', 'L. Chen', '978-1-999-45678-9', 'Computer Science', 2023),
  ('Quantum Narratives', 'M. Ruiz', '978-1-888-12345-6', 'Speculative Fiction', 2024);

16. Aggregating and Grouping

Group rows and compute summaries per group using GROUP BY:

SELECT 
  genre,
  COUNT(*) AS total_books,
  AVG(rating) AS avg_rating,
  MAX(published_year) AS latest_release
FROM books 
GROUP BY genre
HAVING COUNT(*) > 3
ORDER BY avg_rating DESC;

Note: HAVING filters groups (post-aggregation); WHERE filters individual rows (pre-aggregation).

17. Core Aggregate Functions

• COUNT(*): Total rows in group
• COUNT(column): Non-NULL values only
• COUNT(DISTINCT column): Unique non-NULL values
• SUM(), AVG(), MIN(), MAX(): Numeric summaries

SELECT 
  COUNT(*) AS total_reviews,
  COUNT(rating) AS rated_reviews,
  COUNT(DISTINCT reviewer_id) AS unique_reviewers,
  SUM(helpful_votes) AS total_helpful
FROM book_reviews;