基础提升呀

15
select Sname,Sdept,Sage from Student where Sage between 20 and 23;
16
select Sname,Sdept,Sage from Student where Sage not between 20 and 23;
17
select Sname,Ssex from Student where Sdept in ('信息系','数学系','计算机系');
18
select Sname,Ssex from Student where Sdept not in ('信息系','数学系','计算机系');
19
select * from Student where Sname like '张%';
20
select * from Student where Sname like '张%' or Sname like '李%' or Sname like '刘%';
21
select Sname,Sno from Student where Sname like '_[小大]%';
22
select Sname from Student where Sname not like '刘%' ;
23
select * from Student where Sno not like '%[235]' ;
24
select Sno,Cno from SC where Grade is null;
25
select Sno,Cno from SC where Grade is not null;
26
select Sname from Student where Sdept='计算机系' and Sage<20;
27
select * from Student order by Sage asc;
28
select Sno,Grade from SC where Cno='C02' order by Grade desc;
29
select * from Student order by Sdept,Sage desc;
39
select * from Student left outer join SC on Student.Sno=SC.Sno;
41
select Sname,Cno,Grade from Student,SC where Student.Sno=Sc.Sno and Sdept='计算机系';
42
select Sname,Cname,Grade from Student,SC,Course where Student.Sno=SC.Sno and SC.Cno=Course.Cno and sdept='信息系' and Cname='VB';
43
select Sname,Sdept from Student join SC on Student.Sno=SC.Sno join Course on Course.Cno=SC.Cno where Cname='VB';
44
select Sname,Sdept from Student where Sdept=(select Sdept from Student where Sname='刘晨');
45
select * from Student left join SC on Student.Sno=SC.Sno;
46

47
select Sno,Sname from Student where Sno in (select Sno from SC where Grade>90);
48
select Sno,Sname from Student where Sno in (select Sno from SC where Cno in (select Cno from Course where Cname='数据库基础'));
49
select Sno,Grade from SC where Cno='C02' and Grade>(select avg(Grade) from SC where Cno='C02');
50
select Sname from Student where Sno in (select Sno from SC where Cno='C01');
51


53
select Student.Sno,Sname from Student join SC on Student.Sno=SC.Sno where Sdept='数学系' and Grade>80;
54
select Sname from Student join SC on Student.Sno=SC.Sno where Sdept='计算机系' and Grade=(select max(Grade) from Student join SC on Student.Sno=SC.Sno Group by Sdept having Sdept='计算机系');




select count(*) from Student;
select count(distinct Sno) from SC where XKLB='选修';
select sum(Grade) from SC where Sno='9512101';
select avg(Grade) from SC where Cno='C01';
select max(Grade),min(Grade) from SC where Cno='C01';
select Cno,count(Sno) as '选课人数' from SC group by Cno;
select Sno,count(Cno) as '选课门数',avg(Grade) as '平均成绩' from SC group by Sno;
select Sno from SC group by Sno having count(Cno)>3;
select Sno,avg(Grade) as '平均成绩',count(*) as '选课门数' from SC group by Sno having count(*)>=4;
select * from Student left outer join SC on Student.Sno=SC.Sno;

select Sno,Grade from SC where Cno='C02' and Grade>(select avg(Grade) from SC where Cno='C02');