본문 바로가기
728x90
반응형

배우기/복습노트[oracle sql]70

[복습] 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. 14.
[실습문제] 2023. 11. 10.(금) 문제풀이(1문제) 실습문제 1. empno가 중복된 데이터가 다수 발견되어 PK생성이 안된다는 연락을 받았다. empno가 중복된 데이터를 삭제하세요(단, 하나는 남아야 함). 먼저 중복값이 들어간 데이터 만들기. 내가 접근한 방법은 다음과 같다. 여기서 중복값이 들어있다는 걸 눈으로 확인하였다. 복제 테이블을 하나 생성 후 중복사원을 포함한 모든 데이터를 delete하였다. 그리고 복제본에는 있고 원본에는 없는 직원을 찾아 원본 테이블로 해당 직원 자료를 insert 하여 도출해냈다. 그런데 선생님 해설은 다음과 같다. 위에서 보면 자신 포함 중복값이 3개인데 각각 두 건씩 출력되었다. 이대로 delete를 적용하면 될 것 같다. 이렇게 rowid를 이용하여 중복값을 제거하는 코드를 짰다. 이 방법을 기억해두라고 하셨다. 2023. 11. 13.
[복습] SQL | Oracle SQL 데이터의 무결성을 위한 제어장치, 제약조건(Primary Key, Unique Key, Foreign Key, CHECK, NOT NULL) 중 Foreign Key 이어서(FK 옵션) ★Foreign Key에 대한 옵션에 대하여 알아보겠다. 1. on delete cascade: 부모를 지우면 자식도 함께 지워진다. 즉, 부모데이터 삭제 시 자식데이터도 함께 삭제된다. 2. on delete set null: 부모데이터 삭제 시 자식데이터는 null로 수정된다. 예를 들어 어느 부서가 다른 부서와 결합이 되면서 40이라는 부서가 사라지는 경우에 새로 배치가 되기 전에는 null일 필요가 있을 때 사용한다. 새롭게 정의가 될 때까지 비워두는 것이다. foreign key 옵션 테스트를 해보자. 먼저 on delete cascade 이다. 실제로는 이런 옵션을 잘 쓰지 않는다. 삭제가 되지 말아야 할 데이터가 삭제될 수 있어서 위험하기 때문이다. 이 기능을 잘 아는 사람이 써야한다. 다음.. 2023. 11. 12.
[실습문제] 2023. 11. 9.(목) 문제 풀이(3문제) 실습문제 1. uesr_constraint, user_cons_columns 테이블을 사용하여, 각 테이블의 제약조건이 있는 경우 제약조건 이름, 종류, 테이블명, 컬럼명, 참조테이블명, 참조컬럼명을 출력하는 쿼리를 작성하여라. 위 테이블에 7번 라인과 8번 라인을 주석처리 후 실행을 해보면 이런 결과가 출력된다. 여기서 EMP 테이블의 DEPTNO 컬럼이 PK라는 참조키가 걸려있는데 어느 테이블의 어느 컬럼에서 가져왔는지가 알고싶다. 그렇다면 user_constraint와 user_cons_columns를 한 테이블로 묶고 또 같은 테이블을 불러와서 join을 하면 추출할 수 있을 것이다. 실습문제 2. student4 테이블을 만들고 비만여부를 나타내는 컬럼을 새로 추가한 후, 각 학생들의 비만정보를.. 2023. 11. 12.
[실습문제] 2023. 11. 8.(수) 문제 풀이(4문제) 실습문제 1. professor_1을 professor와 동일하게 만들고 홈페이지가 없는 직원들은 email_id를 사용하여 다음과 같이 변경하여라. 양선희의 경우 => http://www.test.com/lamb1 먼저 내 코드 결과이다. 내 결과 16번 짤림ㅎ 선생님이 이 방법은 안좋은 예라고 하셨다. 한번의 테이블 스캔으로 가능한 쿼리를 서브쿼리를 사용하여 테이블을 두 번 스캔하는 방식이기 때문이다. 아래는 선생님 해설이다. 실습문제 2. student_1의 테이블을 student과 동일하게 만들고 제1의 전공번호가 301인 학생들과 출생년도와 동일한 학생들을 삭제하여라(제1의 전공번호가 301인 학생 포함) 선생님은 extract 안 쓰시고 to_char로 사용하였다. 실습문제 3. student.. 2023. 11. 10.
[복습] SQL | Oracle SQL의 분류(DDL, DML, DCL, TCL, DQL) 중 DML의 MERGE + 복습후기(20231108) 4 MERGE: 데이터 병합. 위험한 쿼리이므로 되도록 쓰지 말것. 원본 테이블 기준으로 변경테이블을 수정하므로 old와 new의 관계가 있어야 함. 상당히 좋지 않은 문법을 가짐. 기준테이블 수정할 테이블 ( ) --순서때문에 실수하는 경우가 있음. no price no price 1 1000 1 100 → update 필요 2 2000 2 200 → update 필요 3 3500 3 3500 4 4000 → insert 필요 insert쓰고, update하고 하지말고 한번에 처리하는 방법이 있는가 현업에서는 merge 사용을 금하고 있는 경우가 많음. 시험에는 많이 나옴★ ★문법 문법이 너무 어렵게 생겨서 바로 문제로 적용해보겠다. 예제) merge문 test 위 결과로는 3 rows upserted.. 2023. 11. 10.
728x90
반응형

티스토리툴바