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

  1. world Database (long)
  2. simpsons Database (long)

1. world Database (long)

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

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
3793New YorkUSANew York8008278
1Los AngelesUSACalifornia3694820
...............
languages
country_codelanguageofficialpercentage
AFGPashtoT52.4
NLDDutchT95.6
............
  1. 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;
  2. List all of the official languages spoken around the world in alphabetical order.
    SELECT DISTINCT language
    FROM languages
    WHERE official='T'
    ORDER BY language;
  3. 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';
  4. List the names of all countries in Antarctica.
    SELECT name 
    FROM countries
    WHERE continent = 'Antarctica';
  5. 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;
  6. 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;
  7. 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;
  8. 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;
  9. What countries have the word Republic as part of their name?
    SELECT name
    FROM countries
    WHERE name LIKE '%Republic%';
  10. 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;
  11. List all countries that are not in Africa.
    SELECT name
    FROM countries
    WHERE continent != 'Africa';
  12. 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;
  13. Which countries have a GNP between $10,000 AND $50,000?
    SELECT name, gnp
    FROM countries
    WHERE gnp BETWEEN 10000 AND 50000;
  14. List the countries whose names start with either A or B.
    SELECT name
    FROM countries
    WHERE name LIKE 'A%' OR name LIKE 'B%';
  15. 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;
  16. 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';
  17. 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

  1. SELECT language, percentage
    FROM languages
    WHERE country_code = 'USA'
    ORDER BY percentage DESC;
  2. SELECT DISTINCT language
    FROM languages
    WHERE official='T'
    ORDER BY language;
  3. SELECT country_code, language
    FROM languages
    WHERE percentage = 100
    AND language = 'English';
  4. SELECT name 
    FROM countries
    WHERE continent = 'Antarctica';
  5. SELECT name, life_expectancy
    FROM countries
    WHERE life_expectancy >= 78
    ORDER BY life_expectancy DESC;
  6. SELECT DISTINCT continent, region
    FROM countries
    ORDER BY continent, region;
  7. SELECT name, independence_year
    FROM countries
    WHERE independence_year < 0;
  8. SELECT name
    FROM countries
    WHERE name = local_name
    ORDER BY name;
  9. SELECT name
    FROM countries
    WHERE name LIKE '%Republic%';
  10. SELECT DISTINCT name, government_form
    FROM countries
    WHERE government_form LIKE '%monarchy%'
    ORDER BY government_form, name;
  11. SELECT name
    FROM countries
    WHERE continent != 'Africa';
  12. SELECT continent, name
    FROM countries
    WHERE continent IN ('Europe', 'Oceania', 'Antarctica')
    ORDER BY continent, name;
  13. SELECT name, gnp
    FROM countries
    WHERE gnp BETWEEN 10000 AND 50000;
  14. SELECT name
    FROM countries
    WHERE name LIKE 'A%' OR name LIKE 'B%';
  15. SELECT name, population, surface_area,
         population / surface_area as population_density
    FROM countries
    ORDER BY population_density DESC;
  16. SELECT c.name
    FROM countries c
    JOIN languages l on l.country_code = c.code
    WHERE l.language = 'German' AND l.official = 'T';
  17. 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
idnameemail
123Bartbart@fox.com
456Milhousemilhouse@fox.com
888Lisalisa@fox.com
404Ralphralph@fox.com
teachers
idname
1234Krabappel
5678Hoover
9012Stepp
courses
idnameteacher_id
10001Computer Science 1421234
10002Computer Science 1435678
10003Computer Science 190M9012
10004Informatics 1001234
grades
student_idcourse_idgrade
12310001B-
12310002C
45610001B+
88810002A+
88810003A+
40410004D+
  1. 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-';
  2. 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';
  3. 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-';
  4. 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;
  5. 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

  1. SELECT grade 
    FROM grades 
    WHERE grade <= 'B-';
  2. SELECT g.grade
    FROM grades g
    JOIN courses c ON c.id = g.course_id
    WHERE c.name = 'Computer Science 143';
  3. 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-';
  4. 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;
  5. 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;
Valid HTML Valid CSS