-- 1.学生表 Student(SId,Sname,Sage,Ssex)
-- SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
-- 2.课程表 Course(CId,Cname,TId)
--CId --课程编号,Cname 课程名称,TId 教师编号
-- 3.教师表 Teacher(TId,Tname)
--TId 教师编号,Tname 教师姓名
-- 4.成绩表 SC(SId,CId,score)
--SId 学生编号,CId 课程编号,score 分数
create table Student(SId 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('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2017-12-30' , '女');
insert into Student values('12' , '赵六' , '2017-01-01' , '女');
insert into Student values('13' , '孙七' , '2018-01-01' , '女');
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
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 sc WHERE cid=01;
SELECT * FROM sc WHERE cid=02;
SELECT
a1.sid,a1.cid,a1.score,a2.cid,a2.score
FROM
(SELECT * FROM sc WHERE cid=01) a1
JOIN
(SELECT * FROM sc WHERE cid=02) a2
ON
a1.sid=a2.sid AND a1.score>a2.score
2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
SELECT sid, AVG( score) AS pingjun FROM sc GROUP BY sid HAVING avg(score)>=60;
SELECT
a1.sid,s.sname,a1.pingjun
FROM
(SELECT sid, AVG( score) AS pingjun FROM sc GROUP BY sid HAVING avg(score)>=60) a1
JOIN
student s
ON
a1.sid=s.sid
3.查询在 SC 表存在成绩的学生信息
SELECT DISTINCT
st.*
FROM
sc
JOIN
student st
ON
sc.sid=st.sid;
#SELECT * FROM student WHERE Sid IN(SELECT DISTINCT Sid FROM sc);
4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
SELECT sid,COUNT(cid) AS xuankeshu ,SUM(score) AS zongfen FROM sc GROUP BY sid;
SELECT
a2.xuankeshu,a2.zongfen,ss.sname,ss.sid
FROM
student ss
LEFT JOIN
(SELECT sid,COUNT(cid) AS xuankeshu ,SUM(score) AS zongfen FROM sc GROUP BY sid) a2
ON
a2.sid=ss.sid;
4.1 查有成绩的学生信息
SELECT
s.*,a1.zongfen
FROM
student s
JOIN
(SELECT sid,COUNT(cid) AS xuankeshu ,SUM(score) AS zongfen FROM sc GROUP BY sid) a1
ON
s.sid=a1.sid;
5.查询「李」姓老师的数量
SELECT COUNT(*) FROM teacher WHERE tname LIKE "李%";
6.查询学过「张三」老师授课的同学的信息
SELECT
c.cid
FROM
teacher t
JOIN
course c
ON
t.tid=c.tid AND t.tname="张三";
------ -----------------
SELECT
s.*
FROM
sc sc
JOIN
(SELECT
c.cid
FROM
teacher t
JOIN
course c
ON
t.tid=c.tid AND t.tname="张三") a1
ON
a1.cid=sc.cid
JOIN
student s
ON
s.sid=sc.sid
------ --------------------------
7.查询没有学全所有课程的同学的信息
SELECT COUNT(*) FROM course;
#2.查找成绩表中每个学生学习的课程数,并和总课程数对比,找出没有学全的。
SELECT sid FROM sc GROUP BY sid HAVING COUNT(*)<( SELECT COUNT(*) FROM course);
SELECT
s.*
FROM
student s
JOIN
(SELECT sid FROM sc GROUP BY sid HAVING COUNT(*)<( SELECT COUNT(*) FROM course)) a1
ON
s.sid=a1.sid
8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
#求出01学了哪些课程编号
SELECT cid FROM sc WHERE sid=01;
#选出学了01 || 02 || 03的同学编号
SELECT DISTINCT sid FROM sc WHERE cid IN(SELECT cid FROM sc WHERE sid=01);
SELECT
s.*
FROM
student s
JOIN
(SELECT DISTINCT sid FROM sc WHERE cid IN(SELECT cid FROM sc WHERE sid=01)) a1
ON
a1.sid = s.sid
WHERE
s.sid != 01
9.查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
#先求出01学的课程编号
SELECT cid FROM sc WHERE sid=01;
#找出和01同学学的课程不同的同学学号
SELECT DISTINCT sid FROM sc WHERE cid NOT IN(SELECT cid FROM sc WHERE sid=01);
#排除以上学生
SELECT * FROM sc WHERE sid NOT IN (SELECT DISTINCT sid FROM sc WHERE cid NOT IN(SELECT cid FROM sc WHERE sid=01)) ;
#01同学学了几门
SELECT COUNT(cid)FROM sc WHERE sid=01;
#找出上了课的同学跟01一样门数的学生编号
SELECT
sid
FROM
(
SELECT *
FROM
sc
WHERE
sid NOT IN (
SELECT DISTINCT
sid
FROM
sc
WHERE
cid NOT IN (SELECT cid FROM sc WHERE sid = 01)
)
) a1 GROUP BY sid HAVING COUNT(a1.cid )=(SELECT COUNT(cid)FROM sc WHERE sid=01);
#和学生表匹配
SELECT
s.*
FROM
student s
JOIN
(SELECT
sid
FROM
(
SELECT *
FROM
sc
WHERE
sid NOT IN (
SELECT DISTINCT
sid
FROM
sc
WHERE
cid NOT IN (SELECT cid FROM sc WHERE sid = 01)
)
) a1 GROUP BY sid HAVING COUNT(a1.cid )=(SELECT COUNT(cid)FROM sc WHERE sid=01)) AS a3
ON
a3.sid=s.sid;
10.查询没学过"张三"老师讲授的任一门课程的学生姓名
#查询张三的教师编号
SELECT tid FROM teacher WHERE tname="张三";
#查询教师编号(tid)为01的课程编号(cid) 2
SELECT cid FROM course WHERE tid IN(SELECT tid FROM teacher WHERE tname="张三");
#查询sc里 cid等于2的 sid
SELECT
sc.sid
FROM
sc sc
JOIN
(SELECT cid FROM course WHERE tid IN(SELECT tid FROM teacher WHERE tname="张三")) AS a3
ON
a3.cid=sc.cid
#在 student 里找到sid 和上面不相同的
SELECT sid,sname FROM student WHERE sid NOT IN(SELECT
sc.sid
FROM
sc sc
JOIN
(SELECT cid FROM course WHERE tid IN(SELECT tid FROM teacher WHERE tname="张三")) AS a3
ON
a3.cid=sc.cid);
11.查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
SELECT sid,score FROM sc WHERE score<60;
SELECT a1.sid,AVG(a1.score) FROM (SELECT sid,score FROM sc WHERE score<60) as a1 GROUP BY a1.sid;
SELECT
s.sname,a2.*
FROM
student s
JOIN
(SELECT a1.sid,AVG(a1.score) FROM (SELECT sid,score FROM sc WHERE score<60) as a1 GROUP BY a1.sid) AS a2
ON
a2.sid = s. sid
12.检索" 01 "课程分数小于 60,按分数降序排列的学生信息
#所有01课程的成绩
SELECT sid, score FROM sc WHERE cid=01 AND score<60;
SELECT
s.*,a1.*
FROM
student s
JOIN
(SELECT sid, score FROM sc WHERE cid=01 AND score<60) AS a1
ON
s.sid=a1.sid
ORDER BY
score DESC
13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT Sid,AVG(score) AS 平均分 FROM sc GROUP BY Sid;
SELECT
a1.*,sc.cid,sc.score
FROM
sc sc
JOIN
(SELECT Sid,AVG(score) AS 平均分 FROM sc GROUP BY Sid) a1
ON
a1.sid=sc.sid
ORDER BY
a1.平均分 DESC
14. 查询各科成绩最高分、最低分和平均分:
以如下形式显示:
课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
要求输出课程号和选修人数,查询结果按人数降序排列
若人数相同,按课程号升序排列
SELECT cid, MAX(score)FROM sc ORDER BY cid;
18.查询每门课程被选修的学生数
#选择01 cid 的 次数
SELECT COUNT(cid)FROM sc WHERE cid=01;
SELECT COUNT(cid)FROM sc WHERE cid=02;
SELECT COUNT(cid)FROM sc WHERE cid=03;
SELECT Cid,Count(*) FROM sc GROUP BY Cid;
19.查询出只选修两门课程的学生学号和姓名
SELECT Sid FROM sc GROUP BY Sid HAVING COUNT(*)=2;
SELECT
s.sname,a1.*
FROM
student s
JOIN
(SELECT Sid FROM sc GROUP BY Sid HAVING COUNT(*)=2) AS a1
ON
s.sid = a1.sid
20.查询男生、女生人数
SELECT ssex, COUNT(*) FROM student GROUP BY Ssex;
21.查询名字中含有「风」字的学生信息
SELECT * FROM student WHERE sname LIKE "%风%";
22.查询同名同性学生名单,并统计同名人数
SELECT ssex,sname ,COUNT(*) FROM student GROUP BY ssex,sname;
23.查询 1990 年出生的学生名单
SELECT * FROM student WHERE sage LIKE "1990%";
24.查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排
SELECT cid ,AVG( score) FROM sc GROUP BY cid ORDER BY AVG(score) DESC ,cid;
25.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
SELECT sid ,AVG(score) FROM sc GROUP BY sid HAVING (AVG(score)>=85);
SELECT
s.sname,a1.*
FROM
student s
JOIN
(SELECT sid ,AVG(score) FROM sc GROUP BY sid HAVING (AVG(score)>=85)) AS a1
ON
a1.sid=s.sid
26.查询课程名称为「数学」,且分数低于 60 的学生姓名和分数
SELECT cid FROM course WHERE cname="数学";
SELECT sid ,score FROM sc WHERE score<60 AND cid=(SELECT cid FROM course WHERE cname="数学")
SELECT
s.sname,a1.*
FROM
student s
JOIN
(SELECT sid ,score FROM sc WHERE score<60 AND cid=(SELECT cid FROM course WHERE cname="数学")) AS a1
ON
a1.sid=s.sid
28.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
#首先查score>70的 SId 和 CId
SELECT * FROM sc WHERE score>70;
#查找上表中 对应的 course 表中 找cname
SELECT
a1.*,c.cname
FROM
course c
JOIN
(SELECT * FROM sc WHERE score>70) AS a1
ON
c.CId=a1.cid
#接着拼接student 找 sname
SELECT
s.sname,a2.cname,a2.score
FROM
student s
JOIN
( SELECT
a1.*,c.cname
FROM
course c
JOIN
(SELECT * FROM sc WHERE score>70) AS a1
ON
c.CId=a1.cid) AS a2
ON
a2.sid=s.sid
29.查询不及格的课程
SELECT * FROM sc WHERE score<60;
30.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名>=80
SELECT sid FROM sc WHERE cid=01 AND score>=80;
SELECT
s.sname,a1.*
FROM
student s
JOIN
(SELECT sid FROM sc WHERE cid=01 AND score>=80) AS a1
ON
s.sid=a1.sid
31.求每门课程的学生人数
SELECT cid ,COUNT(*)FROM sc GROUP BY cid;
32.成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
#首先查找张三老师的教师编号 然后对应的 cid 课程编号02 数学
SELECT
t.tid,c.cid,c.cname
FROM
teacher t
JOIN
course c
ON
t.tid=c.tid
WHERE
t.tname="张三"
#和sc表链接 找出cid02的sid 和成绩
SELECT
sc.sid,a1.cid,a1.cname,MAX(score) AS 最高分
FROM
sc sc
JOIN
( SELECT
t.tid,c.cid,c.cname
FROM
teacher t
JOIN
course c
ON
t.tid=c.tid
WHERE
t.tname="张三") AS a1
ON
sc.cid=a1.cid
#和student表链接
SELECT
s.*,a2.最高分
FROM
student s
JOIN
( SELECT
sc.sid,a1.cid,a1.cname,MAX(score) AS 最高分
FROM
sc sc
JOIN
( SELECT
t.tid,c.cid,c.cname
FROM
teacher t
JOIN
course c
ON
t.tid=c.tid
WHERE
t.tname="张三") AS a1
ON
sc.cid=a1.cid)AS a2
ON
a2.sid=s.sid
36.统计每门课程的学生选修人数(超过 5 人的课程才统计)。
SELECT cid , COUNT(sid) FROM sc GROUP BY cid HAVING COUNT(sid)>5;
37.检索至少选修两门课程的学生学号
SELECT sid ,COUNT(cid )FROM sc GROUP BY sid HAVING (COUNT(cid))>2;
38.查询选修了全部课程的学生信息
#选出 cid 个数 为3 的 sid
SELECT sid,COUNT(cid) FROM sc GROUP BY sid HAVING (COUNT(cid))=3;
SELECT
s.*,a1.*
FROM
student s
JOIN
(SELECT sid,COUNT(cid) FROM sc GROUP BY sid HAVING (COUNT(cid))=3) AS a1
ON
a1.sid=s.sid
39.查询各学生的年龄,只按年份来算
SELECT Sname,DATE_FORMAT(CURRENT_DATE(),'%Y')-DATE_FORMAT(Sage,'%Y') FROM student ;