sql 语句练习(1)
2019-05-09 本文已影响0人
dongshangtong
网上流传较广的50道SQL训练,我也看简书人写，加上自己写，我只是记录学习过程。
学生表
create table student(sid varchar(10),sname varchar(10),sage datetime,sex 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' , '女');
2.课程表
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');
3.教师表
create table teacher(tid varchar(10),tname varchar(10));
insert into teacher values('01' , '张三');
insert into teacher values('02' , '李四');
insert into teacher values('03' , '王五');
4.成绩表
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);
insert into sc values('13' , '01' , 29);
1.查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
SELECT St.SId, St.Sname, St.Sage, St.Ssex,sc3.score1,sc3.score2 FROM
Student St RIGHT JOIN
(
SELECT sc1.SId,sc1.score1, sc2.score2 FROM
(SELECT SId ,score AS score1 FROM SC CC WHERE CC.CId ='01') sc1,
(SELECT SId ,score AS score2 FROM SC CC WHERE CC.CId ='02') sc2
WHERE sc1.SId = sc2.SId AND sc1.score1 > sc2.score2
) sc3 on St.SId = sc3.SId
1.1 查询同时存在" 01 "课程和" 02 "课程的情况
SELECT sc1.SId,sc1.score1, sc2.score2 FROM
(SELECT SId ,score AS score1 FROM SC CC WHERE CC.CId ='01') sc1,
(SELECT SId ,score AS score2 FROM SC CC WHERE CC.CId ='02') sc2
WHERE sc1.SId = sc2.SId
1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
SELECT sc1.SId,sc1.score1, sc2.score2 FROM
(SELECT SId ,score AS score1 FROM SC CC WHERE CC.CId ='01') sc1
LEFT JOIN
(SELECT SId ,score AS score2 FROM SC CC WHERE CC.CId ='02') sc2
ON sc1.SId = sc2.SId
1.3 查询不存在" 01 "课程但存在" 02 "课程的情况
select * from SC
where SC.SId not in (
select SId from SC
where SC.CId = '01'
)
AND SC.CId= '02';
2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
-- 第一种写法
SELECT st.SId,st.Sname, sc.avgscore FROM Student st RIGHT JOIN
(SELECT sid ,AVG(score) as avgscore FROM SC GROUP BY SId HAVING avgscore > 60) sc
on sc.SId = st.SId
-- 第二种写法
select Student.SId,sname,ss from Student,(
select SId, AVG(score) as ss from SC
GROUP BY SId
HAVING AVG(score)> 60
)r
where Student.sid = r.sid;
3.查询在 SC 表存在成绩的学生信息
-- 第一 效率比较高
SELECT * FROM Student ,
(SELECT SId FROM SC GROUP BY SC.SId) r
WHERE Student.SId = r.SId
-- 第二 DISTINCT效率比不高
select DISTINCT Student.*
from Student,SC
where Student.SId=SC.SId
4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和
-- 第一种
SELECT ST.SId, ST.Sname,cc.ssum ,cc.argscore FROM Student ST RIGHT JOIN
(SELECT SC.SId, SUM(score) as argscore , SUM(CId) as ssum FROM SC GROUP BY SC.SId) cc
ON cc.SId = ST.SId
-- 第二种
select Student.sid, Student.sname,r.coursenumber,r.scoresum
from Student,
(select SC.sid, sum(SC.score) as scoresum, count(SC.cid) as coursenumber from SC
group by SC.sid)r
where Student.sid = r.sid;
4.1 查有成绩的学生信息
-- 第一种
SELECT * FROM Student ST RIGHT JOIN
(
SELECT SC.SId FROM SC GROUP BY SId
) cc
on ST.SId = cc.SId
-- 第二种
SELECT * FROM Student WHERE SId in(SELECT SC.SId FROM SC GROUP BY SId)
-- 第 三种
select * from Student
where exists (select SC.sid from SC where Student.sid = SC.sid);
5.查询「李」姓老师的数量
-- 第一种
SELECT count(1) FROM Teacher WHERE Teacher.Tname LIKE "李%"
-- 第二种
select count(*)
from Teacher
where Tname like '李%';
6.查询学过「张三」老师授课的同学的信息
-- 第一种
SELECT * FROM Student WHERE SId IN (
SELECT SId FROM SC WHERE CId IN (
SELECT CId FROM Course WHERE TId IN (SELECT TId FROM Teacher WHERE 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 = '张三';
7.查询没有学全所有课程的同学的信息
SELECT * FROM Student WHERE Student.SId NOT IN
(
SELECT sc.SId FROM SC sc GROUP BY sc.SId HAVING COUNT(sc.CId) = ( SELECT COUNT(CId) FROM Course)
)
8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
SELECT * FROM Student,
(
SELECT cc.SId FROM SC cc WHERE cc.CId IN(SELECT SC.CId FROM SC WHERE SC.SId ='01') GROUP BY cc.SId HAVING cc.SId !='01'
) rr
WHERE Student.SId =rr.SId
9.查询和" 01 "号的同学学习的课程完全相同
SELECT Student.SId,Student.Sage,Student.Sname,Student.Ssex FROM Student,
-- 得到部分一样
(SELECT cc.SId FROM SC cc WHERE cc.CId IN(SELECT SC.CId FROM SC WHERE SC.SId ='01') GROUP BY cc.SId) t1,
-- 得到总数一样
( SELECT scy.SId , COUNT(scy.CId) as cccid FROM SC scy GROUP BY scy.SId HAVING cccid = (SELECT COUNT(ss.CId) FROM SC ss WHERE ss.SId ='01')) t2
WHERE Student.SId = t1.SId AND Student.SId = t2.SId AND t1.SId = t2.SId
10.查询没学过"张三"老师讲授的任一门课程的学生姓名
-- SELECT TId FROM Teacher WHERE Tname ='张三'
-- SELECT CId FROM Course WHERE TId = (SELECT TId FROM Teacher WHERE Tname ='张三')
-- SELECT SId FROM SC WHERE CId = (SELECT CId FROM Course WHERE TId = (SELECT TId FROM Teacher WHERE Tname ='张三'))
--
SELECT * FROM Student WHERE Student.SId not IN(
SELECT SId FROM SC WHERE CId = (
SELECT CId FROM Course WHERE TId = (
SELECT TId FROM Teacher WHERE Tname ='张三'
)
)
)
11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT st.SId, st.Sname, tt.avg_score FROM student st,
(SELECT cc.sid, COUNT(cc.score) as count_score, AVG(cc.score) as avg_score FROM sc cc WHERE cc.score < 60 GROUP BY cc.sid HAVING count_score > 1) tt
WHERE st.sid = tt.sid
12.检索" 01 "课程分数小于 60，按分数降序排列的学生信息
SELECT st.SId ,st.Sname, tt.score FROM student st ,
(SELECT cc.sid, cc.score FROM sc cc WHERE cc.score < 60 AND cc.cid ='01' ORDER BY cc.score DESC) tt
WHERE st.SId = tt.SId
13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT ss.SId,ss.cid, ss.score, tt.avg_score FROM sc ss ,
(SELECT cc.sid , AVG(cc.score) as avg_score FROM sc cc GROUP BY cc.sid ) tt
WHERE ss.SId = tt.SId ORDER BY tt.avg_score DESC
14.查询各科成绩最高分、最低分和平均分,以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT
cid,
MAX(score) as 最高分,
MIN(score) as 最低分,
AVG(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
GROUP BY sc.cid
ORDER BY count(*)DESC , cid asc
15.按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
SELECT a.cid, a.sid, a.score, count(b.score)+1 as ranks
FROM sc a
LEFT JOIN sc b
on a.score < b.score and a.cid = b.cid
GROUP BY a.cid, a.sid, a.score
ORDER BY a.cid , ranks ASC
16.查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
set @cranks=0;
select q.sid, total, @cranks := @cranks +1 as ranks from(
select sc.sid, sum(sc.score) as total from sc
group by sc.sid
order by total desc) q;
