• The JOIN reading said that the (actor, movie) parings offer a unique ID for each role, but if an actor played multiple roles in one movie, wouldn't (actor, movie) go with more than one row?

    Yes; great point. We would need to have a different database representation, and the primary key for the CREDIT table would have to include the role. Modelling the world is hard.

  • Is there any difference at all between the usage of '!=' and '<>' in SQL?

    No.

  • Can JOIN connect 3 or more tables in one database? What about in multiple databases?

    Yes and yes. We'll do the first soon if not today. The latter is more rare, but quite possible.

  • Does knowing the unique id of a staff member count as knowing the staff member, or do you also need their human name? I'm assuming you want their human name without separately checking the tables, so you SHOULD use JOIN even through the movie database already gives you their unique id?

    I suppose it depends on the question being asked. But if I ask the registrar for people who are in both 204 and 304, I typically want names, not B-numbers.

  • Does OUTER JOIN give you rows with NA() values for missing data? Is it true to say that INNER JOIN will never create NA() values? Is it correct to say that INNER JOIN omits rows with keys found in one table but not the other, when using that shared key to connect the tables?

    OUTER JOIN gives you NULL for rows that have no match.

    INNER JOIN omits rows that have no match. So your last sentence is correct.

  • "I'm trying to figure out how many rows I can expect to find after using INNER or OUTER JOIN on two tables with 1:1, 1:N, or N:N mappings. I'm going to make some wild guesses, and can you please explain the correct answers?: If we had table A with a rows and table B with b rows, then: 
    Using INNER JOIN to merge tables using a 1:1 key would give us: min(a,b) to max(a,b) rows, or maybe a+b?
Using INNER JOIN to merge tables using a 1:N key would give us: at most a*b rows
Using INNER JOIN to merge tables using a N:N key would give us: also a*b rows??"

    MIN for the first.

    B for the second (can't have more rows than there are in the "many" table)

    For many-many, we'd use an intermediate table, basically representing it as two 1:N mappings. So the size of the intermediate table.

  • Can you go over the WHERE statements that use JOIN? I am feeling unsure about ON and USING

  • Would it be possible to clarify the applications of the cross-product joining of two tables?

    By thinking about it as a cross-product, we can think about joins in a more general way, allowing some more creative uses. For example, finding possible co-star relationships (actors joined with actors).

  • When doing a JOIN, or JOIN ON or USING, can we get some clarification on what the difference is between something like: 
    SELECT * FROM person INNER JOIN movie ON (person.nm = movie.director)
    compared to 
    SELECT * FROM person INNER JOIN movie ON (nm = director)
    Meaning, what happens when there is no dot operator used to specify a table in that ON conditions?

    If you are joining two tables and there is only one column named director among the two tables, so that the name director is unique and unambiguous, you can say director and not movie.director, omitting the name of the table that it is in.

    If it's not unique, you have to specify.

    So the latter is a perfectly equivalent shorthand for the first.

  • For 
    select * from person, movie where nm = director
    is it implied for the SQL query to recognize that nm belongs to person and director belongs to the movie table?

    yes

  • Since joining two tables is temporary for one query, would you need to create a loop if you want to make more queries?

    We don't loop in SQL. Sometimes, we build more complex subqueries. More later.

  • In the case of WMDB, is it possible for multiple "staff" to add the same movie? If that happens, are the staff uid stored in a list with the other uid who added the movie or are there multiple entries of the same movie?

    Yes. I (Scott w/ UID=1) have added many movies.

    No lists. Each movie says who added it.

  • What's the purpose of self crosses? I understand that in the reading, A and B are aliases of the person table, do we need to create shallow copies of the table we want to perform self crosses on before self crossing?

    No copies; just aliases. The purpose is to allow pairs from the same table. You don't like the co-stars example? How about pairs of classes that conflict? 

    select A.crn, B.crn where A.meeting_time = B.meeting_time;
  • I'm slightly confused about the code for self-crosses (what's happening with aliasing?).

    Each table needs a name, so we can refer to its columns. If the table is used twice, we need an alias for it.

  • How does deleting keys work, especially in a many-many relationship?

    Well, we delete rows, not keys. We'll learn that soon. But we also need to learn about referential integrity.

  • I'm bit confused about the cross-product examples given in the readings, could we go over more examples in class?

    Sure. It's in the plan.

  • Does order matter? For example is: 
    SELECT * FROM owner INNER JOIN pet ON (owner.oid = pet.oid) ...
    the same as 
    SELECT * FROM pet INNER JOIN owner ON (owner.oid = pet.oid) ...

    Order does not matter in that case.