Sumário Itens Encontrados: 384Preface xivChapter 1 Overview of Database Concepts 1Chapter 2 Basic SQL SELECT Statements 25Chapter 3 Table Creation and Management 57Chapter 4 Constraints 103Chapter 5 Data Manipulation and Transaction Control 141Chapter 6 Additional Database Objects 181Chapter 7 User Creation and Management 225Chapter 8 Restricting Rows and Sorting Data 255Chapter 9 Joining Data from Multiple Tables 295Chapter 10 Selected Single-Row Functions 347Chapter 11 Group Functions 401Chapter 12 Subqueries and MERGE Statements 449Chapter 13 Views 495Appendix A Tables for the JustLee Books Database 539Appendix B SQL*Plus and SQL Developer Overview 547Appendix C Oracle Resources 555Appendix D SQL*Loader 557Appendix E SQL Tuning Topics 561Appendix F SQL in Various Databases 579Glossary 583Index 589Introduction 2Database Terminology 2Database Management System 3Database Design 4Entity-Relationship (E-R) Model 5Database Normalization 6Relating Tables in the Database 10Structured Query Language (SQL) 12Databases Used in This Textbook 13Basic Assumptions 13Tables in the JustLee Books Database 14Topic Sequence 16Software Used in This Textbook 16Chapter Summary 17Review Questions 17Multiple Choice 18Hands-On Assignments 21Advanced Challenge 22Case Study: City Jail 22Introduction 26Creating the JustLee Books Database 27SELECT Statement Syntax 30Selecting All Data in a Table 31Selecting One Column from a Table 33Selecting Multiple Columns from a Table 34Operations in the SELECT Statement 36Using Column Aliases 36Using Arithmetic Operations 39NULL Values 40Using DISTINCT and UNIQUE 42Using Concatenation 44Chapter Summary 49Chapter 2 Syntax Summary 49Review Questions 50Multiple Choice 51Hands-On Assignments 54Advanced Challenge 55Case Study: City Jail 55Introduction 58Table Design 59Table Creation 63Defining Columns 63Viewing a List of Tables: USER_TABLES 65Viewing Table Structures: DESCRIBE 66Table Creation with Subqueries 70CREATE TABLE ⦠AS Command 70Modifying Existing Tables 72ALTER TABLE ⦠ADD Command 73ALTER TABLE ⦠MODIFY Command 74ALTER TABLE ⦠DROP COLUMN Command 79ALTER TABLE ⦠SET UNUSED/DROP UNUSED COLUMNS Command 80Renaming a Table 83Truncating a Table 85Deleting a Table 86Chapter Summary 91Chapter 3 Syntax Summary 92Review Questions 93Multiple Choice 94Hands-On Assignments 97Advanced Challenge 97Case Study: City Jail 98Introduction 104Creating Constraints 105Creating Constraints at the Column Level 106Creating Constraints at the Table Level 106Using the PRIMARY KEY Constraint 107Using the FOREIGN KEY Constraint 110Using the UNIQUE Constraint 115Using the CHECK Constraint 116Using the NOT NULL Constraint 119Including Constraints During Table Creation 121Adding Multiple Constraints on a Single Column 125Viewing Constraint Information 126Disabling and Dropping Constraints 128Using DISABLE/ENABLE 128Dropping Constraints 129Chapter Summary 131Chapter 4 Syntax Summary 131Review Questions 133Multiple Choice 134Hands-On Assignments 137Advanced Challenge 139Case Study: City Jail 139Introduction 142Inserting New Rows 143Using the INSERT Command 143Handling Virtual Columns 150Handling Single Quotes in an INSERT Value 152Inserting Data from an Existing Table 154Modifying Existing Rows 156Using the UPDATE Command 156Using Substitution Variables 158Deleting Rows 162Using Transaction Control Statements 163COMMIT and ROLLBACK Commands 164SAVEPOINT Command 165Using Table Locks 168LOCK TABLE Command 168SELECT ⦠FOR UPDATE Command 169Chapter Summary 171Chapter 5 Syntax Summary 172Review Questions 173Multiple Choice 173Hands-On Assignments 176Advanced Challenge 177Case Study: City Jail 178Introduction 182Sequences 183Creating a Sequence 184Using Sequence Values 189Setting and Altering Sequence Definitions 192Altering Sequence Definitions 194Removing a Sequence 196Use Identity Columns Instead of Sequences for Primary Key Columns 197Indexes 199B-Tree Indexes 200Bitmap Indexes 206Function-Based Indexes 208Index Organized Tables 209Verifying an Index 210Altering or Removing an Index 211Synonyms 212Deleting a Synonym 215Chapter Summary 216Chapter 6 Syntax Summary 217Review Questions 218Multiple Choice 219Hands-On Assignments 222Advanced Challenge 223Case Study: City Jail 224Introduction 226Data Security 227Creating a User 228Creating Usernames and Passwords 228Assigning User Privileges 230System Privileges 230Granting System Privileges 231Object Privileges 232Granting Object Privileges 232Managing Passwords 236Using Roles 237Creating and Assigning Roles 238Using Predefined Roles 240Using Default Roles 241Enabling Roles After Login 242Viewing Privilege Information 242Removing Privileges and Users 244Revoking Privileges and Roles 244Dropping a Role 246Dropping a User 246Chapter Summary 247Chapter 7 Syntax Summary 247Review Questions 249Multiple Choice 249Hands-On Assignments 253Advanced Challenge 253Case Study: City Jail 254Introduction 256Where Clause Syntax 257Rules for Character Strings 258Rules for Dates 260Comparison Operators 260BETWEEN ... AND Operator 267IN Operator 268LIKE Operator 270Logical Operators 274Treatment of Null Values 277ORDER by Clause Syntax 279Secondary Sort 282Sorting by SELECT Order 284Chapter Summary 285Chapter 8 Syntax Summary 286Review Questions 288Multiple Choice 288Hands-On Assignments 292Advanced Challenge 293Case Study: City Jail 293Introduction 296Cartesian Joins 297Cartesian Join: Traditional Method 298Cartesian Join: JOIN Method 300Equality Joins 301Equality Joins: Traditional Method 303Equality Joins: JOIN Method 308Non-Equality Joins 314Non-Equality Joins: Traditional Method 315Non-Equality Joins: JOIN Method 316Self-Joins 317Self-Joins: Traditional Method 318Self-Joins: JOIN Method 319Outer Joins 320Outer Joins: Traditional Method 321Outer Joins: JOIN Method 324Set Operators 326Chapter Summary 334Chapter 9 Syntax Summary 335Review Questions 337Multiple Choice 338Hands-On Assignments 344Advanced Challenge 345Case Study: City Jail 345Introduction 348Case Conversion Functions 349The LOWER Function 349The UPPER Function 350The INITCAP Function 351Character Manipulation Functions 352The SUBSTR Function 352The INSTR Function 354The LENGTH Function 356The LPAD and RPAD Functions 357The LTRIM and RTRIM Functions 358The REPLACE Function 359The TRANSLATE Function 360The CONCAT Function 360Number Functions 361The ROUND Function 361The TRUNC Function 362The MOD Function 363The ABS Function 364The POWER Function 365Date Functions 365The MONTHS_BETWEEN Function 367The ADD_MONTHS Function 367The NEXT_DAY and LAST_DAY Functions 368The TO_DATE Function 369Rounding Date Values 371Truncating Date Values 372CURRENT_DATE Versus SYSDATE 373Regular Expressions 374Other Functions 377The NVL Function 377The NVL2 Function 380The NULLIF Function 381The TO_CHAR Function 383The DECODE Function 385The CASE Expression 387The SOUNDEX Function 387The TO_NUMBER Function 388The DUAL Table 389Chapter Summary 390Chapter 10 Syntax Summary 390Review Questions 394Multiple Choice 395Hands-On Assignments 399Advanced Challenge 399Case Study: City Jail 400Introduction 402Group Functions 403The SUM Function 404The AVG Function 406The COUNT Function 408The MAX Function 411The MIN Function 412Grouping Data 413Restricting Aggregated Output 417Nesting Functions 421Statistical Group Functions 422The STDDEV Function 422The VARIANCE Function 423Enhanced Aggregation for Reporting 424The GROUPING SETS Expression 427The CUBE Extension 428The ROLLUP Extension 431Pattern Matching 437Chapter Summary 439Chapter 11 Syntax Summary 439Review Questions 441Multiple Choice 442Hands-On Assignments 446Advanced Challenge 447Case Study: City Jail 447Introduction 450Subqueries and Their Uses 451Single-Row Subqueries 451Single-Row Subquery in a WHERE Clause 451Single-Row Subquery in a HAVING Clause 456Single-Row Subquery in a SELECT Clause 457Multiple-Row Subqueries 459The IN Operator 460The ALL and ANY Operators 461Multiple-Row Subquery in a HAVING Clause 465Multiple-Column Subqueries 467Multiple-Column Subquery in a FROM Clause 467Multiple-Column Subquery in a WHERE Clause 469NULL Values 471NVL in Subqueries 471IS NULL in Subqueries 472Correlated Subqueries 473Nested Subqueries 475Subquery Factoring Clause 477DML Actions Using Subqueries 478MERGE Statements 479Chapter Summary 484Chapter 12 Syntax Summary 484Review Questions 486Multiple Choice 486Hands-On Assignments 492Advanced Challenge 493Case Study: City Jail 493Introduction 496Creating a View 498Creating a Simple View 500DML Operations on a Simple View 504Creating a Complex View 508DML Operations on a Complex View with an Arithmetic Expression 508DML Operations on a Complex View Containing Data from Multiple Tables 513DML Operations on a Complex View Containing Functions or Grouped Data 515DML Operations on a Complex View Containing DISTINCT or ROWNUM 517Summary Guidelines for DML Operations on a Complex View 519Dropping a View 519Creating an Inline View 520CROSS and OUTER APPLY Methods for Joins 520TOP-N Analysis 522Creating a Materialized View 527Chapter Summary 531Chapter 13 Syntax Summary 532Review Questions 533Multiple Choice 533Hands-On Assignments 537Advanced Challenge 538Case Study: City Jail 538CUSTOMERS Table 539BOOKS Table 540ORDERS Table 541ORDERITEMS Table 542AUTHOR Table 543BOOKAUTHOR Table 544PUBLISHER Table 545PROMOTION Table 546Introduction 547SQL*Plus 547SQL Developer 551Oracle Academic Initiative (OAI) 555Oracle Certification Program (OCP) 555Oracle Technology Network (OTN) 555International Oracle Users Group (IOUG) 556Introduction 557Read a Fixed File Format 557Read a Delimited File 559Introduction 561Tuning Concepts and Issues 561Identifying Problem Areas in Coding 561Processing and the Optimizer 563The Explain Plan 565Timing Feature 570Selected SQL Tuning Guidelines and Examples 571Avoiding Unnecessary Column Selection 572Index Suppression 573Concatenated Indexes 575Subqueries 576Optimizer Hints 577Introduction 579Suppressing Duplicates 579Locating a Value in a String 580Displaying the Current Date 580Specifying a Default Date Format 580Replacing NULL Values in Text Data 581Adding Time to Dates 581Extracting Values from a String 581Concatenating 582Data Structures 582