学生表(Student),课程表(Course),教师表(Teacher),以及成绩表(SC)
学生表(Student )有四个字段 sid--学生id,sname--学生姓名,sage--学生年龄,ssex--学生性别
create table Student(sid varchar(10),sname varchar(10),sage datetime,ssex nvarchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
课程表(Course)有三个字段,cid--课程id,cname--课程名,tid--教师id
create table Course(cid varchar(10),cname varchar(10),tid varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
教师表(Teacher)有两个字段,tid--教师id,tname--教师姓名
create table Teacher(tid varchar(10),tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
成绩表(SC)有三个字段,sid--学生id,cid--课程id,score--成绩
create table SC(sid varchar(10),cid varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);
1.查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT * from student
RIGHT JOIN
(SELECT t1.sid,class1,class2
FROM
(SELECT sid,score as class1 FROM sc where sc.CId='01')as t1,
(SELECT sid,score as class2 FROM sc where sc.CId='02')as t2
where t1.sid=t2.sid and t1.class1>t2.class2)r
on student.SId=r.sid
#第二种
SELECT A.*,B.score
FROM (SELECT * FROM sc WHERE CId='01')A
LEFT JOIN (SELECT * FROM sc WHERE CId='02')B
ON A.sid=B.sid
WHERE A.score>B.score;
1.1 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
SELECT *
FROM
(SELECT * FROM sc WHERE sc.CId='01')as t1
LEFT JOIN
(SELECT * from sc WHERE sc.CId='02')as t2
on t1.sid=t2.sid
1.2 查询同时存在01和02课程的情况
SELECT *
FROM
(SELECT * FROM sc WHERE sc.CId='01')as t1,
(SELECT * FROM sc WHERE sc.CId='02')as t2
WHERE t1.sid=t2.sid
1.3 查询选择了02课程但没有01课程的情况
where & not in
SELECT *
from sc
where sc.SId NOT IN
(SELECT SId FROM sc where sc.CId='01')
and sc.CId='02'
2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
group by & join
SELECT s.sid,sname,AVG(sc.score)
FROM
student as s INNER JOIN sc
on s.SId=sc.SId
GROUP BY s.SId
HAVING AVG(sc.score)>60
子查询
SELECT *
FROM
(SELECT sid,AVG(sc.score) FROM sc
GROUP BY SId
HAVING AVG(sc.score)>60)r
LEFT JOIN student as s on s.sid=r.sid
3.查询在 SC 表存在成绩的学生信息
Distinct
SELECT DISTINCT student.*
FROM student,sc
where student.SId=sc.SId
4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和
gruop by & sum & join & count
SELECT s.*,COUNT(sc.cid),SUM(score)
FROM student as s LEFT JOIN sc ON s.sid=sc.SId
GROUP BY s.SId;
5.查询「李」姓老师的数量
SELECT COUNT(*)
FROM teacher
WHERE Tname LIKE ('李%')
6.查询学过「张三」老师授课的同学的信息
SELECT s.*
FROM student as s INNER JOIN sc ON s.sid=sc.SId
INNER JOIN course as c on c.CId=sc.CId
INNER JOIN teacher as t on t.TId=c.TId
WHERE Tname='张三'
#第二种解法
SELECT * FROM student
WHERE SId in(SELECT DISTINCT sid FROM sc
WHERE cid=(SELECT CId FROM course
WHERE tid=(SELECT tid FROM teacher WHERE tname='张三')));
7.查询没有学全所有课程的同学的信息
SELECT s.*,COUNT(cid)
FROM sc RIGHT JOIN student as s
on sc.sid=s.SId
GROUP BY sc.SId
HAVING COUNT(CId)<(SELECT COUNT(DISTINCT CId)
FROM course)
8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
SELECT s.*
FROM sc INNER JOIN student as s
on sc.sid=s.sid
WHERE sc.cid in
(SELECT cid
FROM sc
WHERE sc.sid='01')
AND sc.sid !='01'
GROUP BY sc.SId
#第二种
SELECT * FROM student
WHERE SId in (SELECT sid FROM sc
WHERE cid in (SELECT cid FROM sc WHERE sId='01')) AND sid!='01';
9.查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
SELECT * FROM student
WHERE sid in
(SELECT sid FROM
(SELECT * from sc WHERE cid in
(SELECT cid FROM sc
WHERE sid='01'))b
GROUP BY SId
HAVING COUNT(cid)=(SELECT COUNT(cid) FROM sc WHERE sid='01'))
AND sid !='01'
#第二种
select * from student where sid in (select b.sid from sc b where b.sid not in
(select a.sid from sc a where a.cid not in (select cid from sc where sid = '01'))
and b.sid <> '01' group by b.sid
having count(1) = (select count(1) from sc where sid = '01'));
#(1) count(1) 会统计表中的所有的记录数,包含字段为null 的记录。
#(2) count(字段) 会统计该字段在表中出现的次数,忽略字段为null 的情况。即不统计字段为null 的记录。
10.查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT sname FROM student
WHERE sid NOT IN (
SELECT sid FROM sc
LEFT JOIN course ON sc.cid=course.cid
LEFT JOIN teacher ON course.tid=teacher.tid
WHERE tname='张三' )
11.查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
SELECT sname,s.sid,AVG(sc1.score)as avg_score
FROM (SELECT * FROM sc WHERE score<60)sc1,
student as s
WHERE sc1.sid=s.SId
GROUP BY sc1.sid
HAVING COUNT(sc1.cid)>=2
12.检索" 01 "课程分数小于 60,按分数降序排列的学生信息
SELECT s.*
from sc join student as s on s.SId=sc.SId
WHERE cid='01' AND score<60
ORDER BY score DESC
13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT sc.*,avg_score
FROM sc LEFT JOIN
(SELECT sc.sid,AVG(sc.score)as avg_score
from sc
GROUP BY sc.SId)r
ON sc.SId=r.sid
ORDER BY avg_score DESC
14.查询各科成绩最高分、最低分和平均分,以如下形式显示:
课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列。
SELECT
cid AS 课程ID,
COUNT(sid) AS 课程人数,
MAX(score) AS 最高分,
MIN(score) AS 最低分,
AVG(score) AS 平均分,
SUM(及格) / COUNT(sid) AS 及格率,
SUM(中等) / COUNT(sid) AS 中等率,
SUM(优良) / COUNT(sid) AS 优良率,
SUM(优秀) / COUNT(sid) AS 优秀率
FROM
(SELECT *,
CASE WHEN score >= 60 THEN 1 ELSE 0 END AS 及格,
CASE WHEN score >= 70 AND score<80 THEN 1 ELSE 0 END AS 中等,
CASE WHEN score >= 80 AND score<90 THEN 1 ELSE 0 END AS 优良,
CASE WHEN score >= 90 THEN 1 ELSE 0 END AS 优秀
FROM sc) a
GROUP BY cid
ORDER BY COUNT(sid) DESC,cid
15.按各科成绩进行排序,并显示排名, Score 重复时保留名次空缺
SELECT
a.*,
COUNT(a.score) AS 排名
FROM
sc AS a
LEFT JOIN sc AS b
ON a.cid = b.cid
AND a.score < b.score
GROUP BY a.cid,
a.sid
ORDER BY a.cid,排名
15.1 按各科成绩进行行排序,并显示排名, Score 重复时合并名次
SELECT
a.*,
COUNT(b.score)+1 AS 排名
FROM
sc AS a
LEFT JOIN sc AS b
ON a.cid = b.cid
AND a.score < b.score
GROUP BY a.cid,
a.sid
ORDER BY a.cid,
排名 ;
16.查询学生的总成绩,并进行排名,总分重复时保留名次空缺
SELECT
a.*,
@rank := @rank + 1 AS rank
FROM
(SELECT
sid,
SUM(score)
FROM
sc
GROUP BY sid
ORDER BY SUM(score) DESC) a,
(SELECT
@rank := 0) b ;
16.1 查询学生的总成绩,并进行排名,总分重复时不保留名次空缺
SELECT
a.*,
CASE
WHEN @fscore = a.sumscore
THEN @rank
WHEN @fscore := a.sumscore
THEN @rank := @rank + 1
END AS 排名
FROM
(SELECT
sc.sid,
SUM(score) AS sumscore
FROM
sc
GROUP BY sid
ORDER BY SUM(score) DESC) AS a,
(SELECT
@rank := 0,
@fscore := NULL) AS t ;
17. 统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 及所占百分比
SELECT
sc.CId as 课程编号,
cname as 课程名称,
SUM(case WHEN score>=0 and score<60 THEN 1 ELSE 0 END)as '[60-0]',
SUM(CASE WHEN score>=0 and score<60 THEN 1 ELSE 0 END)/COUNT(sid)as'[60-0]百分比 ',
SUM(CASE WHEN score>=60 and score<70 THEN 1 ELSE 0 END)as '[70-60]',
SUM(CASE WHEN score>=60 and score<70 THEN 1 ELSE 0 END)/COUNT(sid)as '[70-60]百分比' ,
SUM(case WHEN score>=70 and score<85 THEN 1 ELSE 0 END)as '[85-70]',
SUM(CASE WHEN score>=70 and score<85 THEN 1 ELSE 0 END)/COUNT(sid)as'[85-70]百分比 ',
SUM(CASE WHEN score>=85 and score<100 THEN 1 ELSE 0 END)as '[100-85]',
SUM(CASE WHEN score>=85 and score<100 THEN 1 ELSE 0 END)/COUNT(sid)as '[100-85]百分比'
FROM sc
JOIN course
on sc.cid=course.CId
group BY sc.CId,Cname
18.查询各科成绩前三名的记录
SELECT a.*,COUNT(b.score)+1 as rank
FROM sc as a
LEFT JOIN sc as b
on a.CId=b.CId
AND a.score<b.score
GROUP BY a.CId,a.SId
HAVING rank<=3
ORDER BY a.CId,rank
19.查询每门课程被选修的学生数
SELECT cid,COUNT(sid)
FROM sc
GROUP BY SId
20.查询出只选修两门课程的学生学号和姓名
SELECT sc.sid,sname
FROM sc INNER JOIN student
on sc.sid=student.SId
GROUP BY sc.SId
HAVING COUNT(cid)<=2
21. 查询男生、女生人数
SELECT ssex,COUNT(ssex)
FROM student
GROUP BY Ssex
SELECT
SUM(case WHEN ssex='男' THEN 1 ELSE 0 END)as '男生数量',
SUM(case WHEN ssex='女' THEN 1 ELSE 0 END)as '女生数量'
FROM student
22. 查询名字中含有「风」字的学生信息
SELECT *
FROM student
WHERE Sname LIKE '%风%'
23查询同名同性学生名单,并统计同名人数
SELECT sname,COUNT(sname)
FROM student
GROUP BY Sname
having COUNT(sname)>1
24.查询 1990 年出生的学生名单
SELECT *
FROM student
WHERE year(Sage) like '1990%'
25.查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
SELECT CId,AVG(score)
FROM sc
GROUP BY CId
ORDER BY AVG(score) DESC,cid
26.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
SELECT sc.sid,sname,AVG(score)
FROM sc,student
WHERE sc.sid=student.SId
GROUP BY sc.sId
HAVING AVG(score)>=85
SELECT sc.sid,sname,AVG(score)
FROM sc INNER JOIN student
ON sc.sid=student.SId
GROUP BY sc.sId
HAVING AVG(score)>=85
27.查询课程名称为「数学」,且分数低于 60 的学生姓名和分数
SELECT Sname,score
from sc INNER JOIN course
on sc.cid=course.CId
and course.cname='数学'
AND sc.score<60
INNER JOIN student as s
on s.sid=sc.SId
28. 查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况)
SELECT Sname,sc.cid,sc.score
FROM student as s INNER JOIN sc
on s.sid=sc.sid
29.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
SELECT Sname,sc.cid,sc.score
FROM student as s INNER JOIN sc
on s.sid=sc.sid
and score>70
30.查询不及格的课程
select * from SC where score<60
31. 查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
select A.Sid,B.Sname from (select * from SC where score>80 and Cid=01)A
left join Student B on A.Sid=B.Sid
32. 求每门课程的学生人数
select Cid,COUNT(*)学生人数 from SC group by Cid
33. 成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
select top 1* from SC
where Cid=(select Cid from Course where Tid=(select Tid from Teacher where Tname='张三'))
order by score desc
34. 成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
select *from(select *,DENSE_RANK()over (order by score desc)A
from SC
where Cid=(select Cid from Course where Tid=(select Tid from Teacher where Tname='张三')))B
where B.A=1
35. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select C.Sid,max(C.Cid)Cid,max(C.score)score from SC C
left join(select Sid,avg(score)A from SC group by Sid)B
on C.Sid=B.Sid
where C.score=B.A
group by C.Sid
having COUNT(0)=(select COUNT(0)from SC where Sid=C.Sid)
36. 查询每门功成绩最好的前两名
select * from
(select *,ROW_NUMBER()over(partition by Cid order by score desc)A from SC)B
where B.A<3
37.统计每门课程的学生选修人数(超过5人的课程才统计)。
要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列.
select Cid,COUNT(Sid)选修人数 from SC
group by Cid
having COUNT(Sid)>5
order by 选修人数 desc,Cid
38. 检索至少选修两门课程的学生学号
select Sid from SC
group by Sid
having COUNT(Cid)>=2
39. 查询选修了全部课程的学生信息
select Sid from SC
group by Sid
having count(Cid)=(select distinct COUNT(0)a from Course)
40. 查询各学生的年龄,只按年份来算
select Sid,datediff(yy,Sage,GETDATE())年龄 from Student
41. 按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一
select *,(case when convert(int,'1'+substring(CONVERT(varchar(10),Sage,112),5,8))
<convert(int,'1'+substring(CONVERT(varchar(10),GETDATE(),112),5,8))
then datediff(yy,Sage,GETDATE())
else datediff(yy,Sage,GETDATE())-1
end)age
from Student
42. 查询本周过生日的学生
select *,(case when datename(wk,convert(datetime,(convert(varchar(10),year(GETDATE()))
+substring(convert(varchar(10),Sage,112),5,8))))=DATENAME(WK,GETDATE())
then 1 else 0 end)生日提醒
from Student
43. 查询下周过生日的学生
select *,(case when datename(wk,convert(datetime,(convert(varchar(10),year(GETDATE()))+
substring(convert(varchar(10),Sage,112),5,8))))=DATENAME(WK,GETDATE())+1
then 1 else 0 end)生日提醒
from Student
44. 查询本月过生日的学生
select *,(case when month(convert(datetime,(convert(varchar(10),year(GETDATE()))
+substring(convert(varchar(10),Sage,112),5,8))))=month(GETDATE())
then 1 else 0 end)生日提醒
from Student
45. 查询下月过生日的学生
select *,(case when month(convert(datetime,(convert(varchar(10),year(GETDATE()))
+substring(convert(varchar(10),Sage,112),5,8))))=month(GETDATE())+1
then 1 else 0 end)生日提醒
from Student