数据库学习之旅——实验2

本次实验的目的是对SQL语句中比较基础的部分，SELECT语句中的group by，order by，having，以及集合运算，聚集函数的学习与练习

由于无法上传附件SCHOOL数据库的信息，所以，这里只提交了数据库的表头，以及练习部分：

/*STUDENTS(SID,SNAME,EMAIL,GRADE);TEACHERS(TID,TNAME,EMAIL,SALARY);COURSES(CID,CNAME,HOUR);CHOICES(NO,SID,TID,CID,SCORE);*/--(1)查询年级为2001的所有学生的名称，按编号升序排列。SELECT SNAME '学生名称'FROM STUDENTSWHERE GRADE='2001'ORDER BY SID; --(2)查询学生的选课成绩合格的课程成绩，并把成绩换算为积点（60分对应积点为1，每增加1分，积点增加0.1）SELECT SID '学生编号' , SCORE '学生成绩', (SCORE-50)/10 '积点'FROM CHOICESWHERE SCORE>'60';--(3)查询课时是48或64的课程的名称SELECT CNAME '课程名称'FROM COURSESWHERE HOUR IN('48','64');--(4)查询所有课程名称中含有DATA的课程编号SELECT CID '课程编号'FROM COURSESWHERE CNAME LIKE '%DATA%';--(5)查询所有选课记录的课程号（不重复显示）SELECT DISTINCT CID '课程号'FROM CHOICES;--(6)统计所有老师的平均工资SELECT AVG(SALARY) '平均工资'FROM TEACHERS;--*********************************(7)查询所有学生的编号，姓名和平均成绩，按总平均成绩降序排列SELECT STUDENTS.SID '学生编号' , SNAME '学生姓名' , AVG(SCORE) '平均成绩'FROM CHOICES,STUDENTSWHERE STUDENTS.SID=CHOICES.SIDGROUP BY STUDENTS.SID,STUDENTS.SNAMEORDER BY AVG(SCORE)DESC;--(8)统计各个课程的选课人数和平均成绩SELECT CID '课程编号' , COUNT(NO)'选课人数' , AVG(SCORE)'平均成绩'FROM CHOICESGROUP BY CID;--ORDER BY CID--(9)查询至少选修了三门课程的学生的编号SELECT SID '编号'FROM CHOICESGROUP BY SIDHAVING COUNT(*)>3--(10)查询编号800009026的学生所选的全部课程的课程名和成绩SELECT COURSES.CNAME,CHOICES.SCOREFROM CHOICES,COURSESWHERE CHOICES.SID='800009026' AND COURSES.CID=CHOICES.CID;--(11)查询所有选了database的学生的编号SELECT CHOICES.SID '学生编号'FROM CHOICES,COURSESWHERE COURSES.CNAME='database' AND CHOICES.CID=CHOICES.CID--(12)求出选择了同一个课程的学生对SELECT X.SID '学生1', Y.SID'学生2'FROM CHOICES X,CHOICES YWHERE X.CID=Y.CID AND X.NO<Y.NO--(13)求出至少被两名学生选修的课程编号SELECT CHOICES.CIDFROM CHOICESGROUP BY CHOICES.SID,CHOICES.CID HAVING COUNT(SID)>2--(14)查询选修了编号850955252的学生所选的某个课程的学生编号SELECT Y.SIDFROM CHOICES AS X, CHOICES AS YWHERE X.CID=Y.CID AND X.SID='850955252'--(15)查询学生的基本信息及选修课程编号和成绩SELECT STUDENTS.SID,SNAME,STUDENTS.EMAIL,GRADE,CHOICES.CID,SCOREFROM STUDENTS,CHOICESWHERE STUDENTS.SID=CHOICES.SID--(16)查询学号850955252的学生的姓名和选修的课程名称及成绩SELECT STUDENTS.SNAME,COURSES.CNAME,CHOICES.SCOREFROM STUDENTS,COURSES,CHOICESWHERE STUDENTS.SID=CHOICES.SID AND COURSES.CID=CHOICES.CID AND STUDENTS.SID='850955252'--(17)查询学号850955252的学生同年级的所有学生资料SELECT *FROM STUDENTSWHERE GRADE IN(SELECT STUDENTS.GRADEFROM STUDENTSWHERE STUDENTS.SID='850955252')--(18)查询所有的有选课的学生的详细信息SELECT *FROM STUDENTSWHERE SID IN(SELECT SIDFROM CHOICES)/*SELECT *FROM STUDENTSWHERE SID IN(SELECT CHOICES.SIDFROM CHOICES,STUDENTSWHERE STUDENTS.SID=CHOICES.SID)*/--(19)查询没有学生选的课程的编号SELECT CIDFROM CHOICESWHERE SID NOT IN(SELECT SIDFROM CHOICES)/*SELECT CIDFROM CHOICESWHERE CID NOT IN(SELECT CIDFROM CHOICES)这两个一样吗？*/--(20)查询选修了课程名为C++的学生学号和姓名SELECT STUDENTS.SID,STUDENTS.SNAME,COURSES.CNAMEFROM STUDENTS,COURSES,CHOICESWHERE CHOICES.CID=COURSES.CID AND COURSES.CNAME='C++'/*SELECT SID,SNAMEFROM STUDENTSWHERE SID IN(SELECT SIDFROM CHOICESWHERE CID IN(SELECT CIDFROM COURSESWHERE CNAME='C++'))问题：1.查询结果是否相同？ 2.如果结果相同，查询时间为什么不同？*/--**********************************(21)找出选修课程成绩最差的选课记录SELECT *FROM CHOICESWHERE CHOICES.SCORE >= ALL(SELECT SCOREFROM CHOICESWHERE SCORE IS NOT NULL)--(22)找出和课程UML或课程C++的课时一样的课程名称SELECT CNAMEFROM COURSESWHERE HOUR IN(SELECT HOURFROM COURSESWHERE CNAME='UML' OR CNAME='C++')/*SELECT CNAMEFROM COURSESWHERE HOUR =SOME(SELECT HOURFROM COURSESWHERE CNAME='UML' OR CNAME='C++')问题：IN 和 =SOME 的区别在哪里？*/--(23)查询所有选修编号10001的课程的学生的姓名SELECT DISTINCT SNAMEFROM STUDENTS,CHOICESWHERE STUDENTS.SID=CHOICES.SID AND CID='10001'/*SELECT SNAMEFROM STUDENTSWHERE EXISTS(SELECT *FROM CHOICES XWHERE X.CID='10001' AND X.SID=STUDENTS.SID)问题：区别在哪里？*/--**********************************(24)查询选修了所有课程的学生姓名SELECT SNAMEFROM STUDENTSWHERE NOT EXISTS(SELECT *FROM COURSES AS XWHERE NOT EXISTS(SELECT *FROM CHOICES AS YWHERE Y.SID=STUDENTS.SID AND Y.CID=X.CID))--(25)利用集合运算，查询选修课程C++或课程Java的学生的编号SELECT CHOICES.SIDFROM CHOICES,COURSESWHERE (COURSES.CNAME='C++' OR COURSES.CNAME='JAVA') AND CHOICES.CID=COURSES.CID--(26)实现集合交运算，查询既选修课程C++又选修课程Java的学生的编号SELECT CHOICES.SIDFROM CHOICES,COURSESWHERE (COURSES.CNAME='C++' AND COURSES.CNAME='JAVA') AND CHOICES.CID=COURSES.CID--(27)实现集合减运算，查询选修课程C++而没有选修课程Java的学生的编号SELECT CHOICES.SIDFROM CHOICES,COURSESWHERE (COURSES.CNAME='C++' AND COURSES.CNAME<>'JAVA') AND CHOICES.CID=COURSES.CID