踩坑记录

（一）SQL相关子查询及增、删、改功能

1．查询选了1号课且选了2号课的学生的班号、学号

Select sclass,sno from sc where cno=1 and (sclass,sno) in(select sclass,sno from sc where cno=2);

2．查询选了1号课但不选2号课的学生的班号、学号

Select sclass,sno from sc where cno=1 and (sclass,sno) not in(select sclass,sno from sc where cno=2);

3．查询1班平均分在85分以上的同学班号、学号、姓名、性别、系、各科课程号及成绩

Select s.sclass,s.sno,sname,ssex,sage,sdept,sc.cno,grade from s left join sc on(s.sclass=sc.sclass and s.sno=sc.sno) where (s.sclass,s.sno) in (select sclass,sno from sc where sclass=1 group by sclass,sno having avg(grade)>85);

4．查询至少选了1班2号同学所选课的所有班号、学号及同学姓名

Select distinct scx.sclass,scx.sno,sname from sc scx left join s on(s.sclass=scx.sclass and s.sno=scx.sno) where not exists (select * from sc scy where scy.sno=2 and scy.sclass=1 and not exists (select * from sc scz where scz.cno=scy.cno and scz.sclass =scx.sclass and scz.sno=scx.sno));

5．查询不选1号课的学生班号及学号

Select distinct sclass,sno from sc where (sclass,sno) not in (select sclass,sno from sc where cno=1);

6．查询选2号课的学生名字及相应2号课成绩，按成绩从高到低排序

Select sname,grade from s,sc where s.sclass=sc.sclass and s.sno=sc.sno and cno=2 order by grade desc;

7．统计学生选修课程的班号、学号及总学分

Select sclass ,sno,sum(ccredit) from sc left join c on (sc.cno=c.cno)  group by sclass,sno;

8．统计1班选修3号课的班号及平均分

Select sclass,avg(grade) from sc where sclass=1 and cno=3 group by sclass; 

9．把个人信息及选课信息插入到Student和SC 表及新增加一门“无机化学”课程信息

Insert into s values(3,1,'孙晨','男',20,'MA');
Insert into sc values(3,1,8,88);
Insert into c values(8,'无机化学',null,3);

Select * from s;
Select * from sc;
Select * from c;

10. 删除选修3号课的所有选课信息并显示删除后的结果

Delete from sc where cno=3;
Select * from sc;

11. 把选修1号课的所有男同学年龄增加1岁并显示最终学生Student信息

Update  s set sage=sage+1 where ssex='男' and (sno,sclass) in (select sno,sclass from sc where cno=1);
Select * from s;

12. 把每个选课人的学号、班号及平均成绩插入到一个新表中。

Create table sg(sno int ,sclass int ,avg_grade smallint);
Insert into sg 
Select sno,sclass,avg(grade) from sc group by sno,sclass;
Select * from sg; 

（二）视图SQL语言功能

1 使用企业管理器创建视图：在ST库中以“student”表为基础，建立信息系学生的视图V_IS_Student

Create view v_is_student as select * from s where sdept=’IS’;

2 使用SQL语句创建视图：

①建立一个每个学生的学号、班号、姓名、选修的课名及成绩的视图 S_C_GRADE；

Create view s_c_grade(sno,sclass,sname,cname,grade) as select s.sno,s.sclass,sname,cname,grade from s left join sc on(s.sno=sc.sno and s.sclass=sc.sclass) left join c on (sc.cno=c.cno);

②建立信息系选修了1号课程且成绩在90分以上的学生的视 图V_IS_Score

Create view v_is_score(sno,sname,ssex,sage,sdept,grade) 
As 
Select s.sno,sname,ssex,sage,sdept,grade
From s left join sc on(s.sclass=sc.sclass and s.sno=sc.sno)
Where (s.sno,s.sclass) in 
(Select sno,sclass from sc 
Where cno=1 and sdept='IS'
Group by sno,sclass,grade having grade>90);

③ 将各系学生人数，平均年龄定义为视图V_NUM_AVG。

Create view v_num_avg(snumber,avg_age) as 
Select count(*),avg(sage) from s group by sdept;

3 查询以上所建的视图结果

Select * from V_IS_Student;

Select * from S_C_GRADE；

Select * from v_is_score;

Select * from b_num_avg;

4 查询选修了1号课程的信息系学生

Select distinct s.sclass,s.sno from s left join sc on(s.sclass=sc.sclass and s.sno=sc.sno) where (s.sclass,s.sno) in (select sclass,sno from sc where cno=1) and sdept='IS';

5 在信息系学生的视图中找出年龄小于20岁的学生

Select * from V_IS_Student where sage<20 and ssex='男';

6 将信息系学生视图V_IS_Student中学号一班2号的学生姓名改为“刘辰”

Update V_IS_Student set sname='刘晨' where sclass=1 and sno=2;

7 用SQL语句删除视图S_C_GRADE

drop view s_c_grade