SQL Hacks

Anyone who works with a relational database, whether it's Access, MySQL, SQL Server, Oracle or PostgreSQL, could use a few tricks for increasing productivity. This new book in our critically acclaimed "Hacks" series provides 100 unique tips and tools that push the limits of traditional SQL, from fun stuff like solving crosswords with SQL to complex data manipulation. Co-written by the author of "A Gentle Introduction to SQL" (sqlzoo.net), one of the top resources for SQL information online, SQL Hacks provides a variety of hacks for querying, reporting, and wrangling data. It also provides hacks on web database development, database administration, and security, including ways to protect you from the dreaded SQL injection attack. Think of it as a set of powerful spurs for data wranglers.

Andrew Cumming is the Zoo Keeper at http://sqlzoo.net . He keeps a collection of tame SQL engines and presents these to the public. He cares for these beasts, protecting them from the abuse inflicted by SQL learners, and protecting SQL learners from the injuries that the wild engines can inflict. But mostly he shovels manure out the cages and hoses them down every now and then. Andrew also teaches at Napier University in Edinburgh, Scotland.

Dr. Gordon Russell is a Computing lecturer in Napier University, Scotland. He teaches databases, Linux, and networking. He also builds educational websites, concentrating on technology-driven online learning environments. These include db.grussell.org, whose claim-to-fame is its automatic checking of SQL assessments, and http://linuxzoo.net, which offers online users root free access to Linux servers. But without database technology to support these sites, even he would be lost.

Credits

xi

Preface

xiii

Chapter 1. SQL Fundamentals

1

(26)

1. Run SQL from the Command Line

1

(8)

2. Connect to SQL from a Program

9

(6)

3. Perform Conditional INSERTs

15

(3)

4. UPDATE the Database

18

(3)

5. Solve a Crossword Puzzle Using SQL

21

(3)

6. Don't Perform the Same Calculation Over and Over

24

(3)

Chapter 2. Joins, Unions, and Views

27

(20)

7. Modify a Schema Without Breaking Existing Queries

27

(2)

8. Filter Rows and Columns

29

(3)

9. Filter on Indexed Columns

32

(2)

10. Convert Subqueries to JOINs

34

(3)

11. Convert Aggregate Subqueries to JOINs

37

(1)

12. Simplify Complicated Updates

38

(2)

13. Choose the Right Join Style for Your Relationships

40

(3)

14. Generate Combinations

43

(4)

Chapter 3. Text Handling

47

(15)

15. Search for Keywords Without LIKE

47

(5)

16. Search for a String Across Columns

52

(1)

17. Solve Anagrams

53

(5)

18. Sort Your Email

58

(4)

Chapter 4. Date Handling

62

(21)

19. Convert Strings to Dates

62

(3)

20. Uncover Trends in Your Data

65

(5)

21. Report on Any Date Criteria

70

(5)

22. Generate Quarterly Reports

75

(3)

23. Second Tuesday of the Month

78

(5)

Chapter 5. Number Crunching

83

(48)

24. Multiply Across a Result Set

83

(2)

25. Keep a Running Total

85

(4)

26. Include the Rows Your JOIN Forgot

89

(3)

27. Identify Overlapping Ranges

92

(3)

28. Avoid Dividing by Zero

95

(1)

29. Other Ways to COUNT

96

(3)

30. Calculate the Maximum of Two Fields

99

(1)

31. Disaggregate a COUNT

100

(2)

32. Cope with Rounding Errors

102

(2)

33. Get Values and Subtotals in One Shot

104

(4)

34. Calculate the Median

108

(3)

35. Tally Results into a Chart

111

(3)

36. Calculate the Distance Between GPS Locations

114

(4)

37. Reconcile Invoices and Remittances

118

(3)

38. Find Transposition Errors

121

(5)

39. Apply a Progressive Tax

126

(3)

40. Calculate Rank

129

(2)

Chapter 6. Online Applications

131

(48)

41. Copy Web Pages into a Table

131

(8)

42. Present Data Graphically Using SVG

139

(8)

43. Add Navigation Features to Web Applications

147

(5)

44. Tunnel into MySQL from Microsoft Access

152

(5)

45. Process Web Server Logs

157

(7)

46. Store Images in a Database

164

(4)

47. Exploit an SQL Injection Vulnerability

168

(6)

48. Prevent an SQL Injection Attack

174

(5)

Chapter 7. Organizing Data

179

(24)

49. Keep Track of Infrequently Changing Values

179

(3)

50. Combine Tables Containing Different Data

182

(2)

51. Display Rows As Columns

184

(3)

52. Display Columns As Rows

187

(2)

53. Clean Inconsistent Records

189

(3)

54. Denormalize Your Tables

192

(2)

55. Import Someone Else's Data

194

(2)

56. Play Matchmaker

196

(2)

57. Generate Unique Sequential Numbers

198

(5)

Chapter 8. Storing Small Amounts of Data

203

(20)

58. Store Parameters in the Database

203

(7)

59. Define Personalized Parameters

210

(3)

60. Create a List of Personalized Parameters

213

(1)

61. Set Security Based on Rows

214

(3)

62. Issue Queries Without Using a Table

217

(2)

63. Generate Rows Without Tables

219

(4)

Chapter 9. Locking and Performance

223

(42)

64. Determine Your Isolation Level

223

(6)

65. Use Pessimistic Locking

229

(2)

66. Use Optimistic Locking

231

(3)

67. Lock Implicitly Within Transactions

234

(1)

68. Cope with Unexpected Redo

235

(5)

69. Execute Functions in the Database

240

(2)

70. Combine Your Queries

242

(1)

71. Extract Lots of Rows

243

(4)

72. Extract a Subset of the Results

247

(2)

73. Mix File and Database Storage

249

(4)

74. Compare and Synchronize Tables

253

(5)

75. Minimize Bandwidth in One-to-Many Joins

258

(3)

76. Compress to Avoid LOBs

261

(4)

Chapter 10. Reporting

265

(50)

77. Fill in Missing Values in a Pivot Table

265

(6)

78. Break It Down by Range

271

(4)

79. Identify Updates Uniquely

275

(4)

80. Play Six Degrees of Kevin Bacon

279

(3)

81. Build Decision Tables

282

(5)

82. Generate Sequential or Missing Data

287

(7)

83. Find the Top n in Each Group

294

(2)

84. Store Comma-Delimited Lists in a Column

296

(3)

85. Traverse a Simple Tree

299

(4)

86. Set Up Queuing in the Database

303

(2)

87. Generate a Calendar

305

(3)

88. Test Two Values from a Subquery

308

(2)

89. Choose Any Three of Five

310

(5)

Chapter 11. Users and Administration

315

(34)

90. Implement Application-Level Accounts

315

(8)

91. Export and Import Table Definitions

323

(9)

92. Deploy Applications

332

(5)

93. Auto-Create Database Users

337

(2)

94. Create Users and Administrators

339

(2)

95. Issue Automatic Updates

341

(3)

96. Create an Audit Trail

344

(5)

Chapter 12. Wider Access

349

(18)

97. Allow an Anonymous Account

350

(2)

98. Find and Stop Long-Running Queries

352

(4)

99. Don't Run Out of Disk Space

356

(3)

100. Run SQL from a Web Page

359

(8)

Index

367

What is included with this book?

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.

FREE SHIPPING

Rent More, Save More! Use code: BBRENTAL

5% off 1 book, 7% off 2 books, 10% off 3+ books

Summary

Author Biography

Table of Contents

Supplemental Materials