-- 查询
--谁的工资比Abel高
-- SELECT SALARY
-- from EMPLOYEES
-- WHERE LAST_NAME = 'Abel'
-- SELECT LAST_NAME , SALARY
-- FROM EMPLOYEES
-- WHERE SALARY > (SELECT SALARY FROM EMPLOYEES
-- WHERE LAST_NAME = 'Abel')
--2)查询员工名为Chen 的manager的信息
-- SELECT *
-- FROM EMPLOYEES
-- WHERE EMPLOYEE_ID = (SELECT MANAGER_ID FROM EMPLOYEES
-- WHERE LAST_NAME = 'Chen')
--
-- SELECT MANAGER_ID FROM EMPLOYEES
-- WHERE LAST_NAME = 'Chen'
--子查询(内查询)在主查询之前执行完成,子查询的结果被主查询(外查询)使用
--子查询要
--返回job_id与141号员工相同,salary比143号员工资多的员工姓名,job_id和工资
-- SELECT LAST_NAME, JOB_ID,SALARY
-- FROM EMPLOYEES
-- WHERE JOB_ID = (
-- SELECT JOB_ID
-- FROM EMPLOYEES
-- WHERE EMPLOYEE_ID = 141
)
-- AND SALARY >
-- (
-- SELECT SALARY
-- FROM EMPLOYEES
-- WHERE EMPLOYEE_ID = 143
)
--返回公司工资最少的员工的last_name job_id 和 salary
-- SELECT last_name ,JOB_ID ,SALARY
-- FROM EMPLOYEES
-- WHERE SALARY = (
-- SELECT min(SALARY)
-- FROM EMPLOYEES
-- )
--查询最低工资大于50号部门最低工资的部门id和最低工资、
-- SELECT min(SALARY) ,DEPARTMENT_ID
-- FROM EMPLOYEES
-- GROUP BY DEPARTMENT_ID
-- HAVING min(SALARY)>
-- (
-- SELECT min(SALARY)
-- FROM EMPLOYEES
-- WHERE DEPARTMENT_ID = 50
-- )
--子查询中的空值问题
--返回其它部门中比Job_id为 ‘IT-PROG’部门任一工资低的员工的
--员工号、姓名、job_id 以及salary
-- in()等于列表中任意一个
-- any()和子查询返回的某一个值比较
-- all()和子查询返回的所有值比较
--
-- SELECT EMPLOYEE_ID,LAST_NAME,SALARY
-- FROM EMPLOYEES
-- WHERE JOB_ID <> 'IT_PROG' AND SALARY < ANY ( --比其中任一个都小
-- SELECT SALARY
-- FROM EMPLOYEES
-- WHERE JOB_ID = 'IT_PROG'
-- )
--1)查询工资最低的员工的信息
-- SELECT *
-- FROM EMPLOYEES
-- WHERE SALARY = (
-- SELECT min(SALARY)
-- FROM EMPLOYEES
-- )
-- 2) 查询平均工资最低的部门的信息和该部门的平均工资
--1查询公司各部门的平均工资是多少
--2查询该公司中各部门的平均工资最少
--3哪个部门的平均工资 = 2.的结果
-- 4.此部门的部门信息
-- SELECT * FROM DEPARTMENTS WHERE DEPARTMENT_ID =
-- (
-- SELECT DEPARTMENT_ID
-- FROM EMPLOYEES
-- HAVING avg(SALARY)=
-- (
-- SELECT min(avg(SALARY))
-- FROM EMPLOYEES
-- GROUP BY DEPARTMENT_ID
-- )
-- GROUP BY DEPARTMENT_ID
-- )
--3)查询平均工资最低的部门[departments 表]和该部门平均工资[用的是employees表]
-- SELECT d.*,(select avg(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID = d.DEPARTMENT_id)
-- FROM DEPARTMENTS d
-- WHERE DEPARTMENT_ID =
-- (
-- SELECT DEPARTMENT_ID
-- FROM EMPLOYEES
-- HAVING avg(SALARY) =
-- (
-- SELECT min(avg(SALARY))
-- FROM EMPLOYEES
-- GROUP BY DEPARTMENT_ID
-- )
-- GROUP BY DEPARTMENT_ID
-- )
-- 4)查询平均工资最高的job信息
-- 1. 按job来分组,查询最高的平均工资
-- SELECT max(avg(salary))
-- FROM EMPLOYEES
-- GROUP BY job_id
-- 2.查询得到哪个job_id的平均工资等于1得到的值
-- SELECT job_id
-- FROM EMPLOYEES
-- HAVING avg(salary) =
-- (
-- SELECT max(avg(salary))
-- FROM EMPLOYEES
-- GROUP BY job_id
-- )
-- GROUP BY job_id
--
-- 3.从jobs表中返回Job_id的对应项的信息
-- SELECT *
-- FROM JOBS
-- WHERE job_id = --in
-- (
-- SELECT job_id
-- FROM EMPLOYEES
-- HAVING avg(salary) =
-- (
-- SELECT max(avg(salary))
-- FROM EMPLOYEES
-- GROUP BY job_id
-- )
-- GROUP BY job_id
-- )
--4. 查询平均工资高于公司的平均工资的部门有哪些?
-- SELECT DEPARTMENT_id ,avg(SALARY)
-- FROM EMPLOYEES
-- GROUP BY DEPARTMENT_ID
-- HAVING avg(SALARY) >
-- (
-- SELECT avg(SALARY)
-- FROM EMPLOYEES
-- )
--5 查询公司中所有manager详细信息
-- SELECT *
-- FROM EMPLOYEES
-- WHERE EMPLOYEE_ID in
(
-- SELECT MANAGER_ID
-- from EMPLOYEES
-- )
--
--6 各个部门中最高工资中最低的那个部门的 最低工资是多少
-- SELECT DEPARTMENT_ID, min(SALARY)
-- FROM EMPLOYEES
-- WHERE DEPARTMENT_ID =
-- (
-- SELECT DEPARTMENT_ID
-- FROM EMPLOYEES
-- HAVING max(SALARY) =
-- (
-- SELECT min(max(SALARY))
-- FROM EMPLOYEES
-- GROUP BY DEPARTMENT_ID
-- )
-- GROUP BY DEPARTMENT_ID
-- )
-- GROUP BY DEPARTMENT_ID
--7 查询平均工资最高的部门的manager 的详细信息
-- last_name,DEPARTMENT_id email
-- SELECT LAST_NAME,DEPARTMENT_ID, EMAIL
-- FROM EMPLOYEES
-- WHERE EMPLOYEE_ID in
(
SELECT DISTINCT MANAGER_ID
FROM EMPLOYEES WHERE DEPARTMENT_ID =
(
SELECT DEPARTMENT_ID
FROM EMPLOYEES
HAVING avg(SALARY) =
(
SELECT max(avg(salary))
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
)
GROUP BY DEPARTMENT_ID
)
)
--8 查询1999年来公司的员工当中的最高工资的那个员工信息
SELECT *
FROM EMPLOYEES
WHERE SALARY =
(
SELECT max(SALARY)
FROM EMPLOYEES
WHERE to_char(HIRE_DATE,'YYYY') = '1999'
)
AND to_char (HIRE_DATE,'YYYY') ='1999'
--9 查询Zlotkey 相同部门的员工的姓名和雇佣日期
-- SELECT LAST_NAME,HIRE_DATE
-- FROM EMPLOYEES
-- WHERE DEPARTMENT_ID =
-- (
-- SELECT DEPARTMENT_ID
-- FROM EMPLOYEES
-- WHERE LAST_NAME = 'Zlotkey'
-- )
-- AND LAST_NAME <> 'Zlotkey' --把这个人拿掉
--10)查询工资比公司平均工资高的员工,工号,
-- SELECT LAST_NAME ,SALARY
-- FROM EMPLOYEES
-- WHERE SALARY > ( SELECT avg(SALARY) FROM EMPLOYEES )
--11)查询各部门中工资比本部门平均工资高的员工号,姓名和工资
-- SELECT DEPARTMENT_ID,LAST_NAME,SALARY
-- FROM EMPLOYEES e1
-- WHERE SALARY >
-- (
-- SELECT avg(SALARY)
-- FROM EMPLOYEES e2
-- WHERE e1.DEPARTMENT_ID = e2.DEPARTMENT_ID --本部门与本部门的进行比较
-- GROUP BY DEPARTMENT_ID
-- );
--12)查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
-- SELECT EMPLOYEE_ID,LAST_NAME
-- FROM EMPLOYEES e1
-- WHERE DEPARTMENT_ID in
-- (
-- SELECT DEPARTMENT_ID
-- FROM EMPLOYEES e2
-- WHERE LAST_NAME LIKE '%u%' --查到的是个集合
-- )
--
-- and LAST_NAME NOT LIKE '%u%'
--13)查询在部门的location_id为1700的部门的员工的员工号
-- SELECT DEPARTMENT_ID,LAST_NAME
-- FROM EMPLOYEES
-- WHERE DEPARTMENT_ID IN
-- (
-- SELECT DEPARTMENT_ID
-- FROM DEPARTMENTS
-- WHERE LOCATION_ID = 1700
-- )
--14)查询管理者是king的员工姓名和工资
-- SELECT LAST_NAME,SALARY
-- FROM EMPLOYEES
-- WHERE MANAGER_ID in
-- (
-- SELECT EMPLOYEE_ID
-- FROM EMPLOYEES
-- WHERE LAST_NAME = 'King'
-- )
-- 查询--谁的工资比Abel高-- SELECT SALARY -- from EMPLOYEES-- WHERE LAST_NAME = 'Abel'-- SELECT LAST_NAME , SALARY-- FROM EMPLOYEES -- WHERE SALARY &gt; (SELECT SALARY FROM EMPLOYEES-- WHERE LAST_NAM...