create table author(
   id             NUMBER(6) PRIMARY KEY,
   fname          VARCHAR2(20) NOT NULL,
   lname          VARCHAR2(20) NOT NULL,
   email          VARCHAR2(50),
   phone_number   VARCHAR2(20)
)

CREATE SEQUENCE author_seq
 START WITH     1
 INCREMENT BY   1
 NOCACHE
 NOCYCLE

create table book(
   id             NUMBER(6) PRIMARY KEY, 
   author_id      NUMBER(6) NOT NULL,
   title          VARCHAR2(80) NOT NULL,
   price          NUMBER(8,2) NOT NULL,
   currency       VARCHAR2(3) NOT NULL,
   descr          VARCHAR2(1000),
   is_ebook       NUMBER(1) DEFAULT 0 NOT NULL,
   publish_date   DATE NOT NULL
)

CREATE SEQUENCE book_seq
 START WITH     1
 INCREMENT BY   1
 NOCACHE
 NOCYCLE

create table promotion(
  id NUMBER(6) PRIMARY KEY,
  descr       VARCHAR2(100),
  start_date  DATE NOT NULL,
  end_date    DATE NOT NULL,
  discount    NUMBER(2) NOT NULL
)

CREATE SEQUENCE promotion_seq
 START WITH     1
 INCREMENT BY   1
 NOCACHE
 NOCYCLE

INSERT INTO author
VALUES
  (author_seq.nextval, 'Stephen', 'King', 'stephen@king.com', NULL)

INSERT INTO author
VALUES
  (author_seq.nextval, 'Harper', 'Lee', NULL, NULL)

INSERT INTO author
VALUES
  (author_seq.nextval, 'Jane', 'Austen', NULL, NULL)

INSERT INTO author
VALUES
  (author_seq.nextval, 'J.K.', 'Rowling', NULL, '555-55-55-01')

INSERT INTO author
VALUES
  (author_seq.nextval, 'C.S.', 'Lewis', NULL, NULL)

INSERT INTO author
VALUES
  (author_seq.nextval, 'Aleko', 'Konstantinov', NULL, NULL)

INSERT INTO book
VALUES
  (book_seq.nextval,
   1,
   'Misery',
   12,
   'USD',
   'Misery is a 1987 psychological horror novel by Stephen King. The novel was nominated for the World Fantasy Award for Best Novel in 1988.',
   0,
   to_date('01.01.1990', 'dd.mm.yyyy'))

INSERT INTO book
VALUES
  (book_seq.nextval,
   1,
   'IT',
   30,
   'USD',
   'It is a 1986 horror novel by American author Stephen King. The story follows the exploits of seven children as they are terrorized by an eponymous being.',
   0,
   to_date('01.05.1989', 'dd.mm.yyyy'))

INSERT INTO book
VALUES
  (book_seq.nextval,
   1,
   'The Shining',
   25,
   'USD',
   'The Shining is a horror novel by American author Stephen King. Published in 1977, it is King''s third published novel and first hardback bestseller.',
   0,
   to_date('01.01.1983', 'dd.mm.yyyy'))

INSERT INTO book
VALUES
  (book_seq.nextval,
   2,
   'To Kill a Mockingbird',
   10,
   'USD',
   'To Kill a Mockingbird is a novel by Harper Lee published in 1960. It was immediately successful, winning the Pulitzer Prize.',
   0,
   to_date('01.01.1971', 'dd.mm.yyyy'))

INSERT INTO book
VALUES
  (book_seq.nextval,
   3,
   'Pride and Prejudice',
   22.90,
   'GBP',
   'First published in 1813, Pride and Prejudice has consistently been Jane Austen''s most popular novel.',
   0,
   to_date('01.01.1971', 'dd.mm.yyyy'))

INSERT INTO book
VALUES
  (book_seq.nextval,
   4,
   'Harry Potter and the Order of the Phoenix',
   22.90,
   'GBP',
   'Harry Potter and the Order of the Phoenix is the fifth novel in the Harry Potter series, written by J. K. Rowling.',
   1,
   to_date('21.06.2003', 'dd.mm.yyyy'))

INSERT INTO book
VALUES
  (book_seq.nextval,
   4,
   'Harry Potter and the Philosopher''s Stone',
   18.95,
   'GBP',
   'Harry Potter and the Philosopher''s Stone is the first novel in the Harry Potter series and J. K. Rowling''s debut novel, first published in 1997 by Bloomsbury.',
   1,
   to_date('26.06.1997', 'dd.mm.yyyy'))

INSERT INTO book
VALUES
  (book_seq.nextval,
   5,
   'The Chronicles of Narnia: The Magician''s Nephew',
   8.90,
   'GBP',
   'The Magician''s Nephew is a high fantasy novel for children by C. S. Lewis, published by Bodley Head in 1955.',
   1,
   to_date('21.06.1955', 'dd.mm.yyyy'))

INSERT INTO book
VALUES
  (book_seq.nextval,
   5,
   'The Chronicles of Narnia: The Horse and His Boy',
   7.90,
   'GBP',
   'The Horse and His Boy is a novel for children by C. S. Lewis, published by Geoffrey Bles in 1954. It was the fifth published of seven novels in The Chronicles of Narnia and one of four that Lewis finished writing before the first book was out.',
   1,
   to_date('09.04.1954', 'dd.mm.yyyy'))

INSERT INTO book
VALUES
  (book_seq.nextval,
   5,
   'The Chronicles of Narnia: Prince Caspian',
   5.95,
   'GBP',
   'Prince Caspian is a high fantasy novel for children by C. S. Lewis, published by Geoffrey Bles in 1951.',
   1,
   to_date('05.12.1951', 'dd.mm.yyyy'))

INSERT INTO book
VALUES
  (book_seq.nextval,
   5,
   'The Chronicles of Narnia: The Lion, the Witch and the Wardrobe',
   10.50,
   'GBP',
   'The Lion, the Witch and the Wardrobe is a high fantasy novel for children by C. S. Lewis, published by Geoffrey Bles in 1950. It''s the first published and best known of seven novels in The Chronicles of Narnia.',
   1,
   to_date('21.08.1950', 'dd.mm.yyyy'))

INSERT INTO book
VALUES
  (book_seq.nextval,
   6,
   'Chicago and Back',
   8.50,
   'BGN',
   'Aleko Konstantinov was a Bulgarian writer, best known for his character Bay Ganyo, one of the most popular characters in Bulgarian fiction.',
   1,
   to_date('21.04.1893', 'dd.mm.yyyy'))

INSERT INTO promotion
VALUES
  (promotion_seq.nextval,
   'Easter 2016',
   to_date('01.04.2016', 'dd.mm.yyyy'),
   to_date('06.05.2016', 'dd.mm.yyyy'),
   10)

INSERT INTO promotion
VALUES
  (promotion_seq.nextval,
   'Christmas 2016',
   to_date('15.12.2016', 'dd.mm.yyyy'),
   to_date('10.01.2017', 'dd.mm.yyyy'),
   15)

INSERT INTO promotion
VALUES
  (promotion_seq.nextval,
   'Christmas 2015',
   to_date('15.12.2015', 'dd.mm.yyyy'),
   to_date('10.01.2016', 'dd.mm.yyyy'),
   15)