Datenbanken und Cloud Computing
Formatting of SQL Queries and PL/SQL code
A good formatting of your source code always helps in writing, correcting and understanding a program or a query. In order to ease it for you and your group members to develop your solutions and for us to evaluate them, please repect the following guidelines.
New lines for keywords
Please use new lines for the common SQL-keywords SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. Often it is also useful to begin each AND/OR-part of the WHERE-clause in a new line.
SELECT name, country, province FROM City WHERE (country='de' and population>10000) OR (country='ch' and population>5000);
Use the standard join syntax
Using the standard join syntax instead of just a comma (cross product) indicates the intention of the query better. Join conditions and filter conditions are clearly separated (they are indeed equivalent for inner joins but not for outer joins). There is also an explicit natural join. However, it should be replaced by JOIN ... USING with a fixed list of columns.
SELECT * FROM Country JOIN Economy ON Code=Country WHERE industry>50;
SELECT City.name, country, City.population FROM City JOIN Province USING (country) WHERE Province.name='Baden-Wuerttemberg';
Conditions on aggregations: HAVING
To put conditions on the result of aggregations, use the HAVING-clause instead of a subquery. Of course, both ways are equivalent concerning the result—but not in the readability.
SELECT Country.name, SUM(City.population)-Country.population diff FROM Country JOIN City ON Code=Country GROUP BY (Country.name, Country.population) HAVING Country.population<SUM(City.population);
SELECT name, sum_city_pop-population diff FROM ( SELECT Country.name, Country.population, SUM(City.population) sum_city_pop FROM Country JOIN City ON Code=Country GROUP BY (Country.name, Country.population) ) WHERE population<sum_city_pop;
Subqueries
Subqueries should only be used when necessary. Subqueries in the FROM-clause can often be rewritten into a normal join. When a subquery has to be used serveral times it is shorter to define it once as temporary view using a With-clause.
These guidelines are nor strict nor exhaustive but should be viewed as rules of thumb. The appropiate formatting depends also on the current problem to solve.