---

IF DB_ID('DB_Books') IS NOT NULL
BEGIN
    ALTER DATABASE DB_Books SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE DB_Books;
END;
GO

CREATE DATABASE DB_Books;
GO

USE DB_Books;
GO

CREATE TABLE Authors
(
    AuthorID INT PRIMARY KEY,
    NameAuthor VARCHAR(50),
    Country VARCHAR(30)
);
GO

CREATE TABLE Publishing_House
(
    Code_publish INT PRIMARY KEY,
    Name_publish VARCHAR(50),
    City VARCHAR(30)
);
GO

CREATE TABLE Books
(
    BookID INT PRIMARY KEY,
    Title VARCHAR(50),
    AuthorID INT,
    Code_publish INT,
    Price INT,
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID),
    FOREIGN KEY (Code_publish) REFERENCES Publishing_House(Code_publish)
);
GO

CREATE TABLE Sales
(
    SaleID INT PRIMARY KEY,
    BookID INT,
    Quantity INT,
    SaleDate DATE,
    FOREIGN KEY (BookID) REFERENCES Books(BookID)
);
GO

INSERT INTO Authors (AuthorID, NameAuthor, Country)
VALUES
(1, 'Pushkin', 'Russia'),
(2, 'Tolstoy', 'Russia'),
(3, 'Rowling', 'UK');
GO

INSERT INTO Publishing_House (Code_publish, Name_publish, City)
VALUES
(1, 'AST', 'Moscow'),
(2, 'Eksmo', 'Moscow'),
(3, 'Bloomsbury', 'London');
GO

INSERT INTO Books (BookID, Title, AuthorID, Code_publish, Price)
VALUES
(1, 'Eugene Onegin', 1, 1, 500),
(2, 'War and Peace', 2, 2, 700),
(3, 'Harry Potter', 3, 3, 900);
GO

INSERT INTO Sales (SaleID, BookID, Quantity, SaleDate)
VALUES
(1, 1, 5, '2025-01-10'),
(2, 2, 3, '2025-02-15'),
(3, 3, 8, '2025-03-20');
GO

SELECT * FROM Authors;
SELECT * FROM Publishing_House;
SELECT * FROM Books;
SELECT * FROM Sales;
GO