CRUD Operations in SQL: Definition with Examples

CREATE operations

The letter 'C' in the CRUD acronym refers to the CREATE operation that implies adding rows and columns to a table. In SQL Server, the INSERT statement can be used to create new records in the table. The syntax of the INSERT INTO statement would be:

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

Keep in mind that INTO is optional and can be omitted. As you can see, you need to specify the name of the table to be populated with records and a list of columns along with their values. Now, let’s try to insert data into the Persons table.

CREATE query example

In the example, we add multiple rows to the Persons table by executing the INSERT INTO query:

INSERT INTO dbo.Persons
(
PersonID
,LastName
,FirstName
,Address
,City
)
VALUES
(1,'Conrad','Baumann','1388 Parkwood Loop, STE 31','Lansing'),
(2, 'Adam', 'Reyes', '56 Front Way', 'Salem'),
(3, 'Keira', 'Delacruz', '2676 E Prospect Hill Ave', 'Lincoln');

READ operations

The letter 'R' in the CRUD acronym refers to the READ operation that retrieves data from the table using the SELECT statement. We can either fetch all data from the table or retrieve data filtered by the condition criteria in the WHERE clause.

The syntax of the SELECT statement would be:

-- syntax to retrieve all data from the table
SELECT * FROM table_name;

-- syntax to retrieve filtered data from the table
SELECT * FROM table_name WHERE condition;

READ operation example

After we inserted new records into the Persons table, we can retrieve data to make sure that the rows have been added to the table. To fetch data from the table, we execute the following query:

SELECT * FROM AdventureWorks2019.dbo.Persons;

The result shows that all values for each record in the table have been inserted.

Now, we change the query to view in the output only data that matches the condition in the WHERE clause. For example, we want to see information about Conrad Baumann. To do that, execute the following query:

SELECT
*
FROM AdventureWorks2019.dbo.Persons
WHERE PersonID = 1;

As you can see, the query returned the record according to the specified condition.

Let's go on to have a look at the CRUD UPDATE operation.

UPDATE operations

The letter 'U' in the CRUD acronym refers to the UPDATE operation that modifies the existing records in a SQL table. Thus, to make changes to data, you can use the UPDATE statement with the following syntax:

UPDATE table_name
SET column_1=value_1,column_2=value_2
WHERE condition;

UPDATE query example

Suppose that Conrad Baumann moved to another place and we need to update his address. To modify values, we execute the UPDATE query:

UPDATE AdventureWorks2019.dbo.Persons
SET
Address = '1399 Firestone Drive'
WHERE
PersonID = 1;

To check that the changes have been applied to the table, we execute the SELECT query:

The result in the screenshot verifies that the specified row has been updated.

DELETE operations

The last letter 'D' in the CRUD acronym refers to data deletion. In SQL, it means deleting records from a SQL table by executing the DELETE query. We can remove either all records or specific ones from the table. In the latter case, we can specify which rows to remove in the WHERE clause.

The syntax of the DELETE statement would be:

-- syntax to delete all records from the table
DELETE FROM table_name;

-- syntax to delete specified records from the table
DELETE FROM table_name WHERE condition;

table_name is the table from which data should be deleted.

DELETE statement example

In our example, we need to remove information about Conrad Baumann because he has retired. To do that, we execute the query with the WHERE clause in which we set a condition:

DELETE FROM AdventureWorks2019.dbo.Persons
WHERE
PersonID = 1;

If we retrieve the updated data, we'll see that the specified record has been deleted from the table.

To remove all records from the Persons table, we execute the following DELETE query:

DELETE FROM AdventureWorks2019.dbo.Persons;

This way we considered how CRUD operations work in SQL Server. In order to add, update, or remove data we needed to write statements each time. As you can notice, this may take a lot of time and effort. But that can be worked around and we’ll show how to do it.