Compute the difference between a city and the one immediately after it and place

Question

Compute the difference between a city and the one immediately after it and place this into the column POPULATION_DIFF

Explanation / Answer

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

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.