單表練習

1. 查詢出部門編號為D2019060011的所有員工

> select * from employee where department_NO = "D2019060011";

2. 所有財務總監的姓名、編號和部門編號。

> select name,number,department_NO from employee where job = "財務總監";

3. 找出獎金高於工資的員工。

> select * from employee where bonus > salary;

4. 找出獎金高於工資40%的員工。

> select * from employee where bonus > salary * 0.4;

5 找出部門編號為D2019090011中所有財務總監，和部門編號為D2019060011中所有財務專員的詳細資料。

> select * from employee

where (department_NO = 'D2019090011' and job = "財務總監")

or (department_NO = 'D2019060011' and job = "財務專員");

6. 找出部門編號為D2019090001中所有總經理，部門編號為D2019090011中所有財務總監，還有即不是總經理又不是銷售總監但其工資大或等於4000的所有員工詳細資料。

> select * from employee where (department_no = 'd2019090001' and job = '總經理') or (department_no = 'd2019090011' and job = '財務總監') or (job not in ('總經理', '銷售總監') and salary >= 4000);

7有獎金的工種。

> select distinct job from employee where bonus is not null;

8無獎金或獎金低於1000的員工。

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

9. 查詢名字由兩個字組成的員工。

> select * from employee where name like "__";

10.查詢2020年入職的員工。

> select * from employee where hire_date like "2020%";

11. 查詢所有員工詳細信息，用編號升序排序。

> select * from employee order by number;

12. 查詢所有員工詳細信息，用工資降序排序，如果工資相同使用入職日期升序排序。

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

13. 查詢每個部門的平均工資。

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

14. 求出每個部門的僱員數量。

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

15. 查詢每種工作的最高工資、最低工資、人數.

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

16. 列出最低薪金大於4000的各種工作及從事此工作的員工人數。

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

17. 統計各部門工資總和，顯示部門編號和該部門僱員的月工資的總和，並且要滿足該部門僱員的月工資合計大於6000，輸出結果按月工資的合計升序排列。

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

多表練習

1. 列出所有員工的姓名及其直接上級的姓名。

> select e1.name "上級姓名",e2.name "員工姓名"from employee e1, employee e2 where e1.number = e2.leader_no;

2. 列出受僱日期早於直接上級的所有員工的編號、姓名、部門名稱。

> select e1.number,e1.name,e2.name "部門"

from employee e1 join employee e3 on e1.leader_NO = e3.number

join department e2 on e1.department_NO = e2.number

where e1.hire_date < e3.hire_date;

3. 列出部門名稱和這些部門的員工信息，同時列出那些沒有員工的部門。

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

4. 列出在財務部工作的員工的姓名，假定不知道財務部的部門編號。

> select e.name from employee e join department d on e.department_NO = d.number where d.name = "財務部";

5. 列出薪金高於公司平均薪金的所有員工信息，所在部門名稱，上級領導。

> select e1.*,e2.name "部門",e3.name as "上級"

from employee e1 join department e2 on e1.department_NO = e2.number

left join employee e3 on e1.leader_NO = e3.number

where e1.salary > (select avg(salary) from employee);

6. 列出與陳超從事相同工作的所有員工及部門名稱。

> select e1.*,e2.name "部門"

from employee e1 join department e2 on e1.department_NO = e2.number

where e1.job = (select job from employee where name = "陳超") and e1.name != "陳超";

7. 查出至少有一個員工的部門。顯示部門編號、部門名稱、部門位置、部門人數。

> select e2.number,e2.name,e2.location,count(e1.id) as "編號"

from department e2 join employee e1 on e2.number = e1.department_NO

group by e2.number,e2.name,e2.location having count(e1.id) > 0;

8. 列出薪金高於在財務部工作員工平均薪金的員工姓名和薪金、部門名稱。

> select e1.name,e1.salary,e2.name "部門" from employee e1 join department e2 on e1.department_NO = e2.number where e1.salary > (select avg(e2.salary) from employee e2 join department d2 on e2.department_NO = d2.number where d2.name like "%財務%");