Information about MySQL RDBMS

• You've obtained the credentials via e-mail. If not, contact your practical course lecturer.
• In order to connect (and use the database) interactively,
  1. Log in via SSH to the MySQL server:

     ssh login@147.32.84.109

  2. At the prompt use the mysql command:

     mysql --user=login --password=SECRET --database=login

• Warning: The access to the MySQL server is allowed only locally, therefore you cannot connect to the database remotely (by specifying the –host parameter).

You can also use the mysql command in batch mode (provided the student table exists). This approach was needed for D-tasks before year 2015.

MYSQL_LOGIN_PARAMS=" --user=login --password=SECRET --database=login "
MYSQL_PARAMS=" --skip-column-names "
MYSQL_SQL_QUERY='SELECT * FROM student;'


mysql ${MYSQL_LOGIN_PARAMS} ${MYSQL_PARAMS} --execute="${MYSQL_SQL_QUERY}" \
  | while read i ; do 
      echo $i ; 
    done

Download and play with the world.sql database export.

Try the following examples:

1. Simple query
   1. Find cities with population greater than 8 mil. people.
   2. Sort the previous results by population size.
2. Query with join
   1. Print a list of countries with population above 60 mil. together with name and population of their capital. Sort by country population (descending).
   2. Where appropriate, rename the attribute names in the resulting table in the query above.
3. Query using with multiple JOIN
   1. List all capitals where the official language is dutch.
4. Query with aggregation – GROUP BY/HAVING
   1. List all languages that are official languages in more than one country. List them together with number of countries an sort them by the count.
5. List all cities (and countries) where at least 50 % inhabitants speak dutch.
6. Find a country with the smallest area
   1. Solve the same query with a sub-query.
7. Find the area of Europe (i.e. sum of areas of all european countries)
8. Find continents with the smallest and greatest population (alternatively, list all and order by population).
9. List all continents where average life time is greater than 70 yrs.
   1. What is an average life time in czech speaking countries?
10. List all countries that have more than one language being spoken by at least 30 % inhabitants.