DEV Community

Cover image for Basic SQL Operations (CRUD)
arjun
arjun

Posted on

Basic SQL Operations (CRUD)

Day 3: Basic SQL Operations (CRUD)

Date: January 21, 2025

On Day 3 of your database learning journey, we dive into the essential SQL operations that form the foundation of any database interaction: CRUD—Create, Read, Update, and Delete. These operations allow you to manage data efficiently and are critical for real-world applications.


What is CRUD?

CRUD stands for:

Create: Adding new records to the database.

Read: Retrieving data from the database.

Update: Modifying existing records in the database.

Delete: Removing records from the database.

Mastering these operations ensures that you can handle all core database interactions.


Key Concepts

  1. CREATE

The CREATE statement is used to define the structure of your database, such as tables, along with their columns and data types.

Example:

CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE
);


  1. INSERT

The INSERT statement is used to add new records into a table.

Example:

INSERT INTO users (name, email)
VALUES ('Alice', 'alice@example.com');


  1. SELECT

The SELECT statement retrieves data from one or more tables.

Example:

Retrieve all user data:

SELECT * FROM users;

Retrieve specific columns:

SELECT name, email FROM users;


  1. UPDATE

The UPDATE statement modifies existing records in a table.

Example:

Update a user’s email:

UPDATE users
SET email = 'alice_new@example.com'
WHERE id = 1;


  1. DELETE

The DELETE statement removes records from a table.

Example:

Delete a user:

DELETE FROM users
WHERE id = 1;


Advanced CRUD Techniques

Filtering with WHERE

The WHERE clause is used to filter records based on specific conditions.

Example:

Retrieve users with a specific name:

SELECT * FROM users
WHERE name = 'Alice';

Sorting with ORDER BY

The ORDER BY clause is used to sort the data in ascending (ASC) or descending (DESC) order.

Example:

Retrieve users in alphabetical order:

SELECT * FROM users
ORDER BY name ASC;


Practice

  1. Insert Data

Add multiple user records to the users table:

INSERT INTO users (name, email) VALUES
('Bob', 'bob@example.com'),
('Charlie', 'charlie@example.com'),
('David', 'david@example.com');

  1. Retrieve Data

Retrieve all users:

SELECT * FROM users;

Retrieve users with an email containing "example":

SELECT * FROM users
WHERE email LIKE '%example%';

  1. Update Data

Change Bob’s email:

UPDATE users
SET email = 'bob_new@example.com'
WHERE name = 'Bob';

  1. Delete Data

Remove Charlie’s record:

DELETE FROM users
WHERE name = 'Charlie';


Real-World Example: CRUD Operations for Users Table

Imagine you are building a user management system for your project. You can use CRUD operations to handle user data efficiently.

  1. Add new users when they sign up:

INSERT INTO users (name, email)
VALUES ('Eve', 'eve@example.com');

  1. Retrieve user information for display:

SELECT * FROM users;

  1. Update user details when they change their email:

UPDATE users
SET email = 'eve_updated@example.com'
WHERE name = 'Eve';

  1. Remove inactive users to keep the database clean:

DELETE FROM users
WHERE name = 'Eve';

By practicing these operations, you’ll gain confidence in managing data for any real-world application.


Interview Preparation

  1. What does CRUD stand for, and why is it important?

  2. How would you retrieve only specific columns from a table?

  3. What’s the difference between DELETE and TRUNCATE?

  4. Explain how to update multiple records with a single query.


Outcome for the Day

By the end of Day 3, you should:

Understand and implement CRUD operations.

Be comfortable filtering and sorting data using WHERE and ORDER BY.

Apply these concepts to manage data for your project.

Tomorrow, we’ll focus on Relationships and Joins, which are essential for connecting multiple tables in your database.


Would you like additional examples or guidance for practicing CRUD operations?

Image of Timescale

Timescale – the developer's data platform for modern apps, built on PostgreSQL

Timescale Cloud is PostgreSQL optimized for speed, scale, and performance. Over 3 million IoT, AI, crypto, and dev tool apps are powered by Timescale. Try it free today! No credit card required.

Try free

Top comments (0)

Postgres on Neon - Get the Free Plan

No credit card required. The database you love, on a serverless platform designed to help you build faster.

Get Postgres on Neon

👋 Kindness is contagious

Engage with a sea of insights in this enlightening article, highly esteemed within the encouraging DEV Community. Programmers of every skill level are invited to participate and enrich our shared knowledge.

A simple "thank you" can uplift someone's spirits. Express your appreciation in the comments section!

On DEV, sharing knowledge smooths our journey and strengthens our community bonds. Found this useful? A brief thank you to the author can mean a lot.

Okay