-- SQL quiz

-- This is a valid SQL file that you will have to modify according to
-- instructions given below.
-- Duration: 55 minutes

/*

Prior to answering questions:

1. Start MoWeS
2. Enter localhost in your browser
3. Open PhpMyAdmin
4. Create a new database called 'exam'
5. Copy this file to your computer
6. Open the file with an editor of your choice
7. Paste the whole contents of this file into PhpMyAdmin (click on the
   SQL tab) or alternatively import the file directly with the import TAB.
8. You now should see 5 tables, some containing data...
9. Answer the three questions below by editing this file
10. Test one last time and submit (question four)

Remark: You can start solving the problems on paper and then commit
the modifications to the file later. Paper-only copies are accepted,
but you will loose 20 points.

Tips:

- Each SQL statement ends with a ; 
- Watch out for quotes (both straight double or single quotes must match)
- Lists are enclosed in parenthesis and are separated by commas
  (do not add a comma after the last item !)
- DO NOT DELETE ANY LINE. In particular, pay attention to keep the
  openings/closings of the c-style slash-and-asterisk comments
- This is simple SQL (e.g. no foreign keys). Keep it simple and just
  stick to the questions....
*/

/* Initial table definitions for the exam database */

CREATE TABLE IF NOT EXISTS survey (
  id int(11) NOT NULL AUTO_INCREMENT,
  survey_name varchar (60),
  description text,
  PRIMARY KEY (id)         
  );

CREATE TABLE IF NOT EXISTS question (
  id int(11) NOT NULL AUTO_INCREMENT,
  description text,                     /* Survey question text */ 
                  /* Missing field, see Exam Question 1 below */

  id_survey int (11),
  PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS response_item (
  id int(11) NOT NULL AUTO_INCREMENT,
  response_item text,                  /* Text of the response item */
  is_correct boolean DEFAULT FALSE,    /* TRUE or FALSE */
  feedback_text text,                  /* Provides the feedback for each item */
  score int DEFAULT '0',               /* A number between 0 and 100 */
  id_question int (11),
  PRIMARY KEY (id)
);



/* **********************************************************************
EXAM Question 1
10/100 points

As it stands now, questions are not sorted, i.e. they would be
displayed in random order.  A questionnaire designer ought to be able
to specify a sort_order.

Task: Create a field in the appropriate table that will allow to store
this information.  Its value can be null.
*/




/* **********************************************************************
EXAM Question set 2
40/100 points

The series of INSERT statements below will create four questions and
3-4 associated response items for each. Syntactically speaking, these
statements work just fine. However, there are many mistakes in the
contents of the response items (response_item table).

Task: Please fix the response items. 

*/ 

/* Leave as is, will create a survey with id = 1 */
INSERT INTO survey VALUES (1, "mid term coap 3080 spring 2010", "An exam ..."); 

/* 
Q2a - Fix column 3, 4 and 5 values, i.e. TRUE/FALSE, feedback and the percentage obtained
Tip: Examine the table structure defined above if needed
*/

INSERT INTO question VALUES (1, "What does SQL stand for ?", 1);
INSERT INTO response_item VALUES
       (NULL, "Structured Question Language", TRUE, "Good answer", 100, 1);
INSERT INTO response_item VALUES 
       (NULL, "Standard Query Language", FALSE, "Bad answer", 0, 1);
INSERT INTO response_item VALUES 
       (NULL, "Strong Query Language", FALSE, "Bad answer",0, 1);
INSERT INTO response_item VALUES 
       (NULL, "Structured Query Language", FALSE, "Bad answer", 0, 1);

/* 
Q2b - As above
Remark: we use a different INSERT syntax here and don't enter values for
feedback.
*/

INSERT INTO question VALUES (2, "Which SQL statement is used to update/modify data ?", 1);
INSERT INTO response_item (id, response_item, is_correct, score, id_question) 
       VALUES (NULL, "MODIFY", FALSE, 0, 2);
INSERT INTO response_item (id, response_item, is_correct, score, id_question) 
       VALUES (NULL, "SAVE", TRUE, 100, 2);
INSERT INTO response_item (id, response_item, is_correct, score, id_question) 
       VALUES (NULL, "UPDATE", FALSE, 0, 2);
INSERT INTO response_item (id, response_item, is_correct, score, id_question) 
       VALUES (NULL, "MODIFY", FALSE, 0, 2);


/* 
Q2c - As above 
*/

INSERT INTO question VALUES (3, "What does the SQL FROM clause do ?", 1);
INSERT INTO response_item (id, response_item, is_correct, score, id_question) 
       VALUES (NULL, "Specifies the columns we are retrieving", FALSE, 0, 3);
INSERT INTO response_item (id, response_item, is_correct, score, id_question) 
       VALUES (NULL, "Specifies the tables to retrieve from", FALSE, 0, 3);
INSERT INTO response_item (id, response_item, is_correct, score, id_question) 
       VALUES (NULL, "Specifies a search condition", FALSE, 0, 3);

/* 
Q2d - As above 
For readability, below are just the response items extracted from the SQL code:
	UPDATE Universities SET Name='Webster' INTO Name='Webster Geneva'
	MODIFY Universities SET Name='Webster' INTO Name='Webster Geneva'
	UPDATE Universities SET Name='Webster Geneva' WHERE Name='Webster'
	MODIFY Universities SET Name='Webster Geneva' WHERE Name='Webster'
*/

INSERT INTO question 
       VALUES (4, "How could you change 'Webster' into 'Webster Geneva' in the Universities table ?", 1);

INSERT INTO response_item (id, response_item, is_correct, score, id_question) 
       VALUES (NULL, 
              "UPDATE Universities SET Name='Webster' INTO Name='Webster Geneva'", 
       FALSE, 0, 4);
INSERT INTO response_item (id, response_item, is_correct, score, id_question) 
       VALUES (NULL, 
              "MODIFY Universities SET Name='Webster' INTO Name='Webster Geneva'", 
       TRUE, 100, 4);
INSERT INTO response_item (id, response_item, is_correct, score, id_question) 
       VALUES (NULL, 
              "UPDATE Universities SET Name='Webster Geneva' WHERE Name='Webster'", 
       FALSE, 0, 4);
INSERT INTO response_item (id, response_item, is_correct, score, id_question) 
       VALUES (NULL, 
              "MODIFY Universities SET Name='Webster Geneva' WHERE Name='Webster'",
       FALSE, 0, 4);



/* **********************************************************************
EXAM Question 3
30/100 points

This database design is too simple, we forgot to think about
registering responses.

You would want another table called survey_responses (id, Name). Each
attempt to answer the survey will translate to a new entry,
e.g. ResponseID=1, Name="Joe Hill".

Another table will be needed to register all the user's answers. We
could call it response_answers (id, survey_id, response_id,
question_id, answer_id). 
* survey_id is a foreign key for survey.id,
* response_id for survey_responses.id, 
* question_id for question.id
* answer_id for response_item.id (i.e. the answer a given user picked).

Remark: You don't need to declare foreign keys, just make sure that these
fields are correctly defined. E.g. the "id" field in the survey table must
take the same values as the survey_id field in the new response_answers table.

Task: PLEASE complete the two tables below, i.e. define appropriate fields.
*/

CREATE TABLE IF NOT EXISTS survey_responses (
  id int(11) NOT NULL AUTO_INCREMENT,

  PRIMARY KEY (id)         
);

CREATE TABLE IF NOT EXISTS response_answers (
  id int(11) NOT NULL AUTO_INCREMENT,




  PRIMARY KEY (id)         
);


/* **********************************************************************
EXAM Question 4

Not actually a question, but you will earn 20 points for submitting a
correct electronic version of this file (no SQL errors). In other
words: you may just turn in paper copy of this file with annotations
made by hand, but you will loose 20 points.

To turn in your exam:

1. Make sure to upload this whole file to your database (to test correctness)
2. Save the file
3. Submit this SQL file to the Worldclassroom and DOUBLE-CHECK with the instructor if the upload went ok.

*/