Overview

In this Lab, you will get practice with more advanced SQL queries, particularly with multi-table queries (joins). Join queries tend to be more tricky to design and write, but are one of the most powerful features of relational databases. This lab will particularly be useful for practicing JOINs for the Final Exam.

We will use the CSE 154 Query Tester for this lab. There are other exercises suggested for working on your own Cloud9 databases if you finish early, for work on your final creative project and/or more exam practice.

Multi-table Query Review

When you need to return information from more than one table in a database, you can join multiple tables in a single query by specifying criteria to join on (e.g. all actor names in the actors table for which the actor starred a role in the roles table, based on matching the actor's id with the role's actor_id column).

Multi-table queries are powerful features of SQL, but figuring out the optimal way to join multiple tables for one query takes plenty of practice.

Template of a Multi-table Query (Join)


          SELECT col(s)
          FROM table1, table2, ...
          WHERE table1.a = table2.b
          AND table2.c > '42';

SQL (template)


          SELECT s.name, g.*
          FROM students s, grades g
          WHERE s.id = g.student_id
          AND g.grade > 'C';

SQL (example with WHERE Join)


          SELECT s.name, g.*
          FROM students s
          JOIN grades g ON s.id = g.student_id
          WHERE g.grade > 'C';

SQL (equivalent example with JOIN keyword)

Designing a Query

Figure out the proper SQL queries in the following way:

  • Which table(s) contain the critical data? (FROM)
  • Which columns to I need in the result set? (SELECT)
  • How are tables connected (JOIN and/or WHERE) and values filtered (WHERE)?
  • Do I need to return only DISTINCT records?
  • Do I care about the order of records returned? If so, which columns do I need to sort by and in what precedence?

Part I: World Database

world database schema

Warm-up Query: Countries in Asia

List all the full names, population, and gnp of all countries in Asia, ordered in descending order by gnp, breaking ties ordering by population in ascending order. Expected results: 

    
+---------------------------------------+
| name        | population | gnp        |
+---------------------------------------+
| Japan       | 126714000  | 3787042.00 |
| China       | 1277558000 | 982268.00  |
| India       | 1013662000 | 447114.00  |
| South Korea | 46844000   | 320749.00  |
| Taiwan      | 22256000   | 256254.00  |
...                                     |
+---------------------------------------+
51 rows in set

English-Speaking Countries

List the full names of all countries where English is spoken as an official language. Do this as a single multi-table query. Expected results: 

    
+--------------------------------------+
| name                                 |
+--------------------------------------+
| American Samoa                       |
| Anguilla                             |
| Antigua and Barbuda                  |
| Australia                            |
| Barbados                             |
...
+--------------------------------------+
44 rows in set (0.02 sec)

Languages in France

List all of the languages that are spoken in France, as well as the expected number of speakers for that language (population * percentage / 100.0). Do this as a single multi-table query. Do not hard code the country code into your query. Expected results: 

    
+--------------+-----------+--------------+-------------------+
| language     | official  | percentage   | estimated_speakers|
+--------------+-----------+--------------+-------------------+
| French       | T         | 93.6         | 5543525429.6      |
| Arabic       | F         | 2.5          | 148064250.0       |
| Portuguese   | F         | 1.2          | 71070842.8        |
| Italian      | F         | 0.4          | 23690280.4        |
| Spanish      | F         | 0.4          | 23690280.4        |
| Turkish      | F         | 0.4          | 23690280.4        |
+--------------+-----------+--------------+-------------------+

Cities in China

List all of the cities in China, whose population is more than 0.2% of China's population. The results should include the name of the city, the district, the population, and the percentage of the city's population based on the total population of China. Organize results by percentage where the city with the highest population ratio comes first in the result. Do this as a single query. Do not hard code China's country code. Expected results: 

    
+-----------+-----------+-----------------+-------------+
| name      | district     | population   | percentage  |
+-----------+-----------+-----------------+-------------+
| Shangai   | Shangai      | 9696300      | 0.0076      |
| Milano    | Lombardia    | 1300977      | 0.0058      |
| Chongqing | Chongqing    | 6351600      | 0.0050      |
| Tianjin   | Tianjin      | 5286800      | 0.0041      |
| Harbin    | Heilongjiang | 4289800      | 0.0034      |
| ...                                                   |
+-----------+--------------+--------------+-------------+
14 total rows returned

High Population

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

    
+----------+
| name     |
+----------+
| Brazil   |
| India    |
| China    |
| Pakistan |
+----------+
4 rows in set

Part II: Movie (IMDB-Small) Database

movie database schema

Roles in Pi

Show all roles played in the movie named Pi. To achieve this, join the movies table with the roles table and filter out all records except those in Pi. You shouldn't need to know the movie ID of Pi ahead of time to do the query. 

    
+-------------------------+
| role                    |
+-------------------------+
| Man Delivering Suitcase |
| Brad                    |
...                       |
| Devi                    |
+-------------------------+
28 rows in set

Actors in Pi

Show the first/last names of all actors who appeared in Pi, along with their roles. You will need to join all three tables. 

    
+------------------+------------+-------------------------+
| first_name       | last_name  | role                    |
+------------------+------------+-------------------------+
| Abraham          | Aronofsky  | Man Delivering Suitcase | 
| Peter            | Cheyenne   | Brad                    | 
...                                                       |
| Samia            | Shoaib     | Devi                    | 
+------------------+------------+-------------------------+ 
28 rows in set

Actors and Genres

List all roles in a Horror or Sci-Fi movie with the first and last name of the actor who played the role, as well as the movie name. Order the results by last name alphabetically, breaking ties by first name alphabetically, further breaking ties by movie name alphabetically. 

    
+-------------+-----------+-------------------------+-------------+
| first_name  | last_name | role                    | movie_name  |              
+-------------+-----------+-------------------------+-------------+
| Steve       | Altes     | Dad                     | Hollow Man  |
| Julian      | Arahanga  | Apoc                    | Matrix, The |
| William (I) | Armstrong | Lydecker                | Aliens      |
| Abraham     | Aronofsky | Man Delivering Suitcase | Pi          |
| Graham      | Ashley    | Gold Five               | Star Wars   |
| Jennifer    | Aspen     | Nina                    | Vanilla Sky |
...                                                               |
| Bill(I)     | Young     | Lieutenant              | Matrix, The |
+-------------+-----------+-------------------------+-------------+
333 rows returned

Part III: Student (Small) Database

simpsons database schema

CSE 143 Grades

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

    
+-------+
| grade |
+-------+
| C     |
| A+    |
| D-    |
| B     |
+-------+
4 rows in set

CSE 143 Grades and Names

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

    
+-------+-------+
| name  | grade |
+-------+-------+
| Lisa  | A+    |
| Ralph | B     |
+-------+-------+
2 rows in set

Popular Classes

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

    
+----------------------+
| name                 |
+----------------------+
| Computer Science 142 |
| Computer Science 143 |
| Computer Science 154 |
+----------------------+
3 rows in set (0.01 sec)

More Practice

If you finish early, here are a few other suggestions to practice multi-table queries:

  • Practice using multi-table queries with the Full Stack Café example, adding Order and Customer tables with your Menu table. Whatkind of join queries would be helpful to include so that you can list all of the orders made by a customer? How about all customers who have ordered Espresso? Or al orders which have only a drink?
  • Integrate a database with multiple tables for your creative project if you haven't already. This is a really great thing to demonstrate you have experience with when applying for software development internships and jobs!
  • There are more join queries using this tutorial, as well as other advanced SQL topics and database updating (which you are working with on HW7).