University of Washington CSE 154

Section 6: SQL Queries

Except where otherwise noted, the contents of this document are Copyright © Marty Stepp, Jessica Miller, and Victoria Kirst. All rights reserved. Any redistribution, reproduction, transmission, or storage of part or all of the contents in any form is prohibited without the author's expressed written permission.

Query Testers

The major query tester is located here.

world database (by Sylvia Tashev, Stefanie Hatcher)

• to test queries on this database, use username traveler, password packmybags

Exercise : World Official Languages

List all of the official languages spoken around the world in reverse alphabetical order.

+------------------+
| language         |
+------------------+
| Zulu             |
| Xhosa            |
| Wolof            |
| Vietnamese       |
| Uzbek            |
...
+------------------+
102 rows in set (0.02 sec)

Exercise Solution

SELECT DISTINCT language
FROM languages
WHERE official='T'
ORDER BY language DESC;

Exercise : Republics

What countries have the word Republic as part of their name?

+---------------------------------------+
| name                                  |
+---------------------------------------+
| Dominican Republic                    |
| Central African Republic              |
| Congo, The Democratic Republic of the |
| Czech Republic                        |
+---------------------------------------+
4 rows in set (0.00 sec)

Exercise Solution

SELECT name
FROM countries
WHERE name LIKE '%Republic%';

Exercise : Not In Africa

List all countries that are not in Africa.

+----------------------------------------------+
| name                                         |
+----------------------------------------------+
| Afghanistan                                  |
| Netherlands                                  |
| Netherlands Antilles                         |
| Albania                                      |
| American Samoa                               |
...
+----------------------------------------------+
181 rows in set (0.00 sec)

Exercise Solution

SELECT name
FROM countries
WHERE continent != 'Africa';

SELECT name
FROM countries 
WHERE continent <> 'Africa';

Exercise : Low GDP

Which countries have a GNP between $10,000 AND $50,000?

+------------------------+----------+
| name                   | gnp      |
+------------------------+----------+
| Algeria                | 49982.00 |
| United Arab Emirates   | 37966.00 |
| Bangladesh             | 32852.00 |
| Brunei                 | 11705.00 |
| Bulgaria               | 12178.00 |
...
+------------------------+----------+
36 rows in set (0.00 sec)

Exercise Solution

SELECT name, gnp
FROM countries
WHERE gnp BETWEEN 10000 AND 50000;

SELECT name, gnp
FROM countries
WHERE gnp < 50000
AND gnp > 10000;

Exercise : Dense Populations

Which countries have the densest population (population divided by surface area)? Show the densest ones at the top.

+--------------+------------+--------------+--------------------+
| name         | population | surface_area | population_density |
+--------------+------------+--------------+--------------------+
| Macao        |     473000 |        18.00 |       26277.777778 |
| Monaco       |      34000 |         1.50 |       22666.666667 |
| Hong Kong    |    6782000 |      1075.00 |        6308.837209 |
| Singapore    |    3567000 |       618.00 |        5771.844660 |
| Gibraltar    |      25000 |         6.00 |        4166.666667 |
...
+--------------+------------+--------------+--------------------+
239 rows in set (0.00 sec)

Exercise Solution

SELECT name, population, surface_area,
       population / surface_area as population_density
FROM countries
ORDER BY population_density DESC;

Exercise : English Speaking Countries

List the full names of all countries where English is spoken as an official language. Do this as a single query. (JOIN) Expected results:

+--------------------------------------+
| name                                 |
+--------------------------------------+
| American Samoa                       |
| Anguilla                             |
| Antigua and Barbuda                  |
| Australia                            |
| Barbados                             |
...
+--------------------------------------+
44 rows in set (0.02 sec)

Exercise Solution

SELECT c.name
FROM countries c
JOIN languages l on l.country_code = c.code
WHERE l.language = 'English' AND l.official = 'T';