Overview

In just 24 sessions of one hour or less, you’ll learn how to use SQL to build effective databases, efficiently retrieve your data, and manage everything from performance to security! Using this book’s straightforward, step-by-step approach, you’ll learn hands-on through practical examples. Each lesson builds on what you’ve already learned, giving you a strong real-world foundation for success. The authors guide you from the absolute basics to advanced techniques—including views, transactions, Web data publishing, and even powerful SQL extensions for Oracle and Microsoft SQL Server!   Step-by-step instructions carefully walk you through the most common SQL tasks.   Quizzes and Exercises at the end of each chapter help you test your knowledge.   By the Way notes present interesting information related to the discussion.   Did You Know? tips offer advice or show you easier ways to perform tasks.   Watch Out! cautions alert you to possible problems and give you advice on how to avoid them.   Learn how to… Understand what SQL is, how it works, and what it does Define efficient database structures and objects “Normalize” raw databases into logically organized tables Edit relational data and tables with DML Manage database 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 Implement effective database security Use views, synonyms, and the system catalog Extend SQL to the enterprise and Internet Master important Oracle and Microsoft SQL Server extensions to ANSI SQL

Full Product Details

Author:   Ryan Stephens ,  Ron Plew ,  Arie D. Jones
Publisher:   Pearson Education (US)
Imprint:   Sams Publishing
Edition:   5th edition
Dimensions:   Width: 17.90cm , Height: 2.60cm , Length: 23.10cm
Weight:   0.770kg
ISBN:  

9780672335419

ISBN 10:   0672335417
Pages:   504
Publication Date:   09 June 2011
Audience:   Professional and scholarly ,  Professional & Vocational
Replaced By:   9780672337598
Format:   Paperback
Publisher's Status:   Out of Print
Availability:   In Print  
Limited stock is available. It will be ordered for you and shipped pending supplier's limited stock.

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     9 The Database Used in This Book     12 Summary     17 Q&A     17 Workshop     18   Part II:  Building Your Database HOUR 2:  Defining Data Structures 21 What Is Data?     21 Basic Data Types     22 Summary     31 Q&A     31 Workshop      32 HOUR 3: Managing Database Objects     37 What Are Database Objects?     37 What Is a Schema?     37 Tables: The Primary Storage for Data     39 Integrity Constraints     49 Summary     54 Q&A     55 Workshop     55 HOUR 4: The Normalization Process     61 Normalizing a Database     61 Denormalizing a Database     69 Summary     70 Q&A     70 Workshop     71 HOUR 5: Manipulating Data     73 Overview of Data Manipulation     73 Populating Tables with New Data     74 Updating Existing Data     80 Deleting Data from Tables     82 Summary     83 Q&A     83 Workshop     84 HOUR 6: Managing Database Transactions     87 What Is a Transaction?     87 Controlling Transactions     88 Transactional Control and Database Performance     95 Summary     96 Q&A     96 Workshop     97   Part III: Getting Effective Results from Queries HOUR 7: Introduction to the Database Query     99 What Is a Query?     99 Introduction to the SELECT Statement     99 Examples of Simple Queries     108 Summary     112 Q&A     112 Workshop     113 HOUR 8: Using Operators to Categorize Data     115 What Is an Operator in SQL?     115 Comparison Operators     116 Logical Operators     119 Conjunctive Operators     126 Negative Operators     129 Arithmetic Operators     133 Summary     136 Q&A     137 Workshop     137 HOUR 9: Summarizing Data Results from a Query     141 What Are Aggregate Functions?     141 Summary     150 Q&A     150 Workshop     150 HOUR 10: Sorting and Grouping Data     153 Why Group Data?     153 The GROUP BY Clause     154 GROUP BY Versus ORDER BY     159 CUBE and ROLLUP Expressions     161 The HAVING Clause     164 Summary     165 Q&A     166 Workshop     166 HOUR 11: Restructuring the Appearance of Data     169 ANSI Character Functions     169 Common Character Functions     170 Miscellaneous Character Functions     179 Mathematical Functions     183 Conversion Functions     183 Combining Character Functions     186 Summary     187 Q&A     188 Workshop     188 HOUR 12: Understanding Dates and Times     191 How Is a Date Stored?     191 Date Functions     193 Date Conversions     198 Summary     204 Q&A     204 Workshop     205   Part IV: Building Sophisticated Database Queries HOUR 13: Joining Tables in Queries     207 Selecting Data from Multiple Tables      207 Understanding Joins     208 Join Considerations     217 Summary     221 Q&A     222 Workshop     222 HOUR 14: Using Subqueries to Define Unknown Data     225 What Is a Subquery?     225 Embedded Subqueries     231 Correlated Subqueries     233 Subquery Performance     234 Summary     235 Q&A     235 Workshop     236 HOUR 15: Combining Multiple Queries into One     239 Single Queries Versus Compound Queries     239 Compound Query Operators     240 Using ORDER BY with a Compound Query     246 Using GROUP BY with a Compound Query     248 Retrieving Accurate Data     250 Summary     250 Q&A     250 Workshop     251   Part V: SQL Performance Tuning HOUR 16: Using Indexes to Improve Performance     255 What Is an Index?     255 How Do Indexes Work?     256 The CREATE INDEX Command     257 Types of Indexes     258 When Should Indexes Be Considered?     260 When Should Indexes Be Avoided?     261 Altering an Index     263 Dropping an Index     263 Summary     264 Q&A     264 Workshop     265 HOUR 17: Improving Database Performance     267 What Is SQL Statement Tuning?     267 Database Tuning Versus SQL Statement Tuning     268 Formatting Your SQL Statement     268 Full Table Scans     274 Other Performance Considerations     275 Cost-Based Optimization     279 Performance Tools     280 Summary     280 Q&A     281 Workshop     281   Part VI: Using SQL to Manage Users and Security HOUR 18: Managing Database Users     285 User Management in the Database     285 The Management Process     288 Tools Utilized by Database Users     296 Summary     296 Q&A     297 Workshop     297 HOUR 19: Managing Database Security     299 What Is Database Security?     299 What Are Privileges?     301 Controlling User Access     304 Controlling Privileges Through Roles     308 Summary     310 Q&A     310 Workshop     311   Part VII: Summarized Data Structures HOUR 20: Creating and Using Views and Synonyms     313 What Is a View?     313 Creating Views     316 WITH CHECK OPTION     320 Creating a Table from a View     321 Views and the ORDER BY Clause     322 Updating Data Through a View     322 Dropping a View     323 Performance Impact of Using Nested Views     323 What Is a Synonym?     324 Summary     325 Q&A     326 Workshop     326 HOUR 21: Working with the System Catalog     329 What Is the System Catalog?     329 How Is the System Catalog Created?     331 What Is Contained in the System Catalog?     331 System Catalog Tables by Implementation     333 Querying the System Catalog     334 Updating System Catalog Objects     336 Summary     337 Q&A     337 Workshop     338   Part VIII: Applying SQL Fundamentals in Today’s World HOUR 22: Advanced SQL Topics     339 Cursors     339 Stored Procedures and Functions     343 Triggers     346 Dynamic SQL     348 Call-Level Interface     349 Using SQL to Generate SQL     350 Direct Versus Embedded SQL     351 Windowed Table Functions     351 Working with XML     352 Summary     353 Q&A     353 Workshop     354 HOUR 23: Extending SQL to the Enterprise, the Internet, and the Intranet     355 SQL and the Enterprise     355 Accessing a Remote Database     357 SQL and the Internet     360 SQL and the Intranet     361 Summary     362 Q&A     363 Workshop     363 HOUR 24: Extensions to Standard SQL     367 Various Implementations     367 Example Extensions     370 Interactive SQL Statements     373 Summary     374 Q&A     375 Workshop     375   Part IX: Appendixes APPENDIX A: Common SQL Commands     377 SQL Statements     377 SQL Clauses     381 APPENDIX B: Using the Databases for Exercises     383 Windows Installation Instructions for MySQL     383 Windows Installation Instructions for Oracle     386 Windows Installation Instructions for Microsoft SQL Server     388 APPENDIX C: Answers to Quizzes and Exercises     391 APPENDIX D: CREATE TABLE Statements for Book Examples     439 APPENDIX E: INSERT Statements for Data in Book Examples     443 APPENDIX F: Glossary     451 APPENDIX G: Bonus Exercises     455 INDEX     461  

Reviews

Author Information

For more than 10 years, the authors have studied, applied, and documented the SQL standard and its application to critical database systems in this book.   Ryan Stephens and Ron Plew are entrepreneurs, speakers, and cofounders of Perpetual Technologies, Inc. (PTI), a fast-growing IT management and consulting firm. PTI specializes in database technologies, primarily Oracle and SQL servers running on all UNIX, Linux, and Microsoft platforms. Starting out as data analysts and database administrators, Ryan and Ron now lead a team of impressive technical subject matter experts who manage databases for clients worldwide. They authored and taught database courses for Indiana University-Purdue University in Indianapolis for five years and have authored more than a dozen books on Oracle, SQL, database design, and high availability of critical systems.   Arie D. Jones is the principal technology manager for Perpetual Technologies, Inc. (PTI) in Indianapolis, Indiana. Arie leads PTI’s team of experts in planning, design, development, deployment, and management of database environments and applications to achieve the best combination of tools and services for each client. He is a regular speaker at technical events and has authored several books and articles pertaining to database-related topics.

Tab Content 6

Author Website:  

Countries Available

All regions