Records 7 - 14 Oracle Database 11g: PL/SQL. Fundamentals. Student Guide. DGC Edition August D ®. He is also the co-author of Oracle Database AJAX & PHP Web Application 3 Language Fundamentals viii Oracle Database 11g PL/SQL Programming. Oracle Database 11g: SQL Fundamentals I Oracle Database 10g PL/SQL Programming Scott Urman Ron Hardman Michael McLaughlin McG.
|Language:||English, Spanish, Dutch|
|ePub File Size:||26.82 MB|
|PDF File Size:||19.21 MB|
|Distribution:||Free* [*Regsitration Required]|
Interacting with Oracle Database Server: SQL Statements in PL/SQL Programs. 5. Writing Control Structures. Day 2: 6. Working with Composite Data Types. 7. Oracle Database PL/SQL Language Reference, 11g Release 1 (). B 05 Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of .. 2 PL/SQL Language Fundamentals. Product 6 - 33 Oracle Database 11g: SQL Fundamentals I is an instructor-led course featuring lectures . major application development environments such as PL/SQL, Java/ JDBC,. . generated in multiple formats such as PDF, HTML, Excel.
You can create two types of indexes. Subqueries have the following characteristics: The size is ultimately defined by the amount of space allocated to the database as a whole. Specify column information. The HR department wants to determine the names of all employees who were hired after Davies. 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. If you find any problems in the document, please report them in writing to:
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. 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. 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.
C - 5 Generating a Cartesian Product Cartesian product: Before the Oracle9i release, the join syntax was proprietary. The SQL: Rows in one table can be joined to rows in another table according to common values that exist in the 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. Qualifying Ambiguous Column Names When joining two or more tables, you need to qualify the names of the columns with the table name to avoid ambiguity. Therefore, 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 a table prefix improves performance, because you tell the Oracle server exactly where to find the columns. Qualifying column names with table names can be very time consuming, particularly if table names are lengthy. Therefore, 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, thereby using less memory. The table name is specified in full, followed by a space and then the table alias.
Often, this type of join involves primary and foreign key complements. Equijoins are also called simple joins or inner joins. Other columns that are not present in both the tables need not be qualified by a table alias, but it is recommended for better performance. Example Retrieving Records with Equijoins: Table aliases are used to qualify the columns and avoid ambiguity.
The example in the slide limits the rows of output to those with a department ID equal to 20 or C - 13 Joining More than Two Tables To join n tables together, you need a minimum of n—1 join conditions.
For example, to join three tables, a minimum of two joins is required. Nonequijoins A nonequijoin is a join condition containing something other than an equality operator. Therefore, each employee can be graded based on the salary. 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 example in the slide for performance reasons, not because of possible ambiguity. To return the department record that does not have any employees, or to return the employee record that does not belong to any department, you can use the outer join. C - 17 Outer Joins: Syntax Missing rows can be returned if an outer join operator is used in the join condition.
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. The Contracting department does not have any employees.
The empty value is shown in the output. It returns those rows, from one table, that have no direct match in the other table. Another Example … Outer Join: In this process, you look in the table twice. C - 21 Self-Join: C - 22 Summary In this appendix, you should have learned how to use joins to display data from multiple tables by using Oracle-proprietary syntax. Summary There are multiple ways to join tables. C - 23 Practice C: Overview This practice is intended to give you practical experience in extracting data from more than one table using the Oracle join syntax.
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. Run the query. The HR department needs a report of all employees.
Write a query to display the last name, department number, and department name for all 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. Order the results by the employee number.
Create a report for the HR department that displays employee last names, department numbers, and all 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 employee Davies. D - 2 Objectives After completing this appendix, you should be able to do the following: Oracle SQL contains many extensions.
It contains its own command language. To log in from a Windows environment: Enter the username, password, and database name. To log in from a command-line environment: Log on to your machine. Enter the sqlplus command shown in the slide. To ensure the integrity of your password, do not enter it at the operating system prompt. Instead, enter only your username. Enter your password at the password prompt. The result of the command is a display of column names and data types as well as an indication if a column must contain data.
In the result: Displays the data type for a column Oracle Database 11g: The SQL prompt then appears. Any edits that you made apply to the current line. The new current line is displayed. The newly edited line is displayed. Verify the new contents of the buffer by using the LIST command. In this case, replace the employees table with the departments table. You can use the commands described in the following table: The default extension is.
GET filename [. The default extension for the file name is. STA[RT] filename [. OFF closes the spool file. OUT closes the spool file and sends the file results to the printer. In this way, you can store frequently used scripts for use in the future. You can also, alternatively, use the symbol to run a script. This opens an editor with the script file in it. The delimiter must be the first character of a new line immediately following the statement. You can now append to, or replace an existing file, where previously you could only use SPOOL to create and replace a file.
You must use quotes around file names containing white space.
You can execute commands from the SQL prompt or from a script file. E - 2 Objectives After completing this appendix, you should be able to do the following: It assumes that you have already created a database connection and that you are ready to browse objects in the SQL Developer interface. SQL Developer uses the left pane for navigation to find and select objects, and the right pane to display information about the selected objects.
You can customize many aspects of the appearance of SQL Developer by setting preferences. You can see the definition of the objects broken into tabs of information that is pulled out of the data dictionary. For example, if you select a table in the Navigator, the details about columns, constraints, grants, statistics, triggers, and so on are displayed on an easy-to-read tabbed page.
Expand the Connections node in the Connections Navigator. Expand Tables. By default, the Columns tab is selected. It shows the column description of the table. Expand Connections. Right-click Tables and select New Table. Alternatively, you can create objects by using the context menus. When created, you can edit the objects using an edit dialog or one of the many context-sensitive menus.
As new objects are created or existing objects are edited, the data definition language DDL for those adjustments is available for review. The slide shows the creation of a table using the context menu. To open a dialog box for creating a new table, right-click Tables and select New Table. The dialog boxes for creating and editing the database objects have multiple tabs, each reflecting a logical grouping of properties for that type of object.
E - 5 Creating a New Table: Click x to remove column. Select the Advanced check box to get all the options for defining tables. Creating a New Table: Example In the Create Table dialog box, if you do not select the Advanced check box, you can create a table quickly by specifying the columns and some frequently used features. If you select the Advanced check box, the Create Table dialog box changes to one with multiple options, in which you can specify an extended set of features while creating the table.
To create a new table, perform the following steps: In the Connections Navigator, right-click Tables. In the Create Table dialog box, select Advanced. Specify column information. Although it is not required, you should also specify a primary key using the Primary Key tab in the dialog box. Sometimes, you may want to edit the table that you have created.
To edit a table, right- click the table in the Connections Navigator and select Edit. Defining Constraints From the same Create Table dialog box, you can define all types of constraint on the table. In the slide, the Primary Key option is selected. In the slide, ID is specified as the primary key. Similarly, click the Unique Constraints option to define a unique constraint, or click Foreign Keys to define a foreign key constraint.
If you want to define constraints on an existing table, in the Connections Navigator, right-click the table, and select Edit.
A similar dialog box as shown in the slide above will appear. You can also click the pencil icon to edit. You can view the table structure and even modify the column definition of the table. Who share their knowledge, you can discover the extent of our being selected to easily learn without spending a fortune!
Oracle Database 11g: SQL Fundamentals. But also many other tutorials are accessible just as easily! Computer PDF guide you and allow you to save on your studies. You should come see our Database documents.
You will find your happiness without trouble! The latest news and especially the best tutorials on your favorite topics, that is why Computer PDF is number 1 for courses and tutorials for download in pdf files - Oracle Database 11g: Download other tutorials for advice on Oracle Database 11g: 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.