Leave a comment

SQL: Understanding basics with CRUD Operation

Introduction to SQL

SQL or Structured Query Language is the language we use to work with the Relational database. Although the syntax differs from vendor to vendor, but moreover the structure of the language is same. What’s better way to learn a language than actually using it to perform some operation.

CRUD Create, Retrieve, Update and Delete are the basic operations that we will perform using SQL

Create Operation

In this example we are going to create a table and insert some data into that table using SQL

So we are going to create a table that looks something like this
Table

Create table command

create table employee(
EmpId INT NOT NULL PRIMARY KEY IDENTITY(1,1),
EmpName VARCHAR(250),
EmpAge INT,
EmpDeptCode INT)

Running the above command will create the table

create_table

If you see carefully, I have used IDENTITY(1,1) on EmpId column. This will set the Identity column and will auto increment the index by 1

Now, we have our employee table created. Let’s put some values in it

Insert Into command


INSERT INTO employee (EmpName, EmpAge, EmpDeptCode)
VALUES ('John', 27, 1)

INSERT INTO employee (EmpName, EmpAge, EmpDeptCode)
VALUES ('Patrick', 30, 2)

INSERT INTO employee (EmpName, EmpAge, EmpDeptCode)
VALUES ('Peter', 22, 3)

INSERT INTO employee (EmpName, EmpAge, EmpDeptCode)
VALUES ('Sam', 21, 4)

INSERT INTO employee (EmpName, EmpAge, EmpDeptCode)
VALUES ('Zac', 26, 5)

Once you execute these commands by pressing F5 you will see the rows updated information
rows_updated

To see what values got updated in employee table, run the SELECT command

select * from employee

select_result

Retrieve And Update

Now, in case you want to update the information of any specific employee. You have to retrieve that employee and run update on it. Let’ update the age of Patrick to 32


UPDATE employee
SET EmpAge = 32
WHERE 1=1
AND EmpId = 2
AND EmpName = 'Patrick'

Now, running the SELECT statement will show you thew updated value

update_result

Now, coming to the last part of the CRUD operation, Let us see how to DELETE the value

deleting the record for Patrick

DELETE FROM command


DELETE FROM employee
where 1=1
AND EmpId = 2
AND EmpName = 'Patrick'

And running again the SELECT command will give you the result but without the data for Patrick

delete_result

Leave a Reply

Your email address will not be published.


+ 4 = seven

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>