آغاز جستجوهای SQL: از مبتدی تا حرفه‌ای؛ رویکردی متفکرانه به یادگیری SQL که به شما کمک می‌کند درباره زبان و داده‌های خود فکر کنید تا بتوانید عملیات مناسب را بر روی مشکل مناسب اعمال کنید و نتایج درست را تولید کنید

This book is for anyone who has a well-designed relational database and needs to extract some information from it. You might have noticed in the previous sentence that the database must be “well designed.” I can’t overemphasize this point. If your database is badly designed, it will not be able to store accurate and consistent data, so the information your queries retrieve will always be prone to inaccuracies. If you are looking to design a database from scratch, you should read my first book, Beginning Database Design (Apress, 2007). The final chapter of this book outlines a few common design problems you are likely to come across and gives some advice about how to mitigate the impact or correct the problem. For this book, you do not need any theoretical knowledge of relational theory, as I will explain the relevant issues as they come up. The first chapter gives a brief overview of relational database theory, but it will help if you have had some experience working with databases with a few or more tables. Beginning SQL Queries: From Novice to Professional 1 Contents at a Glance 7 Contents 9 About the Author 15 About the Technical Reviewer 17 Acknowledgments 19 Introduction 21 Unknown -1 Two-Pronged Approach 21 Who This Book Is For 22 Objective of This Book 22 Relational Database Overview 23 What Is a Relational Database? 23 Introducing Data Models 24 Introducing Tables 26 Inserting and Updating Rows in a Table 27 Designing Appropriate Tables 29 Maintaining Consistency Between Tables 31 Retrieving Information from a Database 32 Relational Algebra: Specifying the Operations 33 Relational Calculus: Specifying the Result 35 Why Do We Need Both Algebra and Calculus? 36 Summary 37 Simple Queries on One Table 39 Retrieving a Subset of Rows 42 Relational Algebra for Retrieving Rows 42 Relational Calculus for Retrieving Rows 42 SQL for Retrieving Rows 43 Retrieving a Subset of Columns 44 Relational Algebra for Retrieving Columns 44 Relational Calculus for Retrieving Columns 44 SQL for Retrieving Columns 45 Using Aliases 45 Combining Subsets of Rows and Columns 46 Saving Queries 47 Specifying Conditions for Selecting Rows 47 Comparison Operators 48 Logical Operators 49 Dealing with Nulls 51 Comparing Null Values 53 Finding Nulls 54 Managing Duplicates 54 Ordering Output 57 Performing Simple Counts 57 Avoiding Common Mistakes 58 Misusing Select to Answer Questions with the Word fbothf 60 Misusing Select Operations to Answer Questions with the Word fiotf 61 Summary 61 A First Look at Joins 63 Joins in Relational Algebra 63 Cartesian Product 63 Inner Join 65 SQL for Cartesian Product and Join 66 Joins in Relational Calculus 67 Extending Join Queries 68 An Algebra Approach 69 Order of Algebra Operations 72 A Calculus Approach 73 Expressing Joins Through Diagrammatic Interfaces 75 Other Types of Joins 76 Summary 80 Nested Queries 83 IN Keyword 83 Using IN with a Nested Query 84 Being Careful with NOT and 86 EXISTS Keyword 89 Different Types of Nesting 91 Inner Queries Returning a Single Value 92 Inner Queries Returning a Set of Values 94 Inner Queries Checking for Existence 94 Using Nested Queries for Updating 95 Summary 97 Self Joins 99 Self Relationships 99 Creating a Self Join 101 Queries Involving a Self Join 102 What Are the Names of the Coaches? 103 Who Is Being Coached by Someone with a Higher Handicap? 103 List the Names of All the Members and the Names of Their Coaches 103 Who Coaches the Coaches, or Who Is My Grandmother? 105 A Calculus Approach to Self Joins 107 Questions Involving fiBthf 110 A Calculus Approach to Questions Involving fiothf 112 An Algebra Approach to Questions Involving fiothf 113 Summary 114 Self Relationships 114 Questions Involving the Word fBothf 115 More Than One Relationship Between Tables 117 Representing Multiple Relationships Between Tables 117 Algebra Approach to Two Relationships Between Tables 119 Calculus Approach to Two Relationships Between Tables 123 Business Rules 124 Summary 127 Set Operations 129 Overview of Basic Set Operations 130 Union-Compatible Tables 131 Union 133 Ensuring Union Compatibility 134 Selecting the Appropriate Columns 135 Uses for Union 136 Intersection 139 Uses of Intersection 139 The Importance of Projecting Appropriate Columns 142 Managing Without the INTERSECT Keyword 144 Difference 145 Uses of Difference 146 Managing Without the EXCEPT Keyword 148 Division 149 Projecting Appropriate Columns 151 SQL for Division 152 Summary 154 Aggregate Operations 155 Simple Aggregates 155 The COUNT Function 155 The AVG Function 158 Other Aggregate Functions 160 Grouping 161 Filtering the Result of an Aggregate Query 165 Using Aggregates to Perform Division Operations 167 Nested Queries and Aggregates 169 Summary 172 Efficiency Considerations 175 Indexes 175 Types of Indexes 176 Indexes for Efficiently Ordering Output 179 Indexes and Joins 180 What Should We Index? 182 Query Optimizer 183 What Does the Query Optimizer Consider? 183 Does the Way We Express the Query Matter? 184 Summary 189 How to Approach a Query 191 Understanding the Data 191 Determine the Relationships Between Tables 191 The Conceptual Model vs. the Implementation 193 What Tables Are Involved? 195 Look at Some Data Values 196 Big Picture Approach 196 Combine the Tables 197 Find the Subset of Rows 198 Retain the Appropriate Columns 199 Consider an Intermediate View 200 Spotting Key Words in Questions 201 And, Both, Also 201 Not, Never 204 All, Every 205 No Idea Where to Start? 205 Find Some Helpful Tables 205 Try to Answer the Question by Hand 205 Write Down a Description of the Retrieved Result 206 Is There Another Way? 207 Checking Queries 208 Check a Row That Should Be Returned 209 Check a Row That Should Not Be Returned 209 Check Boundary Conditions 209 Check Null Values 210 Summary 210 Common Problems 213 Poor Database Design 213 Data That Is Not Normalized 213 No Keys 216 Similar Data in Two Tables 217 Wrong Types 218 Problems with Data Values 219 Unexpected Nulls 219 Wrong or Inconsistent Spelling 219 Extraneous Characters in Text Fields 220 Inconsistent Case in Text Fields 221 Diagnosing Problems 222 Check Parts of Nested Queries Independently 223 Understand How the Tables Are Being Combined 223 Remove Extra WHERE Clauses 223 Retain All the Columns 224 Check Underlying Queries in Aggregates 224 Common Symptoms 224 No Rows Are Returned 224 Rows Are Missing 225 Should You Have an Outer Join? 225 Have Selection Conditions Dealt with Nulls Appropriately? 225 Are You Looking for a Match with a Text Value? 226 Have You Used AND Instead of OR? 226 Do You Have Correct Columns in Set Operations? 227 More Rows Than There Should Be 227 Did You Use NOT Instead of Difference? 227 Have You Dealt with Duplicates Appropriately? 228 Statistics or Aggregates Incorrect 229 The Order Is Wrong 229 Common Typos and Syntax Problems 229 Summary 230 Sample Database 231 Index 233 Beginning SQL Queries: From Novice to Professional......Page 1 Contents at a Glance......Page 7 Contents......Page 9 About the Author......Page 15 About the Technical Reviewer......Page 17 Acknowledgments......Page 19 Two-Pronged Approach......Page 21 Unknown......Page 0 Objective of This Book......Page 22 What Is a Relational Database?......Page 23 Introducing Data Models......Page 24 Introducing Tables......Page 26 Inserting and Updating Rows in a Table......Page 27 Designing Appropriate Tables......Page 29 Maintaining Consistency Between Tables......Page 31 Retrieving Information from a Database......Page 32 Relational Algebra: Specifying the Operations......Page 33 Relational Calculus: Specifying the Result......Page 35 Why Do We Need Both Algebra and Calculus?......Page 36 Summary......Page 37 Simple Queries on One Table......Page 39 Relational Calculus for Retrieving Rows......Page 42 SQL for Retrieving Rows......Page 43 Relational Calculus for Retrieving Columns......Page 44 Using Aliases......Page 45 Combining Subsets of Rows and Columns......Page 46 Specifying Conditions for Selecting Rows......Page 47 Comparison Operators......Page 48 Logical Operators......Page 49 Dealing with Nulls......Page 51 Comparing Null Values......Page 53 Managing Duplicates......Page 54 Performing Simple Counts......Page 57 Avoiding Common Mistakes......Page 58 Misusing Select to Answer Questions with the Word fbothf......Page 60 Summary......Page 61 Cartesian Product......Page 63 Inner Join......Page 65 SQL for Cartesian Product and Join......Page 66 Joins in Relational Calculus......Page 67 Extending Join Queries......Page 68 An Algebra Approach......Page 69 Order of Algebra Operations......Page 72 A Calculus Approach......Page 73 Expressing Joins Through Diagrammatic Interfaces......Page 75 Other Types of Joins......Page 76 Summary......Page 80 IN Keyword......Page 83 Using IN with a Nested Query......Page 84 Being Careful with NOT and ......Page 86 EXISTS Keyword......Page 89 Different Types of Nesting......Page 91 Inner Queries Returning a Single Value......Page 92 Inner Queries Checking for Existence......Page 94 Using Nested Queries for Updating......Page 95 Summary......Page 97 Self Relationships......Page 99 Creating a Self Join......Page 101 Queries Involving a Self Join......Page 102 List the Names of All the Members and the Names of Their Coaches......Page 103 Who Coaches the Coaches, or Who Is My Grandmother?......Page 105 A Calculus Approach to Self Joins......Page 107 Questions Involving fiBthf......Page 110 A Calculus Approach to Questions Involving fiothf......Page 112 An Algebra Approach to Questions Involving fiothf......Page 113 Self Relationships......Page 114 Questions Involving the Word fBothf......Page 115 Representing Multiple Relationships Between Tables......Page 117 Algebra Approach to Two Relationships Between Tables......Page 119 Calculus Approach to Two Relationships Between Tables......Page 123 Business Rules......Page 124 Summary......Page 127 Set Operations......Page 129 Overview of Basic Set Operations......Page 130 Union-Compatible Tables......Page 131 Union......Page 133 Ensuring Union Compatibility......Page 134 Selecting the Appropriate Columns......Page 135 Uses for Union......Page 136 Uses of Intersection......Page 139 The Importance of Projecting Appropriate Columns......Page 142 Managing Without the INTERSECT Keyword......Page 144 Difference......Page 145 Uses of Difference......Page 146 Managing Without the EXCEPT Keyword......Page 148 Division......Page 149 Projecting Appropriate Columns......Page 151 SQL for Division......Page 152 Summary......Page 154 The COUNT Function......Page 155 The AVG Function......Page 158 Other Aggregate Functions......Page 160 Grouping......Page 161 Filtering the Result of an Aggregate Query......Page 165 Using Aggregates to Perform Division Operations......Page 167 Nested Queries and Aggregates......Page 169 Summary......Page 172 Indexes......Page 175 Types of Indexes......Page 176 Indexes for Efficiently Ordering Output......Page 179 Indexes and Joins......Page 180 What Should We Index?......Page 182 What Does the Query Optimizer Consider?......Page 183 Does the Way We Express the Query Matter?......Page 184 Summary......Page 189 Determine the Relationships Between Tables......Page 191 The Conceptual Model vs. the Implementation......Page 193 What Tables Are Involved?......Page 195 Big Picture Approach......Page 196 Combine the Tables......Page 197 Find the Subset of Rows......Page 198 Retain the Appropriate Columns......Page 199 Consider an Intermediate View......Page 200 And, Both, Also......Page 201 Not, Never......Page 204 Try to Answer the Question by Hand......Page 205 Write Down a Description of the Retrieved Result......Page 206 Is There Another Way?......Page 207 Checking Queries......Page 208 Check Boundary Conditions......Page 209 Summary......Page 210 Data That Is Not Normalized......Page 213 No Keys......Page 216 Similar Data in Two Tables......Page 217 Wrong Types......Page 218 Wrong or Inconsistent Spelling......Page 219 Extraneous Characters in Text Fields......Page 220 Inconsistent Case in Text Fields......Page 221 Diagnosing Problems......Page 222 Remove Extra WHERE Clauses......Page 223 No Rows Are Returned......Page 224 Have Selection Conditions Dealt with Nulls Appropriately?......Page 225 Have You Used AND Instead of OR?......Page 226 Did You Use NOT Instead of Difference?......Page 227 Have You Dealt with Duplicates Appropriately?......Page 228 Common Typos and Syntax Problems......Page 229 Summary......Page 230 Sample Database......Page 231 Index......Page 233

Clare Churcher’s Beginning SQL Queries is your guide to mastering the lingua franca of the database industry: the SQL language. Good knowledge of SQL is crucial to anyone working with databases, because it is with SQL that you retrieve data, manipulate data, and generate business results. Knowing how to write good queries is the foundation for all work done in SQL, and it is a foundation that Clare lays well in her book.

• Does not bore with syntax!
• Helps you learn the underlying concepts involved in querying a database, and from there the syntax is easy
• Provides exceptionally clear examples and explanations
• Is academically sound while being practical and approachable

What you’ll learn

• Write simple queries to extract data from a single table
• Understand relational algebra and calculus and why they are important
• Combine data from many tables into one business result
• Avoid pitfalls and traps such as Cartesian products and difficulties with null values
• Summarize large amounts of data for reporting purposes
• Apply set theory to the problems of manipulating data and generating reports

Who this book is for

Beginning SQL Queries is aimed at intelligent laypeople who need to extract information from a database, and at developers and other IT professionals who are new to SQL. The book is especially useful for business intelligence analysts who must ask more complex questions of their database than their GUI–based reporting software supports. Such people might be business owners wanting to target specific customers, scientists and students needing to extract subsets of their research data, or end users wanting to make the best use of databases for their clubs and societies.

Clare Churcher 's Beginning SQL Queries is your guide to mastering the lingua franca of the database the SQL language. Good knowledge of SQL is crucial to anyone working with databases, because it is with SQL that you retrieve data, manipulate data, and generate business results. Knowing how to write good queries is the foundation for all work done in SQL, and it is a foundation that Clare lays well in her book. Beginning SQL Queries is aimed at intelligent laypeople who need to extract information from a database, and at developers and other IT professionals who are new to SQL. The book is especially useful for business intelligence analysts who must ask more complex questions of their database than their GUI-based reporting software supports. Such people might be business owners wanting to target specific customers, scientists and students needing to extract subsets of their research data, or end users wanting to make the best use of databases for their clubs and societies. Beginning Queries with SQL is a friendly and easily read guide to writing queries with the all-important — in the database world — SQL language. Anyone who does any work at all with databases needs to know something of SQL, and that is evidenced by the strong sales of such books as Learning SQL (O'Reilly) and SQL Queries for Mere Mortals (Pearson). Beginning Queries with SQL is written by the author of Beginning Database Design, an author who is garnering great reviews on Amazon due to the clarity and succinctness of her writing. Beginning with a comprehensive review of relational databases, a complete guide to writing SQL queries takes novice users step by step through the principles and techniques of simple queries, joins, nested queries, set operations, and other essentials. Original. (Beginner) Anyone who does any work at all with databases needs to know something of SQL. This is a friendly and easy-to-read guide to writing queries with the all-important - in the database world - SQL language. The author writes with exceptional clarity. "This book is a thoughtful approach to learning SQL that helps you think about the language - and about your data - so that you can apply the right operations to the right problem to generate the right results, every time."--Jacket