EXISTS & IN

문제

테이블 : e21.e2av_emp_base_inf_all e
출력 내용 : name, dept_nm

• 양승현 관악팀
• 양영일 경기수요개발팀
• 양인규 CNG개발1과(은평)
• 양창경 안전점검

select name, dept_nm
 from e21.e2av_emp_base_inf_all
 where (name, dept_nm) in (('양승현','관악팀')
                            ,('양영일','경기수요개발팀')
                            ,('양인규','CNG개발1과(은평)')
                            ,('양창경','안전점검')
                           );
                           
                           
select a.name, a.dept_nm
 from e21.e2av_emp_base_inf_all a
    , (
        select '양승현' as name, '관악팀' as dept_nm from dual
        union all
        select '양영일' as name, '경기수요개발팀' as dept_nm from dual
        union all
        select '양인규' as name, 'CNG개발1과(은평)' as dept_nm from dual
        union all
        select '양창경' as name, '안전점검' as dept_nm from dual
       ) b
where a.name = b.name and a.dept_nm = b.dept_nm   

문제 2

부서역할로 지정된 역할 중에 개인역할로 부여되지 않은 역할 정보 출력
대상테이블

• A11.A1DT_ROLE_DEPT <- 부서권한 정보
• A11.A1DT_ROLE_USER <- 개인 권한 정보
• A11.A1DT_ROLE <- 역할정보
  
  출력대상
• 역할ID, 역할명, 부서코드

1. 부서역할로 지정된 역할 찾기

SELECT ROLE_ID, DEPT_CD
FROM A11.A1DT_ROLE_DEPT RD
WHERE NOT EXISTS (SELECT * FROM A11.A1DT_ROLE_USER RU WHERE RD.ROLE_ID=RU.ROLE_ID);

2. 역활정보랑 JOIN 해서 필요한 정보 추출 ( 답 )

SELECT DR.*, R.NAME
FROM ( SELECT ROLE_ID, DEPT_CD
       FROM A11.A1DT_ROLE_DEPT RD
       WHERE NOT EXISTS (SELECT * FROM A11.A1DT_ROLE_USER RU WHERE RD.ROLE_ID=RU.ROLE_ID)
     ) DR,
     A11.A1DT_ROLE R
WHERE DR.ROLE_ID = R.ROLE_ID;

-- 3. 검사

SELECT EMPLOYEE_ID, ROLE_ID
FROM A11.A1DT_ROLE_USER
WHERE ROLE_ID IN ( 'B10201', 'R00006', 'C20021' , 'C10019' ,'E55003');


SELECT ROLE_ID
FROM A11.A1DT_ROLE_DEPT
MINUS
SELECT ROLE_ID
FROM A11.A1DT_ROLE_USER;

문제 3

사용자정보 테이블에서 서울도시가스 법인(COM_ID = '10') 직원 중 다른 법인에 동명이인이 없는 사람만 출력

대상테이블

• A11.A1AT_USER_MASTER
  출력대상
• 법인명, 부서명, 사번, 이름 출력

동명이인이 없는 사람만 출력

SELECT EMPLOYEE_ID, COM_ID, COM_NAME, NM_KOR
FROM A11.A1AT_USER_MASTER M10
WHERE NOT EXISTS(SELECT * FROM A11.A1AT_USER_MASTER M WHERE COM_ID != '10'
	AND M10.NM_KOR = M.NM_KOR) 
    AND COM_ID='10'
    AND USER_STATUS = 'Y' ;

동명이인이 있는 사람 출력

SELECT EMPLOYEE_ID, COM_ID, COM_NAME, NM_KOR
FROM A11.A1AT_USER_MASTER M10
WHERE EXISTS(SELECT * FROM A11.A1AT_USER_MASTER M WHERE COM_ID != '10' 
	AND M10.NM_KOR = M.NM_KOR) 
    AND COM_ID='10';

테스트

-- 테스트 1 ( 유저마스터에서 COM_ID 가 10이 아닌 회사에서 동명이인 없는 사람들의 이름과 일치하는 데이터만 뽑아내기 --> 0 나와야함 ) 
SELECT M10.NM_KOR, M10.COM_ID
FROM A11.A1AT_USER_MASTER M10,
     (SELECT EMPLOYEE_ID, COM_ID, COM_NAME, NM_KOR
      FROM A11.A1AT_USER_MASTER M10
      WHERE NOT EXISTS(SELECT * FROM A11.A1AT_USER_MASTER M WHERE COM_ID != '10' AND M10.NM_KOR = M.NM_KOR) AND COM_ID='10'
      ) NAME
WHERE M10.NM_KOR = NAME.NM_KOR AND M10.COM_ID !='10';



-- 테스트용 2 
SELECT COUNT(*) FROM A11.A1AT_USER_MASTER M10 WHERE COM_ID = '10' GROUP BY COM_ID;

SELECT COUNT(*)
FROM A11.A1AT_USER_MASTER M10
WHERE NOT EXISTS(SELECT * FROM A11.A1AT_USER_MASTER M WHERE COM_ID != '10' AND M10.NM_KOR = M.NM_KOR) AND COM_ID='10'
GROUP BY COM_ID;

SELECT COUNT(*)
FROM A11.A1AT_USER_MASTER M10
WHERE EXISTS(SELECT * FROM A11.A1AT_USER_MASTER M WHERE COM_ID != '10' AND M10.NM_KOR = M.NM_KOR) AND COM_ID='10'
GROUP BY COM_ID;