SQLD 2과목 (1. SQL기본)

Notice

Recent Posts

Recent Comments

Link

« 2024/07 »
일	월	화	수	목	금	토
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30	31

Tags more

Archives

Today

Total

관리 메뉴

LITTLE BY LITTLE

SQLD 2과목 (1. SQL기본) 본문

SQL/SQLD

SQLD 2과목 (1. SQL기본)

위나 2022. 8. 30. 22:02

2과목. SQL 기본 및 활용

2-1. SQL 기본

2-1-1. 관계형 DB 개요

DB의 발전 : FLOW CHART -> 계층/망형 -> 관계형 -> 객체 관계형
파일 시스템의 단점 : 동시 삽입,수정,삭제 불가 / 관리 어렵 / 복사본 사용시,데이터의 불일치성 발생
RDB 장점
1. 정규화로 이상현상, 중복제거 가능
2. DB 무결성 보장, 데이터 회복 및 복구 가능
3. 병행 제어, 동시성 관리를 통해 데이터 공유
4. DATA 표현 방법 등 체계화되어있어 -> 표준화, 품질 확보
sql
1. DML(조작어) : Select, Insert, Update, Delete
  1. 호스트 프로그램 속에 삽입되어 사용하여, '데이터 부속어'라고 하기도 한다.
  2. Precodural DML 절차적 데이터 조작어 => 초급 언어이기 때문에, WHAT/HOW 처리방법까지 알려주어야함
  3. NonProcedural DML 비절차적 데이터 조작어 => 고급 언어이기 때문에, WHAT만 알려줌, '선언적 언어'라고 하기도 함
  4. Update 테이블명 Set 컬럼명 = 값 Where 조건;
  5. Select ALL 컬럼들 As Alias명 일시 중복 데이터까지, Select Dinstinct 일경우 중복 제외
  6. 산술 연산자 우선 순위
    1. () --> * --> / --> + --> -
  7. 합성 연산자 : 문자와 문자를 연결, <oracle> || , <sql server> +
2. DDL(정의어) : Create, Alter, Drop, Rename
  1. 스키마,도메인,테이블,뷰,인덱스를 정의/변경/제거
  2. auto commit되어 수행시, rollback 있더라도 commit됨
  3. create table 테이블명 (칼럼1유형<constraint> 칼럼2유형<constraint>...)
  4. CREATE TABLE AS : 테이블 구조 복사, 단 NOT NULL 제약조건만 복사한다.
    1. <oracle> Create Table 테이블명B As Select * From 테이블명 A;
    2. <sql server> Select * Into 테이블명B From 테이블명;
  5. 정의어 pk 제약 조건 생성
    1. Create Table 테이블명 ( 컬럼명 varchar2(10) Primary Key, 컬럼명2 varchar2(300) Not Null)
    2. Alter Constraint 컬럼명 Primary Key (컬럼명);
  6. <oracle>에서의 정의어
    1. Alter Table 테이블명 Add (추가 칼럼명 Dtype)
    2. Alter Table 테이블명 Drop (삭제 칼럼명 Dtype)
    3. Alter Table 테이블명 Modify (컬럼명 Dtype)
  7. 테이블의 구조변경(alter)
    1. 삭제된 column은 복구 불가능하다.
    2. 컬럼말고 테이블명을 변경하고자 하는 경우 (alter 사용하지 X)
      1. <oracle> rename 기존 테이블명 to 새로운 테이블명
      2. <sql server> sp_name '기존 테이블명', '새로운 테이블명'
    3. Alter Table + Drop/Add Constraint : 제약 조건 추가 (변경 희망시 삭제 후 다시 add하면 됨)
    4. alter table 주의사항
      1. 컬럼의 길이(크기)를 늘리는 것은 자유이나, 값이 존재할 경우 크기를 줄이는 건 X
      2. 해당 컬럼이 Null값만 가지고 있으면 타입(숫자,문자) 변경 가능
      3. 이미 Null 존재시, Not Null 제약 조건 추가 불가
      4. Default 값을 설정하는 경우, 변경 작업 이후 발생하는 행 삽입에 대해서만 default값이 영향
      5. 변경사항만 적는 것이 아니라, 자료형&제약조건 모두 완벽히 작성해야한다. => Alter Table 기관분류 Alter Column 분류명 Varchar(30) Not Null
  8. Drop table 영구삭제
    1. oracle에만 있다.
    2. drop table 테이블명 cascade constraint
      1. 테이블을 삭제해되, 참조 제약에 걸린것까지 연쇄적 제거
      2. 행의 입력,삭제가 다른 테이블과 연동되어있을 경우 사용
      3. drop table시 자식 테이블에 참조제약이 걸려있는 경우 삭제시 에러가 뜰 수 있다.
    3. drop column은 데이터가 있어도 없어도 삭제가 가능하며, 한번에 1개의 column만 가능하다.
    4. drop constraint는 모든 제약조건을 삭제한다.
  9. Truncate table 테이블 비우기
    1. 테이블 삭제가 아닌, 해당 테이블의 모든 행만 제거한 후, 저장 공간을 재사용하도록 해제해줌.
3. DCL(데이터 제어어) : Grant, Revoke
4. TCL(트랜잭션 제어어) : Commit, Rollback
  1. DML에 의해 조작된 결과를 작업단위별로 제어한다. 일부에서는 DCL로 분류하기도 함
  2. 트랜잭션이란,
    1. db의 논리적 연산 단위
    2. 관련되어 분리될 수 없는 1개 이상의 db조작
    3. 1개 이상의 sql문장이 포함됨
    4. 분할할 수 없는 최소 단위 (전부 적용 or 전부 취소만 가능)
  3. 트랜잭션의 특징 (ACID)
    1. Atomicity 원자성 : 연산은 모두 적용 or 전혀 실행 x
    2. Constitency 일관성 : 실행 전후 상태일관
    3. Isolation 고립성 : 실행 중 다른 트랜잭션의 영향을 받아서는 안됨
    4. Durability 지속성 : 영구적으로 반영되어 저장됨
  4. 격리성이 낮을경우 문제?
    1. Dirty Read : 수정되었지만, 아직 commit되지 않은 데이터읽음
    2. Non-Repeatble Read : 한 트랜잭션 내 쿼리 2번 실행, 그 사이 다른 트랜잭션 수정해서 두 쿼리의 결과가 다름
    3. Phantom Read : 2와 같은 상황에서 1번째 쿼리에는 없던 유령 레코드가 2번째 쿼리에 나타남
  5. 트랜잭션 대상 SQL - DML (Insert, Update, Delete), Select, Select For Update 등 배타적 Lock을 요구하는 것
  6. Commit 이전
    1. 단지 Memory Buffer에만 영향, 이전 상태로 복구 O
    2. Select 문으로 변경된 결과 확인 가능
    3. 다른 사용자는 현재 사용자가 수행한 결과를 확인도 할 수 없음
    4. 변경된 행은 아직 잠금설정 되어, 다른 사용자가 변경 불가능
  7. Commit 이후
    1. 데이터의 변경사항이 DB에 영구 반영됨
    2. 이전 데이터는 영원히 잃어버림
    3. 모든 사용자가 결과 조회 가능
    4. 변경된 행 잠금해제되어, 다른 사용자의 변경/확인 가능
  8. <oracle>에서는 DDL정의어 문장 수행시, 자동으로 COMMIT 수행되어, ROLLACK 해도 저장되어버림
  9. <sql server>에서는 자동 commit되지 X
  10. Rollback시 데이터에 대한 변경사항이 취소되고, 이전 데이터가 재저장되고, 관련 행에 대한 잠금이 풀리고, 다른 사용자들이 데이터를 변경할 수 있다.
TABLE은 RDB의 기본 단위, 최소 1개 이상 COLUMN, 모든 DATA를 2차원으로
테이블의 분할로 불필요한 중복을 없애 이상현상 방지 가능
제약 조건
1. 데이터 무결성 유지를 위한 것
2. Primary key : 한 테이블 당 하나씩, null입력 X, 자동 Unique 처리됨
3. Unique : Null O, 중복X 의미
4. Not null : 명시적으로 Null 입력 방지
5. Check : 데이터 무결성 유지를 위해 Table의 특정 column에 설정하는 제약
6. Foreign key : 왜래 키, 참조 무결성 옵션 선택 O, 여러개 O
Null은 Is Null과 Is Not Null만 가능하다. (!=Null 이런 형태는 불가능 X)
외래키(FK) 참고 사항
1. 테이블 생성시 설정할 수 있다.
2. NULL가질 수 있다.
3. 한 테이블에 여러개 O
4. 참조 무결성 제약을 받는다.
정의어 DDL과 조작어 DML의 차이
1. 정의어는 반드시 AUTO COMMIT이 일어난다. => DROP시 완전 삭제되어 복구가 불가하다.
2. 조작어는 사용자가 COMMIT해야한다. => DELETE시 테이블이 삭제되지만 ROLLBACK으로 복구가능하다.
참조 동작
1. Insert 동작 (child 삽입시 parent에 pk없는 경우)
  1. Automatic : child삽입시 parent에 pk없을 때 자동 생성
  2. Set Null : child삽입시 parent에 pk없을 때 child 외부키를 Null으로
  3. Set Default : child삽입시 parent에 pk없을 때 child 외부키를 Default로
  4. Dependent : child삽입시 parent에 pk가 존재해야지만 삽입 허용
  5. No Action : 참조무결성을 위반하는 삽입액션을 취하지 않음
2. Delete/Modify 동작 (parent 삭제시 child의 변화)
  1. Cascade : parent 삭제시 child 같이 삭제
  2. Set Null : parent 삭제시 child는 Null으로
  3. Set Default : parent 삭제시 child는 default로
  4. Restrict : parent 삭제시 child는 pk없을 때에만 parent의 삭제 허용
  5. No Action : 참조무결성을 위반하는 삭제/수정 액션은 취하지 않음
3. 삭제 sql 비교 정리
  1. Drop : 정의어, auto commit, rollback 불가, 디스크 초기화(=로그 제거), 스키마 정의까지 삭제
  2. Truncate : 정의어(+일부 조작어성격), auto commit, rollback불가, 디스크 초기화(=로그제거), 스키마 구조 유지
  3. Delete : 조작어, 사용자 commit, commit전에 rollback 가능, 디스크 초기와 안함(=로그 유지), 스키마 구조 유지
COMMIT이나 ROLLBACK으로 트랜젝션을 종료하는데, ROLLBACK을 만나면 BEGIN 시점까지 모두 ROLLBACK이 수행된다.
1. COMMIT과 ROLLBACK의 효과
  1. 데이터 무결성 보장
  2. 영구적 변경 이전에 데이터의 변경사항 확인 가능
  3. 논리적으로 연관된 작업을 Grouping해서 처리 가능

13. Savepoint

지정시 포인트까지 일부만 rollback 가능
여러개 지정 가능
동일 이름으로 저장시, 가장 나중에 정의한 point가 유효 (덮어쓰기 됨)
<oracle> savepoint 포인트이름; rollback to 포인트이름;
<sql server. save transaction 포인트이름; rollback transaction 포인트이름;
point1까지 되돌리고 나서 그보다 미래인 point2로 되돌릴 수 없다.
특정 point까지 rollback하면, 그 이후 설정한 point는 전부 무효 처리
point 없이 rollback 시 모든 변경사항 취소

14. Where절

where 절에 조건이 없는 full table scan(FTS)문장은 SQL튜닝 1차 검토 대상
FTS가 항상 나쁜 것은 아니다. 병렬처리를 이용해 유용하게 사용하는 경우도 있다.
연산자 우선순위 : 부정 -> 비교 -> 논리
LIKE 비교 문자열 : 와일드 카드 - % = 0개이상 문자열 일치, _= 1개 단일 문자 일치

15. ROW NUM <oracle>

칼럼과 비슷한 성격의 pseudo column이다.
sql 처리 결과 집합의 각 행에 대해서 임시로 부여되는 일련번호이다.
table이나 집합에서 원하는 만큼의 행만 가져올 때, where에서 행의 개수를 제한하는 용도

16. <sql server>의 Top : Top (Expression) [percent] [with Ties] => order by 절 지정시에만 사용 가능, 동점은 추가 출력 [rank 함수에서도 사용]

17. 단일행 함수 : 내장 함수 중 파라미터가 하나~여러개인 함수 (<->sum, avg는 다중 행임)

18. select, where, order by, update, set절에서 사용 가능

19. 단일행 함수

추출되는 행마다 작업 수행, 각 행마다 하나의 결과 반환, select/where/order by/update의 set절에 사용 가능 , dtype변경 가능 , 중첩 사용 가능

20. 다중행 함수

여러개의 행 입력, 하나의 값 반환, 그룹(집계)함수가 다중 행 함수 (ex. sum, avg, max, min, count...)

21. 단일행 함수

문자형 : Lower, Upper, Ascii(문자를 숫자로 변환), Chr/Char, Concat, Substr('SQL Expert', 5, 3) => 인덱스 '5'번째부터 '3'개 추출, 공백 포함이고 db인덱스는 항상 1부터임, Substring, Length, Len, Ltrim, Rtrim, Trim
숫자형 : Abs(숫자), Sign(숫자), Mod(숫자1,숫자2), Ceil/Ceiling(숫자), Floor(숫자), Trunc(숫자, [ , m] : m자리에서 버림 - <sql server>에는 없음, Exp(), Power(), Sqrt(), Log(), Ln()
날짜형 : Sysdate / Getdate(), Extract(), Datepart(), To_Number
변환형
1. 명시적 : 데이터 변환형 함수로 dtype을 바꾸도록 명시
2. 암시적 : db가 자동으로 dtype을 바꿔서 계산함
3. <oracle> To_Number, To_char, To_Date / <sql server> Cast (expression, As data_type(length), Convert(data_type, [(length)], expression[style])

22. Searched_case Expression --> Simple_case Expression : Case when --> Case Loc When

23. Null 관련 함수

NVL(값1, 값2) => ISNull과 같은 역할, 값1이 Null이 아니면 값1을, Null이면 값2를 반환
NVL(값1, 값2, 값3) => 값1이 Null이 아니면 값2를, Null이면 값3을 반환
Nullif(값1, 값2) => 둘이 같으면 Null, 다르면 값1 반환
Coalesce(값1, 값2, 값3...) => 값들 중에서 Null이 아닌 첫번째 값을 반환 (Null이 아닌 최초의 표현식)

24. 공집합 : 조건에 맞는 데이터가 한건도 없을 때. ex) Select 1 from Dual Where 1=2; 와 같은 조건이 대표적인 공집합을 발생시키는 쿼리이다. 인수값이 공집합인 경우, NVL(), ISNull() 사용해도 공집합을 반환한다.

25. Group By Having 절

집계함수란?
1. 그룹당 1개의 결과를 돌려주는 다중행 함수, group by 절은 행들으 소그룹화함
2. Select / Having / Order By 절에서 사용 가능하다.
3. 모든 집계함수가 기본적으로 Null을 제외시키고 계산하며, Count(*)만 유일하게 Null 포함
4. Group by 하기 전, Where절로 계산 대상을 줄이면 효과적임

26. Group by 절 : 소그룹에 대한 통계정보를 얻을 때 사용한다. Roll up, Cube에 의한 결과에 'Grouping (Expr) =1'표시됨

27. Having절과 Where절의 차이는

Having절은 Group by 뒤에 온다는 것과(앞에 와도 된다) 집계함수 사용이 가능하다는 것(where절에서는 사용 불가)
그리고 Where절에서 조건 변경시, 출력되는 레코드 개수 뿐만아니라, 결과 데이터값까지 변경될 가능성이 있는 반면, Having 절에서는 출력되는 레코드 개수는 똑같이 변경되지만, 결과 데이터값은 변경되지 않는다.

28. Order by 절

Order by에는 Group by의 컬럼이나 Select의 컬럼만 올 수 있다.
숫자형은 오름차순이다 (작은 값부터 출력, 날짜는 빠른 날부터)
<oracle> Null값을 가장 큰 값으로 간주한다.
<sql server> Null값을 가장 작은 값으로 간주한다.
Select A,B,C From Table1 Order by 2,3 => Select문의 2번째로 먼저 정렬 후, 3번째로 정렬 (당연히 select에는 1개있는데 order by 2 로 입력할 경우 잘못된 문장)
Select From Table Group by ID[=>아이디별로 1건씩 출력] Having Count(*)=2 Order by (Case When ID=999 Then 0 Else ID End); [=>999의 인덱스를 0으로 바꿔 asc 오름차순 정렬]

29. 주의 - select문 실행 순서

select 컬럼명'이 제일 먼저 오지만, 실행되는 순서는 다섯번째
Select 컬럼명 From Table Where 조건식 Group by 컬럼/표현식 Having 그룹의 조건식 Order by 컬럼/표현식 [Asc/Desc]; [=>즉, Select문은 Order by절보다만 앞에 오고, 나머지보다는 뒤에 오는 순서

30. Top N 쿼리

<sql server>에서 order by 후에 Top 사용시, 상위/하위인 데이터 추출 가능
With Ties 옵션은 반드시 Order by 와 함께 사용해야 함
Select Top(3) With Ties 팀명, 승리 건수 From 팀별 성적 Order by 승리건수 Desc;
팀별 성적 Table에서 승리건수가 높은 순으로 3위까지 출력하되, 3위의 승리건수가 동일한 팀이있다면 함께 출력

31. Join

PK, FK값의 연관에 의해 Join 성립하지만, 없더라도 논리적인 값들의 연관으로도 join성립이 가능하다.
한 table에서 여러 table 조인 가능, but AND로 묶어주어야한다.
Equi join : '=' 연산자로 하는 조인이다. 두 테이블의 칼럼값이 일치할 때에만 사용, 이외의 조인은 모두 Non-equi
Where로 join하는 것과 On절로 join하는 것의 차이는, Select From table Where / Select From table Inner Join ON
Non equi join의 경우, 'Between', 부등호 등으로 하는 조인이다. 대부분 적용가능하나,데이터 모델에 따라 안될 수 있음, Select From Table Where Between AND

2-1. SQL기본 문제풀이 오답

데이터 제어어(DCL)에 해당하는 명령어는? => INSERT / RENAME / COMMIT / REVOKE
데이터 베이스를 정의하고, 접근하기 위해서는 데이터베이스 시스템과의 통신수단이 필요한데 이를 데이터언어라고 하며, DDL, DML, DCL로 구분된다. 부적절한 것은? => 비절차적 데이터 조작어(DML)은 사용자가 무슨 데이터를 원하며(WHAT), 어떻게 그것을 접근해야 되는지(HOW)를 명세하는 언어이다.(x) => 비절차적 데이터는 what만 명세
절차적 데이터 조작어는 "PL/SQL" <oracle> / "T-SQL" <SQL server> =>여기서 WHAT과 HOW까지
DML(조작어)은 데이터베이스 사용자가 응용 프로그램이나 질의어를 통하여 저장된 데이터베이스를 실직적으로 접근하는데 사용되며, SELECT, INSERT, DELETE, UPDATE 등이 있다.
호스트 프로그램 속에 삽입되어 사용되는 DML명령어들을 "데이터 부속어"라고 한다.
종류와 해당되는 명령어를 바르게 연결한 것을 고르시오. => DML - SELECT / TCL - COMMIT
PK 제약조건을 생성하는 DCL 문장으로 올바른 것 => Create Table Product (Prod_ID Varchar2(10), Prod_NM Varchar2(100), Reg_dt date Not Null), Constraint Product_PK Primary Key (Prod_Id) );
위 문장처럼 Create 절 안에 Constraint를 집어넣어도 되고, 'Alter Table ~ Add Constraint 컬럼명 Primary key'로도 표현 가능
데이터가 들어있지 않은 기관분류 테이블을 변경하고자할 때, 올바른 sql문장은? 기관분류 테이블의 등록일자 컬럼의 dtype을 varchar(10) null => date not null로 바꾸고자함.
1. Alter Table 기관분류 Alter Column 분류명 Varchar(30) Not Null; Alter Table 기관분류 Alter Colum 등록일자 Date Not Null;
2. not null로 지정안할시, 디폴트는 null이다.
3. Alter Table 기관분류 Alter Colum (분류명 varchar(30), 등록일자 date not null) (X) 이처럼 여러개의 COLUMN을 동시에 수정하는 구문은 지원하지 않는다. SQL SERVER에서는 괄호도 사용하지 않음
4. <oracle>에서는 Alter Table Modify
Null은 모르는 값, 값의 부재를 의미하며, 아직 정의되지 않은 미지의 값이거나, 현재 데이터를 입력하지 못하는 경우를 의미한다. null과의 모든 비교는 (is null제외) 알 수 없음(unknown)을 반환한다. => 공백문자 혹은 숫자 0과 다름
아래 테이블 T,S,R이 다음과 같이 선언되었다. (모두 같은 형태이다. 테이블 T는 C,D컬럼에 각 (1,2),(1,1), 테이블 S는 컬럼명이 E,C, 테이블R은 컬럼명이 A,B) 다음 중 DELETE FROM T; 를 수행한 후 테이블 R에 남아있는 데이터로 적절한 것은?
1. Create Table T (C Integer Primary Key, D Integer);
2. Create Table S (B Integer Primary Key, C Integer References T(C) On Delete Cascade);
3. Create Table R (A Integer Primary Key, B Integer References S(B) On Delete Set Null);
4. 답 : (1, Null)과 (2,Null)
5. 1번에서 테이블 T는 delete from t 수행했으니 모두 삭제되고,
6. 2번에서 테이블 S는 References T(C) On Delete Cascade를 이기 때문에, 테이블 T의 PK를 FK로 설정한다는 의미 + 테이블 S의 C칼럼이 T가 모두 삭제되어 여기서도 두 건 모두 삭제된다.
7. 3번에서 R테이블은 References S(B) On Set Null 옵션이 지정되었으므로, 테이블 S의 PK를 FK로 설정한다는 의미 + Child 해당 필드 (FK가 B칼럼)가 Null로 변경되는 것
제약 조건 중 Unique는 테이블 내에서 중복되는 값이 없으며, null 입력이 ~~불가능~~ 가능하다.(X)
제약 조건 중 Not Null은 명시적으로 Null 입력을 방지한다.
틀린 문장 => Create Table 테이블명 ( .. Primary Key, ...); Alter Table 테이블명 Add Constraint 컬럼명 Primary key;(X) => 이미 Primary Key Constraint 제약조건을 지정했는데, 또 지정하면 X
생성) Create Table 학생 (학번 char primary key, 장학금 integer); => SQL 1 : Select Count(*) From 학생 == SQL2 : Select count(학번) from 학생 => 이 두 문장의 실행 결과는 항상 동일하다. count(학번)은 집계함수로 집계함수는 count(*)을 제외하고는 null값 빼고 계산되는데, 여기서는 학번이 primary key라서 null이 없기 때문에, null을 포함해서 count하든, null을 제외하고 count하든 동일한 결과를 출력하는 것
외래 키 값은 null 값을 가질 수 있으며, 외래키 값은 참조 무결성 제약을 받을 수 있다.
테이블의 제약조건에 대한 설명으로 부적절한 것은?
1. Check 제약조건(Constraint)은 데이터베이스에서 데이터의 무결성을 유지하기 위하여 테이블의 특정 컬럼에 생성하는 제약이다.
2. 기본키(Primary Key)는 반드시 테이블 당 하나의 제약만을 정의할 수 있다.
3. 고유키(Unique Key)로 지정된 모든 컬럼은 Null값을 가질 수 없다있다.(X) 고유키 <-> 기본키
4. 외래키는 테이블간의 관계를 정의하기 위해 기본 키를 다른 테이블의 외래키가 참조하도록 생성한다.
테이블의 불필요한 칼럼 삭제하기 - Alter Table Drop Column 삭제할 칼럼명;
테이블 이름 변경 Rename To
관계형 데이터베이스에서 Child Table의 FK 데이터 생성시, Parent Table에 PK가 없는 경우, Child Table 데이터 입력을 허용하지 않는 참조동작인 것은? => Dependent
1. Cascade는 Master 삭제시 Child 삭제
2. Restrict는 Child에 pk없는 경우에만 Master 삭제 허용
3. Automatic은 Master에 pk없는 경우, Master pk자동 생성 후 child 입력
Create Table Tbl (Id Number Primary Key, Amt Number Not Null, Degree Varchar(1))
1. Insert Into Tbl(Id, Degree) Values(2,200,'AB') (X) <= 모든 컬럼을 다 명시해야한다. amt는 not null이라 명시해야함
2. Insert Into Tbl(Id, AMT) Values (3,300) (O) <= 여기서는 degree를 명시하지 않았지만, 디폴트 null이기에 괜찮음
3. Insert Into Tbl Values(5,500,Null) (O)
테이블에 데이터를 입력하는 두 가지 유형
1. Insert Into 테이블명 (Column List) Values (Column List에 넣을 Value_List);
2. Insert Into 테이블명 (전체 Column에 넣을 Value List);
입력된 데이터의 수정
1. Update 테이블명 Set 수정되어야할 칼럼명 = 수정되기를 원하는 새로운 값
다음 중 정상수행되는 sql문장은? (고객 ID는 Not Null이다)
1. Alter Table 주문 Add Constraint FK_001 Foreign Key (고객ID)
2. References 고객 (고객ID) ON Delete Set Null;
3. 고객 테이블의 기본키 고객 ID가 주문 테이블의 외래키이다.
4. Insert Into 고객 Values ('C003','강감찬', '2014-01-01');
5. Delete From 주문 Where 주문번호 In ('0001', '0002');
6. (X) Insert Into 주문 Values ('0005', 'C003', '2013-12-28') => 4번을 실행해야 실행됨
7. (X) Delete From 고객 Where 고객ID = 'C002'; => delete하면 위 2번에 따라 고객 ID를 set null하려고 시도하지만, 고객 id가 not null 제약조건을 갖고있어서 실패한다.
개발 프로젝트의 표준은 모든 삭제 데이터에 대한 로그를 남기는 것을 원칙으로 하고, 테이블 삭제의 경우 허가된 인력만이 정기적으로 수행가능하도록 정하고 있다. 사용 용도가 없다고 판단한 테이블의 데이터를 삭제하는 가장 좋은 방법은? => 로그 유지, 사용자 commit 에 해당하는 삭제 SQL은 데이터 조작어인 Delete이다.
1. 정답 : Delete From 테이블명;
2. 오답 : Delete * from 테이블명(문법에 맞지않음), Truncate(로그제거,스키마유지,auto), Drop(로그제거,스키마삭제,auto)
특정 테이블에 대하여 WHERE 조건절이 없는 DELETE 명령을 수행하면 DROP TABLE 명령을 수행했을 때와 똑같은 결과를 얻을 수 있다.(X) Delete와 Drop은 다르다
Drop 명령어는 테이블 정의 자체를 삭제하고, Truncate 명령어는 테이블을 초기상태로 만든다.
Truncate명령어는 Undo를 위한 데이터를 생성하지 않기 때문에, 동일 데이터량 생성시 Delete보다 빠르다. (= AUTO COMMIT되는 DROP과 TRUNCATE는 UNDO를 위한 데이터를 생성하지 않아 더 빠름)
Drop은 Auto commit이 되고, Delete와 Truncate는 사용자 Commit으로 수행된다.(X)
데이터 베이스 트랜잭션에 대한 격리성이 낮은 경우 발생할 수 있는 문제점으로 가장 부적절한 것은?
1. Dirty Read : 다른 트랜잭션에 의해 수정되었고 이미 커밋된되지않은 데이터를 읽는 것
2. Non-Repeatable Read : 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 그 사이에 다른 트랜잭션이 값을 수정 또는 삭제하는 바람에 두 쿼리 결과가 다르게 나타나는 현상
<oracle>에서는 자동 커밋되어, 내부적으로 트랜잭션을 종료시키고, <sql server>에서는 자동 커밋되지 않는다.
"트랜잭션"은 데이터베이스의 논리적 연산단위로, 밀접히 관련되어 분리될 수 없는 한 개 이상의 데이터베이스 조직이다. 트랜잭션의 종료를 위한 대표적 명령어로는 데이터에 대한 변경사항을 데이터베이스에 영구적으로 반영하는 "commit"과 데이터에 대한 변경사항을 모두 폐기하고 변경 전의 상태로 되돌리는 "rollback"이 있다.
아래와 같은 DDL문장으로 테이블을 생성하고, SQL 수행시 옳은 것은?
1. Create Table 서비스 (서비스 번호 Varchar2(10) Primary Key, 서비스명 Varchar2(100) Null, 개시일자 Date Not Null);
2. [SQL] ㄱ) Select * From 서비스 Where 서비스 번호=1; ㄴ) Insert into 서비스 Values ('999','','2015-11-11'); ㄷ) Select * From 서비스 Where 서비스명 = ''; ㄹ) Select * From 서비스 Where 서비스명 Is Null;
3. 서비스 번호 컬럼에 모든 레코드 중에서 '001'과 같은 숫자형식으로 하나의 레코드만이라도 입력되어 ㄱ은 오류없이 실행된다.(X) 모두 형식을 지켜야한다.
4. <oracle>에서 ㄴ과 같이 데이터를 입력하였을 때, 서비스명 컬럼에 공백문자 데이터가 입력된다. (X) 공백문자 데이터와 NULL은 다르다.ㄴ과 같이 입력시 NULL이 입력된다.
5. <oracle>에서 ㄴ과 같이 데이터를 입력하고, ㄷ과 같이 조회하였을 때, 데이터는 조회된다.(X) ㄴ과 같이 입력시 NULL이므로, <oracle>에서는 'IS NULL'조건으로 조회해야 한다.
6. <sql server>에서 ㄴ과 같이 데이터를 입력하고, ㄹ과 같이 조회하였을 때, 데이터는 조회되지 않는다. (O) => 조회되게 하려면 <sql server>에서는 ㄹ)Select * FROM 서비스 Where 서비스명 Is Null이 아니라 '서비스명='으로 조회해야한다.
7. Select Sum(매출금액) As 매출금액합계 From 월별매출 Where (년 = '2014' And 월 Between '11' ) And '12' Or 년 = '2015' And (월 Between '01' And '03'); 에서 where과 or 뒤에 괄호를 붙이지 않아도 된다. And가 or보다 우선이라서
이 중 다른 하나는?
1. End_Date >= To_Date('20150101000000', 'YYYYMMDDHH24MISS') And End_Date <= To_Date('20150131235959', 'YYYYMMDDHH24MISS') And Concat(Join_Ymd, Join_hh) = '2014120100' => 2015/01/01 00시부터 2015/01/31 12시 59분 59초까지의 기간 동안에 2014년 12월 12월 01시에 발생한 데이터를 찾음.
2. End_Date >= To_Date('20150101', 'YYYYMMDD') And End_Date <= To_Date('20150201', 'YYYYMMDD') And (Join_Ymd, Join_hh) In (('20141201', '00')) => 2015 01/01부터 2015 02/01 이전까지의 데이터 중 2014/12/12인 데이터를 찾음
3. '201501' = To_Char(End_Date, 'YYYYMM') And Join_Ymd = '20141201' And Join_HH = '00' =>To_Char일 경우 2015/01 이므로 한달전체를 의미, 1월의 처음과 끝 사이(1일00시~31일12시59분59초) 와 2014/12 00시 사이의 데이터 중 2014/12/01을 찾음
4. To_Date('201501', 'YYYYMM') = End_Date And Join_Ymd || Join_HH = '2014120100' => 여기서 가리키는건 To_Date이라서 2015년 1.1 하루를 의미, 00시에 종료되는 것이다. 다른 보기와(2015년12월31일00시 종료) 다르다.
단일행 함수는 Select, Where, Order by, Update의 Set절에 사용가능하다.
1:M 관계의 두 테이블을 조인할 경우, M쪽에 다중행이 출력되므로 단일행은 사용할 수 없다 있다.(X) => 1:M에서 M쪽에서 출력된 행이 다중행이어도, 하나씩 단일행 함수의 입력값으로 사용된다.
단일행 함수는 ~~다중행 함수와 다르게~~ 여러 개의 인수가 입력되어도 단일값을 반환한다.(X) => 단일행 함수도, 다중행함수도 반환값은 단일값이다.
Select Sum(CC) From (Select(Length(C1) - Length(Replace(C1, Chr(10))) +1) CC From Tab1 에서 수행결과
1. 라인 수를 구하기 위해 함수를 이용해 작성한 SQL이다.
2. Chr()은 주어진 Ascii코드에 대한 문자를 반환하는 함수이다. 문제에서 Chr(10) : Ascii 값 => 줄바꿈을 의미 라고 했기 때문에, 식에서 chr(10)은 줄바꿈을 의미한다.
3. Replace(C1, Chr(10)) 은 C1대신 줄바꿈을 제거하라는 의미이다.
4. 즉, A 2개가 세로로 (줄바꿈O) 있으면 length(c1)이 3인데, replace(c1, chr(10))으로 줄바꿈이 제거되어 가로로 A 2개가 놓여있게 되어서 length(c1)이 2가 되는 것, B도 마찬가지.
Dual 테이블의 특성
1. 사용자 sys가 소유하며 모든 사용자가 엑세스 가능한 테이블이다.
2. select~from~의 형식을 갖추기 위한 일종의 dummy 테이블이다.
3. dummy라는 문자열 유형의 칼럼에 'X'라는 값이 들어있는 행을 1건 포함하고 있다.
Simple_Case_Expression을 이용해 표현을 바꾸면
1. Case When Loc = 'New York' Then 'East' Else 'Etc'
2. Case Loc When 'New York' Then 'East Else 'Etc'
<oracle>NVL과 <sql server>IS NULL은 같은 결과를 출력한다.
1. Isnull(Sum(Case When Position = 'FW' Then 1 End), 0) FW
2. NVL(Sum(Case Position When 'FW' Then 1 End),0) FW
3. NVL(Sum(Case Position When 'FW' Then 1 ~~Else 1~~ End),0) FW => Else 0으로 해야함
결과값을 null이 아닌 다른값을 얻고자 할 때 NVL/Isnull 함수를 사용한다. Null값의 대상이 숫자 유형인 경우는 주로 0(Zero), 문자형인 경우는 X로 바꾸는 경우가 많음
1. '' <= 공백은 하나의 문자로 취급한다. Select할 경우, 결과는 ~~공집합이다.~~ 1건이다.
2. col1이 Null이라면, Select IsNull ( Col2, 'X' ) From Tab1 Where Col1 = 'a'; => IsNull은 Null일 경우 지정한 값을 반환하기 때문에, 실행결과로 X를 반환
3. Where col2 = Null 은 잘못된 문장, is null로 써야지 equal에 null 쓸 수 X
사원 테이블에서 MGR의 값이 7698과 같으면 NULL을 표시하고, 같지 않으면 MGR을 표시하려고 한다. => 같으면 Null값을, 같지 않으면 지정한 문자열을 보여주는 Nullif
Select Grade From Emp Where Grade In ('차장', '부장', '널'); => 차장=부장 수, '널' 텍스트로 입력된 데이터는 없음, 심지어 null이라고 쳐도 null은 오직 is null, is not null에서만 쓸 수 있기에 IN 뒤에 쓸 수 없다.
Group by 절과 Having 절의 특성
1. Group by 절에서는 Select 절과 달리, Alias 명을 사용할 수 없다.
2. 집계함수는 Where절에는 올 수 없다. (group by 절보다 where절이 먼저 수행되기 때문)
3. where절은 전체 데이터를 Group으로 나누기 전에 행들을 미리 제거시킨다.
4. Having 절은 일반적으로 Group by 절 뒤에 위치한다.
광고매체 ID별로 최초로 게시한 광고명과 광고시작일자를 출력하기 위하여 들어갈 sql로 옳은 것은?
1. Select C.광고매체명, B.광고명, A.광고시작일자 From 광고게시 A, 광고 B, 광고매체 C, ( )D Where A.광고시작일자 = D.광고시작 일자 AND A.광고매체ID = D.광고매체ID AND A.광고ID = B.광고ID AND A.광고매체ID = C.광고매체ID Order By C.광고매체명;
2. Select D.광고매체 ID, MIN(D.광고시작일자) As 광고시작일자 From 광고게시 D Where D.광고매체ID = C.광고매체ID Group BY D.광고매체ID => 연관 서브쿼리를 활용하는 방법(JOIN)이지만, 이를 활용하기 위해서는 Where절에서 사용되어야한다. 이렇게 인라인 뷰안에서 괄호 바깥의 테이블과 JOIN 할 수 없다.
3. 답은 Select D.광고매체, MIN(D.광고시작일자) As 광고시작일자 From 광고게시 Group By 광고매체ID (O)
4. 그 외 오답은 Group by를 광고매체가 아니라,광고게시로 지정했거나, 아예 지정하지 않은 보기. (문제에서 "광고 매체 ID별"이라고 했기 때문에 Group by를 광고매체로 지정해야 함)
다음 중 오류가 발생하는 sql문장은?
1. Select ~~메뉴ID, 사용유형코드~~, Avg(Count(*)) As Avgcnt From 시스템 사용이력 Group By 메뉴ID, 사용유형코드;
2. 위처럼 중첩된 그룹함수의 경우(Avg안에 Count가 있다.) 최종결과값은 1건이 될 수 밖에 없기에, Group by 절에 기술된 메뉴 ID와 사용유형 코드는 Select절에 기술될 수 없다.
Select Max(가) As 가, 나, SUM(다) As 다 From A Group By 나 Having Count(*) > 1 Order By 다 Desc;
1. 의미 : (나) 컬럼에서 카운트가 2개 이상인 것만 select되어, 그 값들과 그 값들의 Max(가)와 Sum(다)가 답이 됨
2. Having절은 Select절보다 선행처리된다는 사실을 보여준다.
다음 SQL 중 오류가 발생하는 것은?
1. Select 지역, 매출금액 From 지역별 매출 Order By 년 Asc;(O)
  1. Select절 수행 후, Order By절이 수행되는데 Order by 년 에 해당하는 컬럼이 Select절에 없다. 하지만 <roacle>은 행 기반이라서 데이터 access시 전체 컬럼을 로드하기에, order by에 해당하는게 select절에 없어도 가능하다.
2. Select 지역, Sum(매출 금액) As 매출금액 From 지역별 매출 Group By 지역 Order By 년 Desc;(X) =>오류가 발생한다. 1번과 달리 지역으로 그룹핑된 후에는 order by 년(X) 불가
3. 1번에서 안되는 경우는, Select 지역, 매출금액 From (Select 지역, 매출금액 From 지역별 매출) Order By 년 Asc; => 인라인 뷰에서 먼저 select절이 수행되기 때문에, 더이상 select절 외의 컬럼 사용 불가
DBMS마다 null값에 대한 정렬 순서가 다를 수 있으므로 주의해야 한다. (오라클은 가장 큰 값, sql server에서는 가장 작은 값으로 간주)
Order By 절에서 컬럼명 대신 Alias명이나 컬럼 순서를 나타내는 정수도 사용 가능하나, 이들을 혼용하여 사용할 수 없다 있다.
Group by 절을 사용하는 경우, Order By 절에 집계함수를 사용할 수 있다.
Select 문장 실행 순서 : 발췌대상 테이블 참조(from) => 발췌 대상 데이터가 아닌 것 제거(where) => 행들을 소그룹화(group by) => 그룹핑된 값의 조건에 맞는 것만을 출력(having) => 테이블 값을 출력/계산(select) => 정렬(order by)
5개의 테이블로부터 필요한 칼럼을 조회하려고 할 때, 최소 4개의 JOIN 조건이 필요하다. (N-1개의 JOIN 조건 필요)
Select 출연.영화명, .. (X) <= 영화명 컬럼은 출연 테이블에 있지 않기 때문에 아래처럼 JOIN 필요함(Where이용)
1. Select 영화.영화명, 배우.배우명, 출연료 From 배우, 영화.출연 Where 출연료 >= 8888 And 출연.영화번호 = 영화.영화번호 And 출연.배우번호 = 배우.배우번호;
DBMS 옵티마이저는 From절에 나열된 테이블들을 항상 2개씩 묶어서 Join을 처리한다.

저작자표시

'SQL > SQLD' 카테고리의 다른 글

SQLD 2과목 (2. SQL활용, 3.SQL최적화 원리) (0)	2022.09.03
SQLD 1과목 (0)	2022.08.29
Day15 SQLD (0)	2022.08.28
Day14 SQLD (0)	2022.08.25
Day13 SQLD (0)	2022.08.24

'SQL/SQLD' Related Articles

Comments

LITTLE BY LITTLE

SQLD 2과목 (1. SQL기본) 본문

SQLD 2과목 (1. SQL기본)

2과목. SQL 기본 및 활용

2-1. SQL 기본

2-1. SQL기본 문제풀이 오답

'SQL > SQLD' 카테고리의 다른 글

티스토리툴바