一道SQL面试题~

设有一个关系数据库，有三个基本表，表结构如下：

1）student

学号stu_num 姓名name 年龄age 性别gender 系号department_id

2) SC

学号stu_num 课程号curr_id 成绩grade

3) Coutse

课程号curr_id 课程名curr_name 学时数class_hour

#建立表并插入数据

create table student (stu_num varchar2(10),name varchar2(10),age int,gender char(4),department_id int); 
create table sc (stu_num varchar2(10),curr_id int,grade int); 
create table course (curr_id int,curr_name varchar2(10),class_hour int); 
#================================================ 
insert into student values ('A1','zs',21,'M',01); 
insert into student values ('A2','wlm',20,'F',01); 
insert into student values ('A3','ls',22,'M',06); 
insert into student values ('B1','ww',21,'M',04); 
insert into student values ('C1','ll',25,'F',06); 
insert into student values ('S1','vv',20,'F',03); 
insert into student values ('S2','yb',21,'M',06); 
insert into student values ('S3','nm',22,'M',04); 
#================================================ 
insert into sc values ('A1',01,65); 
insert into sc values ('A2',02,85); 
insert into sc values ('A3',01,95); 
insert into sc values ('B1',02,65); 
insert into sc values ('C1',01,55); 
insert into sc values ('S1',02,91); 
insert into sc values ('S2',01,91); 
insert into sc values ('S3',03,89); 
  
insert into sc values ('A1',02,77); 
insert into sc values ('A2',01,59); 
insert into sc values ('S1',01,31); 
insert into sc values ('S2',02,96); 
insert into sc values ('S3',01,69); 
  
insert into sc values ('A1',03,91); 
insert into sc values ('A2',03,33); 
  
insert into sc values ('A1',04,73); 
  
insert into sc values ('C1',02,75); 
insert into sc values ('C1',03,91); 
#================================================ 
insert into course values (01,'math',20); 
insert into course values (02,'English',24); 
insert into course values (03,'History',10); 
insert into course values (04,'political',14); 

1.请用SQL语言检索系号为'06'的学生的学号、姓名、课程号和成绩

2.请用SQL语言检索比学号为'S1'的学生大三岁的学生的学号、姓名和年龄

3.请用SQL语言创建一个视图ST_VIEW，检索选修课程在10(笔者没有这么多条目，先用3条代替了~)门以上的学生的系号、学号、姓名、最低分、最高分、平均分和选课门数，其结果要求按照系号，平均分降序排列。

4.请用SQL语言检索至少选修了'学号为S3的学生选修的全部课程'的学生的学号和姓名。

 

第一题答案

select t1.stu_num,t1.name,t2.curr_id,t2.grade 
from student t1,sc t2 
where t1.stu_num = t2.stu_num 
and 
t1.department_id = 06; 

第二题答案

select stu_num,name,age from student where 
age > ( select age + 3 from student where stu_num = 'S1');   

第三题答案

create view st_view as 
with t1 as ( 
select * from ( 
select stu_num,count(curr_id) as sum_count,min(grade) as min_grade,max(grade) as max_grade,avg(grade) as avg_grade 
from sc group by stu_num ) 
where sum_count >= 3 ) 
select t2.department_id,t2.stu_num,t2.name,t1.min_grade,t1.max_grade,t1.avg_grade,t1.sum_count
from t1,student t2 
where t1.stu_num = t2.stu_num 
order by 1 desc,6 desc;  

第四题答案

with t1 as ( 
select stu_num,count(curr_id) as count_curr 
from sc 
where curr_id in  
(select curr_id from sc where stu_num = 'S3') 
group by stu_num 
)select t1.stu_num,t2.name 
from t1,student t2 
where t1.stu_num=t2.stu_num 
and 
count_curr=(select count_curr from t1 where stu_num='S3');  

或者

 

with t1 as ( 
select stu_num,count(curr_id) from sc where curr_id in 
 (select curr_id from sc where stu_num = 'S3') 
 group by stu_num 
 having count(curr_id)=(select count(curr_id) from sc where stu_num = 'S3')) 
select t1.stu_num,t2.name 
 from t1,student t2 where t1.stu_num=t2.stu_num; 

 

也许像下面这个结构看起来会更明白一些？

 

select stu_num,name from student where stu_num in ( 
select stu_num from sc where curr_id in 
 (select curr_id from sc where stu_num = 'S3') 
 group by stu_num 
 having count(curr_id)=(select count(curr_id) from sc where stu_num = 'S3'));