oracle 查询数据 实验笔记三

利用student表、course表和Score表，完成相关的查询。
1.查询“李勇”同学的选课情况。

select score.* from student  join  score on score.sno=student.sno where sname='李勇';

2．查询选修了’C002课程且成绩在80分以上的学生学号、姓名。

select student.sno,sname from student,score
 where student.sno=score.sno and cno='c002' and grade>80;

3．查询没有参加考试的学生学号、姓名、课程号、课程名。

select student.sno,sname,score.cno,course.cname
from  student join score on student.sno=score.sno join course on score.cno=course.cno
where grade is null;

4．查询选修了“高等数学”课程且成绩在80分以上的学生学号、姓名。

select student.sno,sname 
from  student join score on student.sno=score.sno join course on score.cno=course.cno
where grade>80 and cname='高等数学';

5．查询成绩在80~90分之间（包括80、90）的学生学号、姓名。

select student.sno,sname from student,score
where student.sno=score.sno and  grade between 80 and 90;

6．查询‘李勇’同学的平均分、最高分。

select avg(grade),max(grade)
from student  join  score on score.sno=student.sno 
where sname='李勇';

7．查询没有选修‘c005’课程的学生学号、姓名。

select sno,sname 
from  student  where sno not in(select sno from score where cno='c005') ;

8．查询课程名以数据开头的课程信息。

select * from course where cname like '数据%';

9.查询“李勇”选修的课程号、课程名、成绩，查询结果按成绩的升序显示。

select score.cno,course.cname,grade from student,course,score
where student.sno=score.sno and score.cno=course.cno
and sname='李勇'  order by grade;

10．查询选修了‘大学英语’课程且成绩在70分以上的学生学号、姓名。

select  sno,sname from student
where sno in(select sno from score
where grade>70 and cno in
(select cno from course where cname='大学英语'));

10．查询选修了‘大学英语’课程且成绩在70分以上的学生学号、姓名。

select  sno,sname from student
where sno in(select sno from score
where grade>70 and cno in
(select cno from course where cname='大学英语'));

11．查询全部学生的选课情况。

select student.*,score.* from student left join score on student.sno=score.sno

12．查询各系男女生相应的人数。

select sdept,ssex,count(*) from student 
group by sdept,ssex;

13．查询选修了“高等数学”课程的学生的学号及其成绩，查询结果按成绩降序排列。（用子查询完成）

select sno,grade from score 
where cno in(select cno from course where cname='高等数学')
order by grade desc;

14．查询选修两门及以上课程的学生的基本情况。

select * from student where sno in
(select sno from score group by sno having count(*)>=2);

15．查询平均在75分以上的课程号、课程名。

select cno,cname from course 
where cno in(select cno from score
group by cno having avg(grade)>75);