Skip to content

Sep 03: Joins and Aggregation

Learning Objectives

After today's class, you should be able to:

  • Write queries that combine tables using JOIN syntax (both ON and USING).
  • Summarize groups of rows using GROUP BY and aggregate functions.

Lesson Outline

Project Overview [15 min]

Lecture Slides [30 min]

Lab Activity [30 min]

Your To-Do List

  • Submit sqlite-queries.sql on Gradescope
  • Submit hw1.sql on Gradescope ← due Monday
  • Read the project documents before Tuesday

Practice Queries

Instructions

Create a text file named sqlite-queries.sql. Put your name at the top of the file in a comment (starting with --). Use this file to record your solutions for each of the queries below. Submit your work to Gradescope by the end of the day.

World Database
  1. List all countries in Europe with their population.
    • Schema: ID, Name, Population
    • Order: Population (descending)
  2. Find the capital city of Finland.
    • Schema: Country, Capital_City
    • Order: N/A
  3. Show all official languages of countries in South America.
    • Schema: Country, Language
    • Order: Country
  4. What are the top 10 most populous cities in the world?
    • Schema: Name, Population, CountryCode
    • Order: Population (descending)
  5. Count the number of countries per continent.
    • Schema: Continent, Num_Countries
    • Order: Num_Countries (descending)
Company Database
  1. Find the current manager of each department
    • Schema: dept_name, first_name, last_name
    • Order: dept_name
  2. Show the current department of a specific employee
    • Schema: first_name, last_name, dept_name
    • Order: N/A
  3. Top 10 highest paid employees (current salary only)
    • Schema: emp_no, first_name, last_name, salary
    • Order: salary (descending)
  4. Average current salary by department
    • Schema: dept_name, avg_salary
    • Order: avg_salary (descending)
  5. Count how many employees are in each department (currently)
    • Schema: dept_name, num_employees
    • Order: num_employees (descending)