Modification of the Database
The modification of a database has three commands, namely:
- DELETE
- INSERT
- UPDATE
Let us take the following table and understand each command with a few examples
R5: FACULTY
————————————————————
FNo FName DNo Qual Salary
————————————————————
22 Alice 21 Ph.D. 35000
24 Ben 22 MTech 30000
25 Max 22 MTech 42000
27 Becca 23 MTech 28000
30 Bella 23 MTech 32000
33 Priya 24 Ph.D. 33000
35 Riya 24 Ph.D. 32000
37 Sia 25 MTech 26000
39 Tom 25 MTech 24000
40 Bella 25 MTech 32000
————————————————————
Delete Command
This command helps us to remove rows from the table.
Syntax : DELETE from r where P
Example-1: Remove all the employees from Dept. no 24.
DELETE From FACULTY-1
Where DNo = 24 ;
Output : There will be 8 tuples left in Faculty-1.
Example-2 : Remove all the employees from ECE Dept.
DELETE From FACULTY-1
Where DNo = (Select DNo
From DEPT
Where DName = ‘ECE’ ) ;
Output : There will be 8 tuples left in Faculty-1.
Example-3 : Remove all the employees whose salary is less than 30000.
Delete From FACULTY-1
Where Salary< 30000 ;
Output : There will be 7 tuples left in Faculty-1.
Example-4 : Remove all the employees whose salary is less than the average salary of all the employees.
Delete From FACULTY-1
Where Salary< (Select avg(Salary)
From FACULTY-1) ;
Output : FNo FName DNo Qual Salary
————————————————————
22 Alice 21 Ph.D. 35000
25 Max 22 MTech 42000
30 Bella 23 MTech 32000
33 Priya 24 Ph.D. 33000
35 Riya 24 Ph.D. 32000
40 Bella 25 MTech 34000
Note : The average salary is : 31,600
Insert Command
This command helps us to insert rows into the table.
Syntax : INSERT into relation-name values (…..)
Example-1 : Add a tuple to FACULTY-1.
Insert into FACULTY-1 values (532, ‘XX’, 28,’MTech’,25000) ;
Example-2 :Add a tuple to STUD relation
Insert into STUD values ( 130, ‘def’, 24) ;
Example-3 :select tuples from FACULTY-1 and create a
relation FACULTY-2 with tuples belonging to DNo 25.
Let’s assume table structure for FACULTY-2 is already there.
Insert into FACULTY-2
(Select FNo, FName, DNo, Qual, Salary
From FACULTY-1
Where DNo = 25) ;
R5 : FACULTY-2
————————————————————
FNo FName DNo Qual Salary
————————————————————
37 Sia 25 MTech 26000
39 Tom 25 MTech 24000
40 Bella 25 MTech 32000
————————————————————
Note : One can create a new relation by considering data from one or more relations using the insert command.
Update Command
This command helps us to modify columns in table rows.
Syntax : update <relation name>
set <assignment>
where <condition>
Example-1 :Increase the salary of the employees who are drawing more than 35000 by 5 %.
Update FACULTY-1
Set Salary = Salary * 1.05
Where Salary >35000 ;
Example-2 :Increase the salary of the employees who are drawing less than average of all the employees, by 10 %.
Update FACULTY-1
Set Salary = Salary * 1.1
Where Salary <
(select avg(salary)
From FACULTY-1 ) ;
Example-3 :Increase the salary of the employees who are drawing less than 30000, by 8 % and for others by 6 %.
Update FACULTY-1
Set Salary = Salary * 1.06
Where Salary >30000 ;
Update FACULTY-1
Set Salary = Salary * 1.08
Where Salary <30000 ;
Modification of the Database