SQL 练习题

  cheney

    SQL 的想法和一般的编程语言想法不太一样。写简单的 sql 容易,写复杂点的 sql 脑子不清楚。而且 sql 还很难维护。还没有找到正确的学习 sql 的方法。入门的的书是 《sql 必知必会》,知识点应该都覆盖了,感觉内容有点老。关于 sql 优化的部分还没有考虑。

    这是网上的一组练习题,整理和学习一下。

    SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Table structure for course
    -- ----------------------------
    DROP TABLE IF EXISTS `course`;
    CREATE TABLE `course` (
      `cno` varchar(10) NOT NULL DEFAULT '',
      `cname` varchar(20) NOT NULL DEFAULT '',
      `tno` varchar(20) DEFAULT NULL,
      PRIMARY KEY (`cno`,`cname`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of course
    -- ----------------------------
    INSERT INTO `course` VALUES ('c001', 'J2SE', 't002');
    INSERT INTO `course` VALUES ('c002', 'Java Web', 't002');
    INSERT INTO `course` VALUES ('c003', 'SSH', 't001');
    INSERT INTO `course` VALUES ('c004', 'Oracle', 't001');
    INSERT INTO `course` VALUES ('c005', 'SQL SERVER 2005', 't003');
    INSERT INTO `course` VALUES ('c006', 'C#', 't003');
    INSERT INTO `course` VALUES ('c007', 'JavaScript', 't002');
    INSERT INTO `course` VALUES ('c008', 'DIV+CSS', 't001');
    INSERT INTO `course` VALUES ('c009', 'PHP', 't003');
    INSERT INTO `course` VALUES ('c010', 'EJB3.0', 't002');
    
    -- ----------------------------
    -- Table structure for sc
    -- ----------------------------
    DROP TABLE IF EXISTS `sc`;
    CREATE TABLE `sc` (
      `sno` varchar(10) NOT NULL DEFAULT '',
      `cno` varchar(10) NOT NULL DEFAULT '',
      `score` float(4,2) DEFAULT NULL,
      PRIMARY KEY (`sno`,`cno`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of sc
    -- ----------------------------
    INSERT INTO `sc` VALUES ('s001', 'c001', '78.90');
    INSERT INTO `sc` VALUES ('s002', 'c001', '80.90');
    INSERT INTO `sc` VALUES ('s003', 'c001', '81.90');
    INSERT INTO `sc` VALUES ('s004', 'c001', '60.90');
    INSERT INTO `sc` VALUES ('s001', 'c002', '82.90');
    INSERT INTO `sc` VALUES ('s002', 'c002', '72.90');
    INSERT INTO `sc` VALUES ('s003', 'c002', '81.90');
    INSERT INTO `sc` VALUES ('s001', 'c003', '59.00');
    
    -- ----------------------------
    -- Table structure for student
    -- ----------------------------
    DROP TABLE IF EXISTS `student`;
    CREATE TABLE `student` (
      `sno` varchar(10) NOT NULL,
      `sname` varchar(20) DEFAULT NULL,
      `sage` int(2) DEFAULT NULL,
      `ssex` varchar(5) DEFAULT NULL,
      PRIMARY KEY (`sno`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of student
    -- ----------------------------
    INSERT INTO `student` VALUES ('s001', '张三', '23', '男');
    INSERT INTO `student` VALUES ('s002', '李四', '23', '男');
    INSERT INTO `student` VALUES ('s003', '吴鹏', '25', '男');
    INSERT INTO `student` VALUES ('s004', '琴沁', '20', '女');
    INSERT INTO `student` VALUES ('s005', '王丽', '20', '女');
    INSERT INTO `student` VALUES ('s006', '李波', '21', '男');
    INSERT INTO `student` VALUES ('s007', '刘玉', '21', '男');
    INSERT INTO `student` VALUES ('s008', '萧蓉', '21', '女');
    INSERT INTO `student` VALUES ('s009', '陈萧晓', '23', '女');
    INSERT INTO `student` VALUES ('s010', '陈美', '22', '女');
    
    -- ----------------------------
    -- Table structure for teacher
    -- ----------------------------
    DROP TABLE IF EXISTS `teacher`;
    CREATE TABLE `teacher` (
      `tno` varchar(10) NOT NULL,
      `tname` varchar(20) DEFAULT NULL,
      PRIMARY KEY (`tno`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of teacher
    -- ----------------------------
    INSERT INTO `teacher` VALUES ('t001', '刘阳');
    INSERT INTO `teacher` VALUES ('t002', '谌燕');
    INSERT INTO `teacher` VALUES ('t003', '胡明星');
    
    
    • 查询姓“刘”的老师的个数
    SELECT count(*) from teacher WHERE teacher.tname like '刘%'
    

    一个简单的匹配查询

    • 查询没学过“谌燕”老师课的同学的学号、姓名
    SELECT * from student st WHERE st.sno not in 
    (	SELECT DISTINCT sno from sc 
    		join course on course.cno = sc.cno 
    		join teacher on course.tno = teacher.tno
    		WHERE tname = '谌燕'
    )
    

    先把表sc 、 course 和 teacher 表并起来,从而筛选出所有选了 谌燕 课的学生的 id ,然后再使用 not in ,反向筛选。
    还用到了 DISTINCT ,处理重复的学生 id。

    • 查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名
    SELECT * from student
    JOIN sc a on a.sno = student.sno
    JOIN sc b on b.sno = student.sno
    WHERE a.cno = 'c001' and b.cno = 'c002'
    

    这个类似第一题,只要使用 join ,那就只包含存在的,null 的自动去掉了。

    • 查询学过“谌燕”老师所教的所有课的同学的学号、姓名
    SELECT * from (
    SELECT *,COUNT(*) stcnt FROM sc 
    WHERE sc.cno in 
    	(SELECT cno from course 
    		LEFT JOIN teacher on course.tno = teacher.tno
    		WHERE teacher.tname = '谌燕')
    GROUP BY sc.sno) c
    WHERE stcnt = (SELECT count(cno) from course 
    		LEFT JOIN teacher on course.tno = teacher.tno
    		WHERE teacher.tname = '谌燕')
    

    这个题要求找出上了“谌燕”老师所教的所有课的同学,网上的答案应该是错了,我也只能写出这么丑的一个大 sql。由于不清楚 sql 的查询机制,自己觉得这样效率应该不高。

    主要想法是,找出这个老师的所有课,统计每个学上上了这些课的多少,是否和这个老师的所有课的数量相等。

    • 查询所有课程成绩小于60 分的同学的学号、姓名
      这个题目有奇异,到底是 “所有有课程成绩小于60 分的同学” 还是 “所有的课程成绩都小于60 分的同学”?

    按第一种理解比较简单:

    SELECT * from student st
    LEFT JOIN sc on sc.sno = st.sno
    where sc.score <60
    

    按第二种理解:

    SELECT * from
    (SELECT sc.sno,count(*) acnt FROM sc
    GROUP BY sc.sno) a
    WHERE a.acnt = (select count(*) from sc WHERE sc.sno = a.sno and sc.score <60)
    

    我依然按照之前的想法,通过对比所有课程数和小于60分的课程数,来确定某个同学的课程是不是所有课程都小于 60 分。

    • 查询没有学全所有课的同学的学号、姓名
    select stu.sno,stu.sname,count(sc.cno) from student stu
    left join sc on stu.sno=sc.sno
    group by stu.sno,stu.sname
    having count(sc.cno)<(select count(distinct cno)from course)
    

    一样的想法

    • 查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名
    SELECT DISTINCT(sc.sno) from sc 
    WHERE 
    sc.sno <> 's001' and sc.cno in 
    (SELECT cno from sc WHERE sc.sno = 's001')
    

    先查出 s001 同学的课程,然后检查其他同学的课有没有在范围内的,过滤后输出。

    • 查询和“s001”号的同学学习的课程完全相同的其他同学学号和姓名
    SELECT * from (
    	SELECT sc.sno,count(*) st_cnt from sc 
    	WHERE 
    	sc.sno <> 's001' and sc.cno in 
    	(SELECT cno from sc WHERE sc.sno = 's001')
    	GROUP BY sc.sno) t 
    WHERE st_cnt = (SELECT count(cno) from sc WHERE sc.sno = 's001')
    

    又是一个这样的题。

    • 查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分
    SELECT sc.cno,max(sc.score),min(sc.score) from sc 
    GROUP BY sc.cno 
    
    • 查询各科成绩的及格率
    SELECT sc.cno,sum(case when sc.score>=60 then 1 else 0 end)/count(*) from sc 
    GROUP BY sc.cno
    

    使用 sum 配合 case when… then… else…end 语句数出及格人数。

    • 查询不同老师所教不同课程平均分从高到低显示
    SELECT *,convert(COALESCE(AVG(sc.score),0),decimal(10,2)) from course 
    LEFT JOIN sc on sc.cno = course.cno
    LEFT JOIN teacher on teacher.tno = course.tno
    GROUP BY course.cno 
    

    保留两位小数的方法:
    - select convert(4545.1366,decimal); //四舍五入
    - select truncate(4545.1366,2); //截取

    • 统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
    SELECT sc.sno,
    SUM(case when sc.score >90 then 1 ELSE 0 end) as '>90' ,
    SUM(case when sc.score >= 80 and sc.score<90 then 1 ELSE 0 end) as '80-90' ,
    SUM(case when sc.score >= 70 and sc.score<80 then 1 ELSE 0 end) as '70-80' ,
    SUM(case when sc.score >= 60 and sc.score<70 then 1 ELSE 0 end) as '60-70' ,
    SUM(case when sc.score < 60 then 1 ELSE 0 end) as '<60' 
    from sc
    GROUP BY sc.sno
    
    • 查询各科成绩前三名的记录
    不会写
    
    • 查询每门课程被选修的学生数
    SELECT cno,count(sno) from sc GROUP BY cno
    
    • 查询出只选修了一门课程的全部学生的学号和姓名
    SELECT sno,count(cno) c_cnt 
    from sc GROUP BY sno
    HAVING c_cnt =1
    

    直接通过 having 过滤就 OK 了,刚开始想错了。

    • 查询男生、女生人数
    SELECT ssex,count(*) from student 
    GROUP BY ssex
    
    • 查询姓“张”的学生名单
    SELECT * from student st
    where st.sname LIKE '张%' 
    
    • 查询同名同性学生名单,并统计同名人数
    select sname,count(*)
    from student 
    group by sname having count(*)>1;
    
    • 1981 年出生的学生名单(注:Student 表中Sage 列的类型是number)
    select sno,sname,sage,ssex from student t where year(now()) - sage  =1996
    
    • 查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列
    SELECT sc.cno,avg(sc.score) avg_score from sc
    GROUP BY sc.cno
    ORDER by avg_score,cno DESC
    
    • 查询平均成绩大于85 的所有学生的学号、姓名和平均成绩
    SELECT sc.sno,avg(sc.score) avg_score from sc
    GROUP BY sc.sno
    HAVING avg_score > 85
    
    • 查询课程名称为“数据库”,且分数低于60 的学生姓名和分数
    SELECT * from sc 
    join course on sc.cno = course.cno
    where course.cname = 'J2SE' and sc.score < 60
    
    • 查询所有学生的选课情况
    select st.sno,st.sname,c.cname from student st,sc,course c
    where sc.sno=st.sno and sc.cno=c.cno;
    ORDER BY st.sno
    
    
    • 查询任何一门课程成绩在70 分以上的姓名、课程名称和分数
    select st.sno,st.sname,co.cname,sc.score from sc
    join student st on sc.sno = st.sno
    join course co on co.cno = sc.cno
    WHERE sc.score >70
    
    • 查询不及格的课程,并按课程号从大到小排列
    select st.sno,st.sname,co.cname,sc.score from sc
    join student st on sc.sno = st.sno
    join course co on co.cno = sc.cno
    WHERE sc.score <60 ORDER BY sc.cno desc
    
    • 查询“c001”课程比“c002”课程成绩高的所有学生的学号;
    SELECT * from  
    (SELECT * from sc a WHERE a.cno = 'c001') a,
    (SELECT * from sc b where b.cno = 'c002') b
    WHERE a.sno = b.sno and a.score > b.score
    

    这里用到了两个子查询作为表,限定了 a.sno = b.sno 之后,就相当于把这张表的两个课程的成绩拼成了一行。之后的筛选就很方便了。
    要从子查询的表中取数据就要给子查询取个别名。

    SELECT * from sc a
    JOIN sc b on a.sno = b.sno
    WHERE a.cno = 'c001' and b.cno = 'c002'
    

    同样也可以使用 join 的方法。

    对于有限个的这种条件筛选,总是可以使用这种把当前表和自己合并之后再筛选的方法。

    • 查询平均成绩大于60 分的同学的学号和平均成绩
    SELECT *,avg(sc.score) avg_score,COUNT(sc.score) from sc 
    GROUP BY sc.sno 
    HAVING avg_score > 60
    

    这里就是基本的 group 的用法。

    • 查询所有同学的学号、姓名、选课数、总成绩
    SELECT *,count(sc.cno),SUM(sc.score) from student 
    LEFT JOIN sc on sc.sno = student.sno
    GROUP BY student.sno
    

    为了保证打印出所有同学的信息,(注意,包含没有选课,sc 表中不存在信息的同学),所以需要使用 leftjoin
    然后就是基本的分组查询了。

    如果觉得 sum 返回 null 影响后台程序执行, 可以使用 COALESCE(x,0)来提供一个默认值。

    oracle 中可以还可以使用 NVL ,myslq 中还可以使用 ifnull