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

我会带着你远行 2023-01-05 05:07 88阅读 0赞

## 实验题目：检索查询 ##

## 实验目的： ##

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

## 实验原理和方法： ##

将查询语句创建成视图:如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 \])