Solution Manual(Downloadable files)For SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition By John L. Viescas,ISBN-13:9780134858425

$100.00 $50.00

Solution Manual(Downloadablefiles)For SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition By John L. Viescas,ISBN-13:9780134858425



Solution Manual(Downloadablefiles)For SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition By John L. Viescas,ISBN-13:9780134858425



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 readers 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, students learn how to perform complex calculations on groups for sophisticated reporting, and how to partition data into windows for more flexible aggregation.

Students can practice all they need with downloadable sample databases for today’s versions of Microsoft Office Access, Microsoft SQL Server, and the open source MySQL and PostgreSQL databases.

Instructor Resouces include an Instructor Guide, PowerPoint slides, and a TestBank

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

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

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

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
Syntax     324
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

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

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

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

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