Skip to main content
Back to path

Welcome to a Grasp lesson

Create a free account to keep studying and create your own study path.

Lesson 56

SQL CRUD Operations

Recommended resources

Hello! In our last lesson, you successfully designed a database schema and created the users and tasks tables for our task manager application. You now have a solid, structured, but empty database. It's time to bring it to life by learning how to manipulate the data within it.

Today, we'll focus on the four fundamental operations that form the backbone of almost any data-driven application: Create, Read, Update, and Delete, commonly known as CRUD. You will learn to write the core SQL queries—INSERT, SELECT, UPDATE, and DELETE—to manage the data in your tables directly from pgAdmin. Mastering these commands is essential before we integrate them into our Node.js backend.

The Four Pillars of Data Manipulation: CRUD

CRUD is an acronym that represents the four essential functions for managing data in a persistent storage system like a database:

  • Create: Add new data.
  • Read: Retrieve existing data.
  • Update: Modify existing data.
  • Delete: Remove existing data.

In SQL, these operations map directly to specific commands:

Operation SQL Command Description
Create INSERT Adds a new row to a table.
Read SELECT Retrieves rows from a table.
Update UPDATE Modifies data in existing rows.
Delete DELETE Removes rows from a table.

Let's dive into each one.

1. Create: INSERT INTO

The INSERT statement adds new rows to a table. The syntax requires you to specify the table, the columns you're providing data for, and the corresponding values.

PostgreSQL CRASH COURSE - Learn PostgreSQL in 2024

This video provides an excellent, practical demonstration of how to use the INSERT INTO statement. Pay close attention to the syntax, especially how string values are enclosed in single quotes and how the SERIAL primary key is handled automatically.

Watch from 16:57 to 21:56. The presenter shows how to insert single rows and explains why you don't specify the id column.

Now, let's apply this to our users table. Open the Query Tool in pgAdmin for your task_manager database and run the following query.

INSERT INTO users (username, email, password_hash) 
VALUES ('testuser', 'test@example.com', 'some_hashed_password');

Key Points:

  • We did not provide a value for id or created_at. The id is a BIGSERIAL type, so PostgreSQL generates it automatically. The created_at column has a DEFAULT value of CURRENT_TIMESTAMP, so it's also handled for us.
  • Column names in the first parenthesis () must correspond to the values in the VALUES() clause.
  • Text-based values like VARCHAR and TEXT must be enclosed in single quotes ('...').
Test your understanding!

Now it's your turn. Write an INSERT statement to add a task for the user you just created. Assume the id of the testuser is 1. The task should have the title "Learn SQL" and its status should be 'in_progress'.

Show answer 
INSERT INTO tasks (user_id, title, status) 
VALUES (1, 'Learn SQL', 'in_progress');

Note: We are inserting the integer 1 for user_id, so it does not need quotes. The other values are text and require single quotes.

2. Read: SELECT

The SELECT statement is used to retrieve data. You can fetch all columns using an asterisk (*) or specify the exact columns you want. The real power of SELECT comes from the WHERE clause, which allows you to filter the results.

PostgreSQL CRASH COURSE - Learn PostgreSQL in 2024

The following segment covers the SELECT statement, showing how to retrieve all columns or specific ones, and introduces the essential WHERE clause for filtering.

Watch from 21:56 to 25:39. Focus on how the WHERE clause is used to specify exactly which rows you want to see.

Let's try it out. To see the user you just created:

-- Select all columns for all users
SELECT * FROM users;

-- Select a user by their username
SELECT * FROM users WHERE username = 'testuser';

-- Select only the email and creation date for that user
SELECT email, created_at FROM users WHERE username = 'testuser';

And to see the task you created:

-- Select all tasks for user with id = 1
SELECT * FROM tasks WHERE user_id = 1;

3. Update: UPDATE

The UPDATE statement modifies existing rows in a table.

Warning: The WHERE clause is critically important here. If you omit it, the UPDATE statement will modify every single row in the table. This is a common and dangerous mistake.

PostgreSQL CRASH COURSE - Learn PostgreSQL in 2024

This video clearly demonstrates how to use the UPDATE statement and reiterates the importance of the WHERE clause.

Watch from 25:39 to 29:21. Notice how a specific row is targeted for an update using its id.

Let's say our user finished their task. We can update the status of the task from 'in_progress' to 'completed'. Assuming the task id is 1:

UPDATE tasks
SET status = 'completed'
WHERE id = 1;

You can verify the change by running a SELECT query again:
SELECT * FROM tasks WHERE id = 1;

4. Delete: DELETE

The DELETE statement removes rows from a table.

Warning: Just like with UPDATE, the WHERE clause is essential. If you omit it, you will delete all data from your table. Always double-check your WHERE clause before running a DELETE command.

PostgreSQL CRASH COURSE - Learn PostgreSQL in 2024

Finally, let's look at how to remove data. This segment explains the DELETE statement and gives another strong warning about using the WHERE clause.

Watch from 29:21 to 30:43 to understand the syntax and its potential dangers.

Imagine we want to delete the task we just completed. Again, assuming the task id is 1:

DELETE FROM tasks WHERE id = 1;

If you run SELECT * FROM tasks; now, the table should be empty.

Test your understanding!
  1. Write a single SQL query to change the username of the user with id = 1 to 'ryanh'.
  2. Write the SQL query to completely remove the user with the username 'ryanh'.
Show answer
  1. Update query: 
    UPDATE users 
SET username = 'ryanh' 
WHERE id = 1;
  2. Delete query: 
    DELETE FROM users 
WHERE username = 'ryanh';

Quick Reference and Further Examples

As you become more comfortable, you'll want a quick way to look up syntax and discover more powerful options. Cheat sheets are invaluable for this.

PostgreSQL Cheat Sheet for Front-End Developers

This web page serves as an excellent, practical cheat sheet. It's targeted at developers and provides concise, copy-paste-ready examples for all CRUD operations, including variations like inserting multiple rows at once.

Bookmark this page. For now, quickly scan the CRUD Operations section. Note the examples for INSERT, SELECT, UPDATE, and DELETE.

Here is another great visual reference you can download and keep handy.

PostgreSQL Cheat Sheet
A comprehensive cheat sheet from LearnSQL.com covering the most common PostgreSQL commands, including all the CRUD operations we've discussed today.

Conclusion

You have now mastered the four fundamental SQL commands for data manipulation. With INSERT, SELECT, UPDATE, and DELETE, you have the complete toolkit to manage the data lifecycle in your database.

Key Takeaways:

  • CRUD Operations: INSERT creates, SELECT reads, UPDATE modifies, and DELETE removes data.
  • WHERE is Crucial: Always use a WHERE clause with UPDATE and DELETE statements to target specific rows and avoid accidental mass data changes.
  • Syntax Matters: Pay close attention to details like using single quotes for strings and not specifying values for auto-generated columns like SERIAL keys.

Preview of the Next Lesson:
We've touched upon the WHERE clause today for basic filtering, but its capabilities go much further. In the next lesson, we will do a deep dive specifically into the WHERE clause, learning how to write more complex conditions using operators like AND, OR, LIKE, IN, and BETWEEN to build powerful and precise queries.