SQL Basics

SQL (Structured Query Language) is how you talk to a database. You'd use English to ask a librarian for a book, and you use SQL to ask a database for data. Good news? SQL reads almost like plain English, which makes it one of the friendliest programming languages out there to pick up.

Your First SQL Query#

The most basic SQL command there is happens to be SELECT. It's how you ask the database to show you some data.

SELECT * FROM users;

Let's pull that apart.

• SELECT means "I want to get some data"
• * means "give me every column"
• FROM users means "look in the users table"
• ; marks the end of the statement

That query hands back every row and every column from the users table. That's it. You just read a database.

Selecting Specific Columns#

You won't always need every column. To grab just a few, list them by name.

SELECT name, email FROM users;

This returns only the name and email columns and skips everything else. When a table has a ton of columns and you only need a couple, this is the more efficient move.

Filtering with WHERE#

The WHERE clause lets you filter which rows come back.

-- Find users older than 25
SELECT * FROM users WHERE age > 25;
 
-- Find a specific user by name
SELECT * FROM users WHERE name = 'Alice';
 
-- Find users with a specific email domain
SELECT * FROM users WHERE email LIKE '%@gmail.com';

Here are the comparison operators you'll use the most.

• = equals
• != or <> not equals
• > greater than
• < less than
• >= greater than or equal
• <= less than or equal
• LIKE pattern matching (% is a wildcard)

Combining Conditions with AND/OR#

You can stack more than one condition together.

-- Users older than 25 AND from gmail
SELECT * FROM users
WHERE age > 25 AND email LIKE '%@gmail.com';
 
-- Users named Alice OR named Bob
SELECT * FROM users
WHERE name = 'Alice' OR name = 'Bob';

Sorting Results with ORDER BY#

Use ORDER BY to put your results in order.

-- Sort by age, youngest first (ascending - default)
SELECT * FROM users ORDER BY age;
 
-- Sort by age, oldest first (descending)
SELECT * FROM users ORDER BY age DESC;
 
-- Sort by name alphabetically
SELECT * FROM users ORDER BY name;

Limiting Results#

Use LIMIT to cap how many rows come back.

-- Get only the first 10 users
SELECT * FROM users LIMIT 10;
 
-- Get the 3 oldest users
SELECT * FROM users ORDER BY age DESC LIMIT 3;

Counting and Aggregating#

SQL comes with built-in functions that summarize your data for you.

-- Count all users
SELECT COUNT(*) FROM users;
 
-- Find the average age
SELECT AVG(age) FROM users;
 
-- Find the oldest user's age
SELECT MAX(age) FROM users;
 
-- Count users with gmail addresses
SELECT COUNT(*) FROM users WHERE email LIKE '%@gmail.com';

Putting It All Together#

Here's a more realistic query that mixes everything you just learned.

-- Find the 5 newest adult users, showing just their name and email
SELECT name, email
FROM users
WHERE age >= 18
ORDER BY created_at DESC
LIMIT 5;

Read it out loud and it's basically a sentence... "select the name and email from users where age is at least 18, ordered by creation date (newest first), limit to 5 results."

SQL is Case-Insensitive (But Conventions Matter)#

SQL keywords don't care about capital letters, so all three of these do the exact same thing.

SELECT * FROM users;
select * from users;
Select * From Users;

The convention, though, is SQL keywords in UPPERCASE and table and column names in lowercase. Your queries get WAY easier to read that way (and future you will say thanks).

TL;DR#

• SELECT gets data, FROM picks the table and WHERE filters the rows
• Use ORDER BY to sort results and LIMIT to cap how many rows come back
• Aggregate functions like COUNT(), AVG() and MAX() summarize your data
• SQL reads almost like English, which makes it really approachable
• Write SQL keywords in UPPERCASE by convention so they're easy to read

What's Next?#

Now that you can read data out of existing tables, the next step is making tables of your own. You'll lay out the structure of your data, pick the right data types and set up constraints that keep your data clean...