WIXLIB

The Languageof SQLSecond EditionLarry RockoffHoboken, NJ Boston Indianapolis San FranciscoNew York Toronto Montreal London Munich Paris MadridCape Town Sydney Tokyo Singapore Mexico City

The Language of SQL, Second EditionCopyright 2017 by Pearson Education, Inc.All rights reserved. No part of this book shall be reproduced, stored in a retrieval system,or transmitted by any means, electronic, mechanical, photocopying, recording, or otherwise,without written permission from the publisher. No patent liability is assumed with respectto the use of the information contained herein. Although every precaution has been taken inthe preparation of this book, the publisher and author assume no responsibility for errors oromissions. Nor is any liability assumed for damages resulting from the use of the information contained herein.ISBN-13: 978-0-13-465825-4ISBN-10: 0-13-465825-6Library of Congress Control Number: 2016945436Printed in the United States of AmericaFirst Printing: August 2016TrademarksAll terms mentioned in this book that are known to be trademarks or service marks havebeen appropriately capitalized. The publisher cannot attest to the accuracy of this information. Use of a term in this book should not be regarded as affecting the validity of any trademark or service mark.Warning and DisclaimerEvery effort has been made to make this book as complete and as accurate as possible,but no warranty or fitness is implied. The information provided is on an “as is” basis. Theauthor and the publisher shall have neither liability nor responsibility to any person or entitywith respect to any loss or damages arising from the information contained in this book.Special SalesFor information about buying this title in bulk quantities, or for special sales opportunities(which may include electronic versions; custom cover designs; and content particular to yourbusiness, training goals, marketing focus, or branding interests), please contact our corporate sales department at corpsales@pearsoned.com or (800) 382-3419.For government sales inquiries, please contact governmentsales@pearsoned.com.For questions about sales outside the U.S., please contact intlcs@pearson.com.EditorMark TaberProject andCopy EditorDan Foster,Scribe TribeTechnical EditorSiddhartha SinghDesignerChuti PrasertsithCompositorDanielle Foster,Scribe TribeIndexerValerie HaynesPerryProofreaderScout Festa

Contents at a GlanceIntroductionxiii1 Relational Databases and SQL2 Basic Data Retrieval1113 Calculated Fields and Aliases194 Using Functions 275 Sorting Data416 Selection Criteria497 Boolean Logic 618 Conditional Logic 739 Summarizing Data8110 Subtotals and Crosstabs10111 Inner Joins 11512 Outer Joins12313 Self Joins and Views 13514 Subqueries14315 Set Logic 15516 Stored Procedures and Parameters17 Modifying Data16317118 Maintaining Tables18119 Principles of Database Design18920 Strategies for Displaying Data199A Getting Started with Microsoft SQL ServerB Getting Started with MySQL211C Getting Started with Oracle215Index 217209

Table of ContentsIntroductionxiii1 Relational Databases and SQL1What Is SQL? 2Microsoft SQL Server, MySQL, and Oracle 3Relational Databases 4Primary and Foreign Keys 6Datatypes 6NULL Values 8The Significance of SQL 8Looking Ahead92 Basic Data Retrieval11A Simple SELECT 11Syntax Notes 12Comments 13Specifying Columns 14Column Names with Embedded Spaces 15Preview of the Full SELECT 16Looking Ahead 173 Calculated Fields and AliasesLiteral Values 20Arithmetic Calculations 21Concatenating Fields 22Column Aliases 23Table Aliases 24Looking Ahead 254 Using Functions27What Is a Function? 27Character Functions 28Composite Functions 32Date/Time Functions 3319

Table of ContentsNumeric Functions35Conversion Functions 36Looking Ahead 395 Sorting Data41Sorting in Ascending Order 41Sorting in Descending Order 43Sorting by Multiple Columns 43Sorting by a Calculated Field 44Sort Sequences 45Looking Ahead 476 Selection Criteria49Applying Selection Criteria 49WHERE Clause Operators 50Limiting Rows 51Limiting Rows with a Sort53Pattern Matching 54Wildcards 56Looking Ahead 587 Boolean Logic61Complex Logical Conditions 61The AND Operator62The OR Operator 62Using Parentheses 63Multiple Sets of Parentheses 65The NOT Operator 66The BETWEEN Operator 68The IN Operator 69Boolean Logic and NULL Values 70Looking Ahead 728 Conditional Logic73The CASE Expression 73The Simple CASE Format 74The Searched CASE Format 76v

viTable of ContentsConditional Logic in ORDER BY Clauses 78Conditional Logic in WHERE Clauses 79Looking Ahead 809 Summarizing Data81Eliminating Duplicates 81Aggregate Functions 83The COUNT Function 84Grouping Data 86Multiple Columns and Sorting 87Selection Criteria on Aggregates 89Conditional Logic in GROUP BY Clauses 91Conditional Logic in HAVING Clauses 92Ranking Functions 93Partitions 97Looking Ahead 10010 Subtotals and Crosstabs101Adding Subtotals with ROLLUP 102Adding Subtotals with CUBE 106Creating Crosstab Layouts 110Looking Ahead 11411 Inner Joins115Joining Two Tables 116The Inner Join 118Table Order in Inner Joins 119An Alternate Specification of Inner Joins 119Table Aliases Revisited 120Looking Ahead 12112 Outer Joins123The Outer Join 123Left Joins 125Testing for NULL Values 127Right Joins 128

Table of ContentsTable Order in Outer Joins 129Full Joins 129Cross Joins 131Looking Ahead 13413 Self Joins and Views135Self Joins 135Creating Views 137Referencing Views 139Benefits of Views 140Modifying and Deleting Views 141Looking Ahead 14214 Subqueries143Types of Subqueries 143Using a Subquery as a Data Source 144Using a Subquery in Selection Criteria 147Correlated Subqueries 148The EXISTS Operator 150Using a Subquery as a Calculated Column 151Common Table Expressions 152Looking Ahead 15315 Set Logic155Using the UNION Operator 156Distinct and Non-Distinct Unions 158Intersecting Queries 159Looking Ahead 16116 Stored Procedures and Parameters163Creating Stored Procedures 164Parameters in Stored Procedures 165Executing Stored Procedures 167Modifying and Deleting Stored Procedures 167Functions Revisited 168Looking Ahead 169vii

viiiTable of Contents17 Modifying Data171Modification Strategies 171Inserting Data 172Deleting Data 175Updating Data 176Correlated Subquery Updates 177Looking Ahead 17918 Maintaining Tables181Data Definition Language 181Table Attributes 182Table Columns 183Primary Keys and Indexes 183Foreign Keys 184Creating Tables 185Creating Indexes 187Looking Ahead 18719 Principles of Database Design189Goals of Normalization 190How to Normalize Data 191The Art of Database Design 195Alternatives to Normalization 196Looking Ahead 19720 Strategies for Displaying Data199Crosstab Layouts Revisited 199Excel and External Data 200Excel Pivot Tables 203Looking Ahead 207A Getting Started with Microsoft SQL Server209Installing SQL Server 2016 Express 209Installing SQL Server 2016 Management Studio Express 210Using SQL Server 2016 Management Studio Express 210

Table of ContentsB Getting Started with MySQL211Installing MySQL on Windows 211Installing MySQL on a Mac212Using MySQL Workbench 213C Getting Started with Oracle215Installing Oracle Database Express Edition 215Using Oracle Database Express Edition 216Index 217ix

About the AuthorLarry Rockoff has been involved with SQL and business intelligence development for manyyears. His main area of interest is in using reporting tools to explore and analyze data incomplex databases. He holds an MBA from the University of Chicago, with a specializationin management science. He currently works with data warehouse and reporting applicationsfor a major retail pharmacy.Besides writing about SQL, he has also published books on Microsoft Access and Excel.He also maintains a website that features book reviews on technology topics, focusing onanalytics and business intelligence as well as broader societal issues, atlarryrockoff.comPlease feel free to visit that site to contact the author with any comments or questions.You are also encouraged to follow his Facebook author page or Twitter site f

AcknowledgmentsA huge thanks goes out to all at Pearson who assisted with this book. I’d like to specifically thankMark Taber, who was instrumental in bringing this book to Pearson from my previous publisher.I’d also like to thank project editor and copy editor Dan Foster, as well as Danielle Foster,who was responsible for the page layout. Siddhartha Singh did a superb job on the technicalreview. Chuti Prasertsith provided a wonderfully vibrant cover design. Finally, I must mentionthe generally thankless but essential tasks of the book’s indexer, Valerie Haynes Perry, andproofreader, Scout Festa.As this is a second edition, I’d also like to thank all readers of the first edition, and especiallythose individuals who have contacted me at larryrockoff.com and offered gracious commentsas to the usefulness of the book in their personal lives. It’s both humbling and thrilling torealize that your thoughts on a relatively mundane topic can assist someone halfway aroundthe world.

We Want to Hear from You!As the reader of this book, you are our most important critic and commentator. We value youropinion and want to know what we’re doing right, what we could do better, what areas you’dlike to see us publish in, and any other words of wisdom you’re willing to pass our way.We welcome your comments. You can email or write directly to let us know what you did ordidn’t like about this book—as well as what we can do to make our books better.Please note that we cannot help you with technical problems related to the topic of this book, and thatdue to the high volume of mail we receive, we might not be able to reply to every message.When you write, please be sure to include this book’s title and author, as well as your name and phonenumber or email address.Email: feedback@developers-library.infoMail:Reader FeedbackAddison-Wesley Developer’s Library800 East 96th StreetIndianapolis, IN 46240 USAReader ServicesVisit our website and register this book at www.informit.com/register for convenient access toany updates, downloads, or errata that might be available for this book.