Sql练习
1，每日经典sql:
1）根据三张关系表查询雇员中工资最高的雇员的员工姓名、工资和部门号。
salary(工资表) employee(员工表) department（部门表）
SELECT
emp.ename AS '姓名',
sal.sal AS '薪资',
dep.depname AS '部门'
FROM
employee AS emp
INNER JOIN salary AS sal
INNER JOIN department dep ON dep.salid = sal.salid
AND dep.empid = emp.empid
AND sal.sal = ( SELECT max( sal ) FROM salary )
2)找出各月倒数第3天受雇的所有员工：
SELECT
*
FROM
emp
WHERE --关键点:last_day()函数返回日期的最后一天
hiredate = last_day( hiredate ) -2
3)显示所有员工的姓名,用a替换所有"A"
SELECT REPLACE --replace函数 替换一些字符
( ename, 'A', 'a' ) name,
job,
hiredate
FROM
emp;
4）显示所有员工的姓名、加入公司的年份和月份,按受雇日期所在月排序,若月份相同则将最早年份的员工排在最前面:
SELECT
ename,
--t o_char函数是用来截取时间的 to_char ( hiredate, 'yyyy' ) YEAR,
to_char ( hiredate, 'mm' ) months
FROM
emp
ORDER BY
months,
YEAR ASC;
5)显示姓名字段的任何位置包含"A"的所有员工的姓名.
SELECT -- racle instr函数返回要截取的字符串在源字符串中的位置。只检索一次，也就是说从字符的开始到字符的结尾就结束。
ename
FROM
emp
WHERE
instr( ename, 'A', 1 ) > 0;
6)列出薪金比“SMITH”多的所有员工. --upper函数 小写字符转化成大写的函数:
SELECT
ename,
sal
FROM
emp
WHERE
sal > ( SELECT sal FROM emp WHERE upper( ename ) = 'SMITH' );
7)列出受雇日期早于其直接上级的所有员工：
SELECT
e.ename,
m.ename
FROM
emp e,
emp m
WHERE
e.mgr = m.empno
AND ( e.hiredate < m.hiredate );
2，50道经典的sql题:
建表sql语句:https://pan.baidu.com/s/1BTHg7at7eZbDpU4McrrFvQ 提取码：v2hz
1)查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
SELECT
sc.s_id AS '学生id',
sc.s_score AS '成绩'
FROM
( SELECT * FROM score WHERE c_id = "01" ) AS sc
INNER JOIN ( SELECT * FROM score WHERE c_id = "02" ) AS sco ON sc.s_id = sco.s_id
WHERE
sc.s_score > sco.s_score
2)查询同时存在" 01 "课程和" 02 "课程的情况
SELECT s.s_id AS '学生id' FROM score s
WHERE s.c_id="01" AND s.s_id IN(SELECT s1.s_id FROM score s1 WHERE s1.c_id ="02")
3)查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
SELECT
s.c_id AS '01课程id',
s.s_id AS '01课程的学生id',
sc.c_id AS '02课程id',
sc.s_id AS '02课程的学生id'
FROM
(SELECT * FROM score WHERE c_id = "01")AS s
LEFT JOIN
(SELECT * FROM score WHERE c_id = "02")AS sc ON s.s_id = sc.s_id
4)查询不存在" 01 "课程但存在" 02 "课程的情况
SELECT * FROM score s WHERE s.c_id = "02" AND s.s_id
NOT IN (SELECT s1.s_id FROM score s1 WHERE s1.c_id = "01")
5)查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
SELECT
s.s_id,
st.s_name,
AVG( s.s_score )
FROM
score s
INNER JOIN student st ON s.s_id = st.s_id
GROUP BY
s.s_id
HAVING
(
AVG( s.s_score )>= 60)
6)查询在 SC 表存在成绩的学生信息
-- 也可以用内连接
SELECT * FROM student s WHERE s.s_id IN (SELECT s_id FROM score s GROUP BY s_id)
7)查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
SELECT
st.s_id,
st.s_name,
COUNT( sc.c_id ) AS '选课数',
SUM( sc.s_score ) AS '总成绩'
FROM
student st
INNER JOIN score sc ON st.s_id = sc.s_id
GROUP BY
st.s_id
8)查有成绩的学生信息
SELECT * FROM student st WHERE st.s_id IN(SELECT sc.s_id FROM score sc)
9)查询「李」姓老师的数量
SELECT COUNT(1) AS '数量' FROM teacher t WHERE t.t_name LIKE '李%'
10)查询学过「张三」老师授课的同学的信息
SELECT
*
FROM
student st
WHERE
st.s_id IN (
SELECT
s.s_id
FROM
score s
WHERE
s.c_id IN (
SELECT
c.c_id
FROM
course c
WHERE
c.t_id IN ( SELECT t.t_id FROM teacher t WHERE t.t_name = '张三' )))
11)查询没有学全所有课程的同学的信息
SELECT
*
FROM
student st,
(
SELECT
s.s_id
FROM
score s
GROUP BY
s.s_id
HAVING
(
count( s.s_score )<(
SELECT
count( 1 )
FROM
course
))) AS sc
WHERE
st.s_id = sc.s_id
12)查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
SELECT
*
FROM
student st
WHERE
st.s_id IN (
SELECT
sc.s_id
FROM
score sc,
( SELECT c_id FROM score WHERE s_id = '01' ) AS s
WHERE
s_id <> '01'
AND sc.c_id = s.c_id
GROUP BY
s_id)
13)查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
SELECT
s_id
FROM
score
WHERE
s_id != 01
AND c_id IN ( SELECT c_id FROM score WHERE s_id = 01 )
GROUP BY
s_id
HAVING
(
count( c_id ) = ( SELECT count( 1 ) FROM score WHERE s_id = 01 ))
14)查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT
s_id
FROM
score
WHERE
s_id NOT IN (
SELECT
s_id
FROM
score
WHERE
c_id IN (
SELECT
c_id
FROM
course
WHERE
t_id = ( SELECT t_id FROM teacher WHERE t_name = '张三' ))
GROUP BY
s_id
)
GROUP BY
s_id
15)查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT
st.s_id,
st.s_name,
AVG( sco.s_score )
FROM
student st,
(
SELECT
sc.s_id,
sc.s_score
FROM
score sc
GROUP BY
sc.s_id,
sc.c_id
HAVING
( sc.s_score < 60 )) sco
WHERE
st.s_id = sco.s_id
GROUP BY
s_id
-----------------------------或者这样做也行---------------------------------------------------
SELECT
s_id,
avg(s_score)
FROM
score
GROUP BY
s_id
HAVING
(
sum( s_score < 60 ) >= 2)
16)检索" 01 "课程分数小于 60，按分数降序排列的学生信息
SELECT
*
FROM
student AS st,
( SELECT s_score, s_id FROM score WHERE c_id = 01 AND s_score < 60 ORDER BY s_score DESC ) AS sc
WHERE
st.s_id = sc.s_id
17)按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT
s.s_id,
s.s_name,
s.s_birth,
s.s_sex,
s.s_score
FROM
(
SELECT
*
FROM
student AS st,
( SELECT s_id AS st_id, avg( s_score ) AS s_score FROM score GROUP BY s_id) sc
WHERE
st.s_id = sc.st_id
) s ORDER BY s.s_score desc
18)查询各科成绩最高分、最低分和平均分：以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT
c_id AS '选修课号',
count( s_id ) AS '选修人数',
max( s_score ) AS '最高分',
min( s_score ) AS '最低分',
avg( s_score ) AS '平均分',
sum(s_score >= 60) / count( s_id ) * 100 AS '及格率',
sum(s_score >= 70 && s_score <= 80) / count( s_id ) * 100 AS '中等率',
sum(s_score >= 80 && s_score <= 90) / count( s_id ) * 100 AS '优良率',
sum(s_score >= 90) / count( s_id ) * 100 AS '优秀率'
FROM
score sc
GROUP BY
C_ID ORDER BY '选修人数' desc,'选修课号' asc
19)按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
SELECT
s.c_id AS '学生编号',
s.c_name AS '科目',
s.s_score AS '成绩',
s.rank AS '排名' FROM (
SELECT
s.c_id,
s.c_name,
s.s_score,
@curRank := IF(@prevRank = s.s_score,@curRank,@incRank) AS rank,@incRank := @incRank +1,@prevRank := s.s_score
FROM (SELECT cr.c_id,cr.c_name,sc.s_score FROM course cr,
(SELECT * FROM score WHERE c_id in(SELECT c_id FROM score GROUP BY c_id)) sc WHERE cr.c_id = sc.c_id) s,(SELECT @curRank :=0,@prevRank := NULL,@incRank := 1)r ORDER BY s.s_score desc) s
20)按各科成绩进行排序，并显示排名， Score 重复时合并名次
SELECT
s.c_id AS '学生编号',
s.c_name AS '科目',
s.s_score AS '成绩',
CASE
WHEN @prevRank = s.s_score THEN @curRank
WHEN @prevRank := s.s_score THEN @curRank := @curRank + 1
END AS '排名'
FROM (SELECT cr.c_id,cr.c_name,sc.s_score FROM course cr,
(SELECT * FROM score WHERE c_id in(SELECT c_id FROM score GROUP BY c_id)) sc WHERE cr.c_id = sc.c_id) s,(SELECT @curRank := 0,@prevRank := NULL)q ORDER BY s.s_score desc
21)查询学生的总成绩，并进行排名，总分重复时保留名次空缺
SELECT
s.s_name,
s.s_score AS '总成绩',
s.rank AS '排名'
FROM (
SELECT
s.s_name,
s.s_score,
@curRank := IF(@prevRank = s.s_score,@curRank,@incRank) AS rank,@incRank := @incRank +1,@prevRank := s.s_score
FROM (SELECT st.s_name,sum(sc.s_score) as 's_score' FROM score sc,
(SELECT s_id,s_name FROM student) st WHERE st.s_id = sc.s_id GROUP BY sc.s_id) s,(SELECT @curRank :=0,@prevRank := NULL,@incRank := 1)r ORDER BY s.s_score desc)s
22)查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
SELECT
s.s_name,
s.s_score,
@curRank := @curRank +1 as '不保留名次空缺排名'
FROM (SELECT st.s_name,sum(sc.s_score) as 's_score' FROM score sc,
(SELECT s_id,s_name FROM student) st WHERE st.s_id = sc.s_id GROUP BY sc.s_id) s,(SELECT @curRank := 0)r ORDER BY s.s_score desc
23)统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
SELECT #我做法好笨,我想不出来更好的方法,不过我感觉应该有
co.c_id,
co.c_name,
sum(sc.s_score >= 0 && sc.s_score <= 60 ) AS '[60-0]人数',
sum(sc.s_score >= 0 && sc.s_score <= 60 ) / (select count(1) from score WHERE s_score <= 60) * 100 AS '[60-0]所占百分比',
sum(sc.s_score >= 60 && sc.s_score <= 70 ) AS '[70-60]人数',
sum(sc.s_score >= 60 && sc.s_score <= 70 ) / (select count(1) from score WHERE s_score >= 60 AND s_score <= 70) * 100 AS '[70-60]所占百分比',
sum(sc.s_score >= 70 && sc.s_score <= 85 ) AS '[85-70]人数',
sum(sc.s_score >= 70 && sc.s_score <= 85 ) / (select count(1) from score WHERE s_score >= 70 AND s_score <= 85) * 100 AS '[85-70]所占百分比',
sum(sc.s_score >= 85 && sc.s_score <= 100 ) AS '[100-85]人数',
sum(sc.s_score >= 85 && sc.s_score <= 100) / (select count(1) from score WHERE s_score >= 85 AND s_score <= 100) * 100 AS '[100-85]所占百分比'
FROM
course co,
score sc
WHERE
co.c_id = sc.c_id
GROUP BY
co.c_id
24)查询各科成绩前三名的记录
SELECT #看的网上别人做的：遍历所有记录，取每条记录与当前记录做比较，只有当score表中同一科目不超过3个人分数 比自己高时，这个学生才算是分数排行的前三名。也就是括号里面是查询在这一班级中分数比他高的人的数量。
*
FROM
score sc
WHERE
( SELECT count(*) FROM score c WHERE sc.c_id = c.c_id AND sc.s_score < c.s_score ) < 3
ORDER BY
sc.c_id,
sc.s_score DESC
25)查询每门课程被选修的学生数
SELECT
count( 1 ) AS '每门课程被选修的学生数'
FROM
(
SELECT
*
FROM
score
WHERE
c_id IN ( SELECT c_id FROM course )
GROUP BY
s_id
HAVING
(count( s_id ) = ( SELECT count( 1 ) FROM course ))) t
26)查询出只选修两门课程的学生学号和姓名
SELECT st.s_name,st.s_id FROM student st
INNER JOIN
score sc
ON st.s_id = sc.s_id GROUP BY st.s_name,st.s_id HAVING(count(1) = 2 )
27)查询男生、女生人数
SELECT s_sex,count(1) as 人数 FROM student GROUP BY s_sex
28)查询名字中含有「风」字的学生信息
select * FROM student where s_name LIKE '%风%'
29)查询同名同性学生名单，并统计同名人数
SELECT s_name,s_sex,COUNT(1) as '人数' FROM student GROUP BY s_name,s_sex HAVING(COUNT(1) = 2)
30)查询 1990 年出生的学生名单
SELECT * FROM student WHERE DATE_FORMAT(s_birth,'%Y') = DATE_FORMAT('1990-01-01','%Y')
31)查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
SELECT avg(s_score) as '成绩' FROM score GROUP BY c_id ORDER BY avg(s_score) desc,c_id asc
32)查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
SELECT st.s_id,st.s_name,avg(sc.s_score) as '平均成绩' FROM student st
INNER JOIN
score sc ON st.s_id = sc.s_id GROUP BY s_id,s_name HAVING(avg(s_score) > 85)
33)查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
SELECT sc.s_id,st.s_name,sc.s_score FROM course co
INNER JOIN
score sc
ON co.c_name ='数学'
AND co.c_id = sc.c_id
AND sc.s_score < 60
INNER JOIN
student st
ON sc.s_id = st.s_id
34)查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
SELECT co.c_name,st.s_name,sc.s_score FROM student st
LEFT OUTER JOIN
score sc
ON st.s_id = sc.s_id
LEFT OUTER JOIN
course co
ON sc.c_id = co.c_id
35)查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
SELECT co.c_name,st.s_id,st.s_name,sc.s_score FROM student st -- 这种写法在mysql高版本不支持了,展示的字段不能比分组的多
INNER JOIN
score sc
ON st.s_id = sc.s_id
INNER JOIN
course co
ON co.c_id = sc.c_id GROUP BY s_id,s_name HAVING(sc.s_score > 70)
36)查询不及格的课程
SELECT DISTINCT co.c_id,co.c_name FROM score sc -- 有点不是很明白题意,反正就按照含有不及格的课程去做了
INNER JOIN
course co
ON sc.s_score < 60
AND sc.c_id = co.c_id
37)查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
SELECT st.s_id,st.s_name,sc.s_score FROM score sc
INNER JOIN
student st
on sc.s_score > 80
and sc.c_id = 01
and sc.s_id = st.s_id
38)求每门课程的学生人数
SELECT co.c_name,sc.student_count FROM
(SELECT c_id,count(s_id)as student_count FROM score GROUP BY c_id) sc
INNER JOIN
course co
ON sc.c_id = co.c_id
39)成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECT st.s_name,sc.s_score FROM course co
INNER JOIN
teacher t
ON t.t_id = co.t_id
AND t.t_name = '张三'
INNER JOIN
score sc
ON co.c_id = sc.c_id
INNER JOIN
student st
ON st.s_id = sc.s_id ORDER BY sc.s_score desc limit 1
40)成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECT st.s_name,t.c_name,sc.s_score FROM
score sc
INNER JOIN
(SELECT co.c_id,co.c_name,max(sc.s_score) as s_score FROM course co
INNER JOIN
teacher t
ON t.t_id = co.t_id
AND t.t_name = '张三'
INNER JOIN
score sc
ON co.c_id = sc.c_id
) t
ON sc.c_id = t.c_id
AND sc.s_score = t.s_score
INNER JOIN
student st
ON st.s_id = sc.s_id
41)查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT DISTINCT st.s_name,co.c_name,sc.s_score FROM score sc
INNER JOIN
score sc_t
ON sc.c_id <> sc_t.c_id
AND sc.s_score = sc_t.s_score
INNER JOIN
student st
ON st.s_id = sc.s_id
INNER JOIN
course co
ON co.c_id = sc.c_id
42)查询每门功成绩最好的前两名
SELECT * FROM score sc --从网上看的 具体原理不是很懂也
WHERE(SELECT count(1) FROM score s WHERE sc.c_id = s.c_id AND sc.s_score < s.s_score ) < 2
ORDER BY sc.c_id,sc.s_score DESC;
43)统计每门课程的学生选修人数（超过 5 人的课程才统计）。
SELECT sc.c_id as '课号',count(1) '选修人数' FROM score sc
GROUP BY c_id HAVING(count(1) > 5)
44)检索至少选修两门课程的学生学号
SELECT st.s_id,st.s_name FROM student st
WHERE s_id in(SELECT sc.s_id FROM score sc GROUP BY sc.s_id HAVING(count(1) >= 2))
45)查询选修了全部课程的学生信息
SELECT st.s_id,st.s_name FROM student st
WHERE s_id in(SELECT sc.s_id FROM score sc GROUP BY sc.s_id HAVING(count(1) >= (SELECT count(1) FROM course)))
46)查询各学生的年龄，只按年份来算
SELECT st.s_id,st.s_name,DATE_FORMAT(NOW(),'%Y') - DATE_FORMAT(st.s_birth,'%Y') as '年龄' FROM student st
47)按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
SELECT st.s_id,st.s_name,TIMESTAMPDIFF(YEAR, st.s_birth, CURDATE()) FROM student st
48)查询本周过生日的学生
SELECT * FROM student st where WEEK(now()) = WEEK(date_format(st.s_birth,'%Y-%m-%d'))
49)查询下周过生日的学生
SELECT * FROM student st where WEEK(date_add(now(), interval 1 week)) = WEEK(date_format(st.s_birth,'%Y-%m-%d'))
50)查询本月过生日的学生
SELECT * FROM student st where date_format(now(),'%Y-%m') = date_format( st.s_birth,'%Y-%m')
51)查询下月过生日的学生
SELECT * FROM student st where date_format(date_add(now(), interval 1 month),'%Y-%m') = date_format( st.s_birth,'%Y-%m')
作者： 屢敗屢戰
出处： https://www.cnblogs.com/joeyJss/
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文链接;
如有问题可咨询邮箱： 1824293259@qq.com
