Oracle SQL and SQL*Plus: DML and DDL

Doc-To-Help Standard Manual

National Education Training Group, Inc.

Copyright © 1998 National Education Training Group, Inc. All rights reserved. No part of the material protected by this copyright may be reproduced or utilized in any form or by any means, electronic or mechanical, including photocopying, recording, broadcasting, or by any information storage and retrieval system, without permission in writing from National Education Training Group, Inc.

Skill Builder is a registered tradem 858g619i ark of National Education Training Group, Inc. All other trademarks referenced are the trademark, service mark, or registered trademark of their respective holders. National Education Training Group, Inc. is not affiliated with any company or any other product or vendor mentioned in this course and its accompanying materials.

The software and technology used in the CD-ROM portion of the course contain trade secrets that NETG considers to be confidential and proprietary information, and your right to use this material is subject to the restrictions in the license agreement under which you obtained it.

Objectives

Tables

Tables: An Introduction

Identify the features of the table structures.

Tables: Referencing

Identify the rules to reference another user's table.

Tables: Naming Rules

Identify the rules for naming database tables.

Create Table Command

Sequence the syntax elements of the CREATE TABLE command in their correct order.

Datatypes and Constraints

Oracle7 Datatypes

Match the Oracle7 datatypes with their descriptions.

Constraints: An Introduction

Identify the uses of constraints in Oracle7.

Constraints: Types

Match the different types of constraints with their description.

Constraint Levels

Identify the description of constraint levels.

Defining Constraints

NOT NULL Constraints: Defining

Complete the SQL statement to define a NOT NULL constraint, given the table definition.

UNIQUE Constraints: Defining

Complete the SQL statement to define a UNIQUE constraint.

PRIMARY KEY Constraints: Declaring

Complete the SQL statement to declare a PRIMARY KEY, given a partial table definition and column specifications.

FOREIGN KEY Constraints: Defining

Complete the SQL statement to define a FOREIGN KEY constraint with the cascade option.

CHECK Constraints: Defining

Complete the SQL statement to define a check constraint.

Tables: Creation and Confirmation

Table: Creating From an Instance Chart

Create a table from a table instance chart.

Table: Based on Another Table

Create a table by using a subquery.

Table Creation: Confirming

Display the structure of a table by using the DESCRIBE command.

Data Dictionary Views

Oracle Data Dictionary

Identify the contents of Oracle data dictionary.

Data Dictionary Views: Types

Match the different data dictionary views in Oracle7 with their description.

Data Dictionary Queries

Data Dictionary: Listing Views

Display the information about data dictionary views.

Data Dictionary: Using

Query the Oracle data dictionary by issuing the Select statement.

Constraints: Displaying

Display the columns about constraints by querying the USER_CONSTRAINT data dictionary view.

Constraints on a Table: Checking

Display the names of the columns bound by constraints by querying the data dictionary views.

Inserting Data

Transaction Control and DML Commands

Match the SQL commands with their functions.

Rows in a Table: Inserting

Add new rows in a table by using a SQL command.

Special Values: Inserting

Insert special values in a table by using the SQL functions.

Manipulating Row Data

Rows From Another Table: Copying

Copy rows from a table by using a subquery.

Rows: Updating

Update existing rows in a table by using a SQL command.

Table: Updating All Rows

Update all rows in a table by using a SQL command.

Table: Deleting Rows

Delete rows from a table by using a SQL statement.

Transaction Processing

Transactions: An Introduction

Identify the circumstances that ends a transaction.

Database Transactions

Identify when a transaction is controlled in Oracle7.

Committing Changes

Identify the effects of the COMMIT command.

Rolling Back Changes

Identify the effects of the ROLLBACK command.

Rolling Back Changes to a Savepoint

Discard the changes made to a table by using a SQL command.

Altering Columns in a Table

Column: Adding

Add a column to a table using an SQL command.

Column: Modifying

Modify a column definition using an SQL command.

Altering Constraints

Constraint: Adding

Add a constraint to a table using an SQL command.

Constraint: Dropping

Drop a constraint from a table using the SQL command.

Constraints: Disabling and Enabling

Manage constraints by using an SQL statement.

Altering Tables

Table: Dropping

Remove a table from the Oracle database using an SQL statement.

Table: Renaming

Rename a table using an SQL command.

Table: Truncating

Remove all the rows from a table using a DDL command.

Comments: Managing

Manage comments using an SQL command.

Creating and Using Sequences

Sequence: An Introduction

Identify the features of a sequence.

Sequence: Creating

Create a sequence by using the SQL command.

Sequence: Caching Sequence Values

Identify the features of the caching sequence values.

Sequences: Viewing

Display the sequence values from a data dictionary table by using the SELECT statement.

Sequence: Retrieving Numbers

Identify the description of the pseudocolumns.

Managing Sequences

Sequence: Modifying

Modify a sequence by using the SQL command.

Sequence: Removing

Remove a sequence from the data dictionary by using the SQL command.

Managing Views

View: An Introduction

Identify the advantages of views.

CREATE VIEW Command

Match the different parameters in the CREATE VIEW command syntax with their descriptions.

Simple View: Creating

Create a simple view by using the CREATE VIEW command.

Complex View: Creating

Create a complex view that contains group functions to display values from two tables.

View: Removing

Remove a view from the an Oracle database by using a SQL statement.

DML Operations

Views: Rules for DML Operations

Identify the rules for performing DML operations through a view.

WITH CHECK OPTION Clause

Create a view by using the WITH CHECK OPTION clause to restrict the DML operations for the records specified in the WHERE condition.

DML Operations: Denying

Deny the occurrence of DML operations using the WITH READ ONLY option in the view definition.

View Definitions: Displaying

Display the names and the definition of the views by querying the USER_VIEWS data dictionary object at the SQL prompt.

Index Structure

Index: An Introduction

Identify the characteristics of an index.

B*Tree Index Structure

Identify the features of the B*tree index structure.

Index Types

Match the different index types with their description.

Managing Indexes

Indexes: Creating

Create an index using the CREATE INDEX command.

Displaying Data Dictionary Information

View the existence of the indexes in the USER_INDEXES data dictionary view.

Index: Removing

Remove an index from the data dictionary using the SQL command.

Managing System Privileges

Database Security Model

Identify the functions of database security.

User: Creating

Create a user by using the CREATE USER command.

Password: Changing

Change the password for a user by using the ALTER USER command.

System Privileges: Granting

Grant system privileges to a user by using the SQL command.

Roles: Creating

Create a role for granting privileges to a user from the SQL prompt.

Object Privileges

Object Privileges: Granting

Grant object privileges to the user from the SQL prompt.

Privileges: WITH GRANT OPTION

Grant a user an object privilege using the WITH GRANT OPTION.

Granted Privileges: Confirm

Match the privilege-related views in the data dictionary with their description.

Object Privileges: Revoking

Revoke the privileges granted to a user by using the SQL command.

Synonym: Creating

Create a synonym for an object using the SQL command.

Synonym: Removing

Remove a synonym using the SQL command.