ORACLE DATABASE FOUNDATIONS

By DANIEL LOPEZ

CONTENTS CHAPTER RELATIONAL DATABASE CONCEPTS ARE SPREADSHEETS LIKE DATABASES? RELATIONAL DATABASES TABLES, ROWS, AND COLUMNS PRIMARY KEYS, DATATYPES, AND FOREIGN KEYS DATA MODELING OBJECT-RELATIONAL DATABASES ABSTRACTION METHODS AND ENCAPSULATION INHERITANCE OBJECT-RELATIONAL SUPPORT TERMS TO KNOW REVIEW QUESTIONS CHAPTER SQL*PLUS AND ISQL*PLUS BASICS SOME SQL FORMALITIES TOOLS FOR RUNNING SQL SQL*PLUS ISQL*PLUS SQL*PLUS WORKSHEET THIRD-PARTY TOOLS ODBC/JDBC OCI THE UBIQUITOUS SELECT STATEMENT COLUMN SPECIFICATION COLUMN RENAMING DUPLICATE REMOVAL EXPRESSIONS DML FOR MAKING CHANGES THE UPDATE STATEMENT THE INSERT STATEMENT THE DELETE STATEMENT THE MERGE STATEMENT DDL FOR HANDLING DATABASE OBJECTS THE CREATE STATEMENT THE ALTER STATEMENT THE DROP STATEMENT THE RENAME STATEMENT THE TRUNCATE STATEMENT DCL FOR HANDLING PRIVILEGES THE GRANT STATEMENT THE REVOKE STATEMENT ORACLE DATABASE FUNCTIONS QUERY BASICS THE DUAL TABLE NULLS: WHAT, WHEN, WHY, AND HOW STRING LITERALS AND CONCATENATING STRINGS NUMERIC LITERALS OPERATORS AND OPERATOR PRECEDENCE BUILT-IN SINGLE-ROW FUNCTIONS STRING FUNCTIONS NUMERIC FUNCTIONS DATE FUNCTIONS CONVERSION FUNCTIONS GENERAL FUNCTIONS USER-DEFINED FUNCTIONS RESTRICTING, SORTING, AND GROUPING DATA THE WHERE CLAUSE COMPARISON CONDITIONS AND, OR, AND NOT BETWEEN, IN, AND LIKE IS NULL AND IS NOT NULL THE ORDER BY CLAUSE GROUP FUNCTIONS AND THE GROUP BY CLAUSE GROUP FUNCTIONS THE GROUP BY CLAUSE USING NVL WITH GROUP FUNCTIONS THE HAVING CLAUSE USING MULTIPLE TABLES JOIN SYNTAX: OUT WITH THE OLD AND IN WITH THE NEW (SQL:1999) EQUIJOINS PRE-ORACLE9I EQUIJOIN SYNTAX ORACLE9I EQUIJOIN SYNTAX NON-EQUIJOINS PRE-ORACLE9I NON-EQUIJOIN SYNTAX ORACLE9I NON-EQUIJOIN SYNTAX OUTER JOINS PRE-ORACLE9I OUTER JOIN SYNTAX ORACLE9I OUTER JOIN SYNTAX SELF-JOINS PRE-ORACLE9I SELF-JOIN SYNTAX ORACLE9I SELF-JOIN SYNTAX CARTESIAN PRODUCTS: THE BLACK SHEEP OF THE FAMILY PRE-ORACLE9I CARTESIAN PRODUCT SYNTAX ORACLE9I CARTESIAN PRODUCT SYNTAX ADVANCED SQL QUERIES SUBQUERIES SINGLE-ROW SUBQUERIES MULTIPLE-ROW SUBQUERIES CORRELATED SUBQUERIES MULTIPLE-COLUMN SUBQUERIES SET OPERATORS UNION AND UNION ALL INTERSECT MINUS ROLLUP AND CUBE ROLLUP CUBE LOGICAL CONSISTENCY CONSTRAINTS NOT NULL CHECK UNIQUE PRIMARY KEY FOREIGN KEY TRANSACTION PROCESSING THE COMMIT STATEMENT THE ROLLBACK STATEMENT THE SAVEPOINT STATEMENT INSTALLING ORACLE AND CREATING A DATABASE ORACLE COMPONENTS OVERVIEW LOGICAL STORAGE STRUCTURES PHYSICAL STORAGE STRUCTURES ORACLE MEMORY STRUCTURES BACKGROUND PROCESSES INSTALLING ORACLE SOFTWARE USING THE ORACLE UNIVERSAL INSTALLER USING THE ORACLE ENTERPRISE MANAGER TOOLS CREATING AN ORACLE DATABASE DISK AND MEMORY REQUIREMENTS USING THE DATABASE CONFIGURATION ASSISTANT REPORTING TECHNIQUES ISQL*PLUS CONFIGURATION INTERFACE CONFIGURATION SCRIPT FORMATTING AND SYSTEM VARIABLES CHANGE PASSWORD REPORT FORMATTING HEADERS AND FOOTERS COLUMN FORMATTING BREAK PROCESSING SUMMARY OPERATIONS (TOTALS) SUBSTITUTION VARIABLES SAVING AND RUNNING SCRIPTS CREATING AND MAINTAINING DATABASE OBJECTS CREATING TABLES RELATIONAL TABLES CREATE TABLE AS SELECT (CTAS) EXTERNAL TABLES TEMPORARY TABLES CREATING INDEXES CREATING AND USING VIEWS USER-DEFINED VIEWS DATA DICTIONARY VIEWS DYNAMIC PERFORMANCE VIEWS CREATING SEQUENCES AND SYNONYMS SEQUENCES SYNONYMS USERS AND SECURITY CREATING USER ACCOUNTS ASSIGNING PASSWORDS CREATING AND ASSIGNING PROFILES ASSIGNING DEFAULT TABLESPACES AND QUOTAS GRANTING AND REVOKING PRIVILEGES SYSTEM PRIVILEGES OBJECT PRIVILEGES CREATING AND ASSIGNING ROLES AUDITING STATEMENT AUDITING OBJECT AUDITING MAKING THINGS RUN FAST (ENOUGH) ORACLE’S TUNING METHODOLOGY INDEXES WHEN TO CREATE INDEXES INDEX TYPES CREATING, DROPPING, AND MAINTAINING INDEXES MONITORING INDEXES DATA DICTIONARY INDEX INFORMATION DATA DESIGN TUNING PARTITIONED TABLES MATERIALIZED VIEWS SQL APPLICATION TUNING TOP SQL TOOL EXPLAIN PLAN GRAPHICAL TOOL THE ORACLE OPTIMIZER MEMORY TUNING SAVING YOUR STUFF (BACKUPS) DATABASE FAILURES USER BACKUP AND RECOVERY METHODS EXPORT AND IMPORT FOR USERS FLASHBACK QUERY DBA BACKUP AND RECOVERY METHODS EXPORT AND IMPORT FOR DBAS COLD BACKUPS HOT BACKUPS LOG MINER RECOVERY MANAGER TROUBLESHOOTING THE ALERT LOG FILE LOCATING THE ALERT LOG FILE VIEWING THE ALERT LOG FILE MAINTAINING THE ALERT LOG FILE EVENT NOTIFICATION ORACLE9I OEM EVENT MANAGER ORACLE 10G ADVISORY FRAMEWORK SYSTEM TRACE FILES USER TRACE FILES ENABLING TRACING LOCATING THE USER TRACE FILES CONVERTING THE TRACE FILE