As you have seen, the SQL queries could grow quite large, and at some point their complexity could become overwhelming. Moreover, after spending hours to get your query, complete with JOIN(s), UNION(s), functions and other things afforded by SQL syntax, you might want to save the resulting scripts for future use.
This is where SQL VIEW comes into the picture. To paraphrase Goethe, a VIEW is frozen query.
Johann Wolfgang von Goethe, an early nineteenth-century German writer and philosopher who is considered by many one of the most important thinkers of the Western Civilization, once remarked, “I call architecture frozen music.” He might have been paraphrasing his compatriot Friedrich Wilhelm Joseph von Schelling, who expressed a similar idea 30 years earlier.
Like every object in the database, a view is created as part of a Data Definition Language (DDL) statement (see Chapter 2 for discussions on DDL, DML, and DQL). Unlike them, however, the CREATE VIEW syntax also contains Data Query Language (DQL), the query to extract data. Most of the books introduce VIEW(s) along with TABLE(s) due to the similarity of the CREATE syntax. When on the discovery journey, the logical place to find them is in multitable data extraction as a means to manage all the complexity. Let's take a look.
In its most basic form, converting a query into a view is a snap; just add ...