CRUD Operations using SQL Server and Query Window

Performing CRUD Operations using SQL Server

In our previous post, we have created a database and some tables, in case you didn’t see it, you can find it here. We are going to be looking at CRUD using SQL Server

What is CRUD?

CRUD is an acronym for the four basic types of SQL commands: Create, Read, Update, Delete. Most applications have some kind of CRUD functionality, and we can assume that every programmer had to deal with CRUD at some point. A CRUD application is one that uses forms to get data into and out of a database.

Let’s get started

Connect to the database server as shown below

Click on connect button to go to MSSMS object explorer

Expand the Databases folder to list all the databases on the server

Expand the csharpnaija_employees database and expand the Tables folder to list all its tables

Select on the Departments table from csharpnaija_employees database and click on New Query on the toolbar of the Management Studio as shown below

Now we can start writing sql server statements to carry out the CRUD operations needed on the Query Window shown below

CREATE

Let’s start with the first Letter C which stands for CREATE which is used to add records to the database table using an INSERT statement.

Syntax

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

If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. This is shown below

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

Examples (Insert into Departments)

INSERT INTO departments (DeptName,IsActive) VALUES('Admin',1);

INSERT INTO departments (DeptName,IsActive) VALUES('Customer Services',1);

INSERT INTO departments (DeptName,IsActive) VALUES('Operations',1);

INSERT INTO departments (DeptName,IsActive) VALUES('Auditor',1);

The code from the Query Window is as shown below

Now click on the Execute button  to run the queries

Examples (Insert into Employees)

INSERT INTO employees (FirrstName,MiddleName,LastName,Address,PhoneNumber,DateOfBirth,DepartmentId) VALUES('Hauwa','Musa','Gadabs','Gadabuke','07023394848','02/05/1980',7);

INSERT INTO employees (FirrstName,MiddleName,LastName,Address,PhoneNumber,DateOfBirth,DepartmentId) VALUES('Jamila','Suleiman','Gadabs','Abaji','07033394848','02/05/1981',5);

INSERT INTO employees (FirrstName,MiddleName,LastName,Address,PhoneNumber,DateOfBirth,DepartmentId) VALUES('Fatima','Suleiman','Gadabs','Niger','07093394848','02/05/1983',6);

INSERT INTO employees (FirrstName,MiddleName,LastName,Address,PhoneNumber,DateOfBirth,DepartmentId) VALUES('Ibrahim','Audu','Gadabs','Bwari','07053394848','02/05/1987',4);

The code from the Query Window is as shown below

Now click on the Execute button  to run the queries

READ

The second Letter R which stands for READ, is used to get records from the database table using an SELECT statement.

Syntax

SELECT column1, column2, ...
FROM table_name;

Here, column1, column2, ... are the field names of the table you want to select data from. If you want to select all the fields available in the table, use the following syntax:

SELECT * FROM table_name;

Example (Departments)

Select Id,DeptName from departments;

Now click on the Execute button  to run the queries

Example (Employees)

Select Id,FirrstName,MiddleName,LastName,Address,PhoneNumber,DateOfBirth,

DepartmentId from employees;

Now click on the Execute button  to run the queries

UPDATE

The third Letter U which stands for UPDATE, is used to modify existing records in the database table using an UPDATE statement.

Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example (Employees)

UPDATE employees SET Address='Gadabuke' where Id=7;

Now click on the Execute button  to run the queries

DELETE

The last Letter D which stands for DELETE, is used to remove existing records in the database table using a DELETE statement.

Syntax

DELETE FROM table_name WHERE condition;

Example

DELETE FROM employees where id=1;

Now click on the Execute button  to run the queries

WHERE Clause

The WHERE clause is used to filter records.

The WHERE clause is used to extract only those records that fulfill a specified condition.

Syntax

DELETE FROM table_name
WHERE condition;

Example

DELETE FROM employees where id=1;