Getting Started with SQL and Databases: Managing and Manipulating Data with SQL

Table of Contents....5 About the Author....14 About the Technical Reviewer....15 Acknowledgments....16 Introduction....17 Chapter 1: Starting with SQL....20 Basic SELECT Statement....21 Case Sensitivity....22 Spacing....22 Clause Ordering....23 The Semicolon (;)....23 Selecting Specific Columns....24 Column Order....25 Layout....25 Using SELECT ....26 Calculated Columns....26 Aliases....27 Comments....28 Block Comments....29 Uses of Comments....29 Filtering Rows....30 Clause Ordering....31 Placing the Semicolon....32 Ordering the Results....32 Clause Order....33 Distinct Rows....34 Summary....37 Writing SQL....38 Columns....39 Comments....39 Filtering Data....39 Row Order....40 Clause Order....40 Coming Up....40 Chapter 2: Database....41 About the Sample Database....41 Database....42 Database Terminology....43 Data vs. Value....43 Tables....44 Table Terminology....44 Normalized Tables....45 Data Is Atomic....45 Columns Are Independent....46 Columns Are of a Single Type....47 Rows Are Unordered....49 Rows Are Unique....49 Rows Are Independent....50 Column Names Are Unique....50 Columns Are Unordered....51 Multiple Values....51 Using Related Tables....52 Example: Paintings and Artists....52 Examples of Alternative Terminology....53 A More Complex Relationship....54 Summary....55 Terms....55 Normalized Tables....56 Multiple Values....56 Coming Up....57 Chapter 3: Filtering Data....58 The WHERE Clause....58 Unrelated Assertions....61 All and Nothing....61 Dealing with NULL....62 Deliberately Ignoring NULLs....63 Finding NULLs....64 Numbers....66 Discrete vs. Continuous Values....66 Strings....69 Quotes....70 More on MySQLMariaDB Modes....71 More on Double and Single Quotes....72 Case Sensitivity....74 Trailing Spaces....75 Filtering with String Functions....76 Handling Quotes and Apostrophes....78 Before and After Strings....79 Dates....80 Dates Are Not Strings....81 Alternative Date Formats....82 Date Comparisons....83 Filtering with a Date Calculation....86 Multiple Assertions....87 AND and OR....87 The IN Operator....93 Derived Lists....95 Wildcard Matches....98 Case Sensitivity and Patterns....100 Pattern Characters....101 Wildcards with Non-strings....104 Extensions to Wildcards....106 Regular Expressions (PostgreSQL, MySQLMariaDB, Oracle)....106 Simpler Extensions (PostgreSQL, MSSQL)....107 A Simple Pattern Match Example....108 Summary....109 NULL....110 Numbers....110 Strings....110 Dates....111 Multiple Assertions....111 The IN Operator....111 Wildcard Matches....111 Coming Up....112 Chapter 4: Ordering Results....113 Using the ORDER BY Clause....113 Sort Direction....115 Missing Data (NULL)....116 Data Types....118 Case Sensitivity and Collation....122 Multiple Columns....123 Interdependence of Columns....125 Sort Direction on Multiple Columns....126 Sorting by Calculated Columns....128 Limiting the Number of Results....131 Paging....132 Using LIMIT OFFSET (MySQLMariaDB, SQLite, and PostgreSQL)....133 Using TOP (MSSQL)....133 Fetching a Random Row....134 Nonalphabetical String Order....135 Special Strings....136 Summary....137 Sorting with ORDER BY....137 Limiting Results....138 Sorting Strings....138 Coming Up....138 Chapter 5: Calculating Column Values....139 Testing Calculations....141 Emulating Variables....142 Some Basic Calculations....143 Basic Number Calculations....143 Basic String Calculations....144 Basic Date Calculations....145 Working with NULL....145 Using Aliases....151 Aliases Without AS....152 Awkward Aliases....154 Calculating with Numbers....156 Arithmetic Operators....156 Integers....157 Remainder....159 Extra Decimals....160 Mathematical Functions....161 Approximation Functions....162 Formatting Functions....163 Calculating with Dates....165 Simple Calculations....165 Age Calculations....167 Extracting Parts of a Date....169 Date Extraction in PostgreSQL, MariaDBMySQL, and Oracle....169 Date Extraction in Microsoft SQL....170 Extracting a Date from a Datetime....171 Formatting a Date....171 Date Formatting in PostgreSQL and Oracle....172 Date Formatting in MariaDBMySQL....173 Date Formatting in Microsoft SQL Server....173 Using a Formatted Date for Grouping by Month....174 Using a Formatted Date for Grouping by Weekday....175 Strings....176 Character Functions....177 String Length....178 Searching for a Substring....178 Replace....178 Change Case....179 Trim Spaces....179 Substrings....179 Subqueries....181 The CASE Expression....185 Casting to Different Data Types....189 The cast() Function....190 Casting to a String....191 Casting Date Literals....193 Creating a View....193 Using Views in Microsoft SQL....196 Summary....197 Data Types....197 NULLs....197 Aliases....198 Subqueries....198 The CASE Expression....198 Casting a Value....198 Views....199 Coming Up....199 Chapter 6: Joining Tables....200 How a Join Works....201 Joining the Tables....204 Alternative Syntax....204 Selecting the Results....206 Table Aliases....208 Developing a Price List....209 Join Types....210 The INNER JOIN....212 The LEFT OUTER JOIN and RIGHT OUTER JOIN....212 The Preferred Outer Join....214 Some Recommendations on JOINS....215 (Almost) Always Alias Your Tables....216 Which Table Comes First?....216 Decide Whether You Use INNER and OUTER....216 Finishing the Price List....216 Joining Many Tables....217 Building a Larger JOIN....219 Simplifying the Result....221 Revisiting Some Subqueries....224 A More Complex Join....226 Using a Self-Join....231 Summary....235 Syntax....235 Table Aliases....235 The ON Clause....235 Join Types....236 Coming Up....236 Chapter 7: Aggregating Data....237 Counting Data....238 Counting Values....238 How Aggregates Work....239 Counting Selectively....240 Distinct Values....246 Summarizing Numbers....247 Bad Examples....249 Scales of Measurement....250 Aggregating Calculated Data....251 Other Aggregate Functions....253 Using Aggregates As Filters....254 Grouping....257 Using the GROUP BY Clause....259 GROUP BY vs. DISTINCT....264 Grouping with Multiple Tables....265 Redundant Groups....268 Preparing Data for Aggregating....270 Using CASE in a CTE....273 Using a Join in the CTE....274 Summarizing Strings....276 Filtering Grouped Results with HAVING....278 Using Results in a CTE....282 Finding Duplicates....283 Using Aggregates on Aggregates....286 Summary....289 Coming Up....290 Chapter 8: Working with Tables....291 How Tables Are Created....292 Creating a Table....293 Column Names....294 Data Type....295 Primary Keys....296 Constraints....298 NOT NULL....299 UNIQUE....300 DEFAULT....300 CHECK....301 Foreign Keys....301 Indexes....303 Adding Rows to a Table....304 Deleting Rows from a Table....306 Adding More Rows....308 Updating Rows....309 Altering the Table....311 DML in Real Life....313 Security....313 Front-End Software....314 Summary....315 Data Types....315 Constraints....315 Foreign Keys....316 Indexes....316 Manipulating Data....316 Chapter 9: Set Operations....317 Unions....317 Selective Unions....321 SELECT Clauses Must Be Compatible....323 Only Column Names from the First SELECT Statement Are Used....325 Sorting Results....326 Intersections....328 Differences....331 Some Tricks with Set Operations....335 Comparing Results....335 Virtual Tables....337 Mixing Aggregates....340 Summary....344 Appendix 1: Differences Between SQL Dialects....346 Writing SQL....346 Semicolons....347 Data Types....347 Dates....347 Case Sensitivity....348 Quote Marks....348 Sorting (ORDER BY)....349 Limiting Results....349 Filtering (WHERE)....349 Case Sensitivity....349 String Comparisons....349 Dates....350 Wildcard Matching....350 Calculations....350 SELECT Without FROM....350 Arithmetic....351 Formatting Functions....351 Date Functions....352 Concatenation....352 String Functions....352 Joining Tables....353 Aggregate Functions....353 Manipulating Data....353 Manipulating Tables....353 Appendix 2: A Crash Course in PDO....354 PDO Objects....354 The PDO Object....355 The PDOStatement Object....355 Working with PDO....356 Establishing a Connection....356 Other DBMSs....357 Prepared Statements and SQL Injection....357 Prepared Statements....358 Repeated Execution....359 Unprepared (Direct) SQL Statements....360 SELECT Statements....360 INSERT, UPDATE, and DELETE Statements....360 Selecting Data....361 Fetching Data....362 The Result Set....362 Fetching a Single Column....364 A Simple Login Script....364 Getting the Last Auto-Incremented Key....365 Error Reporting....366 Summary of PDO....367 Connection....367 Executing Simple Statements....367 INSERT, UPDATE, and DELETE....367 SELECT Statements....368 Executing Prepared Statements....368 Reading Data....368 Reading a Single Row....368 Reading Multiple Rows....369 Reading a Single Column....369 Appendix 3: Additional Notes....370 Cultural Notes....370 Addresses and Phone Numbers....370 Towns....370 States....371 Postcodes....371 Phone Numbers....371 Email Addresses....372 Measurements, Prices, and Currency....372 Dates....372 SQL Data Values....372 Stored Values....373 Variables....374 Literals....375 Calculated Values....375 Some Notes on Dates (and Times)....376 Oracle Date Format....377 Microsoft Age Function....377 Working with SQLite Dates....378 ISO 8601 Dates and Times....378 Date Functions....379 Manipulating Dates and Times....380 Formatting Dates and Times....380 Index....382