SQL queries

survey_goat

Second, write SQL statements (in your gist) and model methods (in your model files) to accomplish the following:

1. Find all authors with an email address of "[email protected]"
SELECT name
FROM authors
WHERE email = "[email protected]"


2. Find the author who was created most recently
SELECT name, created_at
FROM authors
ORDER BY created_at DESC
LIMIT 1

3. Find the number (count) of each type of question in the database
SELECT question_type, COUNT(*) AS count_duplicate
FROM questions
GROUP BY question_type
ORDER BY COUNT(*) DESC


4. Find the most common answer to question 3 (given a question_id)
SELECT response_text COUNT(*) AS 'response_occurence' 
FROM responses
GROUP BY response_text
ORDER BY COUNT(*) DESC
WHERE question_id = 3


5. Find all survey names, and show their author's email addresses with them
SELECT title. email
FROM surveys LEFT JOIN authors
ON surveys.author_id = authors.id

6. Find all authors who have never created a survey
SELECT name
FROM authors
LEFT JOIN inner surveys
ON surveys.author_id=authors.id