Question

Write about DML Commands?

Answer 1

DML COMMANDS

Once the schema or structure of the table is created, values can be added to the table. The DML commands consist of inserting, deleting and updating rows into the table.

(i) INSERT command

The INSERT command helps to add new data to the database or add new records to the table.

Syntax: INSERT INTO [column-list] VALUES (values);

(a) INSER T INTO Student (Admno, Name, Gender, Age, Place)

VALUES (100, ‘Ashish ’ , ‘M\ 17, ‘Chennai);

(b) INSERT INTO Student (Admno, Name, Gender, Age, Place)

VALUES (10, ‘Adarsh’ , ‘M’ , 18, ‘Delhi);

(c) INSERT INTO Student VALUES (102, ‘Akshith \ ‘M’ , ‘17, ’ ‘Bangalore);

The above command inserts the record into the student table.

To add data to only some columns in a record by specifying the column name and their data, it can be done by:

(d) INSERT INTO Student(Admno, Name, Place) VALUES (103, ‘Ayush’ , ‘Delhi’);

(e) INSERT INTO Student (Admno, Name, Place) VALUES (104, ‘Abinandh ‘Chennai);

The student table will have the following data:

Admno	Name	Gender	Age	Place
100	Ashish	M	17	Chennai
101	Adarsh	M	18	Delhi
102	Akshith	M	17	Bangalore
103	Ayush	M	18	Delhi
104	Abinandh	M	18	Chennai

(ii) DELETE COMMAND

The DELETE command permanently removes one or more records from the table. It removes the entire row, not individual fields of the row, so no field argument is needed.

The DELETE command is used as follows:

DELETE FROM table-name WHERE condition; For example to delete the record whose admission number is 104 the command is given as follows: DELETE FROM Student WHERE Admno=104;

104

Abinandh

M

18

Chennai

The following record is deleted from the Student table. To delete all the rows of the table, the command is used as :

DELETE * FROM Student;

The table will be empty now and could be destroyed using the DROP command.

(iii) UPDATE COMMAND

The UPDATE command updates some or all data values in a database. It can update one or more records in a table. The UPDATE command specifies the rows to be changed using the WHERE clause and the new data using the SET keyword. The command is used as follows: UPDATE SET columnname = value, column-name = value,… WHERE condition;

For example to update the following fields:

UPDATE Student SET Age = 20 WHERE Place = “Bangalore ”;

The above command will change the age to 20 for those students whose place is “Bangalore”.

The table will be as updated as below:

Admno	Name	Gender	Age	Place
100	Ashish	M	17	Chennai
101	Adarsh	M	18	Delhi
102	Akshith	M	20	Bangalore
103	Ayush	M	18	Delhi

To update multiple fields, multiple field assignment can be specified with the SET clause separated by comma. For example to update multiple fields in the Student table, the command is given as: UPDATE Student SETAge=18, Place = ‘Chennai’ WHERE Admno = 102;

102

Akshith

M

20

chennai

The above command modifies the record in the following way.

Admno	Name	Gender	Age	Place
100	Ashish	M	17	Chennai
101	Adarsh	M	18	Delhi
102	Akshith	M	18	Chennai
103	Ayush	M	18	Delhi