Sumário Itens Encontrados: 345Download the JDK 24Create a Connection 28Create a New User 31Create a Connection with New User 36Create a Table 37Insert Some Data 40LiveSQL 44Summary 49Part II: Viewing Data 51Chapter 3: Retrieving Data 53How Can You See the Data? 53What Is the SELECT Statement? 53A Few Things to Note 54Our First SELECT Statement 55Running a Statement in SQL Developer 56Results 60Run Script 61Errors in SQL 62Missing Keyword 62Table Does Not Exist 63Summary 64Chapter 4: Selecting Specific Columns 65Selecting a Single Column 66Selecting Multiple Columns 71Selecting All Columns 73SELECT Columns or SELECT *? 75Summary 76Chapter 5: Restricting the Results 77Why Restrict Data? 77What is the WHERE Clause? 78Example with a Text Value 78Example with a Number Value 81Finding Rows Not Equal to a Value 82Greater Than 83Greater Than or Equal To 84Less Than 85Less Than or Equal To 86Best Practices 87Specify Columns on the Left 87Select Only What You Need 87Summary 88Chapter 6: Comparing Data 89What Is a Partial Match? 89Syntax of LIKE 90The Percent Wildcard Character 90The Underscore Wildcard Character 92Searching for Wildcard Characters 95Performance 96Summary 97Chapter 7: Applying Multiple Filters 99Why Use Multiple Filters? 99The AND Keyword 100Finding Records That Match a Last Name and a Salary 100Finding Records that Match Greater Than and Less Than on Two Columns 101The OR Keyword 102Finding Records that Match a Last Name or a Salary 102Finding Records that Match Greater Than or Less Than on Two Columns 103viMore Than Two Conditions 104Multiple AND Conditions 104Multiple OR Conditions 105Combining Multiple AND and OR Conditions 105Specifying the Order with Multiple Conditions 106Summary 108Chapter 8: Working with Nulls 109Missing Data 109What is NULL? 111How Should NULL Be Treated? 111Examples of Working with NULL 112Adding More Data 112Selecting the Records 118Restricting the NULL Values 120Hiding Null Values 122Multiple Queries in One SQL Window 123Summary 125Chapter 9: Removing Duplicate Results 127Add New Records 128Select the Data 130What is DISTINCT? 131Finding Unique Last Name Values 132Finding Unique Salary Values 133Finding Unique Combinations of Values 134Summary 135Chapter 10: Applying Filters on Lists and Ranges of Values 137Too Many Conjunctions 137The IN Keyword 139Example of Using IN 139A Longer Value List 140viiUsing IN with Text Values 141Using IN with Other Clauses 142IN with AND? 142IN with LIKE? 143Filtering on Ranges of Values 144The BETWEEN Operator 145Using Between with Two Salary Values 146Using BETWEEN When Values Donât Match 147Using BETWEEN with Text Values 147An Example of an Inclusive and Exclusive Check 148Should You Use BETWEEN? 149Summary 149Chapter 11: Ordering Your Data 151Results Are Not Ordered 151Ordering Results with ORDER BY 152ORDER BY Examples 153Order by a Text Value 153Order by a Number Value 154Order by a Column Not in the SELECT Clause 156Order by a Number 156ORDER BY and NULLs 158Order by Multiple Columns 159Do You Really Need to Order Your Data? 161Summary 162Chapter 12: Applying Table and Column Aliases 163What is a Table Alias? 163Example of a Table Alias 164Intellisense or AutoComplete 165Longer Table Aliases 167What If I Donât Use a Table Alias? 168What Is a Column Alias? 168Example of a Column Alias 169The AS Keyword 171Mathematical Operations and Column Aliases 172Addition 173Subtraction 175Multiplication 176Division 177Column Aliases with Table Aliases 178Summary 179Part III: Adding, Updating, Deleting Data 181Chapter 13: Understanding the Data Types 183What is a Data Type? 183Why Do We Have Different Data Types? 184What Are the Different Data Types? 185Text Data Types 185CHAR 186VARCHAR2 186NCHAR 187NVARCHAR2 188RAW 189LONG RAW 189Number Data Types 189NUMBER 190INTEGER 190FLOAT 191DECIMAL 191BINARY_FLOAT 191BINARY_DOUBLE 192Date Data Types 192ixDATE 192TIMESTAMP 192TIMESTAMP WITH TIME ZONE 193TIMESTAMP WITH LOCAL TIME ZONE 193INTERVAL YEAR TO MONTH 193INTERVAL DAY TO SECOND 194Other Data Types 194BLOB 194CLOB 195Data Type Recommendations 195Summary 195Chapter 14: Creating a Table 197Creating Tables Using SQL Code 197The CREATE TABLE Statement 198Our Employee Table 199Storing Office Details 200The Office Table 203The Sales Meeting Table 206Primary Key 206Recording the Employee Again 209Foreign Key 209Summary 212Chapter 15: Adding Data to a Table 213The INSERT Statement 213Running an INSERT Statement 214Inserting More Data 216Inserting Date Values 218Saving and Undoing Changes 221Best Practices for Inserting Data 223Summary 224xChapter 16: Updating and Removing Data 225The UPDATE Statement 225Update to a New Value 226Checking an Update Statement Before Running It 228Update a NULL Value 229Update Based on Existing Value 230Update a Date Value 231Viewing and Updating the Date Format 232Update Two Columns 233Update Without WHERE Clause 234Removing Data from a Table 235Deleting a Record 236Check What Records Will Be Deleted 237Delete All Records in a Table 238Summary 238Chapter 17: Updating or Deleting a Table 239Why Update the Structure of a Table? 239The ALTER TABLE Statement 240Example: Adding a Column 241Example: Change a Data Type 242Example: Add a Primary Key 243Example: Add a Foreign Key 245Example: Rename a Column 246Example: Remove a Column 247Example: Rename a Table 248Removing a Table with DROP TABLE 249Summary 250xiPart IV: Joining Tables 251Chapter 18: Inner Join 253Multiple Tables 253What is a Join? 254An Example of a Join 255Joins, Formatting, and Table Aliases 257An INNER JOIN 260Summary 262Chapter 19: Outer Join 263What is an Outer Join? 263Writing a Left Outer Join 264Showing All Employees with a Left Outer Join 266Writing a Right Outer Join 268When Would You Use a Right Outer Join? 270Writing a Full Outer Join 270Using a Full Outer Join on Our Tables 272Summary 273Chapter 20: Other Join Types 275The USING Keyword 275Updating the Office Table 276Writing a Query with the USING Keyword 277What is a Natural Join? 280Writing a Query with a Natural Join 280What is a Cross Join? 282Using a CROSS JOIN 284Alternative Join Syntax 285Inner Join 285Outer Join 286Why You Shouldnât Use the Alternative Join Syntax 288Summary 289Chapter 21: Joining Many Tables 291Joining Three Tables 291Joining Our Tables Together 293Using Outer Join Types 294Mixing Join Types 296Joining Four or More Tables 297Summary 299Part V: Functions 301Chapter 22: Using functions in SQL 303What is a Function? 303Where Can You Use Functions? 304Simple Numeric Calculations 304The DUAL Table 308Number Functions 311Concatenation of Strings 314String Functions 317Changing Case 317Checking for Matches on the Same Case 319Getting Part of a String 322Calculations on Dates 323Date Functions 327Current Date and Time 327Adding Months 328Summary 330Chapter 23: Writing Conditional Logic 331The CASE Statement 331Simple Case Statement 333Searched Case Statement 337The DECODE Function 342CASE or DECODE? 344Summary 345Chapter 24: Understanding Aggregate Functions 347Aggregate Functions 348The SUM Function 348A Simple SUM Example 349SUM with WHERE 350SUM with Expression 350SUM with DISTINCT 351The COUNT Function 351Counting All Records 352Count a Specific Column 352Count with DISTINCT 354Count with WHERE 356The AVG Function 357Average of All Values 357Average with DISTINCT 358Average with WHERE 359The MIN Function 360Minimum of All Records 360Minimum with WHERE 362The MAX Function 362Maximum of All Records 362Maximum with WHERE 363Summary 364xivChapter 25: Grouping Your Results 365Grouping Your Data 365The GROUP BY Keyword 367GROUP BY with a Join 370GROUP BY and SUM 370GROUP BY with WHERE 371Restricting Results After Grouping 372The HAVING Clause 374GROUP BY, HAVING, and SUM 376Finding Duplicate Records 376Summary 378Chapter 26: What Are Indexes? 379What Is an Index? 379How to Create an Index 380Example of Creating an Index 380Why Is Performance Important? 384Web Application 384Overnight Batch Jobs 384Reporting System 385Index Using a Join 385Creating an Index 387The Disadvantages of Indexes 388Best Practices for Creating Indexes 388Summary 389Part VI: Command Line 391Chapter 27: Using the Command Line 393What is SQL*Plus and Why Would You Use It? 394Itâs Fast 394Itâs Easy to Run Scripts 394Itâs Available on Every Oracle Database 395You Donât Always Have Access to SQL Developer 395How to Start SQL*Plus 395Alternative Login Syntax 397Login with Two Steps 397Log in with One Step 398Running a Query in SQL*Plus 399Formatting Output in SQL*Plus 401Copy and Paste into SQL*Plus 404The Forward Slash Character 404Exiting SQL*Plus 405What About SQLcl? 406How to Download and Run SQLcl 407Summary 410Part VII: Appendixes 411Appendix: How to Find and Navigate the Oracle SQL Reference 413Finding the Oracle SQL Reference 413The Oracle SQL Reference 415Find What You Need Using   417Find What You Need Using Search 419Index 423Table of Conten