In this article i will be explaining CRUD operations in Microsoft SQL. CRUD operations means you will perform some creation, reading, updating and deleting operations in SQL.
Create =Create new table or INSERT Read=Select Update=UPDATE Delete=DELETE
Create refers to creating a new table or inserting a record into a database/tuple. This is very first step while creating the database. Lets see what’s the syntax of creating a new table or how we can insert a record to our table.
Creating new table
The following syntax shows how we can create a new table in SQL.
CREATE TABLE table_Name(column1 datatype,column2 datatype2,column3 datatype3.......)
For example I want to create a new table with name Student having three fields i.e. Student_id,Student_name,Student_age respectively then my SQL statement would be .
CREATE TABLE Student(Student_id int,Student_name varchar(30),Student_age int)
Here i haven’t define any size of the field Student_id and Student_age because i have selected there type as integer so we can not define the size of integer as its size is predefined i.e. 4bit but i can define the size for VARCHAR therefore i have told the SQL-Server to reserve 30 characters for each Student_name field in the table. See the following figure for more help:
Inserting the record
Now we’ve create a table (Student) and now the next step is to insert the data. As we have three fields in our table created above therefore i will insert the data using following syntax:
INSERT INTO Table_name(column1,column2,column3)Values(value1,value2,value3)
INSERT INTO (Table_name) Values(value1,value2,value3)
In the snippet 1 column1,column2,column3 are our tables fields. As we have to insert the values of all three fields therefore i have mentioned all three , or you can left that space blank if you have to insert the values to all field as shown in snippet 2 but if i want to add the values to any two or one record then i will mention only that column and value according to its datatype and its syntax would be:
INSERT INTO Table_name(column1)Values(value1)
Lets take an example now, suppose i have following two SQL statements
INSERT INTO Student Values(1,'Bilal Amjad',21) INSERT INTO Student (Student_name)Values('Bilal Amjad')
The first statement will fill all three fields in the table but the second one will insert the record only into the Student_name field as Bilal Amjad. To view the inserted record select your database from the object explorer window at the left side of your SQL Server Management, expand the database, expand the Tables and you’ll see your table there.Now right click on the table name and choose the option Select Top 1000 rows. The inserted records will be displayed as follow:
As the name suggest Read operation means to read the data from our table/database. For this we use SELECT statement in SQL. SELECT statement search and displays the record on the basis of given conditions.
Lets take an example:
As i have inserted a record in my table with Student_id=1, Student_name=’Bilal Amjad’ and Student_age=21, now if i want to view it i will use the following select statement:
Select * from student where id=1
and as a result the above table will be displayed. We will learn the SELECT statement and its type in details in our next lessons.
As the name suggest Update statement in SQL will help us in updating a record. Following is the syntax for updating a record.
UPDATE table_Name SET column1=value1,column2=value2,... WHERE some_column=some_value;
Update Student SET Student_name="XYZ",Student_age=20 WHERE Student_id=1
Update statement consists a where clause in it which is used to identify the record. We’ll study different where clauses in upcoming lessons.
Delete statement helps us to delete a record from a table. Delete Statement in SQL follows the following syntax:
Delete from table_Name where some_column=some_value
Delete from Student where Student_id=1