Write an SQL query to answer each of the following questions. Recall the world database:
-
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 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 whoose names start with either A or B.
SELECT name
FROM countries
WHERE name LIKE 'A%' OR name LIKE 'B%';
-
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 |
| Switzerland |
+---------------+
6 rows in set (0.00 sec)
SELECT c.name
FROM countries c
JOIN languages l on l.country_code = c.code
WHERE l.language = 'English' 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.00 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;
Write an SQL query to answer each of the following questions. Recall the simpsons database:
-
List all grades where a student was given a B- or better. Expected results:
+-------+
| grade |
+-------+
| B- |
| B+ |
| A+ |
| A+ |
| B |
| A+ |
+-------+
6 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 |
| A+ |
+-------+
5 rows in set (0.00 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 |
| Kellylololol | A+ |
+--------------+-------+
3 rows in set (0.00 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- |
| Kellylololol | Computer Science 143 | A+ |
| Lisa | Computer Science 190M | A+ |
| Lisa | Computer Science 143 | A+ |
| Milhouse | Computer Science 142 | B+ |
| Ralph | Computer Science 143 | B |
+--------------+-----------------------+-------+
6 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.00 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;
Now let's write a page that displays the result of a query in PHP.
Start from the following files:
