Gid Akinsanmi's blog
How to perform CRUD operations with SQL

How to perform CRUD operations with SQL

Gideon Akinsanmi's photo
Gideon Akinsanmi
·

3 min read

Table of contents

SQL is a programming language used for communicating with relational databases like MySQL, PostgreSQL and Oracle. It's used for manipulating and managing data held in a relational database. SQL can be used to create, read, update and delete data from a database.

In this article, I'll be showing you how to perform CRUD operations with SQL. CRUD is an acronym that means create, read, update and delete.

You can use the CREATE TABLE and INSERT INTO statement to create records, SELECT statement to read records, UPDATE statement to update data and the DELETE SQL statement to delete records.

Setting up

Before starting, you need to ensure you’ve installed a relational database on your system. Some of the popular relational databases are PostgreSQL, MySQL and Oracle. Although I’ll be using PostgreSQL, any relational database you use works.

Create operation (Crud)

Creating a table

To create a table, you'll use the CREATE TABLE command. The statement must be in this format:

CREATE TABLE table_name {
    column_name data_type constraints,
    ...
    column_nameN data_typeN constraintsN
};

column_name refers to the name of your table columns.

data_type describes the type of data to be stored in a column. Generally, most relational databases store strings, numbers and dates. In PostgreSQL, INTEGER represents numbers, VARCHAR and TEXT represent characters and DATE represents dates. Check your database specification to learn more about the different data types that can be represented.

constraints are rules that control the data stored in a column. A UNIQUE constraint means all the values in the column must be unique. A NOT NULL constraint means the column must not store any null value. Other constraints are FOREIGN KEY, PRIMARY KEY, CHECK, DEFAULT and CREATE INDEX

Example:

CREATE TABLE users (
    name VARCHAR(20) NOT NULL,
    id INTEGER UNIQUE,
    bio TEXT,
    last_seen DATE
);

Inserting a row into a table

To insert a row into a table, you'll use the INSERT INTO command. The statement must be in this format:

INSERT INTO table_name(column1, ..., columnN)
VALUES(value1, ..., valueN);

Example:

INSERT INTO users(name, id, bio, last_seen)
VALUES("James Bird", 23091, "Hello, my name is James Bird", "2023-03-24");

Read operation (cRud)

Retrieving data from a table

To retrieve data from a table, you’ll use the SELECT command. There are multiple ways to use the SELECT command but I'll be using the general format.

SELECT *
FROM table_name;

The statement above returns all columns in the table.

Example:

SELECT *
FROM users;

Result in PostgreSQL:

Update operation (crUd)

Updating rows of a table

To update the rows of a table, you'll use the UPDATE command. There are multiple ways to use the UPDATEcommand but I'll be using the general format.

UPDATE table_name
SET column2 = new_value
WHERE column1 = value1;

The above statement first finds the row(s) whose column_name is value1 and then changes the column2 value to new_value.

Example:

UPDATE users
SET name = "Dominic Bryan"
WHERE id = 23091;

Delete operation (cruD)

Deleting rows from a table

To delete one or more rows from a table, you'll use the DELETE command. The statement can be in this format:

DELETE FROM table_name
WHERE column_name = value1;

The above statement deletes rows from table_name whose column_name is value1.

Example:

DELETE FROM users
WHERE id = 23091;

Wrapping up

By now you should be able to use your knowledge of SQL to create, read, update and delete data from a relational database. If you want to become better in SQL, I'll advise you to read more content, write more statements and build more projects. I hope you enjoyed reading this article.

SQL