山东大学《数据库系统》实验二：检索查询

实验题目：检索查询

实验目的：

能够掌握并熟练运用检索查询语句

实验原理和方法：

将查询语句创建成视图:如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,NAME
   FROM pub.student
   WHERE 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,NAME
   FROM 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,NAME
    FROM 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 cid
				    from 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 ])