数据库实验二

-实验二指导实验2.1 数据查询1） 要求以School数据库为例，在该数据库中存在四张表格，分别为：l 表STUDENT(sid, sname, email, grade);l 表TEACHERS(tid, tname, email, salary);l 表COURSES(cid, cname, hour);l 表CHOICES(no, sid, tid, cid, score)在数据库中，存在这样的关系：学生可以选择课程，一个课程对应一个教师。在表CHOICES中保存学生的选课记录。按以下要求对数据库进行查询操作：(1) 查询年级为2001的所有学生的姓名和邮箱，并按编号降序排列。Select sname from studentswhere grade=2001order by sid(2) 查询学生的选课成绩合格的课程成绩和任课教师名字，并把成绩换算为积点（60分对应积点为1，每增加1分，积点增加0.1）。 Select CHOICES.Score,1+0.1*(CHOICES.Score-60) GPA,TEACHERS.tname from CHOICES,TEACHERSWHERE CHOICES.Score=60(3) 查询课时介于48和64之间(包括48和64)的课程的名称。SELECT cname from COURSESWHERE hour BETWEEN 48 AND 64(4) 查询所有课程名称中以d打头且以e结尾的课程编号和名称。 SELECT cname,cidfrom COURSESWHERE cname LIKE d%e(5) 查询所有选课记录的教师名字（不重复显示）。 Select DISTINCT tnameFROM TEACHERS,CHOICESWHERE TEACHERS.tid=CHOICES.tid(6) 找出工资最高的教师名字。 SELECT tnamefrom TEACHERSWHERE tid=199999999 ORDER BY salary DESC(7) 查询所有教师的编号及选修其课程的学生的平均成绩，要求只列出平均成绩及格的结果，且按平均成绩降序排列。 SELECT tid,AVG(score)from CHOICESGROUP BY TID,SCOREHaving AVG(score)=60ORDER BY AVG(score) DESC(8) 统计各个学生的学生编号、选课数量和所选课程的平均成绩。 SELECT sid,count(distinct cid),AVG(score) averagefrom CHOICESgroup by sidSELECT sid,count(cid),AVG(score) average?from CHOICESgroup by sid(9) 查询至少选修了四门课程的学生编号和学生姓名。 select choices.sid,snamefrom students,choicesgroup by students.sid,choices.sid,snamehaving count(choices.cid)=4 and students.sid=choices.sid(10) 查询编号800014004的学生所选的全部课程的课程名、任课教师名和成绩。 select cname,tname,scorefrom courses,teachers,choiceswhere choices.sid=800014004 and teachers.tid=choices.tid and courses.cid=choices.cid(11) 求出选择了同一个课程的学生数，以课程名称、学生数方式显示。 select cname,count(choices.cid) student num-STUDENT NUMfrom courses,choicesGROUP BY courses.cid,choices.cid,ame-?why 去掉courses.cid不行(12) 查询选修了编号894037661的学生所选的某个课程的学生名字。 select snamefrom students,choiceswhere cid in(select cidfrom choices,studentswhere students.sid=choices.sid and choices.sid=894037661) and choices.sid894037661(13) 求出至少被两名学生且至多被6000名学生选修的课程编号。 select cidfrom choicesgroup by cidhaving count(distinct sid) between 2 and 6000(14) (不用集合运算)查询所有同时选修了database和c+的学生的编号。(15) 查询学生的基本信息及选修课程编号和成绩。 select students.sid,sname,email,grade,cid,scorefrom students,choiceswhere students.sid=choices.sid(16) 选出被选修，但只有课程编号没有课程名称的课程。（不重复） select distinct cidfrom choices(17) 查询与学号850955252的学生同年级的所有学生资料。 select *from studentswhere grade=(select grade from studentswhere sid=850955252)(18) 查询没有选修10005课程的学生信息。 select *from students,choiceswhere students.sid=choices.sid and choices.sid not in(select sid from choices where cid=1005)(19) 找出和课程java或课程c+的课时一样课程信息。 select *from courseswhere hour in (select hour from courses where cname=java or cname=c+)(20) 利用集合运算，查询选修课程C+或选修课程Java的学生的编号。 select sid from choiceswhere sid in (select sid from courses where cname=java)unionselect sid from choiceswhere sid in (select sid from courses where cname=c+)(21) 实现集合交运算，查询既选修课程C+又选修课程database的学生的编号。 select distinct sidfrom choices,courseswhere courses.cid=choices.cidand ame=c+and sid in(select distinct sidfrom choices,courseswhere courses.cid=choices.cid and ame=database)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=database) (22) 实现集合减运算，查询选修课程C+而没有选修课程database的学生的编号。未用集合运算：Select distinct sid From choices,courses Where courses.cid=choices.cid And ame=c+ And sid not in( Select distinct sid From choices,courses Where courses.cid=choices.cid and ame=database)集合运算：select sid from choices where sid in (select sid from courses where cname=c+) except select sid from choices where sid in (select sid from courses where cname=database)？(23) 选出至少有一门课成绩不合格的学生所选全部课程的平均成绩。提示：蓝色为选作题目！实验2.2 空值和空集的处理1） 要求（1） 查询所有选课记录的成绩并将它换算为五分制（满分5分，合格3分），注意SCORE取NULL值的情况。select sid,tid,cid,score/20 Scorefrom choices（2） 通过查询选修编号10028的课程的学生的人数，其中成绩合格的学生人数，不合格的学生人数，讨论NULL值的特殊含义。 select count(sid) Count_Stufrom choiceswhere cid=10028union select count(sid) Count_StuPassfrom choiceswhere cid=10028 and score=60union select count(sid) Count_StuUnPassfrom choiceswhere cid=10028 and score=60select count(sid) Count_StuUnPassfrom choiceswhere cid=10028 and score60（3） 通过实验检验在使用ORDER BY进行排序时，取NULL的项是否出现在结果中？如果有，在什么位置？ select *from choicesorder by score（4） 在上面的查询过程中如果加上保留字DISTINCT会有什么效果？ select distinct*from choicesorder by score没有变化（5） 通过实验说明使用分组GROUP BY对取值为NULL的项的处理。（6） 结合分组，使用集合函数求每个同学的平均分、总的选课记录、最高成绩、最低成绩和总成绩。 select avg(score) aver_score,count(*) total_num,max(score) max_score,min(score) min_score,sum(score) sum_scorefrom choices（7） 查询成绩小于60的选课记录，统计总数、平均分、最大值和最小值。 select avg(score) aver_score,count(*) total_num,max(score) max_score,min(score) min_score,sum(score) sum_scorefrom choiceswhere score0（8） 采用嵌套查询的方式，利用比较运算符和谓词ALL的结合来查询表COURSES中最少的课时。假设数据库中只有一个记录的时候，使用前面的方法会得到什么结果，为什么？ select min(hour)from courses（9） 创建一个学生表S（NO， SID， SNAME），教师表T（NO， TID， TNAME）作为实验用的表。其中NO分别是这两个表的主键，其他键允许为空。向S插入元组（1， 0129871001， 王小小）、（2， 0129871002， 李四）、（3， 0129871005， NULL）、（4， 0129871004， 关五）；向T插入元组1， 1001