面试必练：50道经典SQL练习

–1.学生表
— Student(S,Sname,Sage,Ssex)
–S 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
–2.课程表
– Course(C,Cname,T)
–C --课程编号,Cname 课程名称,T 教师编号
–3.教师表
– Teacher(T,Tname)
–T 教师编号,Tname 教师姓名
–4.成绩表
– SC(S,C,score)
–S 学生编号,C 课程编号,score 分数

–创建测试数据

create table Student(S varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(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’ , ‘女’);

create table Course(C varchar(10),Cname varchar(10),T varchar(10));
insert into Course values(‘01’ , ‘语文’ , ‘02’);
insert into Course values(‘02’ , ‘数学’ , ‘01’);
insert into Course values(‘03’ , ‘英语’ , ‘03’);

create table Teacher(T varchar(10),Tname varchar(10));
insert into Teacher values(‘01’ , ‘张三’);
insert into Teacher values(‘02’ , ‘李四’);
insert into Teacher values(‘03’ , ‘王五’);

create table SC(S varchar(10),C 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"课程成绩高的学生的信息及课程分数
–2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
–3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
–4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
–5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
–6、查询"李"姓老师的数量
–7、查询学过"张三"老师授课的同学的信息
–8、查询没学过"张三"老师授课的同学的信息
–9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
–10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
–11、查询没有学全所有课程的同学的信息
–12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
–13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
–14、查询没学过"张三"老师讲授的任一门课程的学生姓名
–15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
–16、检索"01"课程分数小于60，按分数降序排列的学生信息
–17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
–18、查询各科成绩最高分、最低分和平均分：以如下形式显示：
–课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
–及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
–19、按各科成绩进行排序，并显示排名
–20、查询学生的总成绩并进行排名
–21、查询不同老师所教不同课程平均分从高到低显示
–22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
–23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
–24、查询学生平均成绩及其名次
–25、查询各科成绩前三名的记录
–26、查询每门课程被选修的学生数
–27、查询出只有两门课程的全部学生的学号和姓名
–28、查询男生、女生人数
–29、查询名字中含有"风"字的学生信息
–30、查询同名同性学生名单，并统计同名人数
–31、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)
–32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号
–33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
–34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
–35、查询所有学生的课程及分数情况；
–36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
–37、查询不及格的课程
–38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；
–39、求每门课程的学生人数
–40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
–41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
–42、查询每门功成绩最好的前两名
–43、统计每门课程的学生选修人数（超过5人的课程才统计）。
–要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
–44、检索至少选修两门课程的学生学号
–45、查询选修了全部课程的学生信息
–46、查询各学生的年龄
–47、查询本周过生日的学生
–48、查询下周过生日的学生
–49、查询本月过生日的学生
–50、查询下月过生日的学生

–参考SQL
–1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

SELECT a.* ,c.*
from
(select
a.*
from
(SELECT * from sc WHERE sc.C in(‘01’))a
left JOIN(SELECT * from sc where sc.C in(‘02’))b
ON a.s =b.s
where a.score>b.score ) a ,student c
where a.s =c.s;
–2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

SELECT a.* ,c.*
FROM
(SELECT a.*
FROM
(SELECT * FROM sc WHERE sc.C in(‘01’))a
LEFT JOIN(SELECT * FROM sc WHERE sc.C in(‘02’))b
ON a.s =b.s WHERE a.score<b.score) a,student c
WHERE a.s =c.s
–3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

SELECT
b.s
,c.Sname
,b.avgscore
FROM
(SELECT
a.s s
, avg(a.score) avgscore
FROM sc a
GROUP BY a.s) b
,student c
WHERE
b.avgscore>60 AND c.s =b.s
–4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

SELECT
b.s
,c.Sname
,b.avgscore
FROM
(SELECT
a.s s
, avg(a.score) avgscore
FROM sc a
GROUP BY a.s) b
,student c
WHERE
b.avgscore<60 AND c.s =b.s
–5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

SELECT
b.s
,c.Sname
,b.num
,b.sumscore
FROM
(SELECT
a.s s
,COUNT(a.c) num
,SUM(a.score) sumscore
FROM
sc a
GROUP BY a.s) b
,student c
WHERE b.s =c.s
–6、查询"李"姓老师的数量

SELECT
COUNT(a.Tname)
FROM
(SELECT
Tname Tname
FROM
teacher
WHERE
Tname LIKE ‘李%’) a
–7、查询学过"张三"老师授课的同学的信息

SELECT
d.*
FROM
student d
,sc e
WHERE
e.S =d.S
AND
e.C=
(SELECT
b.C
FROM
teacher a
,course b
WHERE
a.T =b.T AND a.Tname=‘张三’)
–8、查询没学过"张三"老师授课的同学的信息

SELECT
a.*
FROM
student a
WHERE
a.S NOT in
(SELECT
b.S
FROM
sc a
,student b

WHERE
a.S=b.S AND
a.C =
(SELECT
b.c
FROM
teacher a
,course b
WHERE
a.T =b.T AND a.Tname =‘张三’))
–9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

SELECT
*
FROM
sc a,
sc b,
student c
WHERE
a.S =b.S AND a.C=‘01’ and b.C=‘02’
AND c.S =a.S
–10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

SELECT
*
FROM
student a
WHERE
a.S in
(
SELECT
DISTINCT(sc.s)
FROM
sc
WHERE
sc.S
NOT
in(
SELECT S
FROM sc
WHERE
sc.C in(‘02’)
GROUP BY
sc.S)
)
–11、查询没有学全所有课程的同学的信息

SELECT
b.*
FROM
student b
WHERE
b.S in
(
SELECT
a.s
FROM
(SELECT
a.S s
,COUNT(a.C) NUM
FROM
sc a
GROUP BY
a.s) a
WHERE
a.NUM<3
)
–12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息

SELECT
e.*
FROM
student e
WHERE
e.S in
(SELECT
DISTINCT(c.s)
FROM
student c
,sc d
WHERE
c.s=d.s
AND
d.C in
(SELECT
b.c
FROM
student a
,sc b
WHERE
a.s =b.s AND a.s =‘01’
)
)
–13、查询和"01"号的同学学习的课程完全相同的其他同学的信息

SELECT
a.c
FROM
sc a
WHERE
a.S =‘01’

---

SELECT
*
FROM
(SELECT
a.*
,COUNT(b.c) num
FROM
student a
,sc b
WHERE
a.s=b.S
GROUP BY
b.S
) a
WHERE
a.num=3 AND a.s <> ‘01’
–14、查询没学过"张三"老师讲授的任一门课程的学生姓名

SELECT
*
FROM
student a
WHERE
a.S NOT in
(
SELECT
b.S
FROM
(
SELECT
b.c
,b.Cname
FROM
teacher a
,course b
WHERE
a.T=b.T
AND
a.Tname=‘张三’
) a
,sc b
WHERE
a.c=b.C
)
–15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

SELECT
b.S
,b.Sname
,a.avgscore
FROM
(SELECT
AVG(score) avgscore
,s S
FROM
sc
WHERE
sc.score<60
GROUP BY
s
HAVING COUNT(s)>=2) a
,student b
WHERE
a.S=b.S
–16、检索"01"课程分数小于60，按分数降序排列的学生信息

SELECT
b.*
FROM
(SELECT
a.s s
FROM
sc a
WHERE
a.C =‘01’
AND
a.score<60
ORDER BY a.score DESC
) a
,student b
WHERE
a.s=b.S
–17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

SELECT
*
FROM
(SELECT
b.S
,b.C
,b.score
,c.avgscore
FROM
sc b
LEFT JOIN
(SELECT
a.S
,AVG(a.score) avgscore
FROM
sc a
GROUP BY a.S) c
ON c.S =b.S) a
,student b
WHERE
a.S =b.S
ORDER BY avgscore DESC
–18、查询各科成绩最高分、最低分和平均分：以如下形式显示：
–课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
–及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

SELECT
a.C
,b.Cname
,MAX(a.score)
,MIN(a.score)
,AVG(a.score)
,(SELECT COUNT(1) FROM sc WHERE sc.C=b.C and score>60)/(SELECT COUNT(1)FROM sc WHERE sc.C=b.c ) jigelv
FROM sc a ,course b
WHERE a.C =b.C
GROUP BY
a.C
SELECT
a.C
,a.Cname
,MAX(b.score)
,MIN(b.score)
,CAST(AVG(b.score) AS DECIMAL(18,2)) pingqunfen
,CAST((SELECT COUNT(1) FROM sc WHERE sc.C=a.C AND sc.score>60)/
(SELECT COUNT(1) FROM sc WHERE sc.C =a.C) AS DECIMAL(18,2)) jigelv
,CAST((SELECT COUNT(1) FROM sc WHERE sc.C =a.c AND sc.score>=70 AND sc.score<80)/
(SELECT COUNT(1) FROM sc WHERE sc.c =a.c) AS DECIMAL (18,2)) youlianlv
FROM
course a,sc b
WHERE
a.C=b.C
GROUP BY a.C,a.Cname
ORDER BY a.C DESC
–19、按各科成绩进行排序，并显示排名

SELECT
c.s
,c.sname
,b.cname
,a.score
,(SELECT COUNT(1) FROM sc WHERE sc.C=a.c AND sc.score > a.score)+1 mc
FROM
sc a ,course b,student c
WHERE a.c=‘01’
AND a.c=b.c
AND a.s=c.s
ORDER BY mc
SELECT
c.s
,c.sname
,b.cname
,a.score
,(SELECT COUNT(1) FROM sc WHERE sc.C=a.c AND sc.score > a.score)+1 mc
FROM
sc a ,course b,student c
WHERE a.c=‘02’
AND a.c=b.c
AND a.s=c.s
ORDER BY mc
SELECT
c.s
,c.sname
,b.cname
,a.score
,(SELECT COUNT(1) FROM sc WHERE sc.C=a.c AND sc.score > a.score)+1 mc
FROM
sc a ,course b,student c
WHERE a.c=‘03’
AND a.c=b.c
AND a.s=c.s
ORDER BY mc
–20、查询学生的总成绩并进行排名

SELECT
b.s
,b.sumscore
,@rownum:=@rownum +1 AS rownum
FROM
(SELECT
a.s s
,SUM(a.score) sumscore
FROM
sc a
GROUP BY
a.S
ORDER BY sumscore DESC
) as b
,(SELECT @rownum:=0)r
–21、查询不同老师所教不同课程平均分从高到低显示

SELECT
c.Tname
,b.Cname
,AVG(a.score)
FROM
sc a
,course b
,teacher c
WHERE
a.C =b.C
AND b.T =c.T
GROUP BY a.C
ORDER BY AVG(a.score) DESC
–22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

SELECT
a.*
,b.*
FROM
(SELECT b.s ,b.C ,b.score
,(SELECT COUNT() FROM sc a WHERE a.c='01’AND a.score>b.score)+1 mc
FROM sc b WHERE b.c=‘01’ HAVING mc BETWEEN 2 AND 3
UNION ALL
SELECT b.s ,b.C ,b.score
,(SELECT COUNT() FROM sc a WHERE a.c='02’AND a.score>b.score)+1 mc
FROM sc b WHERE b.c=‘02’ HAVING mc BETWEEN 2 AND 3
UNION ALL
SELECT b.s ,b.C ,b.score
,(SELECT COUNT(*) FROM sc a WHERE a.c='03’AND a.score>b.score)+1 mc
FROM sc b WHERE b.c=‘03’ HAVING mc BETWEEN 2 AND 3
) a
,student b
WHERE
a.s =b.s
–23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

SELECT
a.*
FROM
(SELECT
c.Cname
,(SELECT COUNT()FROM sc a WHERE a.score>85 AND a.C=b.c AND c.c=a.C AND a.C=‘01’) ‘85以上’
,(SELECT COUNT()FROM sc a WHERE a.score<85 AND a.C=b.c AND c.c=a.C AND a.score>70 AND a.C=‘01’) ‘[70_85]’
,(SELECT COUNT()FROM sc a WHERE a.score>70 AND a.C=b.c AND c.c=a.C AND a.score>60 AND a.C=‘01’) ‘[60_70]’
,(SELECT COUNT()FROM sc a WHERE a.score<60 AND a.C=b.c AND c.c=a.C AND a.C=‘01’) ‘60一下’
,(SELECT COUNT()FROM sc a WHERE a.score>85 AND a.C=b.c AND c.c=a.C AND a.C=‘01’)/(SELECT COUNT()FROM sc WHERE c='01’AND C=b.c AND c.c=c) ‘85以上比率’
,(SELECT COUNT()FROM sc a WHERE a.score<85 AND a.C=b.c AND c.c=a.C AND a.score>70 AND a.C=‘01’)/(SELECT COUNT()FROM sc WHERE c=‘01’AND C=b.c AND c.c=c) ‘[70_85]以上比率’
,(SELECT COUNT()FROM sc a WHERE a.score>70 AND a.C=b.c AND c.c=a.C AND a.score>60 AND a.C=‘01’)/(SELECT COUNT()FROM sc WHERE c=‘01’AND C=b.c AND c.c=c)’[60_70]以上比率’
,(SELECT COUNT()FROM sc a WHERE a.score<60 AND a.C=b.c AND c.c=a.C AND a.C=‘01’)/(SELECT COUNT()FROM sc WHERE c=‘01’AND C=b.c AND c.c=c)‘60以上比率’
FROM sc b ,course c WHERE b.c=c.c AND c.c=‘01’
UNION ALL
SELECT
c.Cname
,(SELECT COUNT()FROM sc a WHERE a.score>85 AND a.C=b.c AND c.c=a.C AND a.C=‘02’) ‘85以上’
,(SELECT COUNT()FROM sc a WHERE a.score<85 AND a.C=b.c AND c.c=a.C AND a.score>70 AND a.C=‘02’) ‘[70_85]’
,(SELECT COUNT()FROM sc a WHERE a.score>70 AND a.C=b.c AND c.c=a.C AND a.score>60 AND a.C=‘02’) ‘[60_70]’
,(SELECT COUNT()FROM sc a WHERE a.score<60 AND a.C=b.c AND c.c=a.C AND a.C=‘02’) ‘60一下’
,(SELECT COUNT()FROM sc a WHERE a.score>85 AND a.C=b.c AND c.c=a.C AND a.C=‘02’)/(SELECT COUNT()FROM sc WHERE c=‘02’AND C=b.c AND c.c=c) ‘85以上比率’
,(SELECT COUNT()FROM sc a WHERE a.score<85 AND a.C=b.c AND c.c=a.C AND a.score>70 AND a.C=‘02’)/(SELECT COUNT()FROM sc WHERE c=‘02’AND C=b.c AND c.c=c)’[70_85]以上比率’
,(SELECT COUNT()FROM sc a WHERE a.score>70 AND a.C=b.c AND c.c=a.C AND a.score>60 AND a.C=‘02’)/(SELECT COUNT()FROM sc WHERE c=‘02’AND C=b.c AND c.c=c)’[60_70]以上比率’
,(SELECT COUNT()FROM sc a WHERE a.score<60 AND a.C=b.c AND c.c=a.C AND a.C=‘02’)/(SELECT COUNT()FROM sc WHERE c=‘02’AND C=b.c AND c.c=c)‘60以上比率’
FROM sc b ,course c WHERE b.c=c.c AND c.c=‘02’
UNION ALL
SELECT
c.Cname
,(SELECT COUNT()FROM sc a WHERE a.score>85 AND a.C=b.c AND c.c=a.C AND a.C=‘03’) ‘85以上’
,(SELECT COUNT()FROM sc a WHERE a.score<85 AND a.C=b.c AND c.c=a.C AND a.score>70 AND a.C=‘03’) ‘[70_85]’
,(SELECT COUNT()FROM sc a WHERE a.score>70 AND a.C=b.c AND c.c=a.C AND a.score>60 AND a.C=‘03’) ‘[60_70]’
,(SELECT COUNT()FROM sc a WHERE a.score<60 AND a.C=b.c AND c.c=a.C AND a.C=‘03’) ‘60一下’
,(SELECT COUNT()FROM sc a WHERE a.score>85 AND a.C=b.c AND c.c=a.C AND a.C=‘03’)/(SELECT COUNT()FROM sc WHERE c=‘03’AND C=b.c AND c.c=c) ‘85以上比率’
,(SELECT COUNT()FROM sc a WHERE a.score<85 AND a.C=b.c AND c.c=a.C AND a.score>70 AND a.C=‘03’)/(SELECT COUNT()FROM sc WHERE c=‘03’AND C=b.c AND c.c=c)’[70_85]以上比率’
,(SELECT COUNT()FROM sc a WHERE a.score>70 AND a.C=b.c AND c.c=a.C AND a.score>60 AND a.C=‘03’)/(SELECT COUNT()FROM sc WHERE c=‘03’AND C=b.c AND c.c=c)’[60_70]以上比率’
,(SELECT COUNT()FROM sc a WHERE a.score<60 AND a.C=b.c AND c.c=a.C AND a.C=‘01’)/(SELECT COUNT()FROM sc WHERE c='03’AND C=b.c AND c.c=c)‘60以上比率’
FROM sc b ,course c WHERE b.c=c.c AND c.c=‘03’) a
–24、查询学生平均成绩及其名次

SELECT
a.s
,a.avgscore
,@rownum:=@rownum +1 AS rownum
FROM
(SELECT
s
,AVG(score) avgscore
FROM
sc
GROUP BY s
ORDER BY avgscore DESC
) a
,(SELECT @rownum:=0)r
–25、查询各科成绩前三名的记录

SELECT
a.*
,b.*
FROM
(SELECT b.s ,b.C ,b.score
,(SELECT COUNT() FROM sc a WHERE a.c='01’AND a.score>b.score)+1 mc
FROM sc b WHERE b.c=‘01’ HAVING mc BETWEEN 1 AND 3
UNION ALL
SELECT b.s ,b.C ,b.score
,(SELECT COUNT() FROM sc a WHERE a.c='02’AND a.score>b.score)+1 mc
FROM sc b WHERE b.c=‘02’ HAVING mc BETWEEN 1 AND 3
UNION ALL
SELECT b.s ,b.C ,b.score
,(SELECT COUNT(*) FROM sc a WHERE a.c='03’AND a.score>b.score)+1 mc
FROM sc b WHERE b.c=‘03’ HAVING mc BETWEEN 1 AND 3
) a
,student b
WHERE
a.s =b.s
–26、查询每门课程被选修的学生数

SELECT
a.Cname
,COUNT(b.c)
FROM sc b,course a
WHERE a.c =b.c
GROUP BY b.c
–27、查询出只有两门课程的全部学生的学号和姓名

SELECT
b.S
,b.Sname
,a.countc
FROM
(SELECT
s
,COUNT© countc
FROM
sc
GROUP BY s
HAVING countc =2) a
,student b
WHERE
a.s=b.s
–28、查询男生、女生人数

select s.Ssex,COUNT()from student s where s.Ssex=‘男’
UNION
select s.Ssex,COUNT()from student s where s.Ssex=‘女’
–29、查询名字中含有"风"字的学生信息

SELECT
*
,count(*)
FROM student
WHERE
Sname rLIKE ‘风’
–30、查询同名同性学生名单，并统计同名人数

SELECT
s.Sname
,s.Ssex
,COUNT(*) countnum
from
student s
GROUP BY
s.Sname,s.Ssex
HAVING
countnum>=2
–31、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)

SELECT
*
FROM
student
WHERE
Sage LIKE ‘1990%’
–32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号

SELECT
a.Cname
,AVG(sc.score) avgscore
FROM sc ,course a
WHERE sc.C =a.C
GROUP BY sc.C
ORDER BY avgscore DESC
–33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

SELECT
*
FROM
(SELECT
a.Sname
,a.S
,AVG(sc.score) avgscore
FROM sc ,student a
WHERE sc.s =a.s
GROUP BY sc.s
ORDER BY avgscore DESC
) a
WHERE
a.avgscore>85
–34、查询课程名称为"数学"，且分数低于60的学生姓名和分数

SELECT
*
FROM
sc a,course b
WHERE
a.c =b.c AND b.Cname=‘数学’ AND a.score>60
–35、查询所有学生的课程及分数情况；

SELECT
*
FROM
student a
,course b
,sc c
WHERE
a.S=c.S AND b.C =c.C
–36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；

SELECT
b.Sname
,c.Cname
,a.score
FROM
sc a, student b,course c
WHERE
a.score>70 AND a.C =c.C AND a.S =b.S
–37、查询不及格的课程

SELECT
b.Sname
,c.Cname
,a.score
FROM
sc a, student b,course c
WHERE
a.score<60 AND a.C =c.C AND a.S =b.S
–38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；

SELECT
c.S
,c.Sname
,a.Cname
,b.score
FROM
course a ,sc b ,student c
WHERE a.C =b.C AND b.score>=80 AND c.S =b.S AND a.C=‘01’
–39、求每门课程的学生人数

SELECT
b.Cname
,COUNT(a.c)
FROM
sc a ,course b
WHERE
b.c=a.C
GROUP BY
a.C
–40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

SELECT
b.*
,MAX(c.score)
FROM
teacher a,student b ,sc c ,course d
WHERE
a.T =d.T AND d.C =c.C AND c.S =b.S AND a.Tname =‘张三’
–41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

SELECT c.* FROM sc c GROUP BY c.C,c.score HAVING COUNT(*)>1;
–42、查询每门功成绩最好的前两名

SELECT
a.*
,b.*
FROM
(SELECT b.s ,b.C ,b.score
,(SELECT COUNT() FROM sc a WHERE a.c='01’AND a.score>b.score)+1 mc
FROM sc b WHERE b.c=‘01’ HAVING mc BETWEEN 1 AND 2
UNION ALL
SELECT b.s ,b.C ,b.score
,(SELECT COUNT() FROM sc a WHERE a.c='02’AND a.score>b.score)+1 mc
FROM sc b WHERE b.c=‘02’ HAVING mc BETWEEN 1 AND 2
UNION ALL
SELECT b.s ,b.C ,b.score
,(SELECT COUNT(*) FROM sc a WHERE a.c='03’AND a.score>b.score)+1 mc
FROM sc b WHERE b.c=‘03’ HAVING mc BETWEEN 1 AND 2
) a
,student b
WHERE
a.s =b.s
–43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT
b.C
,b.Cname
,COUNT(a.C) countc
FROM
sc a,course b
WHERE
a.c=b.C
GROUP BY
a.C
HAVING countc>5
ORDER BY countc DESC
–44、检索至少选修两门课程的学生学号

SELECT
sc.S
,COUNT(sc.C) countc
FROM
sc
GROUP BY sc.S
HAVING countc >=2
–45、查询选修了全部课程的学生信息

SELECT
a.*
,COUNT(sc.C) countc
FROM
sc ,student a
WHERE sc.S =a.S
GROUP BY sc.S
HAVING countc =3
–46、查询各学生的年龄

SELECT
s.s,
s.sname,
EXTRACT(YEAR FROM NOW())-EXTRACT(YEAR FROM s.sage) 年龄
FROM
student s
–47、查询本周过生日的学生

SELECT
s.S
,s.Sname
,s.Sage
FROM student s
WHERE YEARWEEK(DATE_FORMAT(s.Sage,’%Y-%m-%d’)) =YEARWEEK(NOW())
–48、查询下周过生日的学生

SELECT
s.S
,s.Sname
,s.Sage
FROM student s
WHERE YEARWEEK(DATE_FORMAT(s.Sage,’%Y-%m-%d’)) =YEARWEEK(NOW())+1
–49、查询本月过生日的学生

SELECT
s.S
,s.Sname
,s.Sage
FROM student s
WHERE MONTH(DATE_FORMAT(s.Sage,’%Y-%m-%d’)) =MONTH(NOW())
–50、查询下月过生日的学生

SELECT
s.S
,s.Sname
,s.Sage
FROM student s
WHERE MONTH(DATE_FORMAT(s.Sage,’%Y-%m-%d’)) =MONTH(NOW())+1