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.

world database (by Sylvia Tashev, Stefanie Hatcher)

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

Exercise : World Languages

What languages are spoken in the United States (country_code 'USA')? Show the language and the percentage. Order by percentage from largest to smallest.

+------------+------------+
| language   | percentage |
+------------+------------+
| English    |       86.2 |
| Spanish    |        7.5 |
| French     |        0.7 |
| German     |        0.7 |
| Chinese    |        0.6 |
...
+------------+------------+
12 rows in set (0.11 sec)

Exercise Solution

SELECT language, percentage
FROM languages
WHERE country_code = 'USA'
ORDER BY percentage DESC;

Exercise : World Official Languages

List all of the official languages spoken around the world in 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;

Exercise : English Exclusive Countries

What country/countries use English as their exclusive language? (Hint: Use the percentage.)

+--------------+----------+
| country_code | language |
+--------------+----------+
| BMU          | English  |
+--------------+----------+
1 row in set (0.00 sec)

Exercise Solution

SELECT country_code, language
FROM languages
WHERE percentage = 100
AND language = 'English';

Exercise : Antarctic Countries

List the names of all countries in Antarctica.

+----------------------------------------------+
| name                                         |
+----------------------------------------------+
| Antarctica                                   |
| Bouvet Island                                |
| South Georgia and the South Sandwich Islands |
| Heard Island and McDonald Islands            |
| French Southern territories                  |
+----------------------------------------------+
5 rows in set (0.02 sec)

Exercise Solution

SELECT name 
FROM countries
WHERE continent = 'Antarctica';

Exercise : Regions

List all continents and all regions within them. Make it easy to see regions for each contient.

+---------------+---------------------------+
| continent     | region                    |
+---------------+---------------------------+
| Asia          | Eastern Asia              |
| Asia          | Middle East               |
| Asia          | Southeast Asia            |
| Asia          | Southern and Central Asia |
| Europe        | Baltic Countries          |
...
+---------------+---------------------------+
25 rows in set (0.00 sec)

Exercise Solution

SELECT DISTINCT continent, region
FROM countries
ORDER BY continent, region;

Exercise : Regions

List all continents and all regions within them. Make it easy to see regions for each contient.

+---------------+---------------------------+
| continent     | region                    |
+---------------+---------------------------+
| Asia          | Eastern Asia              |
| Asia          | Middle East               |
| Asia          | Southeast Asia            |
| Asia          | Southern and Central Asia |
| Europe        | Baltic Countries          |
...
+---------------+---------------------------+
25 rows in set (0.00 sec)

Exercise Solution

SELECT DISTINCT continent, region
FROM countries
ORDER BY continent, region;

Exercise : Early Independance

Which countries received their independence before 0 AD? Show both the name and the year. (Hint: BC years are negative values.)

+----------+-------------------+
| name     | independence_year |
+----------+-------------------+
| Ethiopia |             -1000 |
| Japan    |              -660 |
| China    |             -1523 |
+----------+-------------------+
3 rows in set (0.00 sec)

Exercise Solution

SELECT name, independence_year
FROM countries
WHERE independence_year < 0;

Exercise : Same Name

Which countries have the same local name as their official name? Show them in sorted order.

+----------------------------------------------+
| name                                         |
+----------------------------------------------+
| Andorra                                      |
| Angola                                       |
| Anguilla                                     |
| Antigua and Barbuda                          |
| Argentina                                    |
...
+----------------------------------------------+
104 rows in set (0.00 sec)

Exercise Solution

SELECT name
FROM countries
WHERE name = local_name
ORDER BY name;

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 : Monarchs

What countries have a monarchy as their form of government?

+----------------------------------+-------------------------------------+
| name                             | government_form                     |
+----------------------------------+-------------------------------------+
| Antigua and Barbuda              | Constitutional Monarchy             |
| Bahamas                          | Constitutional Monarchy             |
| Barbados                         | Constitutional Monarchy             |
| Belize                           | Constitutional Monarchy             |
| Cambodia                         | Constitutional Monarchy             |
...
+----------------------------------+-------------------------------------+
43 rows in set (0.01 sec)

Exercise Solution

SELECT DISTINCT name, government_form
FROM countries
WHERE government_form LIKE '%monarchy%'
ORDER BY government_form, name;

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';

Exercise : Countries

List all countries in Europe, Oceania, and Antarctica, sorted by continent.

+------------+----------------------------------------------+
| continent  | name                                         |
+------------+----------------------------------------------+
| Europe     | Albania                                      |
| Europe     | Andorra                                      |
| Europe     | Austria                                      |
| Europe     | Belarus                                      |
| Europe     | Belgium                                      |
...
+------------+----------------------------------------------+
79 rows in set (0.03 sec)

Exercise Solution

SELECT continent, name
FROM countries
WHERE continent IN ('Europe', 'Oceania', 'Antarctica')
ORDER BY continent, name;

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;

Exercise : A/B Countries

List the countries whose names start with either A or B.

+--------------------------------+
| name                           |
+--------------------------------+
| Afghanistan                    |
| Albania                        |
| Algeria                        |
| American Samoa                 |
| Andorra                        |
...
+--------------------------------+
35 rows in set (0.00 sec)

Exercise Solution

SELECT name
FROM countries
WHERE name LIKE 'A%' OR name LIKE 'B%';

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 : German Countries

List the full names of all countries where German 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 : 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)

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;

Exercise : Results Table

Take your query from one of the previous problems and put it into a PHP page that displays the results using a table. (See next slides.)

Recall: Querying a Database in PHP with PDO

$name = new PDO("dbprogram:dbname=database;host=server", username, password);
$name->query("SQL query");

# connect to world database on local server
$db = new PDO("mysql:dbname=world;host=localhost", "traveler", "packmybags");
$db->query("SELECT * FROM countries WHERE population > 100000000;");

• PDO database library allows you to connect to many different database programs
• PDO object's query function returns rows that match a query

Recall: Result rows with query

$db = new PDO("dbprogram:dbname=database;host=server", username, password);
$rows = $db->query("SQL query");
foreach ($rows as $row) {
	do something with $row;
}

• query returns all result rows
  • each row is an associative array of [column name -> value]
  • example: $row["population"] gives the value of the population column

HTML tables: <table>, <tr>, <td>

A 2D table of rows and columns of data (block element)

<table>
	<tr><th>Col 1</th><th>Col 2</th></tr>
	<tr><td>r1c1</td><td>r1c2 hello :-)</td></tr>
	<tr><td>r2c1 is very wide!</td><td>r2c2</td></tr>
</table>

• table defines the overall table
• tr is each row, th each header label, and td each cell's data

Styling tables

table { border: 4px dotted blue; text-align: right; }
td    { border: 3px dashed red; width: 30%; padding: 10pt; }

• all standard CSS styles can be applied to a table, row, or cell
• table specific CSS properties:
  • border-collapse, border-spacing, caption-side, empty-cells, table-layout

Exercise : MySQL Console

Use the MySQL console through an SSH program (e.g. PuTTY) to connect to webster and test out one of your queries. (See next slide.)

Issuing SQL commands directly in MySQL

SHOW DATABASES;
USE database;
SHOW TABLES;

• SSH to Webster, then type:

$ mysql -u yourusername -p
Password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql> USE world;
Database changed

mysql> SHOW TABLES;
+-----------+
| cities    | 
| countries | 
| languages | 
+-----------+
3 rows in set (0.00 sec)