CSE 190 M Section 5

This section focuses on composing SQL queries, creating PHP programs that make MySQL queries, and outputting tabular data.

1. `world` Database (long)

Write an SQL query to answer each of the following questions. Recall the world database:

countries
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
...	...	...	...

What languages are spoken in the United States (country_code 'USA')? Show the language and the percentage. Order by percentage from largest to smallest.
```
SELECT language, percentage
FROM languages
WHERE country_code = 'USA'
ORDER BY percentage DESC;
```
List all of the official languages spoken around the world in alphabetical order.
```
SELECT DISTINCT language
FROM languages
WHERE official='T'
ORDER BY language;
```

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

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

List the names of all countries in Antarctica.

SELECT name 
FROM countries
WHERE continent = 'Antarctica';

What countries have a life expectancy of 78 years or more? List the county names and life expectancies, sorted by greatest to least life expectancy.
```
SELECT name, life_expectancy
FROM countries
WHERE life_expectancy >= 78
ORDER BY life_expectancy DESC;
```
List all continents and all regions within them. Make it easy to see regions for each contient.
```
SELECT DISTINCT continent, region
FROM countries
ORDER BY continent, region;
```
Which countries received their independence before 0 AD? Show both the name and the year. (Hint: BC years are negative values.)
```
SELECT name, independence_year
FROM countries
WHERE independence_year < 0;
```
Which countries have the same local name as their official name? Show them in sorted order.
```
SELECT name
FROM countries
WHERE name = local_name
ORDER BY name;
```
What countries have the word Republic as part of their name?
```
SELECT name
FROM countries
WHERE name LIKE '%Republic%';
```

What countries have a monarchy as their form of government?

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

List all countries that are not in Africa.

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

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

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

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

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

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

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

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

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

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          |
+---------------+
| Belgium       |
| Austria       |
| Liechtenstein |
| Luxembourg    |
| Germany       |
...
+---------------+
6 rows in set (0.01 sec)

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

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)

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;

problem by Sylvia Tashev and Stefanie Hatcher

Solutions

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

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

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

SELECT name 
FROM countries
WHERE continent = 'Antarctica';

SELECT name, life_expectancy
FROM countries
WHERE life_expectancy >= 78
ORDER BY life_expectancy DESC;

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

SELECT name, independence_year
FROM countries
WHERE independence_year < 0;

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

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

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

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

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

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

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

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

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

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;

2. `simpsons` Database (long)

Write an SQL query to answer each of the following questions. Recall the simpsons database:

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+

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)

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

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)

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

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)

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

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)

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;

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)

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;

problem by Sylvia Tashev and Stefanie Hatcher

Solutions

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

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

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

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;

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;

University of Washington, CSE 190 M, Spring 2012

Section 5

Databases/SQL

1. `world` Database (long)

Solutions

2. `simpsons` Database (long)

Solutions

University of Washington, CSE 190 M, Spring 2012

Section 5

Databases/SQL

1. world Database (long)

Solutions

2. simpsons Database (long)

Solutions

1. `world` Database (long)

2. `simpsons` Database (long)