SUBSTR（压缩字符串为一个固定的长度）: Extracts a string of determined length

eg : SUBSTR(‘HelloWorld’,6,5)(输出结果为World)

LENGTH（显示一个字符串的长度）: Shows the length of a string as a numeric value

eg : LENGTH(‘HelloWorld’)(输出结果为10)

INSTR（找到一个字母所在的位置）: Finds the numeric position of a named character

eg : INSTR(’HelloWorld’.’W’)(输出结果为6)

LPAD（右对齐）: Pads the character value right-justified

eg : LPAD(salary,10,’*’)(输出结果为＊＊＊＊＊24000)

RPAD（左对齐）: Pads the character value left-justified

eg : RPAD(salary,10,’*’)（输出结果为 24000*＊＊＊＊）

REPLACE: 字母替换

eg: REPLACE(‘JACE and JUE’,’J’,’BL’)(输出结果为BLACE and BLUE)

TRIM（去掉字符串的首字母或尾字母）: Trims heading or trailing characters (or both) from a character string (If trim_character or trim_source is a character literal, you must enclose it in single quotation marks.)

eg : TRIM(‘H’ FROM ‘HelloWorld’)(输出结果为elloWorld)

隐式数据类型转换 :

对于直接赋值，Oracle服务其能够自动地进行下面的转换：

从 varchar2/char 到 number

从 varchar2/char 到 date

从 number 到 varchar2

从 date 到 varchar2

eg : the expression hire_date > '01-JAN-90' results in the implicit conversion from the string '01-JAN-09' to a date

对于表达式复制，Oracle服务其能够自动地进行下面的转换：

从 varchar2/char 到 number

从 varchar2/char 到  date

eg : the expression salary = '20000' results in the implicit conversion of the string '20000' to the number 20000.

显式数据类型转换 :

数字 <==> 字符（to_number/to_char）

字符 <==> 日期（to_date/to_char）

to_char(number | date,[fmt],[nlsparams])(converts a number or date to a varchar2 character string with format model fmt)

eg : to_char(date,'format_model')

to_number(char,[fmt],[nlsparams])(converts a character string containing digits to a number in the format specified by the optional format model emt.)

to_date(char,[fmt],[nlsparams])(convets a character string representing a date to a date value according to the fmt that is specified.If emt is omitted, the format is DD-MON-YY.)

eg : select employee_id, to char(hire_date,'MM/YY') Month_Hired from employees where last_name = 'Higgins';

日期格式模版的元素 ：

YYYY(年的全写（四位数字）)

YEAR(年的拼写)

MM(月的两位数值)

MONTH(月的全称)

MON(月的前三个字母缩写)

DY(天的前三个字母缩写)

DAY(天的全称)

DDD(年)

DD(月)

D(周)

elements of the date format model :

HH24:MI:SS AM(15:45:32 PM)

DD ''of" MONTH

ddspth

to_char函数用于日期转换：

eg : select last_name,to_char(hire_date,'fmDD Month YYYY') as HIREDATE from employees;

eg : select last_name,to_char(hire_date,'fmDdspth "of" Month YYYY fmHH:MI:SS AM') as HIREDATE from employees;(to display teh dates in a format that appears as "Seventeenth of June 1987 12:00:00 AM.")

to_char函数用于数字转换：

to_char(number,'format_model')

eg : select ro_char(salary,'$99,99.00') SALARY from employees where last_naem = 'Ernst';

使用to_number函数实现将字符串转换为数字格式：

to_number(char ,[format_model'])

使用to_date函数实现将字符串转换为日期格式：

to_date(char,['format_model'])

eg : select last_name,hire_date from employees where hire_date = to_date('May 24,1999','fxMonth DD,YYYY');(to display the name and hire date for all employees who started on May 24,1999.because the fx modifier is used,an exact match is required and the spaces after the word May are not recognized. )

eg : select to_char(next_day(add_months(hiredate,6),'Friday'),'fmDay,Month DDth,YYYY') "Next 6 Month Review" from employees order by hire_date;(to display the date of the next Friday that is six months from the hire date.(the result date should appear as Friday,August 13th,1999.))

通用函数（可用于任意数据类型（number,date,char or varchar2），并且适用于null值）

NVL(expr1,expr2)(converts a null value to an actual value)(expr1 和expr2的数据类型必须一样)

eg : select last_name,salary,nvl(commission_pct,0),(salary*12) + (salary*12*nvl(commission_pct,0)) AN_SAL from employees;

NVL2(expr1,expr2,expr3)(if expr1 is not null,nvl2 returns expr2. If expr1 is null, nvl2 returns expr3. the argument expr1 can have any data type,the argument epr2 and expr3 can have any date types except long, if the data types of expr2 and expr3 are different, the oracle server converts expr3 to the data type of expr2 before comparint them unless expr3 is a null constant. so the data type of the return value is always the same as the data type of expr2 unless expr2 is character data, in which case the return value's data type is varchar2.)

eg : select last_name,salary,commission_pct,nvl2(commission_pct, 'sal_comm', 'sal') income from employees where department_id in (50,80);

NULLIF(expr1,expr2)(compares two expressions and returns null if they are equal, returns the first exression if they are not equal)

eg : select first_name,length(first_name) "expr1", last_name, length(last_name) "expr2", nullif(length(first_name),length(last_name)) result from employees;

COALESCE(expr1,expr2,...,exprn)(returns the first non-null expression in the expression list. all expressions must be of the same data type.)

eg : sleect last_name,coalesce(manager_id, commission_pct, -1) comm from employees order by commission_pct;

条件表达式（if－then－else逻辑）（case表达式，decode表达式）

Note: The CASE expression complies with ANSI SQL. The DECODE function is specific to Oracle syntax.

case表达式

eg : case expr when comparison_expr1 then return_expr1 [when comparison_expr2 then return_expr2 when comparison_exprn then return_exprn else else_expr] end

注：all of the expressions(expr,comparison_expr and return_expr) must be of the same data type, which can be char,varchar2,nchar,or nvarchar2.

eg : select last_name,job_id,salary,case job_id when 'it_prog' then 1.10*salary when 'st_clerk' then 1.15*salary when 'sa_rep' then 1.20*salary else salary end "revised_salary" from employees;

eg : select last_name,salary,(case when salary < 5000 then 'Low' when salary < 10000 then 'Mediun' when salary < 20000 then 'Good' else 'Excellent' end) qualified_salary from employees;

decode函数

eg : decode(col|expression, search1, result1 [,search2,result2,...,] [,default])

eg : select last_name,job_id,salary, decode(job_id, 'it_prog', 1.10*salary, 'st_clerk', 1.15*salary, 'sa_rep', 1.20*salary, salary) revised_salary from employees;

eg : select last_name, salary, decode (trunc(salary/2000,0), 0, 0.00, 1, 0.09, 2, 0.20, 3,0.30,4, 0.40,5, 0.42, 6, 0.44, 0.45) TAX_RATE  from employees where department_id = 80;（显示每一位在部门80的员工的适用税率)

组函数

eg : select [column,] group_function(column), ... from table_name [where condition] [group by column] [order by column];

注：distinct makes the function consider only nonduplicate values; all makes it consider every value, including duplicates. (the default is all and therefore does not need to be specified.)

avg([distinct|all]n) (average value of n, ignoring null values

count({*|[distinct|all]expr}) (number of rows,where expr evalutes to something other than null(count all selected rows using * , including duplicates and rows )

eg : select count(*) from employees where department_id = 50; （displays the number of employees in department 50.) (返回ID为50的行数)

eg : select count(commission_pct) from employees where department_id = 80; (displays the number of employees in department 8 who can earn a commission.)（返回commission为非空值的行数）

注 ：COUNT(*) returns the number of rows in a table that satisfy the criteria of the SELECT statement, including duplicate rows and rows containing null values in any of the columns.

If a WHERE clause is included in the SELECT statement, COUNT(*) returns the number of rows that satisfy the condition in the WHERE clause.

In contrast, COUNT(expr) returns the number of non-null values that are in the column identified by expr.

COUNT(DISTINCT expr) returns the number of unique, non-null values that are in the column identified by expr.

eg : select count(distinct department_id) from employees; (to display the number of distinct department values in the employees table.)

组函数忽略空值的列：

eg : select avg(commission_pct) from employees; （返回值为 .2125）

nvl函数强制组函数包括空值：

eg : select avg(nul(commission_pct,0)) from employees; (返回值为 .0425)

max([distinct|all]expr) (maximun value of expr,ignoring null values)

min([distinct|all]expr) (minimum value of expr, ignoring null values)

stddev([distinct|all]x) (standard deviation of n, ignoring null values)

sum([distinct|all]n) (sum values of n, ignoring null values)

variance([distinct|all]x) (variance of n, ignoring null values)

在数值型数据中可以适用avg 和 sum

eg : select avg(salary), max(salary), min(salary), sum(salary) from employees where job_id like '%REP%';

对于数字，字符和日期数据类型，可以使用min 和 max

eg : select min(hire_date),max(hire_date) from employees;

创建组群数据（group by）

eg ：select column, group_function(column) from table [where condition] [group by group_by_expression] [order by column];

注：group_by_expression specifies columns whose values determine the basis for grouping rows.

you cannot use a column alias in the group by clause(group by条件句后不能有别名)

all columns in the select list that are not in group functions must be in the group by clause.(select 后的列不在组函数里就必须在group by 条件句里)

eg : select department_id, avg(salary) from employees group by department_id; (displays the department number and the average salary for each department.)

the group by column does not have to be in the select list(group by条件句中的列不必要在select中）

eg : select avg(salary) from employees group by department_id;(displays the average salaries for each department without displaying the respective department numbers. without the department numbers, however, the results do not look meaningful.)

you can use the group function in the order by clause :

select department_id,avg(salary) from employees group by department_id order by avg(salary);

using the group by clause on multiple columns

eg : select department_id dept_id,job_id,sum(salary) from employees group by department_id,job_id;

注 ：1.any column or expression in the select list that is not an aggregate function must be in the group by clause（whenever you use a mixture of individual items (eg : department_id) and group functions (eg : count(last_name) in the same select statement, you must include a group by clause that specifies the individual items. If the group by clause is missing, then the error message "not a single_group group function" appears and an asterisk(*) points to the offending column)）

注 ： 2. you cannot use the where clause to restrict groups. you use the having clause to restrict groups.

注 ： 3. you cannot use group functions in the where clause.

eg : select department_id, avg(salary) from employees having avg(salary) > 8000 group by department_id;

restricting group results with the having clause

eg : select column, group_function from table_name [where condition] [group by group_by_condition] [having group_condition] [order by column];

eg : select department_id, max(salary) from employees group by department_id having max(salary) > 1000;(displays department numbers and amximum salaries for those departments with a maximum salary that is greater than $10000.)

注： you can use the group by clause without a group function in the select list(select后没有组函数也可以使用group by 条件句)

eg : select department_id, avg(salary) from employees group by department_id, having max(salary) > 10000;(displays the department numbers and average salaries for those departments with a maximum salary that is greater than $10000.)

eg : select job_id, sum(salary) payroll from employees where job_id not like '%rep%' group by job_id having sum(salary) > 13000 order by sum(salary);(displays the job_id and total monthly salary for each job thqt has a total payroll exceeding $13000. the example excludes sales representives and sort the list by the total monthly salary.)

eg : select max(avg(salary)) from employees group by department_id;(displays the maximum average salary.)

04 retrieving data from several tables

displaying data from multiple tables(joins)

using subqueries to solve queries

using the set operators

types of joins :

cross joins

natural joins

using clause

full (or two-sided) outer joins

arbitrary join conditions for outer joins

e.g. : select table.column, table2.column from table1 [natural join table2] | [join table2 using (column_name)] | [join table2 on (table1.column_name = table2.column_name)] | [left | right | full outer join table2 on (table1.column_name = table2.column_name)] | [cross join table2];

注 : table1.column denotes the table and column from which data is retrieved . natural join joins two tables based on the same column name. join table using column_name performs an equijoin based on the column name. join table on table1.clumn_name = table2.column_name performs an equijoin based on the condition in the on clause. left/right/full outer is used to perform outer joins. cross join returns a cartesian product from the two tables.

e.g. : select employees.employee_id,employees.last_name,departments.department_id,departments.location_id from employees join departments on employees.department_id = departments.department_id;

note : when joining with the using clause,you cannot qualify a column that is used in the using clause it self. furthermore, if that column is used anywhere in the sql statement, you cannot alias it.

using table aliases :

use table aliases to simplify queries

use table aliases to improve performance.

e.g. : select e.employee_id,e.last_name,d.location_id,department_id from employees e join departments d using (department_id);

note : table alias is valid for only the current select statement.

natural joins

creating natural joins :

the natural join clause is based on all columns in the two tables that have the same name.

It select rows from the two tables that have equal values in all matched columns.

If the columns having the same names have different data types, an error is returned.

You can join tables automatically based on columns in the two tables that have matching data types and names by using the keywords natural join.

e.g. : select department_id,department_name,location_id,city from departments natural join locations;(the locations table is joined to the departments table by the locationn_id column,which is the onoy column of the same name in both tables. if other common columns were present,the join would have used them all.)

natural joins with a where clause :

e.g. : select department_id,department_name,location_id,city fom departments natural join locations where department_id in (20,50);(limits the rows of output to those with a department id equal to 20 or 50)

creating joins with the using clause :

if several columns have the same names but the data types do not match, the natural join clause can be modified with the using clause to specify the columns that should be used for an equijoin.

use the using clause to math only one column when more than one column matches.

do not use a table name or alias in the referenced columns.

the natural join and using clauses are mutually exclusive.

e.g. : select l-city,d.department_name from locations l join departments d using (location_id) where location_id = 1400;

notes : eauijoins are also called simple joins or inner joins.

e.g. : select employees.employee_id,employees.last_name,departments.location_id,department_id from employees join departments using (department_id);(joins the department_id column in the employees and departments tables, and thus shows the location where an employee works.

the on clause

creating joins with the on clause :

the join condition for the natural join is basically an equijoin of all columns with the same name.

use the on clause to specify arbitrary conditions or specify columns to join.

the join condition is separated from other search conditions.(use the on clause to specify a join condition. this lets you specify join conditions separate from any search or fitter conditions in the where clause.)

the on class makes code easy to understand.

e.g. : select e.employee_id,e.last_name,e.department_id,d.department_id,d.location_id from employees e join departments d on (e.department_id = d.department_id);(the department_id columns in the employees and departments table are joined using the on clause. wherever a department id in the employees table equals a department id in the departments table , the row is returned.)

note : you can also use the on clause to join columns that have different names.

self-joins using the on clause :

e.g. : select e.last_name emp,m.last_name mgr from employees e join employees m on (e.namager_id = m.manager_id);

note : the on clause can also be used to join columns that have different names, within the same table or in a different table.

applying additional conditions to a join :

e.g. : select e.employee_id,e.last_name,e.department_id,d.department_id,d.location_id from employees e join departments d on (e.department_id = d.department_id) and e.manager_id = 149;(performs a join on the employees and departments tables and in addition, displays only employees who have a manager id of 149.to add additional conditions to the on clause, you can add and clauses. alternatively, you can use a where clause to apply additional conditions.)

e.g. : select

e.employee_id,e.last_name,e.department_id,d.department_id,d.location_id from employees e join departments d on (e.department_id = d.department_id) where e.manager_id = 149;

creating three-way joins with the on clause :

e.g. : select employee_id,city,department_name from employees e join departments d on d.department_id = e.department_id join locations l on d.location_id = l.location_id;

note : a three-way join is a join of three tables. joins are performed from left to right.

non-equijoins

e.g. : select e.last_name,e.salary,j.grade_level from employees e join job_grades j on e.salary between j.lowest_sal and j.highest_sal;(creates a non-equijoin to evaluate an employee’s salary grade. the salary must be between any pair of the low and high salary ranges.)

note : remember to specify the low value first and the high value last when using between.

outer joins

inner versus outer joins :

in sql : the join of two tables returning only matched rows is called an inner join.

a join between two tables that returns the result of the inner join as well as the unmatched rows from the left (or right) tables is called a left(or right) outer join.

a join between two tables that returns the results of an inner join as well as the results of a left and right join is a full outer join.

left outer join :

e.g. : select e.last_name,e.department_id,d.department_name from employees e left outer join departments d on (e.department_id = d.department_id);(retrieves all rows in the employees table, which is the left table even if there is no match in the departments table.)

right outer join :

e.g. : select e.last_name,e.department_id,e.department_name from employees e right outer join departments d on (e.department_id = d.department_id);(retrieves all rows in the departments table, which is the right table even if there is no match in the employees table.)

full outer join :

e.g. : select e.last_name,d.department_id,d.department_name from employees e full outer join departments d on (e.department_id = d.department_id);(retrieves all rows in the departments table, even if there is no match in the employees table. it also retrieves all rows in the employees table, even if there is no match in the departments table.)

cross joins

cartesian products :

a cartesian product is formed when : (a join condition is omitted/a join condition is invalid/all rows in the first table are joined to all rows in the second table)

to avoid a cartesian product , always include a valid join condition.

note : when a join condition is invalid or omitted compiletely, the result is a cartesia product, in which all combinations of rows are displayed,all rows in the first table are joined to all rows in the second table. a cartesian product tends to generate a large number of rows ,and the result is rarely useful. you should always include a valid join condition unless you have a specific need to combine all rows from all tables.

generating a cartesiann product :

e.g. : select last_name,department_name from employees cross join departments;(produces a cartesian product of the employees and departments tables.)

advanced retrieval

using subqueries to solve queries

using the set operators

hierarchical retruval

single-row subqueries

syntax : select select_list from table where expr operator (select select_list from table);

the subquery (inner query) executes once before the main query (outer query).

the result of the subquery is used by the main query.

note : a subquery is a sellct statement that is embedded in a clause if another select statement. you can build powerful statements out of simple ones by using subqueries they can be very useful when you need to select rows from a table with a condition that depends on the data in the table itself. you can place the subquery in a number of sql clauses, including the following : (where clause,having clause,from clause).operator includes a comparison condition such as >, =, or in.(comparison conditions fall into two classes : single-row operators(>, =, >=, <, <>, <=) and multiple-row operators (in,all,any).)

e.g. : select last_name from employees where salary > (select salary from employees where last_name = ‘Abel’);(the inner query determines the salary of employee Abel. the outer query takes the result of the inner query and uses this result to display all the employees who earn more than this amount.)

guidelines for using subqueries :

enclose subqueries in parentheses.

place subqueries on the right side of the comparison condition.

the order by clause in the subquery is not needed unless you are performing top-N analysis.

use single-row operators with single-row subqueries, and use multile-row operators with multiple-row subqueries.

e.g. : select last_name,job_id from employees where job_id = (select job_id from employees where employee_id = 141);(displays the employees whose job id is the same as that of employee 141.)

e.g. : select last_name,job_id,salary from employees where job_id = (select job_id from employees where employee_id = 141) and salary > (select salary from employees where employee_id = 143);(displays employees whose job id is the same as that of employee 141 and whose salary is greater than that of employee143.)

note : the outer and inner queries can get data from different tables.

using group functions in a subquery :

e.g. : select last_name,job_id,salary from employees where salary = (select min(salary) from employees);(displays the employee last name,job id and salary of all employees whose salary is equal to the minimum salary. the min group function returns a single value (2500) to the outer query.)

the having clause with subqueries :

e.g. : select department_id,min(salary) from employees group by department_id having min(salary) > (sellct min(salary) from employees where department_id = 50);(displays all the departments that have a minimum salary greater than that of department 50.)

e.g. : select job_id,avg(salary) from employees group by job_id having avg(salary) = (select min(avg(salary)) from employees group by job_id);(find the job with the lowest average salary.)

multiple-row subqueries

e.g. : select last_name,salary,department_id from employees where salary in (select min(salary) from employees group by department_id);(find the employees who earn the same salary as the minimum salary for each department.)

using the any operator in multiple-row subqueries :

e.g. : select employee_id,last_name,job_id,salary from employees where salady < any (select salary from employees where job_id = ‘IT_PROG’) and job_id <> ‘IT_PROG’;(displays employees who are not it programmers and whose salary is less than that of any it programmer.)

note : the any operator (and its synonym, the some operator) compares a value to each value returned by a subquery.

using the all operator in multiple-row subqueries :

e.g. : select employee_id,last_name,job_id,salary from employees where salary < all (select salary from employees where job_id = ‘IT_PROG’) and job_id <> ‘IT_PROG’;(displays employees whose salary is less than the salary of all employees with a job id of it_prog and whose job is not it_prog

null values in a subquery :

e.g. : select emp.last_name from employs emp where emp.employee_id not in (select mgr.manager_id from employees mgr);(it attempts to display all the employees who do not have any subordinates. logically, this statement should have returned 12 rows. however, the sql statement does not return any rows. one of the values returned by the inner query is a null value, and hence the entire query returns no rows.the reason is that all conditions that compare a null value result in a null. so whenever null values are likely to be part of the results set of a subquery, do not use the not in operator. the not in operator is equivalent to <> all.)

e.g. : select emp.last_name from employees emp where emp.employee_id in (select mgr.manager_id from employees mgr);(to display the employees who have subordinates.)

e.g. : select last_name from employees where employee_id not in (select manager_id from employees where manager_id is not null);(alternatively, a where clause can be included in the subquery to display all employees who do not have any subordinates.)

using the set operators

union operators

intersect operator

minus operator

the set operators combine the results of two or more component queries into one result. queries containing set operators are called compound queries.

union (all distinct rows selected by either query)

e.g. : select employee_id,job_id from employees union select employee_id,job_id from job_history;(display the current and previous job details of all employees.display each employee only once.)

e.g. : select employee_id,job_id,department_id from employees union select employee_id,job_id,department_id from job_history;

union all (all rows selected by either query, including all duplicates.)

e.g. : select employee_id,job_id,department_id from employees union all select employee_id,job_id,department_id from job_history order by employee_id:(display the current and previous departments of all employees.)

intersect (all distinct rows selected by both queries)

e.g. : select wmployee_id,job_id from employees intersect select employee_id,job_id from job_history;(display the employee ids and job ids of those employees who currently have a job title that is the same as their job title when they were initially hired (that is , they changer jobs but have now gone back to doing theyr original job.)

e.g. : select employee_id,job_id,department_id from employees intersect select employee_id,job_id,departmetn_id from job_history;

minus (all distinct rows that are selected by the first select statement and not selected in the second sellct statement.)

note : all of the columns in the where clause must be in the select clause for the minus operator to work.

e.g. : select employee_id,job_id from employees minus select employee_id,job_id from job_history;(display the employee ids of those employees who have not changed their jobs even once.)

e.g. : select employee_id,department_id from employees where (employee_id,department_id) in (select employee_id,department_id from employees union select employee_id,department_id from job_history);

guidelines :

the expression in the select lists must match in number and data types.

parentheses can be used to alter the sequence of execution.

the order by clause can appear only at the very end of the statement and will accept the column name,aliases form the first select statement,or the positional notation.

duplicate rows are automatically eliminated except in union all.

column names from the first query appear in the result.

the output is sorted in ascending order by default except in union all.

matching the select statements :

e.g. : select department_id,to_number(null) location,hire_date from employees union select department_id,location_id,to_date(null) from departments;

e.g. : select employee_id,job_id,salary from employees union select employee_id,job_id,0 from job_history;

produce an  english sentence using two union operators.

e.g. : select ‘sing’ as “My dream”, 3 a_dummy from dual union select ‘I’ ‘d like to teach’, 1 a_dummy from dual union select ‘the world to’,2 a_dummy from dual order by a_dummy;

hierarchical retrieval

syntax :

select [level],column,expr… from table [where condition(s)] [connect by prior comdition(s)];

where condition : expr comparison_operator expr

note : hierarchical queries can be identified by the presence of the connect by and start with clauses.

start with : specifies the root rows of the hierarchy (where to start). this clause is required for a true hierarchical query.

connect by : specifies the columns in which the relationship between parent and child prior rows exist.. this clauses is required for a hierarchical query.

start with column1 = value

connect by prior column1 = column2

direction : from the bottom up :

e.g. : select employee_id,last_name,job_id,manager_id from employees start with employee_id = 101 connect by prior manager_id = employee_id;(displays a list of managers starting with the employees whose employee id is 101.)

direction : from the top down :

e.g. : select last_name || NVL2(PRIOR last_name, ‘reports to ‘ || PRIOR last_name, ‘ is the boss’) “Walk Top Down” from employees start with employee_id = 100 connect by prior employee_id = manager_id;(display the names of the employees and their manager. use employee king as the starting point. print only one column.)

e.g. : select LPAD(last_name, LENGTH(last_name) + 9LEVEL*2) -2, ‘_’) as org_chart from employees start with last_name = ‘king’ connect by prior employee_id = manager_id;(create a report displaying company management levels,beginning with the highest level and indenting cache of the following levels.

pruning branches :

use the where clause to climinate a nod.

e.g. : where last_name != ‘Higgins’

use the connect by clause to eliminate a branch.

e.g. : connect by prior employee_id = manager_id and last_name != ‘Higgins’

e.g. : select department_id,employee_id,last_name,job_id,salary form employees where last_name != ‘Higgins’ start with manager_id is null connect by prior employee_id = manager_id;(starting at the root,walk from the top down, and eliminate employee Higgins in the result, but process the child rows.

select department_id,employee_id,last_name,job_id,salary from employees start with manager_id is null connect by prior employee_id = manager_id and last_name != ‘Higgins’;(starting at the root, walk from the top down, and eliminate employee Higgins and all child rows.)

manipulating data

describe each data manipulation language(DML) statement

insert rows into a table

update rows into a table

delete rows in a table

control transactions

insert statement

update statement and default

delete statement and truncate

merge statement

transactions control

insert statement

data manipulation language

a DML statement is executed when you( add new rows in a table / modify existing rows in a table / remove existing rows from a table)

a transaction consists of a collection of DML statements that form a logical unit of work.

add new rows to a table by using the insert statement :

syntax : insert into table [(column, [column … ])] values (value [,value … ]);

note : in the syntax, table is the name of the table, column is the name of the column in the table to populate, value is the corresponding value for the column. this statement with the values clause adds only one row at a time to a table.

inserting new rows

insert a new row containing values for each column.

list values in the default order of the columns in the table.

optionally, list the columns in the insert clause.

e.g. : insert into departments(department_id,department_name,manager_id,location_id) values (70,’Public Relations’,100,1700);

enclose character and data values in single quotation marks.

insert rows with null values

implicit method : omit the column from the column list.

e.g. : insert into departments(department_id,department_name  ) values (30,’Purchasing’);

explicit method : specify the null keyword in the values clause.

e.g. : insert into departments values(100,’Finance’,null,null);

inserting special values

the sysdate function records the current date and time.

e.g. : insert into employees (employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,dapartment_id) values (113, ’Louis’, ’Poop’ ‘LPOPP’, ’515.124.4567’, sysdate, ‘AC_ACCOUNT’, 6900, NULL, 205, 100);

confirming additions to the table

e.g. : select employee_id,last_name,job_id,hire_date,commission_pct from employees where employee_id = 113;

the user function records the current username.

inserting specific date values

add a new employee

e.g. : insert into employee values (114, ‘Den’, ‘Raphealy’, ‘DRAPHEAL’, ’515.127.4561’, to_date(‘FEB 3,1999’ ‘MON DD, YYYY’), ‘AC_ACCOUNT’, 11000, NULL, 100, 30);

create a script

use & substitution in a sql statement to prompt for values.

& is a placeholder for the variable value.

e.g. : insert into departments (department_id, department_name, location_id) values (&department_id,’&department_name’ ,&location);

note : you can save commands with substitution variables to a file and execute the commands in the file. run the script file and you are prompted for input for each of the & substitution variables. after entering a value for the substitution variable, click the continue button. the values that you input are then substituted into the statement.this enables you to run the same script file over and over but supply a different set of values each time you run it.

copying rows from another table

write your insert statement with a subquery :

syntax : insert into table [column,(column)] subquery;

e.g. : insert into sales_reps(id,name,salary,commission_pct) select employee_id,last_name,salary,commission_pct from employees where job_id like ‘%REP%’;

note : do not use the values clause. match the number of columns in the insert clause to those in the subquery.

e.g. : insert into copy_emp select * from employees;

update statement and default

modifying existing rows with the update statement:

update table set column = value[column = value,…] [where condition]

eg : update employees set department_id = 70 where employee_id = 113;(specific row or rows are modified if you specify the where clause.)

e.g. : update copy_emp set department_id = 100;(all rows in the table are modified if you omit the where clause.)

e.g. : update employees set job_id = (select job_id from employees where employee_id = 205), salary = (select salary from employees where employee_id = 205) where employee_id = 114;(update employee 114’s job and salary to match that of employee 205.)

note : you can update multiple columns in the set clause of an update statement by writing multiple subqueries.

syntax: update table set column = (select column from table where condition) [,column = (select column from table where condition)] [where condition];

updating rows based on another table

use subqueries in update statement to update rows in a table based on values from another table:

e.g. : update copy_emp set department_id = (select department_id from employees where employee_id = 100) where job_id = (select job_id) from employees where employee_id = 200);(updates the copy_emp table based on the values from the employees table.It changes the department number of all employees with employee 200’s job_id to employee 100’s current department number.)

with the explicit default feature, you can use the default keyword as a column value where the column default is desired.

this allows the user to control where and when the default value should be applied to data.

explicit defaults can be used in insert and update statements.

default with insert :

e.g. : insert into deptm3 (department_id,department_name,manager_id) values(300,’Enginerring”,default);

e.g. : update deptm3 set manager_id = default where department_id = 10;

specify default to set the column to the value previously specified as the default value for the column. If no default value for the corresponding column has been specified, the Oracle server sets the column to null.

removing a row from a table

syntax: delete [from] table [where condition];

note : in the syntax, table is the table name. condition identifies the rows to be deleted and is composed of column names,expressions,constants,subqueries,and comparison operators.

specific rows are deleted if you specify the where clause :

e.g. : delete from departments where department_name = ‘Finance’;(deletes the Finance department from the departments table.)

all rows in the table are deleted if you omit the where cause :

e.g. : delete from copy_emp;(deletes all rows from the copy_emp table.)

note : you can confirm the delete operation by displaying the deleted rows using the select statement.

e.g. : select * from departments where department_name = ‘Finance’;

e.g. : delete from employees where employee_id = 114;

e.g. : delete from departments where department_id in (30,40);

deleting rows based on another table

use subqueries in delete statements to remove rows from a table based on values from another table:

e.g. : delete from employees where department_id = (select department_id from departments where department_name like ‘%Public%’);(deletes all the employees who are in a department where the department name contains the string publis. the subquery searches the departments table to find the department number based on the department name containing the string public. the subquery then feeds the department number to the main query, which deletes rows of data from the employees table based on this department number.)

truncate statement(remove all rows from a table,leaving the table empty and the table structure intact.)

is a data definition language(DDL) statement rather than a DML statement cannot easily be undone.

syntax : truncate table table_name;

e.g. : truncate table copy_emp;

merge statement

provides the ability to conditionally update or insert data into a table.

run an update if the row already exists and an insert if the row does not exist.(avoid separate updates/simplifies use and improve performance/very useful in datawarehouing applications.)

the merge statement will allow to insert or update into a table on a condition.

syntax : merge into table_name table_alias using (table | view | sub_query) alias on (join condition) when matched then update set col1 = col1_val, col2 = col2_val when not matchen then insert (column_list) values (column_values);

insert or update rows in the copy_emp table to match the employee table :

e.g. : merge into copy_emp c using employees e on (c.employee_id = e.employee_id) when matched then update set c.first_name = e.first_name, c.last_name = e.last_name,…,c.department_id = e.department_id when not matched then insert values(e.employee_id,e.first_name,e.last_name,e.email,e.phone_number,e.hire_date,e.job_id,e.salary,e.commission_pct,e.manager_id,e.department_id);

control transactions

commit and rollback

database transactions(a database transaction consists of one of the following : DML statements that constitute one consistent change to the data./one DDL statement./one data control language(DCL) statement.)

transaction type

data manipulation language (DML)(consists of any number of DML statements that the oracle server treats as a single entity or a logical unit of work.

data definition language (DDL) (consists of only one DDL statement.

data control language (DCL) (consists of only one DCL statement.

database transactions :

begin when the first DML SQL statement is excited.

end with one of the following events : (a commit or rollback statement is issued./a DDL or DCL statement excites (automatic commit)./the user exits iSQL*PLUS./the system crashes.)

after one transaction ends,the next executable SQL statement automatically starts the next transaction.

a DDL statement or a DCL statement is automatically committed and therefore implicitly ends a transaction.

advantages of commit and rollback statements ,you can :(ensure data consistency, preview data changes before making changes permanent,group logically related operations)

commit (ends the current transaction by making all pending data changes permanent.)

savepoint name (marks a savepoint within the current transaction.)

rollback (ends the current transaction by discarding all pending data changes.)

rollback to savepoint_name (rolls back the current transaction to the specified save point, thereby discarding any changes and or save points that were created after the save point to which you are rolling back if you omit the to savepoint clause,the rollback statement rolls back the entire transaction. because save points are logical,there is no way to list the save points that you have created.

note : save point is not ANSI standard SQL.

rolling back changes to a marker

create a marker in a current transaction by using the save point statement.

roll back to that marker by using the rollback to save point statement.

update … save point update_done;

insert rollback to update_done;

implicit transaction processing

an automatic commit occurs under the following circumstances : (DDL or DCL statement is issued / normal exit from iSQL*PLUS, without explicitly issuing commit or rollback statements.）

an automatic rollback occurs under an abnormal termination of iSQL*PLUS or a system failed.

automatic commit (DDL statement or DCL statement is issued. iSQL*PLUS exited normally,without explicitly issuing commit or rollback commands.)

automatic rollback (abnormal termination of iSQL*PLUS or system failure.)

note : a third command is available in iSQL*PLUS. the auto commit command can be toqqled on or off.if set on,each individual DML statement is committed as soon as it is excited.you cannot roll back the changes. if set off, the commit statement can still be issued explicitly. also, the commit statement is issued when a DDL statement is issued or when you exit iSQL*PLUS.

system failures

when a transaction is interrupted by a system failure, the entire transaction is automatically rolled back. this prevents the error from causing unwanted changes to the data and returns the tables to their state at the time of the last commit. in this way, the oracle server protects the integrity of the tables.

from iSQL*PLUS, a normal exit from the session is accomplished by clicking the exit button. with SQL*PLUS, a normal exit is accomplished by typing the command exit at the prompt. closing the window is interpreted as an abnormal exit.

commit and rollback

state of the data before commit or rollback : (the previous state of the data can be recovered.

the current user can review the results of the DML operations by using the select statement.

other users cannot view the results of the DML statements by the current user.

the affected rows are locked; other users cannot change the data in the affected rows.)

note : every data change made during the transaction is temporary until the transaction is committed. the state of the data before commit or rollback statements are issued can be described as follows:(data manipulation operations primaritly affect the database buffer, therefore, the previous state of the data can be recovered. the current user can review the results of the data manipulation operations by querying the tables. other users cannot view the results of the data manipulations made by the current user.the oracle server institutes read consistency to ensure that cache sees data as it existed at the last commit.

the affected rows are locked; other users cannot change the data in the affected rows.)

state of the data after commit : (the previous state of the data can be recovered. the current user can review the results of the DML operations by using the select statement. other users cannot view the results of the DML statements by the current user. the affected rows are locked, other users cannot change the data in the affected rows.)

note : make all pending changes permanent by using the commit statement. here is what happens after a commit statement : (data changes are written to the database. the previous state of the data is no longer available with normal SQL queries. all users can view the results of the transaction. the locks on the affected rows are released; the rows are now available for othe users to perform new data changes. all save points are erased.)

e.g. : delete from employees where employee_id = 9999;(delete a row from the employees table)

e.g. : insert into departments values (290, ‘Corporate Tax’, NULL, 1700);(insert a new row into the departments table.)

e.g. : commit;(make the changes permanent)

e.g. : delete from departments where department_id in (290,300);

e.g. : update employees set department_id = 80 where employee_id = 206;

e.g. : commit;(remove departments 290 and 300 in the departments table, and update a row in the copy_emp table.make the data change permanent.)

discard all pending changes by using the rollback statement : (data changes are undone. previous state of the data is restored. locks on the affected rows are released.)

e.g. : delete from copy_emp;

e.g. : rollback;

e.g. : while attemptingg to remove a record from the test table you can accidentally empty the table. you can correct the mistake, reissue the proper statement, and make the data change permanent.

e.g. : delete from test;

e.g. : rollback;

e.g. : delete from test where id = 100;

e.g. : select * from test where id = 100;

eg : commit;

statement-level rollback

if a single DML statement fails during execution, only that statement is rolled back.

the oracle server implements an implicit save point.

all other changes are retained.

the user should terminate transactions explicitly by executing a commit or rollback statement.

read consistency

read consistency guarantees a consistent view of the data at all times.

changes made by one use do not conflict with changes made by another user.

read consistency ensures that on the same data : (readers do not wait for writers/writers do not wait for readers.)

creating schema objects

categorise the main database objects

review the table structure

create simple and complex views

using DDL statements to create and manage tables

objects and data types

objects :

table : stores data,basic unit of storage; composed of rows

view : logically represents subsets of data from one or more tables.

sequence : generates numeric values.

index : improves the performance of some queries.

synonym : gives alternative names to objects.

datatypes :

varchar2(size) : variable-length character data.

char(size) : fixed-length character data.

number(p,s) : variable-length numeric data.(p stands for precision and s stands for scale. the precision is the total number of decimal digits, and the scale is the number of digits to the right of the decimal point. the precision can range from 1 to 38, and the scale can range from 84 to 127.)

data : date and time values.(to the nearest second between January 1,4712 B.C.,and December 31, 9999 A.D.)

long : variable-length character data(up to 2 GB).

clog : character data (up to 4 GB).

raw and long row : raw binary data.

blob : binary data (up to 4 GB)

bfile : binary data stored in an external file(up to 4 GB)

rowid : a base-64 number system representing the unique address of a row in its table.

note : (a long column is not copied when a table is created using a subquery. a long column cannot be included in a group by or an order by clause. only one long column can be used per table. no constraints can be defined on a long column. you might want to use a clog column rather than a long column.)

timestamp : variable-length character data

interval year to month : stored as an interval of years and months

interval day to second : stored as an interval of days,hours, minutes, and seconds.

e.g. : timestamp[(fractional_seconds_predision)]

e.g. : timestamp[(fractional_seconds_precision)] with time zone

e.g. : timestamp[(fractional_seconds_precision)] with local time zone

note : the timestamp data type is an extension of the data type. It stores the year.month,and day of the date type plus hour,minute,and second values. this data type is used for storing precise time values.

the fractional_seconds_precision optionally specifies the number of digits in the fractional part of the second date time field and can be a number in the range 0 to 9. the default is 6.

e.g. : create table new_employees(employee_id number, first_name varchar2(15),last_name varchar2(15),.. start_date timestamp(7),… );(a table is created named new_employees, with a column start_date that has a data type of timestamp.)

date time data types

the interval year to month data type stores a period of time using the year and month date time fields:

e.g. : interval year [(year_precision)] to month

the interval day to second data type stores a period of time in terms of days,hours,minutes,and seconds:

e.g. : interval day [(day_precision)] to second [(fractional_seconds_precision)]

managing tables

naming rules : (table names and column names : (must begin with a letter. must be 1-30 characters long. must contain only A-Z, a-z, 0-9, _, $, and #. must not duplicate the name of another object owned by the same user. must not be an oracle server reserved word.))

create table statement

you must have create table privilege and a storage area.

e.g. : create table [schema.] table (column datatype [default expr] [,… ]);

you specify table name and column name,column data type and column size.

note : schema is the same as the owner’s name. default expr specifies a default if a value is omitted in the insert statement.

referencing another user’s tables

tables belonging to other users are not in the user’s schema.

you should use the owner’s name as a prefix to those tables.

e.g. : select * from userB.employees;(for userA)

e.g. : select * from userA.employees;(for userB)

schema is a collection of objects. schema objects are the logical structures that directly refer to the data in a database. schema objects include tables, views, synonyms, sequences, stored procedures, index, clusters,and database links.

default option

specify a default value for a column during a insert.

e.g. : … hire_date date default sysdate,…

literal values, expressions, or SQL functions are legal values.

another column’s name or a pseudocolumn are illegal values.

the default data type must match the column data type.

e.g. : create table hire_dates(id number(8), hire_date date default sysdate);

create the table.

e.g. : create table dept (dept no number(2), dame varchar2(14), loc varchar2(13), create_date date default sysdate);

confirm table creation.

eg : describe dept

note : because creating a table is a DDL statement, an automatic commit takes place when this statement is executed.

alter table statement

use the alter table statement to : (add a new column./modify an existing column./define a default value for the new column./drop a column.)

dropping a table

all table and structure in the table are deleted.

any pending transactions are committed.

all indexes are dropped.

all constraints are dropped.

you cannot roll back the drop table statement.

e.g. : drop table dept8;

note : the drop table statement, once executed, is irreversible(不可逆的).as with all DDL statements, drop table is committed automatically.

advanced creation

creating a table by using a subquery

syntax. : create table table [(column, column … )] as subquery;

match the number of specified columns to the number of subquery columns.

define columns with column names and default values.

e.g. : create table dept80 as select employee_id, last_name, salary*12 ANNSAL, hire_date from employees where department_id = 80;(creates a table named dept80,which contains details of all the employees working in department 80.)

note : be sure to provide a column alias when selecting an expression.

constraints

including constraints

constraints enforce rules at the table level.

constraints prevent the deletion of a table if there are dependencies.

the following constraints types are valid :

not null : specifies that the column cannot contain a null value.

unique : specifies a column or combination of columns whose values must be unique for all rows in the table.

primary key : uniquely identifies each row of the table.

foreign key : establishes and enforces a foreign key relationship between the column and a column of the referenced table.

check : specifies a conditio that must be true.

constraints guidelines

you can name a constraint or the oracle server generates a name by using the SYS_Cn format.

create a constraint at either of the following times : (at the same time as the table is created./ after the table has been created.)

define a constraint at the column or table level.

view a constraint in the data dictionary.

defining constraints

syntax :

create table [schema.]table (column datatype [default expr] [column_constraint], … [table_constraint] [,…]);

column-level constraint :

syntax : column [constraint constraint_name] constraint_type,

e.g. : create table employees(employee_id number(6) constraint emp_emp_id_pk primary key, forst_name varchar2(20), … );

table-level constraint :

syntax : column, … [constraint constraint_name] constriant_type (column,…),

e.g. : create table employees(employee_id number(6),first_name varchar2(20), … job_id varchar2(20) not null, constraint emp_emp_id_pk primary key (employee_id));

note : not null constraints must be defined at the column level. constraints that apply to more than one column must be defined at the table level.

not null

not null constraint(ensures that null values are not permitted for the column :

unique

unique constraint(requires that every value in a column or set of columns(key) be unique. that is, no two rows of a table can have duplicate values in a specified column or set of columns.

e.g. : create table employees (employee_id number(6), last)name varchar2(25) not null, email varchar2(25), salary number(8,2), commission_pct number(2,2), hire_date date not null, … constraint emp_email_uk unique(email);

primary key

primary key constraint(unique and not null)

foreign key

foreign key constraint

note : a foreign key value must match an existing value in the parent table or be null.

foreign keys are based on data values and are purely logical, rather than physical,pointers.

e.g. : create table employees(employee_id number(6),last_name varchar2(25) not null, email varchar2(25), salary number(8,2), commission_pct number(2,2), hire_date date not null, … department_id number(4), constraint emp_dept_fk foreign key(department_id) references departments(department_id), constraint emp_email_uk unique)email));

foreign key constraint : keywords

foreign key : defines the column in the child table at the table_constraint level.

references : identifies the table and column in the parent table.

on delete cascade : deletes the dependent rows in the child table when a row in the parent table is deleted.

on delete set null : converts dependent foreign key values to null.

check

check constraint

defines a condition that each row must satisfy.

the following expression are not allowed : (references to curial,nextval,level,and rownum pseudocolumns./calls to sysdate, hid,user,and userenv functions./queries that refer to other values in other rows.)

e.g. : … , salary number(2) constraint emp_salary_min check (salary > 0), …

e.g. : create table employees (… salary number(8,2) constraint emp_salary_min check (salary > 0), …

how to use

e.g. : create table employees(employee_id number(6) constraint emp_employee_id primary key, first_name varchar2(20),last_name varchar2(25) constraint emp_last_name_nn not null, email varchar2(25) constraint emp_email_nn not null constraint emp_email_uk unique, phone_number varchar2(20), hire_date date constraint emp_hire_date_nn not null, job_id constraint emp_job_nn not null, salary number(8,2) constraint emp_salary_ck check (salary > 0), commission_pct number(2,2), manager_id number(6), department_id number(4) constraint emp_dept_fk references departments(department_id));

violating constraints

e.g. : update employees set department_id = 55 where department_id = 110;(department 545 does not exist in the parent table departments , so you receive the parent key violation ora-02291.)

you cannot delete a row that contains a primary key that is used as a foreign key in another table.

e.g. : delete from departments where department_id = 60;(tries to delete department 60 from the departments table, but it results in an error because that department number is used as a foreign key in the employees table. If the parent record that you attempt to delete has child records, then you receive the child record found violation ora-02292.)

creating other schema objects

views

sequences

indexes

synonyms

managing objects with data dictionary views.

views

a view is a logical table based on a table or another view. a view contains no data of it’s own but is like a window through which data from tables can be viewed or changed. the tables on which a view is based are called base tables. the view is stored as a select statement in the data dictionary.

advantages of views : (to restrict data access/to make complex queries easy/to provide data independence/to present different views of the same data.)

a simple view is one that : (derives data form only one table./contains no functions or groups of data. /can perform DML operations through the view.)

a complex view is one that : (derives data from many tables./contains functions or groups of data./does not always allow DML operations through the view.)

creating a view

syntax : create [or replace] [force | noforce] view view [(alias [,alias]…)] as subquery [with check option [constraint constraint] [with read only [constraint constraint]];

note : force means create the view regardless of whether or not the base tables exists. no force means creates the view only if the base table exist (this is the default.). with check option specifies that only those rows that are accessible to the view can be inserted or updated. with read only ensures that no DML operations can be performed on this view.

e.g. : create view empvu80 as select employee_id,last_name,salary from employees where department_id = 80;(crewte empvu80 view, which contains details of employees in department 80.)

e.g. : describe empvu80(describe the structure of the view.)

create a view by using column aliases in the subquery :

e.g. : create view salvu50 as select employee_id ID_NUMBER, last_name NAME, salary*12 ANN_SALARY from employees where department_id = 50;

e.g. : create or replace view salvu50(ID_NUMBER, NAME, ANN_SALARY) as select employee_id, last_name, salary*12 from employees where department_id = 50;

retrieving data form a view

e.g. : select * from salvu50;

modifying a view

e.g. : create or replace view empvu80 (id_number, name, sal, department_id) as select employee_id, first_name || ‘ ‘ || last_name, salary, departmetn_id from employees where department_id = 80;(modify the empvu80 view by using a create or replace view clause. add an alias for each column name .)

e.g. : create or replace viewdept_sum_vu (name, minsal, maxsal, avgsal) as select d.deaprtment_name, min(e.salary), max(e.salary), avg(e.salary) from employees e join departments d on (e.department_id = d.department_id) group by d.department_name;(create a complex view that contains group functions to display values from two tables.)

rules for performing DML operations on a view : (you can usually perform DML operations on simple views. you cannot remove a row if the view contains the following : (group functions / a group by clause / the distinct keyword / the pseudo column row num key word.) you cannot modify data in a view if it contains : (group functions / a group by clause/ the distinct keyword / the pseudo column rownum keyword/columns defined by expressions) you cannot add data through a view if the view includes : (group functions/a group by clause/the distinct keyword/the pseudo column rownum keyword/columns defined by expressions/not null columns in the base tables that are not selected by the view))

using the with check option clause

e.g. : cease or replace view empvu20 as select * from employees where department_id = 20 with check option constraint empvu20_ck;(ensure that DML operation performed on the view stay in the domain of the view by using the with check option clause.)

any attempt to change the department number for any row in the view fails because it violates the with check option constraint.

denying DML operations

you can ensure that no DML operations occur by adding the with read only option to your view definition.

any attempt to perform a DML operation on any row in the view results in anORacle server error.

e.g. : create or replace view empvu10 (employee_number, employee_name, job_title ) as select employee_id, last_name, job_id from employees where department_id = 1- with read only;

removing a view

syntax : drop view view;

e.g. : drop view empvu80;

note dropping views has no effect on the tables on which the view was based.

sequences

a sequence : (can automatically generate unique numbers/is a sharable object/can be used to create a primary key value/replaces application code/speeds up the efficiency of accessing sequence values when cached in memory.)

create sequence statement

syntax : create sequence sequence [increment by n] [start with n] [{maxvalue n | nomaxvalue}] [{minvalue n | nominvalue}] [{cycle | nocycle}] {{cache n | nocache}];

e.g. : create sequence dept_deptid_sep increment by 10 start with 120 maxvalue 9999 nocache nocycle;

nextval and currval pseudocolumns

nextval returns the next available sequence value. it returns a nuibue value every time it is referenced, even for different users.

curial obtains the current sequence value. nextval must be issueed for that sequence before curial contains a value.

using a sequence

e.g. : insert into departments)department_id, department_name, location_id) values (dept_deptid_seq.nextval, ’Support’, 2500);(insert a new department named ‘support’ in location ID 2500.)

e.g. : select dept_dept_id_seq.currval from dual;(view the current value for the dept_deptid_seq sequence.)

caching sequence values

caching sequence values in memory gives faster access to those values.

gaps in sequence values can occur when : (a rollback occurs/the system crashes/a sequence is used in another table)

modifying a sequence

e.g. : alter sequence dept_deptid_seq increment by 20 maxvalue 999999 nocache nocycle;(change the increment value, maximum value, minimum value, cycle option, or cache option.)

guidelines for modifying a sequence :

you must be the owner or have the alter privilege for the sequence.

only future sequence numbers are affected.

the sequence must be dropped and re-created to restart the sequence at a different number.

some validation is performed.

to remove a sequence, use the drop statement:

e.g. : drop sequence dept_deptid_seq;

indexed

an index : (is a schema object. /can be used by the oracle server to speed up the retrieval of rows by using a pointer/can reduce disk I/O by using a rapid path access method to locate data quickly/is indepentedt of the table that it indexed/is used and maintained automatically by the oracle server)

automatically(unique) : a unique index is created automatically when you define a primary key or unique constraint in a table definition.

manually(nonunique) :users can create non unique indexed on columns to speed up access to the rows.

creating an index

syntax : create index index on table (column, […column]);(create an index on one or more columns.)

e.g. : create index emp_last_name_idx on employees(last_name);(improve the speed of query access to the last_name column in the employees table.)

index creation guidelines

create an index when : (a column contains a wide range of values/a column contains a large number of null values/one or more columns are frequently user together in a where clause or a join condition/the table is large and most queries are expectted to retrieve less than 2% to 4% of the rows in the table.)

removint an index

syntax : drop index index;(remove an index from the data dictionary by using the drop index command.)

e.g. : drop index emp_last)name_idx;(remove the upper_last_name_idx index from the data dictionary.)

note : you cannot modify indexes. to change an index, you must drop it and then re-create it.to drop an index, you must be the owner of the index or have the drop any index privilege. if you drop a table, indexes and constraints are automatically dropped but views and sequences remain.

synonyms

simplify access to objects by creation a synonym(another name for an object). with synonyms, you can : (create an easier reference to a table that is owned by another user/shorten lengthy object names.)

syntax : create [public] synonym synonym for object;

creating and removing synonyms

e.g. : create synonym d_sum for dept_sum_vu;(create a shortened name for the dept_sum_vu view.)

e.g. : drop synonym d_sum;(drop a synonym.)

managing objects with data dictionary views

view naming convention

user :(user’s view(what is in your schema;what you own)

all :(expanded user’s view(what you can access)

dab : (database administrator’s view(what is in everyone’s schemas)

v$ : (performance-related data)

how to use the dictionary views

start with dictionary.it contains the names and descriptions of the dictionary tables and views.

eg : describe dictionary

e.g. : select * from dictionary where table_name = ‘USER_OBJECTS’;(enclose character and date values in single quotation marks.)

note : the names in the data dictionary are uppercase.

user_objects : (query user_objects to see all of the objects that are owned by you/ is a useful way to obtain a listing of all object names and types in your schema, plus the following info. : (date created/date of last modification/status(valid or invalid)))

all_objects views : (query all_objects to see all objects to which you have access.)

e.g. : select object_name, object_type, created, status from user_objects order by object_type;(shows the names, types, dates of creation, and status of all objects that are owned by this user.)

note : for a simplified query and output, you can query the cat view, this view contains only two columns (table_name,and table_type.)it provides the names of all your index,table,cluster,view,synonym,sequence,or undefined objects.

e.g. : describe user_tables

e.g. : select table_name from user_tables;

note : the tabs view is a synonym of the user_tables view. you can query it to see a listing of tables that you own. e.g. : select table_name from tabs;)

e.g. : describe user_tab_columns

e.g. : select column_name, datat_ype, data_length, data_precision, data_scale, nullable from user_tav_columns where table_name = ‘EMPLOYEES’;

constraint info.

user_constraints describe the constraint definitions on your tables.

user_cons_columns describe columns that are owned by you and that are specified in constraints.

e.g. : select constraint_name, constraint_type, search_condition, r_constraint_name, delete_rule, status from user_constraints where table_name = ‘EMPLOYEES’;(the user constraints view is queried to find the names, types, check conditions, name of the unique constraint that the foreign key references, deletion rule for a foreign keym and status for constraints on the employees table.

note : the constraint_type can be : (C : (check constraint on a table)/P : (primary key) U : (unique key) R : (referential integrity) V : (with check option, on a view) O : (with read_only, on a view).) the delete_rule can be : (cascade : if the parent record id deleted, the child records are deleted too. no action : a parent record can be deleted only if no child records exist.)  the status can be : (enabled : constraint is active. disabled : constraint is made not active.)

constraint info.

e.g. : describe user_cons_columns

e.g. : select constraint_name, column_name from user_cons_columns where table_name = ‘EMPLOYEES’;

view info.

e.g. : describe user_views

e.g. : select distinct view_name from user_views;

e.g. : select text from user_views where view_name = ‘EMP_DETAILS_VIEW’;

sequence info.

e.g. : describe user_sequences

e.g. : select sequence_name, min_value, max_value, increment_by, last_number from user_sequences;

synonym info.

e.g. : describe user_synonyms

e.g. : select * form user synonyms;

adding comments to a table

you can add comments to a table or column by using the comment statement

e.g.: comment on table employees IS ‘Employee Information’;

comments can be viewed through the data dictionary views : (ALL_COL_COMMENTS/USER_COL_COMMENTS/ALL_TAB_COMMENTS/USER_TAB_COMMENTS)

note : you can drop a comment from the database by setting it to empty string (‘ ‘)

e.g. : comment on table employees is ‘ ‘;

controlling user access

differentiate system privileges from object privileges

grant or revoke accesses to users

controlling user access

presentation

system provileges

managing users

object privileges

roles

with oracle server database security, you can do the following : (control database access/give accesses to specific objects in the database/confirm given and received privileges with the oracle data dictionary/create synonyms for database objects)

privileges

database security(system security/data security)

system privileges : gaining access to the database

more than 100 privileges are available

the database administrator has high-level system privileges for task such as : (creating new users/removing users/removing tables/backing up tables)

e.g. : create user (grantee can create other oracle users.)

e.g. : drop user (grantee can drop another user.)

e.g. : drop any table (grantee can drop a table in any schema.)

e.g. : backup any table (grantee can back up any table in any schema with the export utility.)

e.g. :select any table (grantee can query tables,view, or materialised views in any schema.)

e.g. : create any table (grantee can create tables in any schema.)

user system privileges

after a user is created, the dab can grant specific system privileges to that user.

syntax : grant privilege [,privilege …] to user [,user | role. public …];

an application developer, for example, may have the following system privileges : (create session/create table/create sequence/create view/create procedure)

create session : connect to the database

create table : create a sequence in the user’s schema

create sequence : create a sequence in the user’s schema

create view : create a view in the user’s schema

create procedure : create a stored procedure, function, or package in the user’s schema

note : in the syntax, public designates that every user is granted the privilege. current system privileges can be found in the session_privs dictionary view.

granting system privileges

the dab can grant specific system privileges to a user.

e.g. : grant create session, create table, create sequence, create view to scott;(user scott has been assigned the privileges to create sessions, tables, sequences, and views.)

confirming privileges granted

user_sys_privs : system privileges granted to the user

note : you can access the data dictionary to view the privileges that you have.

managing users

the dab creates users with the create user statement

syntax : create user user identified by password;

e.g. : create user hr identified by hr;

changing your password

the dab creates your user account and initialises your password.

you ca change you password by using the alter user statement.

e.g. : alter user hr identified by employ;

note : you must have the alter user privilege to change any other option.

object privileges : manipulating the content of the database objects

an object privilege is a privilege or right to perform a particular action on a specific table, view, sequence, or procedure. each object has a particular set of grantable privileges.

note : for table, there are 7 privileges : (alter, delete, index, insert, reference, select, update) for view, there are 4 privileges : (delete, insert, select, update). for sequence, there are 2 privileges : (alter, select). for procedure, you can only execute. a privilege granted on a synonym is converted to a privilege on the base table referenced by synonym.

object privileges vary from object to object.

an owner has all the privileges on the object.

an owner can give specific privileges on that owner’s object.

syntax : grant object_priv [(columns)] on object to {user | role | public} [with grant option];

note : if the grant includes with grant option, then the grantee can further grant the object privilege to other users. otherwise, the grantee can user the privilege but cannot grant it to other users.

granting object privileges

e.g. : grant select on employees to sue, rich;(grant query privileges on the employees table. grants user Sue and Rich the privilege to query your employees table.)

e.g. : grant update (department_name, location_id) on departments to scott, manager;(grant privileges to update specific columns to users and roles. grants update privileges on specific columns in the departments table to Scott and to the manager role.)

passing on your privileges

gives a user authority to pass along privileges

e.g. : grant select, insert on departments to scott with grant option;(gives user Scott access to your departments table with the privileges to query the table and add rows to the table, also grants the user Scott the right to give others these privileges.)

e.g. : grant select on alice.departments to public;(allows all users on the system to query data from alice’s departments table.)

confirming privileges granted

data dictionary view

user_tab_privs_made : object privileges granted on the user’s objects.

user_tab_privs_recd : object privileges granted to the user.

user_col_privs_made : objects privileges granted on the columns of the user’s objects

user_col_privs_recd : objects privileges granted to the user on specific columns.

revoking privileges

you use the revoke statement to revoke privileges granted to other users.

privileges granted to others through the with grant option clause are also revoked.

syntax : revoke {privilege [,privilege …] | all} on object from {user [,user …] | role | public} [cascade constraints];

note : in the syntax, cascade is required to remove any referential integrity constraints made to the constraints object by means of the references privilege. if a user were to leave the company and you revoke his privileges, you must re-grant any privileges that this user may have granted to other users. if you drop the user account without revoking privileges from it, then the system privileges granted by this user to other users are not affected by this action.

e.g. : revoke select, insert on department from scott;(as user Alice, revoke the select and insert privileges given to user Scott on the departments table.)

schemas : collection of objects such as tables,views,and sequences

role

a role is a named group of related privileges that can be granted to the user. this method makes it easier to revoke and maintain privileges.

a user can have access to several roles, and several users can be assigned the same role. roles are typically created for a database application.

syntax : create role role;

after the role is created, the dab can use the grant statement to assign the role to users as well as assign privileges to the role.

roles

creating the role

e.g. : create role manager;(creates a manager role.)

e.g. : grant create table, create view to manager;(enables managers to create tables and views.)

e.g. : grant manager to de_haan, kochhar;(grants De Haan and Kochaar the role of managers.)

confirming privileges granted

data dictionary view

role_sys_privs : system privileges granted to roles.

role_tab_privs : table privileges granted to roles.

user_role_privs : roles accessible by the user.

manage schema objects

drop columns and set column unused

add constraints

create indexes

create indexes using the create table statement

create function-based indexes

create and use external tables

altering a table

adding a column(using the add clause)

syntax : alter table table add (column datatype [default expr] [,column datatype]…);

e.g. : alter table dept80 add (job_id varchar2(9);(adds a column named job_id to the dept80 table.)

the new column becomes the last column.

modifying a column

you can change a column’s data type,size, and default value with the modify clause.

syntax : alter table table modify (column datatype [default expr] [,column datatype]…);

e.g. : alter table dept80 modify (last_name varhar2(30));

a change to the default value affects only subsequent insertion to the table.

dropping a column

use the drop column clause to drop columns you no longer need from the table.

syntax : alter table table drop column (column);

e.g. : alter table dept80 drop column job_id;

the set unused option

you use the set unused option to mark one or more columns as unused.

syntax : alter table table_name set unused (column_name);

syntax : alter table table_name set unused column column_name;

you use the drop unused solumns option to remove the columns that are marked as unused.

syntax : alter table table_name drop unused columns;

note : set unused information is stored in the user_unused-col_tabs dictionary view.

e.g. : alter table dept80 set unused(last_name);

e.g. : alter table dept80 drop unused columns;

managing constraints

adding a constraint

syntax : alter table table_name add [constraint constraint_name] type (column_name);

e.g. : alter table emp2 modify employee_id primary key;(modifies the emp2 table to add a primary key constraint on the employee_id column.)

e.g. : alter table emp2 add constraint emp_mgr_fk foreign key(manager_id) references emp2(employee_id);(creates a foreign key constraint on the emp2 table. the constraint ensures that a manager exists as a valid employee in the emp2 table.)

on delete cascade

delete child rows when a parent key is deleted.

e.g. : alter table emp2 add constraint emp_dt_fk foreign key (department_id_ references departments on delete cascade;

deferring constraints

constraints can have the following attributes : (deferrable or not deferrable/initially deferred or initially immediate.)

e.g. : alter table dept2 add constraint dept2_id_pk primary key (department_id) deferrable initially deferred ;(deferring constraint on creation)

e.g. : set constraint dept2_id_pk immediate;(change a specific constraint attribute)

e.g. : alter session set constraints = immediate;(changing all constraints fro a session)

note : you can defer checking constraints for validity until the end of the transaction. a constraint is deferred if the system checks that it is satisfied only on commit. if a deferred constraint is violated, then commit causes the transaction to roll back. if a constraint is immediate(not deferred), the it is checked at the end of each statement, if it is violated, the statement is rolled back immediately. if a constraint causes an action(for example, delete cascade), that action is always taken as part of the statement that caused it, whether the constraint is deferred or immediate. use the set constraints statement to specify, for a particular transaction, whether a deferrable constraint is checked following each DML statement or when the transaction is committed. in order to create deferrable constraints, you must create a non uniqueindex for that constraint. you can define constraint as either deferrable or not deferrable, and either initially deferred or initially immediate. these attributes can be different for each constraint.

dropping a constraint

e.g. : alter table emp2 drop constraint emp_mgr_fk;(remove the manager constraint from the emp2 table.)

e.g. : alter table dept2 drop primary kay cascade;(remove the primary key constraint on the dept2 table and drop the associated foreign key constraint on the emp2.department_id column.)

note : to drop a constraint, you can identify the constraint name from the user_constraints and user_cons_columns data dictionary views. the use the alter table statement with the drop clause. the cascade option of drop clause causes any dependent constraints also to be dropped.

activating a constraint

disabling constraints

execute the disable clause of the alter table statement to deactivate an integrity constraint.

apply the cascade option to disable dependent integrity constraints.

eg : alter table emp2 disable constraint emp_de_fk;

note : you can use the disable clause in both the crdate table statement and the alter table statement. the cascade clause disables dependent integrity constraints. disabling a unique or primary key constraint removes the unique index.

enabling constraints

activate an integrity constraint currently disabled in the table definition by using the enable clause.

syntax : alter table table_name enable constraint constraint_name;

eg : alter table emp2 enable constraint emp_dt_fk;

a unique index is automatically created if you enable a unique key or primary key constraint.

note : enabling a primary key constraint that was disabled with the cascade option does not enable any foreign keys that are dependent on the primary key. to enable a unique or primary key constraint, you must have the privileges necessary to create an index on the table.

cascading constraints

the cascade constraint constraints clause is used along with the drop column clause.

the cascade constraints clause drops all referential integrity constraints that refer to the primary and unique keys defined on the dropped columns.

the cascade constraints clause also drops all multicolumn constraints defined on the dropped columns.

e.g. : alter table emp2 drop column employee_id cascade constraints;

if all columns fererenced by the constraints defined on the dropped columns are also dropped, then cascade constraints is not required.

e.g. : alter table test1 drop (pk, fk, col1);

managing indexes

creating an index

automatically : (primary key creation/unique key creation)

manually : (create index statement/create table statement)

e.g. : create table new_emp (employee_id number(6) primary key using index (create index emp_id_idx on new_emp(employee_id)), first_name varchar2(20), last_name varchar2(25));

e.g. : select index_name, table_name from user_index where table_name = ‘NEW_EMP’;

e.g. : create table emp_unnamed_index (employee_id number(6) primary key, first_name  varchar2(20), last_name varchar2(25);

e.g. : select index_name, table_name from user_indexes where table_name = ‘EMP_UNNAMED_INDEX’;

step 1 : creating the table

e.g. : create table new_emp2 (employee_id number(6), first_name varchar2(20), last_name varchar2(25));

step 2 : create the index

e.g. : create index emp_id_idx2 on new_emp2(employee_id);

step 3  : create the primary key

e.g. : alter table new_emp2 add primary key (employee_id) using index emp_id_idx2;

function-based indexes

a function-based index is based on expressions.

the index expression is built from columns, constraints, SQL functions, and user-defined functions.

e.g. : create index upper_dept_name_idx on dept2(upper(department_name));

e.g. : select * from dept2 where upper(department_name) = ‘SALES’;

note : function-based indexes defined with the OPPER(column_name or LOWER(column_name) keywords allow case-insensitive searches.

e.g. : create index upper_last_name_idx on emp2 (upper(last_name));

e.g. : select * from emp2 where upper(last_name) = ‘KING’;

e.g. : select * from employees where upper(last_name) is not null order by upper(last_name);

removing an index

remove an index from the data dictionary by using the drop index command.

syntax : drop index index;

remove the upper_dept_name_idx index from the data dictionary.

e.g. : drop index upper_dept_name_idx;

to drop an index, you must be the owner of the index or have the drop any index privilege.

note : you cannot modify indexes. to change an index, you must frop it and then re-create it. remove an index definition from the data dictionary by issuing the drop index statement.

external tables

advanced manipulations

manipulate data using subqueries

describe the features of multitable inserts

use the following types of multitable inserts : (unconditional insert/pivoting insert/conditional all insert/conditional first insert)

merge rows in a table

track the changes to data over a period of time

manipulating data using subqueries

retrieving data with a subquery as source

e.g. : select a.last_name, a.salary, a.department_id, b.salavg from employees a, (select departmetn_id avg(salary) salavg from employees group by department_id) b where a.department_id = b.department_id and a.salary > b.salavg;(displays employee last names, salaries, department numbers, and average salaries for all the employees who earn more than the average salary in their department. the subquery in the from clause is named b, and the outer query references the salavg column using this alias.

note : you can use a subquery in the from clause of a select statement, which is very similar to how views are used. a subquery in the from clause of a select statement is also called an inline view. a subquery in the from clause of a select statement defines a data source for that particular select statement, and only that select statement,

copying rows from another table

write your insert statement with a subquery.(you use a subquery in place of the values clause.)

syntax : insert into table [column, (column)] subquery;

e.g. : insert into sales_reps(id, name, salary, commission_pct) select employee_id, last_name, salary, commission_pct from employees where job_id like ‘%REP%’;

do not use the values clause.

to create a copy of the rows of a table, use select * in the subquery.

e.g. : insert into empl3 select * from employees;

match the number of columns in the insert clause with that in the subquery.

inserting using a subquery as a target

e.g. : insert into (select employee_id, last_name, email, hire_date, job_id, salary, department_id from emp13 where department_id = 50) values (99999,’Taylor’, ‘DTAYLOR’, to_date(’07-JUN-99’, ‘DD-MON-RR’), ’ST_CLERK’, 5000, 50);

note : you can use a subquery in place of the table name in the into clause of the insert statement. this application of subqueries helps avoid having to create a view just for performing an insert.

verify the result

e.g. : select employee_id, alst_name, email, hire_date, job_id, salary, department_id from employees where department_id = 50;

updating rows based on another table

e.g. : update emp13 set department_id = (select department_id from employees where employee_id = 100) where job_id = (select job_id from employees where employee_id = 200);(use subqueries in update statements to update rows in a table based on values from another table. updates the empl3 table based on the values from the employees table. it changes the department number of all employees with employee 200’s job id to employee 100’s current department number.)

e.g. : update empl3 set job_id = (select job_id from employees where employee_id = 205), salary = (select salary from empoyees where employee_id = 168) where employee_id = 114;(update the job and salary of employee 112 to match the job of employee 205 and salary of employee 168.)

deleting rows based on another table

e.g. : delete from empl3 where department_id = (select department_id from departments where departmetn_name like’%Public%’);(deletes all the employees who are in a department where the department name contains the string ‘Public’. )

using the with check option keyword

a subquery is used to identify the table and columns of the DML statement.

the with check option keyword prohibits you from changing rows that are not in the subquey.

e.g. : insert into (select employee_id, last_name, email, hire_date, job_id, salary from empl3 where department_id = 50 with check option values (99998, ’Smith, ‘JSMITH’, to_date(’07-JUN-99’, ‘DD-MON-RR’), ’ST_CLERK’, 5000);(returns error.)

multitable inserts

insert all

syntax : insert all into table_a values(…,…,…) into table_b values(…,…,…) into table_c values(…,…,…) select … from sourcetab where …;

the insert …select statement can be used to insert rows into multiple tables as part of a single DML statement.

multiple insert statements can be used in data warehousing systems to transfer data from one or more operational sources to a set of a target tables.

they provide significant performance improvement over : (single DML versus multiple insert … select statements./single DML versus a procedure to do multiple inserts using IF…THEN syntax.)

syntax : insert [all] [conditional_insert_clause] [insert_into_clause values_clause] (subquery)

conditional insert clause syntax :

[all] [first] [when condition then] [insert_into_clause values_clause] [else] [insert_into_clause values_clause]

e.g. : insert all into sal_history values(empid,hiredate,sal) into mgr_history values(empid,mgr,sal) select employee_id empid, hire_date hiredate, salary sal, manager_id mgr from employees where employee_id > 200;(select the employee_id, hire_date, salary, and manager_id values from the employees table for those employees whose empoyee_id is greater than 200. inserts rows into both the sal_history and the mgr_history tables.)

conditional

select the employee_id, hire_date, salary, and manager_id values from the employees table for those employees whose employee_id is greater than 200.

if the salary is greater than $10000, insert these values into the sal_history table using a conditional multi table insert statement.

if the manager_id is greater than 200, insert these values into the ngr_history table using a conditional multitable insert statement.

eg : insert all when sal > 10000 then into sal_history values(empid,hiredate,sal) when mgr > 200 then into mgr_history values(empid,mgr,sal) select employee_id empid, hire_date hiredate, salary sal, manager_id mgr from employees where employee_id > 200;

insert first

conditional

select the department_id, sum(salary), and max(hire_date) from the employees table.

if the sum(salary) is greater than $25000, then insert these values into the special_sal, using a conditional first multitable insert.

if the first when clause evaluates to true, then the subsequent when clause for this row should be skipped.

for the rows that do not satisfy the first when condition, insert into the hiredate_history_00, hiredate_history_99, or hiredate_history tables, based on the value in the hire_date column using a conditional multitable insert.

e.g. : insert first when sal > 25000 then into special_sal values(dept,sal) when hiredate like (‘500%’) then into hiredate_history_00 values(dept,hiredate) when hiredate like (‘%99%’) then into hiredate_history_99 values(deptid,hiredate) else into hiredate_history values(deptid, hiredate) select department_id deptid, sum(salary) sal, max(hire_date) hiredate from employees group by department_id;

pivoting insert

suppose you receive a set of sales records from a non relational database table, sales_source_data, in the following format : employee_id, week_id, sales_mon, sales_tue, sales_wed, sales_thur, sales_fri.

you want to store these records in the sales_info table in a more typical relational format : employee_id, week, sales.

using a pivoting insert, convert the set of sales records from the nonrelational database table to relational format.

e.g. : insert all into sales_info values(employee_id, week_id, sales_mon) into sales_info values(employee_id, week_id, sales_tue) into sales_info values(employee_id, week_id, sales_wed) into sales_info values(employee_id, week_id, sales_thur) into sales_info values(employee_id, week_id, sales_fri) select employee_id, week_id, sales_mon, sales_tue, sales_wed, sales_thur, sales_fri from sales_source_data;

e.g. : desc sales_source_data

the merge statement (merge)

provides the ability to conditionally update or insert data into a database table.

performs an update if the row exists, and an insert if it is a new row : (avoids separate updates/increases performed and ease of use./is useful in data warehousing applications.)

syntax : merge into table_name table_alias using (table | view | sub_query) alias on (join condition) wen matched then update set col1 = col1_val, col2 = col2_val when not matched then insert (column_list) values (column_values);

e.g. : merge into emp13 c using employees e on (c.employee_id = e.employee_id) when matched then update set c.first_name = e.first_name, c.last_name = e.last_name, … , c.department_id = e.department_id when not matched then insert values (e.employee_id, e.first_name, e.last_name, e.email, e.phone_number, e.hire_date, e.job_id, e.salary, e.commission_pct, e.manager_id, e.department_id);(matches the emloyee_id in the empl3 table to the employee_id in the employees table.if a match is found, the row in the empl3 table is updated to match the row in the employees table. if the row is not found, it is inserted into the empl3 table.)

e.g. : merge into empl3 c using employees e on (c.employee_id = e.employee_id) when matched then update set c.first_name = e.first_name, c.last_name = e.last_name, c.email = e.email, c.phone_number = e.phone_number, c.hire_date = e.hire_date, c.job_id = e.job_id, c.salary = e.salary, c.commission_pct = e.commission_pct, c.manager_id = e.manager_id, c.department_id = e.department_id when not matched then insert values(e.employee_id, e.first_name, e.last_name, e.email, e.phone_number, e.hire_date, e.job_id, e.salary, e.commission_pct, e.manager_id, e.department_id);

merging rows

eg : truncate table empl3;

e.g. : merge into empl3 c using employees e on (c.employee_id = e.employee_id) when matched then update set … when not matched then insert values …;

e.g. : select * from empl3;

other large data sets manipulations

flashback version query

the versions between clause

use multiple flashback queries to view row data at specific points in time. more efficiently, you can use the flashback version query feature to view all changes to a row over a period of time.you can use a query on aa table with a versions clause to produce all the versions of all the rows that exists or ever existed between the time the query was issued and the undo_retention seconds before the current time. undo_retention is an initialisation parameter which is an auto-tuned parameter. a query that includes a versions clause is referred to as a version query. the results of a version query behaves as if the where clause were applied to the versions of the rows. the version query returns versions of the rows only across transactions.

the oracle server assigns a system change number(SCN) to identify the redo records for each committed transaction.

e.g. : select salary from employees3 where employee_id = 107;(the salary for employee 107 is retrieved.)

e.g. : update employees3 set salary = salary * 1.30 where employee_id = 107;(the salary for employee 17 is increased by 30 percent.)

e.g. : commit;(the change is committed.)

e.g. : select salary from employees3 versions between scn min value and maxvalue where employee_id - 107;(the different versions of salary are displayed.)

the versions between clause

e.g. : select versions_starttime “start_date”, version_endtime “END_DATE”, salary from employees versions between SCN MINVALUE AND MAXVALUE where last_name = ‘Lorentz’;

advanced group functions

use the rollup operation to produce subtotal values

use the cube operation to produce cross-tabulation values

use the grouping function to identify the row values created by rollup or cube

use grouping sets to produce a single result set

review of group functions

syntax of group functions

group functions operate on sets of rows to give one result per group

syntax : select [column,] group_function(column) … from table [where condition] [group by group_by_expression] [order by column];

e.g. : select avg(salary), stddev(salary), count(commission_pct), amx(hire_date) from employees where job_id like ’SA%’;(calculates the average salary, standerd deviation on the salary, number of employees earning a commission, and the maximum hire date for those employees whose job_id begins with SA.)

the group by clause

syntax : select [column,] group_function(column) … from table [where condition] [group by group_by_expression] [order by column];

e.g. : select department_id, job_id, sum(salary), count(employee_id) from employees group by department_id, job_id;

the having clause

use the having clause to specify which groups are to be displayed.

you further restrict the groups on the basis of a limiting condition.

syntax : select [column,] group_function(column) … from table [where condition] [group by group_by_expression] [having having_expression] [order by column];

generating reports by grouping related data

rollup operator

rollup is an extension to the group by clause.

use the rollup operation to produce cumulative aggregates, such as subtotals.

syntax : select [column,] group_function(column) … from table [where condition] [group by [rollup group_by_expression] [having having_expression]; [order by column];

e.g. : select department_id, job_id, sum(salary) from employees where department_id < 60 group by rollup(department_id, job_id);

cube operator

cube is an extension to the group by clause.

you can use the cube operator to produce cross-tabulation values with a single select statement.

syntax : select [clumn,] group_function(column) … from table [where by [cube] group_by_expression] [having having_expression] [order by column];

e.g. : select department_id, job_id, sum(salary) from employees where department_id < 60 group by cube (department_id, job_id);

grouping function

the grouping function : (is used with either the cube or rollup operator/is used to find the groups forming the subtotal in a row./is used to differentiate stored null values from null values created by rollup or cube/returns 0 or 1.)

syntax : select [column,] group_function(column) … , grouping(expr) from table [where condition] [group by [rollup] [cube] group_by_expression] [having having_expression] [order by column];

e.g. : select department__id deptno, job_id job, sum(salary), grouping(department_id) grp_dept, grouping(job_id) grp_job from employees where department_id < 50 group by rollup(department_id, job_id);

grouping sets

grouping sets syntax is used to define multiple grouping in the same query.

all grouping specified in the grouping sets clause are computed and the results of individual groupings are combined with a union all operation.

grouping set efficiency : (only one pass over the base table is required./there is no need to write complex union statements./the more elements grouping sets has, the greater the performance benefit.)

e.g. : select department_id, job_id, manager_id, avg(salary) from employees group by grouping sets ((department_id,job_id), (job_id, manager_id));

composite columns

a composite column is a collection of columns that are treated as a unit.

rollup (a, (b, c), d)

use parentheses within the group by clause to group columns, so that they are treated as a unit while computing rollup or cube operations.

when used with rollup or cube, composite columns would require skipping aggregation across certain levels.

e.g. : select department_id, job_id, manager_id, sum(salary) from employees group by rollup(department_id,(job_id,manager_id));

concatenated groupings

concatenated grouping offer a concise way to generate useful combinations of groupings.

to specify concatenated grouping sets, you separate multiple grouping sets, rollup, and cube operations with commas so that the oracle server sombines them into a single group by clause.

the result is a cross-product of groupings from each grouping set.

e.g. : group by grouping sets(a,b), grouping sets(c,d)

e.g. : select department_id, job_id, manager_id, sum(salary) from employees group by department_id, rollup(job_id), cube(manager_id);

10.29