Create triggers on database tables. ✵. Use PL/SQL's datatypes for database and program data. ✵. Use program stucture and control flow to design and write. Dynamic Links: Using the SQL*Plus copy Command. The recycle bin concept introduced with Oracle Database 10g impacts the space requirements for your. Oracle Database SQL Reference, 10g Release 2 (). B . 1 Introduction to Oracle SQL. History of 2 Basic Elements of Oracle SQL.
|Language:||English, Spanish, Dutch|
|Genre:||Fiction & Literature|
|ePub File Size:||22.32 MB|
|PDF File Size:||13.53 MB|
|Distribution:||Free* [*Regsitration Required]|
All rights reserved. Introduction Employ SQL functions to generate and retrieve customized data update data in Oracle Database 10g. • Obtain metadata by. Oracle® Database SQL Reference 10g Release 2 () B This course offers you an introduction to Oracle Database 10g database. Introduction to Oracle 10g SQL Programming is a five-day course where students will and manipulate objects in Oracle10g Structured Query Language (SQL).
Just as a column alias gives a column another name, a table alias gives a table another name. Henley Mark Hurd Jack F. I n c l e r a O Oracle Database 11g: 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. Zero or more rows are added depending on the number of rows returned by the subquery. After the installation process sets up sample tables, the user logs into the database with the username scott and the password tiger.
Oracle Application Express is a no-cost option of the Oracle database. Oracle Transaction Management". Expert Oracle Database 10g Administration. ITPro collection. Managing Default Accounts". Practical Oracle Security: Rockland, Massachusetts: Workspace Manager is a tool used to manage multiple versions of data within the same database.
Oracle Database Documentation. Digital Press. Oracle Multimedia is a feature that enables Oracle Database to store, manage, and retrieve multimedia data in an integrated manner with other enterprise information.
Oracle Database Online Documentation, 10g Release 2 Oracle Utilities [ Oracle SQL Recipes: A Problem-Solution Approach. The oradebug utility can be used to enable and disable tracing for a session. In St. Amant, Kirk; Still, Brian. Handbook of Research on Open Source Software: Technological, Economic, and Social Perspectives. IGI Global research collection. IGI Global. Oracle Corporation has started a drive toward wizard-driven environments with a view to enabling non-programmers to produce simple data-driven applications.
The Java EE implementation offers increased functionality including a command line based configuration, enhanced security, file caching, and RESTful web services.
Harrison, Guy Next Generation Databases. Installation, Administration, and Performance. Expert's Voice in Oracle. Oracle Database 10g New Features.
McGraw Hill Professional. Oracle Database Java Developer's Guide: Redwood City: Oracle USA, Inc. Native compilation provides a speed increase ranging from two to ten times the speed of the bytecode interpretation. Apress Series. OPatch is a collection of Perl scripts and Java classes providing the capability to apply and roll back interim one-off patches to an Oracle database environment.
Oracle live SQL. The Database Hacker's Handbook: Defending Database. New Delhi: Archived from the original PDF on 8 June Retrieved 19 December University of Arkansas. Run the Install checklist for Oracle Archived from the original on 23 October Retrieved 26 July Pocket References Series.
O'Reilly Media, Inc. Oracle products are currently licensed using two different licensing models: Per Named User. Oracle Database Client. The SID automatically defaults to the database name portion of the global database name sales in the example sales. You can accept or change the default value. Osborne Oracle Press. The set of programs also lets you start a database instance.
They allocate a shared memory realm where other programs process SQL statements. This shared memory realm is the active database instance. Expert Oracle Database 11g Administration. The expert's voice in Oracle. Oracle databases are logically divided into one or more tablespaces. An Oracle tablespace is a logical entity that contains the physical datafiles. Archived from the original on 5 July Retrieved 12 July You can use tablespaces to achieve the following goals: Oracle FAQs.
Retrieved 19 January OCP Oracle Database 10g: New Features for Administrators Exam Guide. Archived from the original on 7 July Retrieved 30 July In 10 g , Oracle introduced a new kind of storage for its database product. Automatic Storage Management ASM is a logical volume manager that takes physical disk partitions and manages their contents [ Archived from the original on 20 April New York Oracle users Group.
Oracle Database Reference 11g Release 2 Troubleshooting Oracle Performance. Using Data Pump export and import". It is the process that actually performs the heavy-duty work of loading and unloading data. The master process DMnn creates the worker process. Oracle Data Guard 11g Handbook. Oracle Press. Secrets of the Oracle Database. Apress series. Data Guard". Oracle Database 11g: New Background Processes in 10 g ".
Oracle Database 10g Performance Tuning: Background Processes". Oracle Processes". Expert Oracle Database Architecture. Parallel Query Execution Servers [ These are the parallel query execution servers themselves.
F Background Processes". Archived from the original on 13 November Retrieved 12 November Process Spawner Process [ In Oracle Redo data transmitted from the primary database is received by the remote file server RFS process on the standby system, where the RFS process writes the redo data to archived log files or standby redo log files.
New Oracle Background Processes [ Oracle Database Concepts. A connection is a communication pathway between a user process and an Oracle instance. Oracle Database Reference 11g Release 1 Physical Database Design Using Oracle. Foundations of Database Design. CRC Press. Redwood city, California: Archived from the original on 16 March Data Concurrency and Consistency".
Oracle Database Concepts 11g Release 1 Parameter files contain a list of configuration parameters for that instance and database. April The Expert's voice in Oracle. Berkeley, California: They are the layer closest to C [ Burleson Consulting.
Updated to Oracle Exam 1Z Oracle 10g includes a [ High-Performance Oracle: Oracle Resource Manager is [ Universitas Publishing House.
Archived from the original PDF on 6 March Retrieved 6 March Kaleidoscope Series. Sams Publishing. Background dump bdump files are generated when an Oracle process experiences unexpected problems.
Expert's Voice. The alert log captures major changes and events that occur during the running of the Oracle instance, including log switches, any Oracle-related errors, warnings, and other messages. NLS is actually a subset of globalization support. NLS is the ability to choose a national language and store data in a specific character set. Globalization support enables you to develop multilingual applications and software products that can be accessed and run from anywhere in the world simultaneously.
Loney, Kevin 17 December Archived from the original on 27 August Retrieved 5 September Database management systems. Administration and automation Query optimization Replication. Database models Database normalization Database storage Distributed database Federated database system Referential integrity Relational algebra Relational calculus Relational database Relational model Object-relational database Transaction processing.
Jeffrey Berg H. Henley Mark Hurd Jack F. Kemp Donald L. Lucas Naomi O. Oracle Linux Oracle Solaris. Category Commons. Retrieved from " https: Database management systems Oracle Database Client-server database management systems Relational database management systems Proprietary database management systems Database engines.
Proprietary . Old version, no longer supported: Oracle v2. Oracle v3. Concurrency control , data distribution, and scalability. Oracle v4. Oracle v5. Oracle v6. Oracle 6. Oracle 7. Oracle8 Database. Oracle8 i Database. Oracle9 i Database. Oracle9 i Database Release 2. Oracle Database 10 g Release 1.
Oracle Database 10 g Release 2. Oracle Database 11 g Release 1. Oracle Database 11 g Release 2. 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.
Create a report for HR that displays the department number, last name, and job ID for every employee in the Executive department. Using the Set Operators 1. 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. Using the Set Operators continued 4. 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: This way you get to see the feedback messages on the Script Output tabbed page. From File menu, select Open. In the Open dialog box, navigate to D: You get a Create Table succeeeded message on the Script Output tabbed page. Manipulating Data continued 4. Populate the table with the next two rows of sample data listed in step 3 by running the INSERT statement in the script that you created.
Manipulating Data continued To confirm that the table was created and to view its structure, issue the following command: Include only those columns that you need. To confirm that the table was created and to view its structure: Hence, you will not be allowed to insert any row into the table because it is assigned a read only status. Now try to insert the same row again.
Creating Other Schema Objects Part 1 1. Create a view named DEPT50 that contains the employee numbers, employee last names, and department numbers for all m y e employees in department They have requested that you label the view columns d reassigned to another department through the view.
Creating Other Schema Objects continued Part 2 7. You need a sequence that can be used with the primary key column of the DEPT table. Oracle Join Syntax 1. Write a query to display the last name, department number, and department name for all employees. Order the results by the employee number.
Oracle Join Syntax continued 6. The HR department wants to determine the names of all employees hired after Davies. Create a query to display the name and hire date of any employee hired after employee Davies.
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. Each employee is assigned to a department, and each department is identified either by c a a unique department number or a short name.
Each department is associated with one location, and the country code. A join is used to view information c table. Therefore, you can join tables together to view information from more than one O ly Note: In the example in the slide, the report c displays data from two separate tables: In other words, all rows in the first table are joined to all rows in O ly A Cartesian product tends to generate a large number of rows and the result is rarely useful.
Before the Oracle9i release, the join syntax was proprietary. The SQL: Rows in one c r a 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 O 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. Therefore, it is necessary O ly to add the table prefix to execute your query.
However, using a table prefix improves performance, because you tell the Oracle server exactly where to find the columns. 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.
The table name is specified in full, followed by a space and then the table alias. Equijoins are also called simple joins or inner joins.
Other columns that are not present in both the tables need not be qualified r a by a table alias, but it is recommended for better performance. 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 For example, to join three tables, a minimum of two joins is required.
The relationship is obtained using an operator other than the n a e t e r U s I n c l e r a O Oracle Database 11g: The salary c r a 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 O ly 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.
I n Note: O 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. Syntax e A l 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 O ly rows from the nondeficient table can be joined.
The c Outer Join Restrictions r a Contracting department does not have any employees. The empty value is shown in the output. Overview e A l This practice is intended to give you practical experience in extracting data from more than one table c using the Oracle join syntax. 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 l appropriate label.
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. I n username Your database username password Your database password Your password is visible if you enter it here.
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 c contain data. In c the result: O ly Type: The SQL prompt then appears. The new current line is displayed. The newly edited line is displayed. U I n c l e r a O Oracle Database 11g: 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. 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 l to run a script. This opens an editor with the script file in it.