PRAGMA foreign_key=ON;
DROP TABLE IF EXISTS Person;
DROP TABLE IF EXISTS CD;
DROP TABLE IF EXISTS Genre;

CREATE TABLE CD (
    CDID integer PRIMARY KEY AUTOINCREMENT UNIQUE,
    Titel varchar(20),
    GenreID integer,
    Eigentuemer int,
    Besitzer int,
    CONSTRAINT fk_CD_Genre FOREIGN KEY (GenreID) REFERENCES Genre(Titel),
    CONSTRAINT fk_CD_Eigentuemer_Person FOREIGN KEY (Eigentuemer) REFERENCES Person(PersonID),
    CONSTRAINT fk_CD_Besitzer_Person FOREIGN KEY (Besitzer) REFERENCES Person(PersonID)
);

CREATE TABLE Genre (
    GenreID integer PRIMARY KEY AUTOINCREMENT UNIQUE,
    GenreTitel varchar(20)
);

CREATE TABLE Person(
    PersonID integer PRIMARY KEY AUTOINCREMENT UNIQUE,
    Name varchar(20),
    Vorname varchar(20)
);

INSERT INTO Genre (GenreTitel) Values("ROCK");

INSERT INTO Person (Name,Vorname) VALUES("Doe","Jane");

INSERT INTO CD (Titel, GenreID,Eigentuemer,Besitzer)
VALUES ('RockCD',(SELECT GenreID FROM Genre WHERE GenreTitel like 'ROCK'),(SELECT PersonID FROM Person WHERE Name like 'Doe' AND Vorname like 'JANE'),(SELECT PersonID FROM Person WHERE Name like 'Doe' AND Vorname like 'JANE'));

Select * From CD;

SELECT CDID, Titel, Genre.GenreTitel as GenreTitel
FROM   CD JOIN Genre Using (GenreID);

SELECT
CDID,
Titel,
Person.Name as EigentuemerName
FROM   CD
INNER JOIN Person ON Person.PersonID = Eigentuemer;