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