Oracle SQL and SQL*Plus: SQL*Plus and Reporting

This course is the second in a two-part series on Oracle SQL Specifics that will teach participants how to use commands functions and operators supported by Oracle as extensions to standard SQL. Users will learn how to create and manage tables build integrity constraints and use the COMMIT and ROLLBACK statements to control transactions. Learners will create and maintain views sequences indexes and synonyms. Additionally users will learn how to control database access add new users and provide alternative names for objects using synonyms. Finally participants will learn how to format query output control the SQL*Plus environment and specify variables at runtime.

This is primarily online training This is an online eLearning or CBT training program Contact Serebra Learning Corporation for more information

Duration: 8 hours Training Presented in: English

Audience

Application Developers System Analysts Database Administrators Technical Support Personnel.

Prerequisites

Prior knowledge of SQL is helpful. The first course in this series Oracle SQL Specifics: Retrieving and Formatting Data should be taken prior to this course.

Objective

Upon completion of this course the student will be able to:

• Create and manage tables.
• Use the Oracle database objects: views sequences and indexes.
• Control transactions.
• Control DBA and user access.
• Use SQL*Plus commands.

Topics Include

Unit 1: Creating and Managing Tables

• Match each Oracle8 data structure to its description.
• Use the CREATE TABLE statement to create a table.
• Use the CREATE TABLE statement and an embedded subquery to create a table from rows in another table.
• Match the data dictionary views used to confirm tables and constraints with their purpose.
• Identify the naming conventions to follow when naming tables and columns.
• Identify the four most common datatypes used in the Oracle8 database.
• Match the five Oracle8 data integrity constraints with their descriptions.
• Identify the default constraint name generated by Oracle8.
• Use the CREATE TABLE statement to create a table with integrity constraints.
• Use the ALTER TABLE statement with the ADD clause to add a column to a table.
• Use the ALTER TABLE statement with the MODIFY clause to modify a column definition.
• Use the ALTER TABLE statement with the ADD clause to add a constraint to a table.
• Use the ALTER TABLE statement with the DROP clause to drop a constraint from a table.
• Use the ALTER TABLE statement with the DISABLE or ENABLE clause to activate or deactivate a constraint.
• Use the RENAME statement to rename a table.
• Use the DROP TABLE statement to remove a table and its definition from the database.
• Use the TRUNCATE TABLE statement to truncate a table.
• Use the COMMENT ON statement to add a comment to a table.

Unit 2: Using Oracle Database Objects

• Identify the advantages of views.
• Use the CREATE VIEW statement to create a view.
• Differentiate between a single view and a complex view.
• Use the CREATE OR REPLACE VIEW statement to modify a view.
• Use the CREATE OR REPLACE VIEW statement with the WITH CHECK OPTION clause to create a view that enables you to perform DML operations on the view.
• Use the CREATE OR REPLACE VIEW statement with the WITH READ ONLY option to create a view that does not allow DML operations.
• Query the USER_VIEWS data dictionary view to confirm a view.
• Use the DROP VIEW statement to remove a view from the database.
• Identify a typical usage for a sequence.
• Use the CREATE SEQUENCE statement to define a sequence.
• Query the USER_SEQUENCES data dictionary view to confirm the settings of a sequence.
• Insert a new value using a sequence and view the current value for a sequence.
• Identify actions that cause gaps in sequence numbers.
• Use the ALTER SEQUENCE statement to extend the maximum value of a sequence.
• Use the DROP SEQUENCE statement to remove a sequence from the data dictionary.
• Match index types to their descriptions.
• Use the CREATE INDEX statement to create an index.
• Query the USER_IND_COLUMNS view in the data dictionary to display all indexes and the effected column names.
• Use the DROP INDEX statement to remove an index from the data dictionary.

Unit 3: Controlling Transactions

• Select DML statements from a list of SQL statements.
• Identify actions that end a transaction.
• Identify the three statements used to control transactions explicitly.
• Identify the actions that control transactions implicitly.
• Identify the state of data before a COMMIT or ROLLBACK statement is issued.
• Identify the state of data after a COMMIT statement is issued.
• Select DML statements from a list of SQL statements.
• Identify actions that end a transaction.
• Identify the three statements used to control transactions explicitly.
• Identify the actions that control transactions implicitly.
• Identify the state of data before a COMMIT or ROLLBACK statement is issued.
• Identify the state of data after a COMMIT statement is issued.
• Identify the state of data after a ROLLBACK statement is issued.
• Create a savepoint.
• Identify the definition of statement-level rollback.
• Identify the principles of read consistency.
• Identify features of Oracle data locking.
• Match the Oracle implicit locks to their descriptions.
• Identify the reasons to implement explicit locking.
• Identify the state of data after a ROLLBACK statement is issued.
• Create a savepoint.
• Identify the definition of statement-level rollback.
• Identify the principles of read consistency.
• Identify features of Oracle data locking.
• Match the Oracle implicit locks to their descriptions.
• Identify the reasons to implement explicit locking.

Unit 4: Controlling User Access

• Match typical DBA privileges to the operations they authorize.
• Use the CREATE USER statement to create a new user.
• Use the GRANT statement to allow a user to create tables.
• Use the CREATE ROLE statement to create a role and use the GRANT statement to assign certain privileges to the role.
• Match typical user privileges to the operations they authorize.
• Use the ALTER USER statement to change your password.
• Use the GRANT statement to give another user access to your database objects.
• Use the GRANT statement with the WITH GRANT OPTION keyword to give another user access to your database objects and allow that user to pass these privileges.
• Query the USER_TAB_PRIVS_RECD data dictionary table to confirm the privileges you have been granted.
• Use the REVOKE statement to remove privileges granted to other users.
• Use the CREATE SYNONYM statement to create a synonym for an object.

Unit 5: Using SQL*Plus Commands

• Identify how the data returned from a query is defined for interactive and non-interactive reports.
• Use a single ampersand substitution variable to prompt the user for a variable at runtime.
• Create a script file to produce a series of reports generated by a value supplied at runtime.
• Use a double ampersand substitution variable to prompt the user for a variable at runtime.
• the DEFINE command to define a variable.
• Create a script that uses the ACCEPT command to specify a customized prompt.
• Use the VARIABLE command in SQL*Plus to create a variable.
• Match SET commands to their usage.
• Identify the purpose of the login.sql file.
• Match each SQL*Plus command to its purpose.
• Use the COLUMN command to control the display of a column.
• Use the TTITLE and BTITLE commands to set a page header and a page footer.
• Use the REPHEADER and REPFOOTER commands to set a report header and a report footer.
• Use the BREAK ON command to create a two-level break.
• Use the COMPUTE command to perform a computation on the rows in a subset.
• Create a script to print a formatted report.
• Record the displayed output of a query in a file.

Duration

8

Minimum Requirements

The CDROM version of this course requires:

• At least a 486DX 33Mhz CPU.
• Microsoft Windows 3.1 or higher and a Microsoft compatible mouse.
• At least 8MB RAM.
• At least VGA graphics capability with a minimum 512K video RAM (1MB video RAM recommended).
• At least a double speed CDROM drive.
• An MPC compliant sound card with attached speakers or headphones is recommended (Currently only the CDROM version supports audio).

• At least a 486DX 33Mhz CPU.
• Microsoft Windows 3.1 or higher and a Microsoft compatible mouse.
• At least 8MB RAM and 22MB available hard disk space or file server space.
• At least VGA graphics capability with a minimum 512K video RAM (1MB video RAM recommended).

Media

• Oracle SQL and SQL*Plus: Advanced SELECT Statements