﻿drop schema public;

create table owners(
 id int not null primary key,
 name varchar(100)
 );
create table accounts(
 id int not null,
 accountname varchar(100),
 accountnr int,
 accounttype int,
 ownerid int,
 constraint pk_accounts primary key(id),
 constraint fk_accounts foreign key (ownerid) references owners(id)
 );
create table credits(
 id int,
 accountid int,
 amount float,
 constraint fk_credits foreign key (accountid) references accounts(id) on update cascade,
 constraint ck_credits check(amount is not null)
 );


insert into owners values(1,'John');
insert into owners values(2,n'Mary');
insert into owners values(3,N'Jane');

insert into accounts values(1,'Bank',5101,0,1);
insert into accounts values(2,'Cash',5202,0,1);
insert into accounts values(3,'Giro',5303,0,2);
insert into accounts values(4,'Invoice',7505,1,1);

insert into credits values(1,1,10007777770000000000000);
insert into credits values(2,1,2000);
insert into credits values(3,2,100);
insert into credits values(4,2,200);

select * from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS;


/*r
 John,3
 Mary,1
*/select own.name,count(ac.id) from owners own
 inner join accounts ac on own.id = ac.ownerid
 group by own.id;

/*e*/alter table accounts drop constraint pk_accounts;

alter table accounts drop constraint pk_accounts cascade;

alter table accounts alter column accountname set data type varchar(200);

/*r
 John,3
 Mary,1
*/select own.name,count(ac.id) from owners own
 inner join accounts ac on own.id = ac.ownerid
 group by own.id;
 
alter table accounts drop constraint fk_accounts;

/*r
 John,3
 Mary,1
*/select own.name,count(ac.id) from owners own
 inner join accounts ac on own.id = ac.ownerid
 group by own.id;
 
 
/*e*/insert into credits values(1,1,null);
alter table credits drop constraint ck_credits;
/*u1*/insert into credits values(1,1,1990);



CREATE TABLE PRODUCT(ID INTEGER PRIMARY KEY,NAME VARCHAR(20),COST DECIMAL(10,4));
/*u1*/INSERT INTO PRODUCT VALUES(0,'Iron',5.4);
/*u1*/INSERT INTO PRODUCT VALUES(1,'Wood',24.8);
/*u1*/INSERT INTO PRODUCT VALUES(2,'Fire',24.8);

/*r0*/select id from product p where unique(select p2.cost from product p2 where p.cost = p2.cost);

/*u1*/INSERT INTO PRODUCT VALUES(3,'Wood',50.8);
/*u1*/INSERT INTO PRODUCT VALUES(4,'Fire',26.8);

commit;

CREATE TABLE PRODUCT2(ID INTEGER PRIMARY KEY,NAME VARCHAR(20),COST DECIMAL(10,4))
/*u1*/INSERT INTO PRODUCT2 VALUES(0,'Iron',5.4);
/*u1*/INSERT INTO PRODUCT2 VALUES(1,'Wood',24.8);
/*u1*/INSERT INTO PRODUCT2 VALUES(9,'Cream',50.8);

commit;

/*r
 Fire
*/select name from product
 except
 select name from product2;

/*r
 Fire  
 Fire
 Wood
*/select name from product
 except all
 select name from product2;
 
/*u1*/INSERT INTO PRODUCT2 VALUES(10,'Wood',29.8);
 
/*r
 Iron
 Wood
*/select name from product
 intersect
 select name from product2;
 
/*r
 Iron  
 Wood
 Wood
*/select name from product
 intersect all
 select name from product2;
 
create index idx1 on product(name desc, cost);

/*u1*/delete from product where name='Wood' and cost=50.8;

commit;

/*r 
 0
 1
 2
 4
*/select id from product order by id;



/*rTrue*/select ( DATE '2008-08-08', DATE '2008-10-08') 
 OVERLAPS  ( DATE '2008-10-07', DATE '2009-10-08') from dual;
 
/*rFalse*/select ( DATE '2008-08-08', DATE '2008-10-08') 
 OVERLAPS  ( DATE '2009-10-07', DATE '2009-10-08') from dual;
 
/*rTrue*/select ( DATE '2008-08-08', interval '3' MONTH) 
 OVERLAPS  ( DATE '2008-10-07', DATE '2009-10-08') from dual;
 
/*rTrue*/select NOT (12 > 56) from dual;
/*rFalse*/select NOT (12 < 56) from dual;

drop table PRODUCT;

CREATE TABLE PRODUCT(ID INTEGER PRIMARY KEY,NAME VARCHAR(20),COST DECIMAL(10,4));
/*u1*/INSERT INTO PRODUCT VALUES(0,'Iron',5.4);
/*u1*/INSERT INTO PRODUCT VALUES(1,'Wood',24.8);
/*u1*/INSERT INTO PRODUCT VALUES(2,'Fire',24.8);
/*u1*/INSERT INTO PRODUCT VALUES(3,'Wood',50.8);
/*u1*/INSERT INTO PRODUCT VALUES(4,'Fire',26.8);
/*u1*/INSERT INTO PRODUCT VALUES(5,'Wood',24.8);

/*rTrue*/select ('Wood',24.8) match simple (select name,cost from product) from dual;
/*rFalse*/select ('Wood',24.8) match unique simple (select name,cost from product) from dual;

/*rTrue*/select ('Woodx',NULL) match simple (select name,cost from product) from dual;
/*rTrue*/select ('Fire',26.8) match unique simple (select name,cost from product) from dual;

/*rTrue*/select ('Wood',NULL) match partial (select name,cost from product) from dual;
/*rFalse*/select ('Wood',NULL) match unique partial (select name,cost from product) from dual;
/*rFalse*/select ('Woodx',NULL) match partial (select name,cost from product) from dual;

/*rFalse*/select ('Woodx',NULL) match full (select name,cost from product) from dual;
/*rTrue*/select ('Wood',24.8) match full (select name,cost from product) from dual;
/*rFalse*/select ('Wood',NULL) match full (select name,cost from product) from dual;
/*rFalse*/select ('Wood',24.8) match unique full (select name,cost from product) from dual;

/*rFalse*/select ('Wood',24.8) IS DISTINCT FROM ('Wood',24.8) from dual;
/*rTrue*/select ('Wood',24.8) IS DISTINCT FROM ('Wood',25.8) from dual;
/*rTrue*/select ('Wood',24.8) IS NOT DISTINCT FROM ('Wood',24.8) from dual;

/*rTrue*/select (5 > ANY (select cost from product)) OR (10 < 20) from dual;

/*rTrue*/select some(cost > 25.5) from product;
/*rFalse*/select some(cost > 100.5) from product;
/*rTrue*/select every(cost > 2.5) from product;
/*rFalse*/select every(cost > 20.5) from product;

delete from PRODUCT;

/*u1*/INSERT INTO PRODUCT VALUES(6,'Wood',NULL);
/*u1*/INSERT INTO PRODUCT VALUES(3,'Wood',50.8);
/*u1*/INSERT INTO PRODUCT VALUES(4,'Fire',26.8);

/*r
 4,26.8
 3,50.8
 6,NULL
*/select id,cost from product order by cost NULLS LAST;

/*r
 6,NULL
 4,26.8
 3,50.8 
*/select id,cost from product order by cost NULLS FIRST;

/*r
 6,NULL 
 3,50.8 
 4,26.8
*/select id,cost from product order by cost DESC NULLS FIRST;

/*r 
 3,50.8 
 4,26.8
 6,NULL
*/select id,cost from product order by cost DESC NULLS LAST;


set table product read only;
/*e*/INSERT INTO PRODUCT VALUES(14,'Fired',277.8);

set table product read write;
/*u1*/INSERT INTO PRODUCT VALUES(14,'Fired',277.8);

/*e*/set catalog 'effiproz';
/*u0*/set catalog 'PUBLIC';

/*u0*/set time zone interval '3:0' hour to minute;

create table alt_col_test(col1 int primary key);
alter table alt_col_test add column col2 int unique;


create table numbers_tbl(ti tinyint,ti2 tinyint,si smallint,bi bigint,di number, d double);
/*u1*/insert into numbers_tbl values(6,3,2,7,4,5);
/*r
 -6,-2,-7
*/select -ti,-si,-bi from numbers_tbl;

/*r2*/select mod(d, ti2) from numbers_tbl;
/*r1*/select mod(bi, ti2) from numbers_tbl;


create table fktest1(id int primary key, col2 varchar(100), col3 int,
 constraint constr1 unique(col2,col3));
 
create table fktest2(id int primary key, col3 int,col2 varchar(100),
  FOREIGN KEY  (col3,col2) references fktest1(col3,col2) );




CREATE TABLE myTable (
  id INTEGER,
  duration INTERVAL YEAR(3) TO MONTH
 );
 
/*u1*/ INSERT INTO myTable (id, duration)VALUES (1, INTERVAL '1' YEAR);
/*u1*/ INSERT INTO myTable (id, duration)VALUES (2, INTERVAL '6' MONTH);
/*u1*/ INSERT INTO myTable (id, duration)VALUES (3, INTERVAL '2' MONTH);
/*u1*/ INSERT INTO myTable (id, duration)VALUES (4, INTERVAL '1-4' YEAR TO MONTH);



/*r36*/select sum(duration) from myTable;
/*r9*/select avg(duration) from myTable;

CREATE TABLE myTable2 (
  id INTEGER,
  duration INTERVAL DAY(3) TO SECOND (4)
 );

/*u1*/INSERT INTO myTable2 (id, duration)VALUES (3, INTERVAL '10' MINUTE);
/*u1*/INSERT INTO myTable2 (id, duration)VALUES (4, INTERVAL '20' SECOND);

/*r0.0:10:20*/select sum(duration) from myTable2;
/*r0.0:05:10*/select avg(duration) from myTable2;


CREATE TABLE t1(i int);
INSERT INTO t1 VALUES(1);
CREATE TABLE t2(i int);
INSERT INTO t2 VALUES(2);
COMMIT;

CREATE USER peon1 identified by password;
/*u0*/GRANT CHANGE_AUTHORIZATION TO peon1;

/*u0*/CREATE ROLE r1;
/*u0*/CREATE ROLE r2;
/*u0*/GRANT ALL ON t1 TO r1;
/*u0*/GRANT ALL ON t2 TO r2;
/*u0*/GRANT r2, r1 TO peon1;

CONNECT USER peon1 identified by password;
/*c1*/SELECT * FROM t1;

set role 'R2';

/*c1*/SELECT * FROM t1;
/*rR2*/SELECT CURRENT_ROLE FROM dual;

CONNECT USER sa identified by '';

alter schema public rename to publicx;

create table trig_tbl2(id int , name varchar(50) unique);

create view trig_view as select id, name from trig_tbl2;

alter table trig_tbl2 add column namex varchar(200);
              
select * from information_schema.VIEW_TABLE_USAGE;
select * from information_schema.VIEW_COLUMN_USAGE;

alter table trig_tbl2 add constraint fk_trig_tbl2 foreign key(namex) references trig_tbl2(name);

drop table trig_tbl2 cascade;

create table utest(name varchar(200));
/*u1*/insert into utest values(U&'k\u0066un\u0066y');
/*rkfunfy*/select * from utest;

delete from utest;

/*u1*/insert into utest values(U&'k@u0066un@u0066y' UESCAPE '@');
/*rkfunfy*/select * from utest;

create table U&"k\u0066un\u0066yy"(name varchar(200));
/*u1*/insert into "kfunfyy" values(U&'k@u0066un@u0066y' UESCAPE '@');
/*rkfunfy*/select * from "kfunfyy";

create table U&"k@u0066un@u0066yyy"UESCAPE '@'(name varchar(200));
/*u1*/insert into "kfunfyyy" values(U&'k@u0066un@u0066y' UESCAPE '@');
/*rkfunfy*/select * from "kfunfyyy";


create table chartest1(name varchar_ignorecase(100));
/*u1*/insert into chartest1 values('James');

create table chartest2(name varchar(50));
/*u1*/insert into chartest1 values('David');

/*r
 David
 James 
*/select * from chartest1
 union select * from chartest2;
 
/*r
 David
 James 
*/select * from chartest2
 union select * from chartest1;
 
create table ignorecasecat_tbl(name1 varchar_ignorecase(100),name2 varchar_ignorecase(100));
/*u1*/insert into ignorecasecat_tbl values('James','David');
/*rJamesDavid*/select name1||name2 from ignorecasecat_tbl;

create table clobcat_tbl(name1 clob(100),name2 clob(100));
/*u1*/insert into clobcat_tbl values('James','David');
/*rJamesDavid*/select name1||name2 from clobcat_tbl;

/*r
 James,David,David
*/select cast(name1 as varchar(150)),cast(name2 as varchar(50)),cast(name2 as clob(50)) from clobcat_tbl;

delete from clobcat_tbl;
/*u1*/insert into clobcat_tbl values('james samson','david goliath');
/*rJames Samson*/select initcap(name1) from clobcat_tbl;

/*r
 JAMES SAMSON,david goliath
*/select upper(name1),lower(name2) from clobcat_tbl;

delete from clobcat_tbl;
/*u1*/insert into clobcat_tbl values('  james samson   ','david goliath');
/*rjames samson*/select trim(name1) from clobcat_tbl;


delete from clobcat_tbl;
/*u1*/insert into clobcat_tbl values('james','david');
/*r
 david,     james
*/select name2,lpad(name1,10) from clobcat_tbl;
/*r
 james     ,david
*/select rpad(name1,10),name2 from clobcat_tbl;

/*r
 david,*#*#*james
*/select name2,lpad(name1,10,'*#') from clobcat_tbl;
/*r
 james*#*#*,david
*/select rpad(name1,10,'*#'),name2 from clobcat_tbl;

/*rme*/select substring(name1,3,2) from clobcat_tbl;

/*r
 10,5
*/select OCTET_LENGTH(name1),CHAR_LENGTH(name1) from clobcat_tbl;


delete from ignorecasecat_tbl;
/*u1*/insert into ignorecasecat_tbl values('james','david');

/*r
 david,*#*#*james
*/select name2,lpad(name1,10,'*#') from ignorecasecat_tbl;
/*r
 james*#*#*,david
*/select rpad(name1,10,'*#'),name2 from ignorecasecat_tbl;

/*r
 10,5
*/select OCTET_LENGTH(name1),CHAR_LENGTH(name1) from ignorecasecat_tbl;

delete from clobcat_tbl;
/*u1*/insert into clobcat_tbl values(null,null);

delete from clobcat_tbl;
/*u1*/insert into clobcat_tbl values(null,null);

/*r
 null,null,null,null
*/select initcap(name1),upper(name1),lower(name1),trim(name1) from clobcat_tbl;

delete from ignorecasecat_tbl;
/*u1*/insert into ignorecasecat_tbl values(null,null);

/*r
 null,null,null,null
*/select initcap(name1),upper(name1),lower(name1),trim(name1) from ignorecasecat_tbl;


delete from clobcat_tbl;
/*u1*/insert into clobcat_tbl values('To the west','the');
/*r4*/select position(name2 in 'To the west') from clobcat_tbl;
/*r4*/select position('the' in 'To the west') from dual;

/*rJames*/select cast('James    ' as varchar(5)) from dual;

create table limittest(name varchar(5));
/*u1*/insert into limittest values('James    ');

create table bintest(col1 varbinary(100),col2 varbinary(100),col3 varbinary(100));
/*u1*/insert into bintest(col1,col2,col3) values(x'A103',x'BCC73E',x'AA');
/*u1*/update bintest set col3=col1||col2;
/*rBCC73EA103*/select col3 from bintest;

delete from bintest;
/*u1*/insert into bintest(col1) values(x'00A1030000');
/*rA103*/select trim(col1) from bintest;

delete from bintest;
/*u1*/insert into bintest(col1) values(x'BBA103BBBB');
/*rA103*/select trim(both x'BB' from col1) from bintest;
/*rA103BBBB*/select trim(trailing x'BB' from col1) from bintest;
/*rBBA103*/select trim(leading x'BB' from col1) from bintest;

/*r5*/select octet_length(col1) from bintest;
/*r40*/select bit_length(col1) from bintest;

/*rBBA103FFBB*/select overlay(col1 placing x'FF' from 2) from bintest;
/*rBBA1FFBB*/select overlay(col1 placing x'FF' from 2 for 4) from bintest;

/*rBBA103BB*/select substring(col1 from 2) from bintest;
/*r03BB*/select substring(col1 from 2 for 2) from bintest;

delete from bintest;
/*u1*/insert into bintest(col1,col2,col3) values(x'C7',x'BCC73E',x'AA');
/*r2*/select position(col1 in col2) from bintest;

/*rbcc73e*/select cast(col2 as varchar(100)) from bintest;


/*rPUBLICX*/select CURRENT_SCHEMA from dual;
/*rPUBLIC*/select CURRENT_CATALOG from dual;
/*rSA*/select SESSION_USER from dual;
/*rSA*/select SYSTEM_USER from dual;

create table datetest(col1 date);
/*u1*/insert into datetest values(date '02-03-2010');
/*rFebruary*/select extract(MONTH_NAME from col1) from datetest;
/*rWednesday*/select extract(DAY_NAME from col1) from datetest;

/*rTrue*/select ISAUTOCOMMIT()  from dual;
set autocommit off;
/*rFalse*/select ISAUTOCOMMIT()  from dual;
set autocommit on;

select database()  from dual;

/*rFalse*/select ISREADONLYSESSION()  from dual;
SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;
/*rTrue*/select ISREADONLYSESSION()  from dual;
SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE;

/*rFalse*/select ISREADONLYSESSION()  from dual;
SET READONLY TRUE;
/*rTrue*/select ISREADONLYSESSION()  from dual;
SET READONLY FALSE;

/*rFalse*/select ISREADONLYDATABASE()  from dual;
/*rFalse*/select ISREADONLYDATABASEFILES()  from dual;

/*r
 bb,     ,aa
*/select 'bb',space(5),'aa'  from dual;

/*rababab*/select repeat('ab',3)  from dual;

delete from bintest;
/*u1*/insert into bintest(col1) values(x'3F7A');
/*r3f7a*/select RAWTOHEX(col1)  from bintest;

delete from bintest;
/*u1*/insert into bintest(col1) values( hextoraw('3F7A'));

/*r0*/select DIFFERENCE('bear','beer') from dual;
/*r2*/select DIFFERENCE('bear','house') from dual;

/*r57.2957795130823*/select DEGREES(1) from dual;
/*r1*/select RADIANS(57.2957795130823) from dual;

/*r155*/select BITAND(187,223) from dual;
/*r191*/select BITOR(187,4) from dual;
/*r238*/select BITXOR(187,85) from dual;

/*rNULL*/select BITXOR(NULL,85) from dual;
/*rNULL*/select BITXOR(95,NULL) from dual;


/*rFebruary*/select MONTHNAME(col1) from datetest;
/*rWednesday*/select DAYNAME(col1) from datetest;

/*r3*/select DAYOFMONTH(col1) from datetest;
/*r3*/select extract(DAY_OF_MONTH from col1) from datetest;

/*r34*/select DAYOFYEAR(col1) from datetest;
/*r34*/select extract(DAY_OF_YEAR from col1) from datetest;

/*r4*/select DAYOFWEEK (col1) from datetest;
/*r4*/select extract(DAY_OF_WEEK  from col1) from datetest;

/*rgo the home*/select insert ('go to home' ,4,2,'the') from dual;

/*r3.1415926*/select PI() from dual;

/*r-0.4161468*/select COS(2) from dual;
/*r2*/select ACOS(-0.4161468) from dual;

/*r2*/select LOG10(100) from dual;

/*r-2.18503986*/select TAN(2) from dual;

create table interval_tbl(col1 interval day to second,col2 interval year to month);
/*u1*/insert into interval_tbl values(interval '2 6:20:45' day to second,interval '2-6' year to month);
/*r2*/select extract(year from col2) from interval_tbl;
/*r6*/select extract(month from col2) from interval_tbl;
/*r2*/select extract(day from col1) from interval_tbl;
/*r6*/select extract(hour from col1) from interval_tbl;
/*r20*/select extract(minute from col1) from interval_tbl;

/*r2-3-2012*/select TIMESTAMPADD (SQL_TSI_YEAR ,2,date '2-3-2010') from dual;
/*r4-3-2010*/select TIMESTAMPADD (SQL_TSI_MONTH ,2,date '2-3-2010') from dual;
/*r2-5-2010*/select TIMESTAMPADD (SQL_TSI_DAY ,2,date '2-3-2010') from dual;
/*r2-17-2010*/select TIMESTAMPADD (SQL_TSI_WEEK ,2,date '2-3-2010') from dual;

/*r-2.6:20:45*/select -1*col1 from interval_tbl;
/*r-30*/select -1*col2 from interval_tbl;
update interval_tbl set col1=interval '-2 6:20:45' day to second,col2='-2-6' year to month;
/*r-2.6:20:45*/select col1 from interval_tbl;
/*r2.6:20:45*/select abs(col1) from interval_tbl;
/*r-30*/select col2 from interval_tbl;
/*r30*/select abs(col2) from interval_tbl;

/*r100*/select NULLIF(100,150) from dual;
/*rNULL*/select NULLIF(100,100) from dual;

/*r-30*/select tbl.col2 from (select col1,col2 from interval_tbl) as tbl;

set autocommit off;

create table txtest(id int,name varchar(100));
/*u1*/insert into txtest values(1,'car');
savepoint sp1;
/*u1*/insert into txtest values(2,'van');
/*r
 1,car
 2,van
*/select * from txtest;
rollback to savepoint sp1;
/*r
 1,car
*/select * from txtest;
rollback work and no chain;
start transaction read only;
/*e*/insert into txtest values(3,'bus');
rollback work and chain;
insert into txtest values(3,'bus');
rollback work and no chain;
insert into txtest values(3,'bus');
savepoint sp2;
insert into txtest values(4,'lorry');
release savepoint sp2;
/*e*/rollback to savepoint sp2;

create table lobtest(col1 blob(20k),clo2 clob(30m),col3 blob(4g));




