book

Learning SQL, 3rd Edition

by Alan Beaulieu

March 2020

Beginner

380 pages

9h 44m

English

O'Reilly Media, Inc.

Book available

Read now

Unlock full access

Includes

Includes Quizzes

Has Sandbox

Why Learn SQL?Why Use This Book to Do It?Structure of This BookConventions Used in This BookUsing the Examples in This BookO’Reilly Online LearningHow to Contact UsAcknowledgments
Introduction to DatabasesNonrelational Database SystemsThe Relational ModelSome TerminologyWhat Is SQL?SQL Statement ClassesSQL: A Nonprocedural LanguageSQL ExamplesWhat Is MySQL?SQL UnpluggedWhat’s in Store
Creating a MySQL DatabaseUsing the mysql Command-Line ToolMySQL Data TypesCharacter DataNumeric DataTemporal DataTable CreationStep 1: DesignStep 2: RefinementStep 3: Building SQL Schema StatementsPopulating and Modifying TablesInserting DataUpdating DataDeleting DataWhen Good Statements Go BadNonunique Primary KeyNonexistent Foreign KeyColumn Value ViolationsInvalid Date ConversionsThe Sakila Database
Query MechanicsQuery ClausesThe select ClauseColumn AliasesRemoving DuplicatesThe from ClauseTablesTable LinksDefining Table AliasesThe where ClauseThe group by and having ClausesThe order by ClauseAscending Versus Descending Sort OrderSorting via Numeric PlaceholdersTest Your KnowledgeExercise 3-1Exercise 3-2Exercise 3-3Exercise 3-4
Condition EvaluationUsing ParenthesesUsing the not OperatorBuilding a ConditionCondition TypesEquality ConditionsRange ConditionsMembership ConditionsMatching ConditionsNull: That Four-Letter WordTest Your KnowledgeExercise 4-1Exercise 4-2Exercise 4-3Exercise 4-4
What Is a Join?Cartesian ProductInner JoinsThe ANSI Join SyntaxJoining Three or More TablesUsing Subqueries as TablesUsing the Same Table TwiceSelf-JoinsTest Your KnowledgeExercise 5-1Exercise 5-2Exercise 5-3
Set Theory PrimerSet Theory in PracticeSet OperatorsThe union OperatorThe intersect OperatorThe except OperatorSet Operation RulesSorting Compound Query ResultsSet Operation PrecedenceTest Your KnowledgeExercise 6-1Exercise 6-2Exercise 6-3
Working with String DataString GenerationString ManipulationWorking with Numeric DataPerforming Arithmetic FunctionsControlling Number PrecisionHandling Signed DataWorking with Temporal DataDealing with Time ZonesGenerating Temporal DataManipulating Temporal DataConversion FunctionsTest Your KnowledgeExercise 7-1Exercise 7-2Exercise 7-3
Grouping ConceptsAggregate FunctionsImplicit Versus Explicit GroupsCounting Distinct ValuesUsing ExpressionsHow Nulls Are HandledGenerating GroupsSingle-Column GroupingMulticolumn GroupingGrouping via ExpressionsGenerating RollupsGroup Filter ConditionsTest Your KnowledgeExercise 8-1Exercise 8-2Exercise 8-3
What Is a Subquery?Subquery TypesNoncorrelated SubqueriesMultiple-Row, Single-Column SubqueriesMulticolumn SubqueriesCorrelated SubqueriesThe exists OperatorData Manipulation Using Correlated SubqueriesWhen to Use SubqueriesSubqueries as Data SourcesSubqueries as Expression GeneratorsSubquery Wrap-UpTest Your KnowledgeExercise 9-1Exercise 9-2Exercise 9-3

Outer JoinsLeft Versus Right Outer JoinsThree-Way Outer JoinsCross JoinsNatural JoinsTest Your KnowledgeExercise 10-1Exercise 10-2Exercise 10-3 (Extra Credit)
What Is Conditional Logic?The case ExpressionSearched case ExpressionsSimple case ExpressionsExamples of case ExpressionsResult Set TransformationsChecking for ExistenceDivision-by-Zero ErrorsConditional UpdatesHandling Null ValuesTest Your KnowledgeExercise 11-1Exercise 11-2
Multiuser DatabasesLockingLock GranularitiesWhat Is a Transaction?Starting a TransactionEnding a TransactionTransaction SavepointsTest Your KnowledgeExercise 12-1
IndexesIndex CreationTypes of IndexesHow Indexes Are UsedThe Downside of IndexesConstraintsConstraint CreationTest Your KnowledgeExercise 13-1Exercise 13-2
What Are Views?Why Use Views?Data SecurityData AggregationHiding ComplexityJoining Partitioned DataUpdatable ViewsUpdating Simple ViewsUpdating Complex ViewsTest Your KnowledgeExercise 14-1Exercise 14-2
Data About Datainformation_schemaWorking with MetadataSchema Generation ScriptsDeployment VerificationDynamic SQL GenerationTest Your KnowledgeExercise 15-1Exercise 15-2
Analytic Function ConceptsData WindowsLocalized SortingRankingRanking FunctionsGenerating Multiple RankingsReporting FunctionsWindow FramesLag and LeadColumn Value ConcatenationTest Your KnowledgeExercise 16-1Exercise 16-2Exercise 16-3
PartitioningPartitioning ConceptsTable PartitioningIndex PartitioningPartitioning MethodsPartitioning BenefitsClusteringShardingBig DataHadoopNoSQL and Document DatabasesCloud ComputingConclusion
Introduction to Apache DrillQuerying Files Using DrillQuerying MySQL Using DrillQuerying MongoDB Using DrillDrill with Multiple Data SourcesFuture of SQL
Chapter 3Exercise 3-1Exercise 3-2Exercise 3-3Exercise 3-4Chapter 4Exercise 4-1Exercise 4-2Exercise 4-3Exercise 4-4Chapter 5Exercise 5-1Exercise 5-2Exercise 5-3Chapter 6Exercise 6-1Exercise 6-2Exercise 6-3Chapter 7Exercise 7-1Exercise 7-2Exercise 7-3Chapter 8Exercise 8-1Exercise 8-2Exercise 8-3Chapter 9Exercise 9-1Exercise 9-2Exercise 9-3Chapter 10Exercise 10-1Exercise 10-2Exercise 10-3 (Extra Credit)Chapter 11Exercise 11-1Exercise 11-2Chapter 12Exercise 12-1Chapter 13Exercise 13-1Exercise 13-2Chapter 14Exercise 14-1Exercise 14-2Chapter 15Exercise 15-1Exercise 15-2Chapter 16Exercise 16-1Exercise 16-2Exercise 16-3

Content preview from Learning SQL, 3rd Edition

Chapter 18. SQL and Big Data

While most of the content in this book covers the various features of the SQL language when using a relational database such as MySQL, the data landscape has changed quite a bit over the past decade, and SQL is changing to meet the needs of today’s rapidly evolving environments. Many organizations that had used relational databases exclusively just a few years ago are now also housing data in Hadoop clusters, data lakes, and NoSQL databases. At the same time, companies are struggling to find ways to gain insights from the ever-growing volumes of data, and the fact that this data is now spread across multiple data stores, perhaps both on-site and in the cloud, makes this a daunting task.

Because SQL is used by millions of people and has been integrated into thousands of applications, it makes sense to leverage SQL to harness this data and make it actionable. Over the past several years, a new breed of tools has emerged to enable SQL access to structured, semi-structured, and unstructured data: tools such as Presto, Apache Drill, and Toad Data Point. This chapter explores one of these tools, Apache Drill, to demonstrate how data in different formats and stored on different servers can be brought together for reporting and analysis.

Introduction to Apache Drill

There have been numerous tools and interfaces developed to allow SQL access to data stored in Hadoop, NoSQL, Spark, and cloud-based distributed filesystems. Examples include Hive, ...