CRUD word is an acronym of the following essential database functions.
In the first part of CRUD article series, we mentioned the concept of the CRUD operations and then discussed the INSERT operation with examples. In this part, we will continue exploring the CRUD operations and will focus on the read, update and delete operation details of CRUD.
The Read operation is used to retrieve data from a table and then it returns a result set which includes the records of the table. If we want to retrieve data of more than one table, we can use the JOIN operator. So we can create a logical relation between tables with the help of the JOIN operator. The SELECT statement plays a single primary role in the read operation. The SELECT statements are based on three parts:
Column part: In this part, we can define the columns from which we want to retrieve data.
Table part: In this part, we specify the table from which we want to get data.
Filter part: In this part, we can filter the data that we want to read. This part is not mandatory, so the SELECT statement can be executed without any filter part.
The simplest form of the select statement will be like the following form:
SELECT column1, column2,...,columnN FROM table_name
Now, we will go through the examples and reinforce our theoretical knowledge. At first, we need a sample table to read and for this reason, we will create a sample table with the help of the following query.
DROP TABLE IF EXISTS TblCountry GO CREATE TABLE [dbo].[TblCountry] ( [CountryName] VARCHAR(50), [ContinentNames] VARCHAR(50) NULL, [CountryPopulation] BIGINT NULL ) GO INSERT INTO TblCountry (CountryName,ContinentNames,CountryPopulation) VALUES ('Germany','Europe',8279000 ), ('Japan','Asia',126800000 ), ('Moroco','Africa',35740000)
Reading all columns in the table
The asterisk (*) operator has wide usage in the SELECT statements because it returns all columns in the table. In the following example, we will return all columns and rows present in the table.
SELECT * FROM TblCountry
As you can see, in this select statement, we used the asterisk operator (*) and it allows us to return all columns of the table.
Tip: If you don’t need all columns of the table, you don’t need to use the asterisk (*) operator in the SELECT statement. The main idea behind this suggestion is that the asterisk (*) operator can influence the performance negatively because it causes more network traffic and uses more resources.
Reading particular columns of the table
In the SELECT statements, we can read the particular columns of the table. In the following example, we will return only the CountryName and CountryPopulation columns.
SELECT CountryName,CountryPopulation FROM TblCountry
Using alias in the SELECT statements
In the SELECT statements, we can give temporary names to the table or to the columns and these temporary names are called aliases. Now, we will rewrite the previous two queries with table and column aliases. In the following query, the TblC alias will specify the table name.
SELECT TblC.* FROM TblCountry TblC
In the following example, we will provide aliases for the column names so we will change the CountryName column name to CName and the CountryPopulation column name to CPop.
SELECT TblC.CountryName AS [CName], CountryPopulation AS [CPop] FROM TblCountry TblC
The usage purposes of the alias are:
- Make the query more readable if the table or column names are complex.
- If a table is using a query more than one time.
- It can reduce the query writing effort if the table or column name is long.
Filtering SELECT statements
In SELECT statements, we can filter the result set through the WHERE clause. For example, if we want to filter the SELECT statement according to CountryName column and only return the data of Germany into resultset. The following query will perform read operation with a filter.
SELECT TblC.* FROM TblCountry TblC WHERE TblC.CountryName='Germany'
Sorting SELECT statements results
The ORDER BY clause helps to sort the result set of the SELECT statement according to specified column or columns. We can perform the ascending or descending sorting with the help of the ORDER BY clause. For example, we would like to sort the TblCountry table according to the population of the countries in the ascending order.
SELECT TblC.* FROM TblCountry TblC ORDER BY TblC.CountryPopulation ASC
As you can see from the following image, the resultset of the query is sorted to ascending order.
Tip: You can use the column index in the ORDER BY clause and columns index numbers start by 1. We can also write the previous query as follows. Three (3) number indicates the CounrtyPopulation column.
SELECT TblC.* FROM TblCountry TblC ORDER BY 3 ASC
The UPDATE statement is used to modify the existing data in the table. The Update statement must include a SET clause so that we can define the target column in which we want to modify the data. The following query will change all rows of the CounrtyPopulation column value to 1.
UPDATE TblCountry SET CountryPopulation=1 GO SELECT TblC.* FROM TblCountry TblC
In the UPDATE statements, we can use the WHERE clause so that we can modify particular row or rows in the table. Now, we will change the Japan row of CounrtyPopulation to 245000.
UPDATE TblCountry SET CountryPopulation=245000 WHERE CountryName = 'Japan' GO SELECT TblC.* FROM TblCountry TblC
Actually, the UPDATE statement is a union of the delete and insert statements, so we can return the inserted and deleted values through the OUTPUT clause. Let’s make an example of it.
UPDATE TblCountry SET CountryPopulation=22 OUTPUT inserted.CountryPopulation AS [Insertedvalue], deleted.CountryPopulation AS [Deletedvalue] WHERE CountryName = 'Germany'
As you can see, we modified the CountryPopulation value from 1 to 22 and then we can find out the inserted and deleted values. Additionally, we can insert these values to a table variable. The table variable is a special variable type that can be used as a table. In the following example, we will insert the inserted and deleted values to the table variable.
DECLARE @LogTable TABLE(InsertValLog INT , DelValLog INT) UPDATE TblCountry SET CountryPopulation=45 OUTPUT inserted.CountryPopulation , deleted.CountryPopulation INTO @LogTable WHERE CountryName = 'Germany' SELECT * FROM @LogTable
@@ROWCOUNT is a system variable which returns the number of the row affected in the last statement. So we can use this variable to expose a number of the modified rows in the update statement. In the following example, the update query will change 3 rows and the @@ROWCOUNT system variable will return 3.
UPDATE TblCountry SET CountryPopulation=1 SELECT @@ROWCOUNT AS [AffectedRowNumber]
The Delete statement is used to remove existing row/rows in the table. At first, we will mention how to use the WHERE clause in the DELETE statements, just because most of the time we want to filter the deleted rows. In the following example, we will remove a particular row.
SELECT TblC.* FROM TblCountry TblC DELETE FROM TblCountry WHERE CountryName='Japan' SELECT TblC.* FROM TblCountry TblC
Though with the DELETE statement, we can remove all recordі in the table but the DELETE statement is very basic and we don’t use WHERE condition.
SELECT TblC.* FROM TblCountry TblC DELETE FROM TblCountry SELECT TblC.* FROM TblCountry TblC
However, in some circumstances, according to database designs, the DELETE statement does not delete the row/rows if the DELETE statement violates the foreign key or other constraints. For example, in the AdventureWorks database, we cannot delete the ProductCategory table rows because ProductCategoryID is specified as a foreign key in the ProductSubcategory table.
Now, we will try to delete a row in the ProductCategory table and no doubt we will face following error.
DELETE FROM [Production].[ProductCategory] WHERE ProductCategoryID=1
In this article series, we explored the CRUD operations in SQL. CRUD operation includes INSERT, SELECT, UPDATE and DELETE statements and these are the basic functions of the SQL database. If anyone wants to learn SQL database programming, CRUD theory could be a good starting point.