寫過的SQL

--select * from Object

--select Title , Address from Object where Title like '%夜市%'

--select top 10 * from Object order by OID desc

--select Title, Address from Object where Title like '%埔里酒廠%'

--select * from Object where OID<10 order by OID desc

--select * from Object where Description like '%埔里% '

--select Title,Address 

--from Class C , CO ,Object O 

--where C.CName = '埔里鎮' and C.CID=CO.CID and CO.OID=O.OID

--取出南投縣的全部鄉鎮景點資訊 (87 tuples)

--select C1.CName,C2.CName,O.Address,O.Title,O.Class

--from Class C1, Inheritance I, Class C2, Object O,CO

--where C1.CName= '南投縣' and C1.CID=I.PCID and I.CCID=C2.CID and C2.CID=CO.CID and CO.OID=O.OID

--以… union …取出埔里、魚池的全部景點資訊 (27 tuples)

--select C.CName '鄉鎮',Title '景點',Address '地址'

--from Class C ,CO ,Object O

--where C.CName='埔里鎮' and C.CID=CO.CID and CO.OID=O.OID

--union

--select C.CName ,Title ,Address

--from Class C ,CO, Object O

--where C.CName='魚池鄉' and C.CID=CO.CID and CO.OID=O.OID

--取出南投縣的全部鄉鎮景點資訊 (87 tuples) and 分類

--select C2.CName,count(*)

--from Class C1, Inheritance I, Class C2, Object O,CO

--where C1.CName= '南投縣' and C1.CID=I.PCID and I.CCID=C2.CID and C2.CID=CO.CID and CO.OID=O.OID

--group by C2.CName 

--order by count(*) desc

--用鄉鎮分類

--select C2.CName,O.Title,O.Address

--from Class C1, Inheritance I, Class C2, Object O,CO

--where C1.CName= '南投縣' and C1.CID=I.PCID and I.CCID=C2.CID and C2.CID=CO.CID and CO.OID=O.OID

--order by C2.CName

--IN的用法

--  select Title,Address

--from  Object

--where OID  in (select OID from Object where Address like '埔里鎮%')

--NOT IN的用法

/*select O.OID,O.Title,O.Address

from Class C1, Inheritance I, Class C2, Object O,CO

where C1.CName= '南投縣' and C1.CID=I.PCID and I.CCID=C2.CID and C2.CID=CO.CID and CO.OID=O.OID

union

select O2.OID,O2.Title,O2.Address from Object O2 where O2.Telephone like '049-%' and O2.OID not in 

(

select O.OID

from Class C1, Inheritance I, Class C2, Object O,CO

where C1.CName= '南投縣' and C1.CID=I.PCID and I.CCID=C2.CID and C2.CID=CO.CID and CO.OID=O.OID

)*/

--EXIST

/*select *

from Class C2,Inheritance I

where exists (select * from Class C1 where C1.CName ='南投縣' and C2.CID=I.PCID and I.PCID=C1.CID )*/

/*select max(nClick),min(nClick)

from Object*/

--select * from Object where nClick in (select max(nClick) from Object)

--取出

--select Class,sourceURL ,max(nClick)'max',min(nClick)'min',avg(nClick)'avg',sum(nClick)'sum' from Object group by Class ,sourceURL

--取出02開頭的電話的 (112個) 景點

/*select Title , Address, Telephone  from Object where Telephone like '02-%'*/

--取出電話非數字開頭的 (23個) 景點

--select Title , Address, Telephone  from Object where Telephone not like '[0-9]%'

--取出Object OID 不在 CO 的OID裡面

--select OID, Title from Object where OID not in (select distinct OID from CO)

--取出 CID 不在 Inheritance裡的

select distinct CID,CName from Class where CID not in (select distinct CCID from Inheritance union select distinct PCID from Inheritance)

--select * from dbo.Feb order by 交易日期 ASC

/*CREATE TABLE [dbo].[Feb](

[識別碼] [int] NOT NULL,

[交易日期] [nvarchar](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,

[商品代號] [nvarchar](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,

[交割年月] [nvarchar](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,

[成交時間] [float] NULL,

[成交價格] [float] NULL,

[成交數量(B+S)] [float] NULL,

[近月價格] [nvarchar](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,

[遠月價格] [nvarchar](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL

) ON [PRIMARY]

insert into Feb 

select * from dbo.Feb1  

union

select * from dbo.Feb2 

union

select * from dbo.Feb3*/

insert into dbo.April

select 識別碼,交易日期,商品代號,交割年月,成交時間,成交價格,[成交數量(B+S)],近月價格,遠月價格

from dbo.[20100401-09] 

union

select 識別碼,交易日期,商品代號,交割年月,成交時間,成交價格,[成交數量(B+S)],近月價格,遠月價格

from dbo.[20100412-19]

union

select 識別碼,交易日期,商品代號,交割年月,成交時間,成交價格,[成交數量(B+S)],近月價格,遠月價格 

from  dbo.[20100420-30] order by 交易日期 asc