# 1、 查询Student表中的所有记录的Sname、Ssex和Class列。 select Sname, Ssex, Class from Student; # 2、 查询教师所有的单位即不重复的Depart列。 select distinct Depart, Tname from Teacher; # 3、查询Student表的所有记录。 select * from Student; # 4、查询Score表中成绩在60到80之间的所有记录 select * from Score where Degree between 60 and 80; # 5、查询Score表中成绩为85,86或88的记录。 select * from Score where Degree in (85, 86, 88); # 6、查询Student表中“95031”班或性别为“女”的同学记录。 select * from Student where Ssex = '女' or Class = '95031'; # 7、以Class降序查询Student表的所有记录。 select * from Student order by Class desc; # 8、以Cno升序、Degree降序查询Score表的所有记录。 select * from Score order by Cno, Degree desc; # --多个条件用 "," 来分隔 # 9、查询“95031”班的学生人数。 select count(*) from Student where Class = '95031'; #10、 查询Score表中的最高分的学生学号和课程号。(子查询或者排序) select Sno, Cno from Score where Degree = (select max(degree) from score) select top 1 Sno, Cno from Score order by Degree desc; #11、查询每门课的平均成绩,要按照课程分组group by ,然后求没门课平均avg select Cno, convert(numeric (18, 2), avg(Degree)) 平均成绩 from Score group by Cno; #12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。 select Cno, avg(Degree) from Score group by Cno having Cno like '3%' and Cno in (select Cno from Score group by Cno having count(Cno) > 2); # Like模糊查询 3%以3开头 having只能跟在group by 后面 #13、查询分数大于70,小于90的Sno列。 select Sno from Score where Degree between 70 and 90; #14、查询所有学生的Sname、Cno和Degree列。 select A.Sno, A.Degree, B.Sname from Score as A inner join Student as B on A.Sno = B.Sno; #15、查询所有学生的Sno、Cname和Degree列。 select A.Sno, A.Degree, C.Cname from Score as A inner join Course as C on A.Cno = C.Cno; #16、查询所有学生的Sname、Cname和Degree列。 select S.Sname, A.Degree, C.Cname from Score as A inner join Course as C on A.Cno = C.Cno inner join Student as S on S.Sno = A.Sno; #17、 查询“95033”班学生的平均分。 select S.Class as 班级, avg(A.Degree) as 评分分 from Score as A inner join Student as S on S.Sno = A.Sno group by S.Class # --现查询所有同学的Sno、Cno和rank列。 # 方法一 select *, (select ranks from grade where low < Degree and Upp >= Degree) as 评级 from Score; #方法二 select A.*, G.ranks from Score as A inner join grade as G on A.Degree between G.low and G.upp; # 19、 查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。 select * from Score where Degree > (select Degree from Score where Sno = '109' and Cno = '3-105') and Cno = '3-105'; #20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。 Select * from score a where degree < (select max(degree) from Score b where b.Cno = a.Cno) and Sno in (select Sno from Score group by Sno having count(*) > 1); # 结合条件一定要写在子查询中,子查询内部设定的关联名称 # 只能在该子查询内部使用 # 也就是说内部可以看到外部 # 而 # 外部看不到内部 # SQL是按照先内层子查询后补外层查询的顺序来执行的 # 这样 # 子查询执行结束后只会留下执行结果. #22、查询和学号为105的同学同年出生的所有学生的Sno、Sname和Sbirthday列。 select * from Score AS A inner join Student AS S on A.Sno = S.Sno where year(S.Sbirthday) = (select year(Sbirthday) from Student where Sno = '105'); #23、查询“张旭“教师任的学生成绩。 select A.* from Teacher as T inner join Course as C on T.Tno = C.Tno inner join Score AS A on A.Cno = C.Cno where T.Tname = '张旭'; #24、查询选修某课程的同学人数多于5人的教师姓名。 select A.Cno, T.Tname, Count(A.Cno) from Teacher as T inner join Course as C on T.Tno = C.Tno inner join Score AS A on A.Cno = C.Cno group by A.Cno, T.Tname; #25、查询95033班和95031班全体学生的记录。 select * from Student where Class = '95033' or Class = '95031'; #26、 查询存在有85分以上成绩的课程Cno. select Cno from Score where Degree > 85 group by Cno; #27、查询出“计算机系“教师所教课程的成绩表。 select A.* from Teacher as T inner join Course as C on T.Tno = C.Tno inner join Score AS A on A.Cno = C.Cno where T.Depart = '计算机系'; #28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。? select Prof, Depart from Teacher as a where Prof not in (select Prof from Teacher as b where b.Depart <> a.Depart); #29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的记录 select * from Score as A inner join Student as S on A.Sno = S.Sno where Cno = '3-105' and Degree > (select Degree from Score as B where B.Sno = A.Sno and B.Cno = '3-245'); #31、 查询所有教师和同学的name、sex和birthday. select Sname, Ssex, convert(date,Sbirthday) from Student union ( select Tname, Tsex, convert(date, Tbrithday) from Teacher); #32、查询所有“女”教师和“女”同学的name、sex和birthday. select Sname as name, Ssexassex, convert(date, Sbirthday) as birthday from Student where Ssex = '女' union (select Tname, Tsex, convert(date, Tbrithday) from Teacher where Tsex = '女'); #33、查询成绩比该课程平均成绩低的同学的成绩表。 select * from Score as a where Degree < (select avg(Degree) from Score as b where b.Cno = a.Cno group by b.Cno); #34、查询所有任课教师的Tname和Depart. select Tname, Depart from Teacher; #35 、查询所有未讲课的教师的Tname和Depart. select * from Teacher where Tno not in (select Tno from Course where Cno in (select Cno from Score)); # --36、查询至少有2名男生的班号。 select Class from Student where Ssex = '男' group by Class having Count(Class) >= 2; #37.查询不姓王的同学信息 select * from Student where Sname not like '王%'; #38、查询Student表中每个学生的姓名和年龄。 select Sname, Ssex from Student; # --39、查询Student表中最大和最小的Sbirthday日期值。 select max(Sbirthday) as 最大日期值, min(Sbirthday) as 最小日期值 from Student; # 40、以班号和年龄从大到小的顺序查询Student表中的全部记录。 select * from Student order by Class, Sbirthday desc; #41、查询“男”教师及其所上的课程。 select T.Tname, C.Cno from Teacher as T inner join Course as C on T.Tno = C.Tno where T.Tsex = '男'; #42、查询最高分同学的Sno、Cno和Degree列。 select * from Score where Degree = (select max(Degree) from Score); #43、查询和“李军”同性别的所有同学的Sname. select Sname from Student where Ssex = (select Ssex from Student where Sname = '李军') and Sname <> '李军'; #44、查询和“李军”同性别并同班的同学Sname. select Sname from Student where Ssex = ( select Ssex from Student where Sname = '李军') and Sname <> '李军' and Class =( select Class from Student where Sname = '李军'); #45、查询所有选修“计算机导论”课程的“男”同学的成绩表。SQL语句 select * from Score where Sno in (select Sno from Student where Ssex = '男') and Cno in (select Cno from Course where Cname = '计算机导论'); # --46、查询 " 3-245 " 课程比 " 3-105 " 课程成绩高的学生的信息及课程分数 select * from Score as A inner join Score as B on A.Sno = B.Sno where A.Cno = '3-245' and B.Cno = '3-105' and A.Degree < B.Degree; # - -48.按各科成绩进行排序,并显示排名, degree 重复时保留名次空缺 select *, rank() over(partition by Cno order by Degree) as 排名 from Score; # --49查询各科成绩前两名的记录 select * from (select *, rank() over(partition by Cno order by Degree) as 排名 from Score) as A where A.排名 <= 2; #50查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况) select S.Sno, A.* from Student as S left join Score as A on S.Sno = A.Sno; #51.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 select *from Score as A inner join Score as B on A.Sno = B.Sno where A.Cno <> B.Cno and A.Degree = B.Degree
SQL练习
最新推荐文章于 2021-07-21 18:52:40 发布