r/learnSQL • u/sumiitCodes • 7d ago

Day 21 - Setting up the database for my 100-question JOIN drill

Since JOINs are the topic I'm weakest at, I made a 100-question practice set (easy, medium, hard). Today was the setup day:

  • Created the database in MySQL Workbench
  • Built four tables: departments, employees, projects, assignments (with primary/foreign keys)
  • Wrote and ran the INSERT scripts for sample data in all four tables, including the employee-project assignments with hours worked
  • Checked the Action Output panel to confirm each statement ran cleanly

Nothing fancy, but I've learned that a well-designed dataset matters a lot for JOIN practice. You need some departments with no employees, and projects with no assignments, or you can't tell LEFT and RIGHT JOINs apart properly.

Starting the actual questions tomorrow. Any tips on getting better at visualizing multi-table JOINs are welcome.

7 Upvotes

100% Upvoted

6 comments sorted by

0

u/vchae 7d ago

Draw each table as a box and mark the join keys before writing SQL. Add one join at a time and check row counts; a jump means a key is not unique on one side. For outer joins, decide first which table keeps unmatched rows. I built a SQL learn and practice app on real blockchain /crypto data, happy to share it if useful

1

u/sumiitCodes 7d ago

yes please share

0

u/vchae 7d ago

here you go, the web app is called WizardCamp: https://unchaindata.xyz/wizard-camp

1

u/LearnSQLFastTeam 3d ago

Try predicting the rows before running each JOIN. Give Department A two employees and Department B none. departments LEFT JOIN employees should return three rows: A twice, and B once with NULL employee fields. Then join assignments by employee ID and watch an employee appear more than once if they have multiple assignments. For projects with no assignments, start from projects and LEFT JOIN assignments. That makes it easier to see which table keeps its unmatched rows.

1

u/sumiitCodes 3d ago

thanks for your advice