ORACLE 11G SQL FUNDAMENTALS PDF
Product 6 - 33 Oracle Database 11g: SQL Fundamentals I is an instructor-led course featuring .. 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 This book may not be reproduced, in whole or in part, in any form or by Hardison for coaching and friendship beyond th Load more similar PDF files.
|Language:||English, Spanish, Arabic|
|ePub File Size:||15.53 MB|
|PDF File Size:||12.87 MB|
|Distribution:||Free* [*Regsitration Required]|
Product 6 - 33 Oracle Database 11g SQL Fundamentals I (Vol2).pdf - Ebook download as PDF File .pdf), Text File .txt) or read book online. Product 5 - 27 How This Course Is Organized. Oracle Database 10g: SQL Fundamentals I is an instructor-led course featuring lectures and hands-on exercises. Download free Oracle Database 11g: SQL Fundamentals course material and training, PDF file on pages.
We will do everything to help you! And you dear surfers what you need? The best course and tutorial, and how to learn and use Oracle Database 11g: Useful resource for oracle learners. Watch this video also. It is very helpful for sql beginners https: Home Database Oracle Database 11g: Download free Oracle Database 11g: Beginners Created: December 10, Size: Puja Singh - Brian Pottle Downloads: Summary on tutorial Oracle Database 11g: Download the file.
Alternative Links Oracle Database 11g: SQL Fundamentals pdf. Thank you all for this course, you are doing a great job Bilalkhoder at This is great! Thank you very much and keep up with the same work. Alhumdulilah at Thank you very much it is helpful Masiloharries at Thank you very much for your tutorials Hamada at Thank you so much, I have need a books skumarcode gmail. Thank you so much, I have been looking for this for a while elam at Thank you all for this course because i wont study oracle database ageeb at Queries containing set operators are called compound queries.
All set operators have equal precedence. If a SQL statement contains multiple set operators, the Oracle server evaluates them from left top to right bottom —if no parentheses explicitly specify another order. The column in second query must be in the same data type group such as numeric or character as the corresponding column in the first query. The corresponding expressions in the SELECT lists of the component queries of a compound query must match in number and data type.
If component queries select character data, the data type of the return values is determined as follows: If component queries select numeric data, then the data type of the return values is determined by numeric precedence. In queries using set operators, the Oracle server does not perform implicit conversion across data type groups.
Therefore, if the corresponding expressions of component queries resolve to both character data and numeric data, the Oracle server returns an error. Records the details of the start date and end date of the former job, and the job identification number and department when an employee switches jobs Tables Used in This Lesson Two tables are used in this lesson. Some of the employees have been with the company for a long time and have switched to different jobs.
SQL Fundamentals I 8 - 9 Tables Used in This Lesson continued There have been instances in the company, of people who have held the same position more than once during their tenure with the company. For example, consider the employee Taylor, who joined the company on MAR Display each employee only once.
Consider the following example: Because of these unique combinations of job IDs and department IDs, each row for employee is unique and therefore not considered to be a duplicate. The combination of the two tables totals to 30 rows.
UNION returns all distinct rows selected by either query. This is because it eliminates the following row because it is a duplicate: The names of the columns, however, need not be identical. The results may be different because of the introduction of another column whose values may or may not be duplicates.
These are the records of the employees who have not changed their jobs even once. In the slide, the name, Warehouse location, is given as the dummy column heading. The output of the query is shown: You cannot use the columns from the second query to sort the output.
Unlike the case with the UNION operator, duplicate rows are not eliminated and the output is not sorted by default.
Oracle Database 11g SQL Fundamentals I (Vol2).pdf
Overview In this practice, you create reports by using: Overview In this practice, you write queries using the set operators. Use the set operators to create this report.
The HR department needs a list of countries that have no departments located in them. Display the country ID and the name of the countries. Produce a list of jobs for departments 10, 50, and 20, in that order. Display the job ID and department ID by using the set operators. Create a report that lists 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 by the company that is, they changed jobs but have now gone back to doing their original job.
The HR department needs a report with the following specifications: Manipulating Data Oracle Database 11g: 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 the 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. Most of the DML statements in this lesson assume that no constraints on the table are violated.
Constraints are discussed later in this course. The feedback messages will be shown on the Script Output tabbed page. In the syntax: 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; however, it is not recommended that you enclose numeric values within single quotation marks.
Omit the column from the column list. 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 are checked in the following order: Use of the column list is recommended as it makes the INSERT statement more readable and reliable, or less prone to mistakes.
Method Description Implicit Omit the column from the column list. Inserting Special Values You can use functions to enter special values in your table. You can also use the USER function when inserting rows in a table. The USER function records the current username. With the RR format, the system provides the correct century automatically. This is recommended because it clearly specifies the century and does not depend on the internal RR format logic of specifying the correct century.
Creating a Script You can save commands with substitution variables to a file and execute the commands in the file. After entering a value for the substitution variable, click the OK 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. Zero or more rows are added depending on the number of rows returned by the subquery.
Changing Data in a Table The slide illustrates changing the department number for employees in department 60 to department Using other columns can unexpectedly cause several rows to be updated. The example in the slide shows the transfer of employee Popp to department The syntax is as follows: The example in the slide deletes all employees in a department where the department name contains the string Admin.
Rollback information is covered later in this lesson. If the table is the parent of a referential integrity constraint, you cannot truncate the table. Disabling constraints is covered in a subsequent lesson. 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 constitute one consistent change to the data. For example, a transfer of funds between two accounts should include the debit in one account and the credit to another account of the same amount. Both actions should either fail or succeed together; the credit should not be committed without the debit.
Database Transaction: Start and End When does a database transaction start and end? A transaction begins when the first DML statement is encountered and ends when one of the following occurs: After one transaction ends, the next executable SQL statement automatically starts the next transaction. Because savepoints are logical, there is no way to list the savepoints that you have created.
Note, if you create a second savepoint with the same name as an earlier savepoint, the earlier savepoint is deleted. Implicit Transaction Processing Note: You cannot roll back the changes. To enable Autocommit, perform the following: In the Preferences dialog box, expand Database and select Worksheet Parameters.
Click OK. SQL Fundamentals I 9 - 32 Implicit Transaction Processing continued 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 the 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. The Oracle server institutes read consistency to ensure that each user sees data as it existed at the last commit. Save the data change. Example While attempting to remove a record from the TEST table, you may accidentally empty the table.
However, you can correct the mistake, reissue a proper statement, and make the data change permanent. Statement-Level Rollback A part of a transaction can be discarded through an implicit rollback if a statement execution error is detected. 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 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 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. 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. The same user can login as different sessions.
Each session maintains read consistency in the manner described above, even if they are the same users. It keeps a partial copy of the database in the undo 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. When an insert, update, or delete operation is made on the database, the Oracle server takes a copy of the data before it is changed and writes it to an undo segment.
Before the 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 undo segment. This guarantees that readers of the data read consistent data that is not currently undergoing change. The space occupied by the old data in the 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 immediately to your program or to your SQL Developer environment so that you can perform other 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.
Remember that the Oracle server guarantees a consistent view of data at all times. 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 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. Exception messages that are generated from violating constraints during DML operations are shown and explained.
Each structure should be outlined in the 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 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 quoted identifier, then you must use the double quotation marks whenever you refer to that object.
Quoted identifiers can be reserved words, although this is not recommended. Naming Guidelines Use descriptive names for tables and other database objects. Names are not case-sensitive. However, quoted identifiers are case-sensitive. This statement is 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 record information in the data dictionary.
A schema is owned by 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, 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. Consider the following examples: Because creating a table is a DDL statement, an automatic commit takes place when this statement is executed.
There are several data types available: CHAR [ size ] Fixed-length character data of length size bytes Default and minimum size is 1; maximum size is 2, NUMBER [ p,s ] Number having precision p and scale s Precision is the total number of decimal digits and scale is the number of digits to the right of the decimal point; precision can range from 1 to 38, and scale can range from —84 to These datetime data types are available with Oracle9i and later releases.
You can use constraints to do the following: The constraint must be satisfied for the operation to succeed. Constraint Guidelines All constraints are stored in the data dictionary.
Constraints are easy to reference if you give them 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. Constraints can be defined at the time of table creation or after the creation of the table. You can define a constraint at the column or table level.
Functionally, a table-level constraint is the same as a column-level constraint. You can create constraints 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 refer to the column or columns on which the constraint pertains in a set of parentheses.
Download Free Oracle 1Z0-051 Demo
It is mainly the syntax that differentiates the two; otherwise, functionally, a column-level constraint is the same as a table-level constraint. Constraints that apply to more than one column must be defined at the table level. Constraints can be added to a table after 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. More details about the primary key constraint are provided later in this lesson.
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 constraints because nulls are not considered equal to anything. You define the constraint at 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 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 for each table. This constraint enforces the uniqueness of the column or column combination 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 foreign key must be created by using the table-level definition. The foreign key can also be defined at the column level, provided that the constraint is based on a single column. For example: Keywords The foreign key is defined in the child table and the table containing the referenced column is the parent table.
The foreign key is defined using a combination of the following keywords: The default behavior is called the restrict rule, which disallows the update or deletion of referenced data. The condition can use the same constructs as the query conditions, with the following exceptions: CHECK constraints can be defined at the column level or table level. Violating Constraints When you have constraints in place on columns, an error is returned if you try to violate the constraint rule.
For example, if you try to update a record with a value that is tied to an integrity constraint, an error is returned. The following statement works because there are no employees in department Any other constraint rules are not passed to the new table. However, you can add constraints in the column definition. However, be sure to provide a column alias when selecting an expression.
Without the alias, the following error is generated: You can issue DDL statements as long as they do not modify any data in the table. Dropping a table invalidates the dependent objects and removes object privileges on the table. 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: Confirm that the new table was added to the database.
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.
You get the following error message: Now, try to insert the same row again. You should get the following messages: You are taught the basics of creating and using views, sequences, and indexes.
Copyright 2007, Oracle. All rights reserved.
In this lesson, you learn about the views, sequences, indexes, and synonyms. With views, you can present and hide data from the tables. Many applications require the use of unique numbers as primary key values. You can either build code into the application to handle this requirement or use a sequence to generate unique numbers. If you want to improve the performance of data retrieval queries, you should consider creating an index.
You can also use indexes to enforce uniqueness on a column or a collection of columns. You can provide alternative names for objects by using synonyms. You can present logical subsets or combinations of data by creating views of tables. A view is 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 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 number of aliases listed must match the number of expressions selected in the subquery.
You can display either the contents 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. 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.
You can remove a row from a view unless it contains any of the following: All the required values must be present in the view. Remember that you are adding values directly to the underlying table through the view. You can also enforce constraints at the database level. The view can be used to protect data integrity, but the use is very limited.
Therefore it enables integrity constraints and data validation checks to be enforced on data being inserted or updated. If there is an attempt to perform DML operations on rows that the view has not selected, an error is displayed, along with the 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 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.
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 them to generate numbers. You can define a sequence to generate unique values or to recycle and use the same numbers again. A typical usage for sequences is to create a primary key value, which must be unique for each row. A sequence is generated and incremented or decremented by an internal Oracle routine. This can be a time-saving object because it can reduce the amount of application code needed to write a sequence- generating routine.
Sequence numbers are stored and generated independent of tables. Therefore, the same sequence can be used for multiple tables. Syntax Define a sequence to generate sequential numbers automatically: The sequence starts at , does not allow caching, and does not cycle.
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 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. When you reference sequence. The output of the query is shown below: Suppose that you now want to hire employees to staff the new department. The cache is 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 sequence pulls another cache of sequences into memory.
Gaps in the Sequence Although sequence generators issue sequential numbers without gaps, this action occurs independent of a commit or rollback. Therefore, if you roll back a statement containing a sequence, the number is lost.
Another event that can cause gaps in the sequence is a system crash. If the sequence caches values in memory, then those values are lost if the system crashes. Because sequences are not tied directly to tables, the same sequence can be used for multiple tables. However, if you do so, each table can contain gaps in the sequential numbers.
The sequence must be dropped and re-created to restart the sequence at a different number. Indexes can also be created automatically by the server when you create a primary key or a unique constraint. Indexes can be created explicitly or automatically. If you do not have an index on the column, then a full table scan occurs. 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. Indexes are logically and physically independent of the table that they index.
This means that they 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.
How Are Indexes Created?
You can create two types of indexes. Unique index: The name of the index is the name that is given to the constraint. Nonunique index: 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 constraint, which implicitly creates a unique index.
Specify BITMAP to indicate that the index is to be created with a bitmap for each distinct key, rather than indexing each row separately. Bitmap indexes store the rowids associated with a key value as a bitmap. Each DML operation that is committed on a table with indexes means that the indexes must be updated. The more indexes that you have associated with a table, the more effort the Oracle server must make to update all the indexes after a DML operation. When to Create an Index Therefore, you should create indexes only if: A unique index is then created automatically.
To change an index, you must drop it and then re-create it. 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 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, procedure, or other objects.
This method can be especially useful with lengthy object names, such as views. The database administrator can create a public synonym that is accessible to all users. Only the database administrator can drop a public synonym.
Overview of Part 2 This practice covers the following topics: Complete questions 7—10 at the end of this lesson. 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.
Table Descriptions Oracle Database 11g: The company has three divisions: Tracks business statistics to facilitate business decisions Each of these divisions is represented by a schema. In this course, you have access to the objects in all the schemas. However, the emphasis of the examples, demonstrations, and practices is on the Human Resources HR schema. Human Resources HR This is the schema that is used in this course. In the Human Resource HR records, each employee has an identification number, email address, job identification code, salary, and manager.
Some employees earn commissions in addition to their salary. The company also tracks information about jobs within the organization. Each job has an identification code, job title, and a minimum and maximum salary range for the job.
Some employees have been with the company for a long time and have held different positions within the company. When an employee resigns, the duration the employee was working for, the job identification number, and the department are recorded. The sample company is regionally diverse, so it tracks the locations of its warehouses and departments. Each employee is assigned to a department, and each department is identified either by a unique department number or a short name. Each department is associated with one location, and each location has a full address that includes the street name, postal code, city, state or province, and the country code.
In places where the departments and warehouses are located, the company records details such as the country name, currency symbol, currency name, and the region where the country is located geographically.
Oracle Join Syntax Oracle Database 11g: C - 2 Objectives After completing this appendix, you should be able to do the following: A join is used to view information from multiple tables. Therefore, you can join tables together to view information from more than one table. In the example in the slide, the report displays data from two separate tables: Cartesian Products When a join condition is invalid or omitted completely, the result is a Cartesian product, in which all combinations of rows are displayed.
In other words, 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. Therefore, you should always include a valid join condition unless you have a specific need to combine all rows from all tables.
However, Cartesian products are useful for some tests when you need to generate a large number of rows to simulate a reasonable amount of data.Omit the column from the column list.
The HR department needs a report with the following specifications: For example, a transfer of funds between two accounts should include the debit in one account and the credit to another account of the same amount. The connection gets added in the Connections Navigator. Enter the sqlplus command shown in the slide. More From piciul