Beginning SQL (Programmer to Programmer)

Taking readers through the basics of the language, right up to some more advanced topics, this book is a practical, hands-on resource and aims to keep the reader involved at all times Focuses on the SQL standard and is loaded with detailed examples and code; each chapter includes practice exercises that readers can challenge themselves with before looking at the sample solutions in the appendix Paul Wilton is a successful Wrox "Beginning" book author and is an ideal author to write for those who want a firm grasp of standard SQL before learning the details specific to a particular database product SQL is an international standard for manipulating data in databases and is used by database programmers in all major database systems: Microsoft, IBM, Oracle, MySQL, and many others Team DDU Beginning SQL 1 Cover 1 About the Authors 8 Contents 12 Acknowledgments 20 Introduction 22 Who This Book Is For 23 What This Book Covers 23 How This Book Is Structured 23 What You Need to Use This Book 24 Conventions 25 Source Code 25 Errata 26 p2p.wrox.com 26 Chapter 1: Introduction to SQL 28 A Brief History of Databases 28 Identifying Databases 29 Why and When to Use a Database 30 Database Management Systems Used in This Book 32 Structured Query Language (SQL) 32 Introducing SQL Queries 32 Comparing SQL to Other Programming Languages 33 Understanding SQL Standards 34 Database Creation 35 Organizing a Relational Database 35 SQL Syntax 37 Creating a Database 38 Understanding Data Types 39 Creating, Altering, and Deleting Tables 46 Creating a Table 46 Altering an Existing Table 47 Deleting an Existing Table 48 Good Database Design 49 Obtaining and Analyzing Your Data Needs 49 Dividing Data Logically 50 Selecting Correct Data Types 53 Using a Primary Key 54 Creating the Example Database 56 Summary 60 Exercises 61 Chapter 2: Entering Information 62 Inserting New Data 62 Inserting Data into the Case Study Database 66 Updating Data 66 The WHERE Clause 68 The Logical Operators AND and OR 69 Deleting Data 70 Summary 71 Exercises 72 Chapter 3: Extracting Information 74 The SELECT Statement 74 Returning Only Distinct Rows 76 Using Aliases 77 Filtering Results with the WHERE Clause 77 Logical Operators and Operator Precedence 83 Introducing Operator Precedence 83 Using Logical Operators 86 NOT Operator 87 BETWEEN Operator 87 LIKE Operator 91 IN Operator 94 Ordering Results with ORDER BY 96 Joining Columns-Concatenation 103 MS SQL Server and MS Access 103 Oracle and IBM DB2 106 MySQL 109 Selecting Data from More Than One Table 111 Using Brackets around Inner Joins in MS Access 121 SQL Is Set-Based 123 Introducing NULL Data 134 Summary 136 Exercises 137 Chapter 4: Advanced Database Design 138 Normalization 138 First Normal Form 139 Second Normal Form 140 Third Normal Form 142 Ensuring Data Validity with Constraints 144 NOT NULL Constraint 145 UNIQUE Constraint 146 CHECK Constraint 150 Primary Key and PRIMARY KEY Constraint 153 Foreign Key 156 Speeding Up Results with Indexes 160 Improving the Design of the Film Club Database 164 Reexamining the Film Club Database Structure 164 Improving Data Validation and Efficiency 166 Tips for Designing a Better Database 174 Summary 175 Exercises 176 Chapter 5: Manipulating Data 178 Understanding SQL Arithmetic 178 Basic Math Operators 178 Common Math Functions 180 The ABS() Function 180 The POWER() Function 181 The SQRT() Function 183 The RAND() Function 183 Rounding Numbers 184 The CEILING() Function 186 The FLOOR() Function 187 The ROUND() Function 187 Introducing String Functions 189 The SUBSTRING() Function 189 Case Conversion Functions 191 The REVERSE() Function 192 The TRIM() Functions 193 The LENGTH() Function 193 The SOUNDEX() and DIFFERENCE() Functions 196 Date Functions 199 Converting Different Data Types 200 Re-examining NULL 201 NULLs and Math 201 NULLs and Strings 203 The COALESCE() Function 204 Using INSERT INTO with the SELECT Statement 206 Summary 208 Exercises 209 Chapter 6: Grouping and Aggregating Data 210 Grouping Results 210 Summarizing and Aggregating Data 212 Counting Results 213 Adding Results 217 Averaging Results 219 MAX() and MIN() in Results 221 Using the HAVING Clause with GROUP BY Statements 223 Summary 226 Exercises 226 Chapter 7: Selecting Data from Different Tables 228 Joins Revisited 228 Inner Joins: An In-Depth Look 229 Equijoins and Non-equijoins 229 Multiple Joins and Multiple Conditions 231 Cross Joins 234 Self-Joins 235 Outer Joins 239 Left Outer Join 240 Right Outer Join 242 Full Outer Join 246 Combining Results Sets with the UNION Operator 247 Summary 254 Exercises 254 Chapter 8: Queries within Queries 256 Subquery Terminology 256 Subqueries in a SELECT List 257 Subqueries in the WHERE Clause 261 Operators in Subqueries 263 Revisiting the IN Operator 263 Using the ANY, SOME, and ALL Operators 266 ANY and SOME Operators 266 ALL Operator 268 Using the EXISTS Operator 270 Using the HAVING Clause with Subqueries 273 Correlated Subquery 274 Subqueries Used with Other Statements 276 Using Subqueries with the INSERT Statement 276 Using Subqueries with the UPDATE Statement 278 Using Subqueries with the DELETE FROM Statement 280 Summary 282 Exercises 282 Chapter 9: Advanced Queries 284 Updating the Database 284 Tackling Difficult Queries 291 Work Out What You Want, What You Really, Really Want 291 Choosing the SELECT Column List 292 Creating the FROM Clause 292 Top Tips for Efficient Queries 304 Summary 306 Exercises 307 Chapter 10: Views 308 Introducing Views 308 Creating Views 309 Types of Views 311 Table Join Views 311 Base View 311 Row Views 312 Field Views 313 Filtered Windowed Views 313 Summary Views 314 Updating Views 315 Update Restrictions 316 Check Option 316 Dropping Views 319 Summary 320 Exercises 320 Chapter 11: Transactions 322 Introducing Transactions 323 Example Data 324 ANSI Model 326 COMMIT 327 ROLLBACK 328 Transact-SQL 329 BEGIN TRANSACTION 329 COMMIT TRANSACTION 329 SAVE TRANSACTION 330 ROLLBACK TRANSACTION 331 Transaction Logs 333 Locks 334 Locking Granularity 334 Database 335 Table 335 Page 335 Row 335 Column 335 Locking Levels 335 Shared 336 Exclusive 336 Deadlocks 336 Setting Lock Parameters 337 Lock Size 337 Number of Locks 337 Escalation 337 Timeout 338 Isolation Levels 339 SET TRANSACTION 339 SERIALIZABLE 339 REPEATABLE READ 340 READ COMMITTED 340 READ UNCOMMITTED 340 Versioning 341 Problem Examples 341 The Lost Update 341 The Uncommitted Data 342 The Inconsistent Data 342 The Phantom Insert 343 Revisiting the Example Code 343 Summary 346 Exercises 346 Chapter 12: SQL Security 348 Security Concepts 348 User IDs 350 Creating User IDs 351 Alter User 351 Drop User 352 Group IDs (Roles) 353 Objects 356 Privileges 357 Extended Privileges 357 The USAGE Privilege 358 Ownership 358 Views and Security 358 Vertical and Horizontal Views 358 Grouped Views 361 Limitations on Views 362 Granting Privileges 362 Tables and Views 362 Columns 363 The GRANT OPTION Clause 364 Revoking Privileges 365 The REVOKE Statement 365 Revoking GRANT 366 The CASCADE and RESTRICT Options 367 Summary 369 Exercises 369 Chapter 13: Database Tuning 370 Tuning Hardware 370 Workstations 371 Database Files 372 Processors 372 Gigahertz Networks or Compartmentalized Networks 373 Caches 373 Processor Cache 373 Hard Disk Cache 373 Database Cache 375 Tuning SQL 376 What Does SQL Tuning Mean? 376 Why Do You Do It? 377 How Do You Do It? 378 Indexes-What Are They? 378 Indexes-When They Help, Hurt, or Don't Matter 381 Table Scans-What Are They? 382 When Table Scans Help, Hurt, or Don't Matter 383 Tuning Tips 384 Summary 385 Exercises 386 Appendix A: Exercise Answers 388 Appendix B: Setting Up and Using the Five Database Systems 412 Appendix C: Initial Data Setup 472 Index 496 Team DDU 1

• Taking readers through the basics of the language, right up to some more advanced topics, this book is a practical, hands-on resource and aims to keep the reader involved at all times
• Focuses on the SQL standard and is loaded with detailed examples and code; each chapter includes practice exercises that readers can challenge themselves with before looking at the sample solutions in the appendix
• Paul Wilton is a successful Wrox "Beginning" book author and is an ideal author to write for those who want a firm grasp of standard SQL before learning the details specific to a particular database product
• SQL is an international standard for manipulating data in databases and is used by database programmers in all major database systems: Microsoft, IBM, Oracle, MySQL, and many others

"Packed with essential code, theories, concepts, and techniques, as well as a cache of useful examples, this comprehensive text will have you quickly designing your own databases and writing SQL code sufficient for many real-world situations. Practice exercises in each chapter help speed up your comprehension. By the end of the book, you'll be prepared to handle any surprise that SQL might throw your way." "This book is for programmers with some prior programming experience who are seeking to develop their database programming skills using SQL. The book assumes no previous programming experience, so it is also suitable for database programming beginners including database administrators."--Jacket