【sql】訓練三

1.  Ben的領導是誰（Ben向誰報告）。

SQL>  select first_name,manager_id from employees where manager_id =(select manager_id from employees where first_name='Ben');

 

2.   Ben領導誰。（誰向Ben報告）。

SQL> select first_name,employee_id from employees where manager_id = (select employee_id from employees where first_name='Ben');

3.  哪些員工和Biri(last_name)同部門

SQL> select last_name,department_id from employees where department_id = (select department_id from employees where last_name='Biri');

 

4.  哪些員工跟Smith(last_name)做一樣職位

SQL>  select last_name,job_id from employees where job_id = (select distinct job_id from employees where last_name='Smith');

 

5.   哪些員工跟Biri(last_name)不在同一個部門

SQL> select last_name,job_id from employees where job_id not in ((select distinct job_id from employees where last_name='Smith'));

 

6.  整個公司中，最高工資和最低工資相差多少

 SQL>  select max(salary),min(salary),max(salary)-min(salary) gzc from employees;

 

MAX(SALARY) MIN(SALARY)        GZC

----------- ----------- ----------

      24000        2100      21900

7.  顯示整個公司的最高工資、最低工資、工資總和、平均工資，保留到整數位。

SQL> select max(salary),min(salary),sum(salary),trunc(avg(salary)) from employees;

 

MAX(SALARY) MIN(SALARY) SUM(SALARY) TRUNC(AVG(SALARY))

----------- ----------- ----------- ------------------

      24000        2100      691416               6461

8.  整個公司有多少個領導

SQL> select count(manager_id) from employees;

 

COUNT(MANAGER_ID)

-----------------

              106

9.  各個部門中工資大於1500的員工人數

SQL> select department_id,count(*) from employees where

  2  salary > 1500 group by department_id;

10.該部門中工資高於1000的員工數量超過2人，列出符合條件的部門：顯示部門名字

SQL> select department_name from departments where department_id in (select department_id from employees where salary>1000 group by department_id having count(employee_id)>2);

 

DEPARTMENT_NAME

------------------------------

Finance

Purchasing

Executive

Shipping

Sales

IT

 

6 rows selected.

 

11. 哪些員工的工資，高於整個公司的平均工資，列出員工的名字和工資（降序）

SQL> select first_name,salary from employees where salary > (select avg(salary) from employees);

 

12. 哪些員工的工資，介於32和33部門平均工資之間

SQL> select first_name,salary from employees where salary between (select avg(salary) from employees where department_id =33)

  2  and

  3  ( select avg(salary) from employees where department_id =32 );

 

13.列出各個部門中工資最高的員工的資訊：名字、部門號、工資

SQL> select department_id,last_name,salary from employees where (department_id,salary) in (select department_id,max(salary) from employees group by department_id) order by 1;

 

14.哪些部門的人數比32號部門的人數多

SQL> select department_id,count(last_name) from employees group by department_id

  2  having count(last_name) > (select count(last_name) from employees where department_id = 32);

 

 

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

SQL> select department_id,count(first_name) from

  2  employees group by department_id;

 

16. 查詢出比7654工資要高的全部僱員的資訊

SQL> select FIRST_NAME,SALARY from employees where salary > 7656;

 

 

17. 要求查詢工資比7654高，同時與7788從事相同工作的全部僱員

 

SQL> select first_name,salary from employees where salary > 7654 and job_id = (select job_id from employees where salary = 7788);