Java2Days Oracle workshop

View All Scripts Login to Run Script

Script Name Java2Days Oracle workshop
Description Tiny database suitable for demonstration purposes. Contains three tables and three sequences - author, book, promotion. Practice by trying to solve these queries: 1. Select the full names of the authors (as one column), for which both email and phone number are empty. 2. Select all books that were published at least 10 years ago and which are ebooks. Order by price, from most expensive to cheapest. 3. Select the author names and book titles of books that have 'fantasy' anywhere in the description of the book, regardless of case. Order by title. 4. What is the price of the most expensive book, the price of the cheapest book and how many books are in the DB? 5. Select the book title, its price and its reduced price for each book for the currently active promotion (the current date is between start and end date). Reduced price = price – (price*(discount/100)) 6. Select the number of books, grouped by currencies, for the USD and BGN currencies 7. Select the authors, who have at least one ebook published 8. Select the number of books written by each author, together with the author names. Order by book count descending.
Area SQL General
Contributor Aneliya Vlaykova
Created Thursday November 17, 2016

Statement 1

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)
)

Table created.

Statement 2

CREATE SEQUENCE author_seq
 START WITH     1
 INCREMENT BY   1
 NOCACHE
 NOCYCLE

Sequence created.

Statement 3

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
)

Table created.

Statement 4

CREATE SEQUENCE book_seq
 START WITH     1
 INCREMENT BY   1
 NOCACHE
 NOCYCLE

Sequence created.

Statement 5

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
)

Table created.

Statement 6

CREATE SEQUENCE promotion_seq
 START WITH     1
 INCREMENT BY   1
 NOCACHE
 NOCYCLE

Sequence created.

Statement 7

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

1 row(s) inserted.

Statement 8

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

1 row(s) inserted.

Statement 9

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

1 row(s) inserted.

Statement 10

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

1 row(s) inserted.

Statement 11

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

1 row(s) inserted.

Statement 12

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

1 row(s) inserted.

Statement 13

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'))

1 row(s) inserted.

Statement 14

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'))

1 row(s) inserted.

Statement 15

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'))

1 row(s) inserted.

Statement 16

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'))

1 row(s) inserted.

Statement 17

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'))

1 row(s) inserted.

Statement 18

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'))

1 row(s) inserted.

Statement 19

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'))

1 row(s) inserted.

Statement 20

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'))

1 row(s) inserted.

Statement 21

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'))

1 row(s) inserted.

Statement 22

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'))

1 row(s) inserted.

Statement 23

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'))

1 row(s) inserted.

Statement 24

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'))

1 row(s) inserted.

Statement 25

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)

1 row(s) inserted.

Statement 26

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)

1 row(s) inserted.

Statement 27

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)

1 row(s) inserted.

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database