Intermediate SELECT Queries

Mastering Advanced Data Selection in SQL

Step 1: Understanding the Problem

The Music Database Scenario

Imagine you're a music industry analyst working with an album database. Your task is to extract specific information about albums based on various criteria, much like a librarian finding books that match certain patterns or requirements.

Think of our database as a massive record store where each album has specific details: its title, the band that created it, the year it was released, and how many copies were sold. Our job is to find albums that match specific criteria, just like helping customers find albums that meet their interests.

Our Album Collection (Database Structure)

Each album in our collection has these attributes:

CREATE TABLE albums (
    id INTEGER PRIMARY KEY AUTOINCREMENT,    -- Unique identifier for each album
    title VARCHAR(100) NOT NULL,             -- Album name
    band_id INTEGER,                         -- Which band created it
    year INTEGER NOT NULL,                   -- Release year
    num_sold INTEGER NOT NULL DEFAULT 0      -- Sales figures
);

Step 2: Devising a Plan

We'll tackle our queries in order of complexity:

1. Query albums by sales threshold (basic comparison)

2. Query albums within a time period (range comparison)

3. Query albums by specific band IDs (list matching)

4. Query albums by title pattern (string pattern matching)

5. Query top-selling albums (ordered selection)

6. Query specific rankings (offset selection)

Step 3: Carrying Out the Plan

Basic SELECT Queries

Query 1: Finding High-Selling Albums

Just like finding books that have sold over a certain number of copies:

-- Find albums that sold 100,000 or more copies
SELECT *
FROM albums
WHERE num_sold >= 100000;

/* Expected output would include:
- The End (120,000)
- Under Water (106,000)
- Another Fork (140,000)
*/

Query 2: Finding Albums from a Specific Era

Similar to finding books published during a specific period:

-- Find albums released between 2018 and 2020
SELECT *
FROM albums
WHERE year BETWEEN 2018 AND 2020;

/* This shows us all albums from a three-year window,
   including albums like 'Again' (2018) and 'The End' (2020) */

Query 3: Finding Albums by Specific Bands

Like finding books by a select group of authors:

-- Find albums by bands 1, 3, or 4
SELECT *
FROM albums
WHERE band_id IN (1, 3, 4);

Advanced SELECT Queries

Pattern Matching: Albums Starting with 'The'

-- Find albums with titles starting with 'The'
SELECT *
FROM albums
WHERE title LIKE 'The%';

/* The % wildcard matches any characters after 'The',
   finding albums like 'The Falling Box' and 'The End' */

Finding Top Sellers

-- Find the two highest-selling albums
SELECT *
FROM albums
ORDER BY num_sold DESC
LIMIT 2;

/* This shows us the absolute bestsellers,
   ordered from highest to lowest sales */

Finding Specific Rankings

-- Find the third and fourth highest-selling albums
SELECT *
FROM albums
ORDER BY num_sold DESC
LIMIT 2 OFFSET 2;

/* OFFSET 2 skips the first two results,
   giving us the next two highest sellers */

Step 4: Looking Back and Reflecting

Verifying Our Results

For each query, we should ask ourselves:

• Does the output match our expected results?

• Have we correctly handled edge cases?

• Is our query efficient and readable?

Recognizing Query Patterns

We've learned several fundamental SQL patterns:

• Comparison operators (>=, BETWEEN)

• List matching (IN)

• Pattern matching (LIKE)

• Ordering and limiting (ORDER BY, LIMIT, OFFSET)

Real-World Applications

Similar Database Queries in Other Contexts

These query patterns apply to many real-world scenarios:

• E-commerce: Finding products above a certain price point

• Social Media: Finding posts from specific dates

• Library Systems: Finding books by particular publishers

• Analytics: Finding top-performing items

Try These Variations

To reinforce your understanding, try modifying our queries to:

• Find albums that sold between 50,000 and 100,000 copies

• Find albums released in even-numbered years

• Find albums with titles ending in specific words

• Find the bottom three selling albums