Relational Database Systems
Published on 21. February 2013
447 pages
978-1-4566-1174-3 (ISBN)
Description
This book is specially written for students of Computer Engineering (CE) and Information Technology.
Also every one with interest in Database Management System can refer this book to get the knowledge about RDBMS.
It covers virtually most of core features and some of the advanced features of RDBMS for administrator development including more than hands on examples tested through Oracle 9i. Most of code samples are presented in easy to use through Oracle. Throughout the book most of the features are explained through syntax and examples to develop state-of-the-art Database using advanced concepts like E.R. Modeling, Normalization, Transaction management, Security and other authentication features.
Also every one with interest in Database Management System can refer this book to get the knowledge about RDBMS.
It covers virtually most of core features and some of the advanced features of RDBMS for administrator development including more than hands on examples tested through Oracle 9i. Most of code samples are presented in easy to use through Oracle. Throughout the book most of the features are explained through syntax and examples to develop state-of-the-art Database using advanced concepts like E.R. Modeling, Normalization, Transaction management, Security and other authentication features.
More details
Content
- Intro
- Relational Database Management System
- Copyright 2012 © Jitendra Patel
- Table of Contents
- CHAPTER 1 INTRODUCTION
- Objectives
- Data and Information
- RELATIONAL DATABASE
- Database Management Systems (DBMS)
- ORACLE's RELATIONAL DBMS
- SQL and Oracle's SQL*Plus
- Relational Operations
- Select Operation
- Project Operation
- Join Operation
- USING SQL*PLUS
- Starting SQL*Plus
- Exiting SQL*Plus
- Running (Executing) a Command File
- Storing Commands to a Command File
- Copying and Pasting Commands
- Inserting Remarks in a File
- Syntax Conventions
- SQL Keywords
- SQL Naming Conventions
- Overview of Select STATEMENT Syntax
- SUMMARY
- REVIEW EXERCISES
- Learn These Terms
- Concepts Quiz
- SQL Coding Exercises and Questions
- CHAPTER 2 CREATING TABLES AND INDEXES
- Objectives
- TABLE CREATION
- Naming Tables and Columns
- Selecting a Datatype
- Character Data
- CHAR Datatype
- VARCHAR2 Datatype
- LONG Datatype
- Numeric Data
- NUMBER for Integer Values
- NUMBER for Floating-Point Values
- NUMBER for Fixed-Point Values
- DATE Data
- Large Object (LOB) Data
- CREATING A TABLE
- DATA INTEGRITY AND TABLE CONSTRAINTS
- NOT NULL Constraint
- PRIMARY KEY Constraint
- CHECK Constraint
- UNIQUE Constraint
- COMMANDS TO MANAGE, DROP, AND ALTER TABLES
- Viewing a Table Description
- Dropping a Table
- Renaming a Table
- Adding and Altering a Column for an Existing Table
- RELATING TABLES-IDENTIFYING FOREIGN KEYS
- MAINTAINING REFERENTIAL INTEGRITY
- COMPOSITE PRIMARY KEYS AND MULTIPLE FOREIGN KEYS
- POPULATING TABLES WITH DATA
- The INSERT Command
- The DELETE Command
- The COMMIT and ROLLBACK Commands
- The UPDATE Command
- INDEXES
- PRIMARY KEY Indexes
- Creating an Index
- Creating a Unique Index
- Creating a Composite Index
- Dropping Indexes
- SUMMARY
- REVIEW EXERCISES
- Learn These Terms
- Concepts Quiz
- SQL Coding Exercises and Questions
- CHAPTER 3 SINGLE TABLE QUERY BASICS
- Objectives
- SIMPLE SELECT STATEMENTS
- Indenting SQL Code
- Selecting Specific Columns
- USING COLUMN COMMANDS TO FORMAT OUTPUT
- COMMON ERRORS
- Invalid Column Name
- FROM Keyword Missing
- Unknown Command - Invalid Command Structure
- Errors in Placing Commas
- THE DISTINCT CLAUSE: ELIMINATING DUPLICATE ROWS
- SELECTING ROWS: THE WHERE CLAUSE
- Comparison Operators
- Comparing Character Data
- ORDERING ROWS: THE ORDER BY CLAUSE
- Sort Order
- ORDER BY with ASC and DESC Options
- ORDER BY with More Than One Column
- SUMMARY
- REVIEW EXERCISES
- Learn These Terms
- Concepts Quiz
- SQL Coding Exercises and Questions
- CHAPTER 4 ADDING POWER TO QUERIES
- Objectives
- LOGICAL OPERATORS (AND, OR, AND NOT)
- Using the AND Operator
- Using the OR Operator
- USING the NOT Operator
- Combining OR and AND Operators
- LISTS (IN AND NOT IN)
- Using the IN Operator
- Using the NOT IN Operator
- Common Errors When Using IN and NOT IN Operators
- RANGES (BETWEEN AND NOT BETWEEN)
- Using the BETWEEN Operator
- Specifying More Than One Range of Values
- Using the NOT BETWEEN Operator
- Common Errors When Using BETWEEN and NOT BETWEEN Operators
- CHARACTER MATCHING (LIKE AND NOT LIKE)
- UNKNOWN VALUES (IS NULL AND IS NOT NULL)
- USING EXPRESSIONS IN SELECT CLAUSES
- SUMMARY
- REVIEW EXERCISES
- Learn These Terms
- Concepts Quiz
- SQL Coding Exercises and Questions
- CHAPTER 5 AGGREGATE ROW FUNCTIONS
- Objectives
- IMPLEMENTING AGGREGATE FUNCTIONS
- Using the AVG Function
- Using the Sum (SUM) Function
- Using the Minimum (MIN) and Maximum (MAX) Functions
- Using the COUNT Function
- USING GROUP BY WITH AGGREGATE FUNCTIONS
- Using GROUP BY with Expressions
- Nesting Aggregate Functions: MAX(AVG( ))
- GROUP BY and NULL Values
- Using GROUP BY with a WHERE Clause
- Using GROUP BY with an ORDER BY Clause
- Using GROUP BY with a HAVING Clause
- Standard SQL Rules
- SQL extensions to GROUP BY and HAVING Clauses
- Using HAVING Without a GROUP BY Clause
- SUMMARY
- REVIEW EXERCISES
- Learn These Terms
- Concepts Quiz
- SQL Coding Exercises and Questions
- CHAPTER 6 JOINS
- Objectives
- A TYPICAL JOIN OPERATION
- HOW JOINS ARE PROCESSED
- JOIN OPERATION RULES
- JOINS and the SELECT Clause
- JOINS and the FROM Clause
- JOINS and the WHERE Clause
- Qualifying Column Names and Aliases
- JOIN-Relational Operators
- Additional WHERE Clause Options
- JOIN Operations Using Inequality Operators (&, &, &&)
- COMPLEX JOIN OPERATIONS
- Joining Three Tables
- Joining Four Tables
- Joining Tables by Using Two Columns
- OUTER JOIN Operations
- Types of OUTER JOIN
- OUTER JOINS and NULL Values
- LEFT and RIGHT OUTER JOIN Operations
- SELF-JOIN Operations
- Natural Join
- SUMMARY
- REVIEW EXERCISES
- Learn These Terms
- Concepts Quiz
- SQL Coding Exercises and Questions
- CHAPTER 7 SUBQUERIES
- Objectives
- A SUBQUERY EXAMPLE
- DEFINITION OF A SUBQUERY
- Formal Definition
- Subquery Types
- Subquery Syntax-General Rules
- Subquery Restrictons
- Clause Restrictions
- Data Type Join Compatibility
- Join Compatible Data Columns
- Not Join Compatible
- Other Restrictions
- SUBQUERIES and the IN Operator
- IN Operator
- NOT IN Operator
- MULTIPLE LEVELS OF NESTING
- Subqueries and Comparison Operators
- Comparison Operators and Errors
- Aggregate Functions and Comparison Operators
- Comparison Operators Modified with the ALL or ANY Keywords
- ALL Keyword
- ANY Keyword
- An "= ANY" (Equal Any) Example
- A "!= ANY" (Not Equal Any) Example
- CORRELATED SUBQUERIES
- Subqueries and the EXISTS operator
- Subqueries and the ORDER BY Clause
- SUMMARY
- REVIEW EXERCISES
- Learn These Terms
- Concepts Quiz
- SQL Coding Exercises and Questions
- CHAPTER 8 VIEWS, SYNONYMS, AND SEQUENCES
- Objectives
- VIEWS
- CREATING A VIEW
- CREATE VIEW Syntax
- Example CREATE VIEW Commands
- FUNCTIONS AND VIEWS: JOIN VIEWS
- DROPPING VIEWS
- VIEW STABILITY
- INSERTING, UPDATING, AND DELETING TABLE ROWS THROUGH VIEWS
- Inserting Rows
- Updating Rows
- Deleting Rows
- CREATING A VIEW WITH ERRORS
- CREATING A MATERIALIZED VIEW
- ALTER VIEW
- A Summary of VIEW Facts
- SYNONYMS
- Creating Synonyms
- Dropping Synonyms
- Renaming Synonyms
- SEQUENCES
- Creating Sequences
- A Create Sequence Example
- Accessing Sequence Values
- Altering a Sequence
- Viewing Sequence Properties
- Dropping a Sequence
- SUMMARY
- REVIEW EXERCISES
- Learn These Terms
- Concepts Quiz
- SQL Coding Exercises and Questions
- CHAPTER 9 SQL*PLUS REPORTS
- Objectives
- AN SQL*Plus PROGRAM COMMAND File
- Remarks
- Top and Bottom Titles
- Report Headers and Footers
- Setting the Line and Page Size
- Output to the Computer Monitor Screen
- Select Statement
- CONTROL BREAK REPORTs
- The BREAK Command
- Output Style
- SKIP and PAGE Keywords
- The COMPUTE Command
- The SPOOL Command
- REVISITING THE BREAK AND COMPUTE COMMANDS
- Additional BREAK Command Details
- Viewing Current BREAK and COMPUTE Command Settings
- CREATING MASTER-DETAIL REPORTS
- Using Views in Master-Detail Reports
- USING VARIABLES FOR INTERACTIVE REPORTING
- Executing Program CH9-5.Sql
- Displaying the Current Date in Titles
- The NEW_VALUE Clause and Variables
- The ACCEPT, PROMPT and PAUSE Commands
- The Multiline TTITLE Command
- Formatting the Employee Name
- Using a Substitution Variable in a WHERE Clause
- ADDITIONAL SQL*PLUS FEATURES
- Defining User Variables
- Passing Parameter Values Through the START Command
- Clearing the Computer Monitor Screen
- Formatting Aggregate Function Titles
- Changing Column Headings
- SUMMARY
- REVIEW EXERCISES
- Learn These Terms
- Concepts Quiz
- SQL Coding Exercises and Questions
- CHAPTER 10 ADDITIONAL FUNCTIONS
- Objectives
- General notation
- CHARACTER (String) Functions
- UPPER, LOWER, and INITCAP Functions
- LENGTH Function
- SUBSTR and CONCAT Functions, and Concatenation
- LTRIM, RTRIM, and TRIM Functions
- INSTR Function
- Mathematical FUNCTIONS
- Single-Value Functions
- Transcendental Functions
- NVL Function for NULL Value Substitution
- ABS Function
- POWER and SQRT Functions
- ROUND and TRUNC Functions
- List Functions-GREATEST and LEAST
- CONVERSION FUNCTIONS
- TO_CHAR and TO_DATE Functions
- CAST Function
- DATE Functions
- SYSDATE, CURRENT_DATE, and CURRENT_TIMESTAMP Functions
- Date Arithmetic
- ADD_MONTHS Function
- ROUND and TRUNC Functions with Dates
- DECODE Function
- SUMMARY
- REVIEW EXERCISES
- Learn These Terms
- Concepts Quiz
- SQL Coding Exercises and Questions
- CHAPTER 11 INTRODUCTION TO PL/SQL
- Objectives
- PL/SQL Advantages and Capabilities
- Application Performance
- Productivity, Portability, and Security
- Pre-Defined Packages
- Object-Oriented Programming and Web Applications
- PL/SQL BLOCK STRUCTURE
- How a PL/SQL Program Executes
- PL/SQL Block Sections
- Declaration Section
- Executable Section
- Exception-Handling Section
- Creating and Executing a PL/SQL Program
- Displaying Output
- Using Oracle SQL*Plus to Create/Execute PL/SQL Programs
- Using PuTTY to Create/Execute PL/SQL Programs
- Using SQL Developer to Create/Execute PL/SQL Programs
- Generating Output
- PL/SQL Language Fundamentals
- Terminology
- PL/SQL Character Set
- Identifiers-Naming Constants, Variables, and Other Objects
- Reserved Words
- Delimiters (Operators)
- Literals
- Program Comments
- Common Data Types
- VARCHAR2 and NVARCHAR2
- CHAR and NCHAR
- NUMBER
- PLS_INTEGER
- DATE
- BOOLEAN
- LOB
- TYPE AND ROWTYPE
- SELECT INTO
- SUBSTITUTION VARIABLES
- SUMMARY
- REVIEW EXERCISES
- Learn These Terms
- Concepts Quiz
- PL/SQL Coding Exercises and Questions
- Addendum: Oracle Reserved and Key Words.
- CHAPTER 12 PL/SQL CONTROL STRUCTURES, CURSORS, AND EXCEPTIONS
- Objectives
- CONTROL STRUCTURES
- CONDITIONAL CONTROL
- IF-THEN
- IF-THEN-ELSE
- IF-THEN-ELSIF
- Do Nothing Structure
- ITERATIVE CONTROL
- LOOP
- Labeling Loops
- WHILE-LOOP
- FOR-LOOP
- Sequential Control
- GOTO Statement
- NULL Statement
- CURSORS
- Implicit Cursors
- Explicit Cursors
- Processing a Cursor Using Variables for Column Data
- Processing a Cursor Using a Variable for Row Data
- Updating a Table - WHERE CURRENT OF Clause
- Passing Parameter Values
- Processing Cursors with a FOR Loop
- Exception (Error) Handling
- Coding Common Exceptions
- User-Defined Exception Coding
- CASE Statements
- SUMMARY
- REVIEW EXERCISES
- Learn These Terms
- Concepts Quiz
- PL/SQL Coding Exercises and Questions
- CHAPTER 13 Procedures, Functions, Packages, and Triggers
- Objectives
- PROCEDURES AND FUNCTIONS
- PROCEDURES
- Create Procedure Syntax
- Procedure Parts
- Parameters
- Comparing Anonymous PL/SQL Blocks with Procedures
- Passing IN and OUT Parameter
- Using Bind Variables
- Dropping a Procedure
- FUNCTIONS
- Create Function Syntax
- Dropping a Function
- PACKAGES
- Package Specification and Scope
- Create Package Syntax
- Package Body
- Create Package Body Syntax
- An Example Package
- Calling Package Objects
- Cursors in Packages
- Advantages of Packages
- Database Triggers
- Create Trigger Syntax
- When Triggers Fire
- STATEMENT Trigger Example
- ROW Trigger Example
- WHEN Clause
- Enabling and Disabling Triggers
- Dropping a Trigger
- SUMMARY
- REVIEW EXERCISES
- Learn These Terms
- Concepts Quiz
- PL/SQL Coding Exercises and Questions
