数据库系统概念第三章.pptVIP

* Deletion Try: “delete all tuples in the instructor relation for those instructors associated with a department located in the Watson building.” Deletion Delete all tuples in the instructor relation for those instructors associated with a department located in the Watson building. delete from instructor where dept_name in (select dept_name from department where building = ’Watson’); Insertion Add all instructors to the student relation with tot_creds set to 0 insert into student select ID, name, dept_name, 0 from instructor Insertion The select from where statement is evaluated fully before any of its results are inserted into the relation (otherwise queries like insert into table1 select * from table1would cause problems, if table1 did not have any primary key defined. Updates Recall the basic update statement update instructor set salary = salary *1.05 where salary 70000; Observe the update with subquery update instructor set salary = salary *1.05 where salary (select avg (salary) from instructor); SSDUT-Software School of DUT Updates Can you figure out the statement?“all instructors with salary over $100,000 receive a 3 percent raise, whereas all others receive a 5 percent raise.” Does the following statements works?Write two update statements: update instructor set salary = salary * 1.05 where salary = 100000; update instructor set salary = salary * 1.03 where salary 100000; SSDUT-Software School of DUT Updates update instructor set salary = salary * 1.03 where salary 100000; update instructor set salary = salary * 1.05 where salary = 100000; The order is important!!! Can be done better using the case statement SSDUT-Software School of DUT Case Statement “all instructors with