Sumário Itens Encontrados: 251IntroductionChapter 1 Relational Database Concepts 1Are Spreadsheets Like Databases? 2Relational Databases 3Tables, Rows, and Columns 4Primary Keys, Datatypes, and Foreign Keys 4Data Modeling 6Object-Relational Databases 8Abstraction 9Methods and Encapsulation 9Inheritance 9Object-Relational Support 10Terms to Know 10Review Questions 11Chapter 2 SQL*Plus and iSQL*Plus Basics 13Some SQL Formalities 14Tools for Running SQL 14SQL*Plus 14iSQL*Plus 17SQL*Plus Worksheet 20Third-Party Tools 21ODBC/JDBC 21OCI 23The Ubiquitous SELECT Statement 23Column Specification 24Column Renaming 26Duplicate Removal 27Expressions 28DML for Making Changes 29The UPDATE Statement 29The INSERT Statement 31The DELETE Statement 32The MERGE Statement 33DDL for Handling Database Objects 34The CREATE Statement 34The ALTER Statement 36The DROP Statement 37The RENAME Statement 37The TRUNCATE Statement 38DCL for Handling Privileges 39The GRANT Statement 39The REVOKE Statement 40Terms to Know 40Review Questions 41Chapter 3 Oracle Database Functions 43Query Basics 44The DUAL Table 44NULLs: What, When, Why, and How. 46String Literals and Concatenating Strings 47Numeric Literals 48Operators and Operator Precedence 48Built-In Single-Row Functions 49String Functions 50Numeric Functions 53Date Functions 56Conversion Functions 58General Functions 61User-Defined Functions 63Terms to Know 66Review Questions 67Chapter 4 Restricting, Sorting, and Grouping Data 69The WHERE Clause 70Comparison Conditions 71AND, OR, and NOT 72BETWEEN, IN, and LIKE 75IS NULL and IS NOT NULL 79The ORDER BY Clause 81Group Functions and the GROUP BY Clause 83Group Functions 84The GROUP BY Clause 85Using NVL with Group Functions 87The HAVING Clause 88Terms to Know 90Review Questions 91Chapter 5 Using Multiple Tables 93Join Syntax: Out with the Old and In with the New (SQL:1999) 94Equijoins 94Pre-Oracle 9i Equijoin Syntax 94Oracle 9i Equijoin Syntax 97Non-equijoins 101Pre-Oracle 9i Non-equijoin Syntax 101Oracle 9i Non-equijoin Syntax 102Outer Joins 103Pre-Oracle 9i Outer Join Syntax 103Oracle 9iOuter Join Syntax 107Self-Joins 110Pre-Oracle 9i Self-Join Syntax 110Oracle 9i Self-Join Syntax 111Cartesian Products: The Black Sheep of the Family 112Pre-Oracle 9i Cartesian Product Syntax 112Oracle 9i Cartesian Product Syntax 113Terms to Know 114Review Questions 115Chapter 6 Advanced SQL Queries 117Subqueries 118Single-Row Subqueries 118Multiple-Row Subqueries 119Correlated Subqueries 121Multiple-Column Subqueries 123Set Operators 124UNION and UNION ALL 124INTERSECT. 129MINUS. 130ROLLUP and CUBE 133ROLLUP. 134CUBE. 135Terms to Know 137Review Questions 138Chapter 7 Logical Consistency 139Constraints 140NOT NULL. 140CHECK. 142UNIQUE. 144PRIMARY KEY. 145FOREIGN KEY. 147Transaction Processing 150The COMMIT Statement 151The ROLLBACK Statement 152The SAVEPOINT Statement 152Terms to Know 153Review Questions 154Chapter 8 Installing Oracle and Creating a Database 155Oracle Components Overview 156Logical Storage Structures 156Physical Storage Structures 158Oracle Memory Structures 160Background Processes 162Installing Oracle Software 163Using the Oracle Universal Installer 163Using the Oracle Enterprise Manager Tools 167Creating an Oracle Database 169Disk and Memory Requirements 169Using the Database Configuration Assistant 169Terms to Know 179Review Questions 180Chapter 9 Reporting Techniques 181iSQL*Plus Configuration 182Interface Configuration 184Script Formatting and System Variables 184Change Password 189Report Formatting 190Headers and Footers 191Column Formatting 194BREAK Processing 195Summary Operations (Totals) 196Substitution Variables 198Saving and Running Scripts 201Terms to Know 204Review Questions 205Chapter 10 Creating and Maintaining Database Objects 207Creating Tables 208Relational Tables 208Create Table As Select (CTAS) 209External Tables 211Temporary Tables 214Creating Indexes 215Creating and Using Views 216User-Defined Views 216Data Dictionary Views 218Dynamic Performance Views 222Creating Sequences and Synonyms 223Sequences 223Synonyms 225Terms to Know 226Review Questions 227Chapter 11 Users and Security 229Creating User Accounts 230Assigning Passwords 230Creating and Assigning Profiles 231Assigning Default Tablespaces and Quotas 232Granting and Revoking Privileges 234System Privileges 234Object Privileges 236Creating and Assigning Roles 238Auditing 240Statement Auditing 240Object Auditing 242Terms to Know 243Review Questions 244Chapter 12 Making Things Run Fast (Enough) 245Oracleâs Tuning Methodology 246Indexes 247When to Create Indexes 247Index Types 247Creating, Dropping, and Maintaining Indexes 250Monitoring Indexes 252Data Dictionary Index Information 254Data Design Tuning 255Partitioned Tables 255Materialized Views 258SQL Application Tuning 259Top SQL Tool 260Explain Plan Graphical Tool 262The Oracle Optimizer 264Memory Tuning 267Terms to Know 270Review Questions 271Chapter 13 Saving Your Stuff (Backups) 273Database Failures 274User Backup and Recovery Methods 274Export and Import for Users 275Flashback Query 280DBA Backup and Recovery Methods 282Export and Import for DBAs 282Cold Backups 287Hot Backups 287Log Miner 289Recovery Manager 290Terms to Know 292Review Questions 293Chapter 14 Troubleshooting 295The Alert Log File 296Locating the Alert Log File 296Viewing the Alert Log File 296Maintaining the Alert Log File 297Event Notification 298Oracle 9i OEM Event Manager 299Oracle 10g Advisory Framework 302System Trace Files 303User Trace Files 305Enabling Tracing 305Locating the User Trace Files 306Converting the Trace File 309Terms to Know 310Review Questions 311Appendix A Answers to Review Questions 313Chapter 1 313Chapter 2 314Chapter 3 315Chapter 4 316Chapter 5 317Chapter 6 318Chapter 7 319Chapter 8 320Chapter 9 321Chapter 10 322Chapter 11 323Chapter 12 324Chapter 13 325Chapter 14 326Appendix B Common Database Platforms 327Enterprise Databases 327Oracle 327IBM DB2/UDB 327Sybase 328Microsoft SQL Server 328Personal and Freeware Databases 328Microsoft Access 328MySQL 329Glossary 331Index 339