Skip to content

Aug 28: Relational Algebra, SQL Joins

Learning Objectives

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

  • Summarize relational algebra operations and their relationship to SQL.
  • Write SQL queries using JOIN syntax (both USING and ON syntax).

Lesson Outline

Lecture Slides [30 min]

Lab Activity [45 min]

Your To-Do List

  • Submit the TeamPref Survey on Canvas – due today!
  • Finish reading sections 1.2–1.3 and do at least 75% of the activities
  • Submit HW1 on Gradescope by the end of Monday

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. For full credit, you must submit at least 5 queries with at least 2 from each database.

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)