Product 6 - 33 Volume I • Student Guide .. Oracle® Database SQL Developer User's Guide .. generated in multiple formats such as PDF, HTML, Excel. Product 6 - 33 Oracle Database 11g: SQL Fundamentals I Volume II • Student Guide m y d e c a e A c l r a O ly l & On DGC11 n a e Edition April Record 6 - 14 Oracle Database 11g: PL/SQL Fundamentals Student Guide .. Application Developer's Guide 11g Release 1 () • Oracle Database PL/SQL.
|Language:||English, Spanish, Arabic|
|PDF File Size:||10.23 MB|
|Distribution:||Free* [*Regsitration Required]|
Oracle Database SQL Developer User's Guide Release Team up with other students for this exercise about controlling access to database objects. 6 days ago Fundamentals I Student Guide [PDF] [EPUB] Vicente Llanos. Download with Oracle Database 11g SQL Fundamentals II Student. Guide - Sun. 11g Sql Fundamentals 1 Volume Student Guide [PDF] [EPUB] Appendix A Practices and Solutions. Table of Contents Sun, 31 Mar GMT ( PDF).
Use dynamic sampling — If no histograms: Number of single block reads MRds: Number of multiblock reads CPUCycles: Single block read time Mreadtim: Multiblock read time Cpuspeed: Millions instructions per second 3. Hash Cost: Best join order: Plan Generator select e. Join order: Practice 3: Index Scan Unique 5. Index Scan Index Join Full Table Scan 2. Rowid Scan 3. Sample Table Scan 4. Index Scan Skip 9. Index Scan Range 6.
Index Scan Full 7. Using Bitmap Indexes Index Scan Fast Full Indexes 8. Full Table Scans: B Specific to an application or cartridge 4. Overview Index storage techniques: The default and the most common — Normal — Function based: Index Scans Types of index scans: Index Range Scan: All rights reserved.. Index Skip Scan: ENAME not null. Using Indexes: Key Bitmap Index Access: Combining Bitmap Indexes: Customers 1. Invisible Index: Overview Use index.
Do not use index. Update table. Update index. Invisible Indexes: Consider parallelizing index creation. Index the correct tables and columns. Specify the tablespace for each index. Limit the number of indexes for each table. Order index columns for performance. Drop indexes that are no longer required. Investigating Index Usage An index may not be used for one of many reasons: Practice 4: Overview This practice covers using different access paths for better optimization.
When Are Clusters Useful? Cluster Access Path: To eliminate duplicates JOIN: Buffer Sort Operator select ename. Join predicate Nonjoin predicate Join predicate Nonjoin predicate 4. Nested Loops Join: Cartesian Join select ename.
Outer Joins An outer join also returns a row if no match is found. Semijoins Semijoins only look for the first match. What Is an Execution Plan? Where to Find Execution Plans? SQL is not executed — Disadvantage: Plan hash value: This is only a partial listing of the columns. WHERE ht. SQL Monitoring: SQL Monitoring Report: Global Information Status: Interpreting an Execution Plan Transform it into a tree.
Execution Plan Interpretation: Session altered. Collapse using indentation and focus on operations consuming most resources. Practice 5: SUM s. Star Query: Queries referring to remote fact tables are not transformed. Queries containing antijoined tables are not transformed. Star Transformation: Queries referring to unmerged nonpartitioned views are not transformed. Star Transformation Plan: Use best plan containing a star transformation.
The hinted table should not be considered as the fact table in the context of a star transformation. The hinted table should be considered as the fact table in the context of a star transformation. Bitmap Join Indexes: Practice 6: Overview This practice covers using the star transformation technique to optimize your query. Number of distinct values. Favor alternative paths Small clustering factor: Favor the index range scan path C C C Block 3. Column value Distinct values: Frequency Histograms 10 buckets.
Height-Balanced Histograms 5 buckets. Bucket number Distinct values: Multicolumn Statistics: MODEL 7. Expression Statistics: Gathering System Statistics: Statistic Preferences: Gather statistics right after object creation.
Gather statistics as part of the batch operation. Objects that are volatile Objects modified in batch operations External tables. Determine the cascading effects on indexes. Determine the degree of parallelism. Determine the correct sample sizes. Manual Statistics Collection: Determine if histograms should be used. Managing Statistics Collection: Optimizer Dynamic Sampling: Practice 7: Regardless of the literal value.
Forcing Cursor Sharing: Adaptive Cursor Sharing: Overview Adaptive cursor sharing: Architecture System observes statement for a while.
Views The following views provide information about adaptive cursor sharing usage: Practice 8: Optimizer Hints: Overview Optimizer hints: Specifying Hints Hints apply to the optimization of only one statement block: Optimizer Hint Syntax: Hint Categories There are hints for: Practice 9: Overview This practice covers using various hints to influence execution plans. What Is a Service? Use Enterprise Manager to Trace Services Service Tracing: Session Level Tracing: The trcsess Utility: Overview Use the tkprof utility to format your SQL trace files: FETCH Output of the tkprof Command There are seven categories of trace statistics: Count CPU Elapsed Disk Query Current Rows Number of times the procedure was executed Number of seconds to process Total number of seconds to execute Number of physical blocks read Number of logical buffers read for consistent read Number of logical buffers read in current mode Number of rows processed by the fetch or execute Output of the tkprof Command The tkprof output also includes the following: Misses in library cache during parse: Closing the window is interpreted as an abnormal exit.
The Oracle server institutes read consistency to ensure that each user sees data as it existed at the U last commit. Save the data change. If a single DML statement fails during execution of a transaction, its effect is undone by a statement-level rollback, but the changes made by the previous DML statements in the transaction O ly are not discarded.
They can be committed or rolled back explicitly by the user. So, even if your DDL statement does not execute successfully, you cannot roll back the previous statement because the server issued a commit.
The same user can login as different sessions. Each session maintains read consistency in the c a manner described above, even if they are the same users. It keeps a partial copy of the database in the undo c r a segments.
The read-consistent image is constructed from the committed data in the table and the old data that is being changed and is not yet committed from the undo segment.
Everyone else sees the snapshot in the undo segment. This guarantees I n that readers of the data read consistent data that is not currently undergoing change. The space occupied by the old data in the r a undo segment file is freed for reuse. If the transaction is rolled back, the changes are undone: In general, this is required because the number of records locked at any given time is by default kept to the absolute minimum: There are times, however, when you may want to lock a set of records even before you change them in your program.
In this case, control will be returned r a immediately to your program or to your SQL Developer environment so that you can perform other O work, or simply wait for a period of time before trying again. Examples continued In the following example, the database is instructed to wait for five seconds for the row to become available, and then return control to you.
True 2. Overview This practice covers the following topics: This way you get to see the feedback messages on the Script Output tab page.
Do not enter all rows yet. Confirm your addition to the table. Populate the table with the next two rows of the sample data listed in step 3 by running the INSERT statement in the script that you created.
Confirm your additions to the table. Make the data additions permanent. Change the last name of employee 3 to Drexler. Verify your changes to the table. Confirm your changes to the table. Commit all pending changes. Populate the table with the last row of the sample data listed in step 3 by using the statements in the script that you created in step 6. Run the statements in the script.
Mark an intermediate point in the processing of the transaction. Confirm that the table is empty. Confirm that the new row is still intact.
Make the data addition permanent. You are taught c r a the basics of how to create simple tables, alter them, and remove them. The data types available in DDL are shown and schema concepts are introduced. Constraints are discussed in this lesson. O ly Exception messages that are generated from violating constraints during DML operations are shown and explained. Stores data r a database design so that it can be created during the build stage of database development.
The size is ultimately defined by the amount of space allocated to the database as a whole. It is important, however, to estimate how much space l e a table will use over time.
More database objects are available, but are not covered in this course. If you name a schema object using a t U quoted identifier, then you must use the double quotation marks whenever you refer to that I n object. Quoted identifiers can be reserved words, although this is not recommended. Names are not case-sensitive. However, quoted identifiers are case-sensitive.
This statement is c r a one of the DDL statements that are a subset of the SQL statements used to create, modify, or remove Oracle database structures. These statements have an immediate effect on the database and they also O ly record information in the data dictionary.
A schema is owned by a c r a database user and has the same name as that user. Each user owns a single schema. Schema objects can be created and manipulated with SQL and include tables, views, synonyms, O ly sequences, stored procedures, indexes, clusters, and database links.
This option prevents null values from entering the columns when a row is inserted without a value for the column. The default expression must match the data type of the column. There are c several data types available: U s Number having precision p and scale s Precision is the total I n number of decimal digits and scale is the number of digits to the right of the decimal point; precision can range from 1 to c l e 38, and scale can range from —84 to Used to represent the precise difference between two datetime c l e values Note: These datetime data types are available with Oracle9i and later releases.
The constraint must be satisfied for the operation to succeed. Constraints are easy to reference if you give them a c r a meaningful name. Constraint names must follow the standard object-naming rules, except that the name cannot be the same as another object owned by the same user.
You can a e define a constraint at the column or table level. Functionally, a table-level constraint is the same as a n column-level constraint. You can create constraints c r a at either the column level or table level.
Constraints defined at the column level are included when the column is defined. Table-level constraints are defined at the end of the table definition and must O ly refer to the column or columns on which the constraint pertains in a set of parentheses.
It is mainly as a table-level constraint. Constraints can be added to a table after c r a its creation and also be temporarily disabled. The first example uses the column-level syntax to define the constraint. The second example uses the table-level syntax to define the constraint.
Primary key constraint is discussed in detail later in this lesson. In fact, any number of rows can include nulls for columns without the NOT NULL n t e r s constraints because nulls are not considered equal to anything. U I n Note: You define the constraint at c r a the table level when you want to create a composite unique key.
A composite key is defined when there is not a single attribute that can uniquely identify a row. In that case, you can have a unique key O ly that is composed of two or more columns, the combined value of which is always unique and can identify rows. Only one primary key can be created c r a for each table.
This constraint enforces the uniqueness of the column or column combination O ly and ensures that no column that is part of the primary key can contain a null value. Because uniqueness is part of the primary key constraint definition, the Oracle server enforces the uniqueness by implicitly creating a unique index on the primary key column or columns. A composite c r a foreign key must be created by using the table-level definition.
For example: Keywords e A l The foreign key is defined in the child table and the table containing the referenced column is the c r a parent table. The foreign key is defined using a combination of the following keywords: The condition can use the same c r a constructs as the query conditions, with the following exceptions: If the violation ORA Any other constraint rules are not passed to the new table.
However, you can add constraints in the column definition. Without the alias, the following error is generated: You can issue DDL statements as long as O ly they do not modify any data in the table. The DROP command is executed only in n t e r U s the data dictionary, so access to the table contents is not required. When you drop a table, the database loses all the data in the table and all the indexes associated with it.
This is discussed in detail in the course titled Oracle Database 11g: Enforce rules on the data in a table whenever a row is inserted, updated, or deleted. Prevent the deletion of a table.
Prevent the creation of a table. Prevent the creation of data in a table. Create the DEPT table based on the following table instance chart. Confirm that the table is created. Include only columns that you need. Create the EMP table based on the following table instance chart. Now, try to insert the same row again.
You should get the following messages: You are taught c a the basics of creating and using views, sequences, and indexes. In this lesson, you learn about the c views, sequences, indexes, and synonyms. O ly Many applications require the use of unique numbers as primary key values.
You can also use indexes to enforce uniqueness on a column or a collection of columns. A view is a c r a logical table based on a table or another view. A view contains no data of its own, but is like a window through which data from tables can be viewed or changed.
The tables on which a view is O ly based are called base tables. For example, views can be used to query information from multiple tables without the user knowing how to write a join statement. One view can be used to retrieve data from several tables. The feedback messages will be shown on the Script Output tabbed page. The number of aliases listed must match the number of expressions selected in the subquery. You can display either the contents c a of the entire view or just specific rows and columns.
Add an alias for each column name: This means that the view can be altered without dropping, re-creating, and regranting object privileges. O ly Note: Note that alternative names have been specified for the view.
This is a requirement if any column of the view is derived from a function or an expression. All the required values must be present in the view. Remember that you are adding values directly to the underlying table through the view. O ly Reference 11g, Release 1 You can also enforce constraints c r a at the database level. The view can be used to protect data integrity, but the use is very limited.
If there is an attempt to perform DML operations on rows that the view has not selected, an error is displayed, along with the UPDATE empvu20 n a e constraint name if that has been specified. No rows are updated because, if the department number were to change to 10, the view would no longer be able to see that employee.
The statement removes the view definition c r a from the database. However, dropping views has no effect on the tables on which the view was based. On the other hand, views or other applications based on the deleted views become invalid. In the syntax: Overview of Part 1 This practice covers the following topics: Complete questions 1—6 at the end of this lesson. You can create sequences and then use c them to generate numbers.
O ly A typical usage for sequences is to create a primary key value, which must be unique for each row. This can be a time-saving object because it can reduce the amount of application code needed to write a sequence- generating routine.
Therefore, the same sequence can I n c l e r a O Oracle Database 11g: Syntax Define a sequence to generate sequential numbers automatically: The sequence starts at , does not allow I n Do not use the CYCLE option if the sequence is used to generate primary key values, unless you have a reliable mechanism that purges old rows faster than the sequence cycles.
The sequence is not tied to a table. Generally, you should name the sequence after its intended O use. However, the sequence can be used anywhere, regardless of its name.
It returns a unique value every time it is referenced, even for different users. I n When you reference sequence. The output of the query is shown below: The cache is c r a populated the first time you refer to the sequence.
Each request for the next sequence value is retrieved from the cached sequence. After the last sequence value is used, the next request for the O ly sequence pulls another cache of sequences into memory.
Therefore, if you roll back a statement containing a sequence, the number is lost. If the sequence caches values in I n memory, those values are lost if the system crashes. However, if you do so, each table can contain gaps in the sequential numbers.
If you do not have an index on the column, then a O ly An index provides direct and fast access to rows in a table. An index is used and maintained automatically by the Oracle server.
After an index is created, no direct activity is required by the user. This means that they t e r U s can be created or dropped at any time, and have no effect on the base tables or other indexes. When you drop a table, the corresponding indexes are also dropped. Users can create nonunique indexes on columns to speed up access to the rows. The name of the index is the name that is given to the constraint. This is an index that a user can create.
You can manually create a unique index, but it is recommended that you create a unique t e r U s constraint, which implicitly creates a unique index. Specify BITMAP to indicate that the index is to be created with a bitmap for each n t e r with a key value as a bitmap. U s distinct key, rather than indexing each row separately. Each DML operation that is committed on a table with indexes means that the indexes must be updated. The more indexes that O ly you have associated with a table, the more effort the Oracle server must make to update all the indexes after a DML operation.
A unique index is then created automatically. To change an index, you must drop it and then re-create it.
O ly In the syntax, index is the name of the index. If you drop a table, indexes and constraints are automatically dropped but views and sequences remain. You can create c a synonyms to give an alternative name to a table. With synonyms, you can: Creating a synonym eliminates the need to qualify the object name with the schema and provides you with an alternative name for a table, view, sequence, O ly procedure, or other objects. This method can be especially useful with lengthy object names, such as views.
Only the database administrator can drop a public synonym. Indexes are designed to speed up query performance. However, not all indexes are created c r a manually. Overview of Part 2 This practice covers the following topics: Confirm that the view works. Department 50 needs access to its employee data. Create a view named DEPT50 that contains the employee numbers, employee last names, and department numbers for all employees in department For security purposes, do not allow an employee to be reassigned to another department through the view.
Display the structure and contents of the DEPT50 view. Test your view. Attempt to reassign Matos to department The sequence should start at and have a maximum value of 1, Have your sequence increment by To test your sequence, write a script to insert two rows in the DEPT table.
Be sure to use the sequence that you created for the ID column. Add two departments: Education and Administration. Confirm your additions. Run the commands in your script. Call it EMP. Creating a database connection 1. Access the Demo: When you start SQL Developer for the first time, you need to provide the path to the java. This is already done for you as a part of the classroom setup. In any case, if you are prompted, enter the following path: To create a new database connection, in the Connections Navigator, right-click Connections.
Create a database connection using the following information: Connection Name: Enter the host name of the machine where your database server is running. Select the Save Password check box. Test the new connection. If the Status is Success, connect to the database using this new connection.
If the status is Success, click the Connect button. Browsing the Tables in the Connections Navigator 7. In the Connections Navigator, view the objects available to you under the Tables node. Verify that the following tables are present: Expand the myconnection connection by clicking the plus sign next to it. Expand the Tables icon by clicking the plus sign next to it. Click the Data tab. The tables data is displayed. Open a new SQL Worksheet.
Examine the shortcut icons available for the SQL Worksheet. Introduction continued 3. View the shortcut icons in the SQL Worksheet. Specifically look for the Execute Statement and Run Script icons. There are four coding errors in this statement. Can you identify them?
Open a new worksheet. Your first task is to create some reports based on the data from the Human Resources tables. The HR department wants more descriptive column headings for its report on employees. Then run your query again. The HR department has requested a report of all employees and their job IDs. Display the last name concatenated with the job ID separated by a comma and space and name the column Employee and Title. Separate each column output with a comma.
Run your query. Create a report that displays the last name and department number for employee number The HR departments needs to find employees with high salary and low salary. Create a report to display the last name, job ID, and hire date for employees with the last c names of Matos and Taylor.
Order the query in ascending order by hire date. Display the last name and department ID of all employees in departments 20 or 50 in O ly ascending alphabetical order by name. Restricting and Sorting Data continued 6.
Label the columns Employee and Monthly Salary, respectively. The HR department needs a report that displays the last name and hire date for all employees who were hired in Create a report to display the last name and job title of all employees who do not have a manager.
Sort data in descending order of salary and commissions. Members of the HR department want to have more flexibility with the queries that you are writing.
They would like a report that displays the last name and salary of employees who O ly earn more than an amount that the user specifies after a prompt. Enter when prompted for a value in a dialog box. Restricting and Sorting Data continued The HR department wants to run reports based on a manager.
The HR department wants the ability to sort the report on a selected column. You can test the data with the following values: Write a query to display the system date. Label the column Date. If your database is remotely located in a different time zone, the output will be the date for the operating system on which the database resides. The HR department needs a report to display the employee number, last name, salary, and salary increased by Label the column New Salary.
Run the revised query. Label the column Increase. Using Single-Row Functions to Customize Output continued Rewrite the query so that the user is prompted to enter a letter that starts the last name. The HR department wants to find the duration of employment for each employee.
For each employee, display the last name and calculate the number of months between today and the m y date on which the employee was hired. Order your d e whole number. Round the number of months up to the closest A Note: Create a query to display the last name and salary for all employees.
Each asterisk signifies a thousand dollars. Sort the data in descending order of salary. Create a query to display the last name and the number of weeks employed for all employees in department Truncate the number of weeks value to 0 decimal places.
Using Conversion Functions and Conditional Expressions 1. Create a report that produces the following for each employee: Label the column as Dream Salaries. Order the results by the day of the week, starting with Monday. Display the last name, hire date, and day of the week on which the employee started. Using Conversion Functions and Conditional Expressions continued 5. Rewrite the statement in the preceding exercise using the CASE syntax.
Circle either True or False. Group functions work across many rows to produce one result per group.
Group functions include nulls in calculations. Find 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. Run the query. Write a query to display the number of people with the same job. Determine the number of managers without listing them. Label the column as Number of Managers.
Find the difference between the highest and lowest salaries. Create a report to 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. Create a query that will display the total number of employees and, of that total, the number e l of employees hired in , , , and Create appropriate column headings. Create a matrix query to display the job, the salary for that job based on the department number, and the total salary for that job, for departments 20, 50, 80, and 90, giving each column an appropriate heading.
Displaying Data from Multiple Tables 1. Write a query for the HR department to produce the addresses of all the departments. Show the location ID, street address, city, state or province, and country in the output.
The HR department needs a report of all employees. Write a query to display the last name, department number, and department name for all the employees. The HR department needs a report of employees in Toronto. 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. Run e the query. Displaying Data from Multiple Tables continued 6. Create a report for the HR department that displays employee last names, department numbers, and all the employees who work in the same department as a given employee. Give each column an appropriate label. The HR department needs a report on job grades and salaries. Then create a query that displays the name, job, department name, salary, and grade for all employees.
The HR department wants to determine the names of all employees who were hired after Davies. Create a query to display the name and hire date of any employee hired after e employee Davies. Using Subqueries to Solve Queries 1. The HR department needs a query that prompts the user for an employee last name. The query then displays the last name and hire date of any employee in the same department as the employee whose name they supply excluding that employee.
For example, if the user enters Zlotkey, find all employees who work with Zlotkey excluding Zlotkey. Create a report that displays the employee number, last name, and salary of all employees who earn more than the average salary. Sort the results in order of ascending salary. The HR department needs a report that displays the last name, department number, and job ID of all employees whose department location ID is Using Subqueries to Solve Queries continued 5.
Create a report for HR that displays the last name and salary of every employee who reports to King.