728x90
728x90
* 이 글은 제가 공부하기 위해 최대한 사실에 입각해 내용을 적으려고 하지만 일부 내용들이 정확하지 않을 수 있습니다.
혹시나 잘못된 부분이 있으면 너그럽게 이해해 주시고 피드백 부탁드려요!
1. 조인의 개념
조인(Join)
• 조인의 개념
– 하나의 SQL 명령문에 의해 여러 테이블에 저장된 데이터를 한 번에 조회할 수 있는 기능
– 관계형 데이터베이스 분야의 표준
– 두 개 이상의 테이블을 "결합" 한다는 의미
• 조인의 필요성
– 조인을 사용하지 않는 일반적인 예
• 학생 주소록을 출력하기 위해 학생들의 학번, 이름, 소속학과 이름을 검색
• 학생에 대한 정보 검색하는 단계 필요
• 학생 정보에서 소속학과 번호 정보를 추출하여 소속학과 이름을 검색하는 단계 필요
• 사용 예
– 학번이 10101인 학생의 이름과 소속 학과 이름을 출력하여라.
2. 칼럼 이름의 애매모호성, 테이블 별명, 조인 조건식
칼럼 이름의 애매모호성
• 칼럼 이름의 애매모호성 해결방법
– 서로 다른 테이블에 있는 동일한 칼럼 이름을 연결한 경우 칼럼 이름 앞에 테이블 이름을 접두사로 사용
테이블 이름과 칼럼 이름은 점(.)으로 구분
– SQL 명령문에 대한 구문분석 시간(parsing time) 줄임
• student.deptno와 department.deptno
테이블 별명
• 테이블 이름이 너무 긴 경우 사용
– 테이블 이름을 대신하는 별명 사용 가능
– FROM절에서 테이블 이름 다음에 공백을 두고 별명 정의
– 테이블 별명 작성 규칙
• 테이블의 별명은 30자까지 가능, 너무 길지 않게 작성
• FROM 절에서 테이블 이름을 명시하고 공백을 둔 다음 테이블 별명지정
• 하나의 SQL 명령문에서 테이블 이름과 별명을 혼용할 수 없다
• 테이블의 별명은 해당 SQL 명령문내에서만 유효
• 사용 예
– 테이블 이름과 별명을 혼용하여 사용한 경우
– 테이블 별명을 정확하게 사용한 경우
AND 연산자를 사용한 검색 조건 추가
• 사용 예
– "전인하" 학생의 학번, 이름, 학과 이름 그리고 학과 위치를 출력하여라.
• 실습 예
– 몸무게가 80kg 이상인 학생의 학번, 이름, 체중, 학과 이름, 학과 위치를 출력하여라.
3. 조인의 종류
카티션 곱
• 카티션 곱
– 두 개 이상의 테이블에 대해 연결 가능한 행을 모두 결합
– WHERE 절에서 조인 조건절을 생략하거나 잘못 설정한 경우
– 대용량 테이블에서 발생한 경우 SQL명령문의 처리속도 저하
– 개발자가 시뮬레이션을 위한 대용량의 실험용 데이터를 생성하기 위해 의도적으로 사용 가능
– 오라클 9i 이후 버전에서 FROM절에 CROSS JOIN 키워드 사용
• CROSS JOIN 사용법
• 사용 예
– 학생 테이블과 부서 테이블을 카테션 곱을 한 결과를 출력하여라.
EQUI JOIN
• 개념
– 조인 대상 테이블에서 공통 칼럼을 "="(equal) 비교를 통해 같은 값을 가지는 행을 연결하여 결과를
생성하는 조인 방법
– SQL 명령문에서 가장 많이 사용하는 조인 방법
– 조인 애트리뷰트(join attribute(속성))
• WHERE 절을 이용한 EQUI JOIN 사용법
– FROM : 조인 대상 테이블을 기술한다. 테이블은 콤마(,)로 구분
– WHERE : 조인 애트리뷰트와 "="연산자를 사용하여 조인 조건을 기술
• 사용 예
– 학생 테이블과 부서 테이블을 EQUI JOIN 하여 학번, 이름, 학과 번호, 소속 학과 이름, 학과 위치를
출력하여라
EQUI JOIN – NATURAL JOIN
• 자연조인을 이용한 EQUI JOIN
– 오라클 9i 버전부터 EQUI JOIN을 자연조인이라 명명
– WHERE 절을 사용하지 않고 NATURAL JOIN 키워드 사용
– 오라클에서 자동적으로 테이블의 모든 칼럼을 대상으로 공통 칼럼을 조사 후, 내부적으로 조인문 생성
• 사용법
– 주의
• 조인 애트리뷰트에 테이블 별명을 사용하면 오류가 발생
• 조인 애트리뷰트에서 테이블 별명을 사용한 경우
• 조인 애트리뷰트에서 테이블 별명을 사용하지 않은 경우
• 사용 예
– NATURAL JOIN을 이용하여 교수 번호, 이름, 학과 번호, 학과 이름을 출력하여라.
• 사용 예
– NATURAL JOIN을 이용하여 4학년 학생의 이름, 학과 번호, 학과 이름을 출력하여라.
EQUI JOIN – JOIN ~ USING
• JOIN ~ USING 절을 이용한 EQUI JOIN
– USING절에 조인 대상 칼럼을 지정
– 칼럼 이름은 조인 대상 테이블에서 동일한 이름으로 정의되어 있어야 함
• 사용법
– 주의
• 조인 애트리뷰트에 테이블 별명을 사용하면 오류가 발생
• 사용 예
– JOIN ~ USING 절을 이용하여 학번, 이름, 학과번호, 학과이름, 학과위치를 출력하여라.
• 실습 예
– EQUI JOIN의 3가지 방법을 이용하여 성이 "김"씨인 학생들의 이름, 학과이름, 학과위치를 출력하여라.
– WHERE 절을 사용한 방법
– NATURAL JOIN절을 사용한 방법
– JOIN~USING절을 사용한 방법
NON-EQUI JOIN
• NON-EQUI JOIN
– "<", BETWEEN a AND b와 같이 "=" 조건이 아닌 연산자 사용
• 사용 예
– 교수 테이블과 급여 등급 테이블을 NON-EQUI JOIN 하여 교수별로 급여 등급을 출력하여라.
• 실습 예
– 101번 학과 교수의 교수번호, 이름, 급여, 급여 등급을 출력하여라.
OUTER JOIN
• 개요
– EQUI JOIN의 조인 조건에서 양측 칼럼 값 중, 어느 하나라도 NULL 이면 "=" 비교 결과가 거짓이 되어
NULL 값을 가진 행은 조인 결과로 출력불가
• NULL에 대해서 어떠한 연산을 적용하더라도 연산 결과는 NULL
– 일반적인 EQUI JOIN의 예 :
• 학생 테이블의 학과번호 칼럼과 부서 테이블의 부서번호 칼럼에 대한
EQUI JOIN ( student.deptno = department.deptno ) 한 경우
– 학생 테이블의 deptno 칼럼이 NULL 인 경우 해당 학생은 결과로 출력되지 못함
– EQUI JOIN에서 양측 칼럼 값 중의 하나가 NULL이지만 조인 결과로 출력할 필요가 있는 경우
OUTER JOIN 사용
– OUTER JOIN의 예 :
• 학생 테이블과 교수 테이블을 EQUI JOIN 하여 학생의 지도 교수 이름 출력
• 조건 : 지도 학생을 한 명도 배정받지 못한 교수 이름도 반드시 함께 출력
• (+) 기호를 사용한 OUTER JOIN
– WHERE 절의 조인 조건에서 OUTER JOIN 연산자인 "(+)" 기호 사용
– 조인 조건문에서 NULL 이 출력되는 테이블의 칼럼에 (+) 기호 추가
• 사용법
• 사용 예
– 학생 테이블과 교수 테이블을 조인하여 이름, 학년, 지도교수의 이름, 직급을 출력하여라. 단, 지도교수가 배정되지 않은 학생이름도 함께 출력하여라.
• OUTER JOIN의 제약사항
– OUTER JOIN 연산자(+)는 NULL이 존재하는 칼럼 쪽에 표시
– OUTER JOIN에서는 IN 연산자를 사용 불가
– 다른 조건과 OR 연산자로 결합 불가
• 실습 예
– 학생 테이블과 교수 테이블을 조인하여 이름, 학년, 지도교수 이름, 직급을 출력하여라. 단, 지도학생을 배정받지 않은 교수 이름도 함께 출력하여라.
• OUTER JOIN ~ON 절을 사용한 OUTER JOIN
– 오라클 9i 버전부터 OUTER JOIN 절 사용
• 사용법
• LEFT OUTER JOIN
– FROM 절의 왼쪽에 위치한 테이블이 NULL을 가질 경우에 사용
– WHERE절의 오른편에 "(+)" 기호를 추가한 것과 동일
– 예 : 지도교수가 배정되지 않은 학생 명단을 출력하여라
• RIGHT OUTER JOIN
– FROM 절의 오른쪽에 위치한 테이블이 NULL을 가질 경우, 사용
– WHERE 절의 왼편"(+)" 기호를 추가한 것과 동일
– 예
• 지도학생이 배정되지 않은 교수 명단을 출력하여라
• FULL OUTER JOIN
– LEFT OUTER JOIN과 RIGHT OUTER JOIN을 동시에 실행한 결과를 출력
– 예
• 지도교수가 없는 학생과 지도학생이 배정되지 않은 교수를 함께 출력하라.
• 실습 예
– 학생 테이블과 교수 테이블을 조인하여 학년, 이름, 지도교수 이름, 직급을 출력하여라. 단, 지도교수가 배정되지 않은 학생 명단과 지도 학생이 배정되지 않은 교수 명단도 함께 출력하여라.
– (+) 기호를 사용한 OUTER JOIN(1)
– (+) 기호를 사용한 OUTER JOIN(2)
– OUTER JOIN ~ON절을 사용한 OUTER JOIN
SELF JOIN
• 개요
– 하나의 테이블 내에 있는 칼럼끼리 연결하는 조인이 필요한 경우 사용
– 조인 대상 테이블이 자식 하나라는 것 외에는 EQUI JOIN과 동일
• WHERE 절을 사용한 SELF JOIN
– 한 테이블에서 두 개의 칼럼을 연결하여 EQUI JOIN
– FROM절에서 하나의 테이블에 테이블 별명지정
– 예
• 부서 테이블에서 WHERE 절을 사용하여 SELF JOIN을 처리
• JOIN ~ ON 절을 사용한 SELF JOIN
– 오라클 9i에서 JOIN ~ ON 지원
• 사용법
• 사용 예
– 부서 테이블에서 SELF JOIN을 이용하여 부서 이름과 상위 부서의 이름을 출력하여라.
– WHERE절을 사용한 SELF JOIN방법
– JOIN~ON절을 사용한 SELF JOIN방법
• 사용 예
– 부서 번호가 201 이상인 부서 이름과 상위 부서의 이름을 출력하여라.
– WHERE절을 사용한 SELF JOIN방법
– JOIN~ON절을 사용한 SELF JOIN방법
CREATE TABLE EX_TYPE (
C CHAR(10),
V VARCHAR(10)
);
SELECT * FROM EX_TYPE;
INSERT INTO EX_TYPE VALUES('sql', 'sql');
SELECT * FROM EX_TYPE
WHERE C = 'sql ';
SELECT * FROM EX_TYPE
WHERE V = 'sql';
-- 기본적으로 BOOLEAN으로 나옴(참, 거짓)
-- 공백을 없애야 참으로 나옴
-- 아니면 공백을 포함해서 비교
SELECT * FROM EX_TYPE
WHERE C = V || ' ';
-- 119페이지
-- 이 안에서는 칼럼이 안보임
DESC STUDENT;
-- ROWNUM 행번호(많이 쓰임)
SELECT STUDNO, ROWID, ROWNUM, SYSDATE FROM STUDENT;
-- 129페이지
SELECT 3*6*7*8 FROM STUDENT;
SELECT 3*6*7*8 FROM ROWNUM = 1;
SELECT 3*6*7*8 FROM DUAL;
SELECT SYSDATE FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YY/MM/DD HH24:MI:SS') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YY/MM/DD HH24:MI:SS') AS "시간" FROM DUAL;
-- F10 누르면 COST가 보임 비용이 적을 수록 효율적인 코드
-- 140페이지
-- 학생 테이블에서 1학년 학생만 검색하여 학번, 이름, 학과 번호를 출력하여라.
-- = 비교연산자, GRADE DATA TYPE 은 CHAR 타입
SELECT * FROM STUDENT;
SELECT * FROM STUDENT WHERE GRADE = '1';
SELECT STUDNO, NAME, DEPTNO FROM STUDENT WHERE GRADE = '1';
SELECT STUDNO, NAME, DEPTNO FROM STUDENT WHERE GRADE = 1;
-- 비용은 일종의 비교 지표
-- 밑에 줄은 TO_NUMBER => 숫자로 타입이 바뀌는게 더 좋을 때가 있음
-- 항상 그런 것만은 아님.
-- 142페이지
-- 학생 테이블에서 몸무게가 70KG 이하인 학생만 검색하여 학번, 이름, 학년, 학과번호, 몸무게를 출력하여라.
SELECT * FROM STUDENT;
SELECT STUDNO, NAME, GRADE, DEPTNO, WEIGHT FROM STUDENT WHERE WEIGHT <= 70;
-- 부정연산자 사용
SELECT STUDNO, NAME, GRADE, DEPTNO, WEIGHT FROM STUDENT WHERE NOT WEIGHT <= 70;
-- 144페이지
-- 학생 테이블에서 1학년이면서 몸무게가 70KG 이상인 학생만 검색하여 이름, 학년, 몸무게, 학과번호를 출력하여라.
SELECT * FROM STUDENT WHERE GRADE = '1' AND WEIGHT >= 70;
SELECT NAME, GRADE, WEIGHT, DEPTNO FROM STUDENT WHERE GRADE = '1' AND WEIGHT >= 70;
SELECT NAME, GRADE, WEIGHT, DEPTNO
FROM STUDENT
WHERE GRADE = '1' AND WEIGHT >= 70;
SELECT
NAME, GRADE, WEIGHT, DEPTNO
FROM
STUDENT
WHERE
GRADE = '1' AND WEIGHT >= 70;
SELECT
NAME
, GRADE
, WEIGHT
, DEPTNO
FROM
STUDENT
WHERE
GRADE = '1'
AND
WEIGHT >= 70;
-- 145페이지
-- 위와 동일한 테이블에서 1학년이거나 몸무게가 70KG 이상
SELECT NAME, GRADE, WEIGHT, DEPTNO FROM STUDENT WHERE GRADE = '1' OR WEIGHT >= 70;
-- 146페이지
-- 학생테이블에서 학과번호가 '101'이 아닌 학생의 학번과 이름과 학과번호를 출력해라
SELECT * FROM STUDENT WHERE NOT DEPTNO = '101';
SELECT STUDNO, NAME, DEPTNO FROM STUDENT WHERE NOT DEPTNO = '101';
-- 149페이지
-- BETWEEN 연산자를 사용하여 몸무게가 50KG에서 70KG 사이인 학생의 학번, 이름, 몸무게를 출력하여라.
SELECT * FROM STUDENT WHERE WEIGHT BETWEEN 50 AND 70;
SELECT STUDNO, NAME, WEIGHT FROM STUDENT WHERE WEIGHT BETWEEN 50 AND 70;
SELECT STUDNO, NAME, WEIGHT FROM STUDENT WHERE WEIGHT >= 50 AND WEIGHT <= 70;
-- 150페이지
-- 학생테이블에서 81년에서 83년도에 태어난 학생의 이름과 생년월일을 출력해라
SELECT NAME, BIRTHDATE FROM STUDENT WHERE BIRTHDATE BETWEEN '81/01/01' AND '83/12/31';
-- 날짜는 형변환을 해주는 것이 좋음
SELECT NAME, BIRTHDATE FROM STUDENT
WHERE TO_CHAR(BIRTHDATE, 'YY') BETWEEN 81 AND 83;
-- 학생테이블에서 봄에 태어난 학생의 이름과 생년월일을 출력해라
-- 월정보만 뽑는다는게 쉽지는 않음(국가셋이 바뀔 수 도 있으니)
SELECT NAME, BIRTHDATE FROM STUDENT WHERE TO_CHAR(BIRTHDATE, 'MM') BETWEEN 03 AND 05;
-- 152페이지
-- IN 연산자를 사용하여 102번 학과와 201번 학과 학생의 이름, 학년, 학과번호를 출력하여라
SELECT NAME, GRADE, DEPTNO FROM STUDENT WHERE DEPTNO IN (102,201);
-- 학과번호는 101, 102, 201 밖에 없으니
SELECT NAME, GRADE, DEPTNO FROM STUDENT WHERE DEPTNO != 101;
-- IN을 써서 150페이지 예제 변환해보기
SELECT NAME, BIRTHDATE FROM STUDENT WHERE TO_CHAR(BIRTHDATE, 'YY') IN (81,82,83);
SELECT NAME, BIRTHDATE, TO_CHAR(BIRTHDATE, 'YY') FROM STUDENT WHERE TO_CHAR(BIRTHDATE, 'YY') IN (81,82,83);
-- 153페이지
-- LIKE ~와 같은 전치사 개념
-- 학생 테이블에서 성이 '김'씨인 학생의 이름, 학년, 학과 번호를 출력하여라.
-- %를 붙이면 뒤에 몇글자가 나오든지 상관없음 김O, 김OO
-- 김%, %김, %김%
-- 글자개수 제한 김__ _ : 글자갯수 한글이든 영어든 한글자씩
SELECT NAME, GRADE, DEPTNO FROM STUDENT WHERE NAME LIKE '김%';
SELECT NAME, GRADE, DEPTNO FROM STUDENT WHERE NAME LIKE '김__';
-- 학생테이블에서 이름이 3글자, 성은 '김'씨고 마지막 글자가 '영'으로 끝나는 학생의 이름, 학년, 학과번호를 출력하여라
SELECT NAME, GRADE, DEPTNO FROM STUDENT WHERE NAME LIKE '김_영';
INSERT INTO STUDENT(STUDNO, NAME) VALUES(33333, '황보_정');
INSERT INTO STUDENT(STUDNO, NAME) VALUES(33334, '황보영');
SELECT STUDNO, NAME FROM STUDENT WHERE NAME LIKE '황보%';
SELECT STUDNO, NAME FROM STUDENT WHERE NAME LIKE '황보_%';
SELECT STUDNO, NAME FROM STUDENT WHERE NAME LIKE '황보\_%' ESCAPE '\';
-- 처음 켰던 테이블로 초기화됨
ROLLBACK;
-- ROLLBACK 은 삽입 조회 삭제 등에서만 적용?
-- 164 페이지
-- 교수 테이블에서 이름, 직급, 보직수당을 출력하여라
SELECT NAME, POSITION, COMM FROM PROFESSOR;
-- 166 페이지
-- 교수테이블에서 보직수당이 없는 교수의 이름, 직급, 보직수당을 출력하여라.
SELECT NAME, POSITION, COMM FROM PROFESSOR WHERE COMM IS NULL;
-- SELECT NAME, POSITION, COMM FROM PROFESSOR WHERE COMM = NULL; 이렇게 쓰면안됨
-- 이것은 NULL문자열 검색시 사용
-- 167페이지
-- 교수 테이블에서 급여에 보직수당을 더한 값은 sal_com이라는 별명으로 출력하여라
SELECT NAME, SAL, COMM, SAL+COMM sal_com FROM PROFESSOR;
-- NVL 함수(NULL을 0으로 바꿀수 있음)
-- NVL(NULL이 올수 있는 컬럼 | 값, 널일경우 대체값)
SELECT NAME, SAL, COMM, NVL(COMM, 0), SAL+COMM sal_com FROM PROFESSOR;
SELECT NAME, SAL, COMM, NVL(COMM, 0), SAL+NVL(COMM,0) sal_com FROM PROFESSOR;
-- 170페이지
-- 102번 학과의 학생 중에서 1학년 또는 4학년 학생의 이름, 학년, 학과 번호를 출력하여라.
SELECT NAME, GRADE, DEPTNO FROM STUDENT WHERE DEPTNO = 102 AND GRADE = 1 OR GRADE = 4;
-- 위에 줄은 101번 학과인 학생도 나옴.(비교,논리연산자 때문에) AND부터 처리하고 4학년을 다 땡겨오기 때문
-- OR 연산자를 우선시 해주기 위해 묶어야함.
SELECT NAME, GRADE, DEPTNO FROM STUDENT WHERE DEPTNO = 102 AND (GRADE = 1 OR GRADE = 4);
-- 아니면
SELECT NAME, GRADE, DEPTNO FROM STUDENT WHERE DEPTNO = 102 AND GRADE IN (1,4);
-- 175페이지
-- 1학년이면서 몸무게가 70KG이상인 학생의 집합(STUD_HEAVY)과 1학년 이면서
-- 101번 학과에 소속된 학생(STUD_101)으로 구성된 두 개의 테이블 생성
CREATE TABLE STUD_HEAVY
AS SELECT *
FROM STUDENT
WHERE WEIGHT >= 70 AND GRADE ='1';
CREATE TABLE STUD_101
AS SELECT *
FROM STUDENT
WHERE DEPTNO = 101 AND GRADE = '1';
-- 서재진이 겹침
SELECT * FROM STUD_HEAVY;
SELECT * FROM STUD_101;
-- UNION 합집합
SELECT * FROM STUD_HEAVY
UNION
SELECT * FROM STUD_101;
-- UNION ALL 중복 합집합
SELECT * FROM STUD_HEAVY
UNION ALL
SELECT * FROM STUD_101;
-- INTERSECT 교집합
SELECT * FROM STUD_HEAVY
INTERSECT
SELECT * FROM STUD_101;
-- MINUS 차집합(A-B)
SELECT * FROM STUD_HEAVY
MINUS
SELECT * FROM STUD_101;
-- MINUS 차집합(B-A)
SELECT * FROM STUD_101
MINUS
SELECT * FROM STUD_HEAVY;
-- 183 페이지
SELECT * FROM STUDENT ORDER BY 1;
SELECT * FROM STUDENT ORDER BY STUDNO;
SELECT * FROM STUDENT ORDER BY STUDNO DESC;
-- 185 페이지
-- 학생 테이블에서 이름을 가나다 순으로 정렬하여 이름, 학년, 전화번호를 출력하여라.
SELECT NAME, GRADE, TEL FROM STUDENT ORDER BY NAME;
-- 186 페이지
-- 학생 테이블에서 학년을 내림차순으로 정렬하여 이름, 학년, 전화번호를 출력하여라.
SELECT NAME, GRADE, TEL FROM STUDENT ORDER BY GRADE DESC;
-- SELECT절에 없어도 됨
SELECT NAME, GRADE, TEL FROM STUDENT ORDER BY HEIGHT DESC;
-- 200 페이지
-- 학생 테이블에서 학번이 '20101' 인 학생의 사용자 아이디를 소문자와 대문자로 변환하여 출력하여라.
SELECT USERID, LOWER(USERID), UPPER(USERID) FROM STUDENT WHERE STUDNO = 20101;
-- 202 페이지
-- 부서 테이블에서 부서 이름의 길이를 문자 수와 바이트 수로 각각 출력하여라
SELECT DNAME, LENGTH(DNAME), LENGTHB(DNAME) FROM DEPARTMENT;
-- 205 페이지
SELECT SUBSTR('ABCDE', 2, 3) FROM DUAL;
SELECT SUBSTR('ABCDE', -2, 3) FROM DUAL;
-- 없으면 없는대로 처리함
SELECT SUBSTR('ABCDE ', 6, 3), SUBSTRB('가나다',2) FROM DUAL;
-- 나만 나옴
SELECT SUBSTR('ABCDE ', 6, 3), SUBSTRB('가나다',2, 5) FROM DUAL;
SELECT SUBSTR('ABCDE ', 6, 3), SUBSTRB('가나다',2, 6) FROM DUAL;
SELECT SUBSTR('ABCDE ', 6, 3), SUBSTRB('가나다',2, 7) FROM DUAL;
-- 206 페이지
-- 학생 테이블에서 1학년 학생의 주민등록 번호에서 생년월일과 태어난 달을 추출하여 이름, 주민번호, 생년월일
-- 태어난 달을 출력하여라.
-- DECODE 주민번호 7번째 숫자로 남여 성별 판단
SELECT NAME, IDNUM, SUBSTR(IDNUM, 1, 6) AS "주민앞자리", SUBSTR(IDNUM, 3, 2) AS "월",
DECODE(SUBSTR(IDNUM, 7,1), 1, '남자', '여자')
FROM STUDENT
WHERE GRADE = '1';
-- 나머지연산
SELECT MOD(3,2) FROM DUAL;
-- 207 페이지
-- BC의 위치 찾기
SELECT INSTR('ABCDABCDABCD', 'BC') FROM DUAL;
SELECT INSTR('ABCDABCDABCD', 'BC', 5) FROM DUAL;
SELECT INSTR('ABCDABCDABCD', 'BC', -8) FROM DUAL;
SELECT INSTR('123456789ABC', 'BC', -1) FROM DUAL;
SELECT INSTR('ABCDABCDABCD', 'BC', -1, 2) FROM DUAL;
-- 208 페이지
-- 부서 테이블의 부서 이름 칼럼에서 '과'글자의 위치를 출력하여라.
SELECT DNAME, INSTR(DNAME,'과') FROM DEPARTMENT;
-- 211 페이지
-- 교수테이블에서 직급 칼럼의 왼쪽에 '*' 문자를 삽입하여 10바이트로 출력하고
-- 교수 아이디 칼럼은 오른쪽에 '+'문자를 삽입하여 12바이트로 출력하여라.
SELECT POSITION, LPAD(POSITION, 10, '*'), USERID, RPAD(USERID, 12, '+') FROM PROFESSOR;
-- 아이디 길이에 따라 *표시
SELECT POSITION, LPAD(POSITION, 10, '*'), RPAD(USERID, 12, '+')
,RPAD(SUBSTR(USERID, 1, 3), 12, '*') SHOWN_ID
,RPAD(SUBSTR(USERID, 1, 3), LENGTH(USERID), '*') SHOWN_ID
FROM PROFESSOR;
-- 214 페이지
-- 부서 테이블에서 부서 이름의 마지막 글자인 '과'를 삭제하여 출력하여라
SELECT DNAME, RTRIM(DNAME, '과') FROM DEPARTMENT;
SELECT TRIM(' 가나다라 **') FROM DUAL;
SELECT REPLACE('ABCDEABCDE', 'A', 'F') FROM DUAL;
-- 218 페이지
-- 교수 테이블에서 101학과 교수의 일급을 게산(월근무일은 22일)하여 소수점 첫째자리와
-- 셋째 자리에서 반올림 한 값과 소숫점 왼쪽 첫째 자리에서 반올림한 값을 출력하여라
SELECT NAME, SAL, SAL/22, ROUND(SAL/22), ROUND(SAL/22, 2), ROUND(SAL/22, -1) FROM PROFESSOR
WHERE DEPTNO = 101;
-- 220 페이지
-- 교수 테이블에서 101학과 교수의 일급을 계산(월 근무일은 22일)하여 소수점 첫째자리와
-- 셋째 자리에서 절삭한 값과 소숫점 왼쪽 첫째 자리에서 절삭한 값을 출력하여라
SELECT NAME, SAL, SAL/22, TRUNC(SAL/22), TRUNC(SAL/22, 2), TRUNC(SAL/22, -1)
FROM PROFESSOR WHERE DEPTNO = 101;
-- 222 페이지
-- 교수 테이블에서 101번 학과 교수의 급여를 보직수당으로 나눈 나머지를 계산하여 출력하여라
SELECT NAME, SAL, COMM, MOD(SAL,COMM) FROM PROFESSOR WHERE DEPTNO = 101;
-- 224 페이지
-- 19.7보다 큰 정수 중에서 가장 작은 정수와 12.345보다 작은 정수 중에서 가장 큰 정수를 출력하여라
SELECT CEIL(19.7), FLOOR(12.345) FROM DUAL;
-- 227 페이지
-- 교수 번호가 9908인 교수의 입사일을 기준으로 입사 30일 후와 60일 후의 날짜를 출력하여라
SELECT NAME, HIREDATE, HIREDATE+30, HIREDATE+60 FROM PROFESSOR WHERE PROFNO = 9908;
-- 229 페이지
SELECT SYSDATE FROM DUAL;
-- 수료날까지의 디데이 계산하기
SELECT TO_CHAR(SYSDATE, 'YY/MM/DD HH24:MI:SS')
, TO_DATE('23/06/30 18:30:00', 'YY/MM/DD HH24:MI:SS')
, ROUND(TO_DATE('23/06/30 18:30:00', 'YY/MM/DD HH24:MI:SS') - SYSDATE)
FROM DUAL;
-- 태어난 날로 부터 디데이 계산하기
SELECT TO_CHAR(SYSDATE, 'YY/MM/DD')
, TO_DATE('1993/05/26', 'YY/MM/DD')
, ROUND(SYSDATE - TO_DATE('1993/05/26', 'YY/MM/DD'))
FROM DUAL;
--23.02.01
----------------------------------------------------------------------------
-- 229 페이지
-- 시스템의 현재 날짜를 출력하여라.
SELECT SYSDATE FROM DUAL;
-- 231 페이지
-- 입사한지 360개월 미만인 교수의 교수번호, 입사일, 입사일로부터 현재일까지의 개월 수,
-- 입사일에서 6개월 후의 날짜를 출력하여라.
SELECT
PROFNO, HIREDATE,
MONTHS_BETWEEN(SYSDATE, HIREDATE),
ADD_MONTHS(HIREDATE, 6)
FROM PROFESSOR
WHERE MONTHS_BETWEEN(SYSDATE, HIREDATE) < 360;
-- 233페이지
-- 오늘이 속한 달의 마지막 날짜와 다가오는 일요일의 날짜를 출력하여라
-- KST 적용된 상태(KOREA STANDARD TIME), 다른 시간이면 한국어 안먹힘.
SELECT SYSDATE
, LAST_DAY(SYSDATE)
, NEXT_DAY(SYSDATE, '일요일')
FROM DUAL;
-- 같은 값(밑에것이 더 좋음)
SELECT SYSDATE
, LAST_DAY(SYSDATE)
, NEXT_DAY(SYSDATE, 1)
FROM DUAL;
-- 235페이지
-- 시간 정보를 생략한 경우, ROUND 함수와 TRUNC 함수의 결과를 비교한 예이다.
SELECT TO_CHAR(SYSDATE, 'YY/MM/DD HH24:MI:SS')
, TO_CHAR(TRUNC(SYSDATE), 'YY/MM/DD HH24:MI:SS')
, TO_CHAR(ROUND(SYSDATE), 'YY/MM/DD HH24:MI:SS')
FROM DUAL;
SELECT TO_CHAR(SYSDATE + 3 / 24, 'YY/MM/DD HH24:MI:SS')
, TO_CHAR(TRUNC(SYSDATE + 3 / 24), 'YY/MM/DD HH24:MI:SS')
, TO_CHAR(ROUND(SYSDATE + 3 / 24), 'YY/MM/DD HH24:MI:SS')
FROM DUAL;
-- 월기준 반올림할 때
SELECT ROUND(TO_DATE('231224', 'YYMMDD'), 'MM') FROM DUAL;
-- 236 페이지
-- 101번 학과 교수들의 입사일을 일, 월, 년을 기준으로 반올림하여 출력하여라.
SELECT TO_CHAR(HIREDATE, 'YY/MM/DD HH24:Mi:SS') A
, TO_CHAR(ROUND(HIREDATE, 'DD'), 'YY/MM/DD HH24:Mi:SS') B
, TO_CHAR(ROUND(HIREDATE, 'MM'), 'YY/MM/DD HH24:Mi:SS') C
, TO_CHAR(ROUND(HIREDATE, 'YY'), 'YY/MM/DD HH24:Mi:SS') D
FROM PROFESSOR
WHERE DEPTNO =101;
-- 246 페이지
SELECT STUDNO, TO_CHAR(BIRTHDATE, 'YY.MM')
FROM STUDENT WHERE NAME = '전인하';
-- 247 페이지
SELECT NAME, GRADE, TO_CHAR(BIRTHDATE, 'DAY MONTH DD YYYY ')
FROM STUDENT WHERE DEPTNO = 102;
-- 249 페이지
-- 교수 테이블에서 101번 학과 교수의 이름과 입사일을 출력하여라.
SELECT NAME, TO_CHAR(HIREDATE, 'YYYY MONTH DD, PM HH24:MI;SS')
FROM PROFESSOR
WHERE DEPTNO = 101;
-- 251 페이지
SELECT NAME, TO_CHAR(HIREDATE, 'YYYY MONTH DDTH DDTHSP DAY DY, PM HH24:MI;SS')
, TO_CHAR(SYSDATE, 'DDTH DDTHSP')
FROM PROFESSOR
WHERE DEPTNO = 101;
-- 253 페이지
-- 보직수당을 받는 교수들의 이름, 급여, 보직수당, 그리고 급여와 보직수당을 더한 값에
-- 12를 곱한 결과를 연봉으로 출력하여라.
SELECT NAME, SAL, COMM, TO_CHAR((SAL+COMM)*12, '9,999') "연봉"
FROM PROFESSOR
WHERE COMM IS NOT NULL;
-- 저장 자리값 부족으로 ###이 뜸, 포매터가 무조건 더 크면 값 나옴
SELECT TO_CHAR(1234567890, '9,999') FROM DUAL;
SELECT TO_CHAR(1234567890, '9,999,999,999') FROM DUAL;
-- 257 페이지
-- 주민등록번호에서 생년월일을 추출하여 'YY/MM/DD' 형태로 출력하여라.
SELECT TO_CHAR(TO_DATE(SUBSTR(IDNUM,1,6), 'YYMMDD'), 'YY/MM/DD') A FROM STUDENT;
-- 260 페이지
-- 201번 학과 교수의 이름, 직급, 급여, 보직수당, 급여와 보직수당의 합계를 출력하여라.
-- 단, 보직수당이 NULL인 경우에는 보직수당을 0으로 계산한다.
SELECT NAME, POSITION, SAL, COMM, SAL+COMM, SAL+NVL(COMM, 0), NVL(SAL+COMM, SAL)
FROM PROFESSOR WHERE DEPTNO = 201;
-- 261 페이지
SELECT NAME, POSITION, SAL, COMM
, SAL+COMM
, SAL+NVL(COMM, 0)
, NVL(SAL+COMM, SAL)
, NVL2(COMM, SAL+COMM, SAL)
FROM PROFESSOR WHERE DEPTNO = 201;
-- 262 페이지
SELECT NAME, POSITION, SAL, COMM
, NVL2(COMM, SAL+COMM, SAL) TOTAL
FROM PROFESSOR
WHERE DEPTNO = 102;
-- 270 페이지
SELECT NAME, DEPTNO
, DECODE(DEPTNO, 101, '컴퓨터공학과', 102, '멀티미디어학과', 201, '전자공학과', '기계공학과')
FROM PROFESSOR;
-- 학생테이블에서 생년월일을 기준으로 탄생월을 추출하여 이름, 탄생월, 탄생계절을 조회하시오.
SELECT NAME AS 이름,
TO_CHAR(TO_DATE(SUBSTR(IDNUM,3,2), 'MM'), 'MM') "탄생월"
, SUBSTR(IDNUM,3,2) AS 탄생월
, DECODE(SUBSTR(IDNUM,3,2), 03, '봄', 04, '봄', 05, '봄', 06, '여름', 07, '여름', 08, '여름', 09, '가을', 10,' 가을', 11, '가을', '겨울')
AS 탄생계절
FROM STUDENT;
DESC STUDENT;
SELECT
TO_CHAR(BIRTHDATE, 'MM') A
, TO_CHAR(BIRTHDATE, 'MM') / 3 B
, TRUNC(TO_CHAR(BIRTHDATE, 'MM') / 3) C
, DECODE(TRUNC(TO_CHAR(BIRTHDATE, 'MM') / 3), 1, '봄', 2, '여름', 3, '가을', '겨울') "계절"
, BIRTHDATE
, ADD_MONTHS(BIRTHDATE, -2) "2달땡기기"
, TO_CHAR(ADD_MONTHS(BIRTHDATE, -2), 'Q') 다른방법
, DECODE(TO_CHAR(ADD_MONTHS(BIRTHDATE, -2), 'Q'), 1, '봄', 2, '여름', 3, '가을', '겨울') "계절"
FROM STUDENT;
-- 272페이지
SELECT
NAME, DEPTNO, SAL,
DECODE(DEPTNO, 101, SAL*0.1, 102, SAL*2, 201, SAL*0.3, 0),
CASE DEPTNO
WHEN 101 THEN SAL*0.1
WHEN 102 THEN SAL*0.2
WHEN 201 THEN SAL*0.3
ELSE 0
END A,
CASE
WHEN DEPTNO = 101 THEN SAL*.1
WHEN DEPTNO = 102 THEN SAL*.2
WHEN DEPTNO = 201 THEN SAL*.3
ELSE 0
END B
FROM PROFESSOR;
-- NUMBER 씌워서 해보기
SELECT
NAME, BIRTHDATE,
CASE TO_CHAR(BIRTHDATE, 'MM')
WHEN '1' THEN '봄'
WHEN '2' THEN '여름'
WHEN '3' THEN '가을'
ELSE '겨울'
END A
FROM STUDENT;
SELECT
NAME, BIRTHDATE,
CASE
WHEN TO_CHAR(BIRTHDATE, 'MM') BETWEEN 3 AND 5 THEN '봄'
WHEN TO_CHAR(BIRTHDATE, 'MM') IN(6,7,8) THEN '여름'
WHEN TO_CHAR(BIRTHDATE, 'MM') >= 9 AND TO_CHAR(BIRTHDATE, 'MM') <= 11 THEN '가을'
ELSE '겨울'
END J
FROM STUDENT;
SELECT
CASE
WHEN TO_CHAR(BIRTHDATE, 'MM') BETWEEN 3 AND 5 THEN '봄'
WHEN TO_CHAR(BIRTHDATE, 'MM') IN(6,7,8) THEN '여름'
WHEN TO_CHAR(BIRTHDATE, 'MM') >= 9 AND TO_CHAR(BIRTHDATE, 'MM') <= 11 THEN '가을'
ELSE '겨울'
END J
FROM(
SELECT STUDENT.*, TO_CHAR(BIRTHDATE, 'MM') MON FROM STUDENT
);
SELECT
CASE
WHEN MON BETWEEN 3 AND 5 THEN '봄'
WHEN MON IN(6,7,8) THEN '여름'
WHEN MON >= 9 AND TO_CHAR(BIRTHDATE, 'MM') <= 11 THEN '가을'
ELSE '겨울'
END J
FROM(
SELECT STUDENT.*, TO_CHAR(BIRTHDATE, 'MM') MON FROM STUDENT
);
-- 23-02-02
-------------------------------------------------------------------------------
-- 284페이지
-- 101번 학과 교수 중에서 보직수당을 받는 교수의 수를 출력하여라
SELECT COUNT(*)
FROM PROFESSOR
WHERE DEPTNO = 101;
SELECT COUNT(COMM)
FROM PROFESSOR
WHERE DEPTNO = 101;
SELECT *
FROM PROFESSOR
WHERE DEPTNO = 101 AND COMM IS NOT NULL;
-- 집계함수는 일반컬럼이랑 같이 사용할 수 없음
SELECT COUNT(*), SUM(COMM), AVG(COMM), MAX(COMM), MIN(COMM)
FROM PROFESSOR
WHERE DEPTNO = 101 AND COMM IS NOT NULL;
-- 285 페이지
-- 101번 학과 학생들의 몸무게 평균과 합계를 출력하여라
SELECT AVG(WEIGHT), SUM(WEIGHT)
FROM STUDENT
WHERE DEPTNO = 101;
-- 286 페이지
-- 102번 학과 학생 중에서 최대 키와 최소 키를 출력하여라.
SELECT MAX(HEIGHT), MIN(HEIGHT)
FROM STUDENT
WHERE DEPTNO = 102;
-- 287 페이지
-- 교수 테이블에서 급여의 표준편차와 분산을 출력하여라.
-- 291 페이지
-- 교수 테이블에서 학과별로 교수 수와 보직수당을 받는 교수 수를 출력하여라
SELECT DEPTNO, COUNT(COMM), COUNT(DEPTNO)
FROM PROFESSOR
GROUP BY DEPTNO
ORDER BY 1;
-- 293 페이지
-- 학과별로 소속 교수들의 평균급여, 최소 급여, 최대 급여를 출력하여라
SELECT DEPTNO, POSITION, AVG(SAL), MIN(SAL), MAX(SAL)
FROM PROFESSOR
GROUP BY DEPTNO, POSITION;
-- 294 페이지
-- 전체 학생을 소속 학과별로 나누고, 같은 학과 학생은 다시 학년별로 그룹핑하여
-- 학과와 학년별로 인원수, 평균 몸무게를 출력하여라, 단, 평균 몸무게는 소수점 이하
-- 첫번째 자리에서 반올림 한다.
SELECT DEPTNO, GRADE, COUNT(*), ROUND(AVG(WEIGHT))
FROM STUDENT
GROUP BY DEPTNO, GRADE
ORDER BY 1,2;
-- 201번 학과가 학년이 3,4학년이 없어서 2개만 나옴
-- 297 페이지
-- 소속 학과별로 교수 급여 합계와 모든 학과 교수들의 급여 합계를 출력하여라
SELECT DEPTNO, SUM(SAL)
FROM PROFESSOR
--GROUP BY ROLLUP(DEPTNO);
GROUP BY DEPTNO;
SELECT SUM(SAL)
FROM PROFESSOR;
-- UNION을 쓰기 위해서는 두 컬럼이 갯수가 같아야함.
SELECT DEPTNO, SUM(SAL)
FROM PROFESSOR
--GROUP BY ROLLUP(DEPTNO);
GROUP BY DEPTNO
UNION
SELECT NULL, SUM(SAL)
FROM PROFESSOR;
-- COST 비용가 낮아서 결국 비교하게 됨(f10키)
SELECT DEPTNO, SUM(SAL)
FROM PROFESSOR
GROUP BY ROLLUP(DEPTNO);
-- 298 페이지
-- ROLLUP 연산자를 이용하여 학과 및 직급별 교수수, 학과별 교수수, 전체 교수수를 출력하여라
SELECT DEPTNO, POSITION, COUNT(*)
FROM PROFESSOR
GROUP BY DEPTNO, POSITION
ORDER BY DEPTNO, POSITION;
SELECT DEPTNO, POSITION, COUNT(*)
FROM PROFESSOR
GROUP BY ROLLUP(DEPTNO, POSITION)
ORDER BY DEPTNO, POSITION;
-- 아우터 조인을 쓰면 합계 NULL에 대한 값을 넣을 수 있음.
SELECT DEPTNO, POSITION, COUNT(*)
FROM PROFESSOR
GROUP BY ROLLUP(POSITION, DEPTNO)
ORDER BY DEPTNO, POSITION;
-- UNION 쓴것
SELECT DEPTNO, POSITION, COUNT(*)
FROM PROFESSOR
GROUP BY ROLLUP(DEPTNO, POSITION)
UNION
SELECT DEPTNO, POSITION, COUNT(*)
FROM PROFESSOR
GROUP BY ROLLUP(POSITION, DEPTNO);
-- CUBE 쓴것
SELECT DEPTNO, POSITION, COUNT(*)
FROM PROFESSOR
GROUP BY CUBE(POSITION, DEPTNO)
ORDER BY DEPTNO, POSITION;
-- 299 페이지
-- CUBE 연산자를 이용하여 학과 및 직급별 교수 수, 학과별 교수 수, 전체 교수 수를 출력하여라.
-- 300 페이지
SELECT DEPTNO, POSITION, GROUPING(DEPTNO), GROUPING(POSITION), COUNT(*)
FROM PROFESSOR
GROUP BY CUBE(POSITION, DEPTNO)
ORDER BY DEPTNO, POSITION;
-- NULL이 있는 것들이 결과값 1이 나옴
-- 301 페이지
-- 전체 학생을 학과와 학년별로 그룹화한 후, 학과와 학년별 그룹 인원 수, 학과별
-- 인원 수, 각 그룹 조합에서 학과와 학년 칼럼이 사용되었는지 여부를 출력하여라.
-- 309 페이지
-- 학생 수가 4명 이상인 학년에 대해서 학년, 학생 수 , 평균 키, 평균 몸무게를 출력하여라.
-- 단, 평균 키와 평균 몸무게는 소수점 첫번째 자리에서 반올리하고, 출력순서는 평균 키가
-- 높은 순부터 내림차순으로 출력하여라.
SELECT GRADE, COUNT(*), ROUND(AVG(HEIGHT)), ROUND(AVG(WEIGHT))
FROM STUDENT
GROUP BY GRADE
HAVING COUNT(*) >=4
ORDER BY AVG(HEIGHT) DESC;
SELECT GRADE, COUNT(*), ROUND(AVG(HEIGHT)), ROUND(AVG(WEIGHT))
FROM STUDENT
WHERE PROFNO IS NOT NULL
GROUP BY GRADE
HAVING COUNT(*) >=4
ORDER BY 3 DESC;
SELECT GRADE, COUNT(*), ROUND(AVG(HEIGHT)), ROUND(AVG(WEIGHT))
FROM STUDENT
--WHERE PROFNO IS NOT NULL
GROUP BY GRADE
--HAVING COUNT(*) >=4
ORDER BY 3 DESC;
-- 절별 실행순서 확인하기
SELECT GRADE1, COUNT(*), ROUND(AVG(HEIGHT)), ROUND(AVG(WEIGHT))
FROM STUDENT1
WHERE PROFNO1 IS NOT NULL
GROUP BY GRADE1
HAVING COUNT(STUDENT1) >=4
ORDER BY ROUND(AVG(HEIGHT1)) DESC;
--table or view does not exist
-- FROM 이 1번
SELECT GRADE, COUNT(*), ROUND(AVG(HEIGHT)), ROUND(AVG(WEIGHT))
FROM STUDENT
WHERE PROFNO IS NOT NULL
GROUP BY GRADE
HAVING COUNT(*) >=4
ORDER BY ROUND(AVG(HEIGHT)) DESC;
-- FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY
-- 마트 > 식품층 > 바구니, 카트 > 어떻게 담을지 > 물품선별작업 > 계산 순서
-- 311 페이지
SELECT DEPTNO, AVG(SAL)
FROM PROFESSOR
GROUP BY DEPTNO
HAVING DEPTNO > 102;
SELECT DEPTNO, AVG(SAL)
FROM PROFESSOR
WHERE DEPTNO > 102
GROUP BY DEPTNO;
-- 319페이지
-- 학생의 학번, 학과번호, 학과 이름을 조회
SELECT *
FROM STUDENT, DEPARTMENT;
-- 16 * 7 이어서 112개 행이 나옴
-- 학생한명(전인하) 기준 당 7개의 부서랑 연결
SELECT STUDNO, NAME, STUDENT.DEPTNO, DNAME
FROM STUDENT, DEPARTMENT
WHERE STUDENT.DEPTNO = DEPARTMENT.DEPTNO
ORDER BY 1;
-- 오류발생 DEPTNO가 같아서.
SELECT STUDNO, NAME, DEPTNO, DNAME
FROM STUDENT, DEPARTMENT
WHERE STUDENT.DEPTNO = DEPARTMENT.DEPTNO
ORDER BY 1;
-- 324 페이지
-- 별칭 붙히기
SELECT STUDNO, NAME, S.DEPTNO, DNAME
FROM STUDENT S, DEPARTMENT D
WHERE S.DEPTNO = D.DEPTNO
-- 326 페이지
AND NAME = '전인하';
-- 전인하라는 이름을 가지는 학생의 학번, 이름, 담당교수번호, 담당교수 이름을 조회하시오.
SELECT STUDNO, S.NAME, P.NAME, P.PROFNO
FROM STUDENT S, PROFESSOR P
WHERE S.PROFNO = P.PROFNO
AND S.NAME = '전인하';
SELECT *
FROM PROFESSOR, STUDENT;
SELECT *
FROM STUDENT S, DEPARTMENT D
WHERE S.DEPTNO = D.DEPTNO;
-- 안시조인? 자연조인?
SELECT *
FROM STUDENT
NATURAL JOIN DEPARTMENT;
-- INNER가 생략되어 있는 상태
SELECT *
FROM STUDENT
NATURAL INNER JOIN DEPARTMENT;
SELECT STUDNO, NAME, DEPTNO, DNAME
FROM STUDENT
NATURAL JOIN DEPARTMENT;
-- 23.02.03
-------------------------------------------------------------------------------
-- ANSI JOIN
SELECT STUDNO, NAME, DEPTNO, DNAME
FROM STUDENT
JOIN DEPARTMENT USING(DEPTNO);
-- using, on : ANSI JOIN을 사용하려면
-- 컬럼 이름이 다를 경우는 ON 조건 사용
SELECT STUDNO, NAME, STUDENT.DEPTNO, DNAME
FROM STUDENT
JOIN DEPARTMENT ON STUDENT.DEPTNO = DEPARTMENT.DEPTNO
WHERE NAME = '전인하';
-- 329페이지
SELECT STUDNO, NAME, DNAME
FROM STUDENT
CROSS JOIN DEPARTMENT;
-- 333 페이지
-- 학생 테이블과 부서 테이블을 EQUI JOIN하여 학번, 이름, 학과번호, 소속학과 이름,학과 위치를 출력하여라.
SELECT S.STUDNO, S.NAME, S.DEPTNO, D.DNAME, D.LOC
FROM STUDENT S, DEPARTMENT D
WHERE S.DEPTNO = D.DEPTNO;
-- 뷰라고 불림
WITH A AS (
SELECT 1 A, 2 B FROM DUAL
UNION
SELECT 3, 4 FROM DUAL
)
SELECT *
FROM A
CROSS JOIN A
CROSS JOIN A
CROSS JOIN A;
-- 날짜, 로우넘=행(의사컬럼), 다량의 행 테이블있으면 달력 뽑을 수 있음
-- 데이터 디셕너리
SELECT * FROM ALL_OBJECTS;
-- ALL, USER 3가지 있음
SELECT * FROM DICT;
SELECT COUNT(*) FROM DICT;
SELECT ROWNUM FROM DICT WHERE ROWNUM <=31;
-- 334 페이지
-- 공통컬럼은 반드시 별칭을 붙이면 안됨
SELECT S.STUDNO, DEPTNO
--SELECT S.STUDNO, S.DEPTNO
FROM STUDENT S
NATURAL JOIN DEPARTMENT D;
-- 336 페이지
-- NATURAL JOIN을 이용하교 교수 번호, 이름, 학과 번호, 학과 이름을 출력하여라.
SELECT P.PROFNO, NAME, DEPTNO, D.DNAME
FROM PROFESSOR P
NATURAL JOIN DEPARTMENT D;
-- 337페이지
-- 339 페이지
-- JOIN ~ USING 절을 이용하여 학번, 이름, 학과 번호, 학과 이름, 학과 위치를 출력하여라.
SELECT STUDNO, NAME, DEPTNO, DNAME, LOC
FROM STUDENT
JOIN DEPARTMENT USING(DEPTNO);
-- 340 페이지
-- EQUI JOIN
SELECT *
FROM STUDENT
JOIN DEPARTMENT USING(DEPTNO)
WHERE NAME LIKE '김%';
SELECT NAME, DNAME, LOC
FROM STUDENT
JOIN DEPARTMENT USING(DEPTNO)
WHERE NAME LIKE '김%';
SELECT NAME, DNAME, LOC
FROM STUDENT S
JOIN DEPARTMENT D
ON S.DEPTNO = D.DEPTNO
WHERE NAME LIKE '김%';
SELECT NAME, DNAME, LOC
FROM STUDENT
NATURAL JOIN DEPARTMENT
WHERE NAME LIKE '김%';
SELECT NAME, DNAME, LOC
FROM STUDENT S, DEPARTMENT D
WHERE NAME LIKE '김%' AND S.DEPTNO = D.DEPTNO;
-- 341 페이지
SELECT *
FROM SALGRADE;
SELECT * FROM PROFESSOR;
-- NON-EQUI JOIN
--SELECT S.HISAL, P.*
SELECT NAME, SAL, GRADE
FROM PROFESSOR P
JOIN SALGRADE S ON P.SAL BETWEEN LOSAL AND HISAL;
SELECT DISTINCT PROFNO
FROM PROFESSOR P
JOIN STUDENT S USING(PROFNO);
-- 345 페이지
-- inner join
SELECT STUDNO, S.NAME, PROFNO, P.NAME
FROM STUDENT S
JOIN PROFESSOR P USING(PROFNO);
-- outer join , left join
SELECT STUDNO, S.NAME, PROFNO, P.NAME
FROM STUDENT S
LEFT JOIN PROFESSOR P USING(PROFNO);
-- 삭제하는법 -------------------------------
DELETE STUDENT WHERE STUDIO IN (33333,33334);
COMMIT;
---------------------------------------------
-- 아우터 조인 몰라도 union으로도 표현할 수 는 있음
-- 다만 가독성 등 고려했을 때 아우터 조인 사용
--
-- full join
SELECT STUDNO, S.NAME, PROFNO, P.NAME
FROM STUDENT S
FULL JOIN PROFESSOR P USING(PROFNO);
SELECT STUDNO, S.NAME, PROFNO, P.NAME
FROM STUDENT S
FULL JOIN PROFESSOR P USING(PROFNO)
ORDER BY 1, 3;
SELECT STUDNO, S.NAME, PROFNO, P.NAME
FROM STUDENT S
LEFT OUTER JOIN PROFESSOR P USING(PROFNO);
SELECT *
FROM PROFESSOR P, STUDENT S
WHERE P.PROFNO = S.PROFNO(+);
--WHERE P.PROFNO(+) = S.PROFNO;
-- 이건 안됨
SELECT *
FROM PROFESSOR P, STUDENT S
WHERE P.PROFNO(+) = S.PROFNO(+);
-- 356 페이지
SELECT *
FROM DEPARTMENT D
JOIN DEPARTMENT D1
ON D.DEPTNO = D1.DEPTNO;
-- 내부조인으로 되어있기 때문에 공과대학의 콜리지가 NULL이라서 6개만 나옴
SELECT D1.DNAME || '의 상위 학과는 ' || D2.DNAME
FROM DEPARTMENT D1,DEPARTMENT D2
WHERE D1.COLLEGE = D2.DEPTNO;
SELECT D1.DNAME || '의 상위 학과는 ' || D2.DNAME
FROM DEPARTMENT D1,DEPARTMENT D2
WHERE D1.COLLEGE = D2.DEPTNO(+);
SELECT D1.DNAME || '의 상위 학과는 ' || NVL(D2.DNAME, '없습니다')
FROM DEPARTMENT D1,DEPARTMENT D2
WHERE D1.COLLEGE = D2.DEPTNO(+);
SELECT D1.DNAME || '의 상위 학과는 ' || NVL(D2.DNAME, '없습니다')
FROM DEPARTMENT D1 LEFT JOIN DEPARTMENT D2
ON D1.COLLEGE = D2.DEPTNO;
-- 368 페이지
-- 사용자 아이디가 'JUN123'인
SELECT *
FROM STUDENT
WHERE GRADE = (
SELECT GRADE
FROM STUDENT
WHERE USERID = 'jun123'
);
SELECT STUDNO, NAME, GRADE
FROM STUDENT
WHERE GRADE = (
SELECT GRADE
FROM STUDENT
WHERE USERID = 'jun123'
);
SELECT STUDNO, NAME, GRADE
FROM STUDENT
WHERE GRADE = '4';
-- SELECT STUDNO, NAME, DEPTNO, (?) DNAME
SELECT STUDNO, NAME, DEPTNO, (SELECT DNAME FROM DEPARTMENT WHERE DEPARTMENT.DEPTNO = STUDENT.DEPTNO) DNAME
FROM STUDENT;
-- COST 2
SELECT STUDNO, NAME, DEPTNO,
(SELECT DNAME FROM DEPARTMENT D WHERE D.DEPTNO = S.DEPTNO) DNAME
FROM STUDENT S;
-- COST 5
SELECT STUDNO, NAME, DEPTNO, DNAME
FROM STUDENT NATURAL JOIN DEPARTMENT;
-- 369 페이지
-- 101번 학과 학생들의 평균 몸무게보다 몸무게가 적은 학생의 이름, 학과번호, 몸무게를 출력하여라.
SELECT NAME, DEPTNO, WEIGHT
FROM STUDENT
WHERE WEIGHT < (SELECT AVG(WEIGHT)
FROM STUDENT
WHERE DEPTNO = 101
);
-- 370 페이지
-- 20101번 학생과 학년이 같고, 키는 20101번 학생보다 큰 학생의 이름, 학년, 키를 출력하여라
SELECT NAME, GRADE, HEIGHT
FROM STUDENT
WHERE GRADE = ( -- 1 GRADE
SELECT GRADE
FROM STUDENT
WHERE STUDNO = 20101
)
AND
HEIGHT > ( -- 172CM
SELECT HEIGHT
FROM STUDENT
WHERE STUDNO = 20101
);
-- 373 페이지
-- 정보 미디어학부
SELECT STUDNO, NAME, DEPTNO
FROM STUDENT
WHERE DEPTNO IN (
SELECT DEPTNO FROM DEPARTMENT
WHERE COLLEGE = 100
);
-- 동일한 해법
SELECT STUDNO, NAME, DEPTNO
FROM STUDENT
WHERE DEPTNO IN (101, 102);
-- 해당사항이 없으면 결과값이 나오지 않는다.
SELECT STUDNO, NAME, DEPTNO
FROM STUDENT
WHERE DEPTNO IN (
SELECT DEPTNO FROM DEPARTMENT
WHERE COLLEGE = 300
);
-- 374
SELECT STUDNO, NAME, HEIGHT
FROM STUDENT
WHERE HEIGHT > ANY (
SELECT HEIGHT
FROM STUDENT
WHERE GRADE = '4'
);
-- ANY와 MIN을 결합하면 안됨
SELECT STUDNO, NAME, HEIGHT
FROM STUDENT
WHERE HEIGHT > (
SELECT MIN(HEIGHT)
FROM STUDENT
WHERE GRADE = '4'
);
SELECT STUDNO, NAME, HEIGHT
FROM STUDENT
WHERE HEIGHT < (
SELECT MAX(HEIGHT)
FROM STUDENT
WHERE GRADE = '4'
);
-- 376 페이지
SELECT STUDNO, NAME, HEIGHT
FROM STUDENT
WHERE HEIGHT > (
SELECT MAX(HEIGHT)
FROM STUDENT
WHERE GRADE = '4'
);
-- 377 페이지
SELECT STUDNO, NAME, HEIGHT
FROM STUDENT
WHERE HEIGHT > ALL (
SELECT HEIGHT
FROM STUDENT
WHERE GRADE = '4'
);
-- 379 페이지
SELECT * FROM STUDENT WHERE 1=0;
-- 왜 굳이 볼까?
-- 테이블 구조만 복사해서 생성할때 서브쿼리로 이용하기 위해
SELECT * FROM USER_TABLES;
SELECT * FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'STUDENT';
-- 테이블 구조 복사
-- CREATE TABLE STU2 AS SELECT * FROM STUDENT WHERE 1=0;
-- 380 페이지
-- 보직수당을 받는 교수가
SELECT PROFNO, NAME, SAL, COMM, SAL+NVL(COMM, 0)
FROM PROFESSOR
WHERE EXISTS (SELECT *
FROM PROFESSOR
WHERE COMM IS NOT NULL);
-- 385 페이지
SELECT * FROM STUDENT
WHERE (GRADE, WEIGHT) IN (
SELECT GRADE, MIN(WEIGHT)
FROM STUDENT
GROUP BY GRADE
);
SELECT NAME, GRADE, WEIGHT FROM STUDENT
WHERE GRADE IN (
SELECT GRADE
FROM STUDENT
GROUP BY GRADE
)
AND WEIGHT IN (
SELECT MIN(WEIGHT)
FROM STUDENT
GROUP BY GRADE
)
ORDER BY 2, 3;
SELECT NAME, HEIGHT, DEPTNO FROM STUDENT S1
WHERE HEIGHT < (
SELECT AVG(HEIGHT)
FROM STUDENT S2
WHERE S2.DEPTNO = S1.DEPTNO);
728x90
300x250
'SQL > 데이터베이스' 카테고리의 다른 글
| [풀스택과정] 데이터베이스 9. 데이터 조작어 (0) | 2023.02.07 |
|---|---|
| [풀스택과정] 데이터베이스 8. 서브쿼리 (0) | 2023.02.07 |
| [풀스택과정] 데이터베이스 6. 그룹함수 (0) | 2023.02.03 |
| [풀스택과정] 데이터베이스 5. 함수 (0) | 2023.02.01 |
| [풀스택과정] 데이터베이스 4. WHERE (0) | 2023.02.01 |