数据库练习（二）

建表

create table employee(
 empno     int  primary key auto_increment ,     
 ename     char(10) ,  
 job       char(6)  ,  
 mgr       int      ,  
 hiredate  date     ,  
 sal       float(10,2),
 comm      float(10,2),
 deptno    int    );    

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,'30'),
('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'),
('1010','韦一笑','销售员','1006','2001-09-08','15000.00','0.00','30'),
('1011','周泰','文员','1008','2007-05-23','11000.00',NULL,'20'),
('1012','程普','文员','1006','2001-12-03','9500.00',NULL,'30'),
('1013','庞统','分析师','1004','2001-12-03','30000.00',NULL,'20'),
('1014','黄盖','文员','1007','2002-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 having count(e.deptno) >=1;

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

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

子查询

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

 SELECT   
     e1.empno,   
     e1.ename,   
     d.dname  
 FROM   
     employee e1  
 JOIN   
     employee e2 ON e1.mgr = e2.empno  
 JOIN   
     department d ON e1.deptno = d.deptno  
WHERE   
     e1.hiredate < e2.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   
     e.ename  
 FROM   
     employee e  
JOIN   
     department d ON e.deptno = d.deptno  
 WHERE   
     d.dname = '销售部';

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

SELECT   
     e.empno,   
     e.ename,   
    d.dname,   
     e.mgr,   
     e.sal,   
     sg.grade  
FROM   
     employee e  
 JOIN   
     department d ON e.deptno = d.deptno  
LEFT JOIN   
     salarygrade sg ON e.sal BETWEEN sg.losal AND sg.hisal  
WHERE   
     e.sal > (SELECT avg(sal)+sum(comm)/14 FROM employee)  
 ORDER BY   
    e.empno;

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

 

select e.ename,d.dname from employee e inner join department d on e.deptno = d.deptno where e.job = (select job from employee where ename = "庞统" ) ;

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

select e.ename,e.sal,d.dname from employee e inner join department d

on e.deptno = d.deptno where sal >

(select max(sal+comm) from  employee where deptno = 30);

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

SELECT current_year.year, current_year.zz,

CASE WHEN previous_year.zz = 0 THEN NULL

ELSE CONCAT(ROUND(( (current_year.zz - previous_year.zz) / previous_year.zz )*100 ,2 ),'%')

END AS growth_rate

FROM

         annualprofit current_year

LEFT JOIN

        annualprofit previous_year ON current_year.year = previous_year.year + 1

ORDER BY current_year.year;