CSE 154

Section 7: SQL

Query Testers

The major query tester is located here.

`world` database (by Sylvia Tashev, Stefanie Hatcher)

countries
(Other columns: independence_year, region, surface_area, life_expectancy, gnp_old, local_name, government_form, capital, code2, head_of_state)
code	name	continent	population	gnp	...
AFG	Afghanistan	Asia	22720000	5976.0	...
NLD	Netherlands	Europe	15864000	371362.0	...
...	...	...	...	...	...

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 1: 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)

Solution

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

Exercise 2: 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)

Solution

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

Exercise 3: 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)

Solution

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

Could also use:

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

Exercise 4: Low GNP

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)

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 5: 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)

Solution

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

Exercise 6: English Speaking Countries

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

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

Solution

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

-- without JOIN keyword
SELECT c.name
FROM countrices c, languages l
WHERE l.language = 'English' 
AND l.official = 'T'
AND l.country_code = c.code;

Exercise 7: 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 multi-table query. Do not hard code the country code into your query. 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        |
+--------------+-----------+--------------+-------------------+

Solution

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

Exercise 8: 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      |
+--------------+-----------+--------------+-------------+

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 9: 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)

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 10: 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)

Solution

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

Exercise 11: 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)

Solution

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

Exercise 12: 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)

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 13: 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)

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 14: 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)

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;

CSE 154

Section 7: SQL

Query Testers

world database (by Sylvia Tashev, Stefanie Hatcher)

Exercise 1: World Official Languages

Solution

Exercise 2: Republics

Solution

Exercise 3: Not In Africa

Solution

Exercise 4: Low GNP

Solution

Exercise 5: Dense Populations

Solution

Exercise 6: English Speaking Countries

Solution

Exercise 7: Languages in France

Solution

Exercise 8: Cities in Italy

Solution

Exercise 9: High Population

Solution

simpsons database (by Sylvia Tashev, Stefanie Hatcher)

Exercise 10: Good Grades

Solution

Exercise 11: CSE 143 Grades

Solution

Exercise 12: CSE 143 Grades and Names

Solution

Exercise 13: All Grades

Solution

Exercise 14: Popular Classes

Solution

`world` database (by Sylvia Tashev, Stefanie Hatcher)

`simpsons` database (by Sylvia Tashev, Stefanie Hatcher)