Create, Drop, Insert, Update, Delete and Select – The Basic SQL

This topic for those who are very beginner of SQL. Here I will show some basic SQL by Microsoft SQL Server.

1.       Create a table: Here I created a table name hrm_employee. Here emp_gid is int type, primary key and identity. Identity means its value will increment automatically by the sql server. You don’t need to insert it’s value.

--Create Table : hrm_employee---

CREATE TABLE hrm_employee

(

      emp_gid INT PRIMARY KEY IDENTITY,

      emp_fullnm VARCHAR(100),

      emp_nicknm VARCHAR(50),

      emp_designation VARCHAR(100)

)

2.       Insert data in hrm_employee table: In this occasion, I have inserted 3 data i.e 3 rows. If you see in closer look, you will see that I didn’t insert any value of emp_gid because it increments its value automatically because it is an identity column.

---Insert data ito table: hrm_employee----

-- Insert data i.e. row-1 ---

INSERT INTO hrm_employee

(

      emp_fullnm

      , emp_nicknm

      , emp_designation

)

VALUES

(

      'Md. Mahedee Hasan'

      , 'Mahedee'

      , 'Senior Software Engineer'

)    

-- Insert data i.e. row-2 ---

INSERT INTO hrm_employee

(

      emp_fullnm

      , emp_nicknm

      , emp_designation

)

VALUES

(

      'Md. Asrafuzzaman'

      , 'Emon'

      , 'Senior Software Engineer'

)    

-- Insert data i.e. row-3 ---

INSERT INTO hrm_employee

(

      emp_fullnm

      , emp_nicknm

      , emp_designation

)

VALUES

(

      'Md. Khondakar Enamul Haque'

      , 'Rony'

      , 'Broadcast Engineer'

)    

4. Infromation Retrive: Get all information of hrm_employee. Here I showed two ways to retrive data and I suggest to use second one. Second one is best practiced.

--Get all data from hrm_employee--

SELECT * FROM hrm_employee

--or

SELECT emp_gid, emp_fullnm, emp_nicknm, emp_designation FROM hrm_employee

5.  Update hrm_employee: Is very easy to update a row or multiple rows against a condition. Lets look on the following query.

--update data from table: hrm_employee --

UPDATE hrm_employee

SET emp_fullnm = 'M.K. Enamul Haque'

, emp_designation = 'Senior Broadcast Engineer'

WHERE emp_gid = 3

6. Delete Information: If you want to delete information from hrm_employee against a condition follow the query.

---Delete data from table: hrm_employee---

DELETE FROM hrm_employee WHERE emp_gid = 3

7. Drop Table: Drop table means remove table for database. Use the query to drop hrm_employee table.

---Drop table i.e remove table from Database--

DROP TABLE hrm_employee