SELECT A.*
FROM HR.EMPLOYEES A,
HR.EMPLOYEES B
WHERE 1=1
AND A.MANAGER_ID = B.EMPLOYEE_ID
AND B.SALARY >= ANY A.SALARY;A TABLE의 MANAGER_ID가 B테이블의 EMPLOYEE_ID 이므로
MANAGER 는 직급이라고 나눌 수 있는 컬럼,
EMPLOYEE는 MANAGER를 포함하는 속성 컬럼.
그러니 A 테이블의 매니저의 (직원)아이디가 400 인 사람을 찾아가면
B 테이블의 직원 아이디가 400 인 사람을 찾을 수 있다.
그로 인해 B가 상사이며 A는 그 부하직원이라고 볼 수 있다.
이를 첫번째 AND 절을 통해 확인할 수 있다.
두번째 AND 절은
상사의 연봉 >= 어떠한 부하직원의 연봉이라도..
따라서 위 쿼리는 어떠한(ANY) 부하직원이라도 연봉이 높은 상사를 출력한다.
□ 비교연산자 ANY(서브쿼리)
- 다수의 비교값 중 한개라도 만족하면 true.
- IN과 다른점은 비교 연산자를 사용한다.
- 서브쿼리의 결과에 존재하는 어느 하나의 값이라도 만족하는 조건을 의미
비교연산자로 ">"를 사용했다면 메인쿼리는 서브쿼리의 값들 중 어떤 값이라도
만족하면 되므로 서브 쿼리의 결과는 최소값보다 큰 모든 건이 조건을 만족
예)
SELECT *
FROM EMP
WHERE SAL = ANY(950, 3000, 1250)SAL = 950 OR SAL = 3000 OR SAL = 1250 과 동일.
DEPARTMENT_ID : NULL, 10, 20, 30, 40 50, ~ 90, 100, 110
SELECT DISTINCT DEPARTMENT_ID
FROM HR.EMPLOYEES A
WHERE A.DEPARTMENT_ID <= ALL (30, 50);
HR 테이블의 EMPLOYEES 컬럼을 A 로 부른다.
중복되는 값을 제외한 DEPARTMENT_ID 를 조회할 것인데
A.DEPARTMET_ID <= ALL (30, 50); 은
부서ID(DEPARTMENT_ID)가 30과 50을 모두 만족한 것.
10, 20, (30), (40), (50)
따라서 위 쿼리의 결과는 다음과 같다.
(10, 20, 30)
□ 비교연산자 ALL(서브쿼리)
- 전체 값을 비교하여 모두 만족해야 한다 (AND)
- 서브쿼리의 결과에 존재하는 모든 값을 만족하는 조건을 의미한다.
- 비교연산자로 " > " 를 사용하였다면 메인쿼리는 서브쿼리의 모든 값을
만족해야 하므로 서브쿼리의 결과의 최대값보단 큰 모든 것이 조건을 만족한다.
①
SELECT *
FROM SQLD_21_01
WHERE V1 IN (SELECT V1 FROM SQLD_21_02);| N1 | V1 |
| 1 | A |
| 3 | B |
②
SELECT *
FROM SQLD_21_01
WHERE V1 NOT IN (SELECT V1 FROM SQLD_21_02);위 쿼리를 통해서 출력되는 결과가 틀리다.
| N1 | V1 |
| 4 | C |
NOT IN 의 경우 조건절이 변형되어 수행된다.
NOT 조건이므로 NULL 연산이 Unknown 으로 처리되어 True로 반환되는 현상이 나타남.
SQLD_21_02 의 V1에 NULL이 존재하므로 NOT IN 경우 NULL로 인해 모든 조건이 참이 되어
NOT IN 의 결과 0건이 RETURN 됨.
③
SELECT *
FROM SQLD_21_01 A
WHERE EXISTS (SELECT 'X'
FROM SQLD_21_02 B
WHERE A.V1 = B.V1)| N1 | V1 |
| 1 | A |
| 3 | B |
IN과 EXISTS는 동일한 결과를 보여주며 EXISTS가 더 좋은 성능을 가짐
그래서 단순히 특정 컬럼의 값을 이용할 때는 IN 을 활용하고,
서브쿼리를 이용할 때는 EXISTS를 활용하는 것이 성능이 더욱 좋다.
EXISTS 안의 조건이 "존재" 하면! 전체 결과를 출력. 존재하지 않으면! 출력하지 않는다.
④
SELECT *
FROM SQLD_21_01 A
WHERE NOT EXISTS (SELECT 'X'
FROM SQLD_21_02 B
WHERE A.V1 = B.V1);| N1 | V1 |
| 2 | |
| 4 | C |
NOT EXISTS 안의 조건이 "존재하지 않을 때 만" 결과를 출력하라!
데이터 모델링에 대한 설명.
논리 모델링의 외래키는 물리 모델에서 반드시 구현되지 않는다.(선택사항)
실제 데이터베이스를 구축할 때 참고되는 모델은 (물리적 데이터 모델링) 이다.
(개념) 모델링 -> (논리) 모델링 -> (물리) 모델링 으로 가면서 더 구체적이고 개념모델링이 가장 (추상적)이다.
데이터 모델링의 3요소는 (Thing), (Attributes), (Relationship) 이다.
(개념적) 데이터 모델링
추상화 수준이 높고 업무중심적이고 포괄적인 수준의 모델링 진행, 전사적 데이터모델링, EA 수립시 많이 이용
엔터티 - 인스턴스 - 속성 - 속성값 에 대한 관계 설명
(한 개)의 엔터티는 (두 개) 이상의 (인스턴스)의 집합이어야 한다.
(한 개)의 엔터티는 (두 개) 이상의 (속성)을 갖는다.
하나의 속성은 (하나) 의 속성값을 가지며 하나 이상의 속성값을 가지는 경우 정규화가 필요하다.
하나의 엔터티의 인스턴스는 다른 엔터티의 인스턴스간의 관계인 (Paring)을 가진다.
※ 속성과 속성값은 다르다.
속성은 두개 이상이 되지만 속성값은 두개 이상이 되지 않는다.(된다면 정규화)
학생관련 정보를 조회하는 SQL을 작성하려고 한다.
조회하는 사람은 주로 학생 본인이 학번으로 조회를 주로 한다.
이런 SQL 일 때 성능을 개선하는 방법으로 가장 알맞은 것은?
인덱스 관련..
학번을 선두 컬럼으로 하는 INDEX를 생성한다.
학생 본인이 학번으로 주로 조회를 하기 때문에 학번이 선두컬럼으로 있는 인덱스가 가장 적절함
학교명이 앞으로 올 경우 반드시 학교명을 적어야 하는 부담이 있음
해당 문제는 SQLD보다는 SQLP에 가까운 튜닝 문제임.
아래의 SQL 에서 FUNCTION 자리에 쓰인 함수에 의한 결과 값이 다른 하나는? ②
SELECT function(3.46) FROM DUAL;① TRUNC (소수 자리 설정 없으면 DEFALUT : 0 == ROUND와 같음) - 3.00
② CEIL (천장) - 4.00
③ FLOOR (바닥) 3.00
④ ROUND (반올림) - 3.00
아래 ERD에서 3차 정규형을 만족하게될 때 엔터티의 개수는 몇개가 되는가? 3개.
ㄱ. 평가코드, 평가내역은 학번에 종속적
ㄴ. 코스명, 기간은 코스코드에 종속적
ㄷ. 평가코드 평가내역은 속성간 종속적 관계
아래 쿼리 중 결과 값이 다른 하나는? 마지막 CUBE.
GROUP BY ROLLUP(DNAME, JOB)
GROUP BY GROUPING SETS((DNAME, JOB), DNAME, NULL)
GROUP BY DNAME,JOB
UNION ALL
GROUP BY DNAME
UNION ALL
GROUP BY CUBE(DNAME, JOB)
-> GROUPING SETS(A,B,C) =
GROUP BY A
UNION ALL
GROUP BY B
UNION ALL
GROUP BY C다음의 SQL을 ANSI SQL 로 알맞게 바꾼것은?
단, 조인 조건과 조회 조건은 분리한다.
SELECT *
FROM SCOTT.EMP A, SCOTT.EMP B
WHERE A.DEPTNO = B.DEPTNO
AND B.DNAME = 'SALES'ANSI SQL에서 조인 조건절 (ON) 절에 사용된 조건절은 조인 전 조건으로 작용한다.
ON 절 이후 WHERE 절에서 쓰인 조건절은 조인후 조건절로 사용된다.
조인조건과 조회조건이 분리되어야 하므로
조회조건은 WHERE 절로 분리되어야 함.
A.DEPTNO = B.DEPTNO 이면 INNER JOIN(교집합?)이므로
INNER JOIN에 조회조건 분리가된 다음의 쿼리가 정답.
SELECT *
FROM SCOTT.EMP A
INNER JOIN SCOTT.DEPT B
ON A.DEPT.NO = B.DEPTNO
WHERE 1=1
AND B.DNAME = 'SALES';VIEW에 대한 설명.
독립성, 편리성, 보안성 (독편보)
- 독립성:
테이블 구조가 변경되어도 사용하는 응용프로그램은 변경하지 않아도 된다.
- 편리성:
복잡한 질의를 뷰로 생성함으로 관련 질의를 단순히 작성할 수 있다.
- 보안성:
뷰를 생성할 때 해당 칼럼을 빼고 사용자에게 정보를 감출 수 있다.
VIEW는 논리적인 SELECT를 포함하는 OBJECT로 실제 데이터를 저장하지않는다(물리성은없다)
TAB_A(SQL Server)
A IDENTITY(1, 1)
B VARCCHAR2(10)
INSERT INTO TAB_A(A, B) VALUES(1, 'A');
INSERT INTO TAB_A(B) VALUES('B');
INSERT INTO TAB_A(B) VALUES('D);
--
TAB_B(Oracle)
A CHECK (A < 5)
B VARCHAR2(10)
INSERT INTO TAB_B VALUES(1, 'A');
INSERT INTO TAB_B VALUES(2, 'B');
INSERT INTO TAB_B VALUES(6, 'D');
INSERT INTO TAB_B VALUES(NULL, 'X);
==
TAB_A
1, B
2, D
TAB_B
1, A
2, B
NULL, X
==
INSERT 구문은 IDENTITY 문제 또는 CHECK 문제가 주로 나온다.
조인에 대한 설명 중 Hash Join 에 대한 특성
각 테이블에 (INDEX)가 반드시 필요한 것은 아니다
일반적으로 작은 테이블을 (MEMORY)에 올리는 선행 테이블로 사용한다.
Hash Join은 (Equal Join)만 가능하다.(동등)
사전 Sorting 작업이 필요한 Join 알고리즘은 (Sort Merge Join)이다.
Predicate information(identified by operation id):
4- filter (ROWNUM < 10)
EMP TABLE에 대한 행제한 구문이 있다.
Predicate information 정보를 보면 행의 수를 9개로 제한하고 있다.
다음 SQL 쿼리문의 실행 순서.
SELECT DEPTNO, COUNT(EMPNO)
FROM SCOTT.EMP
WHERE SAL >= 500
GROUP BY DEPTNO
HAVING COUNT(EMPNO) > 2
ORDER BY DEPTNO;FROM→WHERE→GROUP BY→HAVING→SELECT→ORDER BY
아래와 같은 컬럼으로 구성된 테이블에
COL1을 구성컬럼으로 가지는 인덱스가 있다.
가장 효율적으로 해당 인덱스를 사용할 수 있는 조건절은?
TAB_A
COL1 NUMBER
COL2 VARCHAR2(10)
INDEX
COL1
--
WHERE COL1 = 10
--
문자를 숫자형으로 형변환함.
예외적으로 LIKE의 경우 COL을 무조건 형변환함. 숫자를 문자로 변환함.
WHERE COL1 LIKE '2%'
→ LIKE 의 경우 컬럼을 무조건 문자로 형변환함.
즉, 해당 조건절은 WHERE TO_CHAR(COL1) LIKE '2%'로 변형되어 인덱스를 사용하지 못함
WHERE COL1 IS NOT NULL
→ IS NOT NULL은 해당 인덱스를 FULL SCAN 할 수 있으나 효율이 떨어짐
부정형 비교는 인덱스 사용이 불가함
비교연산자의 어느 한쪽이 VARCHAR 유형 타입인 경우 문자 유형 비교에 대한 설명.
서로 다른 문자가 나올때까지 비교한다.
길이가 다르다면 짧은 것이 끝날 때까지만 비교한 후에 길이가 긴 것이 크다고 판단한다.
길이가 같고 다른것이 없다면 같다고 판단한다.
SPACE를 추가하여 길이를 맞춰 비교하는 방법은 CHAR 타입인 경우임.(이것은 VARCHAR)
아래의 SQL에 대해서 결과값이 다른 것은? (결합함수)
SELECT CONCAT ('RDBMS', 'SQL') FROM DUAL;
-- RDBMSSQL (Oracle)
SELECT 'RDBMS' || 'SQL' FROM DUAL;
-- RDBMSSQL (Oracle)
SELECT 'RDBMS' + 'SQL' FROM DUAL;
-- RDBMSSQL (SQL Server)
SELECT 'RDBMS' & 'SQL' FROM DUAL
-- & 치환변수아래의 Oracle SQL을 SQL Server SQL 로 전환한 것 중 가장 알맞은 것은?
SELECT ENAME, SAL
FROM (SELECT ENAME, SAL
FROM SCOTT.EMP
ORDER BY SAL DESC)
WHERE ROWNUM < 4;* TOP (Expression) [PERCENT][WITH TIES]
- WITH TIES: ORDER BY 조건 기준으로 TOP N의 마지막 행으로 표시되는 추가 행의 데이터가 같을 경우
N+ 동일 정렬 순서 데이터를 추가 반환하도록 지정하는 옵션 (마지막 기준 공통일 경우 모두 출력)
SELECT TOP(3) ENAME, SAL
FROM SCOTT.EMP
ORDER BY SAL DESC;ROWNUM < 4 의 의미는 3건까지 출력이므로 TOP(3) 임
WITH TIES를 사용할 경우 동일 데이터가 있을 때 추가 건수가 출력되는 현상이 가능함.(그런 옵션은 없었음)
SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME
FROM EMP NATURAL JOIN DEPT;위 ANSI JOIN SQL 에서 올바르지 않은 것은.
NATURAL JOIN에서 사용된 열은 식별자를 가질 수 없음
즉, EMP.DEPTNO와 같이 OWNER 명을 사용하면 ERROR가 생김
다음 중 엔터티의 종류가 아닌 것은? ③
① 교수
② 학생
③ 청약자
④ 수강
엔터티의 기준.
- 엔터티는 사람, 장소, 물건, 사건, 개념 등의 명사에 해당한다.
- 엔터티는 업무상 관리가 필요한 관심사에 해당한다.
- 엔터티는 저장이 되기 위한 어떤 것(Thing) 이다.
교수, 학생은 엔터티,
수강과 청약자에서 모호함.
청약자라는 개념은 관심사라기보다는 청약의 주체가 되는 속성에 가까운 것으로 보임
속성 값을 가지기에 애매함. 대다수 수강이라고 했으나 청약자로 체크한 사람도 답으로 인정됨.
아래의 계층형 SQL에서 리프 데이터이면 1, 그렇지 않으면 0 을 출력하고 싶을 때 사용하는 키워드
SELECT LEVEL, LPAD(' ', 4 * (LEVEL-1)) || EMPNO,
MGR, ( ) AS ISLEAF
FROM SCOTT.EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;
CONNECT_BY_ISLEAF:
전개 과정에서 해당 데이터가 리프데이터면 1, 아니면 0
CONNECT_BY_ISCYCLE:
전개 과정에서 자식을 갖는데, 해당 데이터가 조상으로 존재하면 1, 그렇지 않으면 0
여기서 조상이란 자신으로부터 루트까지의 경로에 존재하는 데이터를 말함
SYS_CONNECT_BY_PATH:
하위 레벨의 컬럼까지 모두 표시해줌 (구분자 지정 가능)
CONNECT_BY_ROOT:
Root 노드의 정보를 표시
TAB1
KEY1
B
C
D
E
TAB2
KEY2
A
B
C
--
SELECT *
FROM TAB1 A INNER JOIN TAB2 B
ON (A.KEY1 = B.KEY2)
-- 2건 (B, C) 교집합
SELECT*
FROM TAB1 A LEFT OUTER JOIN TAB2 B
ON (A.KEY1 = B.KEY2)
-- 4건 (B, C, D, E) 왼쪽기준
SELECT*
FROM TAB1 A RIGHT OUTER JOIN TAB2 B
ON (A.KEY1 = B.KEY2)
-- 3건 (A, B, C) 오른쪽 기준
SELECT*
FROM TAB1 A FULL OUTER JOIN TAB2 B
ON (A.KEY1 = B.KEY2)
-- 5건 (A, B, C, D, E) 합집합
SELECT*
FROM TAB1 A CROSS JOIN TAB2 B
-- 12건 Cartesian Product (4 * 3 = 12)WINDOW FUNCTION 사용한 SQL
윈도우 함수란
기존 관계형 DB는 칼럼간 연산은 쉽지만 행간의 연산은 어렵다.
행 간의 관계 정의를 위해 윈도우 함수를 고안한 것.
RANGE BETWEEN start_point AND end_point
start_point는 end_point와 같거나 작은 값이 들어감.
== Between 10 AND 15 : 10에서 15사이
Default 값은 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
UNBOUNDED PRECEDING : 현재 파티션의 첫 행부터 현재 행까지(처음부터 현재까지)
: start_point만 들어갈 수 있으며, 파티션의 first row
UNBOUNDED FOLLOWING : end_point만 들어갈 수 있으며, 파티션의 last row
CURRENT ROW: star, end_point 둘 다 가능. 윈도우는 CURRENT ROW에서 start 하거나 end함.
preceding(이전)
following(다음)
SQL 구문에서 FROM 절에 대한 설명
FROM 절은 가장 먼저 수행된다.
FROM 절에 사용되는 subquery를 보통 inline view라고 한다.
FROM 절은 SELECT와 항상 짝을 이룬다.
TABLE SQLD_21_01
N1 V1
1 A
2
3 B
4 C
TABLE SQLD_21_02
N1 V1
1 A
2
3 B
SELECT SUM(A.N1)
FROM SQLD_21_01 A,
SQLD_21_02 B
WHERE A.V1 <> B.V1;Non Equal Join의 경우는 조인 조건을 제외한 Cross Join 후
조인 조건을 필터 조건으로 처리하는 것이 좋다.
SUM(A.N1) = 12
서브쿼리에 대한 설명.
서브쿼리는 괄호로 감싸서 사용한다.
서브쿼리는 비교 연산자와 함께 사용가능하다.
서브쿼리, 특히 INLINE VIEW의 컬럼을 메인 쿼리에서도 사용 가능하다.
서브쿼리는 SELECT절, FROM절, WHERE절 등에서 사용 가능하다.
유저와 권한 중 권한에 대한 설명
사용자가 실행하는 모든 DDL 문장은 그에 해당하는 적절한 권한이 있어야만 문장을 실행 할 수 있다.
DBA 권한은 SYSTEM, SYS 등의 상위 유저와 그에 해당하는 권한을 가진 경우 부여 가능
테이블의 소유자는 해당 테이블의 DML 권한을 다른 유저에게 부여할 수 있다.
권한 부여를 편리하게 관리하기 위해 만들어진 권한의 집합인 ROLE이 있다.
SELECT A*
FROM SQLD_21_01 A,
SQLD_21_02 B
WHERE ( ) (A.V1) LIKE B.V1||'%';
LIKE 구문
쿼리문 WHERE 절에 주로 사용되며 부분적으로 일치하는 칼럼을 찾을 때 사용
SELECT *
FROM [테이블명]
WHERE LIKE [조건]
WHERE (UPPER) (A.V1) LIKE B.V1 || '%';소문자 / 대문자로 되어 있는 경우 두 개의 문자열이 같기 위해서 적용해야 하는 함수 UPPER를 사용
※ 유사한 문제로 주어진 테이블의 데이터 중 한 테이블에 스페이스가 있는 경우에는
빈칸 제거가 답이기 때문에 'TRIM'을 쓰면 된다.
WHERE (TRIM) (A.V1) LIKE B.V1
--공백 제거EMP 테이블은 사원과 매니저의 정보를 담은 계층형 데이터를 포함한 테이블이다.
매니저부터 사원까지 결제 단계가 가장 많은 레벨을 구할려고할 때 빈칸을 완성하시오.
SELECT ( )
FROM SCOTT.EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;SELECT MAX(LEVEL)
FROM SCOTT.EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;아래의 NOT EXISTS 구문을 동일한 결과를 출력하는 SQL로 변경할 때 빈칸을 완성하시오.
SELECT ...
FROM 급여이력 S
WHERE NOT EXISTS (SELECT 'X'
FROM 사원 P
WHERER P.사원번호 = S.사원번호)
SELECT ...
FROM 급여이력 S LEFT OUTER JOIN 사원 P
ON (S.사원번호 = P.사원번호)
WHERE (P.사원번호 IS NULL)NOT EXISTS의 OUTER JOIN으로의 변형을 묻는 문제로
NOT EXISTS는 OUTER JOIN으로 변경 시
NOT NULL COLUMN에 대한 IS NULL 체크로 NOT EXISTS를 구현가능하다.
아래 SQL 의 출력되는 ROWS의 개수를 구하시오.
SELECT DNAME, JOB,
COUNT(*) "Total Emp",
SUM(SAL) "Total Sal"
FROM SCOTT.EMP A, SCOTT.DEPT B
WHERE A.DEPTNO = B.DEPTNO
GROUP BY CUBE(DNAME, JOB)DNAME의 그룹은 3, JOB의 그룹은 5, DNAME,JOB의 그룹은 9개가 형성됨
GROUP BY CUBE(DNAME, JOB) = GROUP BY DNAME, JOB
UNION ALL
GROUP BY DNAME
UNION ALL
GROUP BY JOB
UNION ALL
모든 집합
CUBE 함수는 가능한 모든 조건에 대해서 소계를 리턴하는 함수여서
일단 DNAME 에 대한 소계 12개, JOB에 대한 소계 5개, 총합계 1개 = 18개
아래와 같은 SQL이 있을 때 조건절을 넣기 위한 키워드는 무엇인지 작성하시오.
SELECT *
FROM EMP
(WHERE) EMPID = 10;아래의 SQL의 결과로 나오는 ROWS의 수는?
SELECT *
FROM TAB1 A, TAB2 B
WHERE A.COL1 <> B.COL1;7건
아래의 SQL 의 출력 결과를 작성하시오.
SELECT COUNT(*)
FROM TAB1
WHERE EXISTS (SELECT 1 FROM TAB2 WHERE TAB2.COL1 = 'X');0
'Language > RDBMS' 카테고리의 다른 글
| 계층형 질의 (0) | 2023.03.16 |
|---|---|
| [SQLD]Prev-Exam Solving (30st) (0) | 2023.03.15 |
| SQL 함수 (0) | 2023.03.08 |
| 분산 데이터베이스의 성능 (0) | 2023.03.07 |
| 데이터베이스 구조와 성능 (0) | 2023.03.07 |