Neoview SQL Reference Manual (R2.4)

CREATE VIEW SCH.T1_VIEW1 AS
SELECT SCH.T1.C1, SCH.T1.C2 FROM SCH.T1 WHERE SCH.C1 > 0;
• SHOWDDL on several objects with PRIVILEGES option:
-- add some schema level privileges
Grant select on schema sch to “role_reader”;
-- create some tables and their dependent objects:
create table empl
(empl_no int not null primary key,
empl_name char(50) not null,
empl_address varchar(50) not null,
dept_no int not null,
salary numeric (10,2) default 1000
)
hash partition by (empl_no)
max table size 2000;
create index empl_dept_no on empl (dept_no)
hash partition by (dept_no);
create mv empl_mv
refresh on request
initialize on refresh
store by (dept_no)
hash partition by (dept_no)
as select max(salary) as salary, dept_no
from empl
group by dept_no;
alter table empl add constraint empl_salary_check check (salary >= 1000);
alter table empl add constraint empl_dept_no_check check (dept_no > 0);
alter table empl add constraint empl_unique unique (empl_name, empl_address);
create table dept
( dept_no int not null primary key,
dept_name varchar (20) not null )
hash partition by (dept_no);
CREATE TRIGGER neo.sch.dept_trig AFTER UPDATE
ON neo.sch.empl
REFERENCING OLD AS oldrow, NEW AS newrow
FOR EACH ROW
WHEN (oldrow.dept_no <> newrow.dept_no)
INSERT INTO neo.sch.dept VALUES (oldrow.empl_no, 'new dept');
grant select on table empl to public;
grant delete on table empl to “role_user1” with grant option;
grant references (c2) on table empl to “role_user4”;
-- perform showddl on EMPL
SHOWDDL EMPL, PRIVILEGES
-- Schema level privileges
-- GRANT SELECT ON SCHEMA SCH TO “ROLE.READER”;
CREATE TABLE SCH.EMPL
(
EMPL_NO INT NO DEFAULT -- NOT NULL
, EMPL_NAME CHAR(50) CHARACTER SET ISO88591 NO DEFAULT -- NOT NULL
, EMPL_ADDRESS VARCHAR(50) CHARACTER SET ISO88591 NO DEFAULT -- NOT NULL
, DEPT_NO INT NO DEFAULT -- NOT NULL
, SALARY NUMERIC(10, 2) DEFAULT 1000.00
, CONSTRAINT SCH.EMPL_836528881_1454 PRIMARY KEY (EMPL_NO ASC)
, CONSTRAINT SCH.EMPL_314277881_1454 CHECK (SCH.EMPL.EMPL_NO IS NOT
NULL AND SCH.EMPL.EMPL_NAME IS NOT NULL AND SCH.EMPL.EMPL_ADDRESS
IS NOT NULL AND SCH.EMPL.DEPT_NO IS NOT NULL)
)
HASH PARTITION BY (EMPL_NO)
MAX TABLE SIZE 2000
;
CREATE INDEX EMPL_DEPT_NO ON SCH.EMPL
(
DEPT_NO ASC
)
HASH PARTITION (DEPT_NO)
;
ALTER TABLE SCH.EMPL
ADD CONSTRAINT SCH.EMPL_SALARY_CHECK CHECK (SCH.EMPL.SALARY >= 1000);
ALTER TABLE SCH.EMPL
ADD CONSTRAINT SCH.EMPL_DEPT_NO_CHECK CHECK (SCH.EMPL.DEPT_NO > 0);
CREATE TRIGGER SCH.DEPT_TRIG AFTER UPDATE ON SCH.EMPL REFERENCING OLD
AS OLDROW, NEW AS NEWROW FOR EACH ROW WHEN (OLDROW.DEPT_NO <> NEWROW.DEPT_NO)
INSERT INTO SCH.DEPT VALUES (OLDROW.EMPL_NO, 'new dept');
-- Table has an IUD log.
GRANT SELECT ON TABLE SCH.EMPL TO PUBLIC;
GRANT DELETE ON TABLE SCH.EMPL TO “role_user1” WITH GRANT OPTION;
GRANT REFERENCES (C2) ON TABLE SCH.EMPL TO “role_user4”;
194 SQL Statements