数据库作业2答案.pdf
大丈夫处世，不能立功建业，几与草木同腐乎？罗贯中以铜为镜，可以正衣冠；以古为镜，可以知兴替；以人为镜，可以明得失。旧唐书魏征列传SQL 作业：第三章：课后习题 2：(1)select sno,sname from student where height (2)select cno,credit from course where semester=秋 and cno like CS%;(3)select sname,credit,grade from student s,course c,sc where=and=and like CS%and sex=男and semester=秋and grade is not null (4)select sname from student s,sc where=and cno like EE%and sex=女 or:select sname from student s,sc where=and cno like EE%and sex=女 group by,sname having count(cno)=1 (5)select sno,count(cno),avg(grade)from sc where grade is not null group by sno (6)select cno,count(sno),max(grade),min(grade),avg(grade)from sc where grade is not null group by cno (7)select,sname from student s,sc where=and not in(select sno from sc where grade is null)and grade is not null group by,sname having min(grade)=80 (8)select sname,credit from student s,course c,sc where=and=and grade is null (9)select sname from student s,course c,sc 先天下之忧而忧，后天下之乐而乐。范仲淹谋事在人，成事在天！增广贤文where=and=and credit=3 and grade=3 group by,sname having min(grade)and=all(select sum(sale-amount)from sale s1 where =and=and=group by distirct,sale-year,sale-month,eid)附 sample 数据库模式 employee(eid,ename,dept_id,salary);manager(eid,dept_id);sale(eid,district,sale-year,sale_month,sale-goods,sale-amount).6 用 SQL 命令产生如下的关系模式：供应者 supplier(sno,sname,city),主键是 sno 部件 part(pno,pname,color,weight),主键是 pno 工程 job(jno,jname,city)，主键是 jno 供应数量 spj(sno,pno,jno,quantity),主键是(sno pno jno)各个字段的类型自行定义，但需满足如下的约束：a part 中的 weight 应大于 100;b supplier 中的 city 取值范围是(上海 西安 南京)；c spj 中如果 quantity 小于 10,则删除该记录；d spj 中的 sno、pno、jno 分别和、相关联，并且如果 supplier、part 或 job 中的记录被删除，则 spj 的相关记录也被删除。7 据第 6 题中的关系模式，完成下列查询：取出所有在上海的工程的全部细节(即所有相关信息)；select*from supplier s,part p,job j,spj where=上海 and=and=and=取出给上海或北京的任何工程提供部件的供应者号(sno)；select distinct sno from job,spj where=and=北京 or=上海)取出不提供红色部件给上海的工程的北京的供应者号；select sno from supplier where city=北京 and sno not in (select distinct sno from job,spj,part p where=上海 and=and=and=红色)列出每个城市中每一种零件的供应者个数及其总数；select city,count(distinct sno),sum(quantity)from supplier s,part p,spj where=and=group by city,列出至少有 5 个电动机供应者的城市。select distinct city from supplier s1 where(select count(distinct from supplier s,part p,spj where=and=and=电动机 and=丈夫志四方，有事先悬弧，焉能钧三江，终年守菰蒲。顾炎武万两黄金容易得，知心一个也难求。曹雪芹=5 设有学生选修课程数据库 s(sno,sname,age,sex,department,address)sc(sno,cno,grade)c(cno,cname,teacher)试用 SQL 命令查询下列问题。列出每个男生的平均成绩、最高分，和总分 select,avg(grade),max(grade),sum(grade)from s,sc where=and=男 group by 统计各系的男生和女生的人数 select department,sex,count(*)from s group by department,sex 求出平均成绩在 85 分以上的课程名及其平均成绩、最高成绩 select cname,avg(grade),max(grade)from sc,c where=group by,cname having avg(grade)=85 选出学习过编译，数据库或体系结构课程，且这些课程的成绩之一在 90 分以上的学生的名字 select sname from s where sno in(select sno from sc,c where=and cname in(编译，数据库，体系结构)and grade=90)or select sname from s where 90=35 and max(grade)=95 列出选修过数据库课程两次的学生名单。select sname from s,sc,c where=and=and cname=数据库 group by,having count(grade)=2
