SQL Hacks

Chapter 1. SQL Fundamentals In this chapter, you will see some familiar SQL commands with some surprising variations. SQL includes many subtleties that the discerning programmer can exploit. With better SQL, you can do more pr...

Chapter 2. Joins, Unions, and Views You can use a join to associate the rows of one table with the rows of another. Often you do this to follow a foreign key reference. For example, consider an employ...

Chapter 3. Text Handling SQL has extensive text-handling capabilities built in. You can extract parts of a string using the standard functions. The basic operators, such as LIKE and || or CONCAT (concatenation), are all that y...

Chapter 4. Date Handling Hacks 1923 SQL is capable of handling just about any date calculation. The hacks in this chapter show how to get dates into your database, and how to get weekly, monthly, and quarterly rep...

Chapter 5. Number Crunching A little arithmetic can go a long way in SQL. The language includes the math functions that you would expect to find in any computer language, plus a few that are peculiar to databases. It...

Chapter 6. Online Applications An SQL database provides a powerful solution to many problems that arise in web-based applications. In addition to storing text and image data, you can use SQL to assist in building user interfac...

Chapter 7. Organizing Data When representing complex data you have lots of choices to make. For any given problem there will be a variety of different schemas that you can use. The choices you make will impact three factors: p...

Chapter 8. Storing Small Amounts of Data If you are accustomed to a programming language such as Perl, Java, or Visual Basic, you will have grown used to having variables to store temporary values, global constants, and parame...

Chapter 9. Locking and Performance In applications requiring high query throughput, high concurrency rates, and/or large result sets, you need to ensure good database design. However, systems with good database desig...

Chapter 10. Reporting You might run your quarterly reports only a few times a year, so the speed of your SQL queries may not be your prime concern. Instead, it might be more important that the parameters of the queries be easy...

Chapter 11. Users and Administration You may have one set of user accounts in your application, another set in your database system, and yet another set in your operating system. Sometimes it might be convenient to make some o...

Chapter 12. Wider Access quot;Information wants to be free.quot; So said Stewart Brand at the first Hackers Conference in 1984. If you take a few precautions, you can share your data with the world by giving SQL access to any...