-- 1
create table cytaty_new as select * from ZSBD_TOOLS.cytaty;


-- 2
select *
from cytaty_new
where lower(tekst) LIKE '%pesymista%' OR lower(tekst) LIKE '%optymista%';


-- 3
create index CYTATY_NEW_IDX on cytaty_new(tekst)
indextype is CTXSYS.CONTEXT;


-- 4
select *
from cytaty_new
where contains(tekst, 'pesymista') > 0 OR contains(tekst, 'optymista') > 0;


-- 5
select *
from cytaty_new
where contains(tekst, 'pesymista') > 0 AND contains(tekst, 'optymista') <= 0;


-- 6
select *
from cytaty_new
where contains(tekst,'near((pesymista, optymista), 3)') > 0;


-- 7
select *
from cytaty_new
where contains(tekst,'near((pesymista, optymista), 10)') > 0;


-- 8
select *
from cytaty_new
where contains(tekst, 'życi%') > 0;


-- 9
select autor, score(1) as score, tekst
from cytaty_new
where contains(tekst, 'życi%', 1) > 0;


-- 10
select autor, score(1) as score, tekst
from cytaty_new
where contains(tekst, 'życi%', 1) > 0
order by score desc
fetch first row only;


-- 11 brak
select autor, tekst
from cytaty_new
where contains(tekst, 'fuzzy(probelm)') > 0;


-- 12
INSERT INTO cytaty_new
(id, autor, tekst)
VALUES
(39, 'Bertrand Russell', 'To smutne, że głupcy są tacy pewni siebie, a ludzie rozsądni tacy pełni wątpliwości.');

commit;


-- 13
select *
from cytaty_new
where contains(tekst, 'głupcy') > 0;


-- 14
select * from dr$cytaty_new_idx$i; -- i k n u


-- 15
DROP INDEX CYTATY_NEW_IDX;

create index CYTATY_NEW_IDX on cytaty_new(tekst)
indextype is CTXSYS.CONTEXT;


-- 16
select *
from cytaty_new
where contains(tekst, 'głupcy') > 0;


-- 17
DROP INDEX CYTATY_NEW_IDX;
drop table cytaty_new;


-- czesc druga

-- 1
create table quotes_new as select * from ZSBD_TOOLS.quotes;

-- 2
create index quotes_new_IDX on quotes_new(text)
indextype is CTXSYS.CONTEXT;


-- 3
select *
from quotes_new
where contains(text, 'work | $work | working | $working') > 0;


-- 4
select *
from quotes_new
where contains(text, 'it') > 0;


-- 5
select * from CTX_STOPLISTS;


-- 6
select * from CTX_STOPWORDS;


-- 7
DROP INDEX quotes_new_IDX;

create index quotes_new_IDX on quotes_new(text)
indextype is CTXSYS.CONTEXT
parameters ('stoplist CTXSYS.EMPTY_STOPLIST');


-- 8
select *
from quotes_new
where contains(text, 'it') > 0;


-- 9
select *
from quotes_new
where contains(text, 'fool AND humans') > 0;


-- 10
select *
from quotes_new
where contains(text, 'fool AND computer') > 0;


-- 11
select *
from quotes_new
where contains(text, '(fool AND humans) within SENTENCE') > 0;


-- 12
DROP INDEX quotes_new_IDX;


-- 13
begin
 ctx_ddl.create_section_group('nullgroup', 'NULL_SECTION_GROUP');
 ctx_ddl.add_special_section('nullgroup', 'SENTENCE');
 ctx_ddl.add_special_section('nullgroup', 'PARAGRAPH');
end;
/


-- 14
create index quotes_new_IDX on quotes_new(text)
indextype is CTXSYS.CONTEXT
parameters ('stoplist CTXSYS.EMPTY_STOPLIST
             section group nullgroup');


-- 15 -- brak
select *
from quotes_new
where contains(text, '(fool AND humans) within SENTENCE') > 0;

select *
from quotes_new
where contains(text, '(fool AND computer) within SENTENCE') > 0;


-- 16
select *
from quotes_new
where contains(text, 'humans') > 0;


-- 17
DROP INDEX quotes_new_IDX;

begin
 ctx_ddl.create_preference('lex_z_m','BASIC_LEXER');
 ctx_ddl.set_attribute('lex_z_m',
 'printjoins', '_-');
 ctx_ddl.set_attribute ('lex_z_m',
 'index_text', 'YES');
end;
/

create index quotes_new_IDX on quotes_new(text)
indextype is CTXSYS.CONTEXT
parameters ('stoplist CTXSYS.EMPTY_STOPLIST
             section group nullgroup
             lexer lex_z_m');


-- 18
select *
from quotes_new
where contains(text, 'humans') > 0;


-- 19
select *
from quotes_new
where contains(text, 'non\-humans') > 0;


-- 20
DROP INDEX quotes_new_IDX;

drop table quotes_new;