题目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
各星期下的数字表示:对应的的教师在星期几已经排的课数