Introduction to oracle9i sql vol 1 pdf

Thursday, March 28, 2019 admin Comments(0)

Introduction to Oracle9i: SQL. Instructor Guide • Volume 2 SQL Statements Versus iSQL*Plus Commands Overview of .. to their particular criteria. For more information, see Oracle9i SQL Reference, “CREATE VIEW.”. The Oracle9i SQL Reference is intended for all users of Oracle SQL. Volume 1. Chapter 1, "Introduction". This chapter discusses the history of SQL and. Introduction to oracle9i pl sql student guide volume 1 pdf. Apple Online. Service Assistant currently unavailable Also, as for your Gmail account, I would not.

Language: English, Spanish, German
Country: Kenya
Genre: Academic & Education
Pages: 138
Published (Last): 28.06.2016
ISBN: 288-9-23866-704-1
ePub File Size: 18.77 MB
PDF File Size: 13.78 MB
Distribution: Free* [*Regsitration Required]
Downloads: 31162
Uploaded by: BRYCE

Introduction to Oracle9i: SQL Student Guide •Volume 1 GC10 Production June D Results 5 - 21 Additional Practices • Volume 3 SQL Statements versus iSQL*Plus Commands .. Introduction to Oracle9i: SQL Additional Practices GC10 Production June D Student Guide • Volume 1.

Show More. Each of the attributes is either required or optional. Include the location of department 90 in the output. Produce a hierarchical report to display employee number, manager number, the LEVEL pseudocolumn, and employee last name. Design Considerations and Object Types Codd proposed the relational model for database systems in Clear Screen button:

The Oracle9i server ignores blank spaces before and after the arithmetic operator. SQL Operator Precedence If an arithmetic expression contains more than one operator, multiplication and division are evaluated first.

If operators within an expression are of same priority, then evaluation is done from left to right. You can use parentheses to force the expression within parentheses to be evaluated first. Operator Precedence Notice that multiplication is performed before addition.

Use parentheses to reinforce the standard order of precedence and to improve clarity. The example in the slide displays the last name, salary, and annual compensation of each employee.

Because of the parentheses, addition takes priority over multiplication. SQL Null Values If a row lacks the data value for a particular column, that value is said to be null, or to contain a null. A null is a value that is unavailable, unassigned, unknown, or inapplicable. A null is not the same as zero or a space. Zero is a number, and a space is a character. Columns of any data type can contain nulls. Other employees are not entitled to earn commissions.

A null represents that fact. Null Values in Arithmetic Expressions Arithmetic expressions containing a null value evaluate to null. For example, if you attempt to perform division with zero, you get an error. However, if you divide a number by null, the result is a null or unknown. In the example on the slide, employee King does not get any commission.

Defining a Column Alias A column alias: This heading may not be descriptive and hence may be difficult to understand. You can change a column heading by using a column alias. By default, alias headings appear in uppercase. SQL Column Aliases continued The first example displays the names and the commission percentages of all the employees.

Notice that the optional AS keyword has been used before the column alias name. The result of the query is the same whether the AS keyword is used or not. Also notice that the SQL statement has the column aliases, name and comm, in lowercase, whereas the result of the query displays the column headings in uppercase.

As mentioned in a previous slide, column headings appear in uppercase by default. The second example displays the last names and annual salaries of all the employees. Because Annual Salary contains a space, it has been enclosed in double quotation marks. Notice that the column heading in the output is exactly the same as the column alias. SQL Concatenation Operator You can link columns to other columns, arithmetic expressions, or constant values to create a character expression by using the concatenation operator.

Columns on either side of the operator are combined to make a single output column. Concatenation Operator A concatenation operator: Notice that the employee last name and job code are combined to make a single output column.

It is printed for each row returned. Literal strings of free-format text can be included in the query result and are treated the same as a column in the SELECT list. Date and character literals must be enclosed within single quotation marks ' ' ; number literals need not. The column has the heading Employee Details. The spaces improve the readability of the output.

In the following example, the last name and salary for each employee are concatenated with a literal to give the returned rows more meaning. Duplicate Rows The default display of queries is all rows, including duplicate rows. Notice that the department numbers are repeated. Oracle SQL contains many extensions. Start the browser. Fill in the username, password, Oracle Connection Identifier fields. Enter statements field: Execute button: Click to execute the statements and commands in the Enter statements field.

Output drop-down list: The other options are File or Window. File saves the contents to a specified file. Window places the output on the screen, but in a separate window. Clear Screen button: Click it to clear text from the Enter statements field. Save Script button: Saves the contents of the Enter statements field to a file. Script Location field: Identifies the name and location of a script file that you want to execute. Browse button: Click it to search for a script file using the Windows File Open dialog box.

Exit icon: Help icon: Password icon: Click it to change your password. The command shows the column names and data types, as well as whether a column must contain data. In the syntax: In the result: Click the Save Script button. This opens the Windows File Save dialog box.

Vol 1 sql introduction pdf oracle9i to

Identify the name of the file. It defaults to. You can change the file type to a text file or save it as a. Interacting with Script Files 1 D: Enter the script name and location. Or, click the Browse button to find the script name and location.

Click the Load Script button. Change the output option to Save. It defaults to a. You can change the file type. The results are sent to the file specified. Statements can be executed from the SQL prompt or from a script file.

Practice 1 Overview This practice covers the following topics: The solutions if you require them can be found in Appendix A. Practices are intended to introduce all topics covered in the lesson. Questions 2—4 are paper-based. Do these only if you have completed all other questions within the allocated time and would like a further challenge to your skills.

Perform the practices slowly and precisely. You can experiment with saving and running command files. SQL Practice 1 1. There are four coding errors in this statement. Can you identify them? Select all data from the table.

SQL Practice 1 continued 7. Create a query to display the last name, job code, hire date, and employee number for each employee, with employee number appearing first.

SQL Practice 1 continued 9. If you have time, complete the following exercises: Run your query again. SQL Practice 1 continued Display the last name concatenated with the job ID, separated by a comma and space, and name the column Employee and Title. If you want an extra challenge, complete the following exercise: Separate each column by a comma. SQL Restricting and Sorting Data SQL Lesson Aim While retrieving data from the database, you may need to restrict the rows of data that are displayed or specify the order in which the rows are displayed.

This lesson explains the SQL statements that you use to perform these actions. If the condition is true, the row meeting the condition is returned.

WHERE restricts the query to rows that meet a condition condition is composed of column names, expressions, constants, and a comparison operator The WHERE clause can compare values in columns, literal values, arithmetic expressions, or functions. It consists of three elements: Character strings are case sensitive. Number constants, however, should not be enclosed in single quotation marks. All character searches are case sensitive.

Changing the default date format is covered in a subsequent lesson. SQL Comparison Conditions Comparison conditions are used in conditions that compare one expression to another value or expression. The symbol! The range that you specify contains a lower limit and an upper limit. You must specify the lower limit first. The IN condition is also known as the membership condition. The IN condition can be used with any data type. You can select rows that match a character pattern by using the LIKE condition.

The character pattern-matching operation is referred to as a wildcard search. Two symbols can be used to construct the search string. Show the employee number, last name, salary, department number, and the average salary in their department for all employees. Show all employees who were hired on the day of the week on whic h the highest number of employees has been hired. Create an anniversary overview based on the hire date of the employees.

Sort the anniveraries in ascending order. Find the job that was filled in the first half of and the same job that was filled during the same period in Write a compound query to produce a list of employees showing raise percentages, employee IDs, and old and new salaries.

The output might be different based on the date when the command is executed. The results of the preceding question are based on a different date and in some cases they will not match the actual results that the students get. Also the time zone offset of the various countries might differ based on daylight savings time. The results of the preceding question are based on a different date, and in some cases they will not match the actual results that the students get.

Also th e time zone offset of the various countries might differ based on daylight savings time. Write a query to display the last names, month of the date of join, and hire date of those employees who have joined in the month of January, irrespective of the year of join. Write a query to display the following for those departments whose department ID is greater than Write a query to display the following groupings: Display their last names and salaries.

Write a query to display the employee ID and last names of the employees who work in the state of California. Use scalar subqueries. Delete the records of only those employees who have changed at least 2 jobs. If your query has executed correctly, you will get the feedback: Rollback the transaction.

Write a query to display the job ids of those jobs whose maximum salary is above half the maximum salary in the whole company.

Use the WITH clause to write this query. Write a SQL statement to display employee number, last name, start date, and salary, showing: Write a hierarchical query to display the employee number, manager number, and employee last name for all employees who are two levels below employee De Haan employee number Also display the level of the employee.

Produce a hierarchical report to display the employee number, manager number, the LEVEL pseudocolumn, and employee last name. Use indentations for the NAME column. The output shown is only a sample. All the rows from the actual output are not included here.

Compliant Joins: Prior to release 9i, the join syntax was different from the ANSI standards. The new SQL: Defining Joins When data from more than one table in the database is required, a join condition is used. Rows in one table can be joined to rows in another table according to common values existing in corresponding columns, that is, usually primary and foreign key columns.

For example, to join four tables, a minimum of three joins is required. This rule may not apply if your table has a concatenated primary key, in which case more than one column is required to uniquely identify each row. Frequently, this type of join involves primary and foreign key complements.

Equijoins are also called simple joins or inner joins. Retrieving Records with Equijoins In the slide: Additional Search Conditions In addition to the join, you may have criteria for your WHERE clause to restrict the rows under consideration for one or more tables in the join. It is necessary to add the table prefix to execute your query.

If there are no common column names between the two tables, there is no need to qualify the columns. However, using the table prefix improves performance, because you tell the Oracle Server exactly where to find the columns.

Table Aliases Qualifying column names with table names can be very time consuming, particularly if table names are lengthy. You can use table aliases instead of table names. Just as a column alias gives a column another name, a table alias gives a table another name.

Table aliases help to keep SQL code smaller, therefore using less memory. Notice how table aliases are identified in the FROM clause in the example. The table name is specified in full, followed by a space and then the table alias.

For example, to join three tables, a minimum of two joins is required. Additional Search Conditions Sometimes you may need to join more than two tables. Nonequijoins A nonequijoin is a join condition containing something other than an equality operator. The salary must be between any pair of the low and high salary ranges. It is important to note that all employees appear exactly once when this query is executed. No employee is repeated in the list.

There are two reasons for this: That is, the salary value for an employee can lie only between the low salary and high salary values of one of the rows in the salary grade table.

Table aliases have been specified in the slide example for performance reasons, not because of possible ambiguity. Returning Records with No Direct Match with Outer Joins If a row does not satisfy a join condition, the row will not appear in the query result.

Instead of seeing 20 employees in the result set, you see 19 records. This operator has the effect of creating one or more null rows, to which one or more rows from the nondeficient table can be joined. Place the outer join symbol following the name of the column in the table withou t the matching rows. The Contracting department does not have any employees. The empty value is shown in the output shown.

It returns those rows from one table that have no direct match in the other table. Joining a Table to Itself Sometimes you need to join a table to itself. In this process, you look in the table twice.

Practice 4, Part 1 This practice is designed to give you a variety of exercises that join tables together. You can use the Oracle syntax shown thus far in the lesson. Complete questions 1 through 4 at the end of this lesson.

Creating Cross Joins The example in the slide gives the same results as the following: Creating Natural Joins It was not possible to do a join without explicitly specifying the columns in the corresponding tables in prior releases of the Oracle Server. The join can happen only on columns having the same names and data types in both the tables.

If other common columns were present, the join would have used them all. Equijoins The natural join can also be written as an equijoin: The example below limits the rows of output to those with a department ID equal to 20 or For example, this statement is valid: Therefore columns that have the same name in both tables have to be used without any qualifiers.

This can also be written as an equijoin: Doing so lets you specify join conditions separate from any search or filter conditions in the WHERE clause. Three-Way Joins A three-way join is a join of three tables. In SQL: The second join condition can reference columns from all three tables. This can also be written as a three-way equijoin: Comparing SQL: This query was completed in earlier releases as follows: Summary There are multiple ways to join tables.

Practice 4, Part 2 Overview This practice is intended to give you practical experience in extracting data from more than one table.

Try using both the Oracle proprietary syntax and the SQL: Write a query to display the last name, department number, and department name for all employees. Create a unique listing of all jobs that are in department Include the location of department 90 in the output.

Write a query to display the employee last name, department name, location ID, and city of all employees who earn a commission. Display the employee last name and department name for all employees who have an a lowercase in their last names.

Write a query to display the last name, job, department number, and department name for all employees who work in Toronto. Label the columns Employee, Emp , Manager, and Mgr , respectively. Order the results by the employee number. Create a query that displays employee last names, department numbers, and all the employees who work in the same department as a given employee. Create a query that displays the name, job, department name, salary, and grade for all employees.

If you want an extra challenge, complete the following exercises: Create a query to display the name and hire date of any employee hired after employee Davies. Lesson Aim This lesson further addresses functions. It focuses on obtaining summary information, such as averages, for groups of rows. It discusses how to group rows in a table into smaller sets and how to specify search criteria for groups of rows.

Group functions operate on sets of rows to give one result per group. Group Functions Unlike single-row functions, group functions operate on sets of rows to give one result per group. These sets may be the whole table or the table split into groups. Types of Group Functions Each of the functions accepts an argument. The following table identifies the options that you can use in the syntax: The default is ALL and therefore does not need to be specified.

The example in the slide displays the average, highest, lowest, and sum of monthly salaries for all sales representatives. The slide example displays the most junior and most senior employee. The following example displays the employee last name that is first and the employee last name that is the last in an alphabetized list of all employees.

The example in the slide displays the number of employees in department Group Functions and Null Values All group functions ignore null values in the column.

The average is calculated as the total commission paid to all employees divided by the number of employees receiving a commission. The average is calculated as the total commission paid to all employees divided by the total number of employees in the company. Creating Groups of Data Until now, all group functions have treated the table as one large group of information.

At times, you need to divide the table of information into smaller groups. You can then use the group functions to return summary information for each group. The example in the slide displays the department number and the average salary for each department. The rows are being grouped by department number, so the AVG function that is being applied to the salary column will calculate the average salary for each department.

For example, the SELECT statement on the slide displays the average salaries for each department without displaying the respective department numbers. Without the department numbers, however, the results do not look meaningful.

Groups within Groups Sometimes you need to see results for groups within groups. The slide shows a report that displays the total salary being paid to each job title, within each department. For example, the four stock clerks in department 50 are grouped together and a single result total salary is produced for all stock clerks within the group.

Find the average salary for each department by grouping by department number. Rows are grouped. The group function is applied. The group function is applied to the group. The example excludes sales representatives and sorts the list by the total monthly salary. Nesting Group Functions Group functions can be nested to a depth of two. The example in the slide displays the maximum average salary. Summary Seven group functions are available in SQL: The Oracle Server evaluates the clauses in the following order: Practice 5 Overview At the end of this practice, you should be familiar with using group functions and selecting groups of data.

Column aliases are used for the queries. Circle either True or False. Group functions work across many rows to produce one result per group. Group functions include nulls in calculations.

Display the highest, lowest, sum, and average salary of all employees. Label the columns Maximum, Minimum, Sum, and Average, respectively. Round your results to the nearest whole number. Write a query to display the number of people with the same job. Determine the number of managers without listing them. Label the column Number of Managers. Write a query that displays the difference between the highest a nd lowest salaries. Display the manager number and the salary of the lowest paid employee for that manager.

Exclude anyone whose manager is not known. Sort the output in descending order of salary. Round the average salary to two decimal places.

Create a query that will display the total number of employees and, of that total, the number of employees hired in , , , and Create appropriate column headings.

Create a matrix query to display the job, the salary for that job based on department number, and the total salary for that job, for departments 20, 50, 80, and 90, giving each column an appropriate heading. This lesson covers single-row subqueries and multiple-row subqueries. Main Query: Which employees have salaries greater? To solve this problem, you need two queries: You can solve this problem by combining the two queries, placing one query inside the other query.

The inner query, also called the subquery, returns a value that is used by the outer query or the main query. Using a subquery is equivalent to performing two sequential queries and using the result of the first query as the search value in the second query. 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: Comparison conditions fall into two classes: The subquery generally executes first, and its output is used to complete the query condition for the main or outer query.

Using a Subquery In the slide, 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. Top-n analysis is covered in a later lesson. There are also multiple-column subqueries: This is discussed in a subsequent lesson.

This type of subquery uses a single-row operator. The slide gives a list of single-row operators. Example Display the employees whose job ID is the same as that of employee The example on the slide displays employees whose job ID is the same as that of employee and whose salary is greater than that of employee The example consists of three query blocks: The outer query block is then processed and uses the values returned by the inner queries to complete its search conditions.

The outer and inner queries can get data from different tables. Using Group Functions in a Subquery You can display data from a main query by using a group function in a subquery to return a single row. The subquery is in parentheses and is placed after the comparison condition. The example on the slide 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 to the outer query. The SQL statement on the slide displays all the departments that have a minimum salary greater than that of department Example Find the job with the lowest average salary. Errors with Subqueries One common error with subqueries is more than one row returned for a single-row subquery. In this case, the result of the subquery will be , , , , , , and The outer query takes the results of the subquery , , , , , , and uses these results in its WHERE clause.

Problems with Subqueries A common problem with subqueries is that no rows are returned by the inner query. Presumably, the intention is to find the employee whose name is Haas.

The statement is correct but selects no rows when executed. There is no employee named Haas. So the subquery returns no rows. The outer query finds no employee with a job ID equal to null, and so returns no rows. If a job existed with a value of null, the row is not returned because comparison of two null values yields a null, hence the WHERE condition is not true.

Multiple-Row Subqueries Subqueries that return more than one row are called multiple-row subqueries. You use a multiple-row operator, instead of a single-row operator, with a multiple-row subquery.

The multiple-row operator expects one or more values. The inner query is executed first, producing a query result. The main query block is then processed and uses the values returned by the inner query to complete its search condition. In fact, the main query would look like the following to the Oracle Server: The slide example displays employees who are not IT programmers and whose salary is less than that of any IT programmer. Returning Nulls in the Resulting Set of a Subquery The SQL statement in the slide attempts to display all the employees who do not have any subordinates.

Logically, this SQL 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. Notice that the null value as part of the results set of a subquery is not a problem if you use the IN operator. For example, to display the employees who have subordinates, use the following SQL statement: Summary In this lesson, you should have learned how to use subqueries.

Subqueries are useful when a query is based on a search criteria with unknown intermediate values. Subqueries have the following characteristics: Paper-Based Questions You may want to create the inner query first for these questions. Make sure that it runs and produces the data that you anticipate before coding the outer query.

Write a query to display the last name and hire date of any employee in the same department as Zlotkey. Exclude Zlotkey. Create a query to display the employee numbers and last names of all employees who earn more than the average salary. Sort the results in ascending order of salary.

Write a query that displays the employee numbers and last names of all employees who work in a department with any employee whose last name contains a u. Display the last name, department number, and job ID of all employees whose department location ID is Display the last name and salary of every employee who reports to King. Display the department number, last name, and job ID for every employee in the Executive department.

To change the condition each time the command file is run, you use substitutio n variables. Substitution variables can replace values in the WHERE clause, a text string, and even a column or a table name. Substitution Variables The examples so far have been hard-coded. In a finished application, the user would trigger the report, and the report would run without further prompting.

You can embed substitution variables in a command file or in a single SQL statement. A variable can be thought of as a container in which the values are temporarily stored. When the statement is run, the value is substituted. The same principles can be used to achieve other goals. For example: Single-Ampersand Substitution Variable When running a report, users often want to restrict the data returned dynamically.

You do not need to define the value of each variable. With the single ampersand, the user is prompted every time the command is executed, if the variable does not exist. The same rule applies to the substitution variables. Enclose the variable in single quotation marks within the SQL statement itself. Example Display the employee number and any other column and any condition of employees.

A substitution variable can be used anywhere in the SELECT statement, except as the first word entered at the command prompt. You can also specify the condition for retrieval of rows and the column name by which the resultant data has to be ordered.

The user will see the prompt for the value only once. The value n represents a numeric value. The underlined values indicate default values. Obtaining More Readable Reports You can control the report features by using the following commands: Position of decimal point The Oracle Server displays a string of pound signs in place of a whole number whose digits exceed the number of digits provided in the format model.

It also displays pound signs in place of a value whose format model is alphanumeric but whose actual value is numeric. Footers appear at the bottom of the page. You can use the vertical bar to split the text of the title across several lines.

OFF ON toggles the title either off or on. It is not visible when turned off. Load the script file into an editor. How to Create a Script File 1. Ensure that the data required for the report is accurate before you save the statement to a file and apply formatting commands. Save the script file. How to Create a Script File continued 6. Alternatively, you can store all the format-clearing commands in a reset file.

Save the script file with your changes. Summary In this lesson, you should have learned about substitution variables and how they are useful for running reports. You can customize reports by writing script files with: Practice 7 Overview This practice gives you the opportunity to create files that can be run interactively by using substitution variables to create run-time selection criteria. The following statement is valid: Write a script file to display the last names, job IDs, and hire dates for all employees who started within a given range of dates.

Concatenate the name and job together, separated by a space and comma, and label the column Employees.

1 pdf vol to sql oracle9i introduction

Write a script to display the last names, job IDs, and department names for every employee in a given location. The search condition should allow for case-insensitive searches of the department location. Lesson Aim In this lesson, you learn how to insert rows into a table, update existing rows in a table, and delete existing rows from a table. When you want to add, update, or delete data in the database, you execute a DML statement. A collection of DML statements that form a logical unit of work is called a transaction.

Consider a banking database. When a bank customer transfers money from a savings account to a checking account, the transaction might consist of three separate operations: The Oracle Server must guarantee that all three SQL statements are performed to maintain the accounts in proper balance. When something prevents one of the statements in the transaction from executing, the other statements of the transaction must be undone.

However, if you do not use the column list, the values must be listed according to the default order of the columns in the table, and a value must be provided for each column. Enclose character and date values within single quotation marks; it is not recommended to enclose numeric values within single quotation marks.

Number values should not be enclosed in single quotes, because implicit conversion may take place for numeric values assigned to NUMBER data type columns if single quotes are included. Omit the column from the column list. Be sure that you can use null values in the targeted column by verifying the Null? The Oracle Server automatically enforces all data types, data ranges, and data integrity constraints.

Any column that is not listed explicitly obtains a null value in the new row. Common errors that can occur during user input: You can also use the USER function when inserting rows in a table. The USER function records the current username. With this format, recall that the century defaults to the current century. Because the date also contains time information, the default time is midnight Creating a Script to Manipulate Data You can save commands with substitution variables to a file and execute the commands in the file.

The values you input are then substituted into the statement. This allows you to run the same script file over and over, but supply a different set of values each time you run it. Changing Data in a Table The graphic in the slide illustrates changing the department number for employees in department 60 to department In general, use the primary key to identify a single row. Using other columns can unexpectedly cause several rows to be updated. The example in the slide transfers employee Popp to department D ORA Integrity Constraint Error If you attempt to update a record with a value that is tied to an integrity constraint, an error is returned.

Integrity constraints ensure that the data adheres to a predefined set of rules. A subsequent lesson covers integrity constraints in greater depth. Deleting Rows Based on Another Table You can use subqueries to delete rows from a table based on values from another table.

The example in the slide deletes all the employees who are in a department where the department name contains the Public string. Integrity Constraint Error If you attempt to delete a record with a value that is tied to an integrity constraint, an error is returned. If the parent record that you attempt to delete has child records, then you receive the child record found violation ORA- The following statement works because there are no employees in department For example, you could not put in a duplicate employee ID, nor leave out a value for a mandatory not null column.

Inserting this row would result in a department ID of null, which is not in the subquery. If no default value exists, a null value is used.

If no default value for the corresponding column has been specified, Oracle sets the column to null. If there is no default value defined for the column, a null value is inserted instead. If no default value is defined for the column, it changes the value to null. When creating a table, you can specify a default value for a column.

Using this statement, you can update or insert a row conditionally into a table, thus avoiding multiple UPDATE statements. The decision whether to update or insert into the target table is based on a condition in the ON clause. For example, in a data warehousing application, you may need to work with data coming from multiple sources, some of which may be duplicates. Merging Rows: Example of Merging Rows The condition c. Database Transactions The Oracle Server ensures data consistency based on transactions.

Transactions give you more flexibility and control when changing data, and they ensure data consistency in the event of user process failure or system failure. Transactions consist of DML statements that make up one consistent change to the data. For example, a transfer of funds between two accounts should include the debit to one account and the credit to another account in the same amount. Both actions should either fail or succeed together; the credit should not be committed without the debit.

When Does a Transaction Start and End? A transaction begins when the first DML statement is encountered and ends when one o f the following occurs: As savepoints are logical, there is no way to list the savepoints you have created.

If you create a second savepoint with the same name as an earlier savepoint, the earlier savepoint is deleted. If set to on, each individual DML statement is committed as soon as it is executed. You cannot roll back the changes. 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. Closing the window is interpreted as an abnormal exit. Committing Changes Every data change made during the transaction is temporary until the transaction is committed.

The Oracle Server institutes read consistency to ensure that each user sees data as it existed at the last commit. Make the data change permanent. Example While attempting 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.

Statement-Level Rollbacks Part of a transaction can be discarded by an implicit rollback if a statement execution error is detected. If a single DML statement fails during execution of a transaction, it s effect is undone by a statement-level rollback, but the changes made by the previous DML statements in the transaction are not discarded. They can be committed or rolled back explicitly by the user.

The Oracle Server issues an implicit commit before and after any data definition language DDL statement. So, even if your DDL statement does not execute successfully, you cannot roll back the previous statement because the server issued a commit.

Read Consistency Database users access the database in two ways: The purpose of read consistency is to ensure that each user sees data as it existed at the last commit, before a DML operation started.

Introduction to oracle 9i sql student guide (vol 1)

Implementation of Read Consistency Read consistency is an automatic implementation. It keeps a partial copy of the database in undo segments. When an insert, update, or delete operation is made to the database, the Oracle Server takes a copy of the data before it is changed and writes it to a undo segment. Before changes are committed to the database, only the user who is modifying the data sees the database with the alterations; everyone else sees the snapshot in the und o segment.

This guarantees that readers of the data read consistent data that is not currently undergoing change. If the transaction is rolled back, the changes are undone: What Are Locks? Locks are mechanisms that prevent destructive interaction between transactions accessing the same resource, either a user object such as tables or rows or a system object not visible to users such as shared data structures and data dictionary rows. Implicit locking occurs for SQL statements as necessary, depending on the action requested.

The users can also lock data manually, which is called explicit locking. Locks out other users — Share: Table share, row exclusive — Queries: No locks required — DDL: DML locks occur at two levels: With share lock mode, several transactions can acquire share locks on the same resource. Exclusive locks prevent the row from being changed by other transactions until the transaction is committed or rolled back. This lock ensures that no other user can modify the same row at the same time and overwrite changes not yet committed by another user.

DDL locks occur when you modify a database object such as a table. The Oracle Server guarantees a consistent view of data at all times. Locking can be implicit or explicit.