SQL Fundamentals

SQL Fundamentals: Your Magic Wand for Data 🪄

Imagine you have a giant toy box with thousands of toys. You want to find your red car, count how many dolls you have, or see all your toys arranged by size. That’s hard to do by hand, right?

SQL is like a magic wand that helps you find, count, sort, and organize things in a database (your digital toy box) with just a few magic words!

What is SQL?

SQL stands for Structured Query Language. Don’t let the big words scare you!

• Structured = Organized in a neat way
• Query = A question you ask
• Language = How you talk to the database

Think of it like this: When you go to a restaurant, you tell the waiter what you want. The waiter (SQL) goes to the kitchen (database) and brings back exactly what you asked for!

SELECT pizza FROM menu WHERE topping = 'cheese';

This says: “Bring me pizza from the menu that has cheese on top!”

SELECT and WHERE: Finding What You Want

SELECT - “I Want This!”

SELECT is how you say “give me these things.”

SELECT name, age
FROM students;

This is like saying: “Show me the names and ages of all students.”

WHERE - “But Only If…”

WHERE adds a condition - like a filter on your request.

SELECT name, age
FROM students
WHERE age = 10;

This says: “Show me names and ages, but only students who are 10 years old.”

Real Example

Imagine a table called pets:

SELECT name
FROM pets
WHERE animal = 'dog';

Result: Buddy, Max (only the dogs!)

ORDER BY and DISTINCT: Sorting and Removing Duplicates

ORDER BY - “Line Up Nicely!”

ORDER BY arranges your results in order, like lining up students by height.

SELECT name, age
FROM students
ORDER BY age;

ASC = Smallest to biggest (A to Z) - this is the default DESC = Biggest to smallest (Z to A)

SELECT name, age
FROM students
ORDER BY age DESC;

This shows oldest students first!

DISTINCT - “No Repeats Please!”

DISTINCT removes duplicates. Like if you have 5 red balls and 3 blue balls, DISTINCT would just tell you: “red, blue.”

SELECT DISTINCT animal
FROM pets;

If you have 10 dogs and 5 cats in your table, this just shows: dog, cat

SQL Operators: The Power Tools

Operators are like math symbols that help you compare and combine things.

Comparison Operators

Logical Operators

AND - Both conditions must be true

SELECT * FROM toys
WHERE color = 'red' AND price < 10;

(Red toys that cost less than $10)

OR - At least one condition must be true

SELECT * FROM toys
WHERE color = 'red' OR color = 'blue';

(Toys that are red OR blue)

NOT - The opposite of something

SELECT * FROM toys
WHERE NOT color = 'green';

(All toys that are NOT green)

Special Operators

BETWEEN - In a range

SELECT * FROM students
WHERE age BETWEEN 8 AND 12;

IN - Matches any in a list

SELECT * FROM fruits
WHERE name IN ('apple', 'banana', 'orange');

LIKE - Pattern matching (like a guessing game!)

• % = any characters
• _ = exactly one character

SELECT * FROM names
WHERE name LIKE 'A%';

(All names starting with ‘A’)

SQL Aggregate Functions: Counting and Calculating

Aggregate functions are like calculators for your data. They take many values and give you ONE answer.

The Big Five

Examples

SELECT COUNT(*) FROM students;

Answer: 25 (there are 25 students)

SELECT AVG(age) FROM students;

Answer: 10.5 (average age is 10.5 years)

SELECT MAX(score) FROM tests;

Answer: 100 (the highest test score)

SELECT SUM(price) FROM orders;

Answer: 500 (total money from all orders)

GROUP BY and HAVING: Organizing Into Teams

GROUP BY - “Sort Into Groups!”

GROUP BY puts similar things together, like sorting candies by color.

SELECT animal, COUNT(*) as total
FROM pets
GROUP BY animal;

Result:

This counts how many of each animal you have!

HAVING - “Filter the Groups!”

HAVING is like WHERE, but for groups. It filters AFTER grouping.

SELECT animal, COUNT(*) as total
FROM pets
GROUP BY animal
HAVING COUNT(*) > 2;

This shows only animals where you have MORE than 2.

Result:

(Bird is gone because we only have 2 birds!)

WHERE vs HAVING

• WHERE filters individual rows BEFORE grouping
• HAVING filters groups AFTER grouping

SELECT animal, COUNT(*) as total
FROM pets
WHERE age > 1
GROUP BY animal
HAVING COUNT(*) >= 2;

This:

1. First finds pets older than 1 year (WHERE)
2. Groups them by animal type (GROUP BY)
3. Shows only groups with 2 or more pets (HAVING)

SQL NULL Handling: Dealing With Empty Spaces

What is NULL?

NULL means “nothing” or “unknown” - like an empty box. It’s NOT the same as zero or blank text!

Bob doesn’t have a phone number - it’s NULL (unknown).

Finding NULL Values

You can’t use = with NULL. Use IS NULL or IS NOT NULL:

SELECT name
FROM contacts
WHERE phone IS NULL;

Result: Bob (only Bob has no phone)

SELECT name
FROM contacts
WHERE phone IS NOT NULL;

Result: Alice, Carol (people with phone numbers)

NULL is Tricky!

Important rule: NULL in math = NULL

5 + NULL = NULL
NULL = NULL is NOT true!

COALESCE - Replacing NULL

COALESCE gives a backup value when something is NULL:

SELECT name,
       COALESCE(phone, 'No phone') as phone
FROM contacts;

Result:

Putting It All Together

Here’s a complete query using everything we learned:

SELECT category,
       COUNT(*) as total_products,
       AVG(price) as avg_price,
       MAX(price) as highest_price
FROM products
WHERE price IS NOT NULL
  AND available = true
GROUP BY category
HAVING COUNT(*) >= 5
ORDER BY avg_price DESC;

This magical query:

1. Looks at products that have prices and are available
2. Groups them by category
3. Counts products, finds average and max price per category
4. Only shows categories with 5+ products
5. Sorts by average price (highest first)

Quick Reference Flow

graph TD
    A["FROM - Pick your table"] --> B["WHERE - Filter rows"]
    B --> C["GROUP BY - Make groups"]
    C --> D["HAVING - Filter groups"]
    D --> E["SELECT - Pick columns"]
    E --> F["DISTINCT - Remove duplicates"]
    F --> G["ORDER BY - Sort results"]

You Did It! 🎉

You now know the core magic spells of SQL:

• SELECT/WHERE - Find exactly what you need
• ORDER BY/DISTINCT - Sort and clean your results
• Operators - Compare and combine conditions
• Aggregate Functions - Count, sum, average, min, max
• GROUP BY/HAVING - Organize data into groups
• NULL Handling - Deal with missing data

With these tools, you can ask almost any question about your data. Practice makes perfect - now go play with SQL! 🚀