SQL Plus workshop question

As you do the exercises, it is a good idea to copy/paste your query as well as your query result into a word processor.

3-2

Create two tables, T1(ename, jobno) and T2(jobno, jobdesc). Let jobno be data-type, NUMBER(3), and use appropriate data-types for the other attributes. Put three rows in T1 and two rows in T2. Give T1.jobno values 100, 200, 300 for the three rows: <..., 100>,<..., 200,>,<..., 300>, where ... represents any value you choose. Give T2.jobno the values 100, 200: <100,...>,<200,...>.

a. How many rows are there in the equi-join (on jobno) of T1 and T2?

b. If the values of T2.jobno were <200,...>, <200,...> (with different jobdesc values), how many rows would you expect and why? Why would the rows have to have different descriptions?

c. If the values of T2.jobno were 400, 500 as in <400,...>,<500,...>, how many rows would you expect to get?

d. If the values of T1.jobno were <..., 100>,<..., 100>,<..., 100> (different names) and the values of T2.jobno were <100,...>,<100...> with different descriptions, how many rows would you expect to get?

e. If you have two tables, what is the number of rows you may expect from a equi-join operation (with what conditions)? A Cartesian product?

f. The number of rows in an equi-join of two tables, whose sizes are M and N rows, is from ___ to ____ depending on these conditions: ____.