数据库

案例

基础查询

题目

1、查询MA系的女同学。
2、查询CS系姓李的学生选修的课程,列出学号，课程号和成绩。
3、查询选修了数据库课程的学生的学号成绩，按成绩降序排列。
4、找出学分为4分以上的课程的选修情况，列出学号，课程名,成绩。
5、检索数据库的成绩在90分以上的学牛的学号和姓名.

答案

1.select * from student where sdept like ‘MA’;
2.select sno,cno,grade from sc
where sno in(select sno from student where sdept like ‘CS’ and sname like ‘李%’);
3.select sno,grade from sc
where cno in(select cno from course where cname like ‘DB’) order by grade desc;
4.select sno,cname,grade from sc,course where sc.cno=course.cno and credit>=4;
5.select sno,sname from student where sno in(select sno from sc
where cno in(select cno from course where cname like ‘DB’)
and grade>=90);

嵌套查询

题目

1、查询和数据库相同学分的课程。
2、查询选修了数据库课程的学生的学号。
3、查询成绩最高的学生的姓名。
4、查询CS系成绩最高的学生的学号、姓名以及成绩。
5、查询数据库课程成绩最高的学生的姓名

答案

1.select * from course where credit=(select credit from course where cname like ‘DB’);
2.select sno from sc where cno in=(select cno from course where cname like ‘DB’);
3.select sname from student where sno=(select sno from sc
where grade>=all(select grade from sc);
4.select sc.sno,sname,grade from student,sc
where grade>=all(select grade from sc where sno in(select sno from student where sdept like ‘CS’))
and sdept like ‘CS’ and sc.sno=student.sno;
5.select sname from student
where sno in(select sno from sc where grade>=all(select grade from sc
where cno=(select cno from course where cname like ‘DB’)) and cno=(select cno from course where cname like ‘DB’));

聚合函数+group by

题目

1、查询每门课程的平均成绩。
2、查询每门课程不及格的学生人数。
3、查询每个系学生的最高成绩。
4、查询平均分在75以上的课程。
5、查询女同学中成绩最高的学生所在的系。
6、查询选课人数最多的休任。

答案

1.select cno,avg(grade) from sc group by cno;
2.select cno,count(sno) from sc where grade<60 group by cno;
3.select sdept,max(grade) maxg from student,sc where student.sno=sc.sno group by sdept;
4.select sc.cno,cname,avg(grade) avgg from course,sc
where sc.cno=course.cno
group by sc.cno,cname having avg(grade)>=75;
5…