Post
Ask Question

Learn CRUD Operations in SQL Server with Real-World Examples

Introduction

CRUD is an acronym for Create, Read, Update, and Delete, which represents the four basic operations for managing persistent data in a relational database system like SQL Server. These operations are essential for any application that relies on data storage, allowing users to interact with the data in meaningful ways. This article will provide an in-depth look at CRUD operations in SQL Server, complete with examples to illustrate each concept.

Four types of CRUD Operations

  • Create: Adding new records to a table.
  • Read: Retrieving existing records from a table.
  • Update: Modifying existing records in a table.
  • Delete: Removing existing records from a table.

Let's delve into each of these operations with detailed examples.

CREATE Operation

The CREATE operation involves adding new records to a table. In SQL Server, this is done using the INSERT INTO statement.

--Create a new table
CREATE TABLE EmployeeDetails (
    EmployeeID INT PRIMARY KEY IDENTITY(1,1),
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Email NVARCHAR(100),
    HireDate DATE,
    Salary DECIMAL(10, 2)
);
GO
--Insert the records
INSERT INTO EmployeeDetails (FirstName, LastName, Email, HireDate, Salary)
VALUES
    ('Naveen', 'Kumar', '[email protected]', '2021-05-22', 62000.00),
    ('William', 'Davis', '[email protected]', '2020-11-03', 75000.00),
    ('Jack', 'Sparrow', '[email protected]', '2019-08-12', 48000.00);
GO

READ Operation

The READ operation involves retrieving data from a table. In SQL Server, this is done using the SELECT statement.

--Retrieving All Records
SELECT * FROM EmployeeDetails;
GO
--Retrieving Specific Columns
SELECT FirstName, LastName, Email FROM EmployeeDetails;
GO
--Filtering Records
SELECT * FROM EmployeeDetails
WHERE HireDate > '2021-01-01';
GO

UPDATE Operation

The UPDATE operation involves modifying existing records in a table. In SQL Server, this is done using the UPDATE statement.

--Updating a Single Record
UPDATE EmployeeDetails
SET Salary = 60000.00
WHERE EmployeeID = 1;
GO
--Updating Multiple Records
UPDATE EmployeeDetails
SET Salary = Salary * 1.05
WHERE HireDate < '2020-01-01';
GO

DELETE Operation

The DELETE operation involves removing existing records from a table. In SQL Server, this is done using the DELETE statement.

--Deleting a Single Record
DELETE FROM EmployeeDetails
WHERE EmployeeID = 2;
GO
--Deleting Multiple Records
DELETE FROM EmployeeDetails
WHERE Salary < 50000.00;
GO

Conclusion

CRUD operations form the backbone of any database-driven application. Understanding how to effectively perform these operations in SQL Server is crucial for developers and database administrators. By mastering CRUD operations in SQL Server, you'll be well-equipped to handle a wide range of data management tasks, contributing to the overall success and stability of your applications.


Similar Articles