DROP DATABASE IF EXISTS books1;
CREATE SCHEMA books1;
USE books1;

-- Create the authors table
CREATE TABLE authors (
    author_id INT PRIMARY KEY,
    author_name VARCHAR(50) NOT NULL
);

-- Create the books table with a foreign key constraint
CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    author_id INT,
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

-- Insert some sample data
INSERT INTO authors VALUES (1, 'J.R.R. Tolkien');
INSERT INTO authors VALUES (2, 'Mark Twain');

INSERT INTO books VALUES (101, 'The Lord of The Rings', 1);
INSERT INTO books VALUES (102, 'The Adventures of Tom Sawyer', 2);
INSERT INTO books VALUES (103, 'The Hobbit', 1);

INSERT INTO authors VALUES (3, 'Periklis Andritsos');

INSERT INTO books VALUES (104, 'Clustering Techniques', 2);

UPDATE books
SET author_id = 3
WHERE title = 'Clustering Techniques';

-- Cartesian product between two tables
SELECT B.title, A.author_name
FROM books B, authors A;

-- Two queries for doing a natural join between two tables
SELECT A.author_id, B.title
FROM books B, authors A
WHERE B.author_id = A.author_id;

SELECT A.author_name, B.title 
FROM authors A JOIN books B ON B.author_id = A.author_id;

-- LEFT JOIN shows all rows from the table on the left that doesn't participate in the join and adds NULL values 
SELECT A.author_name, B.title 
FROM authors A LEFT JOIN books B ON B.author_id = A.author_id;

-- RIGHT JOIN : similar to the above but for the table that is on the right of the JOIN
SELECT A.author_name, B.title 
FROM authors A RIGHT JOIN books B ON B.author_id = A.author_id;