2022.07.19 - SQL(1)

sofia·2022년 7월 19일
sql
0

SQL

목록 보기
2/13
post-thumbnail

문제 양이 많아 나눠서 정리

⭐복습

  1. EMP 테이블 구조를 확인하기 위한 SQL*Plus 명령문 작성

desc emp;

  1. emp 테이블의 모든 데이터를 출력하여라.

select * from emp;

  1. EMP테이블에서 사번, 이름 직업 출력

select EMPNO,ENAME,JOB from emp;

  1. 사원이 소속된 부서 번호를 중복되는 행 제외 출력

select distinct deptno from emp;

  1. emp 테이블에서 enpno,enamen job,deptno출력
    단, enpno은 '사번',enamen은 '사원명', job은 '직업', deptno은 '부서번호'로 칼럼 별명 부여

select empno "사번",ename "사원명",job" 직업", deptno "부서번호" from emp;

  1. emp 테이블에서 합성 연산자를 사용하여 다음과 같이 출력되도록 SQL 명령문을 작성하여라

    보기 )
    ENAME 직업은 JOB이고, 부서번호는 deptno 이다.

select ename||'직업은 '||job||'이고, 부서번호는 '||deptno||'이다.' from emp;

  1. emp 테이블에서 사원 이름과 직업, 월급, 연봉을 출력하여라. 단, 연봉은 월급에서 12를 곱한 값이다.

select ename, job, sal, sal*12"연봉" from emp;
또는
select ename"사원명", job"직업", sal"월급", sal*12"연봉" from emp;

8. 보너스를 받지 않는 교수의 이름, 급여, 학과 번호 출력

select name, pay, profno from professor where bonus is null;

9. 교수 테이블에서 교수번호, 이름, 급여, 보너스를 급여가 많은 순으로 출력
select profno, name, pay, bonus from professor order by bonus desc;

order by ㅇㅇㅇ acs; : 오름차순(작은것부터 큰것까지)
order by ㅇㅇㅇ decs; : 내림차순(큰것부터 작은것까지)

10. 교수 테이블에서 교수번호, 이름, 급여, 연봉, 학과 번호를 출력하되, 학과 번호와 연봉으로 정렬하여 출력, 학과번호는 내림차순, 연봉은 오름차순 정렬

select profno, name, pay, pay*12"연봉", deptno from professor order by deptno desc, pay*12 asc;

강사님 답)(위의 것도 맞는데 걍 asc 생략한거임)
select profno, name, pay, pay*12"연봉", deptno from professor order by deptno desc, pay*12;

11. 급여가 300 이상이면서 보너스를 받거나 급여가 450인 이상인 교수 이름, 급여, 보너스 출력
select name, pay, bonus from professor where (pay> =300 and bonus is not null) or (pay>=450);

12. 201학과에 소속된 교수와 학생의 번호, 이름을 출력
(학생 테이블은 학과 1 기준)
select profno,name from professor where deptno = 201 union select studno,name from student where deptno1 = 201;

📙단일 행 함수 - 문자 함수

함수설명사용예
INITCAP첫글자만 대문자INITCAP(’abcd’) ⇒ Abcd
LOWER무조건 다 소문자LOWER(’ABCD’) ⇒ abcd
UPPER무조건 다 대문자UPPER(’abcd’) ⇒ ABCD
LENGTH글자수 출력LENGTH(’한글’) ⇒ 2
LENGTHB글자 바이트 출력LENGTHB(’한글) ⇒4
CONCAT글자 합치기( = ll )CONCAT(’A’,’B’) ⇒ AB
SUBSTR글자 선택 출력, 조건 부여SUBSTR(’ABC’,1,2) ⇒ AB
SUBSTRB글자 선택 출력, 조건 부여(바이트 단위)SUBSTRB(’한글’,1,2) ⇒ 한
INSTR찾는 글자 위치 출력INSTR(’A*B#’,’#’) ⇒ 4
INSTRAB찾는 글자 위치 출력(바이트 단위)INSTRAB(’한글로’,’로’) ⇒ 5
LPAD글자 수 모자란 경우 왼쪽에 첨가LPAD(’love’,6,’*’) ⇒ **love
RPAD글자 수 모자란 경우 오른쪽에 첨가RPAD(’love’,6,’*’) ⇒ love**
LTRIM왼쪽에 있는 글자 삭제LTRIM(’love’,’’) ⇒ love
RTRIM오른쪽에 있는 글자 삭제RTRIM(’love’,’’) ⇒ love
REPLACE글자 교체REPLACE(’AB’,’A’,’E’) ⇒ EB

📖예제 1

select ENAME
     , INITCAP(ENAME) "INITCAP1"
     , INITCAP(ENAME) INITCAP2
     , INITCAP(ENAME) AS INITCAP3
     , INITCAP(ENAME) AS "INITCAP4"
     , DEPTNO
  FROM EMP 
where deptno = 10;

입력
결과)

📖예제 2

select ENAME
     , LENGTH(ENAME) "LENGTH"
     , LENGTHB(ENAME) "LENGTHB" 
     -- 영어라서 글자수와 바이트 수 동일
  FROM EMP 
where deptno = 10;

결과

📖예제 3

SELECT NAME
     , LOWER(NAME)
     , UPPER(NAME)
    FROM PROFESSOR;

결과

📖예제 4

	SELECT '서진수' "NAME"
   		 , LENGTH ('서진수') "LENGTH"
   		 , LENGTHB ('서진수') "LENGTH"
  FROM DUAL;
-- 오라클에서 3바이트를 처리하기도 2바이트 처리하기도 함
-- 그냥 시스템 차이라고 이해하면 될 듯

결과

📖예제 5

select CONCAT(ENAME, JOB)
     , ENAME || JOB
  FROM EMP 
where deptno = 10;

📖예제 6

Select JOB
	 , SUBSTR(JOB,3,2)
   FROM EMP ;

📖예제 7

select JOB
    , SUBSTR(JOB,3,2) -- 3번째 글자에서 2글자 출력
    , SUBSTR(JOB,6,2) -- 6번째 글자에서 2글자 출력
    , SUBSTR(JOB,4)    --4번째 글자부터 출력
    , SUBSTR(JOB,-3,2)   -- 뒤에서 3번째 글자부터 2글자 출력
    , SUBSTR(JOB,-5,3)    -- 뒤에서 5번째 글자부터 3글자 출력
  FROM EMP ;

📖예제 8

테이블에서 JUMIN 컬럼 사용해서 전공 1이 101번인 학생들의 이름과 태어난 월일 출력

SELECT NAME
    , SUBSTR(JUMIN,3,4)"BRITHDAY"
    FROM student
WHERE DEPTNO1 = 101 ;

📖예제 9

SELECT '서진수' "NAME"
    ,LENGTH ('서진수') "LENGTH"
    ,LENGTHB ('서진수') "LENGTH"
  FROM DUAL;
-- 오라클에서 3바이트를 처리하기도 2바이트 처리하기도 함
-- 그냥 시스템 차이라고 이해하면 될 듯

📖예제 10

SELECT 'A-B-C-D' "NAME"
    , INSTR ('A-B-C-D','-',1,3) "INSTR"
        -- -를 찾는데 첫번째 - 시작해서 3번째 -를 찾는다.
    , INSTR ('A-B-C-D','-',3,1) "INSTR"
    -- -를 3번째 글자 B에서 첫번째로 나오는 -를 찾는다.
    , INSTR ('A-B-C-D','-',3) "INSTR"
    -- -를 찾는데 첫번째 - 시작해서 3번째 -를 찾는다.(1이 생략됨)
    , INSTR ('A-B-C-D','-') "INSTR"
    -- 전체에서 -이 처음나오는 자리.  1,1이 생략되어 있음 
    , INSTR ('A-B-C-D','-',-1,3) "INSTR88"
    -- 뒤에서 세번째 - 찾기
    , INSTR ('A-B-C-D','-',-3,1) "INSTR"
    -- 뒤에서 3번째 부터 첫번째 - 찾기 
    , INSTR ('A-B-C-D','-',-6,1) "INSTR"
    -- 뒤에서 6번째 글자 (첫번째 -) 부터 - 찾기
    -- 글자 2번째 자리에 위치함
     , INSTR ('A-B-C-D','-',-6,2) "INSTR"
    -- 뒤에서 6번째 글자 (첫번째 -) 부터 두번째 - 찾기 
    -- 존재하지 않음
  FROM DUAL;

📖예제 11

학생 테이블의 TEL 컬럼 이용해서 전공번호1(DEPTNO1)가 201인 학생의 이름과 전화번호,’)’가 나오는 위치 출력

SELECT NAME
    , TEL
    , INSTR (TEL,')',1,1) ")"
    FROM student
    WHERE DEPTNO1 = 201;

📖예제 12

학생테이블에서 전공 1이 101인 학생들의 TEL 컬럼을 조회하여 8이 두번째로 나오는 위치를 이름. 전화번호와 함께 출력하시오

SELECT NAME
    , TEL
    , INSTR (TEL,'8',1,2) "2번째 8의 위치"
    FROM student
    WHERE DEPTNO1 = 101;

📖예제 13

학생 테이블 참조하여 전공 1이 201인 학생의 이름과 전화번호와 지역번호 출력(지역번호는 숫자만)

SELECT NAME
    , TEL
    -- 처음에 했을때 아래와 같이 함 
    --,SUBSTR(TEL,INSTR(TEL,' ',1,1),3) "AREA CODE" ==> 틀림
    , SUBSTR(TEL,1,INSTR(TEL,')',1,1)-1) "AREA CODE"
    -- 진짜 답
    FROM student
    WHERE DEPTNO1 = '201';

참고 사진

📖예제 14

SELECT NAME
    , ID
    ,LPAD(ID,10,'*') 
    -- 10자를 기준으로 모자란 글은 왼쪽부터 * 출력
    FROM student
    WHERE DEPTNO1 = '201';

📖예제 15

EMP 테이블 이용해서 DEPTNO가 10인 사원들의 사원이름(ENAME)을 총 9바이트로 출력하되 빈자리는 해당 자리의 숫자로 채우기

SELECT LPAD(ENAME,9,'123456789') "LPAD"
FROM EMP
WHERE DEPTNO = '10';

📖예제 16

select ENAME
     , RPAD(ENAME, 10, '-')
     -- 10자 기준으로 모자란 영역은 - 출력
  FROM EMP ;

📖예제 17

아래 화면과 같이 emp 테이블에서 deptno가 10번인 사원들의 이름을 총 9자리로 출력하되 오른 쪽 빈자리에는 해당 자리 수에 해당되는 숫자가 출력되도록 하기


<답>
SELECT RPAD(ENAME,9,SUBSTR('123456789',LENGTH(ENAME)+1))"RPAD"
  FROM EMP
 WHERE DEPTNO = 10;

<상세설명>

SELECT LENGTH(ENAME) "이름길이" 
     , SUBSTR('123456789',LENGTH(ENAME)) "이름길이부터 출력"
     , SUBSTR('123456789',LENGTH(ENAME)+1) "이름+1 자리부터 출력"
     ,RPAD(ENAME,9,SUBSTR('123456789',LENGTH(ENAME)+1))"RPAD"
     -- 이름이 끝난 다음 자리 부터 출력되게끔 함
  FROM EMP
 WHERE DEPTNO = 10;


(참고사진)

📖예제 18

SELECT ENAME
     , LTRIM(ENAME,'C') -- 'C' 삭제
     , RTRIM(ENAME,'R') -- 'R' 삭제
     , REPLACE (ENAME, SUBSTR(ENAME,1,2),'**')
     FROM EMP;

📖예제 19

EMP 테이블에서 아래와 같이 20번 부서에 소속되 직원들의 이름과 2-3번째 글자만 '-' 으로 변경

SELECT ENAME
     , REPLACE (ENAME, SUBSTR(ENAME,2,2),'--')"REPLACE"
     -- SUBSTR으로 먼저 2번째글자부터 2글자 출력
     --REPLACE으로 추출한 2글자를 '--' 으로 변경
     FROM EMP
     WHERE deptno =20;

(결과)

ENAMEREPLACE
SMITHS--TH
JONESJ--ES
FORDF--D

📖예제 20

student테이블에서 전공 1이 101번인 학생들의 이름과 주민등록 번호 출력하되, 주민등록 번호 뒤 7자리는 -/ 로 표시

SELECT NAME
     , JUMIN
     ,REPLACE(JUMIN, SUBSTR(JUMIN,7),'-/-/-/-') "REPLACE"
     FROM student
     WHERE deptno1 =101;

📖예제 21

student테이블에서 전공1(DEPTNO1)이 102번인 학생이름. 전화번호, 전화번호에서 국번 부분만 * 처리(단, 모든 국번은 3자리로 간주)

-- 내가 한 방법
SELECT NAME
     , TEL
     , REPLACE(TEL,SUBSTR(TEL,5,3),'***') "REPLACE"
     FROM student
     WHERE deptno1 =102;

--강사님 방법
SELECT NAME
     , TEL
     , REPLACE(TEL,SUBSTR(TEL,INSTR(TEL,'(')+1,3),'***') "REPLACE"
     FROM student
     WHERE deptno1 =102;

강사님 방법으로 하면 위치가 바뀌더라도 적용됨

📖예제 22

student 테이블에서 DEPTNO1가 101인 학과 학생들의 이름과 전화번호, 전화번호에서 지역번호, 국번 제외 나머지 번호 *로 표시해서 출력

SELECT NAME
     , TEL
     , REPLACE(TEL,SUBSTR(TEL,INSTR(TEL,'-')+1,4),'****') "REPLACE"
     --,SUBSTR(TEL,INSTR(TEL,'-')+1,4) -- 9번째 글자부터 4글자 출력
     --,INSTR(TEL,'-') "REPLACE" -- '-'의 글자 위치 8번째
     FROM student
     WHERE deptno1 =101;


(참고 사진)


(정답)

📕기타 출력

  1. 임시값
SELECT * FROM DUAL; 
-- 오라클 자체적으로 가지고 있음, 
-- 임시값을 불러 처리 할때 사용

  1. 절댓값 출력
SELECT ABS(-352) FROM DUAL; -- 절댓값

  1. 숫자 출력
SELECT 1 FROM DUAL; -- 1 출력

  1. 오늘 날짜 출력
SELECT SYSDATE FROM DUAL;  -- 오늘 날짜 출력

profile
sofia
이전 포스트

SQL 정리 2022.07.13

다음 포스트

2022.07.19 - SQL(2)

0개의 댓글