/* 1일차 SQL 교육 */
-- SQL Gate for Oracle 툴 단축키
-- F5, CTRL + F5 = 실행문장 블록 처리 후 F5
-- SELECT * FROM emp; // 주석처리함 / * * / 동일효과
SELECT * FROM player_t;
-- 고객 테이블 생성
CREATE TABLE 고객 (
번호 NUMBER(5) PRIMARY KEY,
이름VARCHAR2(10) NOT NULL,
전화번호 VARCHAR2(13) UNIQUE,
성별CHAR(4) CHECK(성별 IN ('남자', '여자')),
추천인전화 VARCHAR2(13) REFERENCES 고객(전화번호)
);
-- 고객 테이블 모든 데이터 가져오기
SELECT * FROM 고객;
-- 고객 테이블 보여주기
DESC 고객;
-- 고객 테이블에 데이터 입력하기
INSERT INTO 고객 VALUES(1, '김길동', '1234','남자', null);
SELECT * FROM 고객;
INSERT INTO 고객 VALUES(1, '박길동', '2222', '여자', null);
-- Error!! primary key 중복 오류
SELECT * FROM 고객;
SELECT INTO 고객 VALUES(2, '박길동', '1277', '남여', null);
-- Error!! 성별 입력값 Check
SELECT * FROM 고객;
INSERT INTO 고객 VALUES(70, '김구', '1111', '남자', '2222');
SELECT * FROM 고객;
INSERT INTO 고객 VALUES(71, '김구', '1111', '남자', '1234');
-- Error!! 전화번호 입력값 Check
SELECT * FROM 고객;
SELECT ename, sal, sal+500, ename FROM emp;
-- 별칭이 대소문자 구분없이 표기됨(별칭 Result -> RESULT 로 표기)
SELECT ename 이름, sal 급여, sal+500 Result FROM emp;
-- 별칭 대소문자 구분하여 표기하려면 "" 처리
SELECT ename 이름, sal 급여, sal+500 "Result" FROM emp;
SELECT ename 이름, sal*7+100 sal7 FROM emp;
SELECT ename 이름, sal*7+100 7sal FROM emp;
SELECT ename 이름, sal*7+100 "7sal" FROM emp;
SELECT ename 이름, sal*7+100 AS "7sal" FROM emp;
SELECT 'ename', 'salary', 700 FROM emp;
SELECT ename, job FROM emp;
-- 문자열 연결시 '+' 기호가 아닌 '||' 기호를 이용
SELECT ename+job FROM emp; -- 오류!! +기호는 덧셈기호로 쓰임(오라클 불가)
SELECT ename||job 결과 FROM emp; -- 파이프 라인을 통한 문자열 연결
SELECT ename||(''||empno||')' 사원번호 FROM emp; -- 이번 시간의 핵심!!
SELECT deptno FROM emp;
SELECT DISTINCT deptno FROM emp;
SELECT depno, job FROM emp;
SELECT DISTINCT deptno, job FROM emp;
SELECT deptno, DISTINCT job FROM emp;
SELECT team_id FROM player_t;
SELECT DISTINCT team_id FROM player_t;
SELECT empno, ename FROM emp WHERE sal = 3000;
SELECT empno, ename FROM emp WHERE sal >= 3000;
SELECT empno, ename FROM emp WHERE 1 = 1;
SELECT ename, sal FROM emp WHERE ename = scott;
SELECT ename, sal FROM emp WHERE ename = 'SCOTT';
SELECT * FROM player_t;
-- 문제 1.
SELECT player_name, height FROM player_t WHERE height >= 192;
-- 문제 2.
SELECT player_name, height FROM player_t WHERE position = 'DF';
-- 문제 3.
SELECT player_name, position, height FROM player_t WHERE position='GK' AND height>=190;
-- 교재 P.45 BETWEEN 연산자의 사용
SELECT player_name, height FROM player_t WHERE height >=192 AND height <= 195;
SELECT player_name, position, height FROM player_t WHERE height BETWEEN 192 AND 195;
-- BETWEEN 연산자 범위지정시 주의할 것!!
-- BETWEEN 연산자 작성법 : BETWEEN 작은값 AND 큰값
SELECT player_name, position, height FROM player_t WHERE height BETWEEN 195 AND 192; -- 오류!!
-- 문자, 숫자, 날짜로 가능한 BETWEEN 연산
SELECT player_name, height FROM player_t WHERE player_name BETWEEN '박길동' AND '나길동';
-- 교재 P.47 IN연산자의 사용
-- IN은 =ANY, =SOME와 같은 연산을 하며, NOT IN은 !=ALL과 같은 연산을 한다.
-- 속도 같음. 타이핑 단축 효과를 가져옴
SELECT player_name, position, height FROM player_t WHERE height>=192 AND height<=195;
SELECT player_name, height FROM player_t WHERE height =SOME(165,195, 196)
SELECT player_name, height FROM player_t WHERE height =ANY(165, 195, 196)
SELECT player_name, height FROM player_t WHERE height IN (165, 195, 196);
-- 교재 P.49 LIKE 연산자의 사용
-- % : 0개 또는 1개이상 문자와 대응
-- _ : 한 개의 문자와 대응
-- ESCAPE 검색할 문자에 %, _문자 포함시 사용
SELECT player_name, e_player_name FROM player_t WHERE player_name = '가%';
SELECT player_name, e_player_name FROM player_t WHERE player_name LIKE '가%';
-- 표시할 데이터가 없습니다.
SELECT player_name, e_player_name FROM player_t WHERE player_name = '가%';
-- '가'로 시작하는 선수이름을 모두 가져옴
SELECT player_name, e_player_name FROM player_t WHERE player_name LIKE '가%';
-- '가'로 시작하면서 글자수가 2글자인 선수이름을 모두 가져옴
SELECT player_name, e_player_name FROM player_t WHERE player_name LIKE '가_';
-- 교재 P.49 LIKE 연산자의 사용 문제
-- Q1. 선수이름 중 2번째 글자가 '동'인 선수만 이름, 영문 이름 조회
SELECT player_name, e_player_name FROM player_t WHERE player_name LIKE '_동%';
-- Q2. 이름이 2글자인 선수들만 이름, 영문이름 조회
SELECT player_name, e_player_name FROM player_t WHERE player_name LIKE '__' ;
-- Q3. 영문이름에 T문자가 위치상관없이 1개라도 포함된 선수만 조회
SELECT player_name, e_player_name FROM player_t WHERE e_player_name LIKE '%T%';
-- Q4. 영문이름에 %문자가 위치상관없이 1개라도 포함된 선수만 조회
SELECT player_name, e_player_name FROM player_t WHERE e_player_name LIKE '%%%'; -- 의도치 않은 답
SELECT player_name, e_player_name FROM player_t WHERE e_player_name LIKE '%!%%'; -- 결과값 없음
-- 교재 p. 51 LIKE연산자에서 ESCAPE 연산자
-- 특수기능의 %와 _문자를 검색열에 넣을 경우 바른 결과값을 얻을 수 있는 쿼리문
-- ESCAPE 연산자를 사용하여 특수문자를 처리한다.
SELECT player_name, e_player_name FROM player_t WHERE e_player_name LIKE '%!%%' ESCAPE '!';
SELECT player_name, e_player_name FROM player_t WHERE e_player_name LIKE '%!_%' ESCAPE '!';
-- emp 테이블에서 사람, 봉급, 보너스를 가져옴
SELECT ename, sal, comm FROM emp;
-- emp 테이블에서 사람, 봉급, 보너스, 보너스+500을 가져옴
SELECT ename, sal, comm, COMM+500 FROM emp;
-- emp 테이블에서 보너스가 없는 사람, 보너스를 가져옴.
-- 표시할 데이터가 없습니다. 바르지 않은 구문임.
SELECT ename, comm FROM emp WHERE comm = NULL;
-- emp 테이블에서 보너스가 없는 사람, 보너스를 가져옴.
SELECT ename, comm FROM emp WHERE COMM IS NULL; -- 올바른 문장
-- 교재 P.59 NOT 연산자
SELECT ename, sal FROM emp WHERE NOT sal >2500;
-- NOT의 위치는 LIKE, BETWEEN, IN 연산자에서 위치 변경 가능
SELECT ename, sal FROM emp WHERE NOT ename LIKE '%A%';
SELECT ename, sal FROM emp WHERE NOT ename BETWEEN '%A%';
SELECT ename, sal FROM emp WHERE NOT ename IN '%A%';
SELECT ename, sal FROM emp WHERE ename NOT LIKE '%A%';
SELECT ename, sal FROM emp WHERE ename NOT BETWEEN '%A%';
SELECT ename, sal FROM emp WHERE ename NOT IN '%A%';
-- 교재 P.55 논리연산자
-- AND : 양쪽 조건이 모두 TRUE이면 TRUE를 반환
-- OR : 양쪽 조건 중 하나만 TRUE이면 TRUE를 반환
-- NOT : 뒤따르는 조건이 FALSE인 경우 TRUE를 반환
-- 문제1> player_t선수중에서 포지션이 TM, GK, MF, DF,FW 모두 아닌 선수들의
-- 이름, 포지션 정보 조회하기.
-- 방법1>
SELECT player_name, position FROM PLAYER_T
WHERE position !='TM' AND position !='GK' AND
position !='MF' AND position !='DF' AND
position !='FW';
-- 결과 : 30 개의 행이 선택되었습니다. (하재훈~윤성효)
-- 방법2>
SELECT player_name, position FROM PLAYER_T
WHERE position != ALL('TM','GK','MF','DF','FW');
-- 결과 : 30 개의 행이 선택되었습니다. (하재훈~윤성효)
-- 방법3>
SELECT player_name, position FROM PLAYER_T
WHERE position NOT IN ('TM','GK','MF','DF','FW');
-- 결과 : 30 개의 행이 선택되었습니다. (하재훈~윤성효)
-- 문제2> 키가 167보다 작거나 195보다 큰 선수들만
-- 이름, 키, 포지션 조회하기.
-- 방법1>
SELECT player_name, height, position
FROM PLAYER_T
WHERE height < 167 OR height > 195; -- 결과 : 서동명 196 GK, 박강조 165, MF
-- 방법2>
SELECT player_name, height, position
FROM PLAYER_T
WHERE height NOT BETWEEN 167 AND 195;
-- 문제3> emp 사원중 ename에 A문자가 2번이상 나오는 사원들만 이름, 급여조회
SELECT ename, sal FROM emp WHERE ename LIKE '%A%A%'; -- ADAMS 100
-- 문제4> emp 사원중 ename에 A문자가 딱한번만 나오는사원들만 이름, 급여조회.
SELECT ename, sal FROM emp
WHERE ename LIKE '%A%' AND ename NOT LIKE '%A%A%';
-- 결과 : 6 개의 행이 선택되었습니다.
-- ALLEN 1600, WARD 1250, MARTIN 1250, BLAKE 2850, CLARK 2450, JAMES 950
-- 문제5> K01 팀 선수중에서 키가 190 보다 크거나 포지션이 DF인
-- 선수들만 이름, 팀id, 키, 포지션 정보 나오도록 조회하기.
-- 방법 1)
SELECT player_name, team_id, height, position
FROM PLAYER_T
WHERE team_id='K01' AND height>190 OR position='DF';
-- 결과 : 130 개의 행이 선택되었습니다. (우르모르 ~ 손대호)
-- 방법 2) 결과값 비교
SELECT player_name, team_id, height, position
FROM PLAYER_T
WHERE team_id='K01' AND (height>190 OR position='DF');
-- 결과 : 15 개의 행이 선택되었습니다.
-- 제형진, 권정혁, 서동명, 김윤구, 끌레베르, 박경삼, 박종욱, 변성환, 손상호
-- 유재형, 조세권, 하성용, 한정일, 서덕규, 홍인기
-- 교재 P.63 데이터 정렬
-- 1. 테이블 자료 조회시 결과나오는 순서는?
--> DISK에 자료가 저장되어있는 순서대로 꺼내와서 보여줌.
SELECT ename, sal FROM emp;
-- 자료 저장순서와 관계없이 특정값 기준 정렬해서 조회시 order by절 사용함.
-- 기본정렬은 오름차순정렬(작은값먼저 점점값이 증가함) 기본값 : asc 키워드
SELECT ename, sal FROM emp ORDER BY sal;
-- 결과 : SMITH, JAMES, ADAMS, WARD, MARTIN, MILLER, TURNER ....
-- 내림차순 정렬시 desc 키워드 사용해야함.
SELECT ename, sal FROM emp ORDER BY sal DESC;
-- 결과 : KING, SCOTT, FORD, JONES, BLAKE, CLARK, ALLEN ....
-- 4. 정렬할때 null값은 어디위치에 나오는지 확인
--- 1) null값은 오름차순 정렬(asc)시 bottom
SELECT ename, comm FROM emp ORDER BY comm;
-- 결과 : TURNER, ALLEN, WARD, MARTIN, SMITH, JONES, JAMES ....
--- 2) null값은 내림차순 정렬(desc)시 top
SELECT ename, comm FROM emp ORDER BY comm DESC;
-- 결과 : SMITH, JONES, CLARK, BLAKE, SCOTT, KING, JAMES ....
--- 3) 내림차순 정렬시 null 값을 명시적으로 bottom 정렬하는 키워드
SELECT ename, comm FROM emp ORDER BY comm DESC nulls LAST;
-- 결과 : MARTIN, WARD, ALLEN, TURNER, SMITH, JONES, JAMES ....
-- 5. 정렬시 여러 컬럼사용하면 동작은?
--- 1)
SELECT ename, deptno, sal FROM emp ORDER BY deptno DESC, sal;
-- 결과 : JAMES, WARD, MARTIN, TURNER, ALLEN, BLAKE ....
--- 2)
SELECT ename, deptno, sal FROM emp ORDER BY deptno DESC, sal DESC;
-- 결과 : BLAKE, ALLEN, TURNER, WARD, JAMES, SCOTT ....
-- 6. select 절에 없는 컬럼으로도 정렬가능한가?
--- 1)
SELECT ename, sal FROM emp ORDER BY hiredate;
-- 결과 : SMITH, CLARK, ALLEN, WARD, JONES, BLAKE ....
--- 2)
SELECT ename, sal, hiredate FROM emp ORDER BY hiredate;
-- 결과 : SMITH, CLARK, ALLEN, WARD, JONES, BLAKE ....
-- 교재 P.75 단일행 함수
-- 1. 대소문자 변환시 : LOWER(), UPPER(), INITCAP()
SELECT player_name, e_player_name, UPPER(e_player_name), INITCAP(e_player_name)
FROM PLAYER_T
WHERE player_name LIKE '가%';
-- 결과 :
-- 가이모토 KAIMOTO KOJIRO KAIMOTO KOJIRO Kaimoto Kojiro
-- 가비 Gabriel Popescu GABRIEL POPESCU Gabriel Popescu
-- 2. 문자열 글자수 조회시 length vs. lengthB
SELECT player_name, LENGTHB(player_name), e_player_name, LENGTHB(e_player_name)
FROM PLAYER_T
WHERE player_name = '가비';
-- 결과 : 가비 4 Gabriel Popescu 15
-- 3. 문자열의 일부만 추출시 substr(대상, 추출시작위치, 갯수)
SELECT e_player_name, SUBSTR(e_player_name, 1, 3), SUBSTR(e_player_name, 12)
FROM PLAYER_T
WHERE player_name='가비';
-- 결과 : Gabriel Popescu Gab escu
SELECT player_name, SUBSTRB(player_name, 1, 4)
FROM PLAYER_T
WHERE player_name='가이모토';
-- 결과 : 가이모토 가이
-- 4. LPAD(대상, 공간, 남은공간 채울 문자), RPAD(대상, 공간, 남은공간 채울 문자)
-- ex) LPAD(계좌번호, 15, '0')
SELECT ename, LPAD(ename, 10, '*') FROM emp;
-- 결과 : SMITH *****SMITH, ALLEN *****ALLEN, WARD ******WARD, ....
-- 5. 왼쪽끝/오른쪽끝에 나오는 특정문자/공백제거시
---- LTRIM(대상, 제거할 문자), RTRIM, TRIM(제거할문자 from 대상)
SELECT ename, LTRIM(ename,'A') 왼쪽A제거 FROM emp;
SELECT ename, RTRIM(ename,'T') 오른쪽T제거 FROM emp;
SELECT ename, TRIM('T' FROM ename) 양쪽T제거 FROM emp;
-- 6. 특정문자가 어느 위치에 나오는지 알려주는 함수
---- INSTR( 대상, 찾을문자, 검사시작위치, 횟수)
------ ex)instr(후기, '바보', 1,1), instr(후기, '바보', 1,2)
SELECT ename, INSTR(ename, 'A', 1, 1) FROM emp;
/* 결과
SMITH 0
ALLEN 1
WARD 2
JONES 0
MARTIN 2
BLAKE 3
CLARK 3
SCOTT 0
KING 0
TURNER 0
ADAMS 1
JAMES 2
FORD 0
MILLER 0
*/
SELECT ename, INSTR(ename, 'A', 1, 2) FROM emp;
/* 결과
SMITH 0
ALLEN 0
WARD 0
JONES 0
MARTIN 0
BLAKE 0
CLARK 0
SCOTT 0
KING 0
TURNER 0
ADAMS 3
JAMES 0
FORD 0
