CRUD operations in SQL: Examples and explanations | Retool Blog

Many companies use SQL-compatible databases for their reliability and transactional support. However, SQL can prove difficult for the average non-technical user, especially when complex filters, sorting, or data merging are required. To combat this, organizations build internal tools that facilitate CRUD operations to interact with the underlying database:

Creating the data
Retrieving the data
Updating the data
Deleting the data

In this tutorial, we’ll explore how to use SQL statements to perform all four CRUD operations.

Quick Caveat. Keep in mind that SQL is a flavored language: popular databases such as MySQL, PostgreSQL, and Oracle each use a different dialect of SQL. Thankfully, these dialects don’t have enormous differences between them, and the syntax for basic CRUD operations is fairly consistent.

Want to follow along? If you’d like to try out these queries, you can use Retool to connect to an existing Postgres database or download Postgres locally and access it through the command line.

Example table structure

For our CRUD operations, we’ll create a table, CUSTOMER, with four columns of varying data types. To be clear, this is not a CRUD operation (even though it’s called a CREATE operation)—this is just provisioning the table to query from.

We’ll use the CREATE TABLE statement to create the table.

1CREATE TABLE Customers (
2    customer_id int NOT NULL PRIMARY KEY,
3    customer_code varchar(255) UNIQUE,
4    contact_name varchar(255),
5    active TINYINT DEFAULT 1
6);

SQL

This statement creates the following:

CUSTOMER_ID is the primary key. This column is used to uniquely identify a row in the table. Accordingly, the primary key column only accepts unique values and doesn't accept null values. We can also define triggers to auto-increment the values of the primary key column.
CUSTOMER_CODE is a column with a UNIQUE constraint, which means the column can store only unique values. Unlike primary key columns, unique constraint columns will accept null values.
CONTACT_NAME is a column without any constraints. It will accept any type of value, including duplicate values and null values.
ACTIVE is a column without any constraints, but a DEFAULT value is defined as 0. It is of type TINYINT, which spans values 0 to 255. Here, TINYINT is used as a hack-y boolean because mySQL and SQL Server lack a native boolean type (which is otherwise available in Postgres). If the user doesn't specify any explicit value for this column when inserting the record, the value 1 will be assigned by default.

Now, we’ll explore how different CRUD operations are performed on this table structure.

INSERT (Create)

The INSERT operation is used to add records to the table. INSERT statements can pick and choose which columns to fill when adding a row; alternatively, developers can provide values for all the fields.

Let’s explore various INSERT operations.

Inserting values to all columns

If we want to insert a new row with values for all columns, we can use the following syntax:

1INSERT INTO TABLE_NAME VALUES (VALUE1, VALUE2, …);

SQL

For the earlier syntax to work:

The values must be in the same order as they are defined in the database table.
Accordingly, the number of values must be equal to the number of columns in the table.

SQL is a very picky language when it comes to syntax. Ask any SQL developer: given the error messages are often vague, SQL errors can make a grown dev cry.

We’ll now add two entries to the database by using two INSERT queries, separated by SQL’s favorite character: ;.

1INSERT INTO CUSTOMERS VALUES(1, 'A1','MAEGAN', 1);
2INSERT INTO CUSTOMERS VALUES(2, 'A2','DENNIS', 0);

SQL

Upon success, any SQL database should inform us that two rows have been successfully added. Here, we’re using 1 to signify true and 0 to signify false.

While this format makes for a good example, this add-all-fields syntax isn’t typically used because the script would need to be updated whenever a column is added, removed, or modified. This creates a real hazard because databases are often changed; they are also typically maintained and accessed by various people spanning departments.

Inserting values to specific columns

In order to insert values into specific columns, SQL expects a tuple—of the same length as the values—to identify the columns. Anything omitted will be filled by a static default value (like “Untitled”), an auto-calculated value (like automatically incrementing IDs), or NULL.

The syntax’s general structure is defined as the following:

1INSERT INTO TABLE_NAME
2	(COLUMN_1, COLUMN_2, …) 
3VALUES 
4	(VALUE1, VALUE2, …);

SQL

SQL expects users to name the columns and state the values in the same respective order. If there is a mismatch of type or column/value length, SQL will return an error.

Let’s use this INSERT pattern to add another row:

1INSERT INTO CUSTOMERS 
2	(CUSTOMER_ID, CUSTOMER_CODE, CONTACT_NAME) 
3VALUES 
4	(3, 'A3','LANA');

SQL

When we execute the query, a single new record will be inserted into the CUSTOMERS table. Though the value for the column ACTIVE is not specified in the insert query, the value 1 will automatically be assigned as it’s the default value.

SELECT (Retrieve)

The SELECT statement is used to retrieve records from the database table. While the SELECT statement will return all data from all columns and rows by default, we can specify which rows and/or columns we need.

Select all columns and rows

We can use the * operator to select all columns and rows from a database table.

1SELECT * FROM TABLE_NAME;

SQL

Typically, this query is significantly slower than querying specific rows or columns; constraints limit the data that needs to be looked up and transmitted, thereby expediting results.

Right now, if we ran the following query:

1SELECT * FROM CUSTOMERS;

SQL

we would get an output that looks like this:

11|A1|MAEGAN|1
22|A2|DENNIS|0
33|A3|LANA|1
4

Selecting specific columns

We can specify columns from a database table within the SELECT statement:

1SELECT COLUMN_1, COLUMN_2, COLUMN_N FROM TABLE_NAME;

SQL

We will use the following query statement to select the CONTACT_NAME and ACTIVE columns of the CUSTOMERS table. We can use this to check if the customer is an active customer or not.

1SELECT CONTACT_NAME, ACTIVE FROM CUSTOMERS;

SQL

The output will look like this:

1MAEGAN|1
2DENNIS|0
3LANA|1
4

Selecting specific rows

To select specific rows from the database table, we will need to use the where clause, which will filter the records based on the condition specified.

1SELECT COLUMN1, COLUMN2 FROM TABLE_NAME WHERE CONDITION;

SQL

Similarly, we can use the statement below to retrieve all columns from all active customers.

1SELECT * FROM CUSTOMERS WHERE ACTIVE = 1;

SQL

Accordingly, all the customers with the ACTIVE value 1 will be retrieved:

11|A1|MAEGAN|1
23|A3|LANA|1
3

UPDATE

The UPDATE statement is used to update existing records in the database table.

Using the UPDATE statement, we can update one or more columns in specific row(s) by coupling it with a where clause. Predictably, the where specifies which rows need to be updated.

We will use the following syntax to update specific column(s) based on the specified condition.

1UPDATE TABLE_NAME SET COLUMN1 = NEW_VALUE WHERE CONDITION;

SQL

Previously, we inserted one customer with the Active value 0. Suppose this originally inactive customer is now an active customer, and we want to update them in the database accordingly.

We can use the statement below to update the Active column to 1 specifically for the customer with the CUSTOMER_ID that is 2.

1UPDATE CUSTOMERS SET ACTIVE = 1 WHERE CUSTOMER_ID = 2;

SQL

To validate that the records have been updated correctly, we can retrieve the active customers using the following query:

1SELECT * FROM CUSTOMERS WHERE ACTIVE = 1;

SQL

Which returns the expected output:

11|A1|MAEGAN|1
22|A2|DENNIS|1
33|A3|LANA|1
4

DELETE

The DELETE statement is used to delete the records from the database table.

Just like UPDATE, when using the DELETE statement, we can specify one or more row(s) to be deleted by using the where clause. The where clause is critical—otherwise, on some SQL databases, we will delete all of our data. (Yes, all three rows of it, the horror!)

We’ll use the following syntax to delete specific row(s) from the database table.

1DELETE FROM TABLE_NAME WHERE <CONDITION>;

SQL

For example, if we want to delete a customer whose CUSTOMER_ID is 2, we can use this statement:

1DELETE FROM CUSTOMERS WHERE CUSTOMER_ID = 2;

SQL

This will delete one row. Now when we select all rows from the table using the SELECT statement, we only see two rows:

1SELECT * FROM CUSTOMERS;

SQL

with the output:

11|A1|MAEGAN|1
23|A3|LANA|1
3

Conclusion

In this tutorial, we explored how to use CRUD operations in SQL to insert, retrieve, update, and delete information from a database table. These CRUD operations help connect databases with user-facing applications, from external products to internal apps.

If you’re interested in building an internal application to work with relational data in any SQL database, Retool makes it easy to build and maintain internal tools. Retool provides a UI toolkit via a drag-and-drop interface for displaying and visualizing data retrieved via CRUD operations. Give it a try for free here.

Thanks to Vikram Aruchamy for writing and Mathew Pregasen for contributing to this post.

Reader