Q1: A River Runs Through It

A country can have many rivers running through it or bordering it.
A river can run through many countries and boundaries on its way to the sea.

1. Create a table of major rivers in the world. Add a few records, make them real. Make sure you include keys, constraints, and the usual.

Add one of the rivers without a country. Any river on the continent of Antarctica should fit this condition. (https://en.wikipedia.org/wiki/List_of_rivers_of_Antarctica)

2. Create a table of countries. Add a few records, make them real.

Add a country without any rivers. Vatican City comes to mind. It's so small that only sewers flow through it. You may be able to find other micro nations.

3. Now we want a table that'll relate countries to rivers. The name of "bridge" table is apropos here. Create this table. Establish a primary key and create two foreign keys: one reaching out to countries, one reaching out to rivers.

4. Write a query using a join that'll return countries sans rivers.

5. Write a query using a join that'll return rivers sans countries.

6. Are you able to combine joins 4 and 5 into a single FULL JOIN? If you've properly set up the tables, I don't think you can. But prove me wrong.

Q2: What's in a Name

Create a single-column CTE, call it first_name. The rows will be the letters of your first name in caps. Example: If your first name is Matija, this CTE would have 8 rows with the values of 'M','A','T','I','J','A'.
Create a single-column CTE, call it last_name. The rows will be the letters of your last name in caps.

1. Find letters contained in both your first and last names, using an INNER JOIN.

2. Find letters contained in both your first and last names, using a CROSS JOIN with a WHERE condition.

3. Find letters only in your first name, but not in your last name, using a join.

4. Find letters only in your last name, but not in your first name, using a join.

5. Combine 3, 4 into one single join.

Depending on your name, 1 through 5 could return no records.

Create another pair of CTEs, using the name of your classmate: Dominique Gabrielle.

6. Repeat 1 and 2 using the new CTEs. Before you run the query, ask yourself how many rows you're expecting. Then run the query and see how many rows you get.

Did your expectation of the count differ from the count you obtained? Scrutinize the input tables and the output table carefully.

Q3: Calculate your grade

I'll explain this in class.

Hint: After creating the look up tables for max grade. Use one single SELECT (and a bunch of CTEs) to arrive at the answer. No UPDATES, no intermediate tables. Display the score you calculated.

Below are the max points for each homework assignment and each quiz.

WITH h AS (
    SELECT * FROM (
        VALUES
        (1,12),
        (2,10),
        (3,NULL),
        (4,1),
    ) h(hw,pt)
)
SELECT * FROM h;

WITH q AS (
    SELECT * FROM (
        VALUES
        (1,4),
        (2,3),
        (3,4),
        (4,4),
        (5,3),
        (6,5),
        (7,4),
        (8,5),
        (9,4),
        (10,6),
        (11,1),
        (12,4),
        (13,5),
        (14,5),
        (15,4),
        (16,4),
        (17,4),
        (18,4),
        (19,4),
        (20,4),
        (21,4),
        (22,4),
        (23,4),
        (24,NULL),
        (25,NULL),
        (26,1)
    ) q(quiz,pt)
)
SELECT * FROM q;