Course Overview

Learn 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.

Skills-Based

Audience

Application Developers, System Analysts, Database Administrators, Technical Support Personnel. Prior knowledge of SQL and first course in this series, Oracle SQL Specifics: Retrieving and Formatting Data (Course 60116).

Total Learning Time

6 - 8 Hour(s)

Course Contents

• 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.

• 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.

• 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.

• 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.

• 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.
• Use 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.

Click here for Secure Yahoo Order: Oracle SQL Specifics: Creating and Managing Database Objects

Get Train in Oracle
Oracle Training CD

Best Regards,
Oracle Database, SQL, Application, Programming Tips