java人员安排表_javav 的日志-根据部门表和人员表，完成如下查询

建表sql:

-- ----------------------------

-- Table structure for sg_dept

-- ----------------------------

DROP TABLE IF EXISTS `sg_dept`;

CREATE TABLE `sg_dept` (

`deptno` int(11) NOT NULL,

`dname` varchar(50) DEFAULT NULL,

`loc` varchar(50) DEFAULT NULL,

PRIMARY KEY (`deptno`)

);

-- ----------------------------

-- Records of sg_dept

-- ----------------------------

INSERT INTO `sg_dept` VALUES ('10', '教研部', '北京');

INSERT INTO `sg_dept` VALUES ('20', '学工部', '上海');

INSERT INTO `sg_dept` VALUES ('30', '销售部', '广州');

INSERT INTO `sg_dept` VALUES ('40', '财务部', '武汉');

-- ----------------------------

-- Table structure for sg_emp

-- ----------------------------

DROP TABLE IF EXISTS `sg_emp`;

CREATE TABLE `sg_emp` (

`empno` int(11) NOT NULL,

`ename` varchar(50) DEFAULT NULL,

`job` varchar(50) DEFAULT NULL,

`mgr` int(11) DEFAULT NULL,

`hiredate` date DEFAULT NULL,

`sal` decimal(7,2) DEFAULT NULL,

`COMM` decimal(7,2) DEFAULT NULL,

`deptno` int(11) DEFAULT NULL,

PRIMARY KEY (`empno`),

KEY `fk_emp` (`mgr`),

CONSTRAINT `fk_emp` FOREIGN KEY (`mgr`) REFERENCES `sg_emp` (`empno`)

);

-- ----------------------------

-- Records of emp

-- ----------------------------

INSERT INTO `sg_emp` VALUES ('1009', '董先生', '董事长', null, '2001-11-17', '50000.00', null, '10');

INSERT INTO `sg_emp` VALUES ('1004', '刘备', '经理', '1009', '2001-04-02', '29750.00', null, '20');

INSERT INTO `sg_emp` VALUES ('1008', '诸葛亮', '分析师', '1004', '2007-04-19', '30000.00', null, '20');

INSERT INTO `sg_emp` VALUES ('1013', '庞统', '分析师', '1004', '2001-12-03', '30000.00', null, '20');

INSERT INTO `sg_emp` VALUES ('1001', '甘宁', '文员', '1013', '2000-12-17', '8000.00', null, '20');

INSERT INTO `sg_emp` VALUES ('1006', '关羽', '经理', '1009', '2001-05-01', '28500.00', null, '30');

INSERT INTO `sg_emp` VALUES ('1002', '黛绮丝', '销售员', '1006', '2001-02-20', '16000.00', '3000.00', '30');

INSERT INTO `sg_emp` VALUES ('1003', '殷天正', '销售员', '1006', '2001-02-22', '12500.00', '5000.00', '30');

INSERT INTO `sg_emp` VALUES ('1005', '谢逊', '销售员', '1006', '2001-09-28', '12500.00', '14000.00', '30');

INSERT INTO `sg_emp` VALUES ('1007', '张飞', '经理', '1009', '2001-09-01', '24500.00', null, '10');

INSERT INTO `sg_emp` VALUES ('1010', '韦一笑', '销售员', '1006', '2001-09-08', '15000.00', '0.00', '30');

INSERT INTO `sg_emp` VALUES ('1011', '周泰', '文员', '1008', '2007-05-23', '11000.00', null, '20');

INSERT INTO `sg_emp` VALUES ('1012', '程普', '文员', '1006', '2001-12-03', '9500.00', null, '30');

INSERT INTO `sg_emp` VALUES ('1014', '黄盖', '文员', '1007', '2002-01-23', '13000.00', null, '10');

问题：

列出薪金比关羽高的所有员工

列出所有员工的姓名及其直接上级的姓名

列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称

列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

列出所有文员的姓名及其部门名称，部门的人数

-- 1.列出薪金比关羽高的所有员工

select ename

from sg_emp

where  sal>(select  sal from sg_emp  where sg_emp.ename='关羽')

-- 2. 列出所有员工的姓名及其直接上级的姓名

select t1.empno,t1.ename,t1.mgr,t2.ename

from sg_emp t1 left join  sg_emp t2 on t1.mgr=t2.empno

-- 3. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称

select * from(select t1.empno,t1.ename,t1.hiredate,t2.ename 上级,t2.hiredate 上级入职时间,t1.deptno

from sg_emp t1  left join sg_emp t2 on t1.mgr=t2.empno

where t1.hiredate

where a.deptno=b.deptno

-- 4. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

SELECT t2.dname,t2.deptno,count(t1.ename)

from sg_emp t1  right join sg_dept t2 on t1.deptno=t2.deptno

GROUP BY t2.deptno

-- 5. 列出所有文员的姓名及其部门名称，部门的人数

select a.*,b.c

from

(select *from sg_emp t1 where t1.job='文员') a,

(select t2.dname,t2.deptno,count(t1.ename)c from  sg_emp t1 right join sg_dept t2 on t1.deptno=t2.deptno GROUP BY t2.deptno)b

where a.deptno=b.deptno