Getting Started with SQL: A Hands-On Tutorial

SQL (Structured Query Language) is how you talk to databases. Whether you're building a web app, analyzing data, or just trying to understand what's in a database, SQL is an essential skill.

The good news: SQL is surprisingly approachable. You can learn the fundamentals in an afternoon, and those fundamentals will cover 90% of what you'll ever need to do.

This tutorial is hands-on. Every example below runs in an interactive playground - click "Run" to execute the query, or modify it and try your own. Let's dive in.

1. SELECT: Reading Data

Think of a database table like a spreadsheet. SELECT is how you ask "show me what's in this spreadsheet." It's the most common SQL operation - you'll use it constantly.

The * is a wildcard meaning "all columns" - like saying "show me everything." But sometimes you only care about specific columns. It's like asking someone for their name and phone number instead of their entire life story:

Filtering with WHERE

WHERE is your filter - it's like telling a librarian "I only want books published after 2020" instead of asking them to bring you every book in the library. You specify conditions, and SQL only returns rows that match:

You can use different comparison operators. Think of them like the filters on a shopping website - price greater than X, rating equals 5 stars, etc:

• = equals
• > greater than, < less than
• >= at least, <= at most
• != not equal
• LIKE pattern matching (use % as a wildcard)

Sorting with ORDER BY

ORDER BY arranges your results - like sorting your email by date or your music by artist. Use DESC for descending (biggest/newest first) or ASC for ascending (smallest/oldest first):

Limiting Results

LIMIT caps how many rows come back - like saying "just show me the first 10 results" on a search engine. It's especially useful combined with ORDER BY to get "top N" results:

2. INSERT: Adding Data

INSERT adds new rows to your table. Think of it like adding a new row to a spreadsheet, or a new contact to your phone. You specify which columns you're filling in and what values to put there:

Need to add several people at once? You can insert multiple rows in one statement - like batch-importing contacts instead of typing them one by one:

3. UPDATE: Changing Data

UPDATE modifies existing rows. It's like editing a cell in a spreadsheet - you're not adding or removing rows, just changing what's already there. You use SET to specify the new values:

Important: Always use WHERE with UPDATE. Without it, you'll update every single row in the table. It's like doing find-and-replace in a document without specifying what to find - chaos:

4. DELETE: Removing Data

DELETE removes rows from your table. Think of it like selecting rows in a spreadsheet and hitting the delete key - they're gone:

Warning: Just like UPDATE, always use WHERE with DELETE. Running DELETE FROM employees; without a WHERE clause is like selecting all rows and hitting delete - everyone's gone. There's no undo button:

5. JOINs: Combining Tables

Real databases split data across multiple tables to avoid repetition. Imagine a library system: you wouldn't write the full publisher address on every single book - you'd have a separate publishers table and just reference it.

Look at our orders and customers - orders just stores a customer_id, not the full customer details:

A JOIN connects these tables together, like looking up a phone number in your contacts. You tell SQL how the tables relate (which columns match up), and it stitches them together:

The JOIN ... ON tells SQL "match rows where these columns are equal." Here, every order's customer_id gets matched to a customer's id, pulling in their name.

You can filter and sort joined data just like regular queries:

6. Aggregations: Summarizing Data

Sometimes you don't want individual rows - you want summaries. "How many employees do we have? What's the average salary? What's our total payroll?" These are aggregate functions - they crunch multiple rows into a single answer, like a calculator for your data:

GROUP BY

GROUP BY splits your data into buckets before aggregating. Instead of "average salary across everyone," you can ask "average salary per department." It's like sorting your expenses into categories before totaling each one:

This works with JOINs too - here's total spending per customer:

What's Next?

You've just learned the core of SQL:

• SELECT - read data with filtering, sorting, and limiting
• INSERT - add new rows
• UPDATE - modify existing rows (always use WHERE!)
• DELETE - remove rows (always use WHERE!)
• JOIN - combine related tables
• Aggregations - summarize with COUNT, SUM, AVG, and GROUP BY

These fundamentals cover the vast majority of day-to-day database work. From here, you can explore:

• Subqueries - queries inside queries
• Indexes - making queries faster
• Transactions - grouping operations that should succeed or fail together

If you're using SQLite specifically, check out our posts on JSON virtual columns, JSON functions, and full-text search for more advanced features.

Happy querying!

Jay

---