1. world Database (long)

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 language, percentage
   FROM languages
   WHERE country_code = 'USA'
   ORDER BY percentage DESC;

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

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

problem by Sylvia Tashev and Stefanie Hatcher

2. simpsons Database (long)

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. Expected results:

   +-------+
   | grade |
   +-------+
   | B-    |
   | B+    |
   | A+    |
   | A+    |
   | B     |
   | A+    |
   +-------+
   6 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     |
   | 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';

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

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

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

problem by Sylvia Tashev and Stefanie Hatcher

3. City Searcher:

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)

cities.php (initial HTML/CSS code):

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
   <!--
   CSE 190M, Spring 2009, Marty Stepp
   Section 9: Queries
   -->

  <head>
     <title>City Searcher</title>
     <link href="http://www.cs.washington.edu/education/courses/cse190m/09sp/labs/section9-query/solution/cities.css" type="text/css" rel="stylesheet" />
  </head>


  <body>
      <div>
         <img src="http://www.cs.washington.edu/education/courses/cse190m/09sp/labs/section9-query/solution/banner.gif" alt="City Searcher" />
      </div>

      <p>
         Choose a country, and we will show you its largest cities.
      </p>

      <form action="response.php">
         <select name="countrycode">
            <option value=""> FILL ME WITH DATA! </option>
         </select>

         <select name="count">
            <option value="3">top 3</option>
            <option value="5">top 5</option>
            <option value="10">top 10</option>
         </select>
         <input type="submit" />
      </form>
   </body>
</html>

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

problem by Sylvia Tashev and Stefanie Hatcher

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
   <!--
   CSE 190M, Spring 2009, Marty Stepp
   Section 9: Queries
   -->

  <head>
     <title>City Searcher</title>
     <link href="cities.css" type="text/css" rel="stylesheet" />
  </head>


  <body>
      <div>
         <img src="banner.gif" alt="City Searcher" />
      </div>

      <p>
         Choose a country, and we will show you its largest cities.
      </p>

      <form action="response.php">
         <select name="countrycode">

            <?php
            include("common.php");
            $results = do_query("SELECT name, code FROM countries ORDER BY name;");
            while ($row = mysql_fetch_array($results)) {
               ?>

               <option value="<?= $row['code'] ?>"> <?= $row['name'] ?> </option>

               <?php
            }
            ?>

         </select>

         <select name="count">
            <option value="3">top 3</option>
            <option value="5">top 5</option>
            <option value="10">top 10</option>
         </select>
         <input type="submit" />
      </form>
   </body>
</html>

<?php
include("common.php");

# check for query params
if (!isset($_REQUEST["countrycode"])) {
   header("HTTP/1.1 400 Illegal Request");
   die("Error: no country code specified");
}
if (!isset($_REQUEST["count"])) {
   header("HTTP/1.1 400 Illegal Request");
   die("Error: no count specified");
}

$country_code = $_REQUEST["countrycode"];
$count = (int) $_REQUEST["count"];

$query = "SELECT name, population
          FROM cities
          WHERE country_code = '$country_code'
          ORDER BY population DESC
          LIMIT $count;";
$results = do_query($query);
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
   <head>
      <title>City Searcher - Results</title>
     <link href="cities.css" type="text/css" rel="stylesheet" />
   </head>


   <body>
      <h1> Top <?= $count ?> Biggest Cities </h1>
      
      <table>
         <tr>
            <th>City</th> <th>Population</th>
         </tr>
         
         <?php
         while ($row = mysql_fetch_array($results)) {
            ?>

            <tr>
               <td> <?= $row["name"] ?> </td>
               <td> <?= $row["population"] ?> </td>
            </tr>

            <?php
         }
         ?>

      </table>
   </body>
</html>

<?php
# makes sure result is not false/null; else prints error
function check($result, $message) {
   if (!$result) {
      die("SQL error during $message: " . mysql_error());
   }
}

# performs the given query and returns the result set, else prints error
function do_query($query) {
   # connect to database
   check(mysql_connect("localhost", "traveler", "packmybags"), "mysql_connect");
   check(mysql_select_db("world"), "mysql_select_db");

   # search database for the biggest cities
   $results = mysql_query($query);
   check($results, "mysql_query(\"$query\")");
   return $results;
}
?>

4. Languages of Continents

Write a page that allows you to view all of the languages spoken on a given continent. You can view the finished product here. The user can select a continent from a drop down list, and a table of all of the languages spoken on that continent appears, along with the country where that language is spoken.

This page will require a MySQL query involving two tables in the countries database on webster.cs.washington.edu. You must find all of the countries for a given continent by using the countries table, and then find all of the languages for each of these countries using the languages table. Use the JOIN MySQL keyword to achieve this.

problem by Alex Miller

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
   "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> 
 
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en"> 
   <head>
       <title>Languages of Continents</title>
   </head>
   <body>
       <form action="">
            <select name="continent" />
                <option value="Asia">Asia</option>
                <option value="Africa">Africa</option>
                <option value="Europe">Europe</option>
                <option value="North America">North America</option>
                <option value="South America">South America</option>
            </select>
            <input type="submit" />
        </form>
        <hr />
        <?php
        if (isset($_GET["continent"])) {
            $continent = $_GET["continent"];
            ?>
            <h1>Languages spoken in <?= $continent ?></h1>
            <?php
            $db = mysql_connect("localhost", "traveler", "packmybags");
            mysql_select_db("world");
            $results = mysql_query("SELECT name,language FROM countries JOIN languages ON countries.code = languages.country_code WHERE continent='$continent'");
            $row = mysql_fetch_array($results);
            ?>
            <table>
            <?php
            while ($row) {
                ?>
               <tr>
                   <td><?= $row["name"] ?></td>
                   <td><?= $row["language"] ?></td>
               </tr>
               <?php
                $row = mysql_fetch_array($results);
            }
        }
        ?>
    </body>
</html>