1.题目
第一题:
tb_user:
User_id | User_name | User_phone |
1 | 张三 | 13800138000 |
2 | 李四 | 13800138001 |
tb_customer:
Customer_id | User_id | Customer_name | Company_name |
1 | 1 | 王先生 | 中国移动广州分公司 |
2 | 2 | 林先生 | 中通服建设有限公司 |
tb_project
Project_id | Customer_id | Project_name |
1 | 1 | EOMS系统 |
2 | 2 | ME2.0机务维修系统 |
根据以上三个表,要求输出:
Project_id | User_name | User_phone | Customer_name | Company_name | Project_name |
1 | 张三 | 13800138000 | 王先生 | 中国移动广州分公司 | EOMS系统 |
2 | 李四 | 13800138001 | 林先生 | 中通服建设有限公司 | ME2.0机务维修系统 |
1.请按上表内容写出对应的sql语句?
2.表结构及数据
1 CREATE TABLE `tb_user` ( 2 `id` int(11) DEFAULT NULL, 3 `user_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL, 4 `user_phone` varchar(255) CHARACTER SET latin1 COLLATE latin1_german2_ci DEFAULT NULL 5 ) ENGINE=MyISAM DEFAULT CHARSET=latin1; 6 7 INSERT INTO `test`.`tb_user` (`id`, `user_name`, `user_phone`) VALUES ('1', '张三', '13800138000'); 8 INSERT INTO `test`.`tb_user` (`id`, `user_name`, `user_phone`) VALUES ('2', '李四', '13800138001'); 9 10 11 12 CREATE TABLE `tb_customer` ( 13 `Customer_id` int(11) DEFAULT NULL, 14 `user_id` int(11) DEFAULT NULL, 15 `Customer_name` varchar(255) DEFAULT NULL, 16 `Company_name` varchar(255) DEFAULT NULL 17 ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 18 INSERT INTO `test`.`tb_customer` (`Customer_id`, `user_id`, `Customer_name`, `Company_name`) VALUES ('1', '1', '王先生', '中国移动广州分公司'); 19 INSERT INTO `test`.`tb_customer` (`Customer_id`, `user_id`, `Customer_name`, `Company_name`) VALUES ('2', '2', '林先生', '中通服建设有限公司'); 20 21 22 23 24 CREATE TABLE `tb_project` ( 25 `Project_id` int(11) DEFAULT NULL, 26 `Customer_id` int(11) DEFAULT NULL, 27 `Project_name` varchar(255) DEFAULT NULL 28 ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 29 30 INSERT INTO `test`.`tb_project` (`Project_id`, `Customer_id`, `Project_name`) VALUES ('1', '1', 'EOMS系统'); 31 INSERT INTO `test`.`tb_project` (`Project_id`, `Customer_id`, `Project_name`) VALUES ('2', '2', 'ME2.0机务维修系统'); 32 33 34 35 CREATE TABLE `tb_dept` ( 36 `Dept_id` int(11) DEFAULT NULL, 37 `Dept_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL, 38 `Dept_loc` varchar(255) CHARACTER SET utf8 DEFAULT NULL 39 ) ENGINE=MyISAM DEFAULT CHARSET=latin1; 40 41 INSERT INTO `test`.`tb_dept` (`Dept_id`, `Dept_name`, `Dept_loc`) VALUES ('10', '教研部', '北京'); 42 INSERT INTO `test`.`tb_dept` (`Dept_id`, `Dept_name`, `Dept_loc`) VALUES ('20', 'IT部', '广州'); 43 INSERT INTO `test`.`tb_dept` (`Dept_id`, `Dept_name`, `Dept_loc`) VALUES ('30', '销售部', '深圳'); 44 INSERT INTO `test`.`tb_dept` (`Dept_id`, `Dept_name`, `Dept_loc`) VALUES ('40', '财务部', '深圳'); 45 INSERT INTO `test`.`tb_dept` (`Dept_id`, `Dept_name`, `Dept_loc`) VALUES ('50', '董事会', '上海'); 46 INSERT INTO `test`.`tb_dept` (`Dept_id`, `Dept_name`, `Dept_loc`) VALUES ('60', '行政部', '厦门'); 47 48 49 CREATE TABLE `tb_emp` ( 50 `Emp_id` int(11) DEFAULT NULL, 51 `Emp_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL, 52 `Job` varchar(255) CHARACTER SET utf8 DEFAULT NULL, 53 `Manage` varchar(255) DEFAULT NULL, 54 `Sal` varchar(255) DEFAULT NULL, 55 `Comm` varchar(255) DEFAULT NULL, 56 `Dept_id` int(11) DEFAULT NULL 57 ) ENGINE=MyISAM DEFAULT CHARSET=latin1; 58 59 INSERT INTO `test`.`tb_emp` (`Emp_id`, `Emp_name`, `Job`, `Manage`, `Sal`, `Comm`, `Dept_id`) VALUES ('1001', '甘宁', '文员', '1013', '8000.00', 'Null', '20'); 60 INSERT INTO `test`.`tb_emp` (`Emp_id`, `Emp_name`, `Job`, `Manage`, `Sal`, `Comm`, `Dept_id`) VALUES ('1002', '刘备', '经理', '1010', '29750.00', 'Null', '20'); 61 INSERT INTO `test`.`tb_emp` (`Emp_id`, `Emp_name`, `Job`, `Manage`, `Sal`, `Comm`, `Dept_id`) VALUES ('1006', '关羽', '经理', '1010', '24500.00', 'Null', '30'); 62 INSERT INTO `test`.`tb_emp` (`Emp_id`, `Emp_name`, `Job`, `Manage`, `Sal`, `Comm`, `Dept_id`) VALUES ('1007', '张飞', 'Java工程师', '1008', '28500.00', 'Null', '20'); 63 INSERT INTO `test`.`tb_emp` (`Emp_id`, `Emp_name`, `Job`, `Manage`, `Sal`, `Comm`, `Dept_id`) VALUES ('1008', '诸葛亮', '经理', '1004', '30000.00', '14000.00', '40'); 64 INSERT INTO `test`.`tb_emp` (`Emp_id`, `Emp_name`, `Job`, `Manage`, `Sal`, `Comm`, `Dept_id`) VALUES ('1009', '张无忌', '老师', '1011', '5000.00', 'Null', '10'); 65 INSERT INTO `test`.`tb_emp` (`Emp_id`, `Emp_name`, `Job`, `Manage`, `Sal`, `Comm`, `Dept_id`) VALUES ('1010', '张三丰', '董事长', 'Null', '58900.00', 'Null', '50'); 66 INSERT INTO `test`.`tb_emp` (`Emp_id`, `Emp_name`, `Job`, `Manage`, `Sal`, `Comm`, `Dept_id`) VALUES ('1011', '庞统', '经理', '1010', '30000.00', 'Null', '10'); 67 INSERT INTO `test`.`tb_emp` (`Emp_id`, `Emp_name`, `Job`, `Manage`, `Sal`, `Comm`, `Dept_id`) VALUES ('1010', '张三8', '文员', 'Null', '123', 'Null', '20'); 68 INSERT INTO `test`.`tb_emp` (`Emp_id`, `Emp_name`, `Job`, `Manage`, `Sal`, `Comm`, `Dept_id`) VALUES ('1010', '张三7', '文员', 'Null', '123', 'Null', '30');
第二题:
tb_dept
Dept_id | Dept_name | Dept_loc |
10 | 教研部 | 北京 |
20 | IT部 | 广州 |
30 | 销售部 | 深圳 |
40 | 财务部 | 深圳 |
50 | 董事会 | 上海 |
60 | 行政部 | 厦门 |
tb_emp
Emp_id | Emp_name | Job | Manage | Sal | Comm | Dept_id |
1001 | 甘宁 | 文员 | 1013 | 8000.00 | Null | 20 |
1002 | 刘备 | 经理 | 1010 | 29750.00 | Null | 20 |
1006 | 关羽 | 经理 | 1010 | 24500.00 | Null | 30 |
1007 | 张飞 | Java工程师 | 1008 | 28500.00 | Null | 20 |
1008 | 诸葛亮 | 经理 | 1004 | 30000.00 | 14000.00 | 40 |
1009 | 张无忌 | 老师 | 1011 | 5000.00 | Null | 10 |
1010 | 张三丰 | 董事长 | Null | 58900.00 | Null | 50 |
1011 | 庞统 | 经理 | 1010 | 30000.00 | Null | 10 |
1、查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数?
2、列出薪金比关羽高的所有员工?
3、列出所有员工的姓名及其直接上级的姓名 ?
4、列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门 ?
5、列出每个部门的员工数量、平均工资 ?
6、列出所有文员的姓名及其部门名称,部门的人数 ?
答案:
第一题: 请按上表内容写出对应的sql语句: 答题: select P.Project_id , U.User_name,U.User_phone, c.Customer_name,c.Company_name, p.Project_name from tb_user u LEFT JOIN tb_customer c on u.id=c.user_id LEFT JOIN tb_project p on c.customer_id= p.customer_id ; 第二题: 1、查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数 答题: select * from ( select d.dept_id , d.dept_name, d.dept_loc, count(e.emp_id) pNum from tb_dept d LEFT JOIN tb_emp e on d.dept_id=e.dept_id group by d.dept_id ) t where t.pNum>=1 ; 2、列出薪金比关羽高的所有员工 答题: select * from tb_emp where sal>( select e.sal from tb_emp e where e.emp_name='关羽'); 3、列出所有员工的姓名及其直接上级的姓名 答题: 方法1: select # e.emp_id employeeId, e.emp_name employee, # e1.emp_id leaderId , e1.emp_name leader from tb_emp e , tb_emp e1 where e.manage = e1.emp_id ; 方法2: select leader, GROUP_CONCAT(employee) employee from ( select # e.emp_id employeeId, e.emp_name employee, e1.emp_id leaderId , e1.emp_name leader from tb_emp e , tb_emp e1 where e.manage = e1.emp_id ) t group by t.leaderId ; 4、列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门 答题: select * from tb_dept d LEFT JOIN tb_emp e on d.dept_id=e.dept_id ; 5、列出每个部门的员工数量、平均工资 答题: select e.dept_id 部门,count(e.emp_id) 员工数量, ROUND(sum(e.sal)/count(e.emp_id),2) 平均工资 from tb_emp e group by e.dept_id ; 6、列出所有文员的姓名及其部门名称,部门的人数 答题: #列出所有文员的姓名及其部门名称 select e.emp_name,e.dept_id,d.dept_name from tb_emp e ,tb_dept d where e.dept_id=d.dept_id and e.job='文员' #是文员所在部门下的部门人数,还是部门下文员的人数 ? select e.dept_id,e.emp_name, d.dept_name,count(e.emp_id) from tb_emp e ,tb_dept d where e.dept_id=d.dept_id and e.job='文员' group by e.dept_id