Join algorithms (nested loop, sort-merge, hash join) in DBMS Theory - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to describe the basic operation of a nested loop join.

DBMS Theory

For each row in the outer table, the algorithm scans [1] rows in the inner table to find matching join keys.

Drag options to blanks, or click blank then click option'

Ano

Ball

Cone

Dsome

Attempts:

3 left

💡 Hint

Common Mistakes

Assuming only one or some rows are checked instead of all.

Confusing nested loop join with more efficient join types.

Nested loop join compares each row of the outer table with all rows of the inner table to find matches.

2fill in blank

medium

Complete the code to describe the first step of a sort-merge join.

DBMS Theory

The first step in a sort-merge join is to [1] both input tables on the join key.

Drag options to blanks, or click blank then click option'

Asort

Bhash

Cscan

Dfilter

Attempts:

3 left

💡 Hint

Common Mistakes

Confusing sorting with hashing or scanning.

Skipping the sorting step before merging.

Sort-merge join requires both tables to be sorted on the join key before merging.

3fill in blank

hard

Fix the error in the description of a hash join.

DBMS Theory

In a hash join, the algorithm builds a hash table on the [1] table and probes it with rows from the [2] table.

Drag options to blanks, or click blank then click option'

Aouter

Blarger

Csmaller

Dinner

Attempts:

3 left

💡 Hint

Common Mistakes

Building the hash table on the larger table, which is inefficient.

Confusing which table is hashed and which is probed.

Hash join builds a hash table on the smaller table to optimize memory usage and probes it with rows from the other table.

4fill in blank

hard

Fill both blanks to complete the description of sort-merge join conditions.

DBMS Theory

Sort-merge join requires both tables to be [1] on the join key and then [2] through them to find matching rows.

Drag options to blanks, or click blank then click option'

Asorted

Bhashed

Cmerged

Dscanned

Attempts:

3 left

💡 Hint

Common Mistakes

Using hashing instead of sorting.

Confusing scanning with merging.

Sort-merge join first sorts both tables and then scans through them to find matching rows.

5fill in blank

hard

Fill all three blanks to complete the hash join process description.

DBMS Theory

The hash join algorithm first [1] the smaller table into a hash table, then [2] the larger table, and finally [3] matching rows using the hash table.

Drag options to blanks, or click blank then click option'

Abuilds

Bscans

Cprobes

Dsorts

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up scanning and building steps.

Using sorting instead of hashing in hash join.

Hash join builds a hash table from the smaller table, scans the larger table, and probes the hash table to find matching rows.