数据库复习题全(2)

? where sname=?刘晨?）

? 查询所有学生的考试情况(包括学号sno，姓名sname，课程名cname，成绩grade)。 ? Select student.sno,sname,cname,grade

? from (student left join s_c on student.sno=s_c.sno) ? left join course on s_c.cno=course.cno

? 查询系别sdept=“IS”的每个学生的学号sno，其所选课程的平均成绩，按平均成绩降序排序。 ? Select sno,avg(grade)

? from s_c group by sno ? having sno in

? (select sno from student

? where sdept=?is ?)

? order by avg(grade) desc

五、设某个数据库中有两个表products(pid, pname, pquantity)和sales(saleid, pid, quantity)分别来记录商品的库存和销售情况，其中pquantity用来表示某种商品的库存量，quantity用来表示某种商品的某次销量。请设计触发器完成某种商品的库存量随着销量的变化而变化。 create trigger ins_trigger on sales after insert as

update products

set pquantity=pquantity-

(select sum(quantity) from inserted

where inserted.pid=products.pid) where products.pid in (select pid from inserted) Go

create trigger upd_trigger on sales after update as

begin

/*收回旧值*/

update products

set pquantity=pquantity+

(select sum(quantity) from deleted

where deleted.pid=products.pid) where products.pid in (select pid from deleted) /*减去新值*/

update products

set pquantity=pquantity-

(select sum(quantity) from inserted

where inserted.pid=products.pid)

6

where products.pid in (select pid from inserted) end Go

create trigger del_trigger on sales for delete as

update products

set pquantity=pquantity+

(select sum(quantity) from deleted

where deleted.pid=products.pid) where products.pid in (select pid from deleted) go

7

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说教育文库数据库复习题全(2)在线全文阅读。