Oracle Database 10g SQL Fundamentals I - Practice 7

 

Oracle Database 10g SQL Fundamentals I - Practice 7

ar(11월9일 4차시)

Set Operators

  - UNION ALL : 합집합 (중복 허용)
  - UNION     : 합집합 (중복 제거)
  - INTERSECT : 교집합 (중복 제거)
  - MINUS     : 차집합 (중복 제거)

1.
SELECT DEPARTMENT_ID
FROM   DEPARTMENTS
MINUS
SELECT DEPARTMENT_ID
FROM   EMPLOYEES
WHERE  JOB_ID = 'ST_CLERK';

 

2.
SELECT COUNTRY_ID,COUNTRY_NAME
FROM   COUNTRIES
MINUS
SELECT C.COUNTRY_ID,C.COUNTRY_NAME
FROM COUNTRIES C, LOCATIONS L, DEPARTMENTS D
WHERE C.COUNTRY_ID = L.COUNTRY_ID
AND L.LOCATION_ID = D.LOCATION_ID
;

 

3.
COLUMN DUMMY NOPRINT
SELECT JOB_ID, DEPARTMENT_ID, 'X' DUMMY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 10
UNION
SELECT JOB_ID, DEPARTMENT_ID, 'Y' DUMMY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 50
UNION
SELECT JOB_ID, DEPARTMENT_ID, 'Y' DUMMY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 20
ORDER BY DUMMY
;
COLUMN DUMMY PRINT

 

4.
SELECT EMPLOYEE_ID, JOB_ID
FROM EMPLOYEES
INTERSECT
SELECT EMPLOYEE_ID, JOB_ID
FROM JOB_HISTORY
;

 

5.
1)
SELECT LAST_NAME, DEPARTMENT_ID, TO_CHAR(NULL)
FROM EMPLOYEES
UNION ALL
SELECT TO_CHAR(NULL), DEPARTMENT_ID, DEPARTMENT_NAME
FROM DEPARTMENTS
;

 

2)
SELECT LAST_NAME, DEPARTMENT_ID, TO_CHAR(NULL)
FROM EMPLOYEES
UNION
SELECT TO_CHAR(NULL), DEPARTMENT_ID, DEPARTMENT_NAME
FROM DEPARTMENTS
;