SAP HANA学习资料中的基础表及数据SQL语句汇总

xuanhao

　　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;