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
In this example we are going to create a table and insert some data into that table using SQL
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
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)
To see what values got updated in employee table, run the SELECT command
select * from employee
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
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