sql 练习
今天去面试发现自己的sql很差,多以打算恶补一下.
建表:
DROP TABLE IF EXISTS `course`; CREATE TABLE `course` ( `Cno` varchar(10) DEFAULT NULL, `Cname` varchar(10) DEFAULT NULL, `Tno` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of course -- ---------------------------- INSERT INTO `course` VALUES ('01', '语文', '02'); INSERT INTO `course` VALUES ('02', '数学', '01'); INSERT INTO `course` VALUES ('03', '英语', '03'); -- ---------------------------- -- Table structure for sc -- ---------------------------- DROP TABLE IF EXISTS `sc`; CREATE TABLE `sc` ( `Sno` varchar(10) DEFAULT NULL, `Cno` varchar(10) DEFAULT NULL, `score` decimal(18,1) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of sc -- ---------------------------- INSERT INTO `sc` VALUES ('01', '01', '80.0'); INSERT INTO `sc` VALUES ('01', '02', '91.0'); INSERT INTO `sc` VALUES ('01', '03', '99.0'); INSERT INTO `sc` VALUES ('02', '01', '70.0'); INSERT INTO `sc` VALUES ('02', '02', '60.0'); INSERT INTO `sc` VALUES ('02', '03', '80.0'); INSERT INTO `sc` VALUES ('03', '01', '81.0'); INSERT INTO `sc` VALUES ('03', '02', '82.0'); INSERT INTO `sc` VALUES ('03', '03', '79.0'); INSERT INTO `sc` VALUES ('04', '01', '50.0'); INSERT INTO `sc` VALUES ('04', '02', '30.0'); INSERT INTO `sc` VALUES ('04', '03', '20.0'); INSERT INTO `sc` VALUES ('05', '01', '76.0'); INSERT INTO `sc` VALUES ('05', '02', '87.0'); INSERT INTO `sc` VALUES ('06', '01', '31.0'); INSERT INTO `sc` VALUES ('06', '03', '34.0'); INSERT INTO `sc` VALUES ('07', '02', '89.0'); INSERT INTO `sc` VALUES ('07', '03', '98.0'); -- ---------------------------- -- Table structure for student -- ---------------------------- DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `Sno` varchar(10) DEFAULT NULL, `Sname` varchar(10) DEFAULT NULL, `Sage` datetime DEFAULT NULL, `Ssex` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of student -- ---------------------------- INSERT INTO `student` VALUES ('01', '赵雷', '1990-01-01 00:00:00', '男'); INSERT INTO `student` VALUES ('02', '钱电', '1990-12-21 00:00:00', '男'); INSERT INTO `student` VALUES ('03', '孙风', '1990-05-20 00:00:00', '男'); INSERT INTO `student` VALUES ('04', '李云', '1990-08-06 00:00:00', '男'); INSERT INTO `student` VALUES ('05', '周梅', '1991-12-01 00:00:00', '女'); INSERT INTO `student` VALUES ('06', '吴兰', '1992-03-01 00:00:00', '女'); INSERT INTO `student` VALUES ('07', '郑竹', '1989-07-01 00:00:00', '女'); INSERT INTO `student` VALUES ('08', '王菊', '1990-01-20 00:00:00', '女'); INSERT INTO `student` VALUES ('09', '李云', '1992-10-01 10:34:18', '男'); -- ---------------------------- -- Table structure for teacher -- ---------------------------- DROP TABLE IF EXISTS `teacher`; CREATE TABLE `teacher` ( `Tno` varchar(10) DEFAULT NULL, `Tname` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of teacher -- ---------------------------- INSERT INTO `teacher` VALUES ('01', '张三'); INSERT INTO `teacher` VALUES ('02', '李四'); INSERT INTO `teacher` VALUES ('03', '王五');
sql:
-- 1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数 SELECT * FROM ( SELECT * FROM sc WHERE sc.Cno = '01' ) sc1 JOIN ( SELECT * FROM sc WHERE sc.Cno = '02' ) sc2 ON sc1.sno = sc2.sno WHERE sc1.score > sc2.score; -- 1.1 查询同时存在" 01 "课程和" 02 "课程的情况 SELECT * FROM ( SELECT * FROM sc WHERE sc.Cno = '01' ) sc1 LEFT JOIN ( SELECT * FROM sc WHERE sc.Cno = '02' ) sc2 ON sc1.sno = sc2.sno WHERE sc2.sno IS NOT NULL; -- 1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null ) SELECT * FROM ( SELECT * FROM sc WHERE sc.Cno = '01' ) sc1 LEFT JOIN ( SELECT * FROM sc WHERE sc.Cno = '02' ) sc2 ON sc1.sno = sc2.sno; -- 查询不存在" 01 "课程但存在" 02 "课程的情况 SELECT * FROM sc WHERE sc.Cno = '02' AND sc.Sno NOT IN ( SELECT sc.sno FROM sc WHERE sc.Cno = '01' ); -- 2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩 SELECT st.sno, st.Sname, sc2.score FROM student st JOIN ( SELECT sc.sno, AVG(sc.score) AS score FROM sc GROUP BY sc.sno HAVING AVG(sc.score) >= 60 ) sc2 ON sc2.sno = st.Sno; -- 3. 查询在 SC 表存在成绩的学生信息 SELECT * FROM student WHERE student.Sno IN (SELECT sno FROM sc GROUP BY sno); SELECT * FROM student WHERE student.Sno IN (SELECT DISTINCT sno FROM sc); SELECT st.* FROM student st WHERE EXISTS ( SELECT * FROM sc s WHERE s.sno = st.sno ); -- 4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为null) SELECT A.Sno AS 学生编号, A.Sname AS 学生姓名, B.`选课数量`, B.`总成绩` FROM student AS A LEFT JOIN ( SELECT sc.Sno, COUNT(sc.score) AS 选课数量, SUM(sc.score) AS 总成绩 FROM sc GROUP BY sc.Sno ) AS B ON A.Sno = B.Sno; -- 4.1 查有成绩的学生信息 SELECT * FROM student S JOIN ( SELECT sc.Sno FROM sc GROUP BY sc.Sno ) A ON S.Sno = A.Sno; SELECT * FROM student s WHERE EXISTS ( SELECT * FROM ( SELECT sc.Sno FROM sc GROUP BY sc.Sno ) A WHERE s.sno = A.sno ); -- 5.查询「李」姓老师的数量 SELECT count(*) FROM teacher WHERE Tname LIKE '李%'; -- 6.查询学过「张三」老师授课的同学的信息 SELECT * FROM student WHERE student.Sno IN ( SELECT Sno FROM sc WHERE sc.Cno IN ( SELECT Cno FROM course WHERE course.Tno IN ( SELECT Tno FROM teacher WHERE Tname = '张三' ) ) ) -- 7.查询没有学全所有课程的同学的信息 SELECT student.* FROM student LEFT JOIN (SELECT sno FROM sc GROUP BY sno) s1 ON s1.sno = student.Sno WHERE s1.sno IS NULL -- 查出没学的 UNION SELECT student.* FROM student JOIN ( SELECT sno FROM sc GROUP BY sno HAVING COUNT(1) < (SELECT COUNT(1) FROM course) ) s2 ON s2.sno = student.Sno; -- 查出没学全的 -- 8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息 SELECT * FROM student WHERE student.Sno IN ( SELECT DISTINCT sc.Sno FROM sc WHERE sc.Cno IN ( SELECT cno FROM sc WHERE sc.Sno = '01' ) ); -- 9. 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息 SELECT * FROM Student WHERE Sno IN ( SELECT Sno FROM SC WHERE Cno IN ( SELECT DISTINCT Cno FROM SC WHERE Sno = '01' ) AND Sno <> '01' GROUP BY Sno HAVING COUNT(Cno) >= 3 ); -- 10. 查询没学过「张三」老师讲授的任一门课程的学生姓名 SELECT * FROM student WHERE student.Sno NOT IN ( SELECT sno FROM sc WHERE sc.Cno IN ( SELECT course.cno FROM course WHERE course.Tno = ( SELECT tno FROM teacher WHERE teacher.Tname = '张三' ) ) ); -- 11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 SELECT student.*, s2.avg FROM student JOIN ( SELECT sc.Sno, AVG(sc.score) AS avg FROM sc WHERE sc.score < 60 GROUP BY sc.Sno HAVING COUNT(1) >= 2 ) s2 ON s2.Sno = student.Sno -- 12.检索" 01 "课程分数小于 60 ，按分数降序排列的学生信息 SELECT * FROM sc WHERE sc.Cno = '01' AND sc.score < 60 ORDER BY sc.score DESC; -- 13. （静态写法）按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩 SELECT info.sno AS 学号, info.Sname AS 学生姓名, SUM( CASE info.Cname WHEN '语文' THEN info.score END ) AS 语文, SUM( CASE info.Cname WHEN '数学' THEN info.score END ) AS 数学, SUM( CASE info.Cname WHEN '英语' THEN info.score END ) AS 英语, SUM(info.score)/3 AS 平均分 FROM ( SELECT student.sno, student.Sname, course.Cname, sc.score FROM sc JOIN student ON sc.Sno = student.Sno JOIN course ON sc.Cno = course.Cno ) AS info GROUP BY info.sno -- 19. 查询每门课程被选修的学生数 select sc.Cno ,count(1) from sc group by sc.Cno ; -- 20. 查询出只选修两门课程的学生学号和姓名 SELECT * FROM student WHERE EXISTS ( SELECT * FROM ( SELECT sc.Sno FROM sc GROUP BY sc.Sno HAVING COUNT(1) = 2 ) a WHERE a.Sno = student.Sno ); SELECT * FROM student WHERE student.Sno IN ( SELECT sc.Sno FROM sc GROUP BY sc.Sno HAVING COUNT(1) = 2 ); -- 21. 查询男生、女生人数 select Ssex,COUNT(Ssex)人数 from Student group by Ssex ; -- 22. 查询名字中含有「风」字的学生信息 select * from student where student.Sname like '%风%'; -- 23. 查询同名同性学生名单，并统计同名人数 SELECT Sname, Ssex, COUNT(*) 同名人数 FROM Student GROUP BY Sname, Ssex HAVING COUNT(*) > 1; -- 24.查询 1990 年出生的学生名单 select * from student where year(student.Sage)='1990'; -- 25. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列 select sc.Cno,AVG(sc.score) as score from sc GROUP BY sc.Cno ORDER BY score desc ,cno asc; -- 26. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩 select student.sno,student.Sname,a.scoreavg from student join( select sc.Sno,AVG(sc.score) as scoreavg from sc group by sc.Sno HAVING AVG(sc.score)>=85 )a on a.Sno=student.Sno; -- 27. 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数 SELECT student.Sno, student.Sname, course.Cname, sc.score FROM sc LEFT JOIN student ON student.Sno = sc.sno LEFT JOIN course ON course.Cno=sc.Cno WHERE sc.score < 60 AND EXISTS ( SELECT * FROM ( SELECT * FROM course WHERE course.Cname = '数学' ) a WHERE a.cno = sc.Cno ); -- 28. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况） SELECT student.sno, student.Sname, course.Cname, sc.score FROM student LEFT JOIN sc ON sc.Sno = student.Sno LEFT JOIN course ON course.Cno = sc.Cno; -- 29. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数 select * from sc where EXISTS (select * from (select sno from sc where sc.score>=80 GROUP BY sc.Sno HAVING COUNT(1)>=(select count(1) from course)) a where a.sno= sc.sno ); -- 30. 查询不及格的课程 select course.Cname from sc join course on course.Cno=sc.Cno where sc.score<60 GROUP BY course.Cname; -- 31. 查询课程编号为01且课程成绩在80分以上的学生的学号和姓名 SELECT student.Sno, student.Sname, a.score FROM student JOIN ( SELECT * FROM sc WHERE sc.Cno = '01' AND sc.score >= 80 ) a ON a.sno = student.sno -- 32. 求每门课程的学生人数 select sc.cno as 课程编号 ,COUNT(1) as 人数 from sc GROUP BY sc.Cno -- 33.选修张三老师的课程的学生中,成绩最高 SELECT student.Sname, sc.score FROM sc JOIN student ON student.sno = sc.Sno WHERE sc.score = ( SELECT MAX(sc.score) FROM sc WHERE sc.Cno IN ( SELECT cno FROM course WHERE course.Tno = ( SELECT tno FROM teacher WHERE teacher.Tname = '张三' ) ) ); -- 37.统计每门课程的学生选修人数（超过5人的课程才统计）。 -- 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列 select sc.cno,COUNT(1) as num from sc group by sc.Cno HAVING COUNT(1)>5 order by num desc ,sc.cno ; -- 38. 检索至少选修两门课程的学生学号 select sc.Sno,COUNT(1) from sc group by sc.Sno HAVING COUNT(1)>2; -- 39. 查询选修了全部课程的学生信息 select sc.Sno,COUNT(1) from sc group by sc.Sno HAVING COUNT(1)=(select COUNT(1) from course); -- 40. 查询各学生的年龄，只按年份来算 select student.sno,student.Sname,(YEAR(SYSDATE())-YEAR(student.Sage)) as age from student
