In Aggregate

Jed Rembold

Monday, September 15, 2025

Announcements

  • HW3 is due on Wednesday
    • I’m working on HW2 feedback, should be out today
      • I will generally only be grading a subset of the problems
      • Solutions for all problems will be posted (website coming soon)
  • We’ll start in on Chapter 7 on Wednesday
  • Polling today: polling.jedrembold.prof

Understanding Check

Suppose I have a table which was created as seen to the right, which contains information on spheres formed of different materials. If I then ran the below right query to compute the radius of each of the spheres, what would the resulting data type be?

CREATE TABLE rev (
  material_name TEXT,
  ball_mass INT,
  mat_dens REAL
);
SELECT
  ||/( ball_mass / mat_dens / 
     (4 / 3) / 3.14) AS radius
FROM rev;
  1. Integer
  2. Numeric
  3. Real
  4. Double Precision

Temp Tables

Queries to Tables

  • Sometimes, especially as we continue to build sophistication in our queries, you may want to save the output of a query to another database table

  • This can give you a multistep process in approaching more complicated actions

  • To set a table to a query output, use the AS keyword

    CREATE TABLE |||my new table||| AS (
  |||some SELECT query|||
);

  • This table will have columns name according to the select output, so aliases can be important!

Temporary Tables

  • It is sometimes useful to explicitly indicate a temporary table
    • Add the keyword TEMP before TABLE
    • These tables only exist within a session or transaction
    • Automatically cleaned up
  • In many clients, if you want to use these you might need to run a batch of commands all as a single query.
    • Otherwise, the clients frequently will timeout a session and then open a new one when you run another command.

All Together Now

Prolonged Example

  • I want to build up a box-plot of the distribution of ratings across all the cereals in my cereal table.
  • Need to compute:
    • the median
    • the first and third quartiles
    • the interquartile range
    • the lower and upper whiskers
Boxplot Illustration

Clash of the Calculations

Setting the Stage

  • For the remainder of the day we are going to get some practice in groups with calculations
  • Your groups and locations to gather in the classroom will be on the next slide
  • Each group will be allowed to use one laptop for the problems
    • Ensure that you are using someone’s laptop that has the pokemon dataset loaded into their database
  • The individual allowed to type on the laptop will rotate each round

Groups!

  • Group 1: Kara, Emma M, Julia, Cecil
  • Group 2: Sophie, Catalina, Talia, Evan
  • Group 3: Connor, Michael, Oliver, Tegan
  • Group 4: Jude, Gregory, Mia, Finn
  • Group 5: Thomas, Jackson, Lola, Jose
  • Group 6: Addison, Jimmy, KJ, Farley
  • Group 7: Beyla, Emma S, Saul, Evyn
  • Group 8: Dominic, Nico, Judah

Explanation

  • You will have ~3 minutes to submit an answer.
  • Faster gets you more points, but you only get a single submission!
  • One group member in charge of submitting at: https://pollev.com/jedrembold441
// reveal.js plugins // Added plugins