/*

SurveySez 10 Test SQL Statements

Jennifer Multari

Examples:

#Returns all questions for the first survey:
select q.QuestionID, q.Question from sm11_questions q inner join sm11_surveys s on s.SurveyID = q.SurveyID where s.SurveyID = 1

select Title, Description from fl11_surveys Where SurveyID = 1
(this will show the Title and Description of the first survey)

select q.QuestionID, q.Question from fl11_questions q inner join fl11_surveys s on s.SurveyID = q.SurveyID where s.SurveyID = 1
(this will show all the questions for the first survey) 
*/

#1. Return all answers for question 3
SELECT a.AnswerID, a.Answer 
FROM fl11_answers a inner join fl11_questions q on q.QuestionID = a.QuestionID where q.QuestionID = 3


#2. Return the selected answer for Question 2
SELECT a.AnswerID, a.Answer
FROM fl11_responses_answers ra, fl11_answers a
WHERE ra.AnswerID = a.AnswerID
	AND ra.QuestionID=2;

#3. Return all the questions and selected answers for survey 1
SELECT q.Question, a.Answer
FROM fl11_responses_answers ra, fl11_answers a, fl11_questions q, fl11_surveys s
WHERE ra.AnswerID = a.AnswerID
	AND a.QuestionID = q.QuestionID
	AND q.SurveyID = s.SurveyID
	AND s.SurveyID=1;

#4. Return all questions with an answer of "Yes"
SELECT q.Question
FROM fl11_questions q, fl11_answers a
WHERE q.QuestionID = a.QuestionID
	AND a.Answer = "Yes";

#5. Return number of responses with butterscotch as a selected answer
Select RQID, count(*) as NumButter
FROM fl11_responses_answers ra, fl11_answers a
WHERE ra.AnswerID = a.AnswerID
AND a.Answer="Butterscotch";

#6. Return Answer iD and Answer text together with a heading
SELECT Concat_WS (' ', AnswerID, Answer) as "Answer Info" from fl11_answers;

#7. Return the number of answers grouped by answers
SELECT Answer, count(*) as NumAnswers
FROM fl11_answers
GROUPED BY Answer;

#RUNNING OUT OF IDEAS!!!…..

#8. Return the first answer grouped by question ID
SELECT a.Answer
FROM fl11_answers a
GROUP BY QuestionID;

#9. Return number of responses for survey 1
SELECT SurveyID, count(*) as NumResponses
FROM fl11_responses
WHERE SurveyID=1;

#10. Return the number of selected "Yes" answers against number of all "Yes" answers
SELECT count(*) as SelectedYes, count(*) as AllYes
FROM fl11_responses_answers ra, fl11_answers a
WHERE ra.AnswerID = a.AnswerID
AND a.Answer="Yes";
##This one is not working quite right. Returning "1" for both counts.##