Chapter15 使用集合操作.docVIP

OCP-007-Chapter15 使用集合操作 ?????????????????????????????????????? 目的完成本课，可以作下列事情：描述SET运算符使用set运算符把多个查询组合为一个查询控制返回的列的顺序包含set运算符的查询称为复合查询UNION：选择任意一个查询中不重复的行UNION ALL：选择任意一个查询中的行，包括重复行INTERSECT：选择两个查询中都有的不重复的行MINUS：在第一个查询而不在第二个查询中的不重复的行所有的集合运算符拥有相同的优先级在select列表里面的列的个数和列的类型必须兼容，但不要求名称一样。当检查重复行时会计算NULL值IN运算符比集合运算符的级别高UNION ALL不对结果排序，不删除重复行，同时也不能使用DISTINCT关键字INTERSECT也会计算NULL值MINUS要求所有出现在where子句中的列必须出现在select子句中Order By子句出现在整个语句的最后，接受第一个select语句中的列、别名和位置符号所有的集合操作结果以升序排序，除了union allSELECT department_id, TO_NUMBER(null) location, hire_dateFROM employeesUNIONSELECT department_id, location_id, TO_DATE(null)FROM departments;SELECT employee_id, job_id,salaryFROM employeesUNIONSELECT employee_id, job_id,0FROM job_history;COLUMN a_dummy NOPRINTSELECT sing AS My dream, 3 a_dummyFROM dualUNIONSELECT Id like to teach, 1FROM dualUNIONSELECT the world to, 2FROM dualORDER BY 2;由于order by子句只能出现一次，所以这里不能精确的指定列名，只能使用列的位置练习(答案在卷2的333页)1. select department_idfrom employeesminusselect department_idfrom employeeswhere job_id=ST_CLERK;2. select country_id, country_namefrom countriesminusselect l.country_id, c.country_namefrom departments d, locations l, countries cwhere d.location_id=l.location_idand l.country_id=c.country_id;3. COLUMN a_dummy NOPRINTselect job_id,department_id, 1 a_dummyfrom employeeswhere department_id=10unionselect job_id,department_id, 2from employeeswhere department_id=50unionselect job_id,department_id, 3from employeeswhere department_id=20order by 34. select employee_id, job_idfrom employeesintersectselect employee_id, job_idfrom job_history;5. select last_name, department_id, to_char(null)from employeesunionselect to_char(null), department_id, department_namefrom departments;