Data Manipulation Language (DML) – SQL – Part1

Today i am going to talk about Data Manipulation Language (DML) which is used to insert, update, and delete data in a database.

A popular data manipulation language is Structured Query Language (SQL) which is used to retrieve and manipulate data in relational database.

I am going to use SQL Server Management Studio in my practical explanation.

DML in SQL :-

  1. SELECT … FROM …
  2. INSERT INTO … VALUES …
  3. UPDATE … SET … WHERE …
  4. DELETE … WHERE …

Let us now take examples on SELECT and INSERT … I have created two tables called (Employee and Department) and have the following structure

Diagram

1. SELECT

Select used to show data stored in database, for example to know all the departments i will select all the data in Department table

SelectAllDepts

And also to know all employees i do the same

SelectAllEmps

I also can select an employee according a specific condition … below i select the employee with ID = 1

SelectEmp1Now I will select employee with name = Soaad

SelectEmpSoaadThe below script select an employee with name starts with letter ‘S’

SelectEmpStartWithS
2. INSERT

Insert used to insert data in database, for example if i want to add new department called IT, I will use this query

InsertIntoDepartmentTable

Now if i Select all the departments again i will see that the new department added

SelectAllDeptsAfterInsert

I also could specify columns name in insert

InsertIntoEmployeeTableAfter that when i select all the employees i will see that the new employee added

SelectAfterInsertNewEmp

I also could insert null values like that

InsertNULL

Now let us use the keyword default … first I alter the Department table and make the default for column DepName is (Default Dept) and i will insert new department using the default name

InsertDefault

If i want to insert multiple employees in Employee Table i will do like that

MultipleInsert

Sometimes we need to select some data from existing table to put it in new table … so let us say that we will move all the employees with name start with letter ‘A’ to a new table called AEmployees

AEmployeesTable

As you notice in the above example i take a copy from some data in Employee Table and put it in the new table using SELECT … INTO

The last thing i want to explain it is what if i want to insert in existing table some data that i select it from another table .. for example i will select sooad from Employee table and insert it to AEmployees table .. in this case i will do like that

InsertIntoFromSelect

That is enough for today … In the next lesson I will explain the Update and Delete … I hope everything is clear, see you later 🙂