Skip to content

Jan 27: 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

  • Finish reading sections 1.2 and 1.3 in the book
  • Begin reading sections 1.4 and 1.5
  • Submit HW1 on Gradescope by the end of Friday

Practice Queries on the HW1 databases

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)