SQL Plus workshop question As you do the exercises, it is a good idea to copy/pa
ID: 3900923 • Letter: S
Question
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: ____.
Explanation / Answer
3-2
a. 2 rows are there in equijoin for jobno 100 and 200
b. 3 rows(100,200,200) . The rows have different descriptions for the same jobno.
c. 0 rows. There are no matching rows for equijoin operation
d. 6 rows. (All rows in two tables are matching)
e. The number of rows from an equi-join operation is all the matching rows.
The number of rows from cartesian operation is m*n, where m is the number of rows in first table and n is the number of rows in second table.
f. 0 to M*N
0 if no row match in two tables
M, if M rows match in two tables where M<N
M*N if all rows match in two tables
Do ask if any doubt. Please upvote.
Do ask if any doubt. Please upvote.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.