Chapter 6. Advanced Query Writing

In this chapter, we will discuss some SQL features supported by PostgreSQL, that were not covered in the previous chapters. Some advanced techniques will also be described.

The same sample database as was used in the previous chapters is used in the code examples. It is recommended to recreate the sample database in order to get the same results as shown in the examples.

In this chapter, we are going to cover the following topics:

• Common table expression

• Window functions

• Advanced SQL

• Transaction isolation and Multiversion Concurrency Control

The code examples in this chapter are based on the car portal database described in the previous chapters. The scripts for creating the database and filling it with data (schema.sql and data.sql) can be found in the code bundle in the Chapter 6 folder.

All the code examples of this chapter can be found in the file examples.sql.