Intermediate7 min read
Was this chapter helpful?
Share
The four words that run the entire internet's data — explained without jargon.
Don't worry — you won't write any SQL code by hand. Your AI tool handles all the technical parts. This guide helps you understand the concepts so you can describe what you want.
Every app — Twitter, Airbnb, your todo list — does the same four things with data. They're called CRUD:
C = Create
SQL: INSERT
Add a new row to a table. Sign up a new user, post a new tweet, add an item to a cart.
R = Read
SQL: SELECT
Get rows out of a table. Show all tweets, find one user by email, list this week's orders.
U = Update
SQL: UPDATE
Change a row that already exists. Edit a profile, mark a task as done, change a price.
D = Delete
SQL: DELETE
Remove a row. Delete an account, remove a comment, cancel an order.
That's the whole job of a database: store stuff, get it back, change it, delete it. Everything else is fancy variations on these four.
SELECT is the most common SQL command. You'll use it all the time to fetch data and show it on a page.
A simple SELECT query
SELECT name, email FROM users;
Plain English: "Give me the name and email columns from every row in the users table."
SELECT with a filter (WHERE)
SELECT * FROM users WHERE country = 'France';
Plain English: "Give me all columns from users where the country is France." The * means "everything."
SELECT with sorting and limit
SELECT * FROM posts ORDER BY created_at DESC LIMIT 10;
Plain English: "Give me the 10 newest posts, sorted with the newest first." DESC = descending order.
When a user signs up, posts a tweet, or adds something to a cart, you INSERT a new row.
INSERT INTO users (name, email, country)
VALUES ('Maya', 'maya@example.com', 'France');Plain English: "Add a new row to the users table. Set name to Maya, email to maya@example.com, and country to France."
Tip: You don't need to set every column. Columns you skip will be NULL (empty) or use a default value. The database fills in things like the row ID and the created_at timestamp automatically.
When a user edits their profile or marks a task as done, you UPDATE a row that already exists.
UPDATE users SET country = 'Spain' WHERE id = 42;
Plain English: "Find the user with ID 42 and change their country to Spain."
⚠️ Important: If you forget the WHERE clause, UPDATE changes every row in the table. Always include WHERE. Always.
When a user deletes their account or removes a comment, you DELETE the row.
DELETE FROM users WHERE id = 42;
Plain English: "Delete the user with ID 42."
⚠️ Same warning: Forget the WHERE on a DELETE and you wipe the entire table. Many production apps prefer "soft deletes" (a deleted_at column) instead of real deletes for this reason.
Even complex queries follow the same pattern. Here's the order:
SELECT
Which columns do I want?
SELECT name, email
FROM
Which table?
FROM users
WHERE
Which rows? (filter)
WHERE country = 'France'
ORDER BY
What order?
ORDER BY created_at DESC
LIMIT
How many?
LIMIT 10
Read top to bottom. "Give me the name and email columns from the users table where country is France, sorted by newest first, only the first 10."That's it.
Most apps store related data across multiple tables. Users in one table, orders in another. To get a user's orders, you JOIN the tables together.
SELECT users.name, orders.total FROM orders JOIN users ON orders.user_id = users.id;
Plain English: "Show me each order's total along with the name of the user who placed it. Match them by linking orders.user_id to users.id."
You don't need to memorize the syntax. Just know that JOIN exists and tell AI "join the orders table with the users table to show the customer name on each order."
You have a blog with hundreds of posts and a 'likes' table that tracks which user liked which post. You want a page that shows the 10 most-liked posts of all time, with each post's title, author name, and total like count. That's one SQL query — but it joins three tables and uses GROUP BY. You don't need to know the syntax; you just describe what you want.
Build this with AI
"On my blog, I want a /popular page that shows the top 10 posts by total likes. For each post, show the title, author name, publish date, and number of likes. Sort by like count, highest first. Use a single SQL query that joins my posts, users, and likes tables. Walk me through what the query does in plain English."
Forgot the WHERE clause on UPDATE or DELETE
Always double-check that UPDATE and DELETE queries have a WHERE. Without it, you change or delete every row.
Using SELECT * everywhere
SELECT * fetches every column, even huge ones you don't need. List specific columns to make queries faster.
No index on a column you filter by
If WHERE country = 'France' is slow, add an index on the country column. AI knows how to do this.
Storing dates as text
Always use a real date/timestamp column. Text dates can't be sorted or compared properly.
Trusting user input directly in queries (SQL injection)
Always use parameterized queries. AI tools and ORMs do this automatically — just don't turn it off.
WRITE A QUERY FOR ME
"Write a SQL query that does the following: [describe what you want — e.g., 'show all orders from the last 30 days with the customer name, sorted by order total descending']. Use my Supabase / Postgres database. Explain what the query does in plain English so I understand it."
EXPLAIN THIS QUERY
"Here's a SQL query I don't understand: [paste query]. Explain what it does step by step in plain English. What table does it touch, what does it filter, what does it return?"
OPTIMIZE A SLOW QUERY
"This query is slow: [paste query]. Tell me what's making it slow and how to fix it. If I need to add an index, write the CREATE INDEX statement and explain what it does."