SQL练习

# 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