/    /  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