본문 바로가기
IT코딩공부!

#14 DB공부 3일차!! (예제문제)및 문법공부

history. 2023. 4. 7. 17:21

오늘의 노래!!

 
Zero
아티스트
NewJeans
앨범
Zero
발매일
2023.04.03

 

 

[0] 각 조별로 예제문제 풀기

--[1조] 문제
--Q.1 00년생 이전에 태어난 사람중에 미추홀구에 살고 취미가 컴퓨터인 사람 검색!
SELECT * FROM pystu 
WHERE STU_DAY <= '000101' AND STU_ZU LIKE '%미추홀구%' AND STU_CH LIKE '컴퓨터';

--날짜변경 방법 >> 주의할것!!
ALTER SESSION SET NLS_DATE_FORMAT = 'YY-MM-DD'; 
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';


-- Q.2 주소에 XX시가 써 있는 사람 중 번호가 8번이상 12번 이하
SELECT * FROM pystu 
WHERE STU_NUM BETWEEN 8 AND 12 AND STU_ZU LIKE '%시%'; 

-- Q3. 二十歳から二十三才までの人で住所に仁川が書いてある人を検索してください。
-- Q3. 20살부터 23살까지의 사람 중 주소에 인천이 써있는 사람을 검색해 주세요
SELECT * FROM pystu 
WHERE STU_AGE BETWEEN 20 AND 23 AND STU_ZU LIKE '인천%'; 

--[2조] 문제
--Q1. 성이 김이고 7월생인 30세 미만
SELECT * FROM pystu 
WHERE STU_AGE < 30 AND STU_NUMBER LIKE '김%' AND STU_DAY LIKE '__/07%';

--Q2. 이름에 '현'이 포함되거나  취미가 감상종류이거나 90년대생 
SELECT * FROM pystu 
WHERE STU_NUMBER LIKE '%현%' OR  STU_DAY LIKE '9%'  OR STU_CH = '%감상%';

--Q3. 생년월일에 1이 들어가고 주소가 3글자인 사람
SELECT * FROM pystu 
WHERE  STU_ZU  LIKE '___' AND STU_DAY LIKE '%1%';

--[3조] 문제
-- 1. 2조 이면서 이름의 성이 '이'씨, 생일 97년생인 학생   
SELECT * FROM pystu
WHERE STU_ZO LIKE '2' AND STU_NUMBER LIKE '이%'  AND STU_DAY LIKE '%'; 

-- 2. 30세 미만이면서 생일이 4월이고 4조인 학생    
SELECT * FROM pystu
WHERE STU_ZO = 4 AND STU_AGE < 30 AND STU_DAY LIKE '__/04';

-- 3. 미추홀구에 살고 40 초과인 학생  입니다.
SELECT * FROM pystu
WHERE STU_ZU LIKE '%미추홀구%' AND  STU_AGE > 40;

--[4조] 문제
-- Q.1 생일이 11월달 생 중에 , 이름에 윤자가 들어가는 학생 검색
SELECT * FROM pystu
WHERE STU_DAY LIKE '__/11%' AND STU_NUMBER LIKE '%윤%' ;
--WHERE STU_DAY LIKE '%11%' AND STU_NUMBER NOT LIKE '%윤%' ;


-- Q.2 나이가 21 22 23 , 서구주민
SELECT * FROM pystu
WHERE STU_ZU LIKE '%서구%' AND STU_AGE IN(21,22,23);

-- Q3. 3조인 사람중에 99년에 안 태어나고, 성이 '김' 씨가 아닌 학생을 검색!
SELECT * FROM pystu
WHERE STU_ZO LIKE '3' AND STU_NUMBER NOT LIKE  '김%' AND STU_DAY NOT LIKE '99%';

--[5조] 문제
-- Q1. 2023년 기준으로 5년후 30살 이상인 학생의 이름, 나이, 5년후 나이 데이터를 이름의 내림차순으로 표시
SELECT STU_NUMBER AS 이름, STU_AGE AS 나이, STU_AGE+5 AS "5년후" FROM PYSTU
WHERE STU_AGE+5 >=30
ORDER BY STU_NUMBER DESC;

-- Q2. 전화번호 중 연속된 숫자가 있는 학생 검색
-- %11%, %22%, %33%, %44%, %55%, %66%, %77%, %88%, %99%, %00%
-- QR 사용해서 검색

-- Q3. 생일이 3월21일 부터 8월21일인 학생 검색
SELECT * FROM PYSTU WHERE TO_CHAR(STU_DAY, 'MM/DD') >> 기억하기 (날짜 변환)
BETWEEN '03/21' AND '08/21';

-- Q4. 신발사이즈 컬럼 추가하고 조원들에게 사이즈물어보고 등록
-- 신발사이즈 아는 학생 출력    == IS NOT NULL
-- 신발사이즈 모르는 학생 출력  == IS NULL

[1] 문법 공부!

 

/*
    [1] 대, 소문자 바꿔주는 함수
    [2] 문자열 길이 구하는 함수
    [3] 문자열 일부를 추출하는 함수
    [4] 문자열 데이터 안에서 특정 문자 위치 찾는 함수
    [5] 특정문자 바꾸는 함수
    [6] 문자를 합치는 함수
    [7] 빈공간을 특정문자로 채우는 함수


    ↓ 밑에서 순서대로 예제문제 풀면서 공부해 보았다1
*/

 

[1] 대, 소문자 바꿔주는 함수
     - UPPER : 모두 대문자로
     - LOWER : 모두 소문자로
     - INITCAP : 첫글자 대문자, 나머지 소문자로

 

-- DUAL : 임시테이블, 내용확이용

SELECT UPPER('AbCdEfG') AS "UPPER(대문자)",
                LOWER('AbCdEfG') AS "LOWER(소문자)",
                INITCAP('AbCdEfG') AS "INITCAP(첫글자대문자)"
FROM DUAL;     

// 출력

순서대로 글자형태를 바꾸는걸 볼 수 있다

[2] 문자열 길이 구하는 함수
     - LENGTH(데이터) : 데이터의 길이
     - LENGTHB(데이터): 데이터의 크기

 

--영어, 숫자, 특수문자는 1BYTE, 한글은 3BYTE

SELECT LENGTH ('AbCdEfG')AS "문자열 길이"
FROM DUAL;   --문자열 길이 (7)

SELECT LENGTH ('Ab@@@RKSKEK가나다라마바사아자차카타파하')AS "문자열 길이"
FROM DUAL;   --문자열 길이 (25)

SELECT LENGTH ('가')AS "문자열 길이 차이점"
FROM DUAL;  --문자열 길이 (1)

-------------------------------------- 문자열에 차이점 확인

SELECT LENGTHB ('AbCdEfG')AS "문자열 길이"
FROM DUAL;    --문자열 길이 (7)

SELECT LENGTHB ('Ab@@@RKSKEK가나다라마바사아자차카타파하')AS "문자열 길이"
FROM DUAL;    --문자열 길이 (53)

SELECT LENGTHB ('가')AS "문자열 길이 차이점"
FROM DUAL;    --문자열 길이 (3)

 

-- LENGTH함수를 이용해 주소가 3글자인 학생의 정보를 구하여라
SELECT * FROM pystu
WHERE LENGTH (STU_ZU)  =3;

 

출력값

 

-- LENGTH함수를 이용해 이름이 5글자인 사원의 이름과 길이를 구하시오
SELECT ENAME AS "이름", LENGTH(ENAME)AS "이름길이" FROM EMP
WHERE LENGTH (ENAME)  >= 5;

출력값

[3] 문자열 일부를 추출하는 함수 : SUBSTR
    SUBSTR(데이터, 시작위치, 추출길이(생략시 끝까지))
    데이터 >> '문자열 데이터' OR [컬럼이름]

 

-- HELLO!! 에서 문자열의 2번째(E)부터 2개만 추출
SELECT SUBSTR('HELLO!!',2,2) FROM DUAL;

 

--직무에서 1번째 글자부터 2글자
SELECT SUBSTR(JOB,1,2)AS 직무 FROM EMP;

 

--3번째 글자부터 2글자
SELECT SUBSTR(JOB,3,2) AS 직무 FROM EMP;

 

--5번째 글자부터 끝까지 출력
SELECT SUBSTR(JOB,5) AS 직무 FROM EMP;

 

--(PYSTU 테이블)에서 성과 이름을 따로 출력하시오!
SELECT SUBSTR(STU_NUMBER,1,1)AS 성 , SUBSTR(STU_NUMBER,2)AS 이름 FROM PYSTU;

[4] 문자열 데이터 안에서 특정 문자 위치 찾는 함수
    INSTR(데이터, '찾으려는 문자', '시작위치(기본값1), 같은 문자일 경우 몇번째 문자인지(기본값1));
    EX) 'HELLO, ORACLE!' 문자에서 위치 찾아보기!

 

SELECT INSTR('HELLO, ORACLE!', 'L'),
       INSTR('HELLO, ORACLE!', 'L', 5),
       INSTR('HELLO, ORACLE!', 'L', 2,3),
       INSTR('HELLO, ORACLE!', 'O',1,1),
       INSTR('HELLO, ORACLE!', 'O', 1,2)
FROM DUAL;

 

--(1)1번째 'L'를 찾는 함수;
SELECT INSTR('HELLO, ORACLE!', 'L')FROM DUAL;

 

--(2)5번째 글자부터 첫번째 'L'를 찾는 함수;
SELECT INSTR('HELLO, ORACLE!', 'L', 5)FROM DUAL;

 

--(3)2번쨰 글자부터 3번째 'L'찾는 함수;
SELECT INSTR('HELLO, ORACLE!', 'L', 2,3)FROM DUAL;

 

--(4)1번쨰 글자부터 1번째 'O'찾는 함수;
SELECT INSTR('HELLO, ORACLE!', 'O')FROM DUAL;

 

--(5)1번쨰 글자부터 2번째 'O'찾는 함수;
SELECT INSTR('HELLO, ORACLE!', 'O', 1,2)FROM DUAL;

[5] 특정문자를 다른문자로 바꾸는 함수
        (1) 기본형 :  REPLACE(데이터, '찾을문자') : 찾은 문자 삭제
        (2) 선택형 :  REPLACE(데이터, '찾을문자', '바꿀 문자')

 

SELECT '010-1232-45671' AS 전화번호,
    REPLACE('010-1232-45671', '-') AS 기본형,
    REPLACE('010-1232-45671', '-', ' ') AS 선택형
FROM DUAL;

[6] 문자를 합치는 함수 : CONCAT
    
    CONCAT(데이터A , 데이터B)

 

SELECT CONCAT('안녕',' 하세요') FROM DUAL;
SELECT CONCAT('안녕',CONCAT('하세여','이이')) FROM DUAL;

출력값

--PYSTU >> 15번 : 박연진
SELECT CONCAT(STU_NUM, CONCAT('번 학생 : ', STU_NUMBER)) FROM pystu;

 

--EMP >> 7501 : KING
SELECT EMPNO || '번 사원 : ' || ENAME FROM EMP;

SELECT '힘내세여' || ENAME || '님' AS 화이팅 FROM EMP WHERE JOB = 'SALESMAN'; 

[7] 빈공간을 특정문자로 채우는 함수
     - LPAD(데이터, 데이터자릿수, 빈공간에 채울 문자(기본값 ' ')) 
     - RPAD(데이터, 데이터자릿수, 빈공간에 채울 문자(기본값 ' '))

 

SELECT  'ICIA' AS "주어진 데이터",
        LPAD('ICIA', 7) AS "LPAD 기본형",
        RPAD('ICIA', 7) AS "RPAD 기본형",
        LPAD('ICIA', 7, '#') AS "LPAD 선택형",
        RPAD('ICIA', 7, '#') AS "RPAD 선택형"
FROM DUAL;

출력값

--Q. 주민등록 번호 230407-3456789인 사람의 주민번호를 230407-3******으로 바꾸기!
SELECT RPAD (SUBSTR('230407-3456789', 1,8),14,'*') 
FROM DUAL; 

출력값
출력값

[2] 예제!

// 테이블 생성

-- 테이블 생성 전 DROP 진행 후 생성
-- 테이블 생성후 순성의 반대로 (마지막에 만든 테이블 삭제)
DROP TABLE SALGRADE;
DROP TABLE BONUS;
DROP TABLE EMP;
DROP TABLE DEPT;

// 부서 테이블

-- DEFT(부서 : DEPARTMENT) 테이블
CREATE TABLE DEPT(
    DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,-- DEPTNO :부서번호
	DNAME VARCHAR2(14) ,                            -- DNAME : 부서이름
	LOC VARCHAR2(13)                                -- LOC : 지역
);

INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

// 사원 테이블

--EMP(사원 : EMPLOYEE) 테이블
CREATE TABLE EMP(
    EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,--EMPNO : 사원번호
	ENAME VARCHAR2(10),                           --ENAME : 사원이름
	JOB VARCHAR2(9),                    --JOB : 직급, 직무
	MGR NUMBER(4),                      --MGR : 상사의 사원번호
	HIREDATE DATE,                      --HIREDATE : 입사일
	SAL NUMBER(7,2),                    --SAL : 급여
	COMM NUMBER(7,2),                   --COMM : 수당
	DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT --DEPTNO : 부서번호
);

INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-2020','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-2021','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-2021','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-2021','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-2021','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-2021','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-2021','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-07-2016','dd-mm-yyyy')-85,3000,NULL,20);
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-2021','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-2021','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,to_date('13-07-2017','dd-mm-yyyy')-51,1100,NULL,20);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-2021','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-2021','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-2022','dd-mm-yyyy'),1300,NULL,10);

// 수당 테이블

--BONUS(수당) 테이블
CREATE TABLE BONUS(     
	ENAME VARCHAR2(10),     --ENAME : 직원이름
	JOB VARCHAR2(9),        --JOB : 직무, 직급
	SAL NUMBER,             --SAL : 급여
	COMM NUMBER             --COMM : 수당
);

//  급여수준 테이블

--SALGRADE(급여수준) 테이블
CREATE TABLE SALGRADE( 
    GRADE NUMBER,   --GRADE : 급여 등급
	LOSAL NUMBER,   --LOSAL : 최저급여
	HISAL NUMBER    --HISAL : 최고급여
);

INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);

--모든 데이터를 데이터베이스에 저장
COMMIT;

 

--테이블 확인
SELECT * FROM DEPT;
SELECT *FROM EMP;
SELECT * FROM BONUS;
SELECT * FROM SALGRADE;

 

--조회 시 별칭을 사용해서 조회
SELECT * FROM EMP;

 

--(1) 직무(JOB)의 종류 조회
SELECT DISTINCT JOB AS "직무 종류" FROM EMP;

 

--(2) 급여가 2000이상 3000이하인 사원의 (번호, 이름, 급여)를 조회
SELECT EMPNO AS 번호 , ENAME AS 이름, SAL AS 급여 FROM EMP
WHERE SAL BETWEEN 2000 AND 3000; 

 

--(3) 2021년 이후 입사한 사원 조회
SELECT * FROM EMP
--WHERE HIREDATE  >= '21/01/01';
WHERE TO_CHAR(HIREDATE, 'YY')>'21';

--(4) 이름이 S로 끝나는 사원 조회
SELECT * FROM EMP
WHERE ENAME LIKE '%S';

--(5) 수당을 받지 않은 사원(번호, 이름, 급여, 연봉(급여X12)) 조회
SELECT EMPNO, ENAME, SAL, SAL*12 FROM EMP
WHERE COMM IS NULL;

 

 

--(6) 수당을 받는 사원(번호, 이름, 급여, 연봉(급여X12 + 수당) )조회
SELECT EMPNO, ENAME, SAL, SAL*12+COMM FROM EMP
WHERE COMM IS NOT NULL;

 

--(7) 30번 부서에서 근무하고 직무가 SALESMAN인 사원 조회
SELECT *FROM EMP
WHERE JOB  = 'SALESMAN' AND DEPTNO = 30;

--(8) 20,30번 부서에서 근무하고 2000 초과인 사원 조회
SELECT *FROM EMP
WHERE DEPTNO IN (20, 30) AND  SAL >2000;

// 오늘 교육은 여러예제를 풀어보면서 문법을 이해하게 되었다!

'IT코딩공부!' 카테고리의 다른 글

#16 !CONSTRAINT(제약조건)!  (0) 2023.04.11
#15 집합  (0) 2023.04.10
#13 DB(DDL,DML,DCL)공부  (0) 2023.04.06
#12 DB공부 시작!  (0) 2023.04.05
#11 this, super, final 개념 정리 (+ 오라클 설치)  (0) 2023.04.04

'IT코딩공부!' Related Articles

티스토리툴바