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.