参考链接
https://blog.csdn.net/GoOnDrift/article/details/19212167
查询关键字，in，exist，group by，order by， having。
create table student( Sno int not null primary key, Sname char(10)not null, Ssex bit not null, Sage tinyint not null, Sdept char(20) not null) create table course( Cno int not null primary key, Cname char(20)not null, Cpno int not null, Ccredit tinyint not null) create table sc( Sno int not null, Cno int not null, Grade tinyint not null foreign key(Sno)references student(Sno) foreign key(Cno)references course(Cno) ) create database stu use stu create table S ( sno char(6), sname char(10), age int, sex char(2), constraint PK_S primary key (sno), constraint CK_age check(age>=0 and age<=150) ) create table C ( cno char(8), cname char(16), credit int, constraint PK_C primary key (cno), constraint CK_credit check (credit>=0) ) create table SC ( sno char(6), cno char (8), constraint PK_SC primary key (sno,cno), constraint FK_s foreign key (sno) references S(sno), constraint FK_c foreign key (cno) references C(cno) ) insert into S values ('001','zhang',19,'男') insert into S values('002','li',16,'女') select * from S (1) seleCt top 1 S.sno,sname from SC,S where Cno='C2' and SC.sno=S.sno order by grade desC; (2) seleCt sname,age from Student,SC where SC.sno not in( seleCt SC.sno from SC where Cno='C2' )and SC.sno=S.sno; (3) seleCt sno, avg(grade) as average from SC group by sno having(avg(grade)>80); (3)方法二 seleCt sno, avg(grade) ' average' from SC group by sno having(avg(grade)>80); (4) delete from SC where SC.sno in( seleCt sno from S where sname='S5'); (5) seleCt sname from S where sdept='英语'and sex='男'; (6) seleCt SC.sno,avg(grade) as average from S,SC where S.sno=SC.sno group by SC.sno; (7) select S.sname as 姓名 ,grade as 成绩 ,C.cname as 选修课程 from SC,S,C where S.sno=SC.sno and SC.cno=C.cno and SC.cno in( seleCt cno from C where cname='DB'); (8) select TOP 1 sno as 学号,grade as 分数,cname as 课程名 from SC,C where SC.cno=C.cno and cname='OS' order by grade desc; (9) select Sname from S where not exists( select * from SC where Sno=S.Sno and Cno=1); （10） select Sname from S where not exists( select * from C where not exists( select * from SC where Sno=S.Sno and Cno=C.Cno)); (11) select distinct Sno from SC,SCX where not exists( select * from SC SCY where SCY.Sno=95001 and not exists( select * from SC SCZ where SCZ.Sno=SCX.Sno and SCZ.Cno=SCY.Cno)); (12) select top 3 Cno as 课程号, Sno from SC where Cno=1 order by Grade desc;
