Rank() 연습쿼리

-- 1. 기본급 상위 10%만 출력. (이름, 기본급)
SELECT *
  FROM (SELECT name, basicpay,
    RANK() OVER(ORDER BY basicpay DESC) AS rank
    FROM insa)
    WHERE rank<= (SELECT COUNT(*) * 0.1 AS 총원
    FROM insa);
 
--2. 기본급(basicpay) 높은 순위로 5순위까지만 출력
SELECT *
 FROM (SELECT name, basicpay
  ,RANK() OVER(ORDER BY basicpay DESC) AS rank
  FROM insa)
 WHERE rank<=5;

--3. 입사일이 빠른 순서로 5순위까지만 출력.
SELECT *
 FROM (SELECT name, ibsadate
  ,RANK() OVER(ORDER BY ibsadate ASC) AS rank
  FROM insa)
 WHERE rank<=5;
--4. 부서별 기본급이 가장 높은 사람 출력. (이름, 부서, 기본급)
SELECT buseo, name, basicpay
  FROM (SELECT name, buseo, basicpay
    ,RANK() OVER(PARTITION BY BUSEO ORDER BY basicpay DESC) AS rank
    FROM insa)
  WHERE rank=1;
 
--5. 남, 여별 기본급 순위 출력. (이름, 성별, 순위)
SELECT *
  FROM (SELECT name, DECODE(SUBSTR(ssn, 8, 1),1,'남',2,'여',3,'남',4,'여') AS 성별 , buseo, basicpay
    ,RANK() OVER(PARTITION BY SUBSTR(ssn, 8, 1) ORDER BY basicpay DESC) AS rank
    FROM insa);
--6. 지역(city)별로 급여(기본급+수당) 1순위 직원만 출력. GROUP BY 구문 이용. PARTITION BY 이용.
SELECT name, city, 급여
 FROM (SELECT name, city, basicpay+sudang AS "급여"
  ,RANK() OVER(PARTITION BY city ORDER BY basicpay+sudang DESC) AS rank
  FROM insa)
 WHERE rank=1;
--7. 부서별 인원수가 가장 많은 부서 및 인원수 출력.
SELECT buseo, 인원수
  FROM
  (SELECT buseo, COUNT(*) AS 인원수, RANK() OVER(ORDER BY COUNT(*) DESC) AS rank
    FROM insa
    GROUP BY buseo)
  WHERE rank=1;
--8. 지역(city)별 인원수가 가장 많은 지역 및 인원수 출력.
SELECT city, 인원수
  FROM(
  SELECT city, COUNT(*) AS 인원수, RANK() OVER(ORDER BY COUNT(*) DESC) AS rank
  FROM insa
  GROUP BY city)
  WHERE rank = 1;
--9. 지역(city)별 평균 급여(basicpay+sudang)가
--   가장 높은 지역 및 평균급여 출력.
SELECT city, 급여
  FROM (SELECT city, ROUND(AVG(basicpay+sudang)) AS "급여"
  ,RANK() OVER(ORDER BY ROUND(AVG(basicpay+sudang)) DESC) AS rank
  FROM insa
  GROUP BY city)
  WHERE rank = 1;
--10. 여자 인원수가 가장 많은 부서 및 인원수 출력.
SELECT buseo, 여자인원수
  FROM(SELECT buseo, COUNT(*) AS 여자인원수, RANK() OVER(ORDER BY COUNT(*) DESC) AS rank
  FROM insa
  WHERE SUBSTR(ssn, 8, 1) IN (2, 4)
  GROUP BY buseo)
  WHERE rank = 1;
 
--11. 지역별 인원수 순위 출력.
SELECT city, COUNT(*) AS 인원수, RANK() OVER (ORDER BY COUNT(*) DESC) AS rank
  FROM insa
  GROUP BY city;
--12. 지역별 인원수 순위 출력하되 5순위까지만 출력.
SELECT *
  FROM(SELECT city, COUNT(*) AS 인원수, RANK() OVER (ORDER BY COUNT(*) DESC) AS rank
  FROM insa
  GROUP BY city)
  WHERE rank <= 5;
--13. hr 계정. employees, jobs 테이블.
--job_title이 'Finance Manager'인 직무의 최고 급여액(max_salary)보다
--많은 급여(salary)를 받는 직원 출력. 하위쿼리.
SELECT first_name, last_name, salary
  FROM(SELECT first_name, last_name, salary
  , (SELECT max_salary AS maxs FROM jobs WHERE job_title = 'Finance Manager') AS maxs
  FROM employees)
  WHERE salary > maxs;
 
SELECT first_name, last_name, salary
  FROM (SELECT first_name, last_name, salary, max_salary
  FROM employees, jobs
  WHERE job_title = 'Finance Manager')
  WHERE salary > max_salary;