show databases;
use mysql_1;
show tables;
select * from employee where department_NO = "D2019060011";

select name,number,department_NO from employee where job = '財(cái)務(wù)總監(jiān)';

select * from employee where bonus > salary;

select * from employee where bonus > salary * 0.4;

 select * from employee where (department_NO = 'D2019090011' and job = '財(cái)務(wù)總監(jiān)')
-> or (department_NO = 'D2019060011' and job = '財(cái)務(wù)專員');

select * from employee where (department_NO = 'D2019090001' and job = '總經(jīng)理')
-> or (department_NO = 'D2019090011' and job = '財(cái)務(wù)總監(jiān)')
-> or (job <> '總經(jīng)理' and job <> '銷(xiāo)售總監(jiān)' and salary >= 4000);

select job from employee where bonus is not null;

select * from employee where bonus is null or bonus <= 1000;

 select * from employee where name like '__';

select * from employee where hire_date like '2020%';

select * from employee order by number asc;

select * from employee order by salary desc,hire_date asc;

select department_NO,avg(salary) from employee group by department_NO;

select department_NO,count(id) from employee group by department_NO;

select max(salary),min(salary),count(id) from employee group by job;

select job,count(id) from employee group by job having min(salary) > 4000;

select department_NO,sum(salary) from employee group by department_NO having sum(salary) > 6000 order by sum(salary) asc;

select e1.name,e2.name from employee e1
-> join employee e2
-> on e1.number = e2.leader_NO;

select e1.number as 員工編號(hào), e1.name as 員工姓名, d.name as 部門(mén)名稱 from employee e1
-> join employee e2
-> on e1.leader_NO = e2.number
-> join department d
-> on e1.department_NO = d.number
-> where e1.hire_date < e2.hire_date;

select * from employee e
-> right join department d
-> on e.department_NO = d.number;

select d.name,e.* from employee e
-> right join department d
-> on e.department_NO = d.number;

select name from employee where department_NO in (select number from department where name = '財(cái)務(wù)部');

也可以是以下代碼：
select e.name from employee e
-> join department d
-> on e.department_NO = d.number
-> where d.name = '財(cái)務(wù)部';

select e1.number as 員工編號(hào), e1.name as 員工姓名, e1.job as 職位, e1.salary as 薪資, e1.hire_date as 入職日期, d.name as 部門(mén)名稱, e2.name as 直接上級(jí)姓名 from employee e1
-> join department d
-> on e1.department_NO = d.number
-> left join employee e2
-> on e1.leader_NO = e2.number
-> where e1.salary > (SELECT AVG(salary) from employee);

select e.number as 員工編號(hào),e.name as 員工姓名,e.job as 職位,d.name as 部門(mén)名稱
-> from employee e
-> join department d
-> on e.department_NO = d.number
-> where e.job = (select job from employee where name = '陳超') and e.name != '陳超';

select res.department_NO,d.name,d.location,res.c from (select department_NO,count(*) c from employee group by department_NO) res
-> join department d
-> on res.department_NO = d.number;

select e.name as 員工姓名,e.salary as 薪金,d.name as 部門(mén)名稱
-> from employee e
-> join department d
-> on e.department_NO = d.number
-> where e.salary > (select avg(e2.salary) from employee e2
-> join department d2 on e2.department_NO = d2.number where d2.name = '財(cái)務(wù)部');