SQL 面试题

题目1：

书籍表：books

 

DROP TABLE IF EXISTS `books`;
CREATE TABLE `books` (
  `book_id` int(11) NOT NULL,
  `book_name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

INSERT INTO `books` VALUES ('1', '英语教课书1');
INSERT INTO `books` VALUES ('2', '语文教课书1');
INSERT INTO `books` VALUES ('3', '数学教科书1');
INSERT INTO `books` VALUES ('4', '英语教课书2');
INSERT INTO `books` VALUES ('5', '语文教课书2');
INSERT INTO `books` VALUES ('6', '英语教课书3');
INSERT INTO `books` VALUES ('7', '数学教科书2');
INSERT INTO `books` VALUES ('8', '英语教课书3');

 作者表：authors

 

 

CREATE TABLE `authors` (
  `a_id` int(11) NOT NULL,
  `a_name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

INSERT INTO `authors` VALUES ('1', '张三');
INSERT INTO `authors` VALUES ('2', '李四');
INSERT INTO `authors` VALUES ('3', '王五');
INSERT INTO `authors` VALUES ('4', '赵六');

 部门表：depts

 

 

DROP TABLE IF EXISTS `depts`;
CREATE TABLE `depts` (
  `d_id` int(11) NOT NULL,
  `d_name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

INSERT INTO `depts` VALUES ('1', '英语部');
INSERT INTO `depts` VALUES ('2', '语文部');
INSERT INTO `depts` VALUES ('3', '数学部');

 部门与作者的关系表：depmap

 

 

DROP TABLE IF EXISTS `depmap`;
CREATE TABLE `depmap` (
  `d_id` int(11) DEFAULT NULL,
  `a_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

INSERT INTO `depmap` VALUES ('1', '1');
INSERT INTO `depmap` VALUES ('1', '2');
INSERT INTO `depmap` VALUES ('2', '3');
INSERT INTO `depmap` VALUES ('3', '4');

 书籍与作者的关系表：bookmap

 

 

CREATE TABLE `bookmap` (
  `book_id` int(11) DEFAULT NULL,
  `a_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `bookmap` VALUES ('1', '1');
INSERT INTO `bookmap` VALUES ('6', '2');
INSERT INTO `bookmap` VALUES ('4', '1');
INSERT INTO `bookmap` VALUES ('2', '3');
INSERT INTO `bookmap` VALUES ('5', '3');
INSERT INTO `bookmap` VALUES ('3', '4');
INSERT INTO `bookmap` VALUES ('7', '4');
INSERT INTO `bookmap` VALUES ('8', '1');

 问题：求出每个部门所著书籍的总数量，如：

           部门     数量

           部门1      3

           部门2      2

           部门3      1

 

 

题目2：两表情况如下

wu_plan

 

DROP TABLE IF EXISTS `wu_plan`;
CREATE TABLE `wu_plan` (
  `id` int(11) NOT NULL,
  `plan` varchar(10) DEFAULT NULL,
  `model` varchar(10) DEFAULT NULL,
  `corp_code` varchar(20) DEFAULT NULL,
  `plan_num` int(11) DEFAULT NULL,
  `prixis` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  

INSERT INTO `wu_plan` VALUES ('1', '0001', 'exx22', 'nokia', '2000', '100');
INSERT INTO `wu_plan` VALUES ('2', '0002', 'lc001', 'sony', '3000', '2000');

 wu_bom

 

 

DROP TABLE IF EXISTS `wu_bom`;
CREATE TABLE `wu_bom` (
  `id` int(11) NOT NULL,
  `plan` varchar(10) DEFAULT NULL,
  `pact` varchar(10) DEFAULT NULL,
  `amount` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

INSERT INTO `wu_bom` VALUES ('1', '0001', 'aa1', '300');
INSERT INTO `wu_bom` VALUES ('2', '0001', 'aa2', '200');
INSERT INTO `wu_bom` VALUES ('3', '0002', 'bb1', '500');
INSERT INTO `wu_bom` VALUES ('4', '0002', 'bb2', '800');
INSERT INTO `wu_bom` VALUES ('5', '0002', 'bb3', '400');

 查询这两个表中plan唯一，每一个plan中，amount最少的，并且plan_num大于prixis的记录

 

结果是：

id   plan      model   corp_code    plan_num prixis  pact    amount

1    0001    exx22    nokia            2000         100    aa2    200
2    0002    lc001    sony             3000         2000    bb3    400

 

题目3：

教师号    星期号   是否有课

1              2                1
1              3                1
2              1                1
3              2                1
1              2                1

 

1表示有课

DROP TABLE IF EXISTS `t_class`;
CREATE TABLE `t_class` (
  `teacher_id` int(11) NOT NULL,
  `week_num` int(11) DEFAULT NULL,
  `is_class` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

INSERT INTO `t_class` VALUES ('1', '2', '1');
INSERT INTO `t_class` VALUES ('1', '3', '1');
INSERT INTO `t_class` VALUES ('2', '1', '1');
INSERT INTO `t_class` VALUES ('3', '2', '1');
INSERT INTO `t_class` VALUES ('1', '2', '1');

 写一条SQL语句让它变为这样的表

教师号    星期一  星期二  星期三

1              0              2          2
2              1              0          0
3               0             1          0

各星期下的数字表示：对应的的教师在星期几已经排的课数