CREATE TABLE author (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
name TEXT UNIQUE,
last_name TEXT,
books_sold INTEGER NOT NULL);

INSERT INTO author (name, last_name, books_sold) 
VALUES ("Jakob", "Bandelin", 5000000000);
INSERT INTO author (name, last_name, books_sold) 
VALUES ("Bugs", "Bunny", 50);
INSERT INTO author (name, last_name, books_sold) 
VALUES ("Stefan", "Gefan", 8);
INSERT INTO author (name, last_name, books_sold) 
VALUES ("Kevin", "Macleod", 300000);
INSERT INTO author (name, last_name, books_sold) 
VALUES ("Hugo", "Boss", 400000);
INSERT INTO author (name, last_name, books_sold) 
VALUES ("Carl", "Gustaf", 900000);
INSERT INTO author (name, last_name, books_sold) 
VALUES ("Spoder", "Man", 1000000);
INSERT INTO author (name, last_name, books_sold) 
VALUES ("Dick", "Dick", 1);

CREATE TABLE books (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
author_id INTEGER,
booktitle TEXT UNIQUE NOT NULL,
book_genre TEXT,
FOREIGN KEY(author_id) REFERENCES author(id));

INSERT INTO books (author_id, booktitle, book_genre)
VALUES (1, "SQL is the shit", "educational"); 
INSERT INTO books (author_id, booktitle, book_genre) 
VALUES (2, "Big Chungus", "horror");
INSERT INTO books (author_id, booktitle, book_genre) 
VALUES (2, "What's up", "comedy");
INSERT INTO books (author_id, booktitle, book_genre) 
VALUES (3, "Why mobile games is the best", "educational");
INSERT INTO books (author_id, booktitle, book_genre) 
VALUES (4, "Music of the ages", "historical");
INSERT INTO books (author_id, booktitle, book_genre) 
VALUES (4, "How to create great music", "educational");
INSERT INTO books (author_id, booktitle, book_genre) 
VALUES (5, "How to be big boss", "educational");
INSERT INTO books (author_id, booktitle, book_genre) 
VALUES (6, "Carl Gustav Best King (Måste läsa, annars dö)", "historical");
INSERT INTO books (author_id, booktitle, book_genre) 
VALUES (7, "How to make best memes", "educational");
INSERT INTO books (author_id, booktitle, book_genre) 
VALUES (8, "Not funny, didn't laugh", "comedy");

SELECT * FROM author;

UPDATE author
SET name ="Spider"
WHERE name ="Spoder";
SELECT * FROM author;

DELETE FROM author
WHERE name ="Stefan";
SELECT * FROM author;

CREATE table friends (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    person1_id INTEGER,
    person2_id INTEGER);

INSERT INTO friends (person1_id, person2_id) VALUES (2,7);
INSERT INTO friends (person1_id, person2_id) VALUES (1,6);
INSERT INTO friends (person1_id, person2_id) VALUES (4,5);

SELECT booktitle, name, last_name FROM author JOIN books ON author.id = books.author_id;

SELECT a.name, a. last_name, b.name, b.last_name FROM friends
JOIN author a
ON friends.person1_id = a.id
JOIN author b
ON friends.person2_id = b.id;

SELECT COUNT(author_id),
    CASE
        WHEN author_id = 1 then "Jakob"
        WHEN author_id = 2 then "Bugs"
        WHEN author_id = 4 then "Kevin"
        WHEN author_id = 5 then "Hugo"
        WHEN author_id = 6 then "Carl"
        WHEN author_id = 7 then "Spider"
        WHEN author_id = 8 then "Dick"
        
    END as author

FROM books
GROUP BY author_id