山东大学《数据库系统》实验二：检索查询"/>
山东大学《数据库系统》实验二：检索查询
实验题目：检索查询
实验目的：
能够掌握并熟练运用检索查询语句
实验原理和方法：
将查询语句创建成视图:如test2_01，test2 代表实验二，01 代表题目1:
Create or replace view test2_01 as select……
根据题目要求写出答案查询语句，将查询结果创建到一个视图中，点击题目 交卷验证结果是否正确。
提示：
1、任何select 确保只返回一个结果可以是另外一个select的一个输出表达式。格式如: select sid,(select… …) 列别名 from … where …
2、任何select 确保只返回一个结果可以出现在另外一个sql的条件表达式中。格式如: select … from … where xx=（select… …）。
3、任何select 可以是另外一个sql的表，即派生表。格式如: select … from student,（select…）表别名 where …。
实验步骤：
1． 找出没有选修任何课程的学生的学号、姓名(即没有选课记录的学生）。自己认为查询语句正确后，通过下面语句将查询语句创建成视图test2_01
Create view test2_01 as SELECT SID,NAMEFROM pub.studentWHERE SID NOT IN (SELECT a.SID FROM pub.student_course a,pub.student b WHERE a.SID=b.SID)
2． 找出至少选修了学号为"200900130417"的学生所选修的一门课的学生的学号、姓名(不包含这名同学)。
Create view test2_02 as SELECT UNIQUE SID,NAMEFROM pub.student WHERE SID IN (SELECT a.SID FROM pub.student_course a,pub.student_course b WHERE a.CID=b.CID AND b.SID='200900130417'AND a.sid<>'200900130417')
3．找出至少选修了一门其先行课程号为"300002"号课程的学生的学号、姓名。
Create view test2_03 as SELECT UNIQUE SID,NAMEFROM pub.student WHERE SID IN (SELECT SID FROM pub.student_course a,pub.course b WHERE a.CID=b.CID AND b.FCID=300002)
4．找出选修了"操作系统"并且也选修了"数据结构"的学生的学号、姓名。
Create view test2_04 as
(select sid,name from pub.student_course natural join pub.student where cid in (select cid from pub.course where name='操作系统')
)
intersect
(select sid ,name from pub.student_course natural join pub.student where cid in (select cidfrom pub.course where name='数据结构')
)
5．查询20岁的所有有选课的学生的学号、姓名、平均成绩(avg_score，此为列名，下同)（平均成绩四舍五入到个位）、总成绩(sum_score)
Create or replace view test2_05 as
select SID,NAME,round(avg(SCORE),0) avg_score,sum(SCORE) sum_score from pub.student natural join pub.student_course where age=20 group by sid,name
6.查询所有课的最高成绩、最高成绩人数，test2_06有四个列：课程号cid、课程名称name、最高成绩max_score、最高成绩人数max_score_count（一个学生同一门课成绩都是第一，只计一次）。如果没有学生选课，则最高成绩为空值,最高成绩人数为零。
Create or replace view test2_06 as
select cid,name,ms max_score,count(distinct sid) max_score_count from pub.course natural join pub.student_course a natural join (select cid,max(score) ms from pub.student_course group by cid) where a.score=ms group by cid,name,ms
7.查询所有不姓张、不姓李、也不姓王的学生的学号sid、姓名name
Create view test2_07 as
select sid,name from pub.student where name not like'张%' and name not like'李%' and name not like'王%'
8．查询学生表中每一个姓氏及其人数（不考虑复姓），test2_08有两个列：second_name、p_count
Create view test2_08 as
select substr(name,1,1) second_name,count(SID) p_count from pub.student group by substr(name,1,1)
9．查询选修了300003号课程的学生的sid、name、score
Create or replace view test2_09 as
SELECT sid,name,score FROM pub.student_course natural join pub.student WHERE cid=300003
10．找出同一个同学同一门课程有两次或以上不及格的所有学生的学号、姓名（即一门课程需要补考两次或以上的学生的学号、姓名）。
Create or replace view test2_10 as
select sid,name from (select sid,count(score) cs from pub.student_course where score < 60 group by sid ,cid) natural join pub.studentwhere cs>1
结论分析与体会：
利用折行和缩进会使sql语句结构更加清晰明了，完成实验之前需要仔细阅读题目的要求（如，查询2:不包含这名同学），并且在查询过程中一定要考虑特殊情况，阅读提示（如，查询6:存在一个学生同一门课成绩都是第一，只计一次）。
就实验过程中遇到和出现的问题，你是如何解决和处理的，自拟1－3道问答题：
Q1:查询5：如何平均成绩四舍五入到个位？
A1:上网查询，利用ROUND(number[,decimals])
其中：number 待做截取处理的数值，decimals 指明需保留小数点后面的位数。可选项，忽略则截去所有的小数部分，并四舍五入。
Q2:查询6：提交结果验证返回一行数据错误，如何修正？
A2:存在最高分者再次选修刷到同分的情况，导致最高成绩人数多一人。在实际操作中也要考虑到类似的特殊情况。
Q3:查询8 ：如何查询学生表中每一个姓氏？
A3:查询oracle中截取字符的方法,即substr(string, start_position, [ length ])
更多推荐
山东大学《数据库系统》实验二：检索查询
发布评论