오라클 문제 풀이

1. 서울 사람중에서 김씨와 이씨만 출력(이름)

SELECT name FROM insa WHERE SUBSTR(name,1,1) IN ('김', '이')

   AND city='서울';

2. 모든 테이블의 목록 출력

SELECT * FROM tab;

3. INSA 테이블의 스키마(구조) 출력

DESC insa;

4. 총급여 오름차순으로 출력하되 총급여가 같으면 기본급 오름차순으로 출력(이름,기본급,수당,총급여(기본급+수당))

SELECT name, basicpay, sudang, sudang+basicpay pay FROM insa ORDER BY pay, basicpay;

5. 남자중에서 name, city, buseo, jikwi 필드만 출력하되 필드명은 한글로 출력

SELECT name 이름, city 출신도, buseo 부서, jikwi 직위 FROM insa WHERE SUBSTR(ssn, 8, 1) IN ('1', '3');

6. name 필드와 변환 함수를 이용하여 입사 일을 yyyy-mm-dd 요일 형식으로 출력 한다.(이름, 부서명, 입사일)

SELECT name AS 이름, buseo AS 부서명,

TO_CHAR(ibsadate,'YYYY-MM-DD DAY') AS 입사일 FROM insa;

7. 직위를 중복을 배제하여 출력

SELECT DISTINCT jikwi FROM insa;

8. LIKE를 이용하여 이씨가 아닌 자료만 출력

SELECT name, city FROM insa WHERE name NOT LIKE '이%';

9. 전화번호가 NULL인 경우 '전화없음'으로 치환하여 출력하며 세금은 총급여*3% 로 하되 일의자리에서 반올림한다.

  (세금과 총급여는 세자리마다 컴마 삽입)

SELECT name 이름,

          TO_CHAR(basicpay+sudang,'9,999,999') 총급여, 

          TO_CHAR(ROUND((basicpay+sudang)*0.03,-1), 

'9,999,999') 세금, NVL(tel, '전화없음') 전화번호 FROM insa;

10. 서울 사람 중 근무 개월 수가 60개월 이상인 사람만 출력(이름, 입사일)  -- MONTHS_BETWEEN 함수 이용

SELECT name, ibsaDate FROM insa WHERE city = '서울' AND 

         MONTHS_BETWEEN(SYSDATE, ibsaDate) >= 60;

11. 근무년수가 10년 이상인 사람만 출력(이름, 입사일, 근무년수)

SELECT name, ibsadate,

          TRUNC(MONTHS_BETWEEN(SYSDATE, ibsadate)/12) 근무년수

          FROM INSA

 WHERE TRUNC(MONTHS_BETWEEN(SYSDATE, ibsadate)/12)>=10;

12. 오늘날짜, 이번주 일요일, 이번주 토요일날짜를 출력

SELECT SYSDATE, 

      CASE

        WHEN TO_CHAR(SYSDATE, 'DAY') = '일요일' THEN SYSDATE

        ELSE NEXT_DAY(SYSDATE,1)-7

      END,

      CASE

        WHEN TO_CHAR(SYSDATE, 'DAY') = '토요일' THEN SYSDATE

        ELSE NEXT_DAY(SYSDATE,'토요일')

      END

FROM DUAL;

13. 기본급 100 만원 당 *를 하나씩 출력 한다.(이름, 기본급, 그래프) 단, 기본급이 100만원 미만인 경우 *를 출력하지 않고 아무것도 출력하지 않는다. 

SELECT name, basicPay, 

        LPAD('*', TRUNC(basicPay/1000000), '*') 

        AS graph 

        FROM insa;

14. 부서별 출신도별 인원수를 출력 한다.

SELECT buseo, city, COUNT(*) 인원수 FROM insa GROUP BY buseo, city;

15. 부서별 인원수가 가장 많은 부서명 및 인원수를 출력 한다.

SELECT buseo, 인원수 FROM (SELECT buseo, COUNT(*) 인원수, 

        RANK() OVER(ORDER BY COUNT(*) DESC) 순위 FROM insa 

        GROUP BY buseo ) WHERE 순위 = 1;

16. 부서별 여자 인원수를 출력 한다.

SELECT buseo, COUNT(*) 인원수 FROM insa WHERE SUBSTR(ssn, 8, 1) IN ('2', '4')

        GROUP BY buseo;

17. 부서별 여자 인원수가 가장 많은 부서명 및 여자 인원수를 출력 한다.

SELECT buseo, 인원수 FROM (SELECT buseo, COUNT(*) 인원수, 

        RANK() OVER(ORDER BY COUNT(*) DESC) 순위 FROM insa 

        WHERE SUBSTR(ssn, 8, 1) IN ('2', '4')

        GROUP BY buseo ) WHERE 순위 = 1;

18. 평균 급여(기본급) 및 전체 급여(기본급)를 출력 한다.

   SELECT AVG(basicPay), SUM(basicPay) FROM insa;

19. 평균 급여(기본급)보다 크거나 같은 사람의 인원수를 출력 한다.

   SELECT COUNT(*) FROM insa WHERE basicPay >= (SELECT AVG(basicPay) FROM insa);

20. 80~89 년생의 인원수를 구하여라.

   SELECT COUNT(*) FROM insa WHERE SUBSTR(ssn, 1, 1) = '8';

21. 다음의 필드를 출력하라.(성별은 남, 여 로 출력)

      이름 부서 성별 직위

  SELECT name, buseo, 

     DECODE(MOD(SUBSTR(ssn, 8,1), 2), '1', '남', '0', '여') 성별 

     , jikwi FROM INSA;

22. 부서별 성별(남, 여) 인원수를 출력

  SELECT buseo, 

     DECODE(SUBSTR(ssn, 8, 1), '1', '남자', '2', '여자') 성별,

     COUNT(*) FROM INSA

     GROUP BY buseo, DECODE(SUBSTR(ssn, 8, 1), '1', '남자', '2', '여자')

     ORDER BY BUSEO;

23. 이름, 성별, 기본급, 순위를 출력하되 순위는 기본급에 대한 성별 순위를 출력 한다.

    SELECT name,

       DECODE(SUBSTR(ssn, 8, 1), '1', '남자', '2', '여자') 성별

        ,basicpay,

        RANK() OVER(PARTITION BY DECODE(SUBSTR(ssn, 8, 1), '1', '남자', '2', '여자')

            ORDER BY basicpay DESC) 순위

       FROM INSA;

24. 부서별 여자인원수가 3명이상인 부서와 여자인원수출력

SELECT buseo, COUNT(*) FROM INSA

       WHERE SUBSTR(ssn, 8, 1) IN (2, 4)

       GROUP BY buseo

       HAVING COUNT(*) >= 3;

25. 기본급 하위 20%만 출력(이름, 기본급)

  -- 이름  기본급

  SELECT name, basicpay FROM (

      SELECT name, basicpay,

         RANK() OVER(ORDER BY basicpay ASC) 순위

         FROM insa

     ) WHERE 순위 <=

       TRUNC((SELECT COUNT(*) FROM insa) * 0.2);