본문 바로가기
728x90
반응형

배우기

 (265)
[복습] SQL | Oracle database간의 link인 DB Link DB Link란 database 끼리의 link로 DB간의 데이터 이동이나 다른 DB의 데이터 수정 등을 가능하게 한다. 사전에 DB와 DB가 통신할 수 있게 방화벽이 허용되어야 한다. 예를들어 SCOTT 소유의 EMP가 있는데 HR이 이 테이블을 조회하려고 한다. 그래서 HR이 EMP라는 이름으로 조회가 가능하게끔 경로를 만드는 것이 synonym이다. 이 때에는 타겟 테이블인 EMP가 로컬에 있는 상황이었다(같은 DB안에 있음). 그런데 다른 DB에 있는 A라는 계정 소유의 T1 테이블이 있다. HR이 T1을 조회하게 하고 싶을 때, 원칙적으로는 불가하나 이걸 허용하는 것이 DB Link이다. DB간의 연결 통로를 만드는 것이다. 이 때에는 SELECT문을 날리는 순간 다른 DB에 붙어서 테이블의 원..
[복습] SQL | Oracle 시노님(Synonym)이해하기 + 복습후기(20231115) Synonym 시노님은 테이블의 동의어이며, 목적으로 첫 번째는 보안(원본 테이블의 이름을 노출하지 않기 위함)이며 두 번째로는 편의성이 있다. 먼저 문법으로는 다음과 같다. OR REPLACE: 기존의 같은 이름의 SYNONYM이 있다면 대체할 것. 재생성 대신 이미 객체가 존재할 경우 대체하는 것이다. PUBPLIC: SYNONYM 생성자 외의 유저들도 사용 가능 (DEFAULT: PRIVATE) 예를들어 HR유저가 SCOTT유저의 EMP 테이블을 조회하려고 한다. 일반적으로는 조회 불가하다. HR유저에게 조회권한을 부여하게 되면 다음과 같이 조회하여야 조회 가능하다. SELECT * FROM SCOTT.EMP; 그냥 SELECT * FROM EMP; 하면 조회되지 않는다. 그런데 SYNONYM을 활..
[복습] SQL | Oracle 부서의 계층을 표시하기, 계층형 질의 계층형 질의 계층형 질의란 각 행들의 연결고리를 통하여 행과 행 사이의 계층(depth)를 표현하는 기법이다. dept2파일을 열어보면 PDEPT는 상위부서인데 그렇게되면 아래와 같이 연결하여 해석할 수 있는 게 계층형 질의이다. -- 사장실 ----경영지원부 ------재무관리팀 ------총무팀 ----기술부 ----영업부 먼저, 문법은 다음과 같다. 문법만 보면 잘 이해가 안가서 예시가 필요하다. 사장실은 PDEPT가 NULL이므로 START에 NULL인 PDEPT부터 시작하겠다고 명시한 후, CONNECT는 DCODE와 PDEPT가 같은 조건을 이어가달라고 하는 것이다. 여기서 PRIOR 위치가 중요한데 START WITH에 PDEPT부터 시작하라고 하였으니 다음 레벨을 찾기 위한 조건이 PDEP..
[실습문제] 2023. 11. 14.(화) (2문제) 실습문제 1. subway 테이블을 이용하여 1) 노선별 승차가 가장 만흔 시간대를 확인하여라. 먼저 내 풀이이다. 나는 STEP1과 STEP2를 이용하여 인라인뷰로 풀이하였다. window function 배웠는데, 배워서 푼 거 적용했는데 그 코드가 날아가는 바람에 수업시간에 떠오르는대로 급하게 짠 쿼리이다. 그래도 이젠 급하게 쿼리 짤 때 뭐가 떠오르긴 한 것 같아서 다행이다. 다음은 선생님 해설이다. SOL 1, 2 둘 다 버퍼가 7밖에 나오지 않는다. 인라인뷰로 풀면 두 배가 출력된다. 2) 시간대별로 승차가 가장 많은 순서대로 3위까지 노선을 출력하여라(동순위 인정) 일단 내 코드인데 좀 이상하게 짰다. 쓸 데 없이 GROUP BY도 했고, 승차순위도 번거롭게 작성했다. 다음은 선생님 쿼리이다..
[복습] SQL | Oracle 부정연산자 not equal join 과 exists 연산자 + 복습후기(20231114) 먼저 부정연산자 중에 not equal in이 있다. != 이것인데, 이 부정연산자 not equal은 STEP 1) join할 테이블의 모든 카티시안 곱(발생 가능한 모든 조합)을 만든 후에 STEP 2) 모든 조합에서 조건이 일치하지 않은 데이터만 선택한다 → 따라서 의도하지 않은 데이터가 출력될 가능성이 높으므로 주의하여야 한다. 다음은 exists 연산자이다. exists 연산자는 서브쿼리의 참/거짓 여부를 참고하여 메인쿼리의 필터링을 하는 연산자이다. 메인쿼리의 비교 컬럽 없이 현업에서는 주로 'X'를 쓴다. 부정연산자로는 not exists가 있다. 예를 들어 설명해보겠다. 아래 두 테이블을 사용하여 블랙리스트 고객 정보를 출력하는 쿼리와 블랙리스트가 아닌 고객을 출력하는 쿼리를 각각 작성하여..
[복습] SQL | Oracle 쿼리 안에서 테이블 생성하기, with문 with문은 이름을 가진 서브쿼리(테이블처럼 사용 가능)를 정의하는 구문으로, 쿼리의 가독성을 높이고 쿼리의 복잡도를 일부 해결하는 구문이다. select 뿐 아니라 DML에도 사용이 가능하다. 두 가지 경우로 사용이 가능하다. CASE 1) 특정 테이블에서 데이터를 조회하여 새로운 임시 테이블을 만드는 경우 이렇게 임시 테이블 생성이 가능하다. CASE 2) 상수를 사용한 임시테이블 생성 with문은 쿼리베이스이며 세션베이스가 아니므로 다른 세션에서는 테이블이 조회되지 않는다. with문은 DB성능을 저하시켜 금지되는 회사도 있다고 한다. with문 예제이다. 각 학생의 같은 학년에서의 시험성적이 높은 학생 수를 with문을 사용하여 출력하여라. 코테에서 with문 대체적으로 사용이 가능하다고는 한다.
[복습] SQL | Oracle 서로 다른 행의 비교와 연산을 위한 함수 window function(lag, lead, sum, avg, min, max, count, rank) (2) window function을 이어서 작성해보겠다. 앞서 설명하였듯이 window function[WF]는 서로 다른 행의 비교와 연산을 위해 만든 함수로, group by를 쓰지 않고 데이터 축약 없이 그룹 연산이 가능하다. 종류로는 lag, lead, sum, avg, min, max, count, rank가 있다. sum, avg, min/max, count는 아래 링크를 참고하면 되겠다. https://metime.tistory.com/74 5. rank(일반 함수와 window function의 문법이 다르다) 1) 특정값에 대한 순위 확인(window function이 아닌 일반 함수) 문법은 아래와 같다. 예제를 하나 풀어보자. emp에서 급여가 3000이면 전체 급여 순위는 어느정도일까? ..
[실습문제] 2023. 11. 13.(월) 문제풀이(2문제) 한국소비자원 파일 데이터를 이용하여 아래와 같이 추가 테이블 생성 후 다음 문제의 쿼리를 작성하여라. 먼저 자료는 이런 형식으로 업로드 되었다. 1) 위 6개의 판매업소에 대해 각 품목의 최저가와 최저가 판매업소 출력하여라. 단, 세부 지점까지는 출력 하지 않음. 내가 짠 쿼리는 회생시킬 수 없는 것 같다. 일단 내가 이해하는 선에서 풀이해봐야겠다. 여기까지 하고 자료를 살펴봤을 때 판매가격에 . 이 들어가 있어서 특정 품목의 경우 최저가 확인이 어렵다. 판매가격이 . 인 경우 null로 변경하여 최저가 확인이 필요하다. 현재 판매가격이 문자컬럼으로 되어있기 때문에 위에서 to_number로 변경했고, 누락된 값에 대한 대치로 분석 목적과 데이터 상황에 따라 최대값/최소값/전체평균/그룹평균/삭제/null..
[복습] SQL | Oracle 서로 다른 행의 비교와 연산을 위한 함수 window function(lag, lead, sum, avg, min, max, count, rank) + 복습후기(20231113) window function[WF]란 제목 그대로 서로 다른 행의 비교와 연산을 위해 만든 함수로, group by를 쓰지 않고 데이터 축약 없이 그룹 연산이 가능하다. 종류로는 lag, lead, sum, avg, min, max, count, rank가 있다. 우리가 보통 부서별 급여 총합을 나타내고자 할 때 아래와 같이 쿼리를 짰었다. 그런데 window function을 사용하면 아래와 같이 짤 수 있다. 부서별 최대급여자도 아래와 같이 나타낼 수 있다. 함수 문법은 다음과 같다. 성능도 함께 비교해보자. emp에서 각 직원의 정보와 급여 총합을 함께 출력하고자 한다. 일단 group by 없이는 아래와 같은 쿼리는 에러가 나므로 쓸 수가 없다. 스칼라 서브쿼리를 사용한 경우 TABLE ACCES..
[실습문제] 2023. 11. 10.(금) 문제풀이(1문제) 실습문제 1. empno가 중복된 데이터가 다수 발견되어 PK생성이 안된다는 연락을 받았다. empno가 중복된 데이터를 삭제하세요(단, 하나는 남아야 함). 먼저 중복값이 들어간 데이터 만들기. 내가 접근한 방법은 다음과 같다. 여기서 중복값이 들어있다는 걸 눈으로 확인하였다. 복제 테이블을 하나 생성 후 중복사원을 포함한 모든 데이터를 delete하였다. 그리고 복제본에는 있고 원본에는 없는 직원을 찾아 원본 테이블로 해당 직원 자료를 insert 하여 도출해냈다. 그런데 선생님 해설은 다음과 같다. 위에서 보면 자신 포함 중복값이 3개인데 각각 두 건씩 출력되었다. 이대로 delete를 적용하면 될 것 같다. 이렇게 rowid를 이용하여 중복값을 제거하는 코드를 짰다. 이 방법을 기억해두라고 하셨다.
728x90
반응형

티스토리툴바