最具影响力的数字化技术在线社区

168大数据

 找回密码
 立即注册

QQ登录

只需一步,快速开始

1 2 3 4 5
开启左侧

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

[复制链接]
发表于 2014-12-4 11:01:01 | 显示全部楼层 |阅读模式

马上注册,结交更多数据大咖,获取更多知识干货,轻松玩转大数据

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
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;


楼主热帖
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

关闭

站长推荐上一条 /1 下一条

关于我们|小黑屋|Archiver|168大数据 ( 京ICP备14035423号|申请友情链接

GMT+8, 2024-3-19 19:31

Powered by BI168大数据社区

© 2012-2014 168大数据

快速回复 返回顶部 返回列表