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' )
3. 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
4. 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
5. 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
6. 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