SQL snippets creating, reading and writing to tables

--SQL script for creating, reading and writing tables in SQLite database

--creating the tables 'author' and 'project' (database first)
CREATE TABLE IF NOT EXISTS "Author" (
  "author_id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "first_name" VARCHAR(30) NOT NULL,
  "last_name" VARCHAR(50) NOT NULL,
  "middle_names" VARCHAR(100) NOT NULL,
  "pseudonym" VARCHAR(100) UNIQUE,
  "contact" VARCHAR(100) NOT NULL
);

CREATE TABLE IF NOT EXISTS "Project" (
  "project_id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "author_id" INTEGER NOT NULL REFERENCES "Author" ("author_id") ON DELETE CASCADE,
  "title" VARCHAR(200) UNIQUE NOT NULL,
  "genre" VARCHAR(50) NOT NULL,
  "description" TEXT NOT NULL,
  "creation_time" DATETIME NOT NULL,
  "change_time" DATETIME NOT NULL,
  "deleted" INTEGER NOT NULL
);

CREATE INDEX "idx_project__author_id" ON "Project" ("author_id");

-- inserting new entry in table 'author'
INSERT INTO author(
    first_name,
    middle_names,
    last_name
)
VALUES(
    "Johann",
    "Wolfgang",
    "von Goethe"
);

#retrieving the entry WITH LAST name 'von Goethe' FROM TABLE 'author'
SELECT *
FROM author
WHERE author.last_name = "von Goethe";

#select ALL projects BY Goethe
SELECT *
FROM project
INNER JOIN author
ON (author.author_id = project.author_id)
ORDER BY (project.title);

#update Goethes LAST name AND remove the 'von'
UPDATE author
SET last_name = "Goethe"
WHERE last_name = "von Goethe";

#delete Goethe FROM author TABLE
DELETE FROM author
WHERE first_name = "Johann" AND middle_names = "Wolfgang" AND last_name = "Goethe"