Assignment-2

SQL

Databases Bachelor IK

Lecturer: Dr. Hamideh Afsarmanesh
Course Assistants: Anton Bossenbroek and Simon S. Msanjila / Ekaterina Ermilova

Date of Hand-out: May 2, 2006

Deadline for Assignment-2: May 22, 2006

Note 1: Please, notice that for this assignment you must submit your answers for the assignment (and the bonus queries)in both following ways:

  • show the execution of your queries during the practical laboratory session to the course assistants who will also give you a partial grade for it, and
  • submit your answers to the blackboard site, while giving the access rights to Anton Bossenbroek and Ekaterina Ermilova.

Note 2: Please notice thatthere are also3 bonus queriesincluded. The 3 bonus queries areequivalent two “special pop quizzes” in class.

The ‘Mondial’ databaseused for this assignment has been compiled from the geographical Web data sources by Institute for Informatics of Georg-August-Universität, Göttingen.

The copy of this database is located on the server websec.science.uva.nl, and is called ‘mondial’.

Please, learn about the database yourselves using the schemas provided at , and using ‘SHOW TABLES’ and ‘DESCRIBE’ commands in the database on the ‘websec’ server.

The SQL assignment constitutes the following 3 tasks:

Task N1:Write the following queries in SQL and run themon the‘mondial’ database.

1.1.Find the names of the ethnic groups which live in the countries, where the Karakum desert is located. Sort the result by the names of the ethnic groups.

1.2.Display the names of all geographical objects such as rivers, lakes, and mountains thatconsist of two words or more. Sort them alphabetically.

1.3.Count the number of states in the USA.

1.4.Find the names of all the languages which are spoken in at leasttwo countries.

1.5.List the name of all continents and the total number of their religions, while the list must be sorted descendingly on the total number of their religions.

1.6.For each city where a river flows into a sea display the name of the city, the river and the sea.

1.7.Display the names of all countries through whichthe longest riverpasses.

Task N2:Insert a new row into the ‘tourist’ table, using your student ID, first name, address and phonenumber.

Task N3:Update the row of the ‘tourist’ table that you inserted in Task N2, by replacing your first name with your last name.

Bonus queries:

  1. For each state in the USA display the name of the state, the capital, the number of cities, registered in the DB, and its population, whilesorting this result descendingly on the number of cities.
  1. Select all the rivers, which are located in the same countries at where the river called Volga is located. Suggest two answers: first, using a nested query, and second, using ‘AS’.
  1. For each continent find the name of a country with the longest border.