If there are fewer than 3 people in your group, feel free to merge your group with another group in the room.

Now switch to Pensieve:

• Everyone: Go to pensieve.co, log in with your @berkeley.edu email, and enter your group number (which was in the email that assigned you to this lab).

Once you're on Pensieve, you don't need to return to this page; Pensieve has all the same content (but more features). If for some reason Penseive doesn't work, return to this page and continue with the discussion.

Getting Started

Everybody say your name, and then share your favorite restaurant, cafe, or boba shop near campus. (Yes, Kingpin Donuts counts as a restaurant.)

Select Statements

A SELECT statement describes an output table based on input rows. To write one:

1. Describe the input rows using FROM and WHERE clauses.
2. Format and order the output rows and columns using SELECT and ORDER BY clauses.

SELECT (Step 2) FROM (Step 1) WHERE (Step 1) ORDER BY (Step 2);

The WHERE and ORDER BY clauses are optional.

Pizza Time

The pizzas table contains the names, opening, and closing hours of great pizza places in Berkeley. The meals table contains typical meal times (for college students). A pizza place is open for a meal if the meal time is at or within the open and close times.

CREATE TABLE pizzas AS
  SELECT "Artichoke" AS name, 12 AS open, 15 AS close UNION
  SELECT "La Val's"         , 11        , 22          UNION
  SELECT "Sliver"           , 11        , 20          UNION
  SELECT "Cheeseboard"      , 16        , 23          UNION
  SELECT "Emilia's"         , 13        , 18;

CREATE TABLE meals AS
  SELECT "breakfast" AS meal, 11 AS time UNION
  SELECT "lunch"            , 13         UNION
  SELECT "dinner"           , 19         UNION
  SELECT "snack"            , 22;

Q1: Open Early

You'd like to have pizza before 13 o'clock (1pm). Create a opening table with the names of all pizza places that open before 13 o'clock, listed in reverse alphabetical order.

opening table:

-- Pizza places that open before 1pm in alphabetical order
CREATE TABLE opening AS
  SELECT name FROM pizzas WHERE open < 13 ORDER BY name DESC;

Q2: Study Session

You're planning to study at a pizza place from the moment it opens until 14 o'clock (2pm). Create a table study with two columns, the name of each pizza place and the duration of the study session you would have if you studied there (the difference between when it opens and 14 o'clock). For pizza places that are not open before 2pm, the duration should be zero. Order the rows by decreasing duration.

Hint: Use an expression of the form MAX(_, 0) to make sure a result is not below 0.

study table:

-- Pizza places and the duration of a study break that ends at 14 o'clock
CREATE TABLE study AS
  SELECT name, MAX(14 - open, 0) AS duration FROM pizzas ORDER BY duration DESC;

Q3: Late Night Snack

What's still open for a late night snack? Create a late table with one column named status that has a sentence describing the closing time of each pizza place that closes at or after snack time. Important: Don't use any numbers in your SQL query! Instead, use a join to compare each restaurant's closing time to the time of a snack. The rows may appear in any order.

late table:

The || operator in SQL concatenates two strings together, just like + in Python.

-- Pizza places that are open for late-night-snack time and when they close
CREATE TABLE late AS
  SELECT name || " closes at " || close AS status
  FROM pizzas, meals
  WHERE meal="snack" AND time <= close;

Document the Occasion

Please all fill out the attendance form (one submission per person per week).