Kahn - SQL

Question 1

Create the ‘Pop’ playlist; add a query that will select the title of all the songs from the ‘Pop’ genre artists. It should use IN on a nested subquery

CREATE TABLE artists (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    country TEXT,
    genre TEXT);

CREATE TABLE songs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
artist TEXT,
title TEXT);

Answer 1

Create the ‘Pop’ playlist; add a query that will select the title of all the songs from the ‘Pop’ genre artists. It should use IN on a nested subquery

CREATE TABLE artists (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    country TEXT,
    genre TEXT);

CREATE TABLE songs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
artist TEXT,
title TEXT);

SELECT title FROM songs WHERE artist IN (SELECT name FROM artists WHERE genre = ‘Pop’);

Question 2

Find out which exercises in your exercise_logs burns the most calories - nb type = type of exercise eg biking, dancing etc.

CREATE TABLE exercise_logs
    (id INTEGER PRIMARY KEY AUTOINCREMENT,
    type TEXT,
    minutes INTEGER, 
    calories INTEGER,

Answer 2

SELECT type, SUM(calories) AS total_calories FROM exercise_logs GROUP BY type;

// the ‘AS’ creates a new column called total_calories that adds up the calories burned for biking, dancing etc.

Question 3

Find out which exercises in your exercise_logs burns over 150 calories in total - nb type = type of exercise eg biking, dancing etc.

CREATE TABLE exercise_logs
    (id INTEGER PRIMARY KEY AUTOINCREMENT,
    type TEXT,
    minutes INTEGER, 
    calories INTEGER,

Answer 3

SELECT type, SUM(calories) AS total_calories FROM exercise_logs
GROUP BY type
HAVING total_calories > 150;

(nb don’t use where as that will only show each individual row that’s over 150. You want the aggregate so use HAVING. Using HAVING we are applying the conditions to the grouped values, rather than the individual values in the individual rows.)

Question 4

Find the exercises in your exercise_logs where the average calories burned came to over 70 - nb type = type of exercise eg biking, dancing etc.

CREATE TABLE exercise_logs
    (id INTEGER PRIMARY KEY AUTOINCREMENT,
    type TEXT,
    minutes INTEGER, 
    calories INTEGER,

Answer 4

SELECT type, AVG(calories) AS avg_calories FROM exercise_logs
GROUP BY type
HAVING avg_calories > 70;

(nb don’t use where as that will only show each individual row that’s over 150. You want the aggregate so use HAVING. Using HAVING we are applying the conditions to the grouped values, rather than the individual values in the individual rows.)

Question 5

Find the exercises in your exercises_logs table where you did two or more sessions - eg 2 sessions of biking, 3 of dancing etc

CREATE TABLE exercise_logs
    (id INTEGER PRIMARY KEY AUTOINCREMENT,
    type TEXT,
    minutes INTEGER, 
    calories INTEGER,

Answer 5

SELECT type FROM exercise_logs GROUP BY type HAVING COUNT(*) >= 2;

(nb don’t use where as that will only show each individual row that’s over 150. You want the aggregate so use HAVING. Using HAVING we are applying the conditions to the grouped values, rather than the individual values in the individual rows.)

Question 6

We’ve created a database of a few popular authors and their books, with word counts for each book.

Select all the authors who have written more than 1 million words, using GROUP BY and HAVING. Your results table should include the ‘author’ and their total word count as a ‘total_words’ column.

CREATE TABLE books (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    author TEXT,
    title TEXT,
    words INTEGER);

Answer 6

SELECT author, SUM(words) AS total_words FROM books GROUP BY author HAVING total_words > 1000000;

(nb don’t use where as that will only show each individual row that’s over 150. You want the aggregate so use HAVING. Using HAVING we are applying the conditions to the grouped values, rather than the individual values in the individual rows.)

Question 7

We’ve created a database of a few popular authors and their books, with word counts for each book.

Select all the authors that write more than an average of 150,000 words per book. Your results table should include the ‘author’ and average words as an ‘avg_words’ column.

Answer 7

SELECT author, AVG(words) AS avg_words FROM books GROUP BY author HAVING avg_words > 150000

(nb don’t use where as that will only show each individual row that’s over 150. You want the aggregate so use HAVING. Using HAVING we are applying the conditions to the grouped values, rather than the individual values in the individual rows.)

Question 8

Explain CASE in SQL

Answer 8

KAHN VIDEO “Calculating results with CASE”

CASE is a bit like a switch or if statement

SELECT COUNT(*),
CASE
WHEN heart_rate > 220-30 THEN “above max”
WHEN heart_rate > ROUND(0.90 * (220-30)) THEN “above target”
WHEN heart_rate > ROUND(0.50 * (220-30)) THEN “within target”
ELSE “below target”
END as “hr_zone”
FROM exercise_logs
GROUP BY hr_zone;

CASE sets up new groups (or cases) called ‘above max’ and ‘above target’ etc. These hold the heart rate ranges. It then puts them in a new column called hr_zone (END as “hr_zone)

Question 9

Detail the different ways to join tables

Answer 9

/* cross join */
SELECT * FROM student_grades, students;

Just joins the table horizontally, going from left to right
It then has multiple rows depending on how many students there are - eg student rows x student_grades rows

////////////////////

/* implicit inner join */
SELECT * FROM student_grades, students
    WHERE student_grades.student_id = students.id;

Use this when we want to use get a row that matches the student id row. Only selects the id data in the inner table and excludes non-matching values in the outer table

/////////////////////////////////

/* explicit inner join */
SELECT * FROM students
JOIN student_grades
ON students.id = student_grades.student_id;

/* a safer version using WHERE
SELECT students.first_name, students.last_name, students.email, student_grades.test, student_grades.grade FROM students
JOIN student_grades
ON students.id = student_grades.student_id
WHERE grade > 90;