book

SQL Hacks

by Andrew Cumming, Gordon Russell

November 2006

Intermediate to advanced

409 pages

10h 30m

English

O'Reilly Media, Inc.

Read now

Unlock full access

A Note Regarding Supplemental Files
Credits
About the AuthorsContributorsAcknowledgments
Preface
Why SQL Hacks?How to Use This BookSQL ConventionsHow This Book Is OrganizedConventionsUsing Code ExamplesHow to Contact UsGot a Hack?Safari Enabled
1. SQL Fundamentals
Run SQL from the Command LinePipe into SQLSwitchesMicrosoft SQL ServerOracleGetting into OracleList your tables in OracleImport a file of SQL into OracleMySQLGetting into MySQLList your tables in MySQLImport a file of SQL into MySQLAccessGetting into SQL in AccessImport a file of SQL commandsPostgreSQLGetting into SQL in PostgreSQLList your tables in PostgresImport a file into PostgreSQLDB2Import SQL into DB2Connect to SQL from a ProgramC#Compiling C#Other C# considerationsJavaRunning JavaPerlRunning PerlPHPRunning PHPRubyRunning RubyPerform Conditional INSERTsINSERT ... VALUESINSERT ... SELECTUPDATE the DatabaseMySQL DifferencesSolve a Crossword Puzzle Using SQLSQL Server VariationAccess VariationPostgreSQL VariationFilling a Table with WordsDon’t Perform the Same Calculation Over and OverUse a VIEWHacking the Hack
2. Joins, Unions, and Views
Modify a Schema Without Breaking Existing QueriesCopy the DatabaseAlter the TableCreate a View to Replace a TableFilter Rows and ColumnsPerlJavaRubyC#PHPFilter on Indexed ColumnsConvert Subqueries to JOINsLooking for What’s Not ThereConvert Aggregate Subqueries to JOINsSimplify Complicated UpdatesUsing a CursorUsing a VIEWChoose the Right Join Style for Your RelationshipsA JOIN ChainA JOIN StarGenerate Combinations
3. Text Handling
Search for Keywords Without LIKEMySQLPostgreSQLSQL ServerOracleSearch for a String Across ColumnsSolve AnagramsChoose a Hash FunctionA linear hash functionA quadratic hash functionAn exponential hash functionSort Your EmailImplementation-Specific VariationsSQL Server: extract the domain nameOracle: extract the domain nameExtract the Top-Level DomainMySQL: extract the top-level domainSQL Server: extract the top-level domainOracle: extract the top-level domainPostgreSQL: extract the top-level domainHacking the HackSQL Server: calculated indexOracle: calculated indexPostgreSQL: calculated index
4. Date Handling
Convert Strings to DatesConvert Your DatesParse Dates with OracleParse Dates with MySQLParse Dates with SQL ServerUncover Trends in Your DataModular ArithmeticSQL ServerAccessOracleReport on Any Date CriteriaMonthly TotalsCurrent MonthSQL ServerOracleYear-to-Date TotalsFiscal year to dateGenerate Quarterly ReportsHacking the HackSecond Tuesday of the MonthDay-of-Week FunctionThe FormulaMySQLOracleSQL ServerPostgreSQLHacking the Hack: The Last Thursday of the MonthSee Also
5. Number Crunching
Multiply Across a Result SetKeep a Running TotalHacking the HackSQL ServerMySQLOracleSee AlsoInclude the Rows Your JOIN ForgotIdentify Overlapping RangesHacking the HackAvoid Dividing by ZeroOther Ways to COUNTCounting with a ConditionAccessCalculate the Maximum of Two FieldsMinimum of Two ValuesAlternative FunctionsHacking the HackDisaggregate a COUNTCope with Rounding ErrorsGet Values and Subtotals in One ShotROLLUP and GROUPING SETSSQL Server, MySQL, and DB2Oracle and DB2Hacking the HackCalculate the MedianCreate a Temporary TableFill the Temporary TableMySQLSQL ServerOracleFind the Middle Row or RowsTally Results into a ChartCalculate the Distance Between GPS LocationsHacking the HackReconcile Invoices and RemittancesFind the Exact MatchesInvoice Numbers Do Not MatchFind Possible MatchesFind Transposition ErrorsSQL ServerOracleSingle QueryApply a Progressive TaxWorking Without LEASTCalculate Rank
6. Online Applications
Copy Web Pages into a TableXSLT ProcessingThe Input Documentgross.xslRunning the HackProcessing requiredHacking the HackPresent Data Graphically Using SVGVendor-Specific XML FeaturesSQL ServerOracleMySQLAdd Navigation Features to Web ApplicationsSQL Server and PostgreSQLOracleAccessRunning the HackHacking the HackTunnel into MySQL from Microsoft AccessCreate a Secure TunnelObtain the MySQL ODBC ConnectorStart the Tunnel Using Visual BasicStop the TunnelTest the ConnectionConnecting to Other DatabasesProcess Web Server LogsQueriesCheck for broken linksInvestigate users’ actionsHacking the HackStore Images in a DatabaseUse a BLOBUse a File for Your ImageExploit an SQL Injection VulnerabilityFix the BugExploit the BugAsking Yes/No QuestionsSome informative questions and their injection phraseAsking for StringsGetting the MetadataSummarySee AlsoPrevent an SQL Injection AttackEscaping in PerlEscaping in C#Escaping in PHPEscaping in JavaNonstring DataJavaScript Validation, Cookies, and Hidden VariablesExploits Using Hidden Variables and CookiesRestrict the Rights of the SQL AccountDon’t OverreactSee Also
7. Organizing Data
Keep Track of Infrequently Changing ValuesRecord Price ChangesFind the current priceFind the price at a specified dateList all prices at a specific dateCombine Tables Containing Different DataOracle and PostgreSQLMySQLSQL Server and AccessDisplay Rows As ColumnsUsing a Self-JoinUsing the CASE StatementDisplay Columns As RowsUngroup Data with Repeating ColumnsClean Inconsistent RecordsNormalize the DataDenormalize Your TablesImport Someone Else’s DataPlay MatchmakerGenerate Unique Sequential NumbersMind the GapRaw speedMultiuser ConsiderationsUse System-Generated NumbersMySQL: AUTO_INCREMENT columnSQL Server: IDENTITY columnOracle: SEQUENCEPostgreSQL: SEQUENCEChoosing a Primary KeyHacking the Hack

8. Storing Small Amounts of Data
Store Parameters in the DatabaseRunning the HackDefine Personalized ParametersAdding New UsersPlatform-Specific VariationsCreate a List of Personalized ParametersSet Security Based on RowsMake Use of UsernamesHacking the HackOne-way trapSupervisor modeIssue Queries Without Using a TableSome Useful Static FunctionsMySQLSQL ServerOraclePostgreSQLGenerate Rows Without TablesHacking the HackCombo box from a table in AccessPop-up list from a table in Perl
9. Locking and Performance
Determine Your Isolation LevelAutocommitConcurrency IssuesPhantom readsNonrepeatable readsDirty readsIsolation LevelEnforcing IsolationQuerying the Isolation LevelUse Pessimistic LockingUse Optimistic LockingLock Implicitly Within TransactionsCope with Unexpected RedoShopping BasketsExecute Functions in the DatabaseCombine Your QueriesExtract Lots of RowsUse a Big BufferUse Variable BindingMake a Series of Round TripsExtract a Subset of the ResultsHacking the HackMix File and Database StorageAdding and Removing FilesToo Many FilesHacking the HackCompare and Synchronize TablesHacking the HackMinimize Bandwidth in One-to-Many JoinsRetrieve an Article and Its CommentsThe UNION QueryResultsDoes This Always Work?Compress to Avoid LOBs
10. Reporting
Fill in Missing Values in a Pivot TableInclude Missing ValuesUse a UnionBreak It Down by RangeReduce the Precision of a NumberReduce the Precision of a DateMySQLOracleSQL ServerPostgreSQLHacking the HackIdentify Updates UniquelyCope with Duplicate BatchesCreate Transactions at the BranchUpdate at the Central ServerPlay Six Degrees of Kevin BaconBuild Decision TablesHacking the HackGenerate Sequential or Missing DataCreate and Populate an integers TableGenerate Sequential DataNumbers 0–99Letters A–ZSQL ServerOracleDate rangesOracle and PostgreSQLSQL ServerProvide Missing Data in an OUTER JOINCounts for each letterData for consecutive datesHacking the HackOraclePostgreSQLSQL ServerFind the Top n in Each GroupLast Three ArticlesTop n Rows in Each GroupTiesSee AlsoStore Comma-Delimited Lists in a ColumnAdvantages of Comma-Delimited ListsDisadvantages of Comma-Delimited ListsJoining on a Comma-Delimited ListSee AlsoTraverse a Simple TreeTree VisualizationOracle Recursive ExtensionsSet Up Queuing in the DatabaseGenerate a CalendarVariationsSQL ServerOraclePostgreSQLTest Two Values from a SubqueryChoose Any Three of FiveA JOIN SolutionA GROUP BY Solution
11. Users and Administration
Implement Application-Level AccountsStoring User-Specific InformationHash Your PasswordsOracleSQL ServerIn the Programming LanguagePerlPHPSee AlsoExport and Import Table DefinitionsMySQLPostgreSQLSQL ServerDates in SQL ServerOracleAccessPotential ShowstoppersAuto-numbersSpaces in table names and column namesNonstandard functionsDeploy ApplicationsNamespace ManagementKeep Your CREATE ScriptSQL ServerMySQLOraclePostgreSQLPortabilityDROP Avoiding ConstraintsAuto-Create Database UsersCreate Users and AdministratorsMySQLOraclePostgreSQLSQL ServerIssue Automatic UpdatesOracleMySQLSQL ServerPostgreSQLCreate an Audit TrailSQL ServerMySQLOraclePostgreSQLLocking Down the Underlying TablesProcessing the History Table
12. Wider Access
Sharing Data Across the InternetAllowing Anonymous SQL AccountsAllow an Anonymous AccountA Limited MySQL AccountA Limited SQL Server AccountA Limited PostgreSQL AccountA Limited Oracle AccountFind and Stop Long-Running QueriesOracleSQL ServerMySQLPostgreSQLHacking the HackOracleMySQLSQL ServerPostgreSQLDon’t Run Out of Disk SpaceOracle: Out of Temp SpaceLarge Data TablesOracleMySQLSQL ServerRun SQL from a Web PageMySQLSQL ServerOraclePostgreSQLHacking the HackUsing Other Web InterfacesAuthenticationCGI parametersProcessing resultsSecurity
Index
About the Authors
Colophon
Copyright

Overview

Whether you're running Access, MySQL, SQL Server, Oracle, or PostgreSQL, this book will help you push the limits of traditional SQL to squeeze data effectively from your database. The book offers 100 hacks -- unique tips and tools -- that bring you the knowledge of experts who apply what they know in the real world to help you take full advantage of the expressive power of SQL. You'll find practical techniques to address complex data manipulation problems. Learn how to:

Wrangle data in the most efficient way possible
Aggregate and organize your data for meaningful and accurate reporting
Make the most of subqueries, joins, and unions
Stay on top of the performance of your queries and the server that runs them
Avoid common SQL security pitfalls, including the dreaded SQL injection attack

Let SQL Hacks serve as your toolbox for digging up and manipulating data. If you love to tinker and optimize, SQL is the perfect technology and SQL Hacks is the must-have book for you.

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 0596527993Errata Page

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills