创建表格 插入数据
create table student (xuehao int, xingming char(20), nianling int, xingbie char(10), xihao int);
insert into student values (1,'zs',20,'m',10);
create table sc (xuehao int, kechenghao int, chengji int);
insert into sc values (1,11,98);
create table course (kechenghao int, kechengming char(50), xueshishu int);
insert into course values (11, 'yuwen',20);
1、编写sql语句，查询系号20的同学的学号，姓名，课程号，成绩
select a.xuehao,a.xingming,b.chengji from student a,sc b where a.xihao=20 and a.xuehao=b.xuehao;
2、编写sql语句，查询比学号是1同学年龄大3岁的同学的学号，姓名，年龄。
select xuehao,xingming,nianling from student where nianling>(select nianling from student where xuehao=1)+3;
3、
创建视图st_view 检索选修课程在3门以上的学生的系号，学号，姓名，最低分，最高分，平均分，选修门数，要求按照系号，平均分降序排列。
select xuehao,count(kechenghao) as sum_kch,max(chengji) as max_cj,min(chengji) as min_cj,round(avg(chengji)) as avg_cj from sc group by xuehao;
Select * from (select xuehao,count(kechenghao) as sum_kch,max(chengji) as max_cj,min(chengji) as min_cj,round(avg(chengji)) as avg_cj from sc group by xuehao) where sum_kch>3;
With t2 as (
Select * from (select xuehao,count(kechenghao) as sum_kch,max(chengji) as max_cj,min(chengji) as min_cj,round(avg(chengji)) as avg_cj from sc group by xuehao) where sum_kch>3
)
Select t1.xihao,t1.xuehao,t2.min_cj,t2.max_cj,t2.avg_cj,t2.sum_kch from student t1,t2
where t1.xuehao=t2.xuehao order by 1,5 desc;
Create view st_view as
With t2 as (
Select * from (select xuehao,count(kechenghao) as sum_kch,max(chengji) as max_cj,min(chengji) as min_cj,round(avg(chengji)) as avg_cj from sc group by xuehao) where sum_kch>3)
Select t1.xihao,t1.xuehao,t2.min_cj,t2.max_cj,t2.avg_cj,t2.sum_kch from student t1,t2
where t1.xuehao=t2.xuehao order by 1,5 desc;
Select * from st_view;
查看视图
4、检索至少选修了‘学号为3的学生选修的全部课程’的学生的学号和姓名。
Select xuehao from sc where kechenghao in (select kechenghao as kch from sc where xuehao=3) group by xuehao
Having count( xuehao) =(select count(kechenghao) from sc where xuehao=3)
And xuehao !=3
With abc as
(
Select xuehao from sc where kechenghao in (select kechenghao as kch from sc where xuehao=3) group by xuehao
Having count( xuehao) =(select count(kechenghao) from sc where xuehao=3)
And xuehao !=3)
Select t1.xuehao,t1.xingming from abc t2,student t1 where t1.xuehao=t2.xuehao order by 1 ;
