Review of the book “SQL. A collection of recipes” by Anthony Molinaro

Hi all. Purposeful people who want to become Java developers have gathered here. And this is logical: JavaRush was created for this. To become a full-fledged developer, you need to know a certain list of topics. I described a complete checklist of knowledge required for a Java developer in this article . So: one of the required topics on this list is knowledge of the SQL language and familiarity with at least one relational database. The good news is that you only need one book to learn SQL at a level sufficient for a Java developer! There are a lot of options for books suitable for an initial dive into SQL, but today I would like to focus on “SQL. Collection of recipes” by Anthony Molinaro. The peculiarity of the book is that in addition to studying SQL at a basic level, you will become familiar with several databases at once and see how the queries for them differ and what are the features of a particular database. This book covers queries for the following databases:

• DB2;
• Oracle Database;
• PostgreSQL;
• SQL Server;
• MySQL.

Who is the book for?

• beginner developer

First of all, this book will be useful to a novice developer, for example, in Java. You will have to deal at a basic level with various relational databases, so this book will help you gain the necessary knowledge of SQL.

• experienced developer

You are probably already well acquainted with building SQL queries, but still this book will pleasantly surprise you and complement your knowledge. Or, for example, you move to a project with a relational database that is unfamiliar to you. Then, with the help of this book, you will be able to draw analogies from queries from a familiar database to a new one.

• beginner SQL developer

You may be just thinking about becoming a database developer. Then this book is perfect for you! You will be able to study the basics of SQL, discussed for various databases, and choose “your” database, which you will study in more detail in the future.

What is this book about

This book covers, chapter by chapter, basic SQL topics, as well as various SQL problems and their solutions. Therefore, it can be used as a reference: a problem has arisen, you are looking for suitable ways to solve it, and then you are creating your solution to suit the current conditions. The pages of this book contain over 150 recipes. Yes, exactly recipes for building SQL queries: that’s why the book has this name. What to do after reading this book?

1. Don’t be afraid to experiment, because only by trial and error can you gain such valuable experience, so practice, practice and practice again!
2. Test it. What I mean is that while going through this book, it is better to try out the examples of queries given in parallel, which, thanks to this, will be well remembered in your memory.
3. Repetition is the mother of learning. Therefore, it is advisable to introduce a note in which you can write down key points and repeat them periodically, so that writing queries in the future does not cause you any problems.
4. You need to understand that new does not always mean better. Just because you're not using some of the latest SQL features (such as window functions) doesn't mean your code is ineffective. After all, there are many examples where traditional SQL solutions are no worse, and sometimes even better, than a new solution.
5. Don't be afraid to experiment. Get creative with your work! After all, all solutions have not yet been invented: perhaps in your situation a creative approach will be the best.

Now let's go through the contents of the book a little:

1. Extracting records

This chapter examines the first, simplest queries. In the examples you will see:

• how the WHERE clause can be used to select rows into a common result set;
• how to assign aliases to result columns;
• how you can use a subquery to access columns by aliases;
• how to limit the number returned in the result rows;
• how to return random strings and detect NULL values.

As a rule, the examples are quite simple, but at the same time there are some that can surprise even seasoned developers.

2. Sorting query results

This chapter discusses sorting query results. Actually, the ORDER BY operator is used for this . You'll see examples of varying complexity that range from simply ordering a single column, to sorting by substrings, to sorting using various conditionals.

3. Working with multiple tables

This part of the book introduces ways to combine data from multiple tables. I won't be lying if I say that table joins are one of the fundamental pillars of SQL, and to be successful with SQL, you need to acquire this knowledge and be able to use it. Here you'll see examples of both inner and outer joins, Cartesian products, basic set operations (subtraction, union, intersection), and how joins can affect aggregate functions.

4. Insert, update, delete

In this section, you will learn SQL queries to insert, update, and delete data. More interesting examples are also discussed - operations such as inserting rows from one table into another or using related subqueries during updates. You will understand the consequences of having NULL values, learn about multi-table insertion, the MERGE command, etc.

5. Requests for metadata

This chapter talks about building queries to access the metadata of the database you are using: it is useful to know indexes, constraints, and schema tables. The simple queries proposed here allow you to obtain information about the schema itself. In addition, this chapter shows examples of "dynamic SQL", that is, SQL generated by SQL.

6. Working with strings

This chapter introduces techniques for working with strings. SQL has never been known for its string parsing capabilities, but a little creativity combined with the wide range of functions provided by various databases can achieve considerable success in this matter. Actually, this chapter is where the fun begins. Some examples will surprise you. For example:

• counting instances of a character in a string;
• convert delimited lists and strings to table rows;
• creating delimited lists from table rows;
• separating the numeric and character data of a string consisting of alphanumeric characters.

7. Working with numbers

This part of the book will look at ways to solve standard problems with numbers. Here you can find common examples and learn how window functions can easily handle calculation and aggregation tasks. You will learn:

• calculate the current amount;
• find the mean, median and mode;
• calculate percentile;
• handle NULL values ​​when performing aggregation.

8-9. Date arithmetic

In these two chapters you will learn how to work with dates, because when solving everyday problems it is important to be able to perform the simplest and most common operations with them. Various examples include finding the number of working days between two dates, calculating the difference between two dates in different time units, returning all days of the year, finding leap years, finding the first and last day of a month, creating a calendar, etc. After these chapters, it will be very difficult to scare you with any task tied to dates (but you can scare you).

10. Working with data ranges*

This chapter introduces methods for searching for values ​​already in date ranges. You'll learn how to automatically generate string sequences, insert missing numeric range values, determine the start and end of a value range, and identify sequences of values.

11. Advanced search*

Here you will find features that are vital for everyday work with databases, but sometimes cause difficulties. Examples in this chapter include searching for values ​​using knight moves, splitting a result set into pages, skipping table rows, searching for reciprocal values, and so on.

12. Reporting and data warehouse management**

Often we may need special queries to generate reports. This chapter will help us do just that: here you will look at examples of converting rows to columns and vice versa (cross-reference reports), creating groups of data, creating histograms, calculating simple and full subsums, aggregating a sliding window of rows, and grouping rows by a given time interval.

13. Hierarchical queries**

This chapter will tell us about the possibilities of working with hierarchical data. Regardless of the data model, one day there is a need to format it as a hierarchy tree or parent->child relationship. Therefore, in this chapter you will analyze the problems and solutions for this. Creating tree-structured result sets is difficult using traditional SQL, so this tutorial shows you how to use some extremely useful special functions. In the examples, you will look in detail at the parent->child relationship, traversing the hierarchy from the root node to the leaf nodes, and accumulating the hierarchy.

14. Stuff

And finally, you will see all the remaining features that did not fit into any of the previous chapters, but at the same time they are no less interesting and useful. This chapter also differs from the rest in that it offers solutions only for specific DBMSs. This is the only chapter in the book where only one database is discussed per recipe. This was done to show the capabilities and features of specific DBMSs (so that you can better feel the difference between them). Pros of the book:

1. Lots of examples.
2. Great for “primary” diving.
3. Examples for various DBMSs are considered.
4. There is a good translation.

Disadvantage: More complex, specific, rare tasks/problems are not considered.

Book analogues

1. “Learning SQL” - Alan Bewley: also a book for an initial dive into SQL with consideration of various DBMSs, but it places special emphasis on MySQL. The downside of the book is the insufficient “chewing” of new information.
2. "SQL: A Language Learning Guide" - Chris Fiaily: A book that also covers the basics of various databases. Great for beginners, but if you're already familiar with SQL and need to learn more of the finer points, you might want to look elsewhere.
3. “Head First - Learning SQL” - Lynn Bailey: a book with a very low barrier to entry, it will convey many fundamental points in an accessible and detailed manner (perhaps sometimes even too simply). In its examples, this book considers not several databases, like the analogues above, but one - MySQL.

Bottom line

If you are determined to become a Java developer, you cannot do without learning SQL. But fortunately, we don’t need to know it at the same level as the database developers, and the amount of knowledge required, in principle, can be covered with just one book on SQL. The book reviewed today is “SQL. A collection of recipes”, or one of the presented analogues, may suit you perfectly. Well, the choice of what exactly to read (or not to read) is yours)) PS To get your hands on the practice of writing SQL to solve various problems, you can use this site . PSS And here you can get acquainted with common questions about databases, preferably after reading the book.