University of Washington CSE 154

Section 5: 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.

Valid HTML5 Valid CSS

Query Testers

The major query tester is located here.

If webster is down, you can also access this query tester as a backup here.

world database (by Sylvia Tashev, Stefanie Hatcher)

countries
(Other columns: region, surface_area, life_expectancy, gnp_old, local_name, government_form, capital, code2)
code name continent independence_year population gnp head_of_state ...
AFG Afghanistan Asia 1919 22720000 5976.0 Mohammad Omar ...
NLD Netherlands Europe 1581 15864000 371362.0 Beatrix ...
........................
cities
id name country_code district population
3793New YorkUSANew York8008278
1Los AngelesUSACalifornia3694820
...............
languages
country_codelanguageofficialpercentage
AFGPashtoT52.4
NLDDutchT95.6
............

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';
Could also use:
SELECT name
FROM countries 
WHERE continent <> 'Africa';
[an error occurred while processing this directive]

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;
You can also use:
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';

Exercise : Languages in France

List all of the languages that are spoken in France, as well as the expected number of speakers for that language (population * percentage). Do this as a single query. Do not hard code the country code into your query. (JOIN) Expected results:

+--------------+-----------+--------------+-------------------+
| language     | official  | percentage   | estimated_speakers|
+--------------+-----------+--------------+-------------------+
| Arabic       | F         | 2.5          | 5543525429.6      |
| French       | T         | 93.6         | 148064250.0       |
| Italian      | F         | 0.4          | 71070842.8        |
| Portugese    | F         | 1.2          | 23690280.4        | 
| Spanish      | F         | 0.4          | 23690280.4        |
| Turkish      | F         | 0.4          | 23690280.4        |
+--------------+-----------+--------------+-------------------+

Exercise Solution

SELECT language, official, percentage, 
       percentage * population as estimated_speakers
FROM countries
JOIN languages ON country_code = code
WHERE countries.name = "France"
B

Exercise : Cities in Italy

List all of the cities in Italy, whose population is more than 1% of Italy's population. Organize results by Name alphabetically. Do this as a single query. Do not hard code Italy's country code. (JOIN) Expected results:

+--------------+-----------+--------------+-------------+
| name         | district  | population   | percentage  |
+--------------+-----------+--------------+-------------+
| Genova       | Liguria   | 6361034      | 0.0110      |
| Milano       | Lombardia | 1300977      | 0.0226      |
| Napoli       | Campania  | 1002619      | 0.0174      |
| Palermo      | Sisilia   | 683794       | 0.0119      |
| Roma         | Latium    | 2643581      | 0.0458      |
| Torino       | Piemonte  | 903705       | 0.0157      |
+--------------+-----------+--------------+-------------+

Exercise Solution

SELECT cities.name, district, cities.population, 
       cities.population/countries.population as percentage
FROM countries
JOIN cities ON code = country_code
WHERE countries.name = "Italy"
AND cities.population/countries.population > 0.01
ORDER BY district ASC

Exercise : High Population

List the full names of all countries that contain at least 2 cities of at least 5,000,000 people. Do this as a single query. (JOIN) Expected results:

+----------+
| name     |
+----------+
| Brazil   |
| India    |
| China    |
| Pakistan |
+----------+
4 rows in set (0.03 sec)

Exercise Solution

SELECT DISTINCT c.name
FROM countries c
JOIN cities ci1 ON ci1.country_code = c.code
JOIN cities ci2 ON ci2.country_code = c.code
WHERE ci1.id < ci2.id AND ci1.population >= 5000000 
AND ci2.population >= 5000000;

simpsons database (by Sylvia Tashev, Stefanie Hatcher)

students
idnameemail
123Bartbart@fox.com
456Milhousemilhouse@fox.com
888Lisalisa@fox.com
404Ralphralph@fox.com
teachers
idname
1234Krabappel
5678Hoover
9012Stepp
courses
idnameteacher_id
10001Computer Science 1421234
10002Computer Science 1435678
10003Computer Science 190M9012
10004Informatics 1001234
grades
student_idcourse_idgrade
12310001B-
12310002C
45610001B+
88810002A+
88810003A+
40410004D+
to test queries on this database, use username homer, password d0ughnut

Exercise : Good Grades

List all grades where a student was given a B- or better. Expected results:

+-------+
| grade |
+-------+
| B-    |
| B+    |
| A+    |
| A+    |
| B     |
+-------+
5 rows in set (0.00 sec)

Exercise Solution

SELECT grade 
FROM grades 
WHERE grade <= 'B-';

Exercise : CSE 143 Grades

List all of the grades given in the course Computer Science 143. Do this as a single query and do not hard-code 143's ID number in the query. (JOIN) Expected results:

+-------+
| grade |
+-------+
| C     |
| A+    |
| D-    |
| B     |
+-------+
4 rows in set (0.02 sec)

Exercise Solution

SELECT g.grade
FROM grades g
JOIN courses c ON c.id = g.course_id
WHERE c.name = 'Computer Science 143';

Exercise : CSE 143 Grades and Names

Modify your previous query to list the names and grades of all students that took Computer Science 143 and got a B- or better. Do this as a single query and do not hard-code 143's ID number in the query. (JOIN) Expected results:

+-------+-------+
| name  | grade |
+-------+-------+
| Lisa  | A+    |
| Ralph | B     |
+-------+-------+
2 rows in set (0.02 sec)

Exercise Solution

SELECT DISTINCT s.name, g.grade
FROM students s
JOIN grades g ON g.student_id = s.id
JOIN courses c ON c.id = g.course_id
WHERE c.name = 'Computer Science 143' AND g.grade <= 'B-';

Exercise : All Grades

List all names of all students who were given a B- or better in any class, along with the name of the class(es) and the (B- or better) grade(s) they got. Arrange them by the student's name in ABC order. (JOIN) Expected results:

+----------+-----------------------+-------+
| name     | name                  | grade |
+----------+-----------------------+-------+
| Bart     | Computer Science 142  | B-    |
| Lisa     | Computer Science 190M | A+    |
| Lisa     | Computer Science 143  | A+    |
| Milhouse | Computer Science 142  | B+    |
| Ralph    | Computer Science 143  | B     |
+----------+-----------------------+-------+
5 rows in set (0.00 sec)

Exercise Solution

SELECT s.name, c.name, g.grade
FROM students s
JOIN grades g ON g.student_id = s.id
JOIN courses c ON c.id = g.course_id
WHERE g.grade <= 'B-'
ORDER BY s.name;

Exercise : Popular Classes

List the names of all courses that have been taken by 2 or more students. Do this as a single query and do not hard-code any ID numbers in the query. Don't show duplicates. (JOIN) Expected results:

+----------------------+
| name                 |
+----------------------+
| Computer Science 142 |
| Computer Science 143 |
+----------------------+
2 rows in set (0.01 sec)

Exercise Solution

SELECT DISTINCT c.name
FROM courses c
JOIN grades g1 ON g1.course_id = c.id
JOIN students s1 ON g1.student_id = s1.id
JOIN grades g2 ON g2.course_id = c.id
JOIN students s2 ON g2.student_id = s2.id
WHERE s1.id < s2.id;