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)
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 |
3793 | New York | USA | New York | 8008278 |
1 | Los Angeles | USA | California | 3694820 |
... | ... | ... | ... | ... |
|
languages
country_code | language | official | percentage |
AFG | Pashto | T | 52.4 |
NLD | Dutch | T | 95.6 |
... | ... | ... | ... |
|
- 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';
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|
+--------------+-----------+--------------+-------------------+
| French | T | 93.6 | 5543525429.6 |
| Arabic | F | 2.5 | 148064250.0 |
| Portuguese | F | 1.2 | 71070842.8 |
| Italian | F | 0.4 | 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
id | name | email |
123 | Bart | bart@fox.com |
456 | Milhouse | milhouse@fox.com |
888 | Lisa | lisa@fox.com |
404 | Ralph | ralph@fox.com |
teachers
id | name |
1234 | Krabappel |
5678 | Hoover |
9012 | Stepp |
courses
id | name | teacher_id |
10001 | Computer Science 142 | 1234 |
10002 | Computer Science 143 | 5678 |
10003 | Computer Science 190M | 9012 |
10004 | Informatics 100 | 1234 |
grades
student_id | course_id | grade |
123 | 10001 | B- |
123 | 10002 | C |
456 | 10001 | B+ |
888 | 10002 | A+ |
888 | 10003 | A+ |
404 | 10004 | D+ |
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 |
| Computer Science 154 |
+----------------------+
3 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;