实验二 数据查询 空值空集处理.docx
《实验二 数据查询 空值空集处理.docx》由会员分享，可在线阅读，更多相关《实验二 数据查询 空值空集处理.docx（22页珍藏版）》请在冰豆网上搜索。
实验二数据查询空值空集处理
实验二
实验2.1数据查询
1）要求
以School数据库为例，在该数据库中存在四张表格，分别为：
●表STUDENT（sid,sname,email,grade）;
●表TEACHERS（tid,tname,email,salary）;
●表COURSES（cid,cname,hour）;
●表CHOICES（no,sid,tid,cid,score）
在数据库中，存在这样的关系：
学生可以选择课程，一个课程对应一个教师。
在表CHOICES中保存学生的选课记录。
按以下要求对数据库进行查询操作：
（1）查询年级为2001的所有学生的姓名和邮箱，并按编号降序排列。
selectsname,emailfromSTUDENTS
wheregrade='2001'
orderbysiddesc;
（2）查询学生的选课成绩合格的课程成绩和任课教师名字，并把成绩换算为积点（60分对应积点为1，每增加1分，积点增加0.1）。
createviewGpoint（sname,cname,score,tname）
as
selectsname,cname,score,tname
fromSTUDENTS,TEACHERS,COURSES,CHOICES
whereSTUDENTS.sid=CHOICES.sid
andCHOICES.cid=COURSES.cid
andscore>=60
andCHOICES.tid=TEACHERS.tid;、
selectsname,1+0.1*（score-60）
fromGpoint;
（3）查询课时介于48和64之间（包括48和64）的课程的名称。
selectcnamefromCOURSES
wherehour>=48andhour<=64;
（4）查询所有课程名称中以d打头且以e结尾的课程编号和名称。
selectcid,cnamefromCOURSES
wherecnamelike'd%e';
（5）查询所有选课记录的教师名字（不重复显示）。
selectdistincttnamefromTEACHERS,CHOICES
whereCHOICES.tid=TEACHERS.tid
andCHOICES.no>0;
（6）找出工资最高的教师名字。
selecttname,salaryfromTEACHERS
wheresalary=ANY（
selectmax（salary）
fromTEACHERS
）
（7）查询所有教师的编号及选修其课程的学生的平均成绩，要求只列出平均成绩及格的结果，且按平均成绩降序排列。
createviewaverage（tid,agrade）
as
selectTEACHERS.tid,AVG（CHOICES.score）
fromTEACHERS,CHOICES
whereTEACHERS.tid=CHOICES.tid
groupbyTEACHERS.tid
havingAVG（CHOICES.score）>=60;
select*fromaverage
orderbyagradedesc;
（8）统计各个学生的学生编号、选课数量和所选课程的平均成绩。
createviewSTAT（sid,coun,ag）
as
selectSTUDENTS.sid,COUNT（CHOICES.cid）,AVG（CHOICES.score）
fromSTUDENTS,CHOICES
whereSTUDENTS.sid=CHOICES.sid
groupbySTUDENTS.sid
select*fromSTAT;
（9）查询至少选修了四门课程的学生编号和学生姓名。
createviewxuanxiu（sid,sname）
as
selectCHOICES.sid,STUDENTS.sname
fromSTUDENTS,CHOICES
whereSTUDENTS.sid=CHOICES.sidand
STUDENTS.sidin（selectsidfromCHOICES
groupbysid
havingCOUNT（*）>=4
）
select*fromxuanxiu
（10）查询编号800014004的学生所选的全部课程的课程名、任课教师名和成绩。
createviewstudent（cname,tname,score）
as
selectcname,tname,score
fromCOURSES,TEACHERS,CHOICES
whereCHOICES.sid='800014004'
andCHOICES.cid=courses.cid
andCHOICES.tid=TEACHERS.tid;
select*fromstudent
（11）求出选择了同一个课程的学生数，以课程名称、学生数方式显示。
createviewsa（cname,coun）
as
selectCOURSES.cname,COUNT（CHOICES.sid）
fromCOURSES,CHOICES
whereCOURSES.cid=CHOICES.cid
groupbyCOURSES.cname;
select*fromsa
（12）查询选修了编号894037661的学生所选的某个课程的学生名字。
selectsname
fromstudents,choices
wherecidin
（selectcid
fromchoices,students
wherestudents.sid=choices.sidandchoices.sid='894037661'）
andchoices.sid<>'894037661'
（13）求出至少被两名学生且至多被6000名学生选修的课程编号。
selectcid
fromCHOICES
groupbycid
havingCOUNT（distinctsid）between2and6000;
（14）（不用集合运算）查询所有同时选修了database和c++的学生的编号。
（15）查询学生的基本信息及选修课程编号和成绩。
selectSTUDENTS.sid,sname,email,grade,cid,score
fromSTUDENTS,CHOICES
whereSTUDENTS.sid=CHOICES.sid
（16）选出被选修，但只有课程编号没有课程名称的课程。
（不重复）
selectdistinctcid
fromCHOICES
（17）查询与学号850955252的学生同年级的所有学生资料。
select*fromSTUDENTS
wheregrade=（
selectgradefromSTUDENTS
wheresid='850955252'
）
（18）查询没有选修10005课程的学生信息。
select*
fromstudents,choices
wherestudents.sid=choices.sid
andchoices.sidnotin（selectsidfromchoiceswherecid='1005'）
（19）找出和课程java或课程c++的课时一样课程信息。
select*fromCOURSES
wherehourin（
selecthourfromCOURSES
wherecname='java'orcname='c++'
）
（20）利用集合运算，查询选修课程C++或选修课程Java的学生的编号。
selectsidfromCHOICES
wheresidin（
selectsidfromCOURSES
wherecname='java'
）
union
selectsidfromCHOICES
wheresidin（
selectsidfromCOURSES
wherecname='c++'
）
（21）实现集合交运算，查询既选修课程C++又选修课程database的学生的编号。
selectsidfromCHOICES
wheresidin（
selectsidfromCOURSESwherecname='c++'）
intersect
selectsidfromCHOICES
wheresidin（
selectsidfromCOURSESwherecname='database'
）
（22）实现集合减运算，查询选修课程C++而没有选修课程database的学生的编号。
SelectdistinctsidFromCHOICES,COURSES
WhereCOURSES.cid=CHOICES.cidAndCOURSES.cname='c++'
Andsidnotin（
Selectdistinctsid
FromCHOICES,COURSES
WhereCOURSES.cid=CHOICES.cid
andCOURSES.cname='database'
）
（23）选出至少有一门课成绩不合格的学生所选全部课程的平均成绩。
提示：
蓝色为选作题目！
实验2.2空值和空集的处理
1）要求
（1）查询所有选课记录的成绩并将它换算为五分制（满分5分，合格3分），注意SCORE取NULL值的情况。
selectsid,tid,cid,score/20Score
fromchoices
（2）通过查询选修编号10028的课程的学生的人数，其中成绩合格的学生人数，不合格的学生人数，讨论NULL值的特殊含义。
selectcount（sid）'Count_Stu'fromCHOICES
wherecid='10028'
union
selectcount（sid）'Count_StuPass'fromCHOICES
wherecid='10028'andscore>=60
union
selectcount（sid）'Count_StuUnPass'fromCHOICES
wherecid='10028'andscore<60
selectcount（sid）'Count_Stu'
fromCHOICES
wherecid='10028'
selectcount（sid）'Count_StuPass'
fromCHOICES
wherecid='10028'andscore>=60
selectcount（sid）'Count_StuUnPass'
fromCHOICES
wherecid='10028'andscore<60
（3）通过实验检验在使用ORDERBY进行排序时，取NULL的项是否出现在结果中？
如果有，在什么位置？
select*fromCHOICES
orderbyscore
（4）在上面的查询过程中如果加上保留字DISTINCT会有什么效果？
selectdistinct*fromCHOICES
orderbyscore
无变化
（5）通过实验说明使用分组GROUPBY对取值为NULL的项的处理。
selectscore
fromCHOICES
groupbyscore
（6）结合分组，使用集合函数求每个同学的平均分、总的选课记录、最高成绩、最低成绩和总成绩。
selectavg（score）aver_score,count（*）total_num,max（score）max_score,
min（score）min_score,sum（score）sum_score
fromCHOICES
（7）查询成绩小于60的选课记录，统计总数、平均分、最大值和最小值。
selectavg（score）aver_score,count（*）total_num,max（score）max_score,
min（score）min_score,sum（score）sum_score
fromCHOICES
wherescore<0
（8）采用嵌套查询的方式，利用比较运算符和谓词ALL的结合来查询表COURSES中最少的课时。
假设数据库中只有一个记录的时候，使用前面的方法会得到什么结果，为什么？
selectmin（hour）
fromCOURSES
（9）创建一个学生表S（NO，SID，SNAME），教师表T（NO，TID，TNAME）作为实验用的表。
其中NO分别是这两个表的主键，其他键允许为空。
向S插入元组（1，0129871001，王小小）、（2，0129871002，李四）、（3，0129871005，NULL）、（4，0129871004，关五）；
向T插入元组1，100189，王小小）、（2，100180，李子）、（3，100121，NULL）、（4，100128，NULL）。
对这两个表作对姓名的等值连接运算，找出既是老师又是学生的人员的学生编号和老师编号。
createtableS（
nochar（5）primarykey,
sidchar（10）,
snamenchar（10）
）
createtableT（
nochar（5）primarykey,
tidchar（10）,
tnamenchar（10）
）
insertintoS（no,sid,sname）
values（'1','0129871001','王小小'）,（'2','0129871002','李四'）,（'3','0129871005',''）,
（'4','0129871004','王五'）
insertintoT（no,tid,tname）
values（'1','100189','王小小'）,（'2','100180','李子）,（'3','100121',''）,
（'4','100128',''）
createviews_t（sno,tno）
as
selectS.no,T.no
fromS,T
wheresname=tname
andsname!
=''
andtname!
='';
select*froms_t2;
升级会员 