Adding Data

adding_data.sql

INSERT INTO user (id, first_name, email)
  VALUES ( 0, "Josh", "[email protected]");

INSERT INTO user (id, first_name, email)
  VALUES ( 1, "Aaron", "[email protected]");

INSERT INTO user (id, first_name, email)
  VALUES ( 2, "Matt", "[email protected]");

INSERT INTO user (id, first_name, email)
  VALUES ( 3, "Li", "[email protected]");


  INSERT INTO quiz (id, user_id, name)
  VALUES ( 0, 0, "Ruby Strings");

INSERT INTO question (id, content)
  VALUES ( 0, "what does the + operator do with two strings?" );

INSERT INTO quiz_question (id, quiz_id, question_id)
  VALUES ( 0, 0, 0);

SELECT question.content
FROM quiz, question, quiz_question
WHERE
    quiz.id = quiz_question.quiz_id AND
    question.id = quiz_question.question_id AND
    quiz.id = 0;

INSERT INTO choice (id, content, question_id, correct)
  VALUES (0, "interpolation", 0, 0);

INSERT INTO choice (id, content, question_id, correct)
  VALUES (1, "concatenation", 0, 1);

SELECT choice.content
  FROM question, choice
  WHERE
    choice.question_id = question.id AND
    choice.correct = 1;

INSERT INTO answer (id, user_id, question_id, choice_id)
  VALUES (0, 1, 0, 1);

INSERT INTO answer (id, user_id, question_id, choice_id)
  VALUES (1, 2, 0, 0);

 -- for the first question, how did Aaron answer?
SELECT choice.content
  FROM user, question, choice, answer
  WHERE
    answer.user_id = user.id AND
    answer.question_id = question.id AND
    answer.choice_id = choice.id AND
    user.first_name = "Aaron";

  -- for the first question, was Aaron's answer correct?
  SELECT choice.correct
  FROM user, question, choice, answer
  WHERE
    answer.user_id = user.id AND
    answer.question_id = question.id AND
    answer.choice_id = choice.id AND
    user.first_name = "Aaron";

-- for the first question, how did Matt answer?
SELECT choice.content
  FROM user, question, choice, answer
  WHERE
    answer.user_id = user.id AND
    answer.question_id = question.id AND
    answer.choice_id = choice.id AND
    user.first_name = "Matt";

-- for the first question, was Matt's answer correct?
  SELECT choice.correct
  FROM user, question, choice, answer
  WHERE
    answer.user_id = user.id AND
    answer.question_id = question.id AND
    answer.choice_id = choice.id AND
    user.first_name = "Matt";

-- Inserting more questions
INSERT INTO question (id, content)
  VALUES ( 1, "which method capitalizes the first letter of every word in a string?" );

INSERT INTO quiz_question (id, quiz_id, question_id)
  VALUES ( 1, 0, 1);

-- Inserting the choices
INSERT INTO choice (id, content, question_id, correct)
  VALUES (2, ".capitalize", 1, 0);

INSERT INTO choice (id, content, question_id, correct)
  VALUES (3, ".titleize", 1, 1);

-- check to see the correct answer
SELECT choice.content
  FROM question, choice
  WHERE
    choice.question_id = question.id AND
    choice.correct = 1 AND
    question.id = 1;

-- show me the first quiz
SELECT question.content
  FROM question, quiz, quiz_question
  WHERE
    quiz_question.quiz_id = quiz.id AND
    quiz_question.question_id = question.id AND
    quiz.id = 0;

-- Let's make Josh take the test
INSERT INTO answer (id, user_id, question_id, choice_id)
  VALUES (2, 0, 0, 1);

INSERT INTO answer (id, user_id, question_id, choice_id)
  VALUES (3, 0, 0, 3);

-- For the very first quiz, how many questions did Josh answer correctly?
SELECT choice.content, count(*)
  FROM question, user, quiz_question, quiz, answer, choice
  WHERE
    user.first_name = "Josh" AND
    answer.user_id = user.id AND
    answer.choice_id = choice.id AND
    choice.correct = 1 AND
    choice.question_id = question.id AND
    quiz_question.question_id = question.id AND
    quiz_question.question_id = quiz.id AND
    quiz.id = 0;