INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
  

Simple INSERT Query in DBMS

In this type of insert, all the values should be provided to all the columns that exist in the table without specifying the column name

The order of values declared in the values clause should follow the original order of the columns in the table

  
insert into student
 values(66'trishaank','computers','24-07-1998',92);
  

Inserting data into required columns

In this case, the order of columns declared in insert need not be the same as that of the original table order, only the specified column values are added and null values added to the remaining column values in that tuple

  
insert into student(stuid,sname,branch) values (82,'Srinivas','Computers');
  

In this type of insert, the values are entered by the user at the execution time

  
insert into student values(&stuid,'&sname','&branch','&dob',&score);
  

  
enter value for stuid:73
enter value for sname:Prashanth
enter value for brance:Physics
enter value for dob:17-08-1997
enter value for score:78
  

  
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
  

The SET is Is used to set new values to the required column and the where Clause is used to filter the rows for which rows of data are needed to be updated

Updating a single column

Any row in the database table can be updated using update statement

Consider a sample table EMP as shown below

Update the commission of all employees to 200/- whose salary is less than 3000/-

  
UPDATE emp SET comm=200 
WHERE sal<3000;
  

Output

  
7 ROWS SELECTED
  

Initially, in the EMP table, values are NULLfor comm column for all the rows but using update statement we have changed the commission to 200 from NULL for those employees who are having salaries less than 3000.

Using a single update statement with can parallelly update any number of column

Update salary by 500 and change the commission to 1000 for all analysts working in the company

  
update emp set sal=sal+500 ,comm=1000
where job='ANALYST';
  

  
2 rows selected.
  

We have two analysts, hence two rows will be updated i.e salary will be increased from 3000 to 3500 and commission will be changed to 1000 i.e both salary and commission columns get updated.

If you don’t specify where clause, it means that no condition is required and no data filtering happens and all the rows present in the table will be updated for that column as specified

Make the salary of all employees working in the company to 500

  
update emp 
set sal=500;
  

  
7 rows selected
  

We have seven rows present in the table, the value of a salary in every row becomes 500 because there is no condition for data change, hence all the rows will be updated

  
DELETE FROM table_name
WHERE condition;
  

Deleting a single record

Where clause is used to provide a condition for deleting a particular record in the table

Consider the sample table EMP

Delete the record of employee number 7698

  
delete from emp where empno = 7698;
  

  
1 ROW DELETED
  

• A single employee present in the company with employee number 7698 will be deleted from the database table
• The previous table we have 7 rows but here we are having only 6 records i.e one record has been deleted from the database table.

Delete statement can be used to delete multiple rows at a time in a table

Delete the records of all employees whose salary is greater than 2500 expect the president

  
delete from emp 
where sal>2500  and job!=  'PRESIDENT';
  

  
4 ROWS DELETED
  

In the previous table, we have 7 records but after the execution of this query we are left with only three records i.e all those employees who are having a salary greater than 1000 except president will be deleted.

• All the rows present in the table will be deleted if there is no condition specified by using where clause
• In this type of delete, the table will become empty and no records will be present to display

  
delete from emp;
  

  
7 ROWS DELETED
  

All the seven rows present in the emp table will be deleted and row-count becomes zero i.e nothing to display.

  
truncate table emp;