START WITH last_name='King'
CONNECT BYPRIOR employee_id=manager_id
Group By 子句的增强
在Group By 中使用Rollup 产生常规分组汇总行以及分组小计
在Group By 中使用Cube 产生Rollup结果集+ 多维度的交叉表数据源
GROUPING函数：Rollup 和Cube有点抽象，他分别相当于n+1 和2的n次方常规Group by 运
算；那么在Rollup 和Cube的结果集中如何很明确的看出哪些行是针对那些列或者列的组合进行
分组运算的结果的？ 答案是可以使用Grouping 函数； 没有被Grouping到返回1，否则返回0
使用Grouping Set 来代替多次UNION
Exercise
--14. Showall employees who have managers with a salary higher than $15,000.
--Show the following data: employeename, manager name, manager salary, and salary grade of the manager.
createtable job_grades(grade_levelvarchar(20),lowest_salnumber,highest_salnumber);
insertinto job_gradesvalues('E',10000,1000000);
insertinto job_gradesvalues('E',9000,900000);
SELECT e.last_name, m.last_name manager,m.salary, j.grade_level
FROM employees e,employees m, job_grades j
WHERE e.manager_id = m.employee_id
AND m.salaryBETWEEN j.lowest_salAND j.highest_sal
AND m.salary >15000;
--15. Showthe department number, name, number of employees, and average salary of alldepartments,
--together with the names, salaries,and jobs of the employees working in each department.
SELECT d.department_id,
d.department_name,
count(e1.employee_id)employees,
NVL(TO_CHAR(AVG(e1.salary),'99999.99'), 'No average') avg_sal,
e2.last_name,
e2.salary,
e2.job_id
FROM departments d,employees e1, employees e2
WHERE d.department_id = e1.department_id(+)
AND d.department_id =e2.department_id(+)
GROUPBY d.department_id,
d.department_name,
e2.last_name,
e2.salary,
e2.job_id
ORDERBY d.department_id, employees;
--19. Showthe employee number, last name, salary, department number, and the averagesalary in their department for all employees.
select e.employee_id, e.last_name,e.department_id,avg(d.salary)
from employees d,employees e
where d.department_id = e.department_id
groupby e.employee_id, e.last_name, e.department_id;
--31. Write a query to delete the oldestJOB_HISTORY row of an employee bylooking up the
--JOB_HISTORY table for theMIN(START_DATE) for the employee.
--Delete the records of only those employees who have changed at leasttwo jobs.
--If your query executes correctly, you will get the feedback:
DELETEFROM job_history JH
WHERE employee_id = (SELECT employee_id
FROM employees E
WHERE JH.employee_id = E.employee_id
AND START_DATE =
(SELECTMIN(start_date)
FROM job_history JH
WHERE JH.employee_id =E.employee_id)
AND3 > (SELECTCOUNT(*)
FROM job_history JH
WHERE JH.employee_id =E.employee_id
GROUPBY EMPLOYEE_ID
HAVINGCOUNT(*) >=2));
--41. Write a SQL script file to dropall objects (tables, views, indexes, sequences, synonyms, and so on) that youown.
SET HEADINGOFF ECHO OFF FEEDBACKOFF
SET PAGESIZE0
SELECT'DROP' || object_type ||' ' || object_name ||';'
FROM user_objects
ORDERBY object_type
SET HEADINGON ECHO ON FEEDBACKON
SET PAGESIZE24
PL/SQL
用到的数据库Student1/student ora1/ora2/ora3 oracle system/manager
DECLARE
v_variable VARCHAR2(5);
BEGIN
SELECT column_name
INTO v_variable
FROM table_name;
EXCEPTION
WHEN exception_name THEN
...
END;
SQL不能select into PL/SQL可以。
PLSQL的块包括三种：匿名块、存储过程、函数；
PLSQL特有的%TYPE属性来声明与XX类型一致的变量类型：
identifier Table.column_name%TYPE;
记录类型的定义语法：
TYPE type_name IS RECORD
(field_declaration[, field_declaration]…);
identifier type_name;
这是一个简单数组
TYPE ename_table_type IS TABLE OF
e