DATABASE #9. SQL X .CSV IMPORT + TIPS

이번 포스트에서는 Oracle DB를 기준으로 SQL 실습을 하며 겪은 여러 개념과 문제 해결 과정을 정리해보겠습니다..

---

1. FOREIGN KEY와 INSERT 순서

employee 테이블의 superssn은 같은 테이블의 ssn을 참조하는 외래키입니다.

 

이 경우 상사(superssn)가 될 사원의 데이터가 먼저 삽입되어 있어야 하며, 그렇지 않으면 다음과 같은 오류가 발생합니다:

ORA-02291: integrity constraint violated - parent key not found

해결 방법

• 상사 레코드를 먼저 삽입한 뒤, 참조하는 레코드를 삽입
• INSERT 시 오류가 나더라도 전체를 무시하고 진행하면 이후에 수동 수정 필요 
• 사실 베스트는, 나중에 제약사항을 추가하는것 (무결성 원칙 위배 가능성을 아예 배제한 후, 그 이후 제약사항 추가)

---

2. 제약 조건 이름 바꾸기

ALTER TABLE employee RENAME CONSTRAINT emp_fk_employee TO emp_superssn_fk;

---

3. DISTINCT와 중복 제거

SELECT DISTINCT emp.fname, emp.lname
FROM employee emp
JOIN works_on work ON emp.ssn = work.ssn
WHERE work.hours >= 10 AND emp.dno = 3;

의도: 중복된 투플 제거. DISTINCT는 SELECT 절 전체 기준으로 중복 제거함.

---

4. 외래키로 연결된 테이블 처리

employee에 데이터를 추가할 때, 이 직원이 특정 프로젝트에서 일한 정보도 works_on에 반드시 추가해야 외래키 오류가 나지 않음.

예시:

INSERT INTO works_on VALUES (201, 3388, 12);
INSERT INTO works_on VALUES (201, 3321, 10);
...

---

5. 모든 프로젝트에 참여한 사원 구하기

쿼리:

SELECT e.ssn
FROM employee e
WHERE NOT EXISTS (
    SELECT p.pnumber
    FROM project p
    WHERE NOT EXISTS (
        SELECT 1
        FROM works_on w
        WHERE w.ssn = e.ssn AND w.pnumber = p.pnumber
    )
);

• 내부 NOT EXISTS: 해당 사원이 일한 프로젝트가 아니면 TRUE
• 외부 NOT EXISTS: 위의 조건을 만족하는 프로젝트가 하나도 없으면 = 모든 프로젝트에 참여함

---

6. 모든 프로젝트에 참여하지 않은 사원 찾기

SELECT emp.fname, emp.lname
FROM employee emp
WHERE EXISTS (
    SELECT proj.pnumber
    FROM project proj
    WHERE NOT EXISTS (
        SELECT 1
        FROM works_on work
        WHERE work.pnumber = proj.pnumber AND emp.ssn = work.ssn
    )
);

• 내부 NOT EXISTS는 "참여하지 않은 프로젝트"를 찾고,
• 외부 EXISTS는 그러한 프로젝트가 하나라도 있으면 해당 직원을 출력

즉, 하나라도 참여 안 한 프로젝트가 있는 사원 목록

---

7. 집계 함수와 GROUP BY

SELECT proj.pname, SUM(works.hours)
FROM project proj
JOIN works_on works ON works.pnumber = proj.pnumber
GROUP BY proj.pname;

• 프로젝트별 총 근무 시간을 구함
• GROUP BY에 지정된 속성에 대해서만 집계 가능

---