SQL: A Beginner's Guide, Second Edition

This book teaches core or basic SQL, that is, the subset of the language that's common to all of the many database products. That's a hard thing to do well, because the common core is small compared to the wide variety of vendor-specific extensions. But it's worth doing, and this book does it well. Approaching SQL for the first time, I used the book to get a foundation of understanding and get up and running creating tables, constraints, and views, and constructing queries, joins, and subqueries. The book's explanations are clear, and the selection of material seems good. The end-of-chapter review questions are helpful. The exercises are helpful and essential, but I found them to be somewhat too mechanical, and there should be more of them to help cement each topic before moving on to the next. The book's vendor-neutral approach has its costs. Some of the features that it describes, such as catalogs and roles, aren't implemented in all database products, so you have to try them with your database or consult your documentation to find out. The book is very clear about this, though; I never found an example that didn't work that wasn't flagged as not supported in all products. And an alternative approach is always provided. As another reviewer pointed out, the book describes the crucial 'Hello World' task of connecting to the database server and submitting your first SQL statement in only general terms, since it depends on your database product and client. And unfortunately one very important set of tasks is vendor-specific and therefore not covered here: finding out what databases, users, and roles are present, and what the structure of each database and table is. In summary, if you know you want to use SQL with a particular database product, you'd probably do better to get a book that's specific to that product. But if you want to understand and use the core of SQL before you move on to your vendor's extentions, this is a good book to get you there. SQL A Beginner’s Guide......Page 4 About the Author......Page 6 Contents at a Glance......Page 8 Contents......Page 10 Acknowledgments......Page 18 Introduction......Page 20 What Content the Book Covers......Page 21 Description of the Book¡ ̄s Content......Page 22 Module Content......Page 25 I SQL Databases......Page 30 1 Introduction to Relational Databases and SQL......Page 32 Critical Skill 1.1 Understand Relational Databases......Page 33 Project 1-1 Normalizing Data and Identifying Relationships......Page 41 The Relational Model......Page 34 Progress Check......Page 39 The SQL Evolution......Page 43 Critical Skill 1.3 Use a Relational Database Management System......Page 48 Types of SQL Statements......Page 45 Types of Execution......Page 46 Progress Check......Page 47 SQL Standard Versus Product Implementations......Page 49 Project 1-2 Connecting to a Database......Page 51 Module 1 Mastery Check......Page 54 2 Working with the SQL Environment......Page 56 Critical Skill 2.1 Understand the SQL Environment......Page 57 Critical Skill 2.2 Understand SQL Catalogs......Page 59 Schemas......Page 60 Schema Objects......Page 61 Ask the Expert......Page 63 Then What Is a Database?......Page 64 Progress Check......Page 65 Critical Skill 2.3 Name Objects in an SQL Environment......Page 66 Qualified Names......Page 67 Critical Skill 2.4 Create a Schema......Page 68 Progress Check......Page 70 Critical Skill 2.5 Create a Database......Page 71 Project 2-1 Creating a Database and a Schema......Page 72 Module 2 Mastery Check......Page 73 3 Creating and Altering Tables......Page 76 Critical Skill 3.1 Create SQL Tables......Page 77 Critical Skill 3.2 Specify Column Data Types......Page 81 Progress Check......Page 78 String Data Types......Page 82 Numeric Data Types......Page 84 Datetime Data Types......Page 85 Interval Data Type......Page 86 Boolean Data Type......Page 88 Ask the Expert......Page 89 Progress Check......Page 90 Critical Skill 3.3 Create User-Defined Types......Page 91 Critical Skill 3.4 Specify Column Default Values......Page 92 Progress Check......Page 93 Project 3-1 Creating SQL Tables......Page 94 Critical Skill 3.5 Alter SQL Tables......Page 96 Critical Skill 3.6 Delete SQL Tables......Page 97 Ask the Expert......Page 98 Project 3-2 Altering and Deleting SQL Tables......Page 99 Module 3 Mastery Check......Page 100 4 Enforcing Data Integrity......Page 102 Critical Skill 4.1 Understand Integrity Constraints......Page 103 Critical Skill 4.2 Use NOT NULL Constraints......Page 105 Critical Skill 4.3 Add UNIQUE Constraints......Page 106 Critical Skill 4.4 Add PRIMARY KEY Constraints......Page 109 Critical Skill 4.5 Add FOREIGN KEY Constraints......Page 112 The MATCH Clause......Page 117 The Clause......Page 119 Progress Check......Page 121 Critical Skill 4.6 Define CHECK Constraints......Page 125 Creating Domains and Domain Constraints......Page 128 Progress Check......Page 129 Project 4-2 Adding a CHECK Constraint......Page 130 Module 4 Mastery Check......Page 131 5 Creating SQL Views......Page 134 Critical Skill 5.1 Add Views to the Database......Page 135 Defining SQL Views......Page 141 Critical Skill 5.2 Create Updateable Views......Page 145 Using the WITH CHECK OPTION Clause......Page 148 Progress Check......Page 149 Ask the Expert......Page 150 Project 5-1 Adding Views to Your Database......Page 151 Module 5 Mastery Check......Page 152 6 Managing Database Security......Page 154 Critical Skill 6.1 Understand the SQL Security Model......Page 155 SQL Sessions......Page 156 Accessing Database Objects......Page 159 Critical Skill 6.2 Create and Delete Roles......Page 162 Critical Skill 6.3 Grant and Revoke Privileges......Page 163 Revoking Privileges......Page 167 Critical Skill 6.4 Grant and Revoke Roles......Page 170 Revoking Roles......Page 171 Project 6-1 Managing Roles and Privileges......Page 172 Project Summary......Page 173 Module 6 Mastery Check......Page 174 II Data Access and Modification......Page 176 7 Querying SQL Data......Page 178 Critical Skill 7.1 Use a SELECT Statement to Retrieve Data......Page 179 The SELECT Clause and FROM Clause......Page 180 Ask the Expert......Page 181 Critical Skill 7.2 Use the WHERE Clause to Define Search Conditions......Page 186 Defining the WHERE Clause......Page 190 Progress Check......Page 193 Critical Skill 7.2 Use the WHERE Clause to Define Search Conditions......Page 194 Critical Skill 7.4 Use the HAVING Clause to Specify Group Search Conditions......Page 199 Critical Skill 7.5 Use the ORDER BY Clause to Sort Query Results......Page 201 Ask the Expert......Page 203 Project 7-1 Querying the Inventory Database......Page 204 Module 7 Mastery Check......Page 207 8 Modifying SQL Data......Page 210 Critical Skill 8.1 Insert SQL Data......Page 211 Inserting Values from a SELECT Statement......Page 215 Critical Skill 8.2 Update SQL Data......Page 217 Updating Values from a SELECT Statement......Page 220 Ask the Expert......Page 221 Progress Check......Page 222 Critical Skill 8.3 Delete SQL Data......Page 223 Project 8-1 Modifying SQL Data......Page 224 Module 8 Mastery Check......Page 227 9 Using Predicates......Page 230 Critical Skill 9.1 Compare SQL Data......Page 231 Using the BETWEEN Predicate......Page 235 Critical Skill 9.2 Return Null Values......Page 237 Critical Skill 9.3 Return Similar Values......Page 240 Progress Check......Page 243 Project 9-1 Using Predicates in SQL Statements......Page 244 Critical Skill 9.4 Reference Additional Sources of Data......Page 246 Using the IN Predicate......Page 247 Using the EXISTS Predicate......Page 250 Ask the Expert......Page 252 Critical Skill 9.5 Quantify Comparison Predicates......Page 253 Using the SOME and ANY Predicates......Page 254 Ask the Expert......Page 256 Project 9-2 Using Subqueries in Predicates......Page 257 Module 9 Mastery Check......Page 259 10 Working with Functions and Value Expressions......Page 262 Using the COUNT Function......Page 263 Using the MAX and MIN Functions......Page 265 Using the AVG Function......Page 268 Progress Check......Page 269 Working with String Value Functions......Page 270 Working with Datetime Value Functions......Page 273 Progress Check......Page 274 Working with Numeric Value Expressions......Page 275 Using the CASE Value Expression......Page 278 Ask the Expert......Page 280 Using the CAST Value Expression......Page 281 Progress Check......Page 282 Critical Skill 10.4 Use Special Values......Page 283 Project 10-1 Using Functions and Value Expressions......Page 284 Module 10 Mastery Check......Page 287 11 Accessing Multiple Tables......Page 290 Critical Skill 11.1 Perform Basic Join Operations......Page 291 Using Correlation Names......Page 294 Creating Joins with More than Two Tables......Page 295 Creating the Cross Join......Page 296 Creating the Self- Join......Page 297 Critical Skill 11.2 Join Tables with Shared Column Names......Page 299 Creating the Natural Join......Page 300 Critical Skill 11.3 Use the Condition Join......Page 301 Creating the Inner Join......Page 302 Creating the Outer Join......Page 304 Progress Check......Page 307 Critical Skill 11.4 Perform Union Operations......Page 308 Project 11-1 Querying Multiple Tables......Page 310 Module 11 Mastery Check......Page 312 12 Using Subqueries to Access and Modify Data......Page 314 Critical Skill 12.1 Create Subqueries That Return Multiple Rows......Page 315 Using the IN Predicate......Page 316 Using the EXISTS Predicate......Page 317 Using Quantified Comparison Predicates......Page 318 Critical Skill 12.2 Create Subqueries That Return One Value......Page 320 Progress Check......Page 321 Critical Skill 12.4 Use Nested Subqueries......Page 323 Critical Skill 12.3 Work with Correlated Subqueries......Page 322 Critical Skill 12.5 Use Subqueries to Modify Data......Page 325 Using Subqueries to Insert Data......Page 326 Using Subqueries to Update Data......Page 327 Project 12-1 Working with Subqueries......Page 328 Module 12 Mastery Check......Page 332 III Advanced Data Access......Page 334 13 Creating SQL-Invoked Routines......Page 336 Critical Skill 13.1 Understand SQL-Invoked Routines......Page 337 SQL- Invoked Procedures and Functions......Page 338 Working with the Basic Syntax......Page 339 Progress Check......Page 340 Critical Skill 13.2 Create SQL-Invoked Procedures......Page 341 Invoking SQL- Invoked Procedures......Page 342 Progress Check......Page 343 Critical Skill 13.3 Add Input Parameters to Your Procedures......Page 344 Using Procedures to Modify Data......Page 347 Critical Skill 13.4 Add Local Variables to Your Procedures......Page 348 Create Compound Statements......Page 350 Create Conditional Statements......Page 351 Ask the Expert......Page 353 Project 13-1 Creating SQL-Invoked Procedures......Page 355 Critical Skill 13.6 Add Output Parameters to Your Procedures......Page 357 Progress Check......Page 358 Critical Skill 13.7 Create SQL-Invoked Functions......Page 359 Project 13-2 Creating SQL-Invoked Functions......Page 361 Module 13 Mastery Check......Page 362 14 Creating SQL Triggers......Page 366 Critical Skill 14.1 Understand SQL Triggers......Page 367 Trigger Execution Context......Page 368 Progress Check......Page 369 Critical Skill 14.2 Create SQL Triggers......Page 370 Referencing Old and New Values......Page 371 Critical Skill 14.3 Create Insert Triggers......Page 373 Critical Skill 14.4 Create Update Triggers......Page 376 Ask the Expert......Page 380 Critical Skill 14.5 Create Delete Triggers......Page 381 Project 14-1 Creating SQL Triggers......Page 383 Module 14 Mastery Check......Page 386 15 Using SQL Cursors......Page 390 Critical Skill 15.1 Understand SQL Cursors......Page 391 Declaring and Opening SQL Cursors......Page 392 Progress Check......Page 394 Working with Optional Syntax Elements......Page 395 Creating a Cursor Declaration......Page 399 Progress Check......Page 402 Critical Skill 15.4 Retrieve Data from a Cursor......Page 403 Ask the Expert......Page 408 Using the Positioned UPDATE Statement......Page 409 Using the Positioned DELETE Statement......Page 411 Project 15-1 Working with SQL Cursors......Page 412 Module 15 Mastery Check......Page 415 16 Managing SQL Transactions......Page 418 Critical Skill 16.1 Understand SQL Transactions......Page 419 Critical Skill 16.2 Set Transaction Properties......Page 422 Specifying an Isolation Level......Page 423 Specifying a Diagnostics Size......Page 428 Creating a SET TRANSACTION Statement......Page 429 Critical Skill 16.3 Start a Transaction......Page 430 Critical Skill 16.4 Set Constraint Deferrability......Page 431 Ask the Expert......Page 432 Progress Check......Page 434 Critical Skill 16.5 Create Savepoints in a Transaction......Page 435 Critical Skill 16.6 Terminate a Transaction......Page 437 Committing a Transaction......Page 438 Rolling Back a Transaction......Page 439 Project 16-1 Working with Transactions......Page 440 Module 16 Mastery Check......Page 443 17 Accessing SQL Data from Your Host Program......Page 446 Critical Skill 17.1 Invoke SQL Directly......Page 447 Critical Skill 17.2 Embed SQL Statements in Your Program......Page 448 Creating an Embedded SQL Statement......Page 450 Using Host Variables in Your SQL Statements......Page 452 Retrieving SQL Data......Page 453 Ask the Expert......Page 454 Error Handling......Page 456 Project 17-1 Embedding SQL Statements......Page 458 Critical Skill 17.3 Create SQL Client Modules......Page 461 Defining SQL Client Modules......Page 462 Critical Skill 17.4 Use an SQL Call-Level Interface......Page 464 Allocating Handles......Page 466 Executing SQL Statements......Page 469 Working with Host Variables......Page 470 Retrieving SQL Data......Page 471 Project 17-2 Using the SQL Call-Level Interface......Page 472 Module 17 Mastery Check......Page 475 IV Appendixes......Page 478 Module 1: Introduction to Relational Databases and SQL......Page 481 Module 2: Working with the SQL Environment......Page 482 Module 3: Creating and Altering Tables......Page 484 Module 4: Enforcing Data Integrity......Page 487 Module 5: Creating SQL Views......Page 489 Module 6: Managing Database Security......Page 491 Module 7: Querying SQL Data 1.......Page 493 Module 8: Modifying SQL Data......Page 495 Module 9: Using Predicates......Page 498 Module 10: Working with Functions and Value Expressions......Page 500 Module 11: Accessing Multiple Tables......Page 502 Module 12: Using Subqueries to Access and Modify Data ......Page 504 Module 13: Creating SQL-Invoked Routines......Page 505 Module 14: Creating SQL Triggers......Page 507 Module 15: Using SQL Cursors......Page 510 Index......Page 544 Here is the foundation upon which you can build your relational database design and programming, then apply those skills to any SQL-based product. Organized for beginners, you'll learn step-by-step how to create a database, create tables, enforce data integrity, query and modify data, and implement advanced data access techniques.--From publisher description Here is the foundation upon which you can build your relational database design and programming, then apply those skills to any SQL-based product. Carefully organized for beginners, you’ll learn step-by-step how to create a database, create tables, enforce data integrity, query and modify data, and implement advanced data access techniques. In 1999, the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO) published their long-awaited SQL standard, which was dubbed "SQL:1999" (also known as SQL3).