/    /  DBMS – Modification of the Database

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 ;

 

 

Reference 

Modification of the Database