Table Of Content

Ryan Stephens Ron Plew Arie D. Jones SamsTeachYourself SQL ® 24 in Hours FOURTH EDITION 800 East 96th Street,Indianapolis,Indiana,46240 USA Sams Teach Yourself SQL®in 24 Hours,Fourth Edition Associate Copyright © 2008 by Sams Publishing Publisher All rights reserved. No part of this book shall be reproduced,stored in a retrieval system,or Mark Taub transmitted by any means,electronic,mechanical,photocopying,recording,or otherwise,without Acquisitions Editor written permission from the publisher. No patent liability is assumed with respect to the use of the information contained herein. Although every precaution has been taken in the preparation of Trina MacDonald this book,the publisher and author assume no responsibility for errors or omissions. Nor is any Development liability assumed for damages resulting from the use of the information contained herein. Editor ISBN-13: 978-0-672-33018-6 Michael Thurston ISBN-10: 0-672-33018-0 Library of Congress Cataloging-in-Publication Data Managing Editor Stephens,Ryan K. Patrick Kanouse Sams teach yourself SQL in 24 hours / Ryan Stephens,Ron Plew,Arie D. Project Editor Jones.-- 4th ed. Mandie Frank p.cm.-- (Sams teach yourself in 24 hours) On t.p.of earlier ed.Ronald R.Plew's name appeared first. Copy Editor Includes indexes Heather Wilkins ISBN 978-0-672-33018-6 (pbk.) Editorial Services 1. SQL (Computer program language) I.Plew,Ronald R.II.Jones, Arie.III.Plew,Ronald R.Sams teach yourself SQL in 24 hours.IV. Indexer Title. Heather McNeil QA76.73.S67P554 2008 Proofreader 005.75'6--dc22 Matt Purcell 2008016630 Printed in the United States of America Technical Editor First Printing May 2008 Steve Cvar Trademarks Publishing All terms mentioned in this book that are known to be trademarks or service marks have been Coordinator appropriately capitalized. Sams Publishing cannot attest to the accuracy of this information. Use Olivia Basegio of a term in this book should not be regarded as affecting the validity of any trademark or service mark. Designer Warning and Disclaimer Gary Adair Every effort has been made to make this book as complete and as accurate as possible,but no Composition warranty or fitness is implied. The information provided is on an “as is” basis. The authors and Bronkella Publishing the publisher shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this book. Bulk Sales Sams Publishing offers excellent discounts on this book when ordered in quantity for bulk pur- chases or special sales. For more information,please contact U.S. Corporate and Government Sales 1-800-382-3419 [email protected] For sales outside of the U.S.,please contact International Sales [email protected] Contents at a Glance Introduction................................................................................1 Part I: A SQL Concepts Overview HOUR 1 Welcome to the World of SQL ........................................................7 Part II: Building Your Database HOUR 2 Defining Data Structures.............................................................27 3 Managing Database Objects........................................................41 4 The Normalization Process..........................................................61 5 Manipulating Data....................................................................73 6 Managing Database Transactions.................................................87 Part III: Getting Effective Results from Queries HOUR 7 Introduction to the Database Query............................................101 8 Using Operators to Categorize Data.............................................117 9 Summarizing Data Results from a Query.....................................141 10 Sorting and Grouping Data........................................................151 11 Restructuring the Appearance of Data.........................................165 12 Understanding Dates and Times.................................................185 Part IV: Building Sophisticated Database Queries HOUR 13 Joining Tables in Queries...........................................................203 14 Using Subqueries to Define Unknown Data...................................221 15 Combining Multiple Queries into One.........................................235 Part V: SQL Performance Tuning HOUR 16 Using Indexes to Improve Performance........................................253 17 Improving Database Performance...............................................265 Part VI: Using SQL to Manage Users and Security HOUR 18 Managing Database Users.........................................................283 19 Managing Database Security......................................................297 Part VII: Summarized Data Structures HOUR 20 Creating and Using Views and Synonyms.....................................313 21 Working with the System Catalog...............................................329 Part VIII: Applying SQL Fundamentals in Today’s World HOUR 22 Advanced SQL Topics................................................................343 23 Extending SQL to the Enterprise, the Internet, and the Intranet........359 24 Extensions to Standard SQL........................................................369 Part IX: Appendixes A Common SQL Commands.........................................................381 B Using MySQL for Exercises.........................................................387 C Answers to Quizzes and Exercises................................................391 D CREATE TABLEStatements for Book Examples................................435 E INSERTStatements for Data in Book Examples..............................437 F Bonus Exercises........................................................................441 Glossary..................................................................................447 Index.....................................................................................451 Table of Contents Introduction 1 What This Book Intends to Accomplish ....................................................1 What We Added to This Edition ..............................................................1 What You Need ....................................................................................2 Conventions Used in This Book................................................................2 ANSI SQL and Vendor Implementations....................................................3 Understanding the Examples and Exercises................................................3 Part I: A SQL Concepts Overview HOUR 1: Welcome to the World of SQL 7 SQL Definition and History ....................................................................7 SQL Sessions ......................................................................................14 Types of SQL Commands......................................................................15 The Database Used in This Book............................................................17 Summary ..........................................................................................22 Q&A..................................................................................................23 Workshop ..........................................................................................24 Part II: Building Your Database HOUR 2: Defining Data Structures 27 What Is Data? ....................................................................................27 Basic Data Types ................................................................................28 Summary ..........................................................................................36 Q&A..................................................................................................37 Workshop ..........................................................................................37 vi Sams Teach Yourself SQL in 24 Hours HOUR 3: Managing Database Objects 41 What Are Database Objects? ................................................................41 What Is a Schema?..............................................................................42 A Table: The Primary Storage for Data....................................................44 Integrity Constraints............................................................................52 Summary ..........................................................................................56 Q&A..................................................................................................57 Workshop ..........................................................................................58 HOUR 4: The Normalization Process 61 Normalizing a Database ......................................................................61 Denormalizing a Database....................................................................69 Summary ..........................................................................................69 Q&A..................................................................................................70 Workshop ..........................................................................................70 HOUR 5: Manipulating Data 73 Overview of Data Manipulation ............................................................73 Populating Tables with New Data ..........................................................74 Updating Existing Data........................................................................79 Deleting Data from Tables ....................................................................81 Summary ..........................................................................................82 Q&A..................................................................................................82 Workshop ..........................................................................................83 HOUR 6: Managing Database Transactions 87 What Is a Transaction? ........................................................................87 Controlling Transactions ......................................................................88 Transactional Control and Database Performance ....................................95 Summary ..........................................................................................95 Q&A..................................................................................................96 Workshop ..........................................................................................96 vii Contents Part III: Getting Effective Results from Queries HOUR 7: Introduction to the Database Query 101 What Is a Query? ..............................................................................101 Introduction to the SELECTStatement....................................................101 Examples of Simple Queries ................................................................109 Summary..........................................................................................113 Q&A................................................................................................113 Workshop ........................................................................................114 HOUR 8: Using Operators to Categorize Data 117 What Is an Operator in SQL? ..............................................................117 Comparison Operators........................................................................118 Logical Operators ..............................................................................121 Conjunctive Operators........................................................................127 Negative Operators............................................................................130 Arithmetic Operators..........................................................................134 Summary..........................................................................................138 Q&A................................................................................................138 Workshop ........................................................................................138 HOUR 9: Summarizing Data Results from a Query 141 What Are Aggregate Functions? ..........................................................141 Summary..........................................................................................149 Q&A................................................................................................149 Workshop ........................................................................................149 HOUR 10: Sorting and Grouping Data 151 Why Group Data?..............................................................................151 TheGROUP BYClause..........................................................................152 GROUP BYVersus ORDER BY ..................................................................156 TheHAVINGClause..............................................................................159 Summary..........................................................................................160 viii Sams Teach Yourself SQL in 24 Hours Q&A................................................................................................160 Workshop ........................................................................................161 HOUR 11: Restructuring the Appearance of Data 165 ANSI Character Functions....................................................................165 Various Common Character Functions ..................................................166 Miscellaneous Character Functions ......................................................175 Mathematical Functions......................................................................178 Conversion Functions ........................................................................179 Combining Character Functions ..........................................................181 Summary..........................................................................................182 Q&A................................................................................................182 Workshop ........................................................................................183 HOUR 12: Understanding Dates and Times 185 How Is a Date Stored? ........................................................................186 Date Functions..................................................................................187 Date Conversions ..............................................................................192 Summary..........................................................................................197 Q&A................................................................................................197 Workshop ........................................................................................198 Part IV: Building Sophisticated Database Queries HOUR 13: Joining Tables in Queries 203 Selecting Data from Multiple Tables......................................................203 Types of Joins....................................................................................204 Join Considerations............................................................................214 Summary..........................................................................................218 Q&A................................................................................................218 Workshop ........................................................................................219 ix Contents HOUR 14: Using Subqueries to Define Unknown Data 221 What Is a Subquery? ..........................................................................221 Embedded Subqueries ........................................................................227 Correlated Subqueries ........................................................................229 Summary..........................................................................................230 Q&A................................................................................................231 Workshop ........................................................................................231 HOUR 15: Combining Multiple Queries into One 235 Single Queries Versus Compound Queries ..............................................235 Compound Query Operators................................................................236 UsingORDER BYwith a Compound Query ..............................................242 UsingGROUP BYwith a Compound Query ..............................................244 Retrieving Accurate Data ....................................................................246 Summary..........................................................................................246 Q&A................................................................................................246 Workshop ........................................................................................247 Part V: SQL Performance Tuning HOUR 16: Using Indexes to Improve Performance 253 What Is an Index?..............................................................................253 How Do Indexes Work? ......................................................................254 TheCREATE INDEXCommand ..............................................................255 Types of Indexes................................................................................255 When Should Indexes Be Considered? ..................................................258 When Should Indexes Be Avoided?........................................................259 Dropping an Index ............................................................................260 Summary..........................................................................................261 Q&A................................................................................................261 Workshop ........................................................................................262