Beginning Oracle SQL : for Oracle Database 12c, Third Edition

__Beginning Oracle SQL__Written in an easygoing and example-based style,is the book that will get you started down the path to successfully writing SQL statements and getting results from Oracle Database.Takes an example-based approach, with clear and authoritative explanations Introduces both SQL and the query tools used to execute SQL statements Shows how to create tables, populate them with data, and then query that data to generate business results Contents at a Glance......Page 3 Contents......Page 414 About the Authors......Page 426 About the Technical Reviewer......Page 428 Acknowledgments......Page 429 Introduction......Page 4 1.1 Information Needs and Information Systems......Page 7 Entities and Attributes......Page 8 Generic vs. Specific......Page 9 Consistency, Integrity, and Integrity Constraints......Page 10 Data Modeling Approach, Methods, and Techniques......Page 11 Information Systems Terms Review......Page 12 Kernel......Page 13 DBMS Tools......Page 14 1.4 Relational Database Management Systems......Page 15 Tables, Columns, and Rows......Page 16 Keys......Page 17 Missing Information and Null Values......Page 18 1.6 Relational Operators......Page 19 1.7 How Relational Is My DBMS?......Page 21 1.8 The Oracle Software Environment......Page 22 1.9 Case Tables......Page 23 The ERM Diagram of the Case......Page 24 Table Descriptions......Page 25 2.1 Overview of SQL......Page 29 Data Manipulation and Transactions......Page 30 Retrieval......Page 31 Security......Page 32 Privileges and Roles......Page 33 GRANT and REVOKE......Page 34 Constants (Literals)......Page 35 Arithmetic Operators......Page 37 Logical Operators......Page 38 Functions......Page 39 Comments......Page 40 Installing and Configuring SQL Developer......Page 41 Connecting to a Database......Page 45 Exploring Objects......Page 46 Schema Browser......Page 47 Entering Commands......Page 48 Run Statement......Page 49 Run Script......Page 50 Saving Commands to a Script......Page 51 Running a Script......Page 52 Exporting Your Data......Page 55 User-Defined Reports......Page 56 Tuning Your SQL......Page 58 Writing PL/SQL......Page 60 Running PL/SQL Code for Testing......Page 61 Debugging Code to Find Errors......Page 62 Data Modeller......Page 64 3.1 Schemas and Users......Page 65 3.2 Table Creation......Page 66 Character Datatype......Page 67 Date Datatype......Page 68 3.4 Commands for Creating the Case Tables......Page 69 3.5 The Data Dictionary......Page 70 4.1 Overview of the SELECT Command......Page 75 4.2 The SELECT Clause......Page 76 Column Aliases......Page 77 The DISTINCT Keyword......Page 78 Column Expressions......Page 79 The DUAL Table......Page 80 4.3 The WHERE Clause......Page 81 4.4 The ORDER BY Clause......Page 82 The OR Operator......Page 85 The AND Operator and Operator Precedence Issues......Page 87 The NOT Operator......Page 88 The BETWEEN Operator......Page 89 The IN Operator......Page 90 The LIKE Operator......Page 91 4.7 CASE Expressions......Page 92 4.8 Subqueries......Page 95 When a Subquery Returns Too Many Values......Page 96 Comparison Operators in the Joining Condition......Page 97 When a Single-Row Subquery Returns More Than One Row......Page 98 Null Value Display......Page 99 The Nature of Null Values......Page 100 The IS NULL Operator......Page 101 Null Values and the Equality Operator......Page 102 Null Value Pitfalls......Page 103 4.10 Truth Tables......Page 104 4.11 Exercises......Page 105 5.1 Overview of Functions......Page 107 5.2 Arithmetic Functions......Page 109 5.3 Text Functions......Page 112 5.4 Regular Expressions......Page 115 Regular Expression Operators and Metasymbols......Page 116 Influencing Matching Behavior......Page 117 REGEXP_LIKE......Page 118 REGEXP_REPLACE......Page 119 5.5 Date Functions......Page 120 ROUND and TRUNC......Page 121 MONTHS_BETWEEN and ADD_MONTHS......Page 122 5.6 General Functions......Page 123 DECODE......Page 124 5.7 Conversion Functions......Page 125 TO_NUMBER and TO_CHAR......Page 126 Conversion Function Formats......Page 127 CAST......Page 129 5.8 Stored Functions......Page 130 Function in WITH Clause of Query......Page 131 5.9 Exercises......Page 132 Chapter 6: Data Manipulation......Page 134 Standard INSERT Commands......Page 135 INSERT Using Subqueries......Page 137 6.2 The UPDATE Command......Page 140 6.3 The DELETE Command......Page 142 6.4 The MERGE Command......Page 145 6.5 Transaction Processing......Page 147 Read Consistency......Page 149 7.1 The CREATE TABLE Command......Page 151 7.2 More on Datatypes......Page 153 Comparison Semantics......Page 154 7.3 The ALTER TABLE and RENAME Commands......Page 155 Out-of-Line Constraints......Page 158 Constraint Definitions in the Data Dictionary......Page 160 Case Table Definitions with Constraints......Page 161 A Solution for Foreign Key References: CREATE SCHEMA......Page 163 Deferrable Constraints......Page 164 7.5 Indexes......Page 165 Bitmap Indexes......Page 166 Index Management......Page 167 7.6 Performance Monitoring with SQL Developer AUTOTRACE......Page 169 7.7 Sequences......Page 172 7.8 Synonyms......Page 173 7.9 The CURRENT_SCHEMA Setting......Page 175 7.10 The DROP TABLE Command......Page 176 7.11 The TRUNCATE Command......Page 177 7.12 The COMMENT Command......Page 178 7.13 Exercises......Page 179 8.1 Tuple Variables......Page 181 8.2 Joins......Page 183 Equijoins......Page 184 Non-equijoins......Page 185 Joins of Three or More Tables......Page 186 Self-Joins......Page 187 8.3 The JOIN Clause......Page 188 Natural Joins......Page 189 Equijoins on Columns with the Same Name......Page 190 8.4 Outer Joins......Page 191 New Outer Join Syntax......Page 192 Outer Joins and Performance......Page 193 8.5 The GROUP BY Component......Page 194 Multiple-Column Grouping......Page 195 8.6 Group Functions......Page 196 Group Functions and Duplicate Values......Page 197 Grouping the Results of a Join......Page 198 The COUNT(*) Function......Page 199 Valid SELECT and GROUP BY Clause Combinations......Page 201 HAVING Clauses Without Group Functions......Page 202 A Classic SQL Mistake......Page 203 Grouping on Additional Columns......Page 204 GROUP BY ROLLUP......Page 206 CUBE, ROLLUP, and Null Values......Page 207 The GROUPING_ID Function......Page 208 8.9 Partitioned Outer Joins......Page 210 8.10 Set Operators......Page 212 8.11 Exercises......Page 214 9.1 Subqueries Continued......Page 216 The ANY and ALL Operators......Page 217 Rewriting SQL Statements Containing ANY and ALL......Page 218 Correlated Subqueries......Page 219 Subqueries Following an EXISTS Operator......Page 221 EXISTS, IN, or JOIN?......Page 222 NULLS with NOT EXISTS and NOT IN......Page 224 9.2 Subqueries in the SELECT Clause......Page 225 9.3 Subqueries in the FROM Clause......Page 226 9.4 The WITH Clause......Page 227 9.5 Hierarchical Queries......Page 229 START WITH and CONNECT BY......Page 230 LEVEL, CONNECT_BY_ISCYCLE, and CONNECT_BY_ISLEAF......Page 231 Hierarchical Query Result Sorting......Page 232 9.6 Analytic Functions......Page 234 Partitions......Page 236 Function Processing......Page 238 9.7 Row Limiting......Page 240 9.8 Flashback Features......Page 242 AS OF......Page 243 VERSIONS BETWEEN......Page 244 FLASHBACK TABLE......Page 245 9.9 Exercises......Page 246 10.1 What Are Views?......Page 248 10.2 View Creation......Page 249 Creating a View from a Query......Page 250 Getting Information about Views from the Data Dictionary......Page 251 10.3 What Can You Do with Views?......Page 253 Simplifying Data Retrieval......Page 254 10.4 Data Manipulation via Views......Page 256 Updatable Join Views......Page 258 Nonupdatable Views......Page 259 Inserting Invisible Rows......Page 260 Preventing These Two Scenarios......Page 261 Constraint Checking......Page 262 10.6 Views and Performance......Page 263 10.7 Materialized Views......Page 264 Properties of Materialized Views......Page 265 Query Rewrite......Page 266 10.9 Invisible Columns......Page 267 10.10 Exercises......Page 268 Chapter 11: SQL*Plus Basics and Scripting......Page 270 Entering Commands......Page 271 Using the SQL Buffer......Page 272 Using an External Editor......Page 273 Using the SQL*Plus Editor......Page 274 Using SQL Buffer Line Numbers......Page 277 Using the Ellipsis......Page 278 Saving Commands......Page 279 Running SQL*Plus Scripts......Page 281 Specifying Directory Path Specifications......Page 282 Adjusting SQL*Plus Settings......Page 283 Executing Commands from the Operating System......Page 287 SQL*Plus Substitution Variables......Page 288 SQL*Plus User-Defined Variables......Page 291 User-Friendly Prompting......Page 292 SQL*Plus System Variables......Page 293 11.3 Bind Variables......Page 298 Bind Variables in SQL Statements......Page 299 Script Execution......Page 300 Script Parameters......Page 302 SQL*Plus Commands in Scripts......Page 303 11.5 Report Generation with SQL*Plus......Page 305 The SQL*Plus COLUMN Command......Page 306 The SQL*Plus TTITLE and BTITLE Commands......Page 309 The SQL*Plus BREAK Command......Page 310 The SQL*Plus COMPUTE Command......Page 313 The Finishing Touch: SPOOL......Page 315 HTML in SQL*Plus......Page 316 What Is a SQL*Plus Script?......Page 319 Capturing and Using Input Parameter Values......Page 320 Mechanism 2: Bind Variables......Page 321 Handling Error Conditions......Page 322 SQL*Plus Error Logging......Page 324 11.8 Exercises......Page 325 12.1 More Datatypes......Page 326 Methods......Page 327 Creating the Array......Page 328 Querying Array Columns......Page 330 Creating Table Types......Page 332 Populating the Nested Table......Page 333 Querying the Nested Table......Page 334 Creating User-Defined Types......Page 335 Showing More Information with DESCRIBE......Page 336 Which SQL Multiset Operators Are Available?......Page 337 Preparing for the Examples......Page 338 Using POWERMULTISET......Page 339 Using MULTISET UNION......Page 340 12.6 Exercises......Page 341 ERM Diagram......Page 343 Table Structure Descriptions......Page 344 Columns and Foreign Key Constraints......Page 345 Contents of the Seven Tables......Page 346 Hierarchical Employees Overview......Page 350 Course Offerings Overview......Page 351 Chapter 4 Exercises......Page 353 Chapter 5 Exercises......Page 362 Chapter 7 Exercises......Page 367 Chapter 8 Exercises......Page 369 Chapter 9 Exercises......Page 378 Chapter 10 Exercises......Page 386 Chapter 11 Exercises......Page 388 Chapter 12 Exercises......Page 392 Index......Page 396 Beginning Oracle SQL is your introduction to the interactive query tools and specific dialect of SQL used with Oracle Database. These tools include SQL*Plus and SQL Developer. SQL*Plus is the one tool any Oracle developer or database administrator can always count on, and it is widely used in creating scripts to automate routine tasks. SQL Developer is a powerful, graphical environment for developing and debugging queries. Oracle's is possibly the most valuable dialect of SQL from a career standpoint. Oracle's database engine is widely used in corporate environments worldwide. It is also found in many government applications. Oracle SQL implements many features not found in competing products. No developer or DBA working with Oracle can afford to be without knowledge of these features and how they work, because of the performance and expressiveness they bring to the table. Written in an easygoing and example-based style, Beginning Oracle SQL is the book that will get you started down the path to successfully writing SQL statements and getting results from Oracle Database. Takes an example-based approach, with clear and authoritative explanations Introduces both SQL and the query tools used to execute SQL statements Shows how to create tables, populate them with data, and then query that data to generate business results What you'll learn Create database tables and define their relationships. Add data to your tables. Then change and delete that data. Write database queries that generate accurate results. Avoid common traps and pitfalls in writing SQL queries, especially from nulls. Reap the performance and expressiveness of analytic and window functions. Make use of Oracle Database's support for object types. Write recursive queries to query hierarchical data. Who this book is for Beginning Oracle SQL is aimed at developers and database administrators who must write SQL statements to execute against an Oracle database. No prior knowledge of SQL is assumed