SQL – Views and Subqueries
Firstly, the table was created in the slecdis ltd database by opening the given .sql file. The database was created on a Ubuntu 16.04 server because it was of interest to me to experiment a little bit with the command line controls of PostgreSQL and the connectivity with pgAdmin.
In the exercise section, some basic SQL queries were performed as well as views created. The SQL queries included the already known SELECT & FROM statements, as well as the ORDER BY statement which takes the column and direction of the intended order as arguments.
After displaying the capitals ordered by population count, the so called view was created, which holds routinely performed queries and can be performed by only calling the view it- self. This trick was immediately used by adding 300.000 inhabitants to Singapore in order to push it to Rank No. 20, which lead to the visualization of the new table when calling the capitals top 20 view. The addition was performed by adding 300.000 to the population count of Singapore with the SET – WHERE statements.
Using subqueries, aggregate functions can be used within other conditions. The so-called outer query does the selection of the data and sets the first half of the operator statement, while the inner query holds the function that is to be performed. Basically, a separate question needs to be formulated for both selecting the population and then using a subquery for the calculation of the average.
As already briefly mentioned, operators were introduced which fulfill the same tasks and use the same syntax as different programming/scripting languages. The AVG function was also used.
Explain it!
By storing complex queries as views, a lot of time and effort can be saved. Knowing that certain queries need to be repeated frequently, it makes sense to use these predefined queries in future operations. If the result of a view is known, it is also possible to break down larger problems into smaller sub-problems and breaking the task up int smaller steps. This has the potential to decrease the complexity of larger operations and by calling multiple views in one other query, the readability can potentially be increased.
