DB: CRUD in SQL | Amr Tarek

In SQL, the first step is to create a database and then define tables inside it. The database acts like a container, while tables are the structures that hold the actual data. This setup is the foundation for working with relational databases.

Create (C)

Creating a Database

A database groups tables, views, and other objects together under one name. Before adding data, you need to create the database itself.

SQL Command – The CREATE DATABASE statement initializes a new database.
Example

CREATE DATABASE company;

This command creates a new database named company.

Usage – After creating a database, you usually select it with:

USE company;

This tells SQL that all following commands should apply to this database.

Creating a Table

Once a database exists, you can define its tables. A table specifies columns (fields) and their data types, which describe what kind of data can be stored.

SQL Command – The CREATE TABLE statement defines a new table.
Example

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    hire_date DATE
);

This creates an employees table with four columns: id, name, email, and hire_date.

Details:
INT stores whole numbers (used here for employee IDs).
VARCHAR(100) stores variable-length text up to 100 characters.
DATE stores calendar dates.
PRIMARY KEY ensures each row has a unique identifier.

Data Types

Numeric: INT, DECIMAL
Character: CHAR, VARCHAR
Date and Time: DATE, TIME, TIMESTAMP
Boolean: BOOLEAN

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    price DECIMAL(10,2),   -- fixed-point with 2 decimals
    name VARCHAR(50),      -- variable-length text
    country_code CHAR(2),  -- fixed two-character (NL, US, ...)
    order_data DATE,
    delivery_time TIME,
    is_active BOOLEAN,    -- 0/1
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Constraints

PRIMARY KEY
FOREIGN KEY
NOT NULL
UNIQUE

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_orders_user
        FOREIGN KEY (user_id)
        REFERENCES users(user_id)
        ON DELETE CASCADE
);

users
+---------+----------+
| user_id | username |
+---------+----------+
|    1    |  Amr     |
|    2    |  Sara    |
+---------+----------+
      ^
      |
      | FK
      |
orders
+----------+---------+
| order_id | user_id |
+----------+---------+
|   101    |    1    |
|   102    |    1    |
|   103    |    2    |
+----------+---------+

INSERT

INSERT adds new records into a table

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

This creates a new employee record.

INSERT INTO employees (id, name, email, hire_date)
VALUES (1, 'Alice Johnson', 'alice@example.com', '2025-01-10');

Read (R)

The Read operation retrieves data without changing it. SQL uses the SELECT statement for this.

SELECT column1, column2 FROM table_name;

This retrieves names and emails of all employees.

SELECT name, email FROM employees;

Read all data from employees

SELECT * FROM employees;

Update (U)

The Update operation modifies existing records. SQL uses the UPDATE statement.

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

This updates Alice’s email.

UPDATE employees
SET email = 'alice.new@example.com'
WHERE id = 1;

Delete (D)

The Delete operation removes records from a table. SQL uses the DELETE statement.

DELETE FROM table_name WHERE condition;

This deletes Alice’s record.

DELETE FROM employees WHERE id = 1;

CRUD is the foundation of working with data:

Create – add new records (e.g., new user signup).
Read – retrieve records (e.g., display a profile).
Update – change existing records (e.g., update a password).
Delete – remove records (e.g., delete an account).

CRUD stands for Create, Read, Update, Delete. In SQL, these map directly to INSERT, SELECT, UPDATE, and DELETE. Together, they give you complete control over the lifecycle of your data.