Wordnetport

All queries in this demo will work when all the base tables are already loaded and the CREATE scripts in ORACLE_DDL.sql are executed.

SEARCH_WORDNET is a table that contains the synsets for a given word
--- Number of rows
select count(*) from SEARCH_WORDNET;
--- 206353

--- row countS of SEARCH_WORDNET per POS
select POS, count(*) ROW_COUNT from SEARCH_WORDNET group by POS

--- row counts of SEARCH_WORDNET per LEXDOMAINNAME
select POS, LEXDOMAINID, LEXDOMAINNAME, count(*) ROW_COUNT from SEARCH_WORDNET group by POS, LEXDOMAINID, LEXDOMAINNAME;
select * from SEARCH_WORDNET where lemma= 'java' order by INSTR('n,v,a,s,r,' , pos||',') ,sensenum ;
select TAGCOUNT, SYNSETID, LEXDOMAINNAME, LEXDOMAINID, POS, SHOWALL, DEFINITION, SAMPLESET from SEARCH_WORDNET where lemma= 'java' order by INSTR('n,v,a,s,r,' , pos||',') ,sensenum;

Show All
Reference here
select TAGCOUNT, SYNSETID, LEXDOMAINNAME, LEXDOMAINID, POS, SHOWALL, DEFINITION, SAMPLESET from SEARCH_WORDNET where lemma= 'upstage' order by INSTR('n,v,a,s,r,' , pos||',') ,sensenum;
Show All
Reference here

Views that contains a particular relation

LINK TABLES

select * from MV_DIRECT_HYPERNYM where a_lemma= 'java' order by INSTR('n,v,a,s,r,' , a_pos||',') , a_sensenum;
--- hypernym or direct hypernym
Reference here

select * from MV_DIRECT_HYPONYM where a_lemma= 'java' order by INSTR('n,v,a,s,r,' , a_pos||',') , a_sensenum;
--- hyponym or direct hyponym or troponym (for verb)
Reference here

select * from MV_INSTANCE_HYPERNYM where a_lemma= 'java' order by INSTR('n,v,a,s,r,' , a_pos||',') , a_sensenum;
--- instance hypernym or instance
Reference here

select * from MV_INSTANCE_HYPONYM where a_lemma= 'apus' order by INSTR('n,v,a,s,r,' , a_pos||',') , a_sensenum;
--- instance hyponym or instance
Reference here
note: MV_INSTANCE_HYPERNYM is a subset of MV_INSTANCE_HYPONYM

select * from MV_PART_HOLONYM where a_lemma= 'java' order by INSTR('n,v,a,s,r,' , a_pos||',') , a_sensenum;
--- part holonym
Reference here

select * from MV_PART_MERONYM where a_lemma= 'java' order by INSTR('n,v,a,s,r,' , a_pos||',') , a_sensenum;
--- part meronym
Reference here

select * from MV_MEMBER_HOLONYM where a_lemma= 'angola' order by INSTR('n,v,a,s,r,' , a_pos||',') , a_sensenum;
--- member holonym
Reference here

select * from MV_MEMBER_MERONYM where a_lemma= 'java' order by INSTR('n,v,a,s,r,' , a_pos||',') , a_sensenum;
--- member meronym
Reference here
select A_LEXDOMAINNAME, A_POS, A_DEFINITION, A_SAMPLESET, C_HIDEALL,C_LEXDOMAINNAME, C_POS, C_DEFINITION, C_SAMPLESET from MV_MEMBER_MERONYM where a_lemma= 'java' order by INSTR('n,v,a,s,r,' , a_pos||',') , a_sensenum;

select * from MV_SUBSTANCE_HOLONYM where a_lemma= 'tea' order by INSTR('n,v,a,s,r,' , a_pos||',') , a_sensenum;
--- substance holonym
Reference here

select * from MV_SUBSTANCE_MERONYM where a_lemma= 'java' order by INSTR('n,v,a,s,r,' , a_pos||',') , a_sensenum;
--- substance meronym
Reference here

select * from MV_ENTAILMENT where a_lemma= 'master' order by INSTR('n,v,a,s,r,' , a_pos||',') , a_sensenum;
--- entail
Reference here

select * from MV_CAUSE where a_lemma= 'grow' order by INSTR('n,v,a,s,r,' , a_pos||',') , a_sensenum;
--- cause
Reference here

select * from MV_ANTONYM where a_lemma= 'upstage' order by INSTR('n,v,a,s,r,' , a_pos||',') , a_sensenum;
--- antonym
Reference here

select * from MV_SIMILAR_TO where a_lemma= 'upstage' order by INSTR('n,v,a,s,r,' , a_pos||',') , a_sensenum;
--- Similar to
Reference here

select * from MV_ALSO where a_lemma= 'abundant' order by INSTR('n,v,a,s,r,' , a_pos||',') , a_sensenum;
--- also or see also
Reference here

select * from MV_PHRASAL_VERB where a_lemma= 'fight' order by INSTR('n,v,a,s,r,' , a_pos||',') , a_sensenum;
--- also or phrasal verb
Reference here

select * from MV_ATTRIBUTE where a_lemma= 'orient' order by INSTR('n,v,a,s,r,' , a_pos||',') , a_sensenum;
--- attribute
Reference here

select * from MV_VERB_GROUP where a_lemma= 'action' order by INSTR('n,v,a,s,r,' , a_pos||',') , a_sensenum;
--- verb group
Reference here

select * from MV_PARTICIPLE where a_lemma= 'sought' order by INSTR('n,v,a,s,r,' , a_pos||',') , a_sensenum;
--- participle
Reference here

select * from MV_PERTAINYM where a_lemma= 'aortic' order by INSTR('n,v,a,s,r,' , a_pos||',') , a_sensenum;
--- pertainym
Reference here

select * from MV_DERIVATION where a_lemma= 'gabble' order by INSTR('n,v,a,s,r,' , a_pos||',') , a_sensenum;
--- Derivation or derivationally related form
Reference here

select * from MV_DOMAIN_CATEGORY where a_lemma= 'upstage' order by INSTR('n,v,a,s,r,' , a_pos||',') , a_sensenum;
--- domain category
Reference here

select * from MV_DOMAIN_MEMBER_CATEGORY where a_lemma= 'physics' order by INSTR('n,v,a,s,r,' , a_pos||',') , a_sensenum;
-- domain member category or domain term category
Reference here

select * from MV_DOMAIN_REGION where a_lemma= 'humanitarian' order by INSTR('n,v,a,s,r,' , a_pos||',') , a_sensenum;
-- domain region
Reference here

select * from MV_DOMAIN_MEMBER_REGION where a_lemma= 'mexico' order by INSTR('n,v,a,s,r,' , a_pos||',') , a_sensenum;
-- domain member region or domain term region
Reference here

Sentence frames of verbs

select * from SENTENCE_FRAME where lemma = 'fight' order by synsetid;
Reference here

A unified view that contains all relations

MV_ALL_LINKS

SELECT *
FROM MV_ALL_LINKS WHERE A_LEMMA='java'
order by INSTR('n,v,a,s,r,' , a_pos||',') , a_sensenum,
INSTR('x94,x2,x13,x12,x14,x91,x32,x16,x92,x96,x15,x70,x1,x11,x23,x93,x4,x3,x21,x50,x95,x40,x60,x71,x30,x80,x81,' ,'x'||B_LINKID||',');
Reference here

SELECT A_LEMMA, A_POS, A_DEFINITION, D_LINK, C_POS, C_HIDEALL, C_DEFINITION FROM MV_ALL_LINKS WHERE A_LEMMA='java'
order by INSTR('n,v,a,s,r,' , a_pos||',') , a_sensenum,
INSTR('x94,x2,x13,x12,x14,x91,x32,x16,x92,x96,x15,x70,x1,x11,x23,x93,x4,x3,x21,x50,x95,x40,x60,x71,x30,x80,x81,' ,'x'||B_LINKID||',');

SELECT A_LEMMA, A_POS, A_DEFINITION, D_LINK, C_POS, C_HIDEALL, C_DEFINITION FROM MV_ALL_LINKS WHERE A_LEMMA='upstage'
order by INSTR('n,v,a,s,r,' , a_pos||',') , a_sensenum,
INSTR('x94,x2,x13,x12,x14,x91,x32,x16,x92,x96,x15,x70,x1,x11,x23,x93,x4,x3,x21,x50,x95,x40,x60,x71,x30,x80,x81,' ,'x'||B_LINKID||',');

SELECT *
FROM MV_ALL_LINKS WHERE A_LEMMA='upstage'
order by INSTR('n,v,a,s,r,' , a_pos||',') , a_sensenum,
INSTR('x94,x2,x13,x12,x14,x91,x32,x16,x92,x96,x15,x70,x1,x11,x23,x93,x4,x3,x21,x50,x95,x40,x60,x71,x30,x80,x81,' ,'x'||B_LINKID||',');

SELECT *
FROM MV_ALL_LINKS WHERE A_LEMMA='chile'
order by INSTR('n,v,a,s,r,' , a_pos||',') , a_sensenum,
INSTR('x94,x2,x13,x12,x14,x91,x32,x16,x92,x96,x15,x70,x1,x11,x23,x93,x4,x3,x21,x50,x95,x40,x60,x71,x30,x80,x81,' ,'x'||B_LINKID||',');

SELECT *
FROM MV_ALL_LINKS WHERE A_LEMMA='arithmetic'
order by INSTR('n,v,a,s,r,' , a_pos||',') , a_sensenum,
INSTR('x94,x2,x13,x12,x14,x91,x32,x16,x92,x96,x15,x70,x1,x11,x23,x93,x4,x3,x21,x50,x95,x40,x60,x71,x30,x80,x81,' ,'x'||B_LINKID||',');

SELECT *
FROM MV_ALL_LINKS WHERE A_LEMMA='many'
order by INSTR('n,v,a,s,r,' , a_pos||',') , a_sensenum,
INSTR('x94,x2,x13,x12,x14,x91,x32,x16,x92,x96,x15,x70,x1,x11,x23,x93,x4,x3,x21,x50,x95,x40,x60,x71,x30,x80,x81,' ,'x'||B_LINKID||',');

SELECT *
FROM MV_ALL_LINKS WHERE A_LEMMA='fight'
order by INSTR('n,v,a,s,r,' , a_pos||',') , a_sensenum,
INSTR('x94,x2,x13,x12,x14,x91,x32,x16,x92,x96,x15,x70,x1,x11,x23,x93,x4,x3,x21,x50,x95,x40,x60,x71,x30,x80,x81,' ,'x'||B_LINKID||',');

Inherited hypernymns of coffee

Reference here

select distinct A_HIDEALL, C_HIDEALL, C_SYNSETID from MV_DIRECT_HYPERNYM where A_SYNSETID=107860414;
select distinct A_HIDEALL, C_HIDEALL, C_SYNSETID from MV_DIRECT_HYPERNYM where A_SYNSETID=107812430;
select distinct A_HIDEALL, C_HIDEALL, C_SYNSETID from MV_DIRECT_HYPERNYM where A_SYNSETID=100021445;

A table that contains inflected forms of words

MORPHOLOGY

SELECT * FROM MORPHOLOGY WHERE MORPH in ('quizzes', 'men', 'best','wolves') order by morph;
SELECT * FROM MORPHOLOGY WHERE LEMMA in ('good', 'well', 'man', 'quiz', 'wolf') order by morph;

Materialized view customized for a specific Word Games such as Hangaroo and Hangman

MV_HANGGAMES

SELECT * FROM MV_HANGGAMES WHERE lemma in ('introjection', 'delphic oracle', 'expansion slot', 'user interface', 'technical foul', 'topicalization');

Stored functions for Word Games

select sf_cnt_in_set_ratio( 'abecd', 'aeiouAEIOU ') from dual;
select sf_cnt_not_in_set_consecutive( 'abecdfgha', 'aeiouAEIOU-/.'' ') from dual;
select sf_excluding_chars('abcd', '0123456789') from dual;
select SF_STR_LEN ('ab cde-g') from dual;

Sample Difficult Words
select category, lemma, definition from MV_HANGGAMES where
sf_excluding_chars(lemma, '0123456789')=1
and SF_STR_LEN(lemma)> 9
and sf_cnt_in_set_ratio(lemma, 'aeiouAEIOU ') < 0.2
and sf_cnt_not_in_set_consecutive( lemma, 'aeiouAEIOU-/.'' ') = 5;

Hangaroo Content from MV_HANGGAMES

select count(*)/5 from mv_hanggames where subcategory is not null and
sf_excluding_chars(lemma, '0123456789/') = 1 and SF_STR_LEN(lemma)>3;
--- 972
--- the number of rows per difficulty level should be more less the same with this value

select lemma, subcategory, category, definition from mv_hanggames
where subcategory is not null and
sf_excluding_chars(lemma, '0123456789/') = 1 and SF_STR_LEN(lemma)>4
and POLYSEMY_CNT >3;
-- level 1
--- easiest level

select count(*) from mv_hanggames
where subcategory is not null and
sf_excluding_chars(lemma, '0123456789/') = 1 and SF_STR_LEN(lemma)>4
and POLYSEMY_CNT > 3;
-- level 1
--- 729

select lemma, subcategory, category, definition from mv_hanggames
where subcategory is not null and
sf_excluding_chars(lemma, '0123456789/') = 1 and SF_STR_LEN(lemma)>4
and POLYSEMY_CNT <=3
and sf_cnt_in_set_ratio(lemma, 'aeiouAEIOU ') >=0.429
-- level 2
--- 1013

select lemma, subcategory, category, definition from mv_hanggames
where subcategory is not null and
sf_excluding_chars(lemma, '0123456789/') = 1 and SF_STR_LEN(lemma)>4
and POLYSEMY_CNT <=3
and sf_cnt_in_set_ratio(lemma, 'aeiouAEIOU ') < 0.429 and sf_cnt_in_set_ratio(lemma, 'aeiouAEIOU ') >= 0.4
-- level 3

select count(*) from mv_hanggames
where subcategory is not null and
sf_excluding_chars(lemma, '0123456789/') = 1 and SF_STR_LEN(lemma)>4
and POLYSEMY_CNT <=3
and sf_cnt_in_set_ratio(lemma, 'aeiouAEIOU ') < 0.429 and sf_cnt_in_set_ratio(lemma, 'aeiouAEIOU ') >= 0.4
-- level 3
-- 902

select lemma, subcategory, category, definition from mv_hanggames
where subcategory is not null and
sf_excluding_chars(lemma, '0123456789/') = 1 and SF_STR_LEN(lemma)>4
and POLYSEMY_CNT <=3
and sf_cnt_in_set_ratio(lemma, 'aeiouAEIOU ') < 0.4
and sf_cnt_not_in_set_consecutive( lemma, 'aeiouAEIOU-/.'' ') <= 2
-- level 4

select count(*) from mv_hanggames
where subcategory is not null and
sf_excluding_chars(lemma, '0123456789/') = 1 and SF_STR_LEN(lemma)>4
and POLYSEMY_CNT <=3
and sf_cnt_in_set_ratio(lemma, 'aeiouAEIOU ') < 0.4
and sf_cnt_not_in_set_consecutive( lemma, 'aeiouAEIOU-/.'' ') <= 2
-- level 4
-- 1056

select lemma, subcategory, category, definition from mv_hanggames
where subcategory is not null and
sf_excluding_chars(lemma, '0123456789/') = 1 and SF_STR_LEN(lemma)>4
and POLYSEMY_CNT =1
and sf_cnt_in_set_ratio(lemma, 'aeiouAEIOU ') < 0.3
and sf_cnt_not_in_set_consecutive( lemma, 'aeiouAEIOU-/.'' ')>2
-- level 5
--- most difficult level

select count(*) from mv_hanggames
where subcategory is not null and
sf_excluding_chars(lemma, '0123456789/') = 1 and SF_STR_LEN(lemma)>4
and POLYSEMY_CNT =1
and sf_cnt_in_set_ratio(lemma, 'aeiouAEIOU ') < 0.3
and sf_cnt_not_in_set_consecutive( lemma, 'aeiouAEIOU-/.'' ')>2
level 5
-- 201

In this project, there are 3 tables that are identified to provide content for Hangman content

Namely, MV_INSTANCE_HYPONYM, MV_HANGGAMES, SEARCH_WORDNET

Hangman content from MV_INSTANCE_HYPONYM table
select a_synsetid, a_lemma, a_lexdomainname, a_pos, a_hideall, c_lexdomainname, c_pos, c_hideall from MV_INSTANCE_HYPONYM
where c_hideall like '%country%' and a_lemma not like '% %'
order by a_synsetid;
--- countries

select a_lemma, a_lexdomainname, a_pos, a_hideall, c_lexdomainname, c_pos, c_hideall from MV_INSTANCE_HYPONYM
where c_hideall like '%composer%';
--- composers

select a_lemma, a_lexdomainname, a_pos, a_hideall, c_lexdomainname, c_pos, c_hideall from mv_instance_hyponym
where c_hideall like '%actress%';
--- actress

select a_lemma, a_lexdomainname, a_pos, a_hideall, c_lexdomainname, c_pos, c_hideall from mv_instance_hyponym
where c_hideall like '%chemist%';

select a_lemma, a_lexdomainname, a_pos, a_hideall, c_lexdomainname, c_pos, c_hideall from mv_instance_hyponym
where c_hideall like '%dictator%';

select a_lemma, a_lexdomainname, a_pos, a_hideall, c_lexdomainname, c_pos, c_hideall from mv_instance_hyponym
where c_hideall like '%fictional character%';

select a_lemma, a_lexdomainname, a_pos, a_hideall, c_lexdomainname, c_pos, c_hideall from mv_instance_hyponym
where c_hideall like '%constellation%';

select a_lemma, a_lexdomainname, a_pos, a_hideall, c_lexdomainname, c_pos, c_hideall from mv_instance_hyponym
where c_hideall like '%pianist%';

--- Hangman content from MV_HANGGAMES table
select subcategory, count(*) WORDCOUNT from mv_hanggames where subcategory is not null group by subcategory
having count(*)>=10 order by count(*);
--- word count per Hangman category

select distinct lemma from mv_hanggames where subcategory = 'basketball';
--- basketball

select distinct lemma from mv_hanggames where subcategory = 'astrology' order by lemma;
--- astrology

----Hangman content from SEARCH_WORDNET table
select lexdomainname, count(*) wordcount from SEARCH_WORDNET group by lexdomainname order by count(*);

---- Some categories here can be used in Hangman
select distinct lemma from SEARCH_WORDNET where lexdomainname='verb.weather' order by lemma;

Boggle and Scrabble

select * from mv_wordnet_scrabble where lemma='prep';

select * from mv_wordnet_scrabble where lemma='pre';
-- but this word is a valid word in National Scrabble Association

select * from mv_wordnet_scrabble where lemma in ('lincoln', 'daniel');
-- proper nouns are excluded just like the standard Scrabble.

Batang henyo

select distinct pos from search_wordnet;
select distinct LEXDOMAINNAME from search_wordnet where pos='n';

--- location is divided into: eastern hemisphere abd western hemisphere

select distinct b.a_lemma from MV_PART_MERONYM a join MV_PART_MERONYM b on a.c_synsetid=b.a_synsetid where a.A_LEMMA = 'eastern hemisphere';

select distinct b.a_lemma from MV_PART_MERONYM a join MV_PART_MERONYM b on a.c_synsetid=b.a_synsetid where a.A_LEMMA = 'eurasia';

select distinct b.a_lemma from MV_PART_MERONYM a join MV_PART_MERONYM b on a.c_synsetid=b.a_synsetid where a.A_LEMMA = 'asia';

select distinct b.a_lemma from MV_PART_MERONYM a join MV_PART_MERONYM b on a.c_synsetid=b.a_synsetid where a.A_LEMMA = 'southeast asia';

select distinct b.a_lemma from MV_PART_MERONYM a join MV_PART_MERONYM b on a.c_synsetid=b.a_synsetid where a.A_LEMMA = 'indonesia';