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
- 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
);
- INSERT
The INSERT statement is used to add new records into a table.
Example:
INSERT INTO users (name, email)
VALUES ('Alice', 'alice@example.com');
- 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;
- 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;
- 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
- 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');
- Retrieve Data
Retrieve all users:
SELECT * FROM users;
Retrieve users with an email containing "example":
SELECT * FROM users
WHERE email LIKE '%example%';
- Update Data
Change Bob’s email:
UPDATE users
SET email = 'bob_new@example.com'
WHERE name = 'Bob';
- 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.
- Add new users when they sign up:
INSERT INTO users (name, email)
VALUES ('Eve', 'eve@example.com');
- Retrieve user information for display:
SELECT * FROM users;
- Update user details when they change their email:
UPDATE users
SET email = 'eve_updated@example.com'
WHERE name = 'Eve';
- 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
What does CRUD stand for, and why is it important?
How would you retrieve only specific columns from a table?
What’s the difference between DELETE and TRUNCATE?
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?
Top comments (0)