SQL & CRUD Basics for AI Coders
The four words that run the entire internet's data, explained without jargon.
In this chapter
- What "CRUD" means and why every app needs it
- How to read a SQL query in plain English
- How to ask AI for the queries you need
- How to spot common SQL mistakes before they cause problems
CRUD: The Four Things You Do With Data
Every app (Twitter, Airbnb, your todo list) does the same four things with data. They're called CRUD:
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: Reading Data
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.
INSERT: Creating New Rows
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', '[email protected]', 'France');
Plain English: "Add a new row to the users table. Set name to Maya, email to [email protected], and country to France."
UPDATE: Changing Existing Rows
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."
DELETE: Removing Rows
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."
How to Read a SQL Query in 10 Seconds
Even complex queries follow the same pattern. Here's the order:
SELECT name, email
Which columns do I want?
FROM users
Which table?
WHERE country = 'France'
Which rows? (filter)
ORDER BY created_at DESC
What order?
LIMIT 10
How many?
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.
JOINs: Combining Two Tables
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."
Real World Example: Building a 'most popular posts' page
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.
“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.”
5 Common SQL Mistakes
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.
Prompt This
“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.”
“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?”
“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.”
Frequently Asked Questions
Do I need to memorize SQL?
No. Your AI tool writes the queries for you. But knowing what SELECT, INSERT, UPDATE, and DELETE do helps you describe what you want and spot mistakes when AI writes the wrong query.
What's the difference between SQL and a database?
A database is the storage (like a filing cabinet). SQL is the language you use to talk to it (like asking the cabinet 'show me all the green folders'). Most databases (Postgres, MySQL, SQLite, Supabase) speak SQL.
Is SQL the same in every database?
Mostly. The basics (SELECT, INSERT, UPDATE, DELETE) work the same everywhere. Some advanced features differ between databases. For 95% of what you'll do, SQL is universal.
What's a 'NoSQL' database then?
NoSQL databases (like MongoDB or Firebase) don't use SQL. They store data as flexible documents instead of strict tables. They're powerful but harder to learn. Stick with SQL databases (Postgres via Supabase) until you have a specific reason not to.