SQL SELECT Practice

Understanding the Problem

We need to practice various SQL SELECT queries on a puppies database table that contains information about different dogs. The table includes columns for id, name, age_yrs, breed, weight_lbs, and microchipped status. We need to write different types of SELECT statements to retrieve specific data based on various conditions.

Key information about the table structure:

CREATE TABLE puppies (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(100),
    age_yrs DECIMAL(2,1),
    breed VARCHAR(100),
    weight_lbs INT,
    microchipped BOOLEAN
);
        

Devising a Plan

1. Write a query to select all data (SELECT *)
2. Write a query to select specific columns (name, age, weight)
3. Write a query to select one row using PRIMARY KEY
4. Write a query to filter rows based on a boolean condition
5. Write a query to filter rows based on numeric comparison
6. Write a query combining multiple conditions

Carrying Out the Plan

Basic Solution: Step by Step

Step 1: Select All Data

-- Select everything from the puppies table
SELECT *
FROM puppies;
        

Step 2: Select Specific Columns

-- Select only name, age, and weight
SELECT name, age_yrs, weight_lbs
FROM puppies;
        

Step 3: Select by Primary Key

-- Select specific puppy with id = 5
SELECT name, age_yrs, weight_lbs
FROM puppies
WHERE id = 5;
        

Step 4: Select by Boolean Condition

-- Select all microchipped puppies
SELECT name, age_yrs, weight_lbs
FROM puppies
WHERE microchipped = 1;
        

Step 5: Select by Numeric Comparison

-- Select puppies over 25 pounds
SELECT name, age_yrs, weight_lbs
FROM puppies
WHERE weight_lbs > 25;
        

Step 6: Select with Multiple Conditions

-- Select large microchipped puppies
SELECT name, age_yrs, weight_lbs
FROM puppies
WHERE weight_lbs > 25 AND microchipped = 1;
        

Alternative Advanced Solutions

Using Aliases

-- Using table and column aliases for cleaner output
SELECT 
    p.name AS puppy_name,
    p.age_yrs AS age,
    p.weight_lbs AS weight
FROM puppies p
WHERE p.weight_lbs > 25 
    AND p.microchipped = 1;
        

Using BETWEEN

-- Using BETWEEN for range queries
SELECT name, age_yrs, weight_lbs
FROM puppies
WHERE weight_lbs BETWEEN 25 AND 70
    AND microchipped = 1;
        

Understanding the Solution

Core Concepts Explained

SELECT statements are like asking questions to a database. Think of the puppies table as a spreadsheet, and SELECT queries as different ways to filter and view that spreadsheet.

Let's break down each component:

SELECT: This specifies which columns you want to see. It's like choosing which spreadsheet columns to keep visible while hiding others.

FROM: This tells the database which table to query. In a library analogy, this would be like specifying which shelf to look at.

WHERE: This filters the rows based on conditions. It's similar to using filters in a spreadsheet or online shop to show only items meeting certain criteria.

Real-World Applications

These SQL concepts are used everywhere in the real world:

E-commerce: Finding products by price range, category, or availability.

Social Media: Displaying posts from users you follow or posts with specific hashtags.

Banking: Finding transactions within a date range or above a certain amount.

Common Pitfalls and Tips

Remember that:

• SQL is not case-sensitive for keywords, but it's common practice to write keywords in uppercase for readability
• String comparisons need single quotes in SQLite
• Boolean values are stored as 0 (false) and 1 (true) in SQLite
• The WHERE clause filters rows before they are returned

Practice Exercises

Try these additional queries:

1. Find all puppies under 1 year old
2. Find all non-microchipped puppies over 20 pounds
3. Find all Miniature Schnauzer puppies
4. Find the heaviest puppy