13.4. Assignments DATA.0
Prev Chapter 13. SQL Data Next

13.4. Assignments DATA.0

Submission
You must submit assignments using git
Create Repo
  • Create an empty repo on bitbucket.org, gitlab.com, or github.com,
  • git push your local repo to the above remote repo.
  • For node assignments the line node_modules/must be in your .gitignore file.
Submit
By email to with:
  • The word 'submission <subjectname>' in the subject line
  • The url(s) of your repo(s) in the body

13.4.1. Assignment Data.0

In the world database you will find a table countrylanguage.

  • Write an SQL statement that reads all columns from 'AFG', Afghanistan.
  • Write an SQL statement that reads all columns from 'TJK', Tajikistan
  • Write an SQL statement that reads all columns from 'UZB', Uzbekistan
  • Write an SQL statement that reads all columns from 'UZB', 'TJK', and 'AFG'
  • Now, verbally, not SQL, assuming these were the only countries in the world, how would you calculate how many persons speak each language in the world? Your data are: 
    +-------------+------------+------------+------------+
| countrycode | language   | isofficial | percentage |
+-------------+------------+------------+------------+
| AFG         | Balochi    | F          |        0.9 |
| AFG         | Dari       | T          |       32.1 |
| AFG         | Pashto     | T          |       52.4 |
| AFG         | Turkmenian | F          |        1.9 |
| AFG         | Uzbek      | F          |        8.8 |
| TJK         | Russian    | F          |        9.7 |
| TJK         | Tadzhik    | T          |       62.2 |
| TJK         | Uzbek      | F          |       23.2 |
| UZB         | Karakalpak | F          |        2.0 |
| UZB         | Kazakh     | F          |        3.8 |
| UZB         | Russian    | F          |       10.9 |
| UZB         | Tadzhik    | F          |        4.4 |
| UZB         | Tatar      | F          |        1.8 |
| UZB         | Uzbek      | T          |       72.6 |
+-------------+------------+------------+------------+
  • What actual information is missing? Where would you find it?

You must write the declarations as a text file. Execute the text file in MySQL workbench, or on the CLI. Hand in the textfile.

13.4.2. Assignment Data.1

From the table countrylanguage:

  • Write an SQL statement that reads all countries that have 'Danish' as a language.
  • Write an SQL statement that shows what languages they speak in South Africa. Order them alphabetically.
  • Write an SQL statement that shows what languages they speak in 'UZB', 'TJK', and 'AFG'. Order them in descending order by percentage.

You must write the declarations as a text file. Execute the text file in MySQL workbench, or on the CLI. Hand in the textfile.

13.4.3. Assignment Data.2

From the table country:

  • Write an SQL statement that lists the head of state of all European countries.
  • Write an SQL statement that lists the code, name and population of countries in the region 'Southern and Central Asia'. Sort them by population.

You must write the declarations as a text file. Execute the text file in MySQL workbench, or on the CLI. Hand in the textfile.

13.4.4. Assignment Data.3

Please write a query with country name, country population, continent, name of capital, population of capital from all countries in Oceania, Antarctica, and South America. You must include countries that do not have a capital city.

You must write the declarations as a text file. Execute the text file in MySQL workbench. Hand in the textfile.

13.4.5. Assignment Data.4

Please write a query with country name, population, language, percentage, and percentage recalculated into number of people, for all languages in the countrylanguage table.

You must write the declarations as a text file. Execute the text file in MySQL workbench, or on the CLI. Hand in the textfile.

13.4.6. Assignment Data.5

Using the SQL query from the previous assignment, augment it to group the languages.

You must write the declarations as a text file. Execute the text file in MySQL workbench, or on the CLI. Hand in the textfile.

13.4.7. Assignment Data.6

Please write an SQL query counting how many different languages are spoken in the world.

You must write the declarations as a text file. Execute the text file in MySQL workbench, or on the CLI. Hand in the textfile.

13.4.8. Assignment Data.7

Please write an SQL query showing what cities have a namesake in the database.

You must write the declarations as a text file. Execute the text file in MySQL workbench, or on the CLI. Hand in the textfile.

Prev Up Next
13.3. SQL Data - SELECT Home Chapter 14. Node.js and npm - The First Steps