建表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<t2.hiredate) a,sg_dept b
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