본문 바로가기

Oracle

Oracle_02

-- 1. 서울 사람중에서 김씨와 이씨만 출력
SELECT name, city
FROM insa WHERE city='서울' AND (substr(name, 1,1)='김' OR substr(name, 1,1)='이');

-- 2. 모든 테이블의 목록 출력
SELECT * FROM insa;

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

-- 4. 총급여 오름차순으로 출력하되 총급여가 같으면 기본급 오름차순으로 출력(이름, 기본급, 수당, 총급여(기본급+수당))
SELECT name 이름, basicpay 기본급, sudang 수당, basicpay+sudang 총급여
FROM insa ORDER BY basicpay+sudang ASC, basicpay ASC;

-- 5. 남자중에서 name, city, buseo, jikwi 필드만 출력하되 필드명은 한글로 출력
SELECT name 이름, city 출신도, buseo 부서, jikwi 직위
FROM insa WHERE substr(ssn, 8,1)=1;

-- 6. name 필드와 변환 함수를 이용하여 입사 일을 yyyy- mm-dd dw요일 형식으로 출력 한다.(이름, 부서명, 입사일)
SELECT name 이름, buseo 부서, to_char(ibsadate, 'yyyy-mm-dd day') 입사일
FROM insa;

-- 7. 직위를 중복을 배제하여 출력
SELECT DISTINCT jikwi AS "직위"
FROM insa;

-- 8. LIKE를 이용하여 이씨가 아닌 자료만 출력
SELECT name
FROM insa WHERE name NOT LIKE '이%';

-- 9. 전화번호가 NULL인 경우 '전화없음'으로 치환하여 출력하며 
-- 세금은 총급여*3% 로 하되 일의자리에서 반올림한다.
--  (세금과 총급여는 세자리마다 컴마 삽입)
SELECT name, nvl(tel, '전화없음') 전화번호, to_char((basicpay+sudang), '99,999,999') 총급여,
to_char(trunc((basicpay+sudang)*0.03), '99,999,999') 세금
FROM insa;

-- 10. 서울 사람 중 근무 개월 수가 60개월 이상인 사람만 출력(이름, 입사일)  -- MONTHS_BETWEEN 함수 이용
SELECT name 이름, ibsadate "입사일"
FROM insa WHERE months_between(sysdate, ibsadate)>='60';

-- 11. 근무년수가 10년 이상인 사람만 출력(이름, 입사일, 근무년수)
SELECT name 이름, ibsadate 입사일, trunc(months_between(sysdate, ibsadate)/12) 근무년수
FROM insa;

-- 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 기본급, nvl(lpad('*', basicpay/1000000, '*'), ' ') 그래프
FROM insa;

-- 14. 부서별 출신도별 인원수를 출력 한다.
SELECT buseo 부서, count(buseo) "부서별 인원수", city 출신도, count(city) "출신도별 인원수"
FROM insa GROUP BY buseo, city;

-- 15. 부서별 인원수가 가장 많은 부서명 및 인원수를 출력 한다.
SELECT buseo, count(*), rank() over(ORDER BY count(*) DESC) FROM insa GROUP BY buseo;

SELECT buseo, 인원수 FROM (SELECT buseo, count(*) 인원수, rank()
over(ORDER BY count(*) DESC) r FROM insa GROUP BY buseo) WHERE r=1;

-- 16. 부서별 여자 인원수를 출력 한다.
SELECT buseo, count(*) "여자 인원수"
FROM insa WHERE substr(ssn, 8,1)=2 GROUP BY buseo;


-- 17. 부서별 여자 인원수가 가장 많은 부서명 및 여자 인원수를 출력 한다.
SELECT buseo, 인원수 FROM (SELECT buseo, count(*) 인원수, rank()
over(ORDER BY count(*) DESC) r FROM insa WHERE substr(ssn,8,1)=2 GROUP BY buseo) WHERE r=1;


-- 18. 평균 급여(기본급) 및 전체 급여(기본급)를 출력 한다.
SELECT trunc(avg(basicpay)) "평균급여(기본급)", sum(basicpay) "전체급여(기본급)"
FROM insa;


-- 19. 평균 급여(기본급)보다 크거나 같은 사람의 인원수를 출력 한다.
SELECT count(*) "기본급 평균이상"
FROM insa WHERE basicpay >=(SELECT avg(basicpay) FROM insa);

-- 20. 80~89 년생의 인원수를 구하여라.
SELECT count(*) "80~89년생"
FROM insa WHERE substr(ssn, 1,1)=8;

-- 21. 다음의 필드를 출력하라.(성별은 남, 여 로 출력)
--      이름 부서 성별 직위
SELECT name 이름, buseo 부서, decode(substr(ssn,8,1),1,'남',2,'여') 성별, 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,'여');

-- 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)=2 GROUP BY buseo HAVING count(*)>=3;

-- 25. 기본급 하위 10%만 출력(이름, 기본급)
  -- 이름  기본급
SELECT name, basicpay FROM (
SELECT name, basicpay, rank() over(ORDER BY basicpay) 순위 FROM insa)
WHERE 순위 <= trunc((SELECT count(*) FROM insa)*0.1);

'Oracle' 카테고리의 다른 글

11g R2 - LISTAGG 함수  (0) 2012.05.21
Oracle 이론 총 정리  (2) 2011.11.21
Oracle 실습문제 (8)  (0) 2011.11.17
Oracle_03  (0) 2011.11.09
Oracle_01  (0) 2011.11.07