SQL запити

%load_ext sql
%sql mysql://root@localhost

%%sql 
DROP DATABASE IF EXISTS test;

%sql SHOW DATABASES;

%sql CREATE DATABASE test;

%sql SHOW DATABASES;

%sql mysql://root@localhost/test

%%sql 
CREATE TABLE `books` (
    `id` int NOT NULL AUTO_INCREMENT,
    `category_id` int DEFAULT NULL,
    `title` text,
    `year` smallint DEFAULT NULL,
    PRIMARY KEY (`id`)
);

%%sql

CREATE TABLE `authors` (
    `id` int NOT NULL AUTO_INCREMENT,
    `name` text,
    PRIMARY KEY (`id`)
);

CREATE TABLE `books_authors` (
    `book_id` int NOT NULL,
    `author_id` int NOT NULL
);

CREATE TABLE `categories` (
    `id` int NOT NULL AUTO_INCREMENT,
    `name` text,
    PRIMARY KEY (`id`)
);

%sql SHOW TABLES

%%sql

ALTER TABLE `books_authors` ADD CONSTRAINT books_book_id
FOREIGN KEY (`book_id`) REFERENCES `books` (`id`);

ALTER TABLE `books_authors` ADD CONSTRAINT authors_author_id
FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`);

ALTER TABLE `books` ADD CONSTRAINT categories_category_id
FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`);

%%sql

INSERT INTO `authors` (`name`)
VALUES
    ('Author Name 1'),
    ('Author Name 2'),
    ('Author Name 3'),
    ('Author Name 4'),
    ('Author Name 5');

INSERT INTO `categories` (`name`)
VALUES
    ('Fiction'),
    ('Non-Fiction'),
    ('Science Fiction'),
    ('Mystery'),
    ('Romance');

INSERT INTO `books` (`category_id`, `title`, `year`)
VALUES
    (1, 'Book Title 1', 2022),
    (2, 'Book Title 2', 2019),
    (1, 'Book Title 3', 2020),
    (3, 'Book Title 4', 2021),
    (2, 'Book Title 5', 2018),
    (3, 'Book Title 6', 2017),
    (1, 'Book Title 7', 2016),
    (2, 'Book Title 8', 2015),
    (3, 'Book Title 9', 2014),
    (1, 'Book Title 10', 2013);

INSERT INTO `books_authors` (`book_id`, `author_id`)
VALUES
    (1, 1),
    (1, 2),
    (2, 2),
    (3, 3),
    (4, 4),
    (5, 5),
    (6, 1),
    (7, 2),
    (8, 3),
    (9, 4);

%%sql
SELECT * FROM books;

%%sql
SELECT
    b.title AS book_title,
    a.name AS author_name
FROM
    books_authors ba
JOIN
    books b ON ba.book_id = b.id
JOIN
    authors a ON ba.author_id = a.id;

%%sql
SELECT * FROM books;

%%sql
DELETE FROM `books` WHERE `id` = 1;

%%sql
DELETE FROM `books_authors` WHERE `book_id` = 1;
DELETE FROM `books` WHERE  `id` = 1;

%%sql
SELECT * FROM books;

%%sql
SELECT * FROM books WHERE id = 2;

%%sql
UPDATE books SET category_id = 4 WHERE id = 2;

%%sql
SELECT * FROM books WHERE id = 2;

%%sql
SELECT * FROM books;

%%sql 
SELECT title, year FROM books;

%%sql 
SELECT * FROM books WHERE year > 2019;

%%sql 
SELECT * FROM books ORDER BY year DESC;

%%sql 
SELECT category_id, COUNT(*) AS book_count FROM books
GROUP BY category_id;

%%sql
SELECT
    books.id AS book_id,
    books.title AS book_title,
    books.year,
    authors.id AS author_id,
    authors.name AS author_name,
    categories.id AS category_id,
    categories.name AS category_name
FROM
    books
JOIN
    books_authors ON books.id = books_authors.book_id
JOIN
    authors ON books_authors.author_id = authors.id
LEFT JOIN
    categories ON books.category_id = categories.id;