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

 

 

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

 

 

题目背景：学生选课系统

三个数据表：

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

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

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

 

设计好的数据：

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

 

 

问题：

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

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

分析：

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

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

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

 

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

SELECT cnameFROM cWHERE cnum = ( SELECT cnumFROM scGROUP BY cnumHAVING COUNT( cnum ) > ( SELECT COUNT( * ) FROM scWHERE snum = ( SELECT snumFROM sWHERE sname = 'laohyx' ) ) )  

 

 

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

SELECT cnameFROM cWHERE cnum = ( SELECT cnumFROM scGROUP BY cnumHAVING COUNT( cnum ) > ( SELECT COUNT( * ) FROM scINNER JOIN s ON sc.snum = s.snumWHERE s.sname = 'laohyx' ) )  

 

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

SELECT cnameFROM cINNER JOIN scWHERE c.cnum = sc.cnumGROUP BY sc.cnumHAVING COUNT( sc.cnum ) > ( SELECT COUNT( * ) FROM scINNER JOIN s ON sc.snum = s.snumWHERE 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.3SET 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);