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.
CREATE TABLE Customers ( customer_id int NOT NULL PRIMARY KEY, customer_code varchar(255) UNIQUE, contact_name varchar(255), active TINYINT DEFAULT 1 );
This statement creates the following:
CUSTOMER_IDis 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_CODEis a column with a
UNIQUEconstraint, which means the column can store only unique values. Unlike primary key columns, unique constraint columns will accept null values.
CONTACT_NAMEis a column without any constraints. It will accept any type of value, including duplicate values and null values.
ACTIVEis a column without any constraints, but a
DEFAULTvalue is defined as
0. It is of type
TINYINT, which spans values
TINYINTis 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 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
Inserting values to all columns
If we want to insert a new row with values for all columns, we can use the following syntax:
INSERT INTO TABLE_NAME VALUES (VALUE1, VALUE2, …);
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:
INSERT INTO CUSTOMERS VALUES(1, 'A1','MAEGAN', 1); INSERT INTO CUSTOMERS VALUES(2, 'A2','DENNIS', 0);
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
The syntax’s general structure is defined as the following:
INSERT INTO TABLE_NAME (COLUMN_1, COLUMN_2, …) VALUES (VALUE1, VALUE2, …);
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:
INSERT INTO CUSTOMERS (CUSTOMER_ID, CUSTOMER_CODE, CONTACT_NAME) VALUES (3, 'A3','LANA');
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 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.
SELECT * FROM TABLE_NAME;
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:
SELECT * FROM CUSTOMERS;
we would get an output that looks like this:
1|A1|MAEGAN|1 2|A2|DENNIS|0 3|A3|LANA|1
Selecting specific columns
We can specify columns from a database table within the
SELECT COLUMN_1, COLUMN_2, COLUMN_N FROM TABLE_NAME;
We will use the following query statement to select the
ACTIVE columns of the
CUSTOMERS table. We can use this to check if the customer is an active customer or not.
SELECT CONTACT_NAME, ACTIVE FROM CUSTOMERS;
The output will look like this:
MAEGAN|1 DENNIS|0 LANA|1
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.
SELECT COLUMN1, COLUMN2 FROM TABLE_NAME WHERE CONDITION;
Similarly, we can use the statement below to retrieve all columns from all active customers.
SELECT * FROM CUSTOMERS WHERE ACTIVE = 1;
Accordingly, all the customers with the
1 will be retrieved:
UPDATE statement is used to update existing records in the database table.
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.
UPDATE TABLE_NAME SET COLUMN1 = NEW_VALUE WHERE CONDITION;
Previously, we inserted one customer with the
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
UPDATE CUSTOMERS SET ACTIVE = 1 WHERE CUSTOMER_ID = 2;
To validate that the records have been updated correctly, we can retrieve the active customers using the following query:
SELECT * FROM CUSTOMERS WHERE ACTIVE = 1;
Which returns the expected output:
1|A1|MAEGAN|1 2|A2|DENNIS|1 3|A3|LANA|1
DELETE statement is used to delete the records from the database table.
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.
DELETE FROM TABLE_NAME WHERE <CONDITION>;
For example, if we want to delete a customer whose
2, we can use this statement:
DELETE FROM CUSTOMERS WHERE CUSTOMER_ID = 2;
This will delete one row. Now when we select all rows from the table using the
SELECT statement, we only see two rows:
SELECT * FROM CUSTOMERS;
with the output:
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.