Oracle SQL Developer (SELECT_Option, DDL) 복습

coldZero·2023년 11월 28일

OracleSQLDeveloper

목록 보기

4/4

SELECT(Option)관련 문제

문제 1.

국어국문학과에서 총 평점이 가장 높은 학생의 이름과 학번을 표시하는 SQL문을 작성하시오

SELECT STUDENT_NO, STUDENT_NAME
FROM (SELECT STUDENT_NO, STUDENT_NAME, AVG(POINT) 평점
        FROM TB_GRADE
        JOIN TB_STUDENT USING(STUDENT_NO)
        WHERE DEPARTMENT_NO = (SELECT DEPARTMENT_NO
                    FROM TB_DEPARTMENT
                    WHERE DEPARTMENT_NAME = '국어국문학과')
        GROUP BY STUDENT_NO, STUDENT_NAME
        ORDER BY 평점 DESC)
WHERE ROWNUM = 1;

서브쿼리(인라인뷰-INLINE-VIEW)를 사용
인라인뷰안에 서브쿼리를 사용
ROWNUM은 가상컬럼이기에 SELECT문에 작성 안해도 사용가능

문제 2.

춘 기술대학교 서반아어학과 학생들의 지도교수를 게시하고자 한다, 학생이름과 지도교수 이름을 찾고 만일 지도 교수가 없는 학생일 경우 "지도교수 미지정"으로 표시하도록 하는 SQL 문을 작성하시오.(단, 출력헤더는 "학생이름", "지도교수"로 표시하며 고학번 학생이 먼저 표시되도록 한다.)

SELECT STUDENT_NAME 학생이름, NVL(PROFESSOR_NAME, '지도교수 미지정') 지도교수
FROM TB_STUDENT S
LEFT JOIN TB_PROFESSOR ON (COACH_PROFESSOR_NO = PROFESSOR_NO)
JOIN TB_DEPARTMENT D ON (S.DEPARTMENT_NO = D.DEPARTMENT_NO)
WHERE DEPARTMENT_NAME = '서반아어학과'
ORDER BY STUDENT_NO;

JOIN을 할 때 LEFT사용은 NULL값까지 표현하기 위해 사용
JOIN 순서에 잘 맞게 사용하고 그에 따른 컬럼값 확인하여 사용
JOIN구문 테이블에 별칭을 사용하여 구분하여 사용

문제 3.

예체능 계열 과목 중 과목 담당교수를 한 명도 배정받지 못한 과목을 찾아 그 과목 이름과 학과 이름을 출력하는 SQL 문장을 작성하시오.(단, 결과 행의 수만 동일하게 조회)

SELECT CLASS_NAME, DEPARTMENT_NAME
FROM TB_CLASS
LEFT JOIN TB_CLASS_PROFESSOR USING (CLASS_NO)
JOIN TB_DEPARTMENT USING (DEPARTMENT_NO)
WHERE CATEGORY = '예체능'
AND PROFESSOR_NO IS NULL;

JOIN순서 확인
JOIN시 컬럼명 및 컬럼값 확인 후 JOIN 사용
'IS NULL'은 NULL인 값만 표현

DDL이란?

DATA DEFINITION LANGUAGE : 데이터 정의 언어
객체(OBJECT)를 만들고(CREATE), 수정(ALTER)하고, 삭제(DROP) 등 데이터의 전체 구조를 정의하는 언어로 주로 DB관리자, 설계자가 사용한다.

오라클에서의 객체란?

테이블(TABLE), 뷰(VIEW), 시퀀스(SEQUENCE), 인덱스(INDEX), 패키지(PACKAGE), 트리거(TRIGGER), 프로시져(PROCEDURE), 함수(FUNCTION), 동의어(SYNONYM), 사용자(USER)

1) 테이블 생성하기

테이블이란?
행(row)과 열(column)으로 구성되는 가장 기본적인 데이터베이스 객체이며, 데이터베이스 내에서 모든 데이터는 테이블을 통해서 저장된다.

[표현식]
 CREATE TABLE 테이블명 (
        컬럼명 자료형(크기), 
        컬럼명 자료형(크기),
        ...);

자료형 종류(주로사용)
1. NUMBER : 숫자형(정수, 실수)
2. CHAR(크기) : 고정길이 문자형 (2000BYTE)
  -> ex) CHAR(10) 컬럼에 'ABC' 3BYTE 문자열만 저장해도 10BYTE 저장공간을 모두 사용.
3. VARCHAR2(크기) : 가변길이 문자형 (4000 BYTE)
  -> ex) VARCHAR2(10) 컬럼에 'ABC' 3BYTE 문자열만 저장하면 나머지 7BYTE를 반환함.
4. DATE : 날짜 타입
5. BLOB : 대용량 이진 데이터 (4GB)
6. CLOB : 대용량 문자 데이터 (4GB)

-- MEMBER 테이블 생성
CREATE TABLE MEMBER(
    MEMBER_ID VARCHAR2(20), -- 가변 길이 문자열 20바이트(영어, 숫자만 작성 시 20글자)
    MEMBER_PWD VARCHAR2(20),
    MEMBER_NAME VARCHAR2(30), -- 한글 3BYTE * 10글자 == 30BYTE
    MEMBER_SSN CHAR(14), -- 990808-1234567
    ENROLL_DATE DATE *DEFAULT SYSDATE
); -- Table MEMBER이(가) 생성되었습니다.

*DEFRULT : 입력되는 값이 없거나 'DEFAULT' 키워드 사용 시에 기록되어질 값을 지정

2) 테이블에 만든 컬럼에 주석 달기

[표현식]
COMMENT ON COLUMN 테이블명.컬럼명 IS '주석내용';

COMMENT ON COLUMN MEMBER.MEMBER_ID IS '회원 아이디'; -- Comment이(가) 생성되었습니다.
COMMENT ON COLUMN MEMBER.MEMBER_PWD IS '회원 비밀번호';
COMMENT ON COLUMN MEMBER.MEMBER_NAME IS '회원 이름';
COMMENT ON COLUMN MEMBER.MEMBER_SSN IS '주민 등록 번호';
COMMENT ON COLUMN MEMBER.ENROLL_DATE IS '회원 가입일';

3) 테이블에 데이터 삽입

INSERT INTO MEMBER VALUES('MEM01', '123ABC', '홍길동',
							'990808-1234567', DEFAULT);
COMMIT; -- DB반영

COMMIT은 메모리 버퍼(트랜잭션)에 임시 저장된 데이터 변경 사항을 DB에 반영하기 위해서 사용한다.
(즉, 데이터 저장을 셋팅 및 저장)

테이블을 생성할 때 아래와 같이 DEFAULT를 넣어주어서 사용이 가능

ENROLL_DATE DATE DEFAULT SYSDATE -- 가입일

가입일을 위처럼 "SYSDATE"를 사용할 수도 있고 "DEFAULT"를 사용하여 값을 넣을 수도 있다.
"DEFAULT"를 사용하여 값을 넣으면 테이블 생성 시 정의된 값이 반영된다.
또한, INSERT시 미작성 하는 경우에는 "DEFAULT"값이 반영된다.

제약조건(CONSTRAINTS)

사용자가 원하는 조건의 데이터만 유지하기 위해서 특정 컬럼에 설정하는 제약이며 데이터 무결성을 보장한다.

이 때, 데이터 무결성이란?
중복데이터 X (최소화), NULL X (최소화를 목표, 지향)

제약조건을 사용하는 목적
1. 입력 데이터에 문제가 없는지 자동으로 검사하기 위함
2. 데이터의 수정/삭제 가능 여부 검사등을 목적으로 위함
이 때, 제약조건을 위배하는 *DML구문은 사용할 수 없다.

DML(Data Manipulation Langauage) : 데이터 조작 언어, 테이블에 값을 삽입하거나(INSERT), 수정하거나(UPDATE), 삭제(DELETE)하는 구문

제약조건 종류

PRIMARY KEY
NOT NULL
UNIQUE
CHECK
FOREIGN KEY

1. 제약조건 - NOT NULL

해당 컬럼에 반드시 값이 기록되어야 하는 경우 사용하며, 삽입 / 수정 시 NULL값을 허용하지 않도록 컬럼레벨에서 제한한다.

CREATE TABLE USER_USED_NN(
    USER_NO NUMBER NOT NULL, -- NULL(X)
    USER_ID VARCHAR2(20),
    USER_PWD VARCHAR2(30),
    USER_NAME VARCHAR2(30),
    GENDER VARCHAR2(10),
    PHONE VARCHAR2(30),
    EMAIL VARCHAR2(50)
);
NUMBER에 값을 넣으려면 NULL이 아닌 다른 값을 꼭 넣어야한다.

2. 제약조건 - UNIQUE

컬럼 입력 값에 대해서 중복을 제한하는 제약조건이며, 컬럼레벨, 테이블레벨에서 설정 가능하다.
단, UNIQUE 제약조건이 설정된 컬럼에 NULL값은 중복으로 삽입이 가능하다.

CREATE TABLE USER_USED_UK(
    USER_NO NUMBER,
    
	USER_ID VARCHAR2(20) UNIQUE,
    --컬럼레벨 제약조건 설정 (제약조건명 미지정)
    
	--USER_ID VARCHAR2(20) CONSTRAINT USER_ID_U UNIQUE,
    --컬럼레벨 제약조건 설정 (제약조건명 지정)
    
    USER_ID VARCHAR2(20),
    USER_PWD VARCHAR2(30),
    USER_NAME VARCHAR2(30),
    GENDER VARCHAR2(10),
    PHONE VARCHAR2(30),
    EMAIL VARCHAR2(50),
    
    UNIQUE(USER_ID)
    --테이블 레벨 제약조건 설정(제약조건명 미지정)
    
    CONSTRAINT USER_ID_U UNIQUE(USER_ID)
    --테이블 레벨 제약조건 설정(제약조건명 지정)

3. 제약조건 - UNIQUE(복합키)

두 개 이상의 컬럼을 묶어서 하나의 UNIQUE 제약 조건을 설정하며, 이 때 모든 컬럼의 값이 같아야 중복으로 취급하고, 복합키는 테이블레벨로만 지정할 수 있다.

CONSTRAINT USER_ID_NAME_U UNIQUE(USER_ID, USER_NAME)
--괄호안에 ','를 사용

값을 저장할 때,
두 컬럼이 모두 중복되는 값일 경우에만 오류가 발생한다.

4. 제약조건 - PRIMARY KEY (기본키)

테이블에서 한 행의 정보를 찾기 위해 사용할 컬럼을 의미한다.
테이블에 대한 식별자(IDENTIFIRE) 역할을 한다.
NOT NULL + UNIQUE 제약조건의 의미한다.

한 테이블당 한개만 설정할 수 있으며, 컬럼레벨, 테이블레벨 둘다 설정이 가능하다.
이 때, 한 개 컬럼에 설정할 수도 있으며, 여러개의 컬럼을 묶어서 설정할 수 있다.(복합키)

 USER_NO NUMBER
 /*CONSTRAINT USER_NO_PK*/ /*PRIMARY KEY*/
-> 컬럼레벨PK지정 (제약조건이름)  생략 가능
 
 CONSTRAINT USER_NO_PK PRIMARY KEY(USER_NO)
-> 테이블레벨PK지정

* PRIMARY KEY는 UNIQUE와 NOT NULL의 제약조건 둘 다
허용하지 않는다.

5. 제약조건 - PRIMARY KEY (복합키)

CONSTRAINT PK_USERNO_USERID
PRIMARY KEY(USER_NO, USER_ID)

위 처럼 복합적으로 사용할 수 있으며, 테이블레벨에서만 가능하다.
이 또한 NULL값 과 중복값이 데이터에 삽입할 수 없다.

6. 제약조건 - FOREIGN KEY (외부키/외래키)

다른 테이블의 PK 또는 UNIZUE 제약조건이 설정된 컬럼이다.

참조(REFERENCES)된 다른 테이블의 컬럼이 제공하는 값만 사용할 수 있다.
FOREIGN KEY 제약조건에 의해서 테이블간의 관계(RELATIONSHIP)가 형성된다.
제공되는 값 외에는 NULL을 사용할 수 있다.

* 테이블레벨일 경우
[CONSTRAINT 이름] FOREIGN KEY (적용할컬럼명) REFERENCES 참조할테이블명 [(참조할컬럼)] [삭제룰]
--> 테이블 레벨에서만 FOREIGN KEY 단어가 사용된다.

* 컬럼레벨일 경우
컬럼명 자료형(크기)[CONSTRAINT 이름] REFERENCES 참조할 테이블명 [(참조할컬럼)] [삭제룰]

참조될 수 있는 컬럼은 PRIMARY KEY 컬럼과 UNIQUE 지정된 컬럼만 외래키로 사용할 수 있다.

참조할 테이블에 참조할 컬럼명이 생략이 되면, PRIMARY KEY로 설정된 컬럼이 자동으로 참조할 컬럼이 된다.

FOREIGN KEY 설정 방법 (컬럼레벨)
GRADE_CODE NUMBER CONSTRAINT GEADE_CODE_FK 
REFERENCES USER_GRADE(GRADE_CODE)
-- 컬럼 레벨 FK(FOREIGN) 설정

FOREIGN KEY 설정 방법 (테이블레벨)
CONSTRAINT GRADE_CODE_FK FOREIGN KEY(GRADE_CODE) 
REFERENCES USER_GRADE
이 때 참조 테이블 컬럼명 생략 시 자동으로 PK(PRIMARY)를 참조

7. 제약조건 - CHECK

컬럼에 기록되는 값에 조건 설정을 할 수 있게 하는 제약조건이다.
(주의) : 비교값은 리터럴만 사용할 수 있으며, 변하는 값이나 함수는 사용하지 못한다.

[작성법]
CHECK (컬럼명 비교연산자 비교값)
GENDER VARCHAR2(10) CONSTRAINT GENDER_CHECK
CHECK(GENDER IN ('남', '여'))

제약조건 중요 순서
PK , FK > NOT NULL > UNIQUE > CHECK

coldZero

교육생

이전 포스트

Oracle SQL Developer (SELECT_Option, DDL) 복습