University of Washington, CSE 190 M, Spring 2009
Section 9: Query as Folk (Databases/SQL)
Tuesday, May 26, 2009

section idea/code by Sylvia Tashev and Stefanie Hatcher

Today we will develop several SQL queries against the world and simpsons databases.

world Database Queries:

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

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, independace_year
   FROM countries
   WHERE independace_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 in Europe, Oceania, and Antarctica, sorted by continent.

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

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

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

13. List the countries whoose names start with either A or B.

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

14. 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;
    

simpsons Database Queries:

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

1. List all grades where a student was given a B- or better.

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

2. List all of the grades given in the course Computer Science 190M (2 Steps). You'll need to find out 190M's id number first.

   -- step 1: get the id of Computer Science 190 M
   SELECT id
   FROM courses
   WHERE name = 'Computer Science 190M';
   

   -- step 2: list students in 190 M
   SELECT grade
   FROM grades
   WHERE id = '10003';
   

3. List the names of all students that took Computer Science 143 (3 Steps). You'll need to find out 143's id number and the students' id numbers first.

   -- step 1: get the id of Computer Science 143
   SELECT id
   FROM courses
   WHERE name = 'Computer Science 143'; 
   

   -- step 2: get student ids that took 143
   SELECT student_id
   FROM grades
   WHERE course_id = '10002';
   

   -- step 3: list student names that match the previous ids
   SELECT name
   FROM students
   WHERE id IN ('123','888','456','404');
   

Viewing a Query in PHP:

Now let's write a page that displays the result of a query in PHP. Start from the following files:

• cities.php (initial HTML/CSS code)

Click the following image to run our sample solution (solution source code):