alex_take_test.sql

INSERT INTO answers (answer_id, user_id, question_id, choice_id)
VALUES 
  (11,2,1,1),
  (12,2,2,7),
  (13,2,3,9),
  (14,2,4,14),
  (15,2,5,17),
  (16,2,6,21),
  (17,2,7,27),
  (18,2,8,32),
  (19,2,9,35),
  (20,2,10,40)
  ;

bana_take_test.sql

INSERT INTO answers (answer_id, user_id, question_id, choice_id)
VALUES 
  (21,3,1,1),
  (22,3,2,6),
  (23,3,3,12),
  (24,3,4,16),
  (25,3,5,20),
  (26,3,6,24),
  (27,3,7,28),
  (28,3,8,32),
  (29,3,9,35),
  (30,3,10,40)
;

beyonce_take_test.sql

INSERT INTO answers (answer_id, user_id, question_id, choice_id)
VALUES 
  (1,1,1,1),
  (2,1,2,7),
  (3,1,3,9),
  (4,1,4,15),
  (5,1,5,18),
  (6,1,6,21),
  (7,1,7,27),
  (8,1,8,29),
  (9,1,9,36),
  (10,1,10,40)
  ;

grading.sql

SELECT users.name, quizzes.name, questions.content, choices.correct
  FROM users
    JOIN choices ON answers.choice_id = choices.choice_id
    JOIN quizzes ON quizzes.quiz_id = questions.quiz_id
    JOIN answers ON answers.user_id = users.user_id
    JOIN questions ON questions.question_id = choices.question_id;

insert.sql

INSERT INTO users (user_id, name)
VALUES 
  (1,"Saron Yitbarek"),
  (2,"Alex Chiu"),
  (3,"Bana Malik")
;
 
INSERT INTO quizzes (quiz_id, name)
VALUES 
  (1,"SQL Quiz"),
  (2,"Ruby Quiz")
;
 
INSERT INTO questions (question_id, content, quiz_id)
VALUES 
  (1,"Why did the chicken cross the road?", 1),
  (2,"Where are the wild things?", 1),
  (3,"Her?", 1),
  (4,"Where's Mike?", 1),
  (5,"Egg?", 1),
  (6,"Albuquerque, where?", 2),
  (7,"Rock climbing when?", 2),
  (8,"Did the Heat lose in the finals?",2),
  (9,"Who is the President?", 2),
  (10,"Is the NSA watching you?",2)
;
 
INSERT INTO choices (choice_id, name, correct, question_id)
VALUES 
  (1,"To get to the other side.", 1, 1),
  (2,"Because it's not too bright.", 0, 1),
  (3,"Her?", 0, 1),
  (4,"Your mom",0, 1),
  (5,"Over there!",0, 2),
  (6,"Bana's house.",0, 2),
  (7,"At FS.",1, 2),
  (8,"Under the table.",0, 2),
  (9,"Bland?",1, 3),
  (10,"Him.",0, 3),
  (11,"Your dad.",0, 3),
  (12,"Cookies.",0, 3),
  (13,"At the Grand Canyon",0,4),
  (14,"Texas.",0,4),
  (15,"New York",1,4),
  (16,"California",0,4),
  (17,"Sunny-Side Up",0,5),
  (18,"Over-Easy",1,5),
  (19,"Scrambled",0,5),
  (20,"With Shells, Manley-Style",0,5),
  (21,"New Mexico",1,6),
  (22,"Washington",0,6),
  (23,"Russia",0,6),
  (24,"Alaska",0,6),
  (25,"Wednesday",0,7),
  (26,"Thursday",0,7),
  (27,"Saturday",1, 7),
  (28,"Now",0,7),
  (29,"Should have.",1,8),
  (30,"Yep! Two years ago..",0,8),
  (31,"Nope",0,8),
  (32,"Lebron who?",0,8),
  (33,"Washington.",0,9),
  (34,"Jay-Z",0,9),
  (35,"Obama's Mama",0,9),
  (36,"Barry Hussein",1,9),
  (37,"Who, me?",0,10),
  (38,"All the time",0,10),
  (39,"In your sleep.",0,10),
  (40,"Never!",1,10)
;

quiz.sql

-- SELECT content FROM questions;
-- SELECT name FROM choices;
 
-- SELECT content FROM questions 
 
-- JOIN choices ON questions.question_id = choices.question_id;
SELECT questions.question_id, questions.content AS "Questions",
choices.name AS "Answer Choices" 
FROM choices LEFT OUTER JOIN questions
ON choices.question_id=questions.question_id
WHERE questions.quiz_id=1;
 
 
 
 
 
-- SELECT content FROM questions;
 
-- SELECT * FROM questions WHERE questions.choice_id
-- =choices.choice_id;

schema.sql

-- 1. 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 ... */
 
CREATE TABLE users (
user_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT
);
 
CREATE TABLE quizzes (
quiz_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT
);
 
CREATE TABLE questions (
question_id INTEGER PRIMARY KEY AUTOINCREMENT,
content TEXT,
quiz_id INTEGER REFERENCES quizzes
);
 
CREATE TABLE choices (
choice_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
correct FLOAT,
question_id INTEGER REFERENCES questions
);
 
CREATE TABLE answers (
answer_id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER REFERENCES users,
question_id INTEGER REFERENCES questions,
choice_id INTEGER REFERENCES choices
);

SQL_Joins Homework_(Alex, Mike, Saron, Bana)

The following are our groups' SQL JOIN homework files

stats.sql

SELECT users.name, quizzes.name, (ROUND(COUNT(choices.correct),2)/5)*100
  FROM users
    JOIN choices ON answers.choice_id = choices.choice_id
    JOIN quizzes ON quizzes.quiz_id = questions.quiz_id
    JOIN answers ON answers.user_id = users.user_id
    JOIN questions ON questions.question_id = choices.question_id
      WHERE choices.correct = 1
      GROUP BY users.name, quizzes.quiz_id;

update.sql

UPDATE users
SET name = "Beyonce Yitbarek"
WHERE user_id = 1
;