Includes Coverage of Oracle and Microsoft SQL Implementations In just 24 lessons of one hour or less, Sams Teach Yourself SQL in 24 Hours, Sixth Edition, helps you use SQL to build effective databases, efficiently retrieve data, and manage everything from performance to security. This book’s straightforward, step-by-step approach shows you how to work with database structures, objects, queries, tables, and more. In just hours, you will be applying advanced techniques, including views, transactions, web connections, and powerful Oracle and SQL Server extensions. Every lesson builds on what you’ve already learned, giving you a rock-solid foundation for real-world success. Step-by-step instructions carefully walk you through the most common SQL tasks. Practical, hands-on examples show you how to apply what you learn. Quizzes and exercises help you test your knowledge and stretch your skills. Notes and tips point out shortcuts and solutions. Learn how to… • Define efficient database structures and objects • “Normalize” raw databases into logically organized tables • Edit relational data and tables with DML • Manage transactions • Write effective, well-performing queries • Categorize, summarize, sort, group, and restructure data • Work with dates and times • Join tables in queries, use subqueries, and combine multiple queries • Master powerful query optimization techniques • Administer databases and manage users • Secure databases and protect data • Use views, synonyms, and the system catalog • Extend SQL to the enterprise and Internet • Master important Oracle and Microsoft extensions to ANSI SQL Register your product at informit.com/register for convenient access to downloads, updates, and corrections as they become available.

Table of Contents:
PART I: AN SQL CONCEPTS OVERVIEW Hour 1: Welcome to the World of SQL   1 SQL Definition and History   1 SQL Sessions   8 Types of SQL Commands   8 Canary Airlines: The Database Used in This Book   11 Summary   15 Q&A   16 Workshop   16 PART II: BUILDING YOUR DATABASE Hour 2: Defining Data Structures   19 What Is Data?   19 Basic Data Types   20 Summary   28 Q&A 29 Workshop   29 Hour 3: Managing Database Objects   33 Database Objects and Schema   33 Tables: The Primary Storage for Data 35 Integrity Constraints   44 Summary 49 Q&A   50 Workshop   50 Hour 4: The Normalization Process 55 Normalizing a Database   55 Denormalizing a Database 63 Summary   64 Q&A 64 Workshop   64 Hour 5: Manipulating Data   67 Overview of Data Manipulation   67 Populating Tables with New Data   68 Updating Existing Data 73 Deleting Data from Tables   75 Summary   76 Q&A   77 Workshop   77 Hour 6: Managing Database Transactions 81 What Is a Transaction?   81 Controlling Transactions   82 Poor Transactional Control   90 Summary   90 Q&A   90 Workshop   91 PART III: GETTING EFFECTIVE RESULTS FROM QUERIES Hour 7: Introduction to Database Queries   93 The SELECT Statement   93 Case-Sensitivity   101 Fundamentals of Query Writing 102 Summary   106 Q&A   106 Workshop   107 Hour 8: Using Operators to Categorize Data   109 What Is an Operator in SQL?   109 Comparison Operators   110 Logical Operators 113 Conjunctive Operators   120 Negative Operators   123 Arithmetic Operators   128 Summary   130 Q&A 131 Workshop   131 Hour 9: Summarizing Data Results from a Query   133 Aggregate Functions   133 Summary 141 Q&A   142 Workshop 142 Hour 10: Sorting and Grouping Data 145 Why Group Data?   145 The GROUP BY Clause   146 GROUP BY Versus ORDER BY   150 CUBE and ROLLUP Expressions 153 The HAVING Clause   155 Summary   156 Q&A   157 Workshop   157 Hour 11: Restructuring the Appearance of Data   159 ANSI Character Functions   159 Common Character Functions   160 Miscellaneous Character Functions   169 Mathematical Functions 172 Conversion Functions   173 Combining Character Functions   176 Summary 177 Q&A   177 Workshop   178 Hour 12: Understanding Dates and Times   181 How Is a Date Stored?   181 Date Functions   183 Date Conversions   188 Summary   193 Q&A   194 Workshop   194 PART IV: BUILDING SOPHISTICATED DATABASE QUERIES Hour 13: Joining Tables in Queries   197 Selecting Data from Multiple Tables   197 Understanding Joins   198 Join Considerations   207 Summary   210 Q&A   210 Workshop 211 Hour 14: Using Subqueries to Define Unknown Data   213 What Is a Subquery?   213 Embedded Subqueries 219 Correlated Subqueries   223 Subquery Performance   225 Summary   225 Q&A   226 Workshop   226 Hour 15: Combining Multiple Queries into One   229 Single Queries Versus Compound Queries   229 Compound Query Operators   230 Using ORDER BY with a Compound Query   235 Using GROUP BY with a Compound Query   237 Retrieving Accurate Data   238 Summary   239 Q&A   239 Workshop   240 PART V: SQL PERFORMANCE TUNING Hour 16: Using Indexes to Improve Performance 243 What Is an Index?   243 How Do Indexes Work?   244 The CREATE INDEX Command   245 Types of Indexes   245 When Should Indexes Be Considered?   248 When Should Indexes Be Avoided?   248 Altering an Index   250 Dropping an Index   250 Summary   251 Q&A   251 Workshop 252 Hour 17: Improving Database Performance   253 What Is SQL Statement Tuning?   253 Database Tuning Versus SQL Statement Tuning   254 Formatting Your SQL Statement   254 Full Table Scans   260 Other Performance Considerations 261 Cost-Based Optimization   264 Summary   266 Q&A   266 Workshop 266 PART VI: USING SQL TO MANAGE USERS AND SECURITY Hour 18: Managing Database Users   269 User Management in the Database   269 The Management Process   272 Tools Utilized by Database Users   279 Summary   279 Q&A 280 Workshop   280 Hour 19: Managing Database Security   283 What Is Database Security?   283 What Are Privileges?   284 Controlling User Access 287 Controlling Privileges Through Roles   291 Summary   293 Q&A   293 Workshop   294 PART VII: SUMMARIZED DATA STRUCTURES Hour 20: Creating and Using Views and Synonyms   297 What Is a View?   297 Creating Views   300 Updating Data Through a View   307 Dropping a View   307 Performance Impact of Nested Views   308 What Is a Synonym?   308 Summary   310 Q&A   310 Workshop   311 Hour 21: Working with the System Catalog   313 What Is the System Catalog?   313 How Is the System Catalog Created?   314 What Is Contained in the System Catalog?   315 System Catalog Tables by Implementation   316 Querying the System Catalog   317 Updating System Catalog Objects   320 Summary 320 Q&A   321 Workshop   321 PART VIII: APPLYING SQL FUNDAMENTALS IN TODAY’S WORLD Hour 22: Advanced SQL Topics   323 Cursors 323 Stored Procedures and Functions   326 Triggers   329 Dynamic SQL 331 Call-Level Interface 331 Using SQL to Generate SQL   332 Direct Versus Embedded SQL 333 Windowed Table Functions 333 Working with XML 334 Summary   335 Q&A 335 Workshop   336 Hour 23: Extending SQL to the Enterprise, the Internet, and the Intranet   337 SQL and the Enterprise 337 Accessing a Remote Database 339 SQL and the Internet   342 SQL and the Intranet   343 Summary   344 Q&A   344 Workshop 344 Hour 24: Extensions to Standard SQL   347 Various Implementations   347 Example Extensions   350 Interactive SQL Statements   353 Summary   354 Q&A   354 Workshop   355 PART IX: APPENDIXES Appendix A: Common SQL Commands   357 SQL Statements   357 SQL Clauses   360 Appendix B: Installing Oracle and Microsoft SQL 363 Windows Installation Instructions for Oracle   363 Windows Installation Instructions for Microsoft SQL Server   365 Appendix C: Answers to Quizzes and Exercises   369 Hour 1, “Welcome to the World of SQL”   369 Hour 2, “Defining Data Structures”   371 Hour 3, “Managing Database Objects”   373 Hour 4, “The Normalization Process”   374 Hour 5, “Manipulating Data”   376 Hour 6, “Managing Database Transactions”   379 Hour 7, “Introduction to the Database Queries”   380 Hour 8, “Using Operators to Categorize Data”   383 Hour 9, “Summarizing Data Results from a Query”   386 Hour 10, “Sorting and Grouping Data”   388 Hour 11, “Restructuring the Appearance of Data”   390 Hour 12, “Understanding Dates and Times”   391 Hour 13, “Joining Tables in Queries”   392 Hour 14, “Using Subqueries to Define Unknown Data”   394 Hour 15, “Combining Multiple Queries into One”   396 Hour 16, “Using Indexes to Improve Performance”   397 Hour 17, “Improving Database Performance”   399 Hour 18, “Managing Database Users”   403 Hour 19, “Managing Database Security”   404 Hour 20, “Creating and Using Views and Synonyms”   404 Hour 21, “Working with the System Catalog”   406 Hour 22, “Advanced SQL Topics”   407 Hour 23, “Extending SQL to the Enterprise, the Internet, and the Intranet”   408 Hour 24, “Extensions to Standard SQL”   409 Appendix D: Bonus Exercises   411 Appendix E: Glossary 425 Index   429