book

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

by John L. Viescas

February 2018

Intermediate to advanced

960 pages

22h 7m

English

Addison-Wesley Professional

Read now

Unlock full access

Are You a Mere Mortal?About This BookWhat This Book Is NotHow to Use This BookReading the Diagrams Used in This BookSample Databases Used in This Book“Follow the Yellow Brick Road”

Topics Covered in This ChapterTypes of DatabasesA Brief History of the Relational ModelIn the Beginning …Relational Database SystemsAnatomy of a Relational DatabaseTablesColumnsRowsKeysViewsRelationshipsWhat’s in It for You?Where Do You Go from Here?Summary
Topics Covered in This ChapterWhy Is this Chapter Here?Why Worry about Sound Structures?Fine-Tuning ColumnsWhat’s in a Name? (Part One)Smoothing Out the Rough EdgesResolving Multipart ColumnsResolving Multivalued ColumnsFine-Tuning TablesWhat’s in a Name? (Part Two)Ensuring a Sound StructureResolving Unnecessary Duplicate ColumnsIdentification Is the KeyEstablishing Solid RelationshipsEstablishing a Deletion RuleSetting the Type of ParticipationSetting the Degree of ParticipationIs That All?Summary
Topics Covered in This ChapterThe Origins of SQLEarly Vendor Implementations“… And Then There Was a Standard”Evolution of the ANSI/ISO StandardOther SQL StandardsCommercial ImplementationsWhat the Future HoldsWhy Should You Learn SQL?Which Version of SQL Does this Book Cover?Summary
Topics Covered in This ChapterIntroducing SELECTThe SELECT StatementA Quick Aside: Data versus InformationTranslating Your Request into SQLExpanding the Field of VisionUsing a Shortcut to Request All ColumnsEliminating Duplicate RowsSorting InformationFirst Things First: Collating SequencesLet’s Now Come to OrderSaving Your WorkSample StatementsSummaryProblems for You to Solve
Topics Covered in This ChapterWhat Is an Expression?What Type of Data Are You Trying to Express?Changing Data Types: The CAST FunctionSpecifying Explicit ValuesCharacter String LiteralsNumeric LiteralsDatetime LiteralsTypes of ExpressionsConcatenationMathematical ExpressionsDate and Time ArithmeticUsing Expressions in a SELECT ClauseWorking with a Concatenation ExpressionNaming the ExpressionWorking with a Mathematical ExpressionWorking with a Date ExpressionA Brief Digression: Value ExpressionsThat “Nothing” Value: NullIntroducing NullThe Problem with NullsSample StatementsSummaryProblems for You to Solve
Topics Covered in This ChapterRefining What You See Using WHEREThe WHERE ClauseUsing a WHERE ClauseDefining Search ConditionsComparisonRangeSet MembershipPattern MatchNullExcluding Rows with NOTUsing Multiple ConditionsIntroducing AND and ORExcluding Rows: Take TwoOrder of PrecedenceChecking for Overlapping RangesNulls Revisited: A Cautionary NoteExpressing Conditions in Different WaysSample StatementsSummaryProblems for You to Solve
Topics Covered in This ChapterWhat Is a Set, Anyway?Operations on SetsIntersectionIntersection in Set TheoryIntersection between Result SetsProblems You Can Solve with an IntersectionDifferenceDifference in Set TheoryDifference between Result SetsProblems You Can Solve with DifferenceUnionUnion in Set TheoryCombining Result Sets Using a UnionProblems You Can Solve with UnionSQL Set OperationsClassic Set Operations versus SQLFinding Common Values: INTERSECTFinding Missing Values: EXCEPT (DIFFERENCE)Combining Sets: UNIONSummary
Topics Covered in This ChapterWhat Is a JOIN?The INNER JOINWhat’s “Legal” to JOIN?Column ReferencesSyntaxCheck Those Relationships!Uses for INNER JOINsFind Related RowsFind Matching ValuesSample StatementsTwo TablesMore Than Two TablesLooking for Matching ValuesSummaryProblems for You to Solve
Topics Covered in This ChapterWhat Is an OUTER JOIN?The LEFT/RIGHT OUTER JOINSyntaxThe FULL OUTER JOINSyntaxFULL OUTER JOIN on Non-Key ValuesUNION JOINUses for OUTER JOINsFind Missing ValuesFind Partially Matched InformationSample StatementsSummaryProblems for You to Solve
Topics Covered in This ChapterWhat Is a UNION?Writing Requests with UNIONUsing Simple SELECT StatementsCombining Complex SELECT StatementsUsing UNION More Than OnceSorting a UNIONUses for UNIONSample StatementsSummaryProblems for You to Solve
Topics Covered in This ChapterWhat Is a Subquery?Row SubqueriesTable SubqueriesScalar SubqueriesSubqueries as Column ExpressionsSyntaxAn Introduction to Aggregate Functions: COUNT and MAXSubqueries as FiltersSyntaxSpecial Predicate Keywords for SubqueriesUses for SubqueriesBuild Subqueries as Column ExpressionsUse Subqueries as FiltersSample StatementsSubqueries in ExpressionsSubqueries in FiltersSummaryProblems for You to Solve
Topics Covered in This ChapterAggregate FunctionsCounting Rows and Values with COUNTComputing a Total with SUMCalculating a Mean Value with AVGFinding the Largest Value with MAXFinding the Smallest Value with MINUsing More Than One FunctionUsing Aggregate Functions in FiltersSample StatementsSummaryProblems for You to Solve
Topics Covered in This ChapterWhy Group Data?The GROUP BY ClauseSyntaxMixing Columns and ExpressionsUsing GROUP BY in a Subquery in a WHERE ClauseSimulating a SELECT DISTINCT Statement“Some Restrictions Apply”Column RestrictionsGrouping on ExpressionsUses for GROUP BYSample StatementsSummaryProblems for You to Solve
Topics Covered in This ChapterA New Meaning for “Focus Groups”Where You Filter Makes a DifferenceShould You Filter in WHERE or in HAVING?Avoiding the HAVING COUNT TrapUses for HAVINGSample StatementsSummaryProblems for You to Solve
Topics Covered in This ChapterWhat Is an UPDATE?The UPDATE StatementUsing a Simple UPDATE ExpressionA Brief Aside: TransactionsUpdating Multiple ColumnsUsing a Subquery to Filter RowsSome Database Systems Allow a JOIN in the UPDATE ClauseUsing a Subquery UPDATE ExpressionUses for UPDATESample StatementsSummaryProblems for You to Solve
Topics Covered in This ChapterWhat Is an INSERT?The INSERT StatementInserting ValuesGenerating the Next Primary Key ValueInserting Data by Using SELECTUses for INSERTSample StatementsSummaryProblems for You to Solve
Topics Covered in This ChapterWhat Is a DELETE?The DELETE StatementDeleting All RowsDeleting Some RowsUses for DELETESample StatementsSummaryProblems for You to Solve
Topics Covered in This ChapterA Short Review of SetsSets with Multiple AND CriteriaSets with Multiple NOT CriteriaSets Including Some Criteria but Excluding OthersFinding Out the “Not” CaseUsing OUTER JOINUsing NOT INUsing NOT EXISTSUsing GROUP BY/HAVINGFinding Multiple Matches in the Same TableUsing INNER JOINUsing INUsing EXISTSUsing GROUP BY/HAVINGSample StatementsSummaryProblems for You to Solve
Topics Covered in This ChapterConditional Expressions (CASE)Why Use CASE?SyntaxSolving Problems with CASESolving Problems with Simple CASESolving Problems with Searched CASEUsing CASE in a WHERE ClauseSample StatementsSummaryProblems for You to Solve
Topics Covered in This ChapterWhat Is Unlinked Data?Deciding When to Use a CROSS JOINSolving Problems with Unlinked DataSolving Problems Using “Driver” TablesSetting Up a Driver TableUsing a Driver TableSample StatementsExamples Using Unlinked TablesExamples Using Driver TablesSummaryProblems for You to Solve
Topics in this ChapterGrouping in Sub-GroupsExtending the GROUP BY ClauseSyntaxGetting Totals in a Hierarchy Using RollupCalculating Totals on Combinations Using CUBECreating a Union of Totals with GROUPING SETSVariations on Grouping TechniquesSample StatementsExamples using ROLLUPExamples using CUBEExamples using GROUPING SETSSummaryProblems for You to Solve
Topics in this ChapterWhat You Can Do With a “Window” into Your DataSyntaxCalculating a Row NumberRanking DataSplitting Data into QuintilesUsing Windows with Aggregate FunctionsSample StatementsExamples Using ROW_NUMBERExamples Using RANK, DENSE_RANK, and PERCENT_RANKExamples Using NTILEExamples Using Aggregate FunctionsSummaryProblems for You to Solve
Sales Orders Example DatabaseSales Orders Modify DatabaseEntertainment Agency Example DatabaseEntertainment Agency Modify DatabaseSchool Scheduling Example DatabaseSchool Scheduling Modify DatabaseBowling League Example DatabaseBowling League Modify DatabaseRecipes Database“Driver” Tables
IBM DB2Microsoft AccessMicrosoft SQL ServerMySQLOraclePostgreSQL
Database BooksBooks on SQL

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

22Partitioning Data into Windows

In my head there are several windows, that I do know, but perhaps it is always the same one, open variously on the parading universe.

SAMUEL BECKETT

Topics in this Chapter

What You Can Do with a “Window” into Your Data

Calculating a Row Number

Ranking Data

Splitting Data into Quintiles

Using Windows with Aggregate Functions

Sample Statements

Summary

Problems for You to Solve

I’ve shown you several different ways that you can group and aggregate data, but I still haven’t shown you everything.

In earlier incarnations of the SQL standards, there really was no ability to work with data where the results depended on adjacent rows. It was always felt that the order of the rows didn’t matter, as long as you could match ...