云南大学 软件学院数据库试验5精选.docVIP

云南大学软件学院 实 验 报 告 姓名： 学号： 专业： 实验5 数据查询 1、完成教科书163页的如下习题： [3.2]b：select aid from AGENTS where [percent] =all (select MAX([percent]) from AGENTS) [3.5]：select aid,cid from AGENTS A,CUSTOMERS C where aid not in (select aid from ARDERS X where X.cid=C.cid and X.aid=a.aid) order by aid,cid [3.8]a：select cid,pid,MAX(dollars) as MAXSPENT from ARDERS group by cid,pid,dollars [3.8]b：select AVG (MAXSPENT) as AVERAGE from (select cid,pid,MAX(dollars) as MAXSPENT from ARDERS group by cid,pid,dollars) as x group by cid [3.11]b：(select cid,pid,AVG(dollars) as AVGSPENT from ARDERS group by cid,pid,dollars) as x where cid not in ( select cid from ( select cid,pid,AVG(dollars) as AVGSPENT from ARDERS group by cid,pid,dollars) as x where AVGSPENT 300) [3.11]f：select pid from ARDERS where cid in ( select cid from CUSTOMERS where city=Dallas) [3.11]j：update PRODUCTS set price=1.1*price where city=Duluthor city=Dallas [3.11]select aid,[percent] from AGENTS a where not exists ( select cid from CUSTOMERS c where city=Duluth and not exists ( select o.cid from ARDERS o where o.cid=c.cid and a.aid=o.aid)) order by [percent] 2、在“学生管理数据库”中完成如下查询： （1）列出软件2班女同学的名单 select * from 学生表where 班级=软件班 and 性别=女 （2）列出2002年元旦前注册的学生名单 select * from 学生表where 入学日期2002-1-1 （3）列出所有年龄在19岁以下，或者女同学的名单 select * from 学生表where 年龄=19 or 性别=女 （4）列出没有先修课的课程名 select * from 课程表where 先修课is null （5）列出既不是“电子系”，也不是“会计系”的学生的学号、姓名和所在院系 select 学号,姓名,所在院系from 学生表where 所在院系not in(电子系,会计系) （6）查询选修“C801”课程的学生的最高分 select max(成绩) as c801最高分from 成绩表where 课程号=c801 （7）统计男、女学生各自的人数和平均年龄 select 性别,count(性别) as 人数,avg(年龄) as 平均年龄from 学生表group by 性别 （8）列出选修了一门以上课程的学生学号，及其选修门数 select 学号,count(学号) as 选修门数from 成绩表group by 学号having count(学号)1 （9）查询“沈香娜”同学所在班级的所有学生的学号和姓名 select 学号,姓名from 学生表where 班级in (select 班级from 学生表where 姓名=沈香娜) （10）统计每一年龄选修课程的学生人数 select 年龄,count(s.学号) as 人数from (学生表s j