2022.08.09 - SQL(1) 복습 문제

sofia·2022년 8월 9일
sql
0

SQL

목록 보기
7/13
post-thumbnail

📚복습 문제

📖복습 문제 1

PROFESSOR 테이블에서 아래 그림과 같이 홈페이지가 있는 교수들의 이름과 홈페이지, 홈페이지에서 http:// 와 도메인 사이에 있는 부분만 ‘study’ 처리하여 출력하세요.

SELECT
    name,
    hpage,
    replace(hpage,
            substr(hpage,
                   instr(hpage, '/') + 2,
                   instr(hpage, '.') - instr(hpage, '/') - 2),
            'study') "STUDY_HPAGE"
FROM
    professor
WHERE
    hpage IS NOT NULL;

📖복습 문제 2

CUSTOMER 테이브을 조회하여 GNAME,POINT,CLASS 를 아래와 같이 출력하세요.

단 클래스는 POINT를 기준으로 1~99999이면 C, 100000~299999이면 B, 300000~600000 이면 A, 600000보다 많으면 S로 출력하세요.(단 ,POINT 가 많은 순으로 정렬)

SELECT
    GNAME,
    POINT,
    CASE 
    WHEN POINT BETWEEN 1 AND 99999 THEN 'C'
    WHEN POINT BETWEEN 100000 AND 299999 THEN 'B'
    WHEN POINT BETWEEN 300000 AND 600000 THEN 'A'
    WHEN POINT >600000 THEN 'S'
    END "CLASS"
FROM
    customer
    ORDER BY POINT DESC;

📖복습 문제 3

MEMBER 테이블에서 jumin 컬럼을 사용해서 AN_KEY_DAP 컬림이 ‘Ni’로 시작되는 회원들의 이름, 태어난 월, AN_KEY_DAP을 출력하세요

SELECT
    NAME,
    SUBSTR(JUMIN,3,2) "MONTH" , 
    AN_KEY_DAP
FROM
    member
    WHERE AN_KEY_DAP LIKE 'Ni%';

📖복습 문제 4

PROFESSOR 테이블의 EMAIL이 ‘net’으로 끝나는 교수의 이름과, 포지션과, 포지션에서 p가 첫번째로 나오는 위치와 email을 출력하세요

SELECT
    NAME,
    POSITION,
    INSTR(POSITION,'p') "P",
    EMAIL
FROM
    professor
    where EMAIL LIKE '%net';

📖복습 문제 5

PROFESSOR 테이블을 참조해서 홈페이지가 있는 교수의 이름과 홈페이지와 http://와 . 사이의 문자를 출력하세요.

SELECT
    name,
    hpage,
    substr(hpage,
           instr(hpage, '/') + 2,
           instr(hpage, '.') - instr(hpage, '/')-2)"WWW"
FROM
    professor
    where hpage is not null;

📖복습 문제 6

STUDENT 테이블에서 몸무게가 70 미만인 학생들의 이름과 몸무게와 주민번호와 주민번호에서 생년월일을 제외한 숫자를 ‘*’ 로 변경하여 출력

SELECT
    NAME,
    WEIGHT,
    JUMIN,
    REPLACE(JUMIN,SUBSTR(JUMIN,7,7),'*******')"SECURE"
FROM
    STUDENT
    WHERE WEIGHT <70;

📖복습 문제 7

professor 테이블을 조회하여 급여(PAY)의 합계, 평균, 최대값, 최소값, 표준편차, 분산을 출력하시오.

(단, 표준편차, 분산은 반올림 해서 소수점 2자리로 표시)

SELECT
    SUM(PAY) "SUM" , 
    AVG(PAY) "A" ,
    MAX(PAY) "X" ,
    MIN(PAY) "N" ,
    ROUND(STDDEV(PAY),2) "S" ,
    ROUND(VARIANCE(PAY),2) "V"
FROM
    professor;

📖복습 문제 8

PROFESSOR 테이블에서 평균 급여가 300이상인 교수의 포지션과 평균급여를 구하시오.(단 , 평균급여는 반올림해서 소수점 2자리로 표시)

SELECT
   POSITION,
   ROUND(AVG(PAY),2) "AVG"
FROM
    professor
    group by POSITION 
    HAVING AVG(PAY) >= 300;

📖복습 문제 9

EMP 테이블 사용하여 사원들 중에서 급여(SAL)와 보너스(COMM)를 합친 금액이 가장 많은 경우와 가장 적은 경우, 평균 금액을 구하세요.

단, 보너스가 없는 경우에는 보너스를 0으로 계산하고 평균 금액은 소수점 첫째 자리까지만 나오게 하세요.

SELECT
    MAX(sal + nvl(comm, 0)) "MAX",
    MIN(sal + nvl(comm, 0)) "MIN",
    round(AVG(sal + nvl(comm, 0)),1) "AVG"
FROM
    emp;

📖복습 문제 10

STUDENT 테이블의 BIRTHDAY 컬럼을 참조해서 아래와 같이 월별로 생일자수를 출력하시오

SELECT
    COUNT(BIRTHDAY)||'EA' "TOTAL",
    COUNT(DECODE(SUBSTR(BIRTHDAY,4,2),01,01))||'EA' "JAN" ,
    COUNT(DECODE(SUBSTR(BIRTHDAY,4,2),02,02))||'EA' "FEB" ,
    COUNT(DECODE(SUBSTR(BIRTHDAY,4,2),03,03))||'EA' "MAR" ,
    COUNT(DECODE(SUBSTR(BIRTHDAY,4,2),04,04))||'EA' "APR" ,
    COUNT(DECODE(SUBSTR(BIRTHDAY,4,2),05,05))||'EA' "MAY" ,
    COUNT(DECODE(SUBSTR(BIRTHDAY,4,2),06,06))||'EA' "JUN" ,
    COUNT(DECODE(SUBSTR(BIRTHDAY,4,2),07,07))||'EA' "JUL" ,
    COUNT(DECODE(SUBSTR(BIRTHDAY,4,2),08,08))||'EA' "AUG" ,
    COUNT(DECODE(SUBSTR(BIRTHDAY,4,2),09,09))||'EA' "SEP" ,
    COUNT(DECODE(SUBSTR(BIRTHDAY,4,2),10,10))||'EA' "OCP" ,
    COUNT(DECODE(SUBSTR(BIRTHDAY,4,2),11,11))||'EA' "NOV" ,
    COUNT(DECODE(SUBSTR(BIRTHDAY,4,2),12,12))||'EA' "DEC" 
FROM
    STUDENT;

📖복습 문제 11

STUDENT 테이블의 tel 컬럼을 참조해서 아래와 같이 지역별 인우너수를 출력하세요.

SELECT 
    COUNT(TEL) "TOTAL" ,
    COUNT(DECODE(SUBSTR(TEL,1,INSTR(TEL,')')-1),'02','02')) "SEOUL" ,
    COUNT(DECODE(SUBSTR(TEL,1,INSTR(TEL,')')-1),'031','02')) "GYEONGGI" ,
    COUNT(DECODE(SUBSTR(TEL,1,INSTR(TEL,')')-1),'051','02')) "BUSAN" ,
    COUNT(DECODE(SUBSTR(TEL,1,INSTR(TEL,')')-1),'052','02')) "ULSAN" ,
    COUNT(DECODE(SUBSTR(TEL,1,INSTR(TEL,')')-1),'053','02')) "DAEGU" ,
    COUNT(DECODE(SUBSTR(TEL,1,INSTR(TEL,')')-1),'055','02')) "GYEONGNAM" 
FROM 
    STUDENT;

📖복습 문제 12

emp 테이블을 사용하여 직원들의 급여와 전체 급여의 누적 급여금액 출력하시오.(단, 급여를 오름차순으로 정렬해서 출력)

SELECT
    DEPTNO,
    ENAME,
    SAL,
    SUM(SAL) OVER (ORDER BY SAL ASC) "TOTAL"
FROM
    EMP;

📖복습 문제 13

FRUIT 테이블을 아래와같은 형태로 출력하세요.

SELECT
    MIN(DECODE(NAME,'apple',price)) "APPLE",
    MIN(DECODE(NAME,'grape',price)) "GRAPE", 
    MIN(DECODE(NAME,'orange',price)) "ORANGE"
FROM
    FRUIT;

📖복습 문제 14

STUDENT 테이블의 Tel 컬럼을 사용하여 아래와 같이 지역별 인원수와 전체대비 차지하는 비율을 출력하세요

    COUNT(tel)
    || 'EA ('
       || COUNT(tel) / COUNT(tel) * 100
                                    || '%)' "TOTAL",
    COUNT(decode(substr(tel, 1, instr(tel, ')') - 1), '02', '02'))
    || 'EA ('
       || COUNT(decode(substr(tel, 1, instr(tel, ')') - 1), '02', '02')) / COUNT(tel) * 100
                                                                                        || '%)' "SEOUL",
    COUNT(decode(substr(tel, 1, instr(tel, ')') - 1), '031', '02'))
    || 'EA ('
       || COUNT(decode(substr(tel, 1, instr(tel, ')') - 1), '031', '02')) / COUNT(tel) * 100
                                                                                         || '%)' "GYEONGGI",
    COUNT(decode(substr(tel, 1, instr(tel, ')') - 1), '051', '02'))
    || 'EA ('
       || COUNT(decode(substr(tel, 1, instr(tel, ')') - 1), '051', '02')) / COUNT(tel) * 100
                                                                                         || '%)' "BUSAN",
    COUNT(decode(substr(tel, 1, instr(tel, ')') - 1), '052', '02'))
    || 'EA ('
       || COUNT(decode(substr(tel, 1, instr(tel, ')') - 1), '052', '02')) / COUNT(tel) * 100
                                                                                         || '%)' "ULSAN",
    COUNT(decode(substr(tel, 1, instr(tel, ')') - 1), '053', '02'))
    || 'EA ('
       || COUNT(decode(substr(tel, 1, instr(tel, ')') - 1), '053', '02')) / COUNT(tel) * 100
                                                                                         || '%)' "DAEGU",
    COUNT(decode(substr(tel, 1, instr(tel, ')') - 1), '055', '02'))
    || 'EA ('
       || COUNT(decode(substr(tel, 1, instr(tel, ')') - 1), '055', '02')) / COUNT(tel) * 100
                                                                                         || '%)' "GYEONGNAM"
FROM
    student;

📖복습 문제 15

EMP 테이블을 사용하여 부서별로 급여 누적 합계가 나오도록 출력하시오.(부서별 오름차순 출력)

SELECT 
    DEPTNO,
    ENAME,
    SAL,
    SUM(SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL) "TOTAL"
FROM
    EMP
GROUP BY DEPTNO, ENAME, SAL;;

📖복습 문제 16

EMP 테이블을 사용하여 각 사원의 급여액이 전체 직원 급여총액에서 몇%의 비율을 차지하는지 출력하시오. 단, 급여 비중이 높은 사람이 먼저 출력되도록 하세오.

SELECT 
    DEPTNO,
    ENAME,
    SUM(SAL) OVER () "TOTAL_SAL",
    ROUND(SAL/(SUM(SAL) OVER ())*100,2) "()%"
FROM
    EMP
    group by DEPTNO, ENAME, SAL
    ORDER BY SAL DESC;

📖복습 문제 17

EMP 테이블을 조회하여 각 직원들의 급여가 해당 부서 합계 금액에서 몇 % 의 비중을 차지하는지 출력하시오.(단, 부서 번호를 기준으로 오름차순 출력)

SELECT 
    DEPTNO,
    ENAME,
    SAL,
    SUM(SAL) OVER (PARTITION BY DEPTNO) "SUM_DEPT",
    ROUND(SAL / SUM(SAL) OVER (PARTITION BY DEPTNO) *100,2) "%"
FROM
    EMP
    GROUP BY DEPTNO, ENAME, SAL
    ORDER BY DEPTNO;

📖복습 문제 18

loan 테이블을 사용하여 1000번 지점(L_STORE)의 대출 내열을 출력하되, 대출일자(L_DATE), 대출종목 코드(L_CODE), 대출건수(L_QTY), 대출 총액(L_TOTAL), 누적 대출 금액을 아래와 같이 출력하시오,

SELECT 
    L_DATE "대출일자",
    L_CODE "대출종목코드"  ,
    L_QTY "대출건수"  ,
    L_TOTAL "대출총액" ,
    SUM(L_TOTAL)OVER(ORDER BY L_DATE ) "누적대출금액"
FROM
    LOAN
    WHERE L_STORE = '1000';

📖복습 문제 19

loan 테이블을 사용하여 전체 지점의 대출종목코드, 대출지점, 대출일자, 대출건수, 대출액을 대출 코드와 대출지점별로 누적합계를 구하시오

SELECT 
    L_CODE "대출종목코드",
    L_STORE "대출지점",
    L_DATE "대출일자",
    L_QTY "대출건수"  ,
    L_TOTAL "대출액" ,
    SUM(L_TOTAL)OVER(PARTITION BY L_CODE,L_STORE ORDER BY L_DATE ) "누적대출금액"
FROM 
    LOAN;

📖복습 문제 20

SELECT 
    L_DATE "대출일자" ,  
    L_CODE "대출구분코드",
    L_QTY "대출건수"  ,
    L_TOTAL "대출액" ,
    SUM(L_TOTAL)OVER(PARTITION BY L_CODE ORDER BY L_TOTAL ) "누적대출금액"
FROM 
    LOAN
    WHERE L_STORE =1000;

📖복습 문제 21

PROFESSOR 테이블에서 각 교수들의 급여를 구하고, 각 교수의 급여액이 전체 교수의 급여 합계에서 차지하는 비율을 출력하시오

SELECT 
    DEPTNO,
    NAME,
    PAY,
    SUM(PAY) OVER()"TOTAL PAY",
    ROUND(PAY/SUM(PAY) OVER()*100,2)"RATIO %"
FROM 
    PROFESSOR
    GROUNP BY DEPTNO, NAME, PAY
    ORDER BY PAY DESC;

📖복습 문제 22

PROFESSOR 테이블을 조회하여, 학과번호, 교수명, 급여, 학과별 급여 합계를 구하고 각 교수의 급여가 해당 학과별 급여 합계에서 차지하는 비율을 출력하세요.

SELECT 
    DEPTNO,
    NAME,
    PAY,
    SUM(PAY) OVER(PARTITION BY DEPTNO )"TOTAL_DEPTNO",
    ROUND(PAY/SUM(PAY) OVER(PARTITION BY DEPTNO)*100,2) "RATIO %"
FROM 
    PROFESSOR
    GROUP BY DEPTNO,NAME,PAY
    ORDER BY NAME;
--강사님 방법
SELECT 
    DEPTNO,
    NAME,
    PAY,
    SUM(PAY) OVER(PARTITION BY DEPTNO )"TOTAL_DEPTNO",
    ROUND(RATIO_TO_REPORT(SUM(PAY)) OVER(PARTITION BY DEPTNO)*100,2) "RATIO %"
FROM 
    PROFESSOR
    GROUP BY DEPTNO,NAME,PAY
    ORDER BY NAME;
profile
sofia
이전 포스트

2022.08.02 - SQL

다음 포스트

2022.08.09 - SQL(2)

0개의 댓글