Database Systems Using Oracle: A Simplified Guide to SQL and PL/SQL

Description

COVER CONTENTS FOREWORD FOREWORD PREFACE The Reader The Text The Software Using the Text Acknowledgments Part 1: Database Concepts CHAPTER 1 DATABASE CONCEPTS: A RELATIONAL APPROACH Database: An Introduction Relationships Database Management System (DBMS) The Relational Database Model Integrity Rules Theoretical Relational Languages In a Nutshell . . . Exercise Questions CHAPTER 2 DATABASE DESIGN: DATA MODELING AND NORMALIZATION Data Modeling Dependency Database Design Normal Forms Dependency Diagrams Denormalization Another Example of Normalization In a Nutshell . . . Exercise Questions Part 2: Oracle SQL CHAPTER 3 ORACLE9i: AN OVERVIEW Personal Databases Client/Server Databases Oracle9i: An Introduction The SQL*Plus Environment Structured Query Language (SQL) Logging in to SQL* Plus SQL*Plus Commands Oracle Errors and Online Help Alternate Text Editors SQL*Plus Worksheet iSQL* Plus Sample Databases In a Nutshell . . . Exercise Questions Lab Activity CHAPTER 4 ORACLE TABLES: DATA DEFINITION LANGUAGE (DDL) Naming Rules and Conventions Data Types Constraints Creating an Oracle Table Displaying Table Information Altering an Existing Table Dropping a Table Renaming a Table Truncating a Table Oracle’s Various Table Types Spooling Error Codes In a Nutshell . . . Exercise Questions Lab Activity CHAPTER 5 WORKING WITH TABLES: DATA MANAGEMENT AND RETRIEVAL Data Manipulation Language (DML) Adding a New Row/Record Customized Prompts Updating Existing Rows/Records Deleting Existing Rows/Records Retrieving Data from a Table Arithmetic Operations Restricting Data with a WHERE Clause Sorting Revisiting Substitution Variables DEFINE Command CASE Structure In a Nutshell . . . Exercise Questions Lab Activity CHAPTER 6 WORKING WITH TABLES: FUNCTIONS AND GROUPING Built-In Functions Grouping Data In a Nutshell . . . Exercise Questions Lab Activity CHAPTER 7 MULTIPLE TABLES: JOINS AND SET OPERATORS Join Set Operators In a Nutshell . . . Exercise Questions Lab Activity CHAPTER 8 SUBQUERIES: NESTED QUERIES Subquery Top-N Analysis MERGE Statement Correlated Subquery In a Nutshell . . . Exercise Questions Lab Activity CHAPTER 9 ADVANCED FEATURES: OBJECTS, TRANSACTIONS, AND DATA CONTROL Views Sequences Synonyms Index ROWID Pseudocolumn Transactions Locking Rows for Update Controlling Access In a Nutshell . . . Exercise Questions Lab Activity SQL REVIEW: SUPPLEMENTARY EXAMPLES Script for Creation of Tables Script for Insertion of Rows into Tables Insertion of Rows with Substitution Variables Part 3: PL/SQL CHAPTER 10 PL/SQL: A PROGRAMMING LANGUAGE A Brief History of PL/SQL Fundamentals of PL/SQL PL/SQL Block Structure Comments Data Types Other Data Types Variable Declaration Anchored Declaration Assignment Operation Bind Variables Substitution Variables in PL/SQL Printing in PL/SQL Arithmetic Operators In a Nutshell . . . Exercise Questions Lab Activity CHAPTER 11 MORE ON PL/SQL: CONTROL STRUCTURES AND EMBEDDED SQL Control Structures Nested Blocks SQL in PL/SQL Data Manipulation in PL/SQL Transaction Control Statements In a Nutshell . . . Exercise Questions Lab Activity CHAPTER 12 PL/SQL CURSORS AND EXCEPTIONS Cursors Implicit Cursors Explicit Cursors Explicit Cursor Attributes Implicit Cursor Attributes Cursor FOR Loops SELECT . . . FOR UPDATE Cursor WHERE CURRENT OF Clause Cursor with Parameters Cursor Variables: An Introduction Exceptions Types of Exceptions More Sample Programs In a Nutshell . . . Exercise Questions Lab Activity CHAPTER 13 PL/SQL COMPOSITE DATA TYPES: RECORDS, TABLES, AND VARRAYS Composite Data Types PL/SQL Records PL/SQL Tables PL/SQL Varrays In a Nutshell . . . Exercise Questions Lab Activity CHAPTER 14 PL/SQL NAMED BLOCKS: PROCEDURE, FUNCTION, PACKAGE, AND TRIGGER Procedures Functions Packages Triggers Data Dictionary Views In a Nutshell . . . Exercise Questions Lab Activity Part 4: Miscellaneous Topics CHAPTER 15 ORACLE WITH JAVA: A TUTORIAL ON JDBC AND SQLj Java: A Programming Language JDBC Sun’s JDBC Driver and the Oracle Data Source OracleDriver and Oracle thin Driver Java Applet: Putting It All Together SQLj Host Variables SQLj Iterators PL/SQL from SQLj In a Nutshell . . . Exercise Questions Lab Activity CHAPTER 16 ORACLE9i: ARCHITECTURE AND ADMINISTRATION Database Administrator (DBA) Oracle Architecture: An Overview Installation Connecting to the Oracle9i Database Instance and Database Working with Oracle Enterprise Manager (OEM) System Privileges Oracle Data Dictionary In a Nutshell . . . Exercise Questions APPENDIX A: SAMPLE DATABASES: TABLE DEFINITIONS The Indo–US (IU) College Student Database The NamanNavan (N2) Corporation Employee Database APPENDIX B: QUICK REFERENCE TO SQL AND PL/SQL SYNTAX SQL Key Words PL/SQL Key Words SQL and PL/SQL Syntax Creating a Table Column-Level Constraint Table-Level Constraint Adding a Column to an Existing Table Modifying an Existing Column Adding a Constraint to a Table Dropping a Column (Oracle8 Onward) Setting a Column as Unused (Oracle8 Onward) Dropping an Unused Column (Oracle8 Onward) Renaming a Column (Oracle9i Onward) Renaming a Constraint (Oracle9i Onward) Dropping a Table Renaming a Table Truncating a Table Inserting a New Row into a Table Customized Prompts Updating Rows Deleting Rows Dropping a Constraint Enabling|Disabling a Constraint Retrieving Data from a Table DEFINE Command DECODE Function CASE Structure Joining Tables: Equijoin or Outer Join Set Operation SELECT Subquery Creating a Table Using a Subquery Inserting a Row Using a Subquery Inserting into Multiple Tables Updating Using a Subquery Deleting Using a Subquery Top-N Query MERGE Statement Creating a View Altering a View Dropping a View Creating a Sequence Modifying a Sequence Creating a Synonym Dropping a Synonym Creating an Index Rebuilding an Index Locking Rows for Update Creating a User Changing a User’s Password Granting System Privileges Granting Object Privileges Revoking Privileges PL/SQL Anonymous Block PL/SQL Variable/Constant Declaration Anchored Variable Declaration Assignment Operation IF-THEN-END IF IF-THEN-ELSE-END IF IF-THEN-ELSIF-END IF CASE Statement Basic Loop WHILE Loop FOR Loop Bind/Host Variable SELECT-INTO in PL/SQL Explicit Cursor Declaration Opening an Explicit Cursor Fetching a Row from an Explicit Cursor Closing an Explicit Cursor Cursor FOR Loop Cursor FOR Loop with a Subquery WHERE CURRENT OF Clause Cursor with SELECT-FOR UPDATE Cursor with Parameters REF CURSOR Type Opening a Cursor Variable Fetching from a Cursor Variable EXCEPTION Section PRAGMA EXCEPTION_INIT Directive RAISE_APPLICATION_ERROR Procedure Creating a PL/SQL Record Declaring a PL/SQL Table Declaring a PL/SQL Varray PL/SQL Procedure Calling a Procedure Recompiling a Procedure PL/SQL Function PL/SQL Package Specification PL/SQL Package Body PL/SQL Trigger Creating a Tablespace Starting Up an Instance Shutting Down an Instance Creating a User from the Command Line with Various Clauses Dropping a User Logging into SQL*PLUS from the Command Line APPENDIX C: REFERENCE TO SQL*Plus COMMANDS SQL*Plus Editing Commands SQL*Plus File-Related Commands APPENDIX D: OBJECT ORIENTATION An Object SQL Queries for Objects Retrieving Data from an Object Table Inserting a Row into an Object Table Updating an Object Deleting Rows from on Object Table APPENDIX E: WHAT’S NEW IN ORACLE9i SQL AND PL/SQL? New Features in SQL New Features in PL/SQL APPENDIX F: ADDITIONAL REFERENCES Web Sites Books and Other Published Material INDEX A B C D E F G H I J K L M N O P R S T U V W