SQL实验2
利用查询分析器完成实验
School
数据库中存在以下四张表：
表STUDENTS( sid，sname,email,grade) 表TEACHERS(tid，tname，email，salary) 表COURSES（cid，cname，hour） 表CHOICES（no，sid，cid，tid，score）
学生选择课程，一个课程对应一个老师，表CHOICES保存学生的选课记录，对数据库进行以下操作：
1、查询年级为2001的所有学生的名字，并按标号顺序排列。 select sname from STUDENTS where grade='2001' ORDER BY sid ASC
2、查询学生的选修课成绩合格的课程成绩，并把成绩换算成积分，60分对应积分1，每增加1分，积分增加0.1 select cid, score, 1+0.1*(score-60) jifen from CHOICES
Where score>=60;
3、查询学时是48或64的课程名称
select cname from COURSES where hour=48 OR hour=64 4、查询所有课程名称中含有data 的课程编号 select cid from COURSES where cname LIKE 'úta%'
5、查询所有选课记录的课程号（不重复显示）
select distinct cid from CHOICES 6、统计所有老师的平均工资
select AVG(salary) from TEACHERS
7、查询所有老师的编号及选修其课程的学生的平均成绩，按平均成绩降序排列
select tid , AVG(score) from CHOICES GROUP BY tid , cid
ORDER BY AVG(score) DESC;
8、统计每个课程的选课人数和平均成绩 select cid , count(distinct sid), AVG(score) from CHOICES GROUP BY cid;
9、查询至少选修了三门课程的学生编号 select sid from CHOICES GROUP BY sid having count(cid)>=3;
10、查询编号为800009026的学生姓名、所选的全部课程的课程名
和成绩(3行)
select distinct sname,cname , score from STUDENTS,COURSES, CHOICES
where STUDENTS.sid = '800009026' and
STUDENTS.sid = CHOICES.sid and COURSES.cid=CHOICES.cid ;
11、查询所有选修了database的学生
select sid from COURSES , CHOICES
where COURSES.cid = CHOICES.cid and cname = 'database'; 12、求出选择了同一门课程的学生对 13、查询至少被两名学生选修了的课程编号 select cid from CHOICES
GROUP BY cid having count(distinct sid)>=2;
14、查询选修了编号800009026的学生所选的某个课程的学生编号 select distinct sid
from CHOICES where cid in (select cid from CHOICES where sid='800009026') and sid<>'800009026'; 15、查询学生的基本信息及选修课程编号和成绩
select STUDENTS.sid , sname , email , grade , cid , score from STUDENTS,CHOICES
where STUDENTS.sid =CHOICES.sid;
16、查询学号800009026的学生的姓名和选修的课程的名称及成绩(3 行受影响)
select sname, cname, score from STUDENTS,COURSES,CHOICES
where CHOICES.sid='800009026' and
CHOICES.sid=STUDENTS.sid and COURSES.cid=CHOICES.cid; 17、查询与学号850955252的学生同年级的所有学生资料
select * from STUDENTS where grade=(select grade from STUDENTS where sid='850955252' ); 18、查询所有有选课的学生的详细信息
select STUDENTS.sid , sname , email , grade , no,cid , score from STUDENTS,CHOICES
Where cid is NOT NULL and STUDENTS.sid =CHOICES.sid; 19、查询没有学生选的课程编号（0行） select cid from CHOICES GROUP BY cid having count(sid)=0;
20、查询选修了课程名为C++的学生学号和姓名 （所影响的行数为 5876 行）
select sid, sname from STUDENTS where sid in
(select sid from CHOICES where cid in (select cid
from COURSES where cname='C++'));
21、找出选修课程成绩最好的选修记录
select * from CHOICES where score in(select MAX(score) from CHOICES);
22、找出课程UML或课程C++课时一样的课程名称
select cname from COURSES where hour in (select hour from COURSES where cname='UML' OR cname='C++' );
23、查询所有选修编号10001的课程的学生姓名
select STUDENTS.sname from STUDENTS,CHOICES where STUDENTS.sid=CHOICES.sid and cid='10001';
24、查询选修了所有课程的学生姓名
select sname from STUDENTS where not exists
(select * from COURSES where not exists
(select * from CHOICES where sid=STUDENTS.sid and cid=COURSES.cid) );
25、利用集合运算查询选修了课程C++或课程Java的学生编号
select sid from CHOICES
where sid in(select sid from COURSES where cname='C++') UNION
select sid from CHOICES
where sid in(select sid from COURSES where cname='Java');
26、实现集合交运算，查询选修了课程C++和课程Java的学生编号（所影响的行数为 306 行）
Select distinct sid from CHOICES,COURSES
where COURSES.cid=CHOICES.cid and COURSES.cname='C++' and sid in (Select distinct sid from CHOICES,COURSES where COURSES.cid=CHOICES.cid and COURSES.cname='Java') 第二种：
select sid from CHOICES
where sid in(select sid from COURSES where cname='C++') intersect
select sid from CHOICES
where sid in(select sid from COURSES where cname='Java');