SQL Fundamentals: SELECT, INSERT, UPDATE, DELETE Explained

# SQL Fundamentals: SELECT, INSERT, UPDATE, DELETE Explained Every application you build—from a simple todo app to a globally distributed edge database—relies on the same four core SQL operations: **SELECT**, **INSERT**, **UPDATE**, and **DELETE**. Together they form the CRUD backbone (Create, Read, Update, Delete) of every database-driven application. In this tutorial, we'll walk through each one with clear syntax and practical examples using SQLite, the engine that powers Turso's edge database platform. --- ## Table of Contents 1. [Setting Up: Our Example Table](#setting-up) 2. [SELECT — Reading Data](#select) 3. [INSERT — Adding Data](#insert) 4. [UPDATE — Modifying Data](#update) 5. [DELETE — Removing Data](#delete) 6. [Putting It All Together](#putting-it-together) 7. [Next Steps](#next-steps) --- ## Setting Up: Our Example Table Throughout this tutorial we'll use a simple `users` table: ```sql CREATE TABLE users ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, email TEXT UNIQUE NOT NULL, role TEXT DEFAULT 'member', created_at DATETIME DEFAULT CURRENT_TIMESTAMP ); ``` This table has five columns: an auto-incrementing ID, a name, a unique email, a role with a default value, and a timestamp. Let's populate it with some seed data: ```sql INSERT INTO users (name, email, role) VALUES ('Ada Lovelace', 'ada@example.com', 'admin'), ('Grace Hopper', 'grace@example.com', 'member'), ('Linus Torvalds', 'linus@example.com', 'member'); ``` Now we have three rows to work with. Let's dive into each operation. --- ## SELECT — Reading Data The `SELECT` statement is the most frequently used SQL operation. It retrieves rows from one or more tables. ### Basic SELECT ```sql SELECT name, email FROM users; ``` This returns the `name` and `email` columns for every row: | name | email | |------|-------| | Ada Lovelace | ada@example.com | | Grace Hopper | grace@example.com | | Linus Torvalds | linus@example.com | ### SELECT All Columns ```sql SELECT * FROM users; ``` The `*` wildcard returns every column. It's convenient for exploration, but in production code you should always list the specific columns you need for better performance and clarity. ### Filtering with WHERE ```sql SELECT name, email FROM users WHERE role = 'admin'; ``` | name | email | |------|-------| | Ada Lovelace | ada@example.com | You can combine multiple conditions with `AND` and `OR`: ```sql SELECT name, email FROM users WHERE role = 'member' AND name LIKE 'G%'; ``` ### Sorting with ORDER BY ```sql SELECT name, email FROM users ORDER BY name ASC; ``` Use `ASC` for ascending (default) or `DESC` for descending order: ```sql SELECT name, created_at FROM users ORDER BY created_at DESC; ``` ### Limiting Results ```sql SELECT name, email FROM users LIMIT 10; ``` In SQLite, `LIMIT` is especially useful for pagination. Combine it with `OFFSET` to skip rows: ```sql SELECT name, email FROM users LIMIT 10 OFFSET 20; ``` ### Aggregations ```sql SELECT role, COUNT(*) as count FROM users GROUP BY role; ``` | role | count | |------|-------| | admin | 1 | | member | 2 | Other aggregate functions available in SQLite include `MAX()`, `MIN()`, `AVG()`, and `SUM()`. --- ## INSERT — Adding Data The `INSERT` statement adds new rows to a table. ### Single Row Insert ```sql INSERT INTO users (name, email, role) VALUES ('Dennis Ritchie', 'dennis@example.com', 'member'); ``` Columns you omit will use their default values. In our table, `id` auto-increments and `created_at` defaults to the current timestamp. ### Insert Without Column Names ```sql INSERT INTO users VALUES (NULL, 'Dennis Ritchie', 'dennis@example.com', 'member', CURRENT_TIMESTAMP); ``` This works but is fragile—if you add or reorder columns later, the statement breaks. **Always specify column names** in production code. ### Multi-Row Insert ```sql INSERT INTO users (name, email, role) VALUES ('Dennis Ritchie', 'dennis@example.com', 'member'), ('Ken Thompson', 'ken@example.com', 'admin'), ('Brian Kernighan', 'brian@example.com', 'member'); ``` SQLite supports inserting multiple rows in a single statement, which is far more efficient than individual inserts—especially at the edge where network round-trips matter. ### Insert or Ignore ```sql INSERT OR IGNORE INTO users (name, email, role) VALUES ('Ada Lovelace', 'ada@example.com', 'admin'); ``` Since `email` has a `UNIQUE` constraint, this insert would normally fail. `OR IGNORE` silently skips it instead. You can also use `INSERT OR REPLACE` to overwrite the existing row. --- ## UPDATE — Modifying Data The `UPDATE` statement modifies existing rows. **Always include a `WHERE` clause**—without it, you'll update every row in the table. ### Basic Update ```sql UPDATE users SET role = 'admin' WHERE name = 'Grace Hopper'; ``` This promotes Grace to admin. After the update: ```sql SELECT name, role FROM users WHERE name = 'Grace Hopper'; ``` | name | role | |------|------| | Grace Hopper | admin | ### Updating Multiple Columns ```sql UPDATE users SET role = 'member', email = 'linus.t@example.com' WHERE name = 'Linus Torvalds'; ``` ### Conditional Updates ```sql UPDATE users SET role = 'admin' WHERE role = 'member' AND name LIKE 'D%'; ``` This promotes all members whose name starts with "D" to admin. ### Safe Update Pattern A defensive habit: run a `SELECT` first to verify which rows will be affected, then run the `UPDATE` with the same `WHERE` clause. ```sql -- Check first SELECT id, name, role FROM users WHERE role = 'member'; -- Then update UPDATE users SET role = 'admin' WHERE role = 'member'; ``` --- ## DELETE — Removing Data The `DELETE` statement removes rows from a table. Like `UPDATE`, **always use a `WHERE` clause** to avoid deleting everything. ### Basic Delete ```sql DELETE FROM users WHERE name = 'Dennis Ritchie'; ``` ### Delete with Multiple Conditions ```sql DELETE FROM users WHERE role = 'member' AND name LIKE 'K%'; ``` ### Delete All Rows (Use with Caution!) ```sql DELETE FROM users; ``` This removes every row but keeps the table structure intact. The auto-increment counter is not reset unless you also run: ```sql DELETE FROM sqlite_sequence WHERE name = 'users'; ``` ### Safe Delete Pattern Just like with updates, verify before you delete: ```sql -- Check first SELECT id, name FROM users WHERE role = 'member'; -- Then delete DELETE FROM users WHERE role = 'member'; ``` --- ## Putting It All Together Here's a realistic workflow that uses all four operations: ```sql -- 1. INSERT a new user INSERT INTO users (name, email, role) VALUES ('Margaret Hamilton', 'margaret@example.com', 'member'); -- 2. SELECT to verify SELECT * FROM users WHERE email = 'margaret@example.com'; -- 3. UPDATE to promote UPDATE users SET role = 'admin' WHERE email = 'margaret@example.com'; -- 4. DELETE a deactivated account DELETE FROM users WHERE email = 'margaret@example.com'; ``` ### Pro Tip: Use Transactions Wrap related operations in a transaction so they either all succeed or all roll back: ```sql BEGIN TRANSACTION; INSERT INTO users (name, email, role) VALUES ('Margaret Hamilton', 'margaret@example.com', 'admin'); UPDATE users SET role = 'member' WHERE email = 'ada@example.com'; COMMIT; ``` If anything fails before `COMMIT`, all changes are automatically rolled back. This is essential for data integrity. --- ## Next Steps Now that you've mastered the four fundamental SQL operations, here's where to go next: - **Practice with Turso** — Sign up for a free Turso account and run these examples against a real edge database in seconds. Turso uses SQLite under the hood, so everything in this tutorial works out of the box. - **Learn advanced querying** — Explore `JOIN`s, subqueries, window functions, and CTEs in our [Advanced SQL tutorial](/advanced-sql-window-functions-ctes-recursive-queries). - **Explore SQLite-specific features** — SQLite supports full-text search (FTS5), JSON operations, and vector search. Check out our [SQLite indexing guide](/sqlite-indexing-strategies-btree-fts5-vector-search) to learn more. - **Build something real** — Use Turso's TypeScript, Python, Go, or Rust SDKs to connect your application to a distributed SQLite database at the edge. SQL is a skill that pays dividends across every database you'll ever work with—SQLite, PostgreSQL, Oracle, MySQL, and beyond. Master these four operations and you've covered 80% of what you'll do in any database.