====== Information about MySQL RDBMS ======
===== Connection =====
* You've obtained the credentials via e-mail. If not, contact your practical course lecturer.
* In order to connect (and use the database) interactively,
- Log in via SSH to the MySQL server: ssh login@147.32.84.109
- 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).
===== Use in the BASH script =====
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
===== An example database =====
Download and play with the [[http://labe.felk.cvut.cz/~stepan/33OSD/files/world.sql|world.sql]] database export.
Try the following examples:
- //Simple query//
- Find cities with population greater than 8 mil. people.
- Sort the previous results by population size.
- //Query with join//
- Print a list of countries with population above 60 mil. together with name and population of their capital. Sort by country population (descending).
- Where appropriate, rename the attribute names in the resulting table in the query above.
- //Query using with multiple JOIN//
- List all capitals where the official language is dutch.
- //Query with aggregation -- GROUP BY/HAVING//
- 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.
- List all cities (and countries) where at least 50 % inhabitants speak dutch.
- Find a country with the smallest area
- Solve the same query with a sub-query.
- Find the area of Europe (i.e. sum of areas of all european countries)
- Find continents with the smallest and greatest population (alternatively, list all and order by population).
- List all continents where average life time is greater than 70 yrs.
- What is an average life time in czech speaking countries?
- List all countries that have more than one language being spoken by at least 30 % inhabitants.