Homework

gistfile1.txt

Find all authors with an email address of "shakespeare@example.com"

SELECT *
FROM authors
WHERE email= "shakespeare@example.com";


Find the author who was created most recently

SELECT *
FROM authors
ORDER BY created_at DESC
LIMIT 1;


Find the number (count) of each type of question in the database

SELECT question_type, count(*)
FROM questions
GROUP BY question_type
ORDER BY count(*);

Find the most common answer to a particular question (given a question_id)
SELECT response_text, count (*)
FROM responses
WHERE (question_id)= 4
GROUP BY response_text
ORDER BY count(*)

Find all survey names, and show their author's email addresses with them

SELECT surveys.title, authors.email
FROM authors JOIN surveys ON authors.id = surveys.author_id


Find all authors who have never created a survey
SELECT *
FROM authors LEFT JOIN surveys ON authors.id = surveys.author_id
WHERE surveys.id IS NULL
ORDER BY authors.id;