学生表 Student
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-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-01-01' , '女');
insert into Student values('07' , '郑竹' , '1989-01-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2012-06-06' , '女');
insert into Student values('12' , '赵六' , '2013-06-13' , '女');
insert into Student values('13' , '孙七' , '2014-06-01' , '女');
科目表 Course
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');
教师表 Teacher
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
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);
练习题及答案（有自己写的答案有从网上copy的答案）
SELECT s1.*,s2.class1,s2.class2 FROM student s1,
(SELECT t1.SId,t1.score class1,t2.score class2 FROM
(SELECT * FROM sc WHERE CId =01 ) t1,
(SELECT * FROM sc WHERE CId =02 ) t2
WHERE t1.SId =t2.SId AND t1.score>t2.score) s2
WHERE s1.`SId`=s2.SId
SELECT * FROM
(SELECT * FROM sc WHERE CId=01) t1,
(SELECT * FROM sc WHERE CId=02) t2
WHERE t1.SId=t2.SId
SELECT * FROM
(SELECT * FROM sc WHERE sc.`CId`=01) t1 LEFT JOIN
(SELECT * FROM sc WHERE sc.`CId`=02) t2
ON t1.SId=t2.SId
SELECT * FROM sc WHERE SId NOT IN
(SELECT SId FROM sc WHERE CId=01) AND CId= 02
SELECT t1.SId,t1.Sname,AVG(t2.score) AVG
FROM student t1,sc t2
WHERE t1.SId =t2.SId GROUP BY t1.SId HAVING AVG(t2.score)>60
SELECT DISTINCT t1.*
FROM student t1,sc t2
WHERE t1.SId=t2.SId AND t2.score IS NOT NULL
SELECT t1.SId,t1.Sname,COUNT(t2.CId) ClassCount ,SUM(t2.score) TotalScore
FROM
student t1 RIGHT JOIN sc t2
ON t1.SId=t2.SId GROUP BY t1.Sname
SELECT t1.SId,t1.Sname,COUNT(t2.CId) ClassCount ,SUM(t2.score) TotalScore
FROM
student t1 LEFT JOIN sc t2
ON t1.SId=t2.SId GROUP BY t1.Sname
SELECT t1.*
FROM
student t1,sc t2
WHERE t1.SId=t2.Sid AND t2.score IS NOT NULL GROUP BY t1.Sname
SELECT t1.*
FROM student t1
WHERE
t1.SId IN(SELECT sc.`SId` FROM sc )
SELECT * FROM student
WHERE EXISTS (SELECT sc.sid FROM sc WHERE student.sid = sc.sid);
SELECT COUNT(*) FROM teacher WHERE Tname LIKE '李%'
SELECT t5.SId,t5.Sname,t5.Sage,t5.Ssex
FROM
(SELECT t3.*,t4.TId FROM
( SELECT t1.*,t2.CId
FROM student t1 , sc t2
WHERE t1.SId=t2.SId) t3,
course t4
WHERE t3.CId=t4.CId) t5,
teacher t6
WHERE t5.TId=t6.TId AND t6.Tname="张三"
SELECT student.* FROM student,teacher,course,sc
WHERE
student.sid = sc.sid
AND course.cid=sc.cid
AND course.tid = teacher.tid
AND tname = '张三';
SELECT t3.SId,t3.Sname,t3.Sage,t3.Ssex
FROM
(
SELECT t1.*,t2.CId FROM student t1 LEFT JOIN sc t2
ON t1.SId=t2.SId
) t3
GROUP BY t3.SId HAVING COUNT(t3.CId) < (SELECT COUNT(CId) FROM course)
SELECT * FROM student WHERE SId IN
(
SELECT t1.SId FROM student t1 LEFT JOIN sc t2
ON t1.SId=t2.SId GROUP BY t1.SId HAVING COUNT(t2.CId)<(SELECT COUNT(CId) FROM course)
)
SELECT * FROM student
WHERE student.sid NOT IN (
SELECT sc.sid FROM sc
GROUP BY sc.sid
HAVING COUNT(sc.cid)= (SELECT COUNT(cid) FROM course)
);
SELECT DISTINCT t1.*
FROM
student t1,sc t2
WHERE
t1.`SId`=t2.`SId` AND t2.CId IN ( SELECT CId FROM sc WHERE SId =01 )
SELECT * FROM student
WHERE student.sid IN (
SELECT sc.sid FROM sc
WHERE sc.cid IN(
SELECT sc.cid FROM sc
WHERE sc.sid = '01'
)
);
SELECT t1.SId,t1.Sname,t1.Sage,t1.Ssex FROM
(
SELECT t1.*, GROUP_CONCAT(t2.`CId` ORDER BY t2.`CId` ASC SEPARATOR ',') course
FROM student t1,sc t2
WHERE t1.`SId`=t2.`SId` GROUP BY t1.SId
) t1
WHERE t1.course = (SELECT GROUP_CONCAT(CId ORDER BY CId ASC SEPARATOR ',' ) FROM sc WHERE SId=01 ) AND t1.SId<>01
SELECT Sname FROM student WHERE SId NOT IN
(
SELECT t1.`SId`
FROM student t1 LEFT JOIN sc t2
ON t1.`SId`=t2.`SId` WHERE t2.`CId` = (
SELECT CId FROM course WHERE TId =
(
SELECT TId FROM teacher WHERE Tname="张三"
)
)
)
SELECT * FROM student
WHERE student.sid NOT IN(
SELECT sc.sid FROM sc WHERE sc.cid IN(
SELECT course.cid FROM course WHERE course.tid IN(
SELECT teacher.tid FROM teacher WHERE tname = "张三"
)
)
)
SELECT * FROM student
WHERE student.sid NOT IN(
SELECT sc.sid FROM sc,course,teacher
WHERE
sc.cid = course.cid
AND course.tid = teacher.tid
AND teacher.tname= "张三"
);
SELECT t1.SId,t1.Sname , AVG(t2.`score`) AVG
FROM student t1,sc t2
WHERE t1.Sid=t2.SId AND t1.SId IN (
SELECT SId
FROM sc
WHERE score < 60 GROUP BY SId HAVING COUNT(CId)>=2
)
GROUP BY t1.SId
SELECT student.sid, student.sname, AVG(sc.score) FROM student,sc
WHERE
student.sid = sc.sid AND sc.score<60
GROUP BY sc.sid
HAVING COUNT(*)>1;
SELECT t1.* ,t2.score
FROM student t1,sc t2
WHERE t1.SId=t2.`SId` AND t2.CId=01 AND t2.`score`<60 ORDER BY t2.`score` DESC
SELECT t1.*,t2.avg
FROM sc t1 LEFT JOIN
(
SELECT sc.`SId`,AVG(sc.`score`) AVG
FROM sc GROUP BY sc.`SId`
) t2
ON t1.`SId`=t2.`SId` ORDER BY t2.avg DESC
SELECT * FROM sc
LEFT JOIN (
SELECT sid,AVG(score) AS avscore FROM sc
GROUP BY sid
)r
ON sc.sid = r.sid
ORDER BY avscore DESC;
SELECT
sc.CId ,course.`Cname`,
MAX(sc.score)AS 最高分,
MIN(sc.score)AS 最低分,
AVG(sc.score)AS 平均分,
COUNT(*)AS 选修人数,
SUM(CASE WHEN sc.score>=60 THEN 1 ELSE 0 END )/COUNT(*)AS 及格率,
SUM(CASE WHEN sc.score>=70 AND sc.score<80 THEN 1 ELSE 0 END )/COUNT(*)AS 中等率,
SUM(CASE WHEN sc.score>=80 AND sc.score<90 THEN 1 ELSE 0 END )/COUNT(*)AS 优良率,
SUM(CASE WHEN sc.score>=90 THEN 1 ELSE 0 END )/COUNT(*)AS 优秀率
FROM sc , course
WHERE sc.`CId`=course.`CId`
GROUP BY sc.CId
ORDER BY COUNT(*)DESC, sc.CId ASC
SELECT a.cid, a.sid, a.score, COUNT(b.score)+1 AS rank
FROM sc AS a
LEFT JOIN sc AS b
ON a.score<b.score AND a.cid = b.cid
GROUP BY a.cid, a.sid,a.score
ORDER BY a.cid, rank ASC;
SET @crank=0;
SELECT t1.* ,@crank := @crank +1 AS rank
FROM
(
SELECT sid , SUM(sc.score) total
FROM sc
GROUP BY sid ORDER BY total DESC
) t1
SET @crank=0;
SELECT q.sid, total, @crank := @crank +1 AS rank FROM(
SELECT sc.sid, SUM(sc.score) AS total FROM sc
GROUP BY sc.sid
ORDER BY total DESC)q;
SELECT course.cname, course.cid,
SUM(CASE WHEN sc.score<=100 AND sc.score>85 THEN 1 ELSE 0 END) AS "[100-85]",
SUM(CASE WHEN sc.score<=85 AND sc.score>70 THEN 1 ELSE 0 END) AS "[85-70]",
SUM(CASE WHEN sc.score<=70 AND sc.score>60 THEN 1 ELSE 0 END) AS "[70-60]",
SUM(CASE WHEN sc.score<=60 AND sc.score>0 THEN 1 ELSE 0 END) AS "[60-0]"
FROM sc LEFT JOIN course
ON sc.cid = course.cid
GROUP BY sc.cid;
SELECT a.sid,a.cid,a.score FROM sc a
LEFT JOIN sc b ON a.cid = b.cid AND a.score<b.score
GROUP BY a.cid, a.sid
HAVING COUNT(b.cid)<3
ORDER BY a.cid;
SELECT t1.`CId`,t1.`Cname`, COUNT(t2.sid) COUNT
FROM course t1 LEFT JOIN sc t2
ON t1.`CId`=t2.`CId` GROUP BY t1.`CId`
SELECT cid, COUNT(sid) FROM sc
GROUP BY cid;
SELECT sid,sname
FROM student
WHERE sid IN
(
SELECT sid
FROM sc
GROUP BY sid HAVING COUNT(cid)=2
)
SELECT ssex,COUNT(ssex) num
FROM student
GROUP BY ssex
SELECT *
FROM student
WHERE sname LIKE '%风%'
SELECT sname,COUNT(sname) num
FROM student
GROUP BY sname HAVING COUNT(sname)>=2
SELECT * FROM student WHERE sname IN(
SELECT sname
FROM student
GROUP BY sname HAVING COUNT(sname)>=2)
SELECT *
FROM student
WHERE sage LIKE '1990%'
SELECT *
FROM student
WHERE YEAR(sage)=1990
SELECT cid,AVG(score) AVG
FROM sc
GROUP BY cid ORDER BY AVG DESC , cid ASC
SELECT sc.cid, course.cname, AVG(SC.SCORE) AS average FROM sc, course
WHERE sc.cid = course.cid
GROUP BY sc.cid
ORDER BY average DESC,cid ASC;
SELECT t1.`SId`,t1.`Sname`,AVG(t2.`score`) AVG
FROM student t1,sc t2
WHERE t1.`SId`=t2.`SId` GROUP BY t1.`SId` HAVING AVG(t2.score)>=85
SELECT student.sid, student.sname, AVG(sc.score) AS aver FROM student, sc
WHERE student.sid = sc.sid
GROUP BY sc.sid
HAVING aver >= 85;
SELECT t1.sname,t2.score
FROM student t1,sc t2,course t3
WHERE t1.sid=t2.sid AND t2.cid =t3.cid AND t3.`Cname`='数学' AND t2.score<60
SELECT student.sname, sc.score FROM student, sc, course
WHERE student.sid = sc.sid
AND course.cid = sc.cid
AND course.cname = "数学"
AND sc.score < 60;
SELECT t3.`Sname`,t4.cname,t4.score
FROM student t3 LEFT JOIN
(
SELECT t1.`SId`,t2.`Cname`,t1.`score`
FROM sc t1,course t2
WHERE t1.`CId`=t2.`CId`
) t4
ON t3.`SId`=t4.sid
SELECT student.sname, cid, score FROM student
LEFT JOIN sc
ON student.sid = sc.sid;
SELECT t3.`Sname`,t4.cname,t4.score
FROM student t3 LEFT JOIN
(
SELECT t1.`SId`,t2.`Cname`,t1.`score`
FROM sc t1,course t2
WHERE t1.`CId`=t2.`CId`
) t4
ON t3.`SId`=t4.sid WHERE t4.score>70
SELECT student.sname, course.cname,sc.score FROM student,course,sc
WHERE sc.score>70
AND student.sid = sc.sid
AND sc.cid = course.cid;
SELECT DISTINCT t2.`Cname`
FROM sc t1,course t2
WHERE t1.cid=t2.cid AND t1.`score`<60
SELECT cid FROM sc
WHERE score< 60
GROUP BY cid;
SELECT t1.`SId`,t1.`Sname`
FROM student t1,sc t2
WHERE t1.`SId`=t2.`SId` AND t2.`CId`='01' AND t2.score >=80
SELECT student.sid,student.sname
FROM student,sc
WHERE cid="01"
AND score>=80
AND student.sid = sc.sid;
SELECT t1.`Cname`, COUNT(t2.`SId`) 学生人数
FROM course t1 LEFT JOIN sc t2
ON t1.`CId`= t2.`CId`
GROUP BY t1.`CId`
SELECT sc.CId,COUNT(*) AS 学生人数
FROM sc
GROUP BY sc.CId;
SELECT t1.*,t2.`score`
FROM
student t1,sc t2,course t3,teacher t4
WHERE t1.sid=t2.sid AND t2.cid=t3.cid AND t3.`TId`=t4.`TId` AND t4.tname='张三' HAVING MAX(t2.`score`)
SELECT student.*, sc.score, sc.cid FROM student, teacher, course,sc
WHERE teacher.tid = course.tid
AND sc.sid = student.sid
AND sc.cid = course.cid
AND teacher.tname = "张三"
HAVING MAX(sc.score);
SELECT t1.*,t2.`score`
FROM
student t1,sc t2,course t3,teacher t4
WHERE t1.sid=t2.sid AND t2.cid=t3.cid AND t3.`TId`=t4.`TId` AND t4.tname='张三'
HAVING t2.`score`=
(
SELECT MAX(sc.`score`)
FROM sc , course ,teacher
WHERE sc.`CId`=course.`CId` AND course.`TId`=teacher.`TId` AND teacher.`Tname`='张三'
)
SELECT DISTINCT t1.`SId`, t1.cid,t1.score
FROM sc t1 INNER JOIN sc t2
ON t1.`SId`=t2.`SId`
WHERE t1.cid != t2.`CId` AND t1.score=t2.`score`
SELECT a.cid, a.sid, a.score FROM sc AS a
INNER JOIN
sc AS b
ON a.sid = b.sid
AND a.cid != b.cid
AND a.score = b.score
GROUP BY cid, sid;
SELECT a.sid,a.cid,a.score
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 COUNT(b.cid)<2
ORDER BY a.cid;
SELECT t1.`Cname`,COUNT(t2.sid) num
FROM
course t1 LEFT JOIN sc t2 ON t1.`CId`=t2.`CId`
GROUP BY t1.`CId` HAVING COUNT(t2.`SId`)>5
SELECT sc.cid, COUNT(sid) AS cc FROM sc
GROUP BY cid
HAVING cc >5;
SELECT t1.`SId`
FROM student t1,sc t2
WHERE t1.`SId`=t2.`SId` GROUP BY t1.`SId` HAVING COUNT(t2.cid)>=2
SELECT t1.*
FROM student t1 , sc t2
WHERE t1.`SId`= t2.`SId`
GROUP BY t1.`SId` HAVING COUNT(*)= (SELECT DISTINCT COUNT(*) FROM course)
SELECT student.*
FROM sc ,student
WHERE sc.SId=student.SId
GROUP BY sc.SId
HAVING COUNT(*) = (SELECT DISTINCT COUNT(*) FROM course )
SELECT sid 编号, sname 姓名, (YEAR(CURDATE()) - YEAR(student.`Sage`))年龄
FROM student
SELECT student.SId AS 学生编号,student.Sname AS 学生姓名,
TIMESTAMPDIFF(YEAR,student.Sage,CURDATE()) AS 学生年龄
FROM student
SELECT *
FROM student
WHERE WEEKOFYEAR(student.Sage)=WEEKOFYEAR(CURDATE());
SELECT *
FROM student
WHERE WEEKOFYEAR(student.Sage)=WEEKOFYEAR(CURDATE())+1;
SELECT *
FROM student
WHERE MONTH(student.Sage)=MONTH(CURDATE());
SELECT *
FROM student
WHERE MONTH(student.Sage)=MONTH(CURDATE())+1;
Processed:
0.012, SQL:
9
