数据库练习

insert into employee(empno,ename,job,mgr,hiredate,sal,comm,deptno) values
('1001','甘宁','文员','1013','2000-12-17','8000.00',NULL,'20'),
('1002','黛绮丝','销售员','1006','2001-02-20','16000.00','3000.00','30'),
('1003','殷天正','销售员','1006','2001-02-22','12500.00','5000.00','30'),
('1004','刘备','经理','1009','2001-04-02','29750.00',NULL,'20'),
('1005','谢逊','销售员','1006','2001-09-28','12500.00','14000.00','30'),
('1006','关羽','经理','1009','2001-05-01','28500.00',NULL,'20'),
('1007','张飞','经理','1009','2001-09-01','24500.00',NULL,'10'),
('1008','诸葛亮','分析师','1004','2007-04-19','30000.00',NULL,'20'),
('1009','曾阿牛','董事长',NULL,'2001-11-17','50000.00',NULL,'10'),
('1011','韦一笑','销售员','1006','2001-09-08','15000.00','0.00','30'),
('1012','周泰','文员','1008','2007-05-23','11000.00',NULL,'20'),
('1013','程普','文员','1006','2001-12-03','9500.00',NULL,'30'),
('1014','庞统','分析师','1004','2001-12-03','30000.00',NULL,'20'),
('1015','黄盖','文员','1007','2000-01-23','13000.00',NULL,'10');

mysql> create table department(
    -> deptno int(5) primary key,
    -> dname char(10),
    -> loc char(10));

insert into department(deptno,dname,loc)  values
('10','教研部','北京'),
('20','学工部','上海'),
('30','销售部','广州'),
('40','财务部','武汉');

mysql> create table salarygrade(
    -> grade int(5) primary key auto_increment,
    -> losal float(10,2),
    -> hisal float(10,2));
Query OK, 0 rows affected, 3 warnings (0.00 sec)

insert into salarygrade(losal,hisal) values
('7000.00','12000.00'),
('12012.00','14000.00'),
('14010.00','20000.00'),
('20010.00','30000.00'),
('30010.00','99990.00');

mysql> create table annualprofit(
    -> year year(4) primary key,
    -> zz float(10,2));

insert into annualprofit(year,zz) values
('2010','100.00'),
('2011','150.00'),
('2012','250.00'),
('2013','800.00'),
('2014','1000.00');

1.查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。

select e.deptno,d.dname,d.loc,count(*) as 部门人数 from employee as e,department as d where d.deptno=e.deptno group by e.deptno;

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

SELECT e1.ename AS employee_name, e2.ename AS manager_name FROM employee e1 LEFT JOIN employee e2 ON e1.empno = e2.mgr;

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

SELECT e1.empno as 管理者编号,e1.ename AS 管理者,e1.deptno as 管理员部门, e2.empno as 员工编号,e2.ename AS 员工,e2.deptno as员工部门 FROM employee e1 LEFT JOIN employee e2 ON e1.empno = e2.mgr where e2.hiredate < e1.hiredate;

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

select d.dname,e.* from  employee e RIGHT OUTER JOIN department d on d.deptno = e.deptno;

5.列出最低薪金大于15000的各种工作及从事此工作的员工人数。

select job,count(*) as 人数 from employee where sal > 15000 or sal+comm > 15000 group by job;

6.列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。

我知道编号 select ename from employee where deptno = 30;

我不知道销售部门但是我知道其他部门

select ename from employee where deptno not between 10 and 20;

7.列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。

select d.dname, e.ename from department d,employee e

where d.deptno = (select deptno from  employee e where sal > (select avg(sal) from  employee) or sal+comm > (select avg(sal+comm) from  employee) )

and e.empno = (select mgr from  employee e where sal > (select avg(sal) from  employee) or sal+comm > (select avg(sal+comm) from  employee) ) ;

select d.dname from department d where d.deptno = ( select deptno from  employee e where sal > (select avg(sal) from  employee) or sal+comm > (select avg(sal+comm) from  employee) );

8.列出与庞统从事相同工作的所有员工及部门名称。

9.列出薪金高于在部门30工作的所有员工的薪金，的员工姓名和薪金、部门名称。

10.查出年份、利润、年度增长比.