# ferdo Lab | UCL PALS | Week 2 independent extra example
# Student practice — the two original tables are complete.
# Instructions and explanations: https://lab.ferdo.net/r/pals-masterclass/extra-inner-join-two-keys/
# This is independent fictional material, not official UCL Moodle work.
# Install dplyr once from the Console if necessary: install.packages("dplyr")
library(dplyr)

# STARTING DATA — inspect these tables before joining.
ogrenciler <- data.frame(
  ogrenci_no = c(101, 102, 103, 104, 105),
  ders_kodu  = c("MAT101", "FIZ101", "KIM101", "BIO101", "TAR101"),
  ad         = c("Ali", "Zeynep", "Can", "Elif", "Mert")
)

sinav_sonuclari <- data.frame(
  ogrenci_no = c(101, 101, 101, 102, 103, 104, 106),
  ders_kodu  = c("MAT101", "MAT101", "FIZ101", "FIZ101",
                 "MAT101", "BIO101", "MAT101"),
  tur        = c("Vize", "Final", "Vize", "Final",
                 "Vize", "Final", "Vize"),
  notu       = c(70, 82, 55, 91, 78, 88, 66)
)

# STEP 1. Print the five-row student table and the seven-row exam table.
# print(
# print(

# STEP 2. Join the tables using ONLY ogrenci_no.
#         Store the result in sonuc_tek and print it.
# sonuc_tek <- inner_join(
# print(

# STEP 3. Compare the two course-code columns in the result.
#         Which rows have different values for ders_kodu.x and ders_kodu.y?
# uyusmayan_dersler <- filter(

# STEP 4. Join again, but this time require BOTH ogrenci_no and
#         ders_kodu to match. Store the result in sonuc_iki.
# sonuc_iki <- inner_join(
# print(

# STEP 5. Count the rows in each result and count the distinct
#         student numbers in sonuc_iki.
# nrow(
# nrow(
# n_distinct(

# STEP 6. Use anti_join to inspect student rows without a matching
#         student/course pair, then inspect unmatched exam rows.
# anti_join(
# anti_join(

# DISCUSS AFTER RUNNING YOUR CODE:
# - Why is student 101 listed twice in the two-key result?
# - What happened to the FIZ101 exam for 101?
# - Why does the exam for 103 appear with one key but not with two keys?
# - What happened to students 105 and 106?
# - Why do the course code columns have .x and .y after the one-key join?