Oracle---子查询（五）

-- 查询
--谁的工资比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'
-- )