Beginning SQL Queries : From Novice to Professional

This book is for anyone who has a well-designed relational database and needs to extract 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, then it will not be able to store accurate and consistent data, and 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 .” The final chapter in this book will outline a few common design problems you are likely to come across and give some advice about how to mitigate the impact or correct the problem. Contents at a Glance......Page 6 Contents......Page 8 About the Author......Page 16 About the Technical Reviewer......Page 18 Acknowledgments......Page 20 Introduction......Page 22 Introducing Database Tables......Page 24 Attributes......Page 25 Inserting and Updating Rows in a Table......Page 26 Designing Appropriate Tables......Page 28 Introducing Data Models......Page 29 Retrieving Information from a Database......Page 32 Process Approach......Page 33 Outcome Approach......Page 34 Why We Consider Two Approaches......Page 35 Summary......Page 36 Subsets of Rows and Columns......Page 37 Using Aliases......Page 40 Comparison Operators......Page 41 Logical Operators......Page 43 Dealing with Nulls......Page 45 Comparisons Involving Null Values......Page 46 Managing Duplicates......Page 47 Ordering Output......Page 50 Avoiding Common Mistakes......Page 51 Incorrectly Using a WHERE Clause to Answer Questions with the Word “both”......Page 52 Summary......Page 54 Cartesian Product......Page 55 Inner Join......Page 57 Outcome Approach to Joins......Page 58 Extending Join Queries......Page 60 A Process Approach......Page 61 Order of Operations......Page 63 An Outcome Approach......Page 64 Expressing Joins Through Diagrammatic Interfaces......Page 65 Other Types of Joins......Page 66 Outer Joins......Page 67 Summary......Page 70 IN Keyword......Page 72 Using IN with Subqueries......Page 73 Being Careful with NOT and ......Page 75 EXISTS Keyword......Page 78 Inner Queries Returning a Single Value......Page 81 Inner Queries Checking for Existence......Page 83 Using Subqueries for Updating......Page 84 Summary......Page 85 A subquery checking for existence......Page 86 Update data......Page 87 Self Relationships......Page 88 Creating a Self Join......Page 91 What Are the Names of the Coaches?......Page 92 List the Names of All Members and the Names of Their Coaches......Page 93 Who Coaches the Coaches, or Who Is My Grandmother?......Page 95 An Outcome Approach to Self Joins......Page 97 Questions Involving “Both”......Page 100 An Outcome Approach to Questions Involving “Both”......Page 101 A Process Approach to Questions Involving “Both”......Page 102 Questions Involving the Word “Both”......Page 103 Two Relationships Between the Same Tables......Page 105 Extracting Information from Multiple Relationships......Page 109 Process Approach......Page 110 Outcome Approach......Page 113 Business Rules......Page 114 Summary......Page 117 Overview of Basic Set Operations......Page 118 Union-Compatible Tables......Page 120 Ensuring Union Compatibility......Page 123 Union......Page 124 Selecting the Appropriate Columns......Page 125 Uses of Union......Page 127 Union and Full Outer Joins......Page 128 Intersection......Page 130 Uses of Intersection......Page 131 The Importance of Projecting Appropriate Columns......Page 134 Managing Without the INTERSECT Keyword......Page 135 Uses of Difference......Page 137 Managing Without the EXCEPT Keyword......Page 140 Division......Page 141 Projecting Appropriate Columns......Page 143 SQL for Division......Page 144 Summary......Page 145 Difference......Page 146 Division......Page 147 The COUNT() Function......Page 148 Managing Nulls......Page 149 The AVG() Function......Page 151 Managing Duplicates......Page 152 The ROUND() Function......Page 153 Grouping......Page 154 Filtering the Result of an Aggregate Query......Page 159 Using Aggregates to Perform Division Operations......Page 161 Nested Queries and Aggregates......Page 163 Summary......Page 165 Simple Aggregates......Page 166 Partitions......Page 168 Cumulative Aggregates......Page 169 Ranking......Page 171 Combining Ordering with Partitions......Page 172 Framing......Page 175 OVER(ROWS BETWEEN AND )......Page 178 What Happens to a Query......Page 179 Storing Records in Order......Page 181 Clustered Index......Page 182 Non-Clustered Indexes......Page 183 Clustered Index on a Compound Key......Page 184 Updating Indexes......Page 185 Join Techniques......Page 186 Nested Loops......Page 187 Merge Join......Page 188 Different SQL Expressions for Joins......Page 189 Use the Tools......Page 191 Determine the Relationships Between Tables......Page 192 Real World Versus Implementation......Page 195 Look at Some Data Values......Page 197 Combine the Tables......Page 198 Retain the Appropriate Columns......Page 200 Spotting Keywords in Questions......Page 201 And, Both, Also......Page 202 Not, Never......Page 204 All, Every......Page 205 Try to Answer the Question by Hand......Page 206 Are There Alternatives?......Page 207 Checking Queries......Page 208 Check Boundary Conditions......Page 209 Summary......Page 210 Data That Is Not Normalized......Page 212 Tables with No Primary Key......Page 215 Similar Data in Two Tables......Page 216 Inappropriate Types......Page 217 Unexpected Nulls......Page 218 Extraneous Characters in Text Fields......Page 219 Diagnosing Problems......Page 220 Retain All the Columns......Page 221 Should You Have an Outer Join?......Page 222 Have You Used AND Instead of OR?......Page 223 Did You Use NOT Instead of Difference?......Page 224 Incorrect Statistics or Aggregates......Page 225 Common Typos and Syntax Problems......Page 226 Summary......Page 227 Appendix 1: Example Database......Page 228 Introduction......Page 230 Relations, Tuples, and Attributes......Page 231 Relational Algebra: Specifying the Operations......Page 233 Select......Page 234 Combining Select and Project......Page 235 Cartesian Product......Page 236 Inner Join......Page 237 Union, Difference, and Intersection......Page 238 Division......Page 240 Free and Bound Variables......Page 242 Existential Quantifier and SQL......Page 243 Universal Quantifier and SQL......Page 245 Algebra......Page 246 Calculus......Page 247 Conclusion......Page 248 Index......Page 249 Get started on mastering the one language binding the entire database industry. That language is SQL, and how it works is must-have knowledge for anyone involved with relational databases, and surprisingly also for anyone involved with NoSQL databases. SQL is universally used in querying and reporting on large data sets in order to generate knowledge to drive business decisions.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. Every relational database supports SQL for its expressiveness in writing queries underlying reports and business intelligence dashboards. Knowing how to write good queries is the foundation for all work done in SQL, and it is a foundation that Clare Churcher's book, Beginning SQL Queries, 2nd Edition, lays well. What You Will LearnWrite simple queries to extract datafrom a single tableCombine data from many tables into one business result using set operationsTranslate natural language questions into database queries providing meaningful information to the businessAvoid errors associated with duplicated and null valuesSummarize data with amazing ease using the newly-added feature of window functionsTackle tricky queries with confidence that you are generating correct resultsInvestigate and understand the effects of indexes on the efficiency of queriesWho This Book Is ForBeginning SQL Queries, 2nd Edition 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 reportingsoftware 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. Get started on mastering the one language binding the entire database industry. That language is SQL, and how it works is must-have knowledge for anyone involved with relational databases, and surprisingly also for anyone involved with NoSQL databases. SQL is universally used in querying and reporting on large data sets in order to generate knowledge to drive business decisions. 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. Every relational database supports SQL for its expressiveness in writing queries underlying reports and business intelligence dashboards. Knowing how to write good queries is the foundation for all work done in SQL, and it is a foundation that Clare Churcher's book, Beginning SQL Queries, 2nd Edition, lays well. Write simple queries to extract data from a single table Combine data from many tables into one business result using set operations Translate natural language questions into database queries providing meaningful information to the business Avoid errors associated with duplicated and null values Summarize data with amazing ease using the newly-added feature of window functions Tackle tricky queries with confidence that you are generating correct results Investigate and understand the effects of indexes on the efficiency of queries. 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 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.