You can write your SQL statements in Sequel Pro and then save them to individual text files with a .sql extension. The difference between choices and answers is confusing. Answer are a persons actual answer to a question, choices are the precreated choices for a multiple choice question.
- schema.sql
Create a schema.sql file to make the following database structure
Users have a name. Quizzes have a name. Questions have content and belong to a quiz. (Each question only belongs to 1 quiz) Choices have content, can be correct or not, and belong to a question. Answers belong to a user, a question, and a choice.
Your response should be in the format of a series of sql commands like:
CREATE TABLE user ( /* do some sql magic here / ); / etc ... */
- insert.sql
Write an insert.sql file to create the following data:
There should be 3 users. There should be 2 quizzes. Each quiz has 5 questions. Each question has 4 choices.
Choices should be inserted into questions via a multi-insert.
- update.sql
Write an update.sql that will update user with id 1's name.
- quiz.sql
Create a quiz.sql file that will display all questions in a quiz.
- choices.sql
Create an choices.sql that will display all the choices for a particular question.
- take_test.sql
Create a take_test.sql for each user that multi inserts their answers for.
Every user has taken both quizzes and answered all questions.
User 1 has gotten a perfect User 2 has answer 3/5 correct. User 3 has answered 1/5 correct. 7. grading.sql
Create a grading.sql that will grade each quiz for each user.
These should be using joins. The goal is to show how many correct answers a user got. 8. stats.sql
Create a stats.sql that will show the percentage or total right answers.
Do percentage if you want to try to use MATH functions within SQL This will need to use joins too and might get complicated.