关于数据库考试，想出来的BT题目

 

 

在复习数据库时，觉得SQL语句无比强大，于是 想到一个bt题目来测试- -#

 

 

题目背景：学生选课系统

三个数据表：

s：学生列表，包括snum和sname：学号，学生名

c：课程列表，包括cnum和cname：课号，课程名

sc：选课记录，包括cnum，snum和grade：课号，学号，成绩

 

设计好的数据：

见后面的sql文件，直接导入ok

 

 

问题：

选出（选课人数大于‘laohyx’选的课数）的所有课程名称

（好bt啊，有选课人数和人选课数比较的么- -）

分析：

先在s中查找叫‘laohyx’的学生的学号，然后 在sc中统计该学号的选课数。

之后在sc中统计再查找课程号，它的选课人数比该学号的选课数更多

再在c中找到课程号对应的课程名。

 

于是四层select嵌套语句出现了…

SELECT cname FROM c WHERE cnum = ( SELECT cnum FROM sc GROUP BY cnum HAVING COUNT( cnum ) > ( SELECT COUNT( * ) FROM sc WHERE snum = ( SELECT snum FROM s WHERE sname = 'laohyx' ) ) )  

 

 

考虑到snum是联系sc和s表的重要桥梁，可以通过inner join把它加入sc中，使减少一层select嵌套，而且更易理解

SELECT cname FROM c WHERE cnum = ( SELECT cnum FROM sc GROUP BY cnum HAVING COUNT( cnum ) > ( SELECT COUNT( * ) FROM sc INNER JOIN s ON sc.snum = s.snum WHERE s.sname = 'laohyx' ) )  

 

再考虑到c和sc中也有以cnum建立的联系，因此在查找cname时，也可以通过连接来完成，于是又减少一层嵌套。。

SELECT cname FROM c INNER JOIN sc WHERE c.cnum = sc.cnum GROUP BY sc.cnum HAVING COUNT( sc.cnum ) > ( SELECT COUNT( * ) FROM sc INNER JOIN s ON sc.snum = s.snum WHERE s.sname = 'laohyx' )  

 

 

于是就有2个连接的语句了。。。。。

有点bt…似乎这可能是最bt的题目了吧。。- -#

欢迎讨论 - -#

我承认自己疯了……

 

 

sql文件，可以导入的。。。

==================================================

-- phpMyAdmin SQL Dump -- version 3.3.10 -- http://www.phpmyadmin.net -- -- 主机: 127.0.0.1 -- 生成日期: 2011 年 05 月 03 日 22:08 -- 服务器版本: 5.5.11 -- PHP 版本: 5.3.3 SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; -- -- 数据库: `shujuku_class` -- -- -------------------------------------------------------- -- -- 表的结构 `c` -- CREATE TABLE IF NOT EXISTS `c` ( `cname` varchar(30) NOT NULL, `cnum` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- 转存表中的数据 `c` -- INSERT INTO `c` (`cname`, `cnum`) VALUES ('math', 1), ('chinese', 2), ('english', 3), ('phy', 4), ('art', 5); -- -------------------------------------------------------- -- -- 表的结构 `S` -- CREATE TABLE IF NOT EXISTS `S` ( `sname` varchar(30) NOT NULL, `snum` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- 转存表中的数据 `S` -- INSERT INTO `S` (`sname`, `snum`) VALUES ('laohyx', 1), ('baidu', 2), ('renren', 3), ('google', 4), ('emacs', 6); -- -------------------------------------------------------- -- -- 表的结构 `sc` -- CREATE TABLE IF NOT EXISTS `sc` ( `cnum` int(11) NOT NULL, `snum` int(11) NOT NULL, `grade` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- 转存表中的数据 `sc` -- INSERT INTO `sc` (`cnum`, `snum`, `grade`) VALUES (1, 4, 42), (1, 6, 72), (2, 1, 34), (2, 3, 85), (2, 4, 52), (3, 2, 93), (3, 3, 14), (3, 4, 93), (3, 6, 22), (4, 1, 33), (4, 2, 99), (4, 3, 97), (4, 4, 87), (4, 6, 46), (5, 1, 70), (5, 2, 13), (5, 3, 53), (5, 6, 80);