escape
LIKE연산자에서 '%'와 '_'를 포함하는 단어 검색 방법
✔️ LIKE 연산으로 '%'나 ''가 들어간 문자를 검색하기 위해서는 ESCAPE 를 사용해야 한다. ''나 '%'앞에 ESCAPE로 특수 문자를 지정하면 검색할 수 있다.
ROWNUM 과 ROWID
❓ROWID 는 인덱스가 있어야만 볼수있나?
->ROWID는테이블에 데이터를 입력하면 자동으로 생성되는 값
"SELECT ROWID, EMPNO FROM EMP"와 같은 SELECT문으로 확인할 수 있다.
count() 활용해서 데이터찾기
dept 테이블에 deptno 에 중복된 데이터가 무엇인지 찾는법
-> count 가 2개 이상되는 데이터들을 찾아내면 된다 (2개 이상인게 중복된거니까!)
select deptno,count(*)
from dept
group by deptno
having count(*) >= 2; ➡️ 중복데이터가 있는 데이터가 뭔지 확인하는것
select *
from dept
where deptno in ( select deptno
from dept
group by deptno
having count(*) >=2); 
➡️ 서브쿼리 사용해서 중복 데이터 내용까지 확인하는것
제약 (constraint)
❓테이블 레벨, 컬럼 레벨로 거는 것의 차이는 뭘까? 봐도 잘 구분이 안됨
✔️ sal이 1200보다 큰 데이터만 입력되게 하고 아니면 입력 안되게 하라
- column level
crate table emp619 ( empno number(10), ename varchar2(10), sal number(10) constraint emp619_sal_ck check (sal>1200) ); - table level
내 정리 : 둘다 기존 sql 문법의 where 절에 해당되는 느낌이지만,create table emp620 (empno number(10), ename varchar2(10), sal number(10), constraint emp620_sal_ck check (sal >1200) );X
컬럼 레벨에 거는건, sql 테이블 작성할때 컬럼이름 옆에 쓰고,
테이블 레벨에 거는건 컬럼들 다 작성한다음에 밑에 쓰는정도..?
부모키와 자식키 (pk, fk)
전체적으로 내용 한번 더 공부해보기!
❓자식키, 부모키 설정 기준은 뭔가?
alter table dept
add constraint dept_pk primary key(deptno, loc);이렇게는 된다..? 컬럼 여러개를 한번에 pk 만들수는 있다? ❓🤔
그럼 둘다 걸린거임?
이거 궁금..!!
WITH절
🔎직업별 토탈 값의 평균값에 3000을 더한 값보다 더 큰 부서번호별 토탈 월급들을 출력하라
필요한 테이블 -> 직업별 토탈값 A
a 의 평균값 + 3000 보다 큰 부서번호별 토탈 월급 B
최종 목적 -> 부서번호별 토탈월급 (deptno, 토탈월급)
근데 이 최종목적의 토탈월급은 '직업별토탈월급의 평균 값 + 3000' 보다 큰! 이라는 조건
그러면 먼저 '직업별 토탈월급' 테이블을 임시로 만든다음에,
그 다음 조건들을 달아주고 최종 select-from 을 붙이면 된다.
with A as ( select job, sum(sal) as 토탈
from emp
group by job ),
B as ( select deptno, sum(sal) as 토탈
from emp
group by deptno
having sum(sal) > ( select avg(토탈) + 3000
from A) )
select deptno, 토탈
from B ; 
✏️ 기출문제 풀어보자
문제. 아래와 같은 데이터를 가진 테이블이 있을 때 SQL의 수행 결과를 적으시오
WITH WITH_TAB (last_name, EMP_ID, MGR_ID, sum_salary )
AS ( SELECT last_name,EMPLOYEE_ID,MANAGER_ID, salary
FROM HR.EMPLOYEES
WHERE MANAGER_ID IS NULL
UNION ALL
SELECT a.last_name, a.EMPLOYEE_ID, a.MANAGER_ID, a.salary + b.sum_salary
FROM HR.EMPLOYEES A, WITH_TAB B
WHERE B.EMP_ID = A.MANAGER_ID
)
SELECT SUM_SALARY
FROM WITH_TAB
WHERE EMPLOYEE_ID = 105;-
union all : 합집합 연산자
-
사용할 임시 테이블 : with_tab
-> 매니저아이디가 null 인 사원 테이블 (= 즉 KING)
여기의 sal 합은 24000 .. (이게 맞나) -
밑의 테이블 :
KING 나오는 테이블의 employee_id 가 EMPLOYEES 테이블의 MANAGER_ID 인 중에서employees 테이블은 a 라고 하고 위에 만든 king 테이블은 b 라고 하자.
employees 의 last name,
employees 의 employee_id,
employees 의 manager_id,
employees 의 salary + king 테이블의 salary의 합 (17000+17000+24000근데 구해야 하는 최종 결과 : KING테이블에서 empid 가 105 인것의 월급합?? 4800
49800??답: 54800 ㅡㅡ
*chatgpt 해석.. 이해안감
위의 SQL 쿼리는 HR.EMPLOYEES 테이블에서 사원들의 정보를 가져와서 조건에 따라 계층적으로 누적된 급여 합계를 계산하는 것을 목적으로 합니다.
WITH 문은 WITH_TAB이라는 임시 테이블을 생성하고, 해당 테이블에 계산된 값을 채워 넣습니다. 이 테이블은 루트 매니저인 사원의 정보와 급여를 담습니다.
첫 번째 부분은 루트 매니저인 사원들을 선택합니다. WHERE 절의 조건 MANAGER_ID IS NULL은 매니저가 없는 사원들을 필터링하는 역할을 합니다.
두 번째 부분은 재귀적인 쿼리로, WITH_TAB 테이블에 이미 계산된 급여의 합계를 가지고 있는 사원들과 관련된 사원들의 정보를 선택합니다. 이는 MANAGER_ID와 EMP_ID를 사용하여 두 테이블을 조인하는 방식으로 이루어집니다. 그리고 선택된 사원의 급여를 현재 사원의 급여와 합산하여 새로운 합계를 계산합니다.
마지막 부분은 WITH_TAB 테이블에서 EMP_ID가 105인 사원의 급여 합계를 선택합니다.
즉, 이 쿼리는 MANAGER_ID가 NULL인 루트 매니저부터 시작하여 하위 직원들의 급여를 누적하여 계산한 후, EMP_ID가 105인 사원의 최종 급여 합계를 조회하는 것입니다.
A 테이블에 대한 해석:
해당 SQL 쿼리는 HR.EMPLOYEES 테이블(A)과 WITH_TAB 테이블(B)을 조인하여 결과를 조회하는 것을 나타냅니다.
쿼리는 다음과 같은 열을 선택합니다: a.last_name, a.EMPLOYEE_ID, a.MANAGER_ID, a.salary + b.sum_salary. 이는 HR.EMPLOYEES 테이블의 last_name, EMPLOYEE_ID, MANAGER_ID 열과 salary 열에 b.sum_salary 값을 더한 것입니다.
FROM HR.EMPLOYEES A, WITH_TAB B는 HR.EMPLOYEES 테이블(A)과 WITH_TAB 테이블(B)을 조인합니다.
WHERE B.EMP_ID = A.MANAGER_ID는 WITH_TAB 테이블의 EMP_ID 열과 HR.EMPLOYEES 테이블의 MANAGER_ID 열이 일치하는 조건을 나타냅니다. 이 조건은 WITH_TAB 테이블에서 각 직원의 매니저를 찾기 위해 사용됩니다.
결과적으로, 이 쿼리는 HR.EMPLOYEES 테이블의 직원과 WITH_TAB 테이블의 계층 구조를 사용하여 각 직원의 정보와 해당 직원의 급여에 부하 직원들의 급여 합계를 더한 결과를 반환합니다.
