8月5日 Oracle作业

-- 用到的数据库表

 

CREATE TABLE xapp_employees (

    id      number(4),

    name    varchar2(20),

    salary  number(6),

deptid  number(4)

);

 

CREATE TABLE xapp_depts(

    id      number(4),

name    varchar2(20)

);

 

-- 用到的数据

 

INSERT INTO xapp_depts VALUES(1, 'DEPT_AAA');

INSERT INTO xapp_depts VALUES(2, 'DEPT_BBB');

INSERT INTO xapp_depts VALUES(3, 'DEPT_CCC');

INSERT INTO xapp_depts VALUES(4, 'DEPT_DDD');

INSERT INTO xapp_depts VALUES(32, 'DEPT_DDD');

INSERT INTO xapp_depts VALUES(33, 'DEPT_DDD');

INSERT INTO xapp_employees VALUES(1, 'A', 2100, 1);

INSERT INTO xapp_employees VALUES(2, 'B', 3020, 1);

INSERT INTO xapp_employees VALUES(3, 'C', 2003, 2);

INSERT INTO xapp_employees VALUES(4, 'D', 3400, 2);

INSERT INTO xapp_employees VALUES(5, 'E', 2050, 3);

INSERT INTO xapp_employees VALUES(6, 'F', 3006, 4);

INSERT INTO xapp_employees VALUES(7, 'G', 2700, 4);

INSERT INTO xapp_employees VALUES(8, 'H', 3080, 4);

INSERT INTO xapp_employees VALUES(9, 'I', 2009, 32);

INSERT INTO xapp_employees VALUES(10, 'J', 2100, 32);

INSERT INTO xapp_employees VALUES(11, 'K', 3020, 33);

INSERT INTO xapp_employees VALUES(12, 'L', 3003, 33);

INSERT INTO xapp_employees VALUES(13, 'M', 3400, 2);

INSERT INTO xapp_employees VALUES(14, 'biri', 2345, 4);

 

 

 

--15 公司有多少人和资金不为空的人数

SELECT COUNT(*) FROM xapp_employees;

 

SELECT COUNT(*) FROM xapp_employees

WHERE salary IS NOT NULL;

 

 

--16 资金大于0的人数

SELECT COUNT(*) FROM xapp_employees

WHERE salary > 0;

 

 

--17 各个部门平均最大，最小工资，人数，按照部门号升序排列

SELECT

  deptid       "Department ID",

  MAX(salary)  "Max Salary",

  MIN(salary)  "Min Salary",

  COUNT(*)     "Employee Count"

FROM xapp_employees

GROUP BY deptid

ORDER BY deptid;

 

 

--18 工资大于1500的部门的人数

SELECT

  deptid       "Department ID",

  COUNT(*)     "Employee Count"

FROM xapp_employees

WHERE salary > 1500

GROUP BY deptid;

 

 

--19 各个部门平均工资和人数，按照部门名字升序排列

SELECT

  d.name         "Department Name",

  AVG(e.salary) "Average Salary",

  COUNT(e.id)   "Employee Count"

FROM xapp_employees e, xapp_depts d

WHERE e.deptid = d.id

GROUP BY d.name

ORDER BY d.name;

 

 

--20 是否有员工在同一部门，而且工资相同，列出这样的部门号和工资，人数

SELECT

  e1.deptid,

  e1.salary,

  COUNT(e1.name)

FROM xapp_employees e1, xapp_employees e2

WHERE e1.id <> e2.id

  AND e1.salary = e2.salary

  AND e1.deptid = e2.deptid

GROUP BY e1.deptid, e1.salary;

 

 

--21 哪些部门工资高于1000的人数超过2人，列出部门名字

SELECT d.name

FROM xapp_employees e, xapp_depts d

WHERE e.deptid = d.id AND e.salary > 1000

GROUP BY d.name

HAVING COUNT(*) > 2;

 

 

--22 哪些员工和biri同部门

SELECT * FROM xapp_employees

WHERE deptid = (SELECT deptid FROM xapp_employees

                WHERE name = 'biri')

AND name <> 'biri';

 

 

 

--23 哪些员工的工资，高于整个公司的平均工资，列出员工的名字和工资（降序）

SELECT name, salary FROM xapp_employees

WHERE salary >

  (SELECT AVG(salary) FROM xapp_employees)

ORDER BY salary DESC;

 

 

--24 哪些员工的工资，介于32和33部门（33高些）平均工资之间

SELECT name FROM xapp_employees

WHERE salary BETWEEN

  (SELECT AVG(salary) FROM xapp_employees

   WHERE deptid = 32)

  AND

  (SELECT AVG(salary) FROM xapp_employees

   WHERE deptid = 33);

 

 

--25 所在部门平均工资高于1500的员工名字

SELECT name,deptid FROM xapp_employees

WHERE deptid IN (SELECT deptid FROM xapp_employees

                 GROUP BY deptid HAVING AVG(salary)>1500);

 

 

--26 列出各个部门的ID，以及这个部门的最高工资员工名字和工资

SELECT d.id "Department Id",

  (SELECT MAX(salary) FROM xapp_employees WHERE deptid=d.id) "Max Salary",

  (SELECT name FROM

  (SELECT name, deptid FROM xapp_employees ORDER BY salary DESC)

   WHERE deptid=d.id AND ROWNUM=1) "Name"

FROM xapp_depts d

 

 

--27 哪个员工是整个公司的最高工资

SELECT name

FROM (SELECT name FROM xapp_employees

      ORDER BY salary DESC)

WHERE ROWNUM = 1;

 

 

--28 部门平均工资最高的是多少

SELECT name FROM xapp_depts

WHERE id= (SELECT deptid

           FROM (SELECT deptid,

                 FROM xapp_employees

                 GROUP BY deptid

                 ORDER BY AVG(salary) DESC)

           WHERE ROWNUM = 1);