2018년 8월 10일 (금) 06:42에 대한 Hiblue의 편집

2018-08-10T06:42:13Z
Hiblue: Hiblue 사용자가 ITCK convert sql 문서를 ITKC convert sql 문서로 옮겼습니다

2018-08-10T05:43:05Z
Hiblue 사용자가 ITCK convert sql 문서를 ITKC convert sql 문서로 옮겼습니다
Hiblue: 새 문서:
 use hiblue select * from 번역연호 --year  	--year class year 	select distinct '', '
2018-08-10T05:38:28Z
새 문서: <pre> 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...
새 문서
<pre>

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



</pre>
← 이전 판	2018년 8월 10일 (금) 05:43 판
(차이 없음)
ITKC convert sql - 편집 역사

2018년 8월 10일 (금) 06:42에 대한 Hiblue의 편집

Hiblue: Hiblue 사용자가 ITCK convert sql 문서를 ITKC convert sql 문서로 옮겼습니다
