example.sql

CREATE /*TEMPORARY*/ TABLE IF NOT EXISTS mongorestore_tbl (
  block_id  VARCHAR(33) PRIMARY KEY,
  exam      VARCHAR(33),
  children  TEXT
) DEFAULT CHARSET=utf8;

CREATE /*TEMPORARY*/ TABLE IF NOT EXISTS study_tbl (
  course_id VARCHAR(33),
  student_id VARCHAR(33),
  id MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY
) DEFAULT CHARSET=utf8;

INSERT INTO mongorestore_tbl VALUES (
  ('5555', 'exam3', 'AAAAA:KKKKK:EEEEE:PPPPP'),
  ('2222', 'exam3', 'DDDDD:LLLLL'),
  ('7777', 'exam3', 'JJJJJ:NNNNN'),
  ('3333', NULL, NULL),
  ('0000', NULL, 'CCCCC:')
);

INSERT INTO study_tbl (course_id, student_id) VALUES (
  ('AAAAA', '[email protected]'),
  ('KKKKK', '[email protected]'),
  ('EEEEE', '[email protected]'),
  ('PPPPP', '[email protected]'),
  ('DDDDD', '[email protected]'),
  ('JJJJJ', '[email protected]'),
  ('CCCCC', '[email protected]')
);

/* GOAL: SELECT where you get all exams for students in study_tbl 

  ('AAAAA', '[email protected]', 'exam3'),
  ('KKKKK', '[email protected]', 'exam3'),
  ('EEEEE', '[email protected]', 'exam3'),
  ('PPPPP', '[email protected]', 'exam3'),
  ('DDDDD', '[email protected]', 'exam3'),
  ('JJJJJ', '[email protected]', 'exam3'),
  ('CCCCC', '[email protected]', NULL)
*/