Home > Store

SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition

eBook (Watermarked)

  • Your Price: $38.39
  • List Price: $47.99
  • Includes EPUB and PDF
  • About eBook Formats
  • This eBook includes the following formats, accessible from your Account page after purchase:

    ePub EPUB The open industry format known for its reflowable content and usability on supported mobile devices.

    Adobe Reader PDF The popular standard, used most often with the free Acrobat® Reader® software.

    This eBook requires no passwords or activation to read. We customize your eBook by discreetly watermarking it with your name, making it uniquely yours.

Also available in other formats.

Register your product to gain access to bonus material or receive a coupon.

Description

  • Copyright 2018
  • Dimensions: 7" x 9-1/8"
  • Pages: 960
  • Edition: 4th
  • eBook (Watermarked)
  • ISBN-10: 0-13-485841-7
  • ISBN-13: 978-0-13-485841-8

The #1 Easy, Common-Sense Guide to SQL Queries—Updated with More Advanced Techniques and Solutions

Foreword by Keith W. Hare, Vice Chair, USA SQL Standards Committee

SQL Queries for Mere Mortals has earned worldwide praise as the clearest, simplest tutorial on writing effective queries with the latest SQL standards and database applications. Now, author John L. Viescas has updated this hands-on classic with even more advanced and valuable techniques.

Step by step, Viescas guides you through creating reliable queries for virtually any current SQL-based database. He demystifies all aspects of SQL query writing, from simple data selection and filtering to joining multiple tables and modifying sets of data.

Building on the basics, Viescas shows how to solve challenging real-world problems, including applying multiple complex conditions on one table, performing sophisticated logical evaluations, and using unlinked tables to think “outside the box.”

In two brand-new chapters, you learn how to perform complex calculations on groups for sophisticated reporting, and how to partition data into windows for more flexible aggregation.

Practice all you want with downloadable sample databases for today’s versions of Microsoft Office Access, Microsoft SQL Server, and the open source MySQL and PostgreSQL databases. Whether you’re a DBA, developer, user, or student, there’s no better way to master SQL.

Coverage includes:

  • Getting started: understanding what relational databases are, and ensuring that your database structures are sound
  • SQL basics: using SELECT statements, creating expressions, sorting information with ORDER BY, and filtering data using WHERE
  • Summarizing and grouping data with GROUP BY and HAVING clauses
  • Drawing data from multiple tables: using INNER JOIN, OUTER JOIN, and UNION operators, and working with subqueries
  • Modifying data sets with UPDATE, INSERT, and DELETE statements
  • Advanced queries: complex NOT and AND, conditions, if-then-else using CASE, unlinked tables, driver tables, and more
  • NEW! Using advanced GROUP BY keywords to create subtotals, roll-ups, and more
  • NEW! Applying window functions to answer more sophisticated questions, and gain deeper insight into your data
Software-Independent Approach!
If you work with database software such as Access, MS SQL Server, Oracle, DB2, MySQL, Ingres, or any other SQL-based program, this book could save you hours of time and aggravation—before you write a single query!

Sample Content

Sample Pages

Download the sample pages (includes Chapter 7)

Table of Contents

Foreword     xxi
Preface     xxii
Acknowledgments     xxiv
About the Author     xxv

Introduction     1
Are You a Mere Mortal?     1
About This Book     2
What This Book Is Not     4
How to Use This Book     4
Reading the Diagrams Used in This Book     5
Sample Databases Used in This Book     9
    “Follow the Yellow Brick Road”     12

PART I:  RELATIONAL DATABASES AND SQL     13
Chapter 1:  What Is Relational?     15

Types of Databases     15
A Brief History of the Relational Model     16
    In the Beginning . . .     16
    Relational Database Systems     17
Anatomy of a Relational Database     19
    Tables     20
    Columns     21
    Rows     21
    Keys     22
    Views     23
    Relationships     25
What’s in It for You?     29
    Where Do You Go from Here?     30
Summary     31
Chapter  2:  Ensuring Your Database Structure Is Sound     33
Why Is this Chapter Here?     34
Why Worry about Sound Structures?     34
Fine-Tuning Columns     35
    What’s in a Name? (Part One)     35
    Smoothing Out the Rough Edges     38
    Resolving Multipart Columns     40
    Resolving Multivalued Columns     43
Fine-Tuning Tables     46
    What’s in a Name? (Part Two)     46
    Ensuring a Sound Structure     48
    Resolving Unnecessary Duplicate Columns     50
    Identification Is the Key     56
Establishing Solid Relationships     60
    Establishing a Deletion Rule     63
    Setting the Type of Participation     64
    Setting the Degree of Participation     66
Is That All?     69
Summary     69
Chapter 3:  A Concise History of SQL     71
The Origins of SQL     72
Early Vendor Implementations     73
“. . . And Then There Was a Standard”     75
Evolution of the ANSI/ISO Standard     76
    Other SQL Standards     79
Commercial Implementations     83
What the Future Holds     83
Why Should You Learn SQL?     84
Which Version of SQL Does this Book Cover?     84
Summary     85

PART II:  SQL BASICS     87
Chapter 4:  Creating a Simple Query     89

Introducing SELECT     90
The SELECT Statement     91
A Quick Aside: Data versus Information     93
Translating Your Request into SQL     95
    Expanding the Field of Vision     100
    Using a Shortcut to Request All Columns     101
Eliminating Duplicate Rows     103
Sorting Information     105
    First Things First: Collating Sequences     107
    Let’s Now Come to Order     108
Saving Your Work     111
Sample Statements     113
Summary     122
Problems for You to Solve     123
Chapter 5:  Getting More Than Simple Columns     125
What Is an Expression?     126
What Type of Data Are You Trying to Express?     127
Changing Data Types: The CAST Function     130
Specifying Explicit Values     132
    Character String Literals     133
    Numeric Literals     135
    Datetime Literals     135
Types of Expressions     138
    Concatenation     138
    Mathematical Expressions     142
    Date and Time Arithmetic     146
Using Expressions in a SELECT Clause     150
    Working with a Concatenation Expression     151
    Naming the Expression     152
    Working with a Mathematical Expression     154
    Working with a Date Expression     156
    A Brief Digression: Value Expressions     157
That “Nothing” Value: Null     159
    Introducing Null     160
    The Problem with Nulls     162
Sample Statements     163
Summary     172
Problems for You to Solve     173
Chapter 6:  Filtering Your Data     175
Refining What You See Using WHERE     176
    The WHERE Clause     176
    Using a WHERE Clause     179
Defining Search Conditions     181
    Comparison     181
    Range     189
    Set Membership     192
    Pattern Match     194
    Null     199
    Excluding Rows with NOT     201
Using Multiple Conditions     204
    Introducing AND and OR     205
    Excluding Rows: Take Two     211
    Order of Precedence     214
    Checking for Overlapping Ranges     219
Nulls Revisited: A Cautionary Note     221
Expressing Conditions in Different Ways     225
Sample Statements     226
Summary     234
Problems for You to Solve     235

PART III:  WORKING WITH MULTIPLE TABLES     239
Chapter 7:  Thinking in Sets     241

What Is a Set, Anyway?     242
Operations on Sets     243
Intersection     244
    Intersection in Set Theory     244
    Intersection between Result Sets     246
    Problems You Can Solve with an Intersection     249
Difference     250
    Difference in Set Theory     250
    Difference between Result Sets     252
    Problems You Can Solve with Difference     256
Union     257
    Union in Set Theory     257
    Combining Result Sets Using a Union     259
    Problems You Can Solve with Union     261
SQL Set Operations     262
    Classic Set Operations versus SQL     262
    Finding Common Values: INTERSECT     262
    Finding Missing Values: EXCEPT (DIFFERENCE)     265
    Combining Sets: UNION     268
Summary     271
Chapter 8:  INNER JOINs     273
What Is a JOIN?     273
The INNER JOIN     274
    What’s “Legal” to JOIN?     275
    Column References     275
    Syntax     276
    Check Those Relationships!     291
Uses for INNER JOINs     293
    Find Related Rows     293
    Find Matching Values     293
Sample Statements     294
    Two Tables     295
    More Than Two Tables     300
    Looking for Matching Values     306
Summary     316
Problems for You to Solve     316
Chapter 9:  OUTER JOINs     321
What Is an OUTER JOIN?     321
The LEFT/RIGHT OUTER JOIN     323
    Syntax     324
The FULL OUTER JOIN     344
    Syntax     344
    FULL OUTER JOIN on Non-Key Values     347
    UNION JOIN     348
Uses for OUTER JOINs     349
    Find Missing Values     349
    Find Partially Matched Information     349
Sample Statements     350
Summary     365
Problems for You to Solve     366
Chapter 10:  UNIONs     369
What Is a UNION?     369
Writing Requests with UNION     372
    Using Simple SELECT Statements     372
    Combining Complex SELECT Statements     375
    Using UNION More Than Once     379
    Sorting a UNION     381
Uses for UNION     383
Sample Statements     385
Summary     395
Problems for You to Solve     396
Chapter 11:  Subqueries     399
What Is a Subquery?     400
    Row Subqueries     400
    Table Subqueries     402
    Scalar Subqueries     402
Subqueries as Column Expressions     402
    Syntax     402
    An Introduction to Aggregate Functions: COUNT and MAX     406
Subqueries as Filters     408
    Syntax     408
    Special Predicate Keywords for Subqueries     411
Uses for Subqueries     422
    Build Subqueries as Column Expressions     422
    Use Subqueries as Filters     423
Sample Statements     424
    Subqueries in Expressions     425
    Subqueries in Filters     430
Summary     437
Problems for You to Solve     438

PART IV:  SUMMARIZING AND GROUPING DATA     441
Chapter 12:  Simple Totals     443

Aggregate Functions     444
    Counting Rows and Values with COUNT     446
    Computing a Total with SUM     450
    Calculating a Mean Value with AVG     451
    Finding the Largest Value with MAX     452
    Finding the Smallest Value with MIN     454
    Using More Than One Function     455
Using Aggregate Functions in Filters     457
Sample Statements     459
Summary     466
Problems for You to Solve     467
Chapter 13:  Grouping Data     471
Why Group Data?     472
The GROUP BY Clause     475
    Syntax     475
    Mixing Columns and Expressions     481
    Using GROUP BY in a Subquery in a WHERE Clause     483
    Simulating a SELECT DISTINCT Statement     484
“Some Restrictions Apply”     485
    Column Restrictions     486
    Grouping on Expressions     488
Uses for GROUP BY     490
Sample Statements     491
Summary     501
Problems for You to Solve     501
Chapter 14:  Filtering Grouped Data     505
A New Meaning for “Focus Groups”     506
Where You Filter Makes a Difference     510
    Should You Filter in WHERE or in HAVING?     510
    Avoiding the HAVING COUNT Trap     513
Uses for HAVING     518
Sample Statements     519
Summary     527
Problems for You to Solve     528

PART V:  MODIFYING SETS OF DATA     533
Chapter 15:  Updating Sets of Data     535

What Is an UPDATE?     536
The UPDATE Statement     536
    Using a Simple UPDATE Expression     537
    A Brief Aside: Transactions     540
    Updating Multiple Columns     541
    Using a Subquery to Filter Rows     543
Some Database Systems Allow a JOIN in the UPDATE Clause     546
    Using a Subquery UPDATE Expression     548
Uses for UPDATE     551
Sample Statements     552
Summary     569
Problems for You to Solve     569
Chapter 16:  Inserting Sets of Data     573
What Is an INSERT?     573
The INSERT Statement     575
    Inserting Values     575
    Generating the Next Primary Key Value     578
    Inserting Data by Using SELECT     581
Uses for INSERT     587
Sample Statements     588
Summary     598
Problems for You to Solve     598
Chapter 17:  Deleting Sets of Data     603
What Is a DELETE?     603
The DELETE Statement     604
    Deleting All Rows     605
    Deleting Some Rows     607
Uses for DELETE     611
Sample Statements     612
Summary     620
Problems for You to Solve     621

PART VI:  INTRODUCTION TO SOLVING TOUGH PROBLEMS     625
Chapter 18:  “NOT” and “AND” Problems     627

A Short Review of Sets     628
    Sets with Multiple AND Criteria     628
    Sets with Multiple NOT Criteria     629
    Sets Including Some Criteria but Excluding Others     630
Finding Out the “Not” Case     632
    Using OUTER JOIN     632
    Using NOT IN     635
    Using NOT EXISTS     637
    Using GROUP BY/HAVING     638
Finding Multiple Matches in the Same Table     641
    Using INNER JOIN     642
    Using IN     644
    Using EXISTS     646
    Using GROUP BY/HAVING     648
Sample Statements     652
Summary     671
Problems for You to Solve     672
Chapter 19:  Condition Testing     677
Conditional Expressions (CASE)     678
    Why Use CASE?     678
    Syntax     678
Solving Problems with CASE     683
    Solving Problems with Simple CASE     683
    Solving Problems with Searched CASE     688
    Using CASE in a WHERE Clause     691
Sample Statements     692
Summary     705
Problems for You to Solve     706
Chapter 20:  Using Unlinked Data and “Driver” Tables     709
What Is Unlinked Data?     710

    Deciding When to Use a CROSS JOIN     713
Solving Problems with Unlinked Data     714
Solving Problems Using “Driver” Tables     717
    Setting Up a Driver Table     717
    Using a Driver Table     720
Sample Statements     725
    Examples Using Unlinked Tables     726
    Examples Using Driver Tables     736
Summary     743
Problems for You to Solve     744
Chapter 21:  Performing Complex Calculations on Groups     749
Grouping in Sub-Groups     750
Extending the GROUP BY Clause     753
    Syntax     753
Getting Totals in a Hierarchy Using Rollup     754
Calculating Totals on Combinations Using CUBE     765
Creating a Union of Totals with GROUPING SETS     771
Variations on Grouping Techniques     775
Sample Statements     780
    Examples using ROLLUP     781
    Examples using CUBE     783
    Examples using GROUPING SETS     786
Summary     788
Problems for You to Solve     789
Chapter 22:  Partitioning Data into Windows     793
What You Can Do With a “Window” into Your Data     794
    Syntax     798
Calculating a Row Number     814
Ranking Data     818
Splitting Data into Quintiles     824
Using Windows with Aggregate Functions     827
Sample Statements     834
    Examples Using ROW_NUMBER     835
    Examples Using RANK, DENSE_RANK, and PERCENT_RANK     838
    Examples Using NTILE     842
    Examples Using Aggregate Functions     844
Summary     852
Problems for You to Solve     853
In Closing     857

PART VII:  APPENDICES     859
Appendix A:  SQL Standard Diagrams     861

Appendix B:  Schema for the Sample Databases     877
Sales Orders Example Database     878
Sales Orders Modify Database     879
Entertainment Agency Example Database     880
Entertainment Agency Modify Database     881
School Scheduling Example Database     882
School Scheduling Modify Database     883
Bowling League Example Database     884
Bowling League Modify Database     885
Recipes Database     886
“Driver” Tables     887
Appendix C:  Date and Time Types, Operations, and Functions     889
IBM DB2     889
Microsoft Access     893
Microsoft SQL Server     895
MySQL     897
Oracle     901
PostgreSQL     904
Appendix D:  Suggested Reading     907
Database Books     907
Books on SQL     908
Index     909

Updates

Errata

We've made every effort to ensure the accuracy of this book and its companion content. Any errors that have been confirmed since this book was published can be downloaded below.

Download the errata

Submit Errata

More Information

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020