? 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