DROP SCHEMA SqlScriptDocumentation CASCADE;
CREATE SCHEMA SqlScriptDocumentation;
ALTER SESSION SET CURRENT_SCHEMA = SqlScriptDocumentation;
--test data
CREATE COLUMN TABLE publishers( pub_id INTEGER PRIMARY KEY, name VARCHAR(50), street VARCHAR(50), post_code VARCHAR(10), city VARCHAR(50), country VARCHAR(50));
insert into publishers VALUES ( 1, 'Oldenburg Wissenschaftsverlag GmbH', 'Rosenheimer Strasse 145', '81671', 'Muenchen', 'Germany');
insert into publishers VALUES ( 2, 'Pearson Education Deutschland GmbH', 'Martin-Kollar-Strasse 10-12', '81829', 'Muenchen', 'Germany');
insert into publishers VALUES ( 3, 'mitp & bhv-Buch', 'Augustinusstrasse 9d', '50226', 'Frechen', 'Germany');
insert into publishers VALUES ( 4, 'Roof Music', 'Prinz-Regent-Strasse 50-60', '44795', 'Bochum', 'Germany');
CREATE COLUMN TABLE books( isbn VARCHAR(20) PRIMARY KEY, title VARCHAR(50), publisher INTEGER, edition INTEGER, year VARCHAR(4), price DECIMAL(5, 2), crcy VARCHAR(3));
insert into books VALUES ('978-3-486-57690-0', 'Datenbanksysteme: Eine Einfuehrung', 1, 6, '2006', '39.80', 'EUR');
insert into books VALUES ('978-3-86894-012-1', 'Grundlagen von Datenbanken', 2, 3, '2009', '29.95', 'EUR');
insert into books VALUES ('978-3-8266-1664-8', 'Datenbanken: Konzepte und Sprachen', 3, 3, '2008', '39.95', 'EUR');
CREATE COLUMN TABLE audiobooks( isbn VARCHAR(20) PRIMARY KEY, title VARCHAR(50), publisher INTEGER, year VARCHAR(4), price DECIMAL(5, 2), crcy VARCHAR(3));
insert into audiobooks VALUES ('978-39388781-37-1', 'Ich bin dann mal weg', 4, '2006', '24.90', 'EUR');
--output tables
CREATE TABLE op_publishers( publisher INTEGER, name VARCHAR(50), price DECIMAL, cnt INTEGER);
CREATE TABLE op_years( year VARCHAR(4), price DECIMAL, cnt INTEGER);
CREATE COLUMN TABLE op_audiobooks1( isbn VARCHAR(20) PRIMARY KEY, title VARCHAR(50), publisher INTEGER, year VARCHAR(4), price DECIMAL(5, 2), crcy VARCHAR(3));
CREATE COLUMN TABLE op_audiobooks2( isbn VARCHAR(20) PRIMARY KEY, title VARCHAR(50), publisher INTEGER, year VARCHAR(4), price DECIMAL(5, 2), crcy VARCHAR(3));
CREATE TABLE op_proj_books( title VARCHAR(50), price DECIMAL(5, 2), price_vat DECIMAL(5, 2), currency VARCHAR(3));
CREATE TABLE op_colt_books( title VARCHAR(50), price DECIMAL(5, 2), price_vat DECIMAL(5, 2), crcy VARCHAR(3));
CREATE TABLE op_colt_books1( title VARCHAR(50), price DECIMAL(5, 2), crcy VARCHAR(3));
CREATE COLUMN TABLE op_books( isbn VARCHAR(20) PRIMARY KEY, title VARCHAR(50), publisher INTEGER, edition INTEGER, year VARCHAR(4), price DECIMAL(5, 2), crcy VARCHAR(3));
CREATE TABLE op_join_books_pubs( publisher INTEGER, name VARCHAR(50), street VARCHAR(50), post_code VARCHAR(10), city VARCHAR(50), country VARCHAR(50), isbn VARCHAR(20), title VARCHAR(50), edition INTEGER, year VARCHAR(4), price DECIMAL(5, 2), crcy VARCHAR(3));
CREATE TABLE op_join_books_pubs2( publisher INTEGER, name VARCHAR(50), street VARCHAR(50), post_code VARCHAR(10), city VARCHAR(50), country VARCHAR(50), isbn VARCHAR(20), title VARCHAR(50), edition INTEGER, year VARCHAR(4), price DECIMAL(5, 2), crcy VARCHAR(3));
CREATE TABLE op_joinp_books_pubs1( title VARCHAR(50), name VARCHAR(50), publisher INTEGER, year VARCHAR(4));
CREATE TABLE op_joinp_books_pubs2( title VARCHAR(50), name VARCHAR(50), publisher INTEGER, year VARCHAR(4));
CREATE TABLE op_agg_books1( publisher INTEGER, year VARCHAR(4));
CREATE TABLE op_agg_books2( publisher INTEGER, year VARCHAR(4));
CREATE COLUMN TABLE books1( isbn VARCHAR(20) PRIMARY KEY, title VARCHAR(50), publisher INTEGER, edition INTEGER, year VARCHAR(4), price DECIMAL(5, 2), crcy VARCHAR(3));
CREATE INSERT ONLY COLUMN TABLE op_sales_books( title VARCHAR(50), price DECIMAL(5, 2), crcy VARCHAR(3));
-- drop table type
DROP TYPE tt_publishers CASCADE;
DROP TYPE tt_years CASCADE;
-- create table type
CREATE TYPE tt_publishers AS TABLE ( publisher INTEGER, name VARCHAR(50), price DECIMAL, cnt INTEGER);
CREATE TYPE tt_years AS TABLE ( year VARCHAR(4), price DECIMAL, cnt INTEGER);
CREATE TYPE tt_sales_books AS TABLE ( title VARCHAR(50), price DECIMAL(5, 2), crcy VARCHAR(3));
CREATE TYPE tt_agg_books AS TABLE ( publisher INTEGER, year VARCHAR(4));
CREATE TYPE tt_proj_books AS TABLE ( title VARCHAR(50), price DECIMAL(5, 2), price_vat DECIMAL(5, 2), currency VARCHAR(3));
CREATE TYPE tt_colt_books AS TABLE ( title VARCHAR(50), price DECIMAL(5, 2), price_vat DECIMAL(5, 2), crcy VARCHAR(3));
CREATE TYPE tt_join_books_pubs AS TABLE ( publisher INTEGER, name VARCHAR(50), street VARCHAR(50), post_code VARCHAR(10), city VARCHAR(50), country VARCHAR(50), isbn VARCHAR(20), title VARCHAR(50), edition INTEGER, year VARCHAR(4), price DECIMAL(5, 2), crcy VARCHAR(3));
-- tracing for procedures
drop table message_box;
CREATE TABLE message_box(
message VARCHAR(200),
log_time TIMESTAMP
);
drop procedure ins_msg_proc;
CREATE PROCEDURE ins_msg_proc (p_msg VARCHAR(200)) LANGUAGE SQLSCRIPT AS
BEGIN
INSERT INTO message_box VALUES (:p_msg, SYSTIMESTAMP);
END;
drop procedure init_proc;
CREATE PROCEDURE init_proc LANGUAGE SQLSCRIPT AS
BEGIN
DELETE FROM message_box;
END;
