Relational Database Systems

Jitendra Inc. Patel

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.

Sujets

• Langages
• Computers
• Informatique
• SQL

Informations

• Publié par: eBookIt.com
• Date de parution: 21 février 2013
• Nombre de lectures: 0
• EAN13: 9781456611743
• Langue: English

Extrait

Relational Database Management System
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.
Copyright 2012 © Jitendra Patel
All rights reserved. No part of this book shall be reproduced, stored in a retrieval system, or transmitted by any means, electronic, mechanical, photocopying, recording, or otherwise, without written permission from the publisher. No patent liability is assumed with respect to the use of the information contained herein. Although every precaution has been taken in the preparation of this book, the publisher and author assume no responsibility for errors or omissions. Nor is any liability assumed for damages resulting from the use of the information contained herein.
Table of Contents
CHAPTER 1 INTRODUCTION
Objectives .
Data and Information . 20
RELATIONAL DATABASE .. 20
Database Management Systems (DBMS) 22
ORACLE's RELATIONAL DBMS . 22
SQL and Oracle’s SQL*Plus . 23
Relational Operations . 24
Select Operation . 25
Project Operation . 25
Join Operation . 26
USING SQL*PLUS . 27
Starting SQL*Plus . 27
Exiting SQL*Plus . 29
Running (Executing) a Command File . 29
Storing Commands to a Command File . 30
Copying and Pasting Commands . 31
Inserting Remarks in a File . 32
Syntax Conventions . 32
SQL Keywords . 34
SQL Naming Conventions . 34
Overview of Select STATEMENT Syntax . 35
SUMMARY .. 35
REVIEW EXERCISES . 35
Learn These Terms . 35
Concepts Quiz . 36
SQL Coding Exercises and Questions . 36
CHAPTER 2 CREATING TABLES AND INDEXES
Objectives . 38
TABLE CREATION .. 39
Naming Tables and Columns . 39
Selecting a Datatype . 39
Character Data . 40
Numeric Data . 41
DATE Data . 43
Large Object (LOB) Data . 43
CREATING A TABLE .. 43
DATA INTEGRITY AND TABLE CONSTRAINTS . 44
NOT NULL Constraint 45
PRIMARY KEY Constraint 45
CHECK Constraint 46
UNIQUE Constraint 46
COMMANDS TO MANAGE, DROP, AND ALTER TABLES . 47
Viewing a Table Description . 47
Dropping a Table . 48
Renaming a Table . 48
Adding and Altering a Column for an Existing Table . 48
RELATING TABLES—IDENTIFYING FOREIGN KEYS . 49
MAINTAINING REFERENTIAL INTEGRITY .. 51
COMPOSITE PRIMARY KEYS AND MULTIPLE FOREIGN KEYS . 52
POPULATING TABLES WITH DATA .. 54
The INSERT Command . 54
The DELETE Command . 55
The COMMIT and ROLLBACK Commands . 55
The UPDATE Command . 55
INDEXES . 56
PRIMARY KEY Indexes . 56
Creating an Index . 57
Creating a Unique Index . 57
Creating a Composite Index . 57
Dropping Indexes . 58
SUMMARY .. 58
REVIEW EXERCISES . 59
Learn These Terms . 59
Concepts Quiz . 60
SQL Coding Exercises and Questions . 61
CHAPTER 3 SINGLE TABLE QUERY BASICS
Objectives . 63
SIMPLE SELECT STATEMENTS . 63
Indenting SQL Code . 65
• SELECT . 66
• FROM ... 66
• WHERE .. 66
• GROUP BY .. 66
• HAVING .. 66
• ORDER BY .. 66
Selecting Specific Columns . 66
USING COLUMN COMMANDS TO FORMAT OUTPUT . 67
COMMON ERRORS . 69
Invalid Column Name . 69
FROM Keyword Missing . 69
Unknown Command – Invalid Command Structure . 70
Errors in Placing Commas . 70
THE DISTINCT CLAUSE: ELIMINATING DUPLICATE ROWS . 71
SELECTING ROWS: THE WHERE CLAUSE .. 73
Comparison Operators . 74
Comparing Character Data . 74
ORDERING ROWS: THE ORDER BY CLAUSE .. 75
Sort Order 76
Characters, in ASCII Order . 76
ORDER BY with ASC and DESC Options . 77
ORDER BY with More Than One Column . 77
SUMMARY .. 79
REVIEW EXERCISES . 79
Learn These Terms . 79
Concepts Quiz . 80
SQL Coding Exercises and Questions . 81
CHAPTER 4 ADDING POWER TO QUERIES
Objectives . 83
LOGICAL OPERATORS (AND, OR, AND NOT) 83
Using the AND Operator 84
Using the OR Operator 85
USING the NOT Operator 86
Combining OR and AND Operators . 87
LISTS (IN AND NOT IN) 89
Using the IN Operator 89
Using the NOT IN Operator 91
Common Errors When Using IN and NOT IN Operators . 91
RANGES (BETWEEN AND NOT BETWEEN) 92
Using the BETWEEN Operator 92
Specifying More Than One Range of Values . 93
Using the NOT BETWEEN Operator 93
Common Errors When Using BETWEEN and NOT BETWEEN Operators . 94
CHARACTER MATCHING (LIKE AND NOT LIKE) 94
UNKNOWN VALUES (IS NULL AND IS NOT NULL) 97
USING EXPRESSIONS IN SELECT CLAUSES . 99
SUMMARY .. 103
REVIEW EXERCISES . 103
Learn These Terms . 103
Concepts Quiz . 104
SQL Coding Exercises and Questions . 105
CHAPTER 5 AGGREGATE ROW FUNCTIONS
Objectives . 108
IMPLEMENTING AGGREGATE FUNCTIONS . 108
Function Syntax . 109
Function Use . 109
Using the AVG Function . 110
Using the Sum (SUM) Function . 111
Using the Minimum (MIN) and Maximum (MAX) Functions . 112
Using the COUNT Function . 113
USING GROUP BY WITH AGGREGATE FUNCTIONS . 113
Using GROUP BY with Expressions . 115
Nesting Aggregate Functions: MAX(AVG( )) 116
GROUP BY and NULL Values . 117
Using GROUP BY with a WHERE Clause . 118
Using GROUP BY with an ORDER BY Clause . 118
Using GROUP BY with a HAVING Clause . 119
Standard SQL Rules . 120
SQL e xtensions to GROUP BY and HAVING Clauses . 122
GROUPING AND SUMMARIZING .. 122
Using HAVING Without a GROUP BY Clause . 123
SUMMARY .. 123
REVIEW EXERCISES . 123
Learn These Terms . 123
Concepts Quiz . 124
SQL Coding Exercises and Questions . 125
CHAPTER 6 JOINS
Objectives . 127
A TYPICAL JOIN OPERATION .. 127
HOW JOINS ARE PROCESSED .. 130
JOIN OPERATION RULES . 132
JOINS and the SELECT Clause . 132
JOINS and the FROM Clause . 133
JOINS and the WHERE Clause . 133
Qualifying Column Names and Aliases . 133
JOIN—Relational Operators . 135
Additional WHERE Clause Options . 135
JOIN Operations Using Inequality Operators (<, >, <>) 136
COMPLEX JOIN OPERATIONS . 137
Joining Three Tables . 137
Joining Four Tables . 140
Joining Tables by Using Two Columns . 142
OUTER JOIN Operations . 143
Types of OUTER JOIN .. 143
OUTER JOINS and NULL Values . 145
LEFT and RIGHT OUTER JOIN Operations . 145
SELF-JOIN Operations . 146
Natural Join . 148
SUMMARY .. 149
REVIEW EXERCISES . 150
Learn These Terms . 150
Concepts Quiz . 150
SQL Coding Exercises and Questions . 151
CHAPTER 7 SUBQUERIES
Objectives . 155
A SUBQUERY EXAMPLE .. 155
DEFINITION OF A SUBQUERY .. 157
Formal Definition . 157
Subquery Types . 157
Subquery Syntax—General Rules . 158
Subquery Restrictons . 158
Clause Restrictions . 158
Data Type Join Compatibility . 159
Other Restrictions . 160
SUBQUERIES and the IN Operator 160
IN Operator 160
NOT IN Operator 162
MULTIPLE LEVELS OF NESTING .. 163
Subqueries and Comparison Operators . 164
Comparison Operators and Errors . 164
Aggregate Functions and Comparison Operators . 166
Comparison Operators Modified with the ALL or ANY Keywords . 166
CORRELATED SUBQUERIES . 170
Subqueries and the EXISTS operator 171
Subqueries and the ORDER BY Clause . 173
SUMMARY .. 174
REVIEW EXERCISES . 174
Learn These Terms . 174
Concepts Quiz . 175
SQL Coding Exercises and Questions . 176
CHAPTER 8 VIEWS, SYNONYMS, AND SEQUENCES
179
Objectives . 179
VIEWS . 179
CREATING A VIEW ... 181
CREATE VIEW Syntax . 181
Example CREATE VIEW Commands . 182
FUNCTIONS AND VIEWS: JOIN VIEWS . 183
DROPPING VIEWS . 184
VIEW STABILITY .. 184
INSERTING, UPDATING, AND DELETING TABLE ROWS THROUGH VIEWS . 186
Inserting Rows . 186
Updating Rows . 187
Deleting Rows . 188
CREATING A VIEW WITH ERRORS . 189
CREATING A MATERIALIZED VIEW ... 189
ALTER VIEW ... 190
A Summary of VIEW Facts . 190
SYNONYMS . 191
Creating Synonyms . 192
Dropping Synonyms . 193
Renaming Synonyms . 193
SEQUENCES . 194
Creating Sequences . 194
A Create Sequence Example . 195
Accessing Sequence Values . 196
Altering a Sequence . 198
Viewing Sequence Properties . 198
Dropping a Sequence . 199
SUMMARY .. 199
REVIEW EXERCISES . 199
Learn These Terms . 199
Concepts Quiz . 200
SQL Coding Exercises and Questions . 200
CHAPTER 9 SQL*PLUS REPORTS
205
Objectives . 205
AN SQL*Plus PROGRAM COMMAND File . 205
CONTROL BREAK REPORTs . 210
The BREAK Command . 212
Output Style . 213
SKIP and PAGE Keywords . 214
The COMPUTE Command . 214
The SPOOL Command . 216
REVISITING THE BREAK AND COMPUTE COMMANDS . 217
Additional BREAK Command Details . 217
Viewing Current BREAK and COMPUTE Command Settings . 217
CREATING MASTER-DETAIL REPORTS . 218
Using Views in Master-Detail Reports . 220
USING VARIABLES FOR INTERACTIVE REPORTING .. 222
Displaying the Current Date in Titles . 224
The NEW_VALUE Clause and Variables . 225
The ACCEPT, PROMPT and PAUSE Commands . 225
The Multiline TTITLE Command . 225
Using a Substitution Variable in a WHERE Clause . 226
ADDITIONAL SQL*PLUS FEATURES . 226
Defining User Variables . 226
Passing Parameter Values Through the START Command . 227
Formatting Aggregate Function Titles . 228
Changing Column Headings . 229
SUMMARY .. 230
REVIEW EXERCISES . 230
Learn These Terms . 230
Concepts Quiz . 231
SQL Coding Exercises and Questions . 232
CHAPTER 10 ADDITIONAL FUNCTIONS
237
Objectives . 237
General notation . 237
CHARACTER (String) Functions . 238
UPPER, LOWER, and INITCAP Functions . 239
LENGTH Function . 240
SUBSTR and CO