"ITKC convert sql"의 두 판 사이의 차이

2018년 8월 10일 (금) 14:43 판

use hiblue
select * from 번역연호
--year 
	--year class year
	select distinct '<http://db.itkc.or.kr/time#year'+cast(서기 as nvarchar)+'>', '<http://www.w3.org/2002/07/owl#class>', '<http://dh.aks.ac.kr/ontologies/dhtime#Year>'   from 번역연호 
	--year dangi
	select distinct '<http://db.itkc.or.kr/time#year'+cast(서기 as nvarchar)+'>', '<http://dh.aks.ac.kr/ontologies/dhtime#dangunEra>', '"'+cast(단기 as nvarchar)+'"^^xsd:nonNegativeInteger'   from 번역연호
	--year ganzhi
	select distinct '<http://db.itkc.or.kr/time#year'+cast(서기 as nvarchar)+'>', '<http://dh.aks.ac.kr/ontologies/dhtime#ganzhi>', '"'+substring(간지,0,3)+'"@ko' from 번역연호
	select distinct '<http://db.itkc.or.kr/time#year'+cast(서기 as nvarchar)+'>', '<http://dh.aks.ac.kr/ontologies/dhtime#ganzhi>', '"'+substring(간지,4,2)+'"@hanja' from 번역연호
	--year after year
	select distinct  '<http://db.itkc.or.kr/time#year'+cast(a.서기 as nvarchar)+'>', '<http://www.w3.org/2006/time#after>', '<http://db.itkc.or.kr/time#year'+cast(b.서기 as nvarchar)+'>' from 번역연호 a, 번역연호 b where a.서기=b.서기+1
	--year before year
	select distinct  '<http://db.itkc.or.kr/time#year'+cast(a.서기 as nvarchar)+'>', '<http://www.w3.org/2006/time#before>', '<http://db.itkc.or.kr/time#year'+cast(b.서기 as nvarchar)+'>' from 번역연호 a, 번역연호 b where a.서기+1=b.서기
	--year eraYear / emperorYear
	select distinct  '<http://db.itkc.or.kr/time#year'+cast(서기 as nvarchar)+'>', '<http://dh.aks.ac.kr/ontologies/dhtime#yearLink>', '_:yearLink'+cast(일련번호 as nvarchar) from 번역연호
	select distinct '_:yearLink'+cast(일련번호 as nvarchar) , '<http://dh.aks.ac.kr/ontologies/dhtime#eraYear>', '"'+cast(연호년 as nvarchar)+'"^^xsd:nonNegativeInteger' from 번역연호 where  연호년 is not null
	select distinct '_:yearLink'+cast(일련번호 as nvarchar) , '<http://dh.aks.ac.kr/ontologies/dhtime#emperorYear>', '"'+cast(재위년 as nvarchar)+'"^^xsd:nonNegativeInteger' from 번역연호 where  재위년 is not null
	--year hasYearLink
	drop table k_era
--era
	create table k_era (id INT IDENTITY(1,1) not null, 휘이름 nvarchar(100), 연호 nvarchar(100), first int,firstyearlink int, last int,lastyearlink int)
	insert into k_era select 휘이름, 연호, min(서기), min(일련번호), max(서기), max(일련번호) from 번역연호 group by 휘이름, 연호 having 연호 is not null
	select * from k_era
	--era class era
	select '<http://db.itkc.or.kr/time#era'+cast(id as nvarchar)+'>', '<http://www.w3.org/2002/07/owl#class>', '<http://dh.aks.ac.kr/ontologies/dhtime#Era>'   from k_era
	--era label
	select '<http://db.itkc.or.kr/time#era'+cast(id as nvarchar)+'>', '<http://www.w3.org/2000/01/rdf-schema#label>', '"'+substring(연호,0,CHARINDEX ('(',연호))+'"@ko' from k_era
	select '<http://db.itkc.or.kr/time#era'+cast(id as nvarchar)+'>', '<http://www.w3.org/2000/01/rdf-schema#label>', '"'+substring(연호,CHARINDEX ('(',연호)+1,len(연호)-CHARINDEX ('(',연호)-1)+'"@hanja' from k_era
	--era hasBeginning year
	select '<http://db.itkc.or.kr/time#era'+cast(id as nvarchar)+'>', '<http://www.w3.org/2006/time#hasBeginning>', '<http://db.itkc.or.kr/time#year'+cast(first as nvarchar)+'>' from  k_era
	--era hasEnd year
	select '<http://db.itkc.or.kr/time#era'+cast(id as nvarchar)+'>', '<http://www.w3.org/2006/time#hasEnd>', '<http://db.itkc.or.kr/time#year'+cast(last as nvarchar)+'>' from  k_era
	--era inside year
	select '<http://db.itkc.or.kr/time#era'+cast(id as nvarchar)+'>', '<http://www.w3.org/2006/time#inside>', '<http://db.itkc.or.kr/time#year'+cast(last as nvarchar)+'>' from  k_era, 번역연호 where 서기<=last and 서기>=first 
	--era inside yearLink
	select '<http://db.itkc.or.kr/time#era'+cast(id as nvarchar)+'>', '<http://www.w3.org/2006/time#inside>', '_:yearLink'+cast(일련번호 as nvarchar) from  k_era, 번역연호 where 서기<=last and 서기>=first and  k_era.연호=번역연호.연호 and k_era.휘이름=번역연호.휘이름
	--era hasBeginning yearLjnk
	select '<http://db.itkc.or.kr/time#era'+cast(id as nvarchar)+'>', '<http://www.w3.org/2006/time#hasBeginning>', '_:yearLink'+cast(일련번호 as nvarchar) from  k_era, 번역연호 where 서기=first and  k_era.연호=번역연호.연호 and k_era.휘이름=번역연호.휘이름
	--era hasEnd yearLjnk
	select '<http://db.itkc.or.kr/time#era'+cast(id as nvarchar)+'>', '<http://www.w3.org/2006/time#hasEnd>', '_:yearLink'+cast(일련번호 as nvarchar) from  k_era, 번역연호 where 서기=first and  k_era.연호=번역연호.연호 and k_era.휘이름=번역연호.휘이름

	--쿼리문 고민...
		--era before era
		--era after era
		select '<http://db.itkc.or.kr/time#era'+cast(a.id as nvarchar)+'>', '<http://www.w3.org/2006/time#before>', '<http://db.itkc.or.kr/time#era'+cast(b.id as nvarchar)+'>' from k_era a, k_era b where a.last=b.first+1 and a.휘이름=b.휘이름 and a.id <> b.id

drop table k_emperor
	
--emperor
	create table k_emperor (id INT IDENTITY(1,1) not null, 휘이름 nvarchar(100), 묘호_한글 nvarchar(100), 묘호_한자  nvarchar(100), 시호_한글 nvarchar(100), 시호_한자 nvarchar(100), 나라_한글 nvarchar(100), 나라_한자 nvarchar(100), 지역 nvarchar(100),   first int,firstyearlink int, last int,lastyearlink int)
	insert into k_emperor select 휘이름, 묘호_한글, 묘호_한자, 시호_한글, 시호_한자, 나라_한글, 나라_한자, 지역, min(서기) as first, min(일련번호) as firstyearlink, max(서기) as last, max(일련번호) as lastyearlink from 번역연호 group by 휘이름, 묘호_한글,묘호_한자, 시호_한글, 시호_한자, 나라_한글, 나라_한자, 지역
	select * from k_emperor
	--emperor class emperor	
	select '<http://db.itkc.or.kr/time#emperor'+cast(id as nvarchar)+'>', '<http://www.w3.org/2002/07/owl#class>', '<http://dh.aks.ac.kr/ontologies/dhtime#Emperor>'   from k_emperor
	--emperor label
	select '<http://db.itkc.or.kr/time#emperor'+cast(id as nvarchar)+'>', '<http://www.w3.org/2000/01/rdf-schema#label>', '"'+substring(휘이름,0,CHARINDEX ('(',휘이름))+'"@ko' from k_emperor where 휘이름 <> 'null'
	select '<http://db.itkc.or.kr/time#emperor'+cast(id as nvarchar)+'>', '<http://www.w3.org/2000/01/rdf-schema#label>', '"'+substring(휘이름,CHARINDEX ('(',휘이름)+1,len(휘이름)-CHARINDEX ('(',휘이름)-1)+'"@hanja' from k_emperor where 휘이름 is not null
	select '<http://db.itkc.or.kr/time#emperor'+cast(id as nvarchar)+'>', '<http://www.w3.org/2000/01/rdf-schema#label>', '"'+묘호_한글+'"@ko' from k_emperor where 묘호_한글 is not null
	select '<http://db.itkc.or.kr/time#emperor'+cast(id as nvarchar)+'>', '<http://www.w3.org/2000/01/rdf-schema#label>', '"'+묘호_한자+'"@hanja' from k_emperor where 묘호_한자 is not null
	select '<http://db.itkc.or.kr/time#emperor'+cast(id as nvarchar)+'>', '<http://www.w3.org/2000/01/rdf-schema#label>', '"'+시호_한글+'"@ko' from k_emperor where 시호_한글 is not null
	select '<http://db.itkc.or.kr/time#emperor'+cast(id as nvarchar)+'>', '<http://www.w3.org/2000/01/rdf-schema#label>', '"'+시호_한자+'"@hanja' from k_emperor where 시호_한자 is not null
	--emperor hasBeginning year
	select distinct '<http://db.itkc.or.kr/time#emperor'+cast(id as nvarchar)+'>', '<http://www.w3.org/2006/time#hasBeginning>', '<http://db.itkc.or.kr/time#year'+cast(first as nvarchar)+'>' from k_emperor
	--emperor hasEnd year
	select distinct '<http://db.itkc.or.kr/time#emperor'+cast(id as nvarchar)+'>', '<http://www.w3.org/2006/time#hasEnd>', '<http://db.itkc.or.kr/time#year'+cast(last as nvarchar)+'>' from k_emperor
	--emperor inside year
	select distinct '<http://db.itkc.or.kr/time#emperor'+cast(id as nvarchar)+'>', '<http://www.w3.org/2006/time#inside>', '<http://db.itkc.or.kr/time#year'+cast(번역연호.서기 as nvarchar)+'>' from k_emperor, 번역연호 where 서기 >= first and 서기<=last 	
	--emperor hasbeginning yearLink
	select '<http://db.itkc.or.kr/time#emperor'+cast(id as nvarchar)+'>', '<http://www.w3.org/2006/time#hasBeginning>', '_:yearLink'+cast(firstyearlink as nvarchar) from  k_emperor
	--emperor hasEnd yearLink
	select '<http://db.itkc.or.kr/time#emperor'+cast(id as nvarchar)+'>', '<http://www.w3.org/2006/time#hasEnd>', '_:yearLink'+cast(lastyearlink as nvarchar) from  k_emperor
	--emperor inside yearLink
	select '<http://db.itkc.or.kr/time#emperor'+cast(id as nvarchar)+'>', '<http://www.w3.org/2006/time#inside>', '_:yearLink'+cast(일련번호 as nvarchar) from  k_emperor, 번역연호 where k_emperor.휘이름 = 번역연호.휘이름 and 일련번호 >= firstyearlink and 일련번호 <= lastyearlink
	--emperor hasBeginning era
	select '<http://db.itkc.or.kr/time#emperor'+cast(k_emperor.id as nvarchar)+'>', '<http://www.w3.org/2006/time#hasBeginning>', '<http://db.itkc.or.kr/time#era'+cast(k_era.id as nvarchar)+'>' from  k_emperor, k_era where k_emperor.firstyearlink=k_era.firstyearlink and k_era.휘이름 = k_emperor.휘이름
		
	--emperor hasEnd era
	select '<http://db.itkc.or.kr/time#emperor'+cast(k_emperor.id as nvarchar)+'>', '<http://www.w3.org/2006/time#hasEnd>', '<http://db.itkc.or.kr/time#era'+cast(k_era.id as nvarchar)+'>' from  k_emperor, k_era where k_emperor.lastyearlink=k_era.lastyearlink and k_era.휘이름 = k_emperor.휘이름


drop table k_dynasty

--dynasty
	create table k_dynasty (id INT IDENTITY(1,1) not null, 나라_한글 nvarchar(100), 나라_한자 nvarchar(100), 지역 nvarchar(100), first int, firstyearlink int, last int, lastyearlink int) 
	insert into k_dynasty select 나라_한글, 나라_한자, 지역, min(서기) as first, min(일련번호) as firstyearlink, max(서기) as last, max(일련번호) as lastyearlink from 번역연호 group by  나라_한글, 나라_한자, 지역
	--dynasty class dynasty
	select '<http://db.itkc.or.kr/time#dyansty'+cast(id as nvarchar)+'>', '<http://www.w3.org/2002/07/owl#class>', '<http://dh.aks.ac.kr/ontologies/dhtime#Dyansty>'   from k_dynasty
	--dynasty label 
	select '<http://db.itkc.or.kr/time#dyansty'+cast(id as nvarchar)+'>', '<http://www.w3.org/2000/01/rdf-schema#label>', '"'+나라_한글+'"@ko' from k_dynasty 
	select '<http://db.itkc.or.kr/time#dyansty'+cast(id as nvarchar)+'>', '<http://www.w3.org/2000/01/rdf-schema#label>', '"'+나라_한자+'"@hanja' from k_dynasty
	--dynasty nation
	select '<http://db.itkc.or.kr/time#dyansty'+cast(id as nvarchar)+'>', '<http://dh.aks.ac.kr/ontologies/dhtime#nation>', case when 지역='한국' then '"korean"^^xsd:string' when 지역='중국' then '"chinese"^^xsd:string' when 지역='일본' then '"japanese"^^xsd:string' end from k_dynasty 

	--dynasty hasBeginning year
	 select distinct '<http://db.itkc.or.kr/time#dynasty'+cast(id as nvarchar)+'>', '<http://www.w3.org/2006/time#hasBeginning>', '<http://db.itkc.or.kr/time#year'+cast(first as nvarchar)+'>' from k_dynasty
	--dynasty hasEnd year
	 select distinct '<http://db.itkc.or.kr/time#dynasty'+cast(id as nvarchar)+'>', '<http://www.w3.org/2006/time#hasEnd>', '<http://db.itkc.or.kr/time#year'+cast(last as nvarchar)+'>' from k_dynasty
	--dynasty inside year
	select distinct '<http://db.itkc.or.kr/time#dynasty'+cast(id as nvarchar)+'>', '<http://www.w3.org/2006/time#inside>', '<http://db.itkc.or.kr/time#year'+cast(번역연호.서기 as nvarchar)+'>' from k_dynasty, 번역연호 where 서기 >= first and 서기<=last 	

	--dynasty hasBeginning yearLink
	select '<http://db.itkc.or.kr/time#dynasty'+cast(id as nvarchar)+'>', '<http://www.w3.org/2006/time#hasBeginning>', '_:yearLink'+cast(firstyearlink as nvarchar) from  k_dynasty
	--dynasty hasEnd yearLink
	select '<http://db.itkc.or.kr/time#dynasty'+cast(id as nvarchar)+'>', '<http://www.w3.org/2006/time#hasEnd>', '_:yearLink'+cast(firstyearlink as nvarchar) from  k_dynasty
	--dynasty inside yearLink
	select '<http://db.itkc.or.kr/time#dynasty'+cast(id as nvarchar)+'>', '<http://www.w3.org/2006/time#hasBeginning>',  '_:yearLink'+cast(일련번호 as nvarchar) from  k_dynasty, 번역연호 where k_dynasty.나라_한자 = 번역연호.나라_한자 and 일련번호 >= firstyearlink and 일련번호 <= lastyearlink

	--dynasty hasBeginning emperor
		select distinct '<http://db.itkc.or.kr/time#dynasty'+cast(k_dynasty.id as nvarchar)+'>', '<http://www.w3.org/2006/time#hasBeginning>', '<http://db.itkc.or.kr/time#emperor'+cast(k_emperor.id as nvarchar)+'>' from k_dynasty, k_emperor where k_emperor.나라_한글=k_dynasty.나라_한글 and k_dynasty.firstyearlink = k_emperor.firstyearlink
	--dynasty hasEnd emperor
	select distinct '<http://db.itkc.or.kr/time#dynasty'+cast(k_dynasty.id as nvarchar)+'>', '<http://www.w3.org/2006/time#hasEnd>', '<http://db.itkc.or.kr/time#emperor'+cast(k_emperor.id as nvarchar)+'>' from k_dynasty, k_emperor where k_emperor.나라_한글=k_dynasty.나라_한글 and k_dynasty.lastyearlink = k_emperor.lastyearlink
	--dynasty inside emperor
		select distinct '<http://db.itkc.or.kr/time#dynasty'+cast(k_dynasty.id as nvarchar)+'>', '<http://www.w3.org/2006/time#inside>', '<http://db.itkc.or.kr/time#emperor'+cast(k_emperor.id as nvarchar)+'>' from k_dynasty, k_emperor where k_emperor.나라_한자=k_dynasty.나라_한자 

	
--period
select 시대_한자, 시대_한글 from 번역연호 group by 시대_한글,  시대_한자

	select * from 번역연호 
	select * from k_dynasty where 지역='일본'

	select t, count(*) from (select distinct '<http://db.itkc.or.kr/time#dynasty'+cast(k_dynasty.id as nvarchar)+'>' as s, '<http://www.w3.org/2006/time#inside>' as r,  '<http://db.itkc.or.kr/time#emperor'+cast(k_emperor.id as nvarchar)+'>' as t from k_dynasty, k_emperor where k_emperor.나라_한글=k_dynasty.나라_한글 and k_emperor.나라_한자 = k_dynasty.나라_한자) a group by t order by count(*) desc

	select * from k_dynasty