Sams Teach Yourself SQL in 24 Hours - pearsoncmg.com

1 800 East 96th Street, Indianapolis, Indiana, 46240 USARyan StephensRon PlewArie D. JonesSamsTeach Yourself24inHoursSQLFIFTH EDITIONSams Teach Yourself SQL in 24 Hours , Fifth EditionCopyright 2011 by Pearson Education, rights reserved. No part of this book shall be reproduced, stored in a retrieval system, ortransmitted by any means, electronic, mechanical, photocopying, recording, or otherwise, withoutwritten permission from the publisher. No patent liability is assumed with respect to the use ofthe information contained herein. Although every precaution has been taken in the preparation ofthis book, the publisher and author assume no responsibility for errors or omissions. Nor is anyliability assumed for damages resulting from the use of the information contained and Java are registered trademarks of Oracle and/or its affiliates.

2 Other names may betrademarks of their respective : 978-0-672-33541-9 ISBN-10: 0-672-33541-7 The Library of Congress cataloging-in-publication data is on in the United States of AmericaFourth Printing: May 2013 TrademarksAll terms mentioned in this book that are known to be trademarks or service marks have beenappropriately capitalized. Sams Publishing cannot attest to the accuracy of this information. Useof a term in this book should not be regarded as affecting the validity of any trademark or and DisclaimerEvery effort has been made to make this book as complete and as accurate as possible, but nowarranty or fitness is implied. The information provided is on an as is basis. The authors andthe publisher shall have neither liability nor responsibility to any person or entity with respect toany loss or damages arising from the information contained in this book or from the programsaccompanying SalesSams Publishing offers excellent discounts on this book when ordered in quantity for bulk pur-chases or special sales.

3 For more information, please Corporate and Government sales outside of the , please contactInternational PublisherMark TaubAcquisitions EditorTrina MacDonaldDevelopment EditorMichael ThurstonManaging EditorKristy HartProject EditorJovana San Nicolas-ShirleyCopy EditorThe WordsmitheryLLCI ndexerLisa StumpfProofreaderGill Editorial ServicesTechnical EditorBenjamin SchupakPublishingCoordinatorOlivia BasegioBook DesignerGary AdairCompositionGloria SchurickContents at a GlancePart I: An SQL Concepts OverviewHOUR 1 Welcome to the World of II: Building Your DatabaseHOUR 2 Defining Data Database Normalization Database III: Getting Effective Results from QueriesHOUR 7 Introduction to the Database Operators to Categorize Data Results from a and Grouping the Appearance of Dates and IV: Building Sophisticated Database QueriesHOUR 13 Joining Tables in Subqueries to Define Unknown Multiple Queries into V: SQL Performance TuningHOUR 16 Using Indexes to Improve Database VI: Using SQL to Manage Users and SecurityHOUR 18 Managing Database Database VII: Summarized Data StructuresHOUR 20 Creating and Using Views and with the System VIII: Applying SQL Fundamentals in Today s WorldHOUR 22 Advanced SQL SQL to the Enterprise, the Internet, and the to Standard IX.

4 AppendixesACommon SQL the Databases for to Quizzes and TABLE Statements for Book Statements for Data in Book Yourself SQL in 24 HoursTable of ContentsPart I: An SQL Concepts OverviewHOUR 1:Welcome to the World of SQL1 SQL Definition and of SQL Database Used in This II: Building Your DatabaseHOUR 2:Defining Data Structures21 What Is Data?..21 Basic Data 3:Managing Database Objects37 What Are Database Objects?..37 What Is a Schema?..37 Tables: The Primary Storage for 4:The Normalization Process61 Normalizing a a 5:Manipulating Data73 Overview of Data Tables with New Existing Data from 6:Managing Database Transactions87 What Is a Transaction?..87 Controlling Control and Database III: Getting Effective Results from QueriesHOUR 7:Introduction to the Database Query99 What Is a Query?

5 99 Introduction to the SELECT of Simple Yourself SQL in 24 HoursHOUR 8:Using Operators to Categorize Data115 What Is an Operator in SQL?..115 Comparison 9:Summarizing Data Results from a Query141 What Are Aggregate Functions?.. 10:Sorting and Grouping Data153 Why Group Data?..153 The GROUP BY BY Versus ORDER and ROLLUP HAVING 11:Restructuring the Appearance of Data169 ANSI Character Character Character Character 12:Understanding Dates and Times191 How Is a Date Stored?..191 Date IV: Building Sophisticated Database QueriesHOUR 13:Joining Tables in Queries207 Selecting Data from Multiple 14:Using Subqueries to Define Unknown Data225 What Is a Subquery?..225 Embedded Yourself SQL in 24 HoursHOUR 15:Combining Multiple Queries into One239 Single Queries Versus Compound Query ORDER BY with a Compound GROUP BY with a Compound Accurate V: SQL Performance TuningHOUR 16:Using Indexes to Improve Performance255 What Is an Index?

6 255 How Do Indexes Work?..256 The CREATE INDEX of Should Indexes Be Considered?..260 When Should Indexes Be Avoided?..261 Altering an an 17:Improving Database Performance267 What Is SQL Statement Tuning?..267 Database Tuning Versus SQL Statement Your SQL Table Performance VI: Using SQL to Manage Users and SecurityHOUR 18:Managing Database Users285 User Management in the Management Utilized by Database 19:Managing Database Security299 What Is Database Security?..299 What Are Privileges?..301 Controlling User Privileges Through VII: Summarized Data StructuresHOUR 20:Creating and Using Views and Synonyms313 What Is a View?..313 Creating CHECK a Table from a and the ORDER BY Data Through a a Impact of Using Nested Is a Synonym?

7 324xTeach Yourself SQL in 24 21:Working with the System Catalog329 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 the System System Catalog VIII: Applying SQL Fundamentals in Today s WorldHOUR 22:Advanced SQL Procedures and SQL to Generate Versus Embedded Table with 23:Extending SQL to the Enterprise, the Internet, and the Intranet355 SQL and the a Remote Yourself SQL in 24 HoursSQL and the and the 24:Extensions to Standard SQL367 Various SQL IX: AppendixesAPPENDIX A:Common SQL Commands377 SQL B:Using the Databases for Exercises383 Windows Installation Instructions for Installation Instructions for Installation Instructions for Microsoft SQL C:Answers to Quizzes and Exercises391 APPENDIX D:CREATE TABLE Statements for Book Examples439 APPENDIX E.

8 INSERT Statements for Data in Book Examples443 APPENDIX F:Glossary451 APPENDIX G:Bonus Exercises455 INDEX461 About the AuthorFor more than 10 years, the authors have studied, applied, and documented the SQL stan-dard and its application to critical database systems in this book. Ryan StephensandRon Pleware entrepreneurs, speakers, and cofounders of PerpetualTechnologies, Inc. (PTI), a fast-growing IT management and consulting firm. PTI specializesin database technologies, primarily Oracle and SQL servers running on all UNIX, Linux,and Microsoft platforms. Starting out as data analysts and database administrators, Ryanand Ron now lead a team of impressive technical subject matter experts who manage data-bases for clients worldwide.

9 They authored and taught database courses for IndianaUniversity-Purdue University in Indianapolis for five years and have authored more than adozen books on Oracle, SQL, database design, and high availability of critical D. Jonesis the principal technology manager for Perpetual Technologies, Inc. (PTI) inIndianapolis, Indiana. Arie leads PTI s team of experts in planning, design, development,deployment, and management of database environments and applications to achieve thebest combination of tools and services for each client. He is a regular speaker at technicalevents and has authored several books and articles pertaining to database-related topics. DedicationThis book is dedicated to my parents, Thomas and Karlyn Stephens, who alwaystaught me that I can achieve anything if determined.

10 This book is also dedicated tomy brilliant son, Daniel, and to my beautiful daughters, Autumn and Alivia; don tever settle for anything less than your dreams. RyanThis book is dedicated to my family: my wife, Linda; my mother, Betty; my children,Leslie, Nancy, Angela, and Wendy; my grandchildren, Andy, Ryan, Holly, Morgan,Schyler, Heather, Gavin, Regan, Caleigh, and Cameron; and my sons-in-law, Jasonand Dallas. Thanks for being patient with me during this busy time. Love all of you. PoppyI would like to dedicate this book to my wife, Jackie, for being understanding andsupportive during the long Hours that it took to complete this book. ArieAcknowledgmentsThanks to all the people in our lives who have been patient during all editions of thisbook mostly to our wives, Tina and Linda.