DML Commands
DML -Commands
• Data Manipulation Language (DML) commands are used for managing data in database. DML
commands are not auto-committed. It means changes made by DML command are not permanent to
database, it can be rolled back.
• DML Commands:
• INSERT
• UPDATE
• DELETE
emp table
Emp_id Emp_name Emp_add Emp_salary Emp_DOB PermanentYN
101 Sam Malad 50000 1999-03-15 Y
102 Jill Dadar 80000 1996-05-18 Y
103 Mack Bandra 30000 1992-05-12 Y
104 Tim Kandivali 40000 1992-08-05 N
105 Sid Bandra 50000 1994-07-25 Y
106 Kim Dadar 100000 1987-12-09 N
107 Ann Malad 150000 1985-08-17 Y
INSERT command
• Insert command is used to insert data into a table.
• Following is its general syntax,
INSERT INTO table_name VALUES(data1, data2, ...);
• Lets see an example,
• Consider a table emp with the following fields.
Emp_id Emp_name Emp_salary
• INSERT INTO emp VALUES(101, 'Adam’, 40000);
• The above command will insert a new record into emp table.
Emp_id Emp_name Emp_salary
101 Adam 40000
Insert value into only specific columns
• We can use the INSERT command to insert values for only some specific columns of a row. We can
specify the column names along with the values to be inserted like this,
insert into emp(emp_no, emp_name)values(102, ’John’);
• The above SQL query will only insert id and name values in the newly inserted record.
UPDATE SQL command
• UPDATE command is used to update any record of data in a table. Following is its general syntax,
• UPDATE table_name SET column_name = new_value WHERE some_condition;
• E.g.
update emp set emp_salary=60000 where emp_no=102;
Note: In the above statement, if we do not use the WHERE clause, then our update query will
update salary for all rows of the table to 60000.
Updating Multiple Columns
We can also update values of multiple columns using a single UPDATE statement.
UPDATE emp SET emp_name='Abhi', emp_salary=120000 where emp_no=102;
• When we have to update any integer value in a table, then we can fetch and update the value in the
table in a single statement.
• For example, if we have to update the salary(10% increment) column of emp table every year for every
emp, then we can simply run the following UPDATE statement to perform the following operation:
update emp set emp_salary=emp_salary*0.15;
DELETE SQL command
• DELETE command is used to delete data from a table.
• Following is its general syntax to delete all the tuples.
DELETE FROM table_name;
OR
TRUNCATE TABLE table_name;
DELETE FROM emp;
TRUNCATE TABLE emp;
• The above command will delete all the records from the table emp.
Delete a particular Record from a Table
In our emp table if we want to delete a single record, we can use the WHERE clause to provide
a condition in DELETE statement.
DELETE FROM emp WHERE emp_no=102;
• The above command will delete the record where emp_no is 102 from the table emp.
SQL Commands
• DDL: Data Definition Language
• DML: Data Manipulation Language
• DQL: Data Query Language (select Query)
• TCL: Transaction Control Language
• DCL: Data Control Language
SELECT SQL Query
• SELECT query is used to retrieve data from a table. It is the most used SQL query. We can retrieve complete
table data, or partial data by specifying conditions using the WHERE clause.
• SELECT query is used to retieve records from a table. We can specify the names of the columns which we
want in the resultset.
• Syntax of SELECT query
SELECT
column_name1,
column_name2,
column_name3,
...
column_name
FROM table_name;
emp table
Emp_id Emp_name Emp_add Emp_salary Emp_DOB PermanentYN
101 Sam Malad 50000 1999-03-15 Y
102 Jill Dadar 80000 1996-05-18 Y
103 Mack Bandra 30000 1992-05-12 Y
104 Tim Kandivali 40000 1992-08-05 N
105 Sid Bandra 50000 1994-07-25 Y
106 Kim Dadar 100000 1987-12-09 N
107 Ann Malad 150000 1985-08-17 Y
Select Query
1. To extract emp id,name and salary
Select emp_id,emp_name,emp_salary from emp;
2. To extract complete emp data
Select * from emp;
3. To select a particular record based on a condition
We can use the where clause to set a condition,
select * from emp where name = ‘Sid’;
Select Query
4. SELECT emp_id, emp_name, emp_salary+3000 FROM emp;
5. Applying condition on numeric Fields
SELECT emp_id, emp_add, emp_salary FROM emp WHERE emp_id = 104;
6. Applying condition on Text Fields
SELECT emp_id, emp_name, emp_salary FROM emp WHERE emp_name = ‘Kim’
Select Query
Operators for WHERE clause condition
Following is a list of operators that can be used while specifying the WHERE clause condition.
Operator Description
= Equal to
!= Not Equal to
< Less than
> Greater than
<= Less than or Equal to
>= Greate than or Equal to
BETWEEN Between a specified range of values
LIKE This is used to search for a pattern in
value.
IN In a given set of values
Select Query
• Select emp_id, emp_name from emp
where emp_salary >=100000;
• Select emp_id, emp_name from emp
where emp_salary !=100000;
• Select emp_id, emp_name, salary from emp
where salary between 50000 and 100000;
