Dunk

*/
/*設定初始資料*/
use tempdb
if exists(select * from sys.tables where name=N'BRANCH')
	drop table BRANCH

if exists(select * from sys.tables where name=N'NULLTEST')
	drop table NULLTEST

if exists(select * from sys.tables where name=N'NULLTESTJOIN')
	drop table NULLTESTJOIN

if exists(select * from sys.tables where name=N'FUNCTIONTEST')
	drop table FUNCTIONTEST


create table [BRANCH]
(
	BRNO int primary key not null,
	BRNAME varchar(30) not null
)

insert into BRANCH 
select 0501,'資訊處' union
select 2000,'台北大樓'

select * from BRANCH


create table [NULLTEST]
(
	EmpID char(6) primary key not null,
	Salary decimal(12,2)
)

select * from NULLTEST


create table [NULLTESTJOIN]
(
	EmpID char(6) primary key not null,
	Salary decimal(12,2)
)

select * from NULLTESTJOIN


create table [FUNCTIONTEST]
(
	EmpID char(6) primary key not null,
	Salary decimal(12,2)
)

insert into FUNCTIONTEST
select '000001',100 union
select '000002',200 union
select '000003',null 

select * from FUNCTIONTEST
/*設定初始資料完畢*/

--測試SQL
--出現'---select all---'選項
select -1 as BRNO, '---Select All--' as BRNAME
union
select BRNO,BRNAME from BRANCH

--產生NULL的方式
--1. insert null 值
insert into NULLTEST(EmpID,Salary) values('155953',null)
select * from NULLTEST
--2. insert時不指定欄位值,預設會null
insert into NULLTEST(EmpID) values('155952')
select * from NULLTEST
--3. 用left join , right join , full join
select * 
from NULLTEST a
left outer join NULLTESTJOIN b
on a.EmpID = b.EmpID

select * 
from NULLTEST a
right outer join NULLTESTJOIN b
on a.EmpID = b.EmpID

select * 
from NULLTEST a
full join NULLTESTJOIN b
on a.EmpID = b.EmpID
--4.加入新欄位
alter table NULLTEST add  c1 decimal
select * from NULLTEST

--測試常用函數(function)
--當欄位有null值時,count及avg函數將會排除運算
select sum(Salary) as sum_Salary,
max(Salary) as max_Salary,
min(Salary) as min_Salary,
count(Salary) as count_Salary,
avg(Salary) as avg_Salary
from FUNCTIONTEST

--如果avg要加入null值當分母運算
select avg(isnull(Salary,0)) as avg_Salary
from FUNCTIONTEST

--如果count要加入null值計算
select count(*) as count_Salary
from FUNCTIONTEST

/*----------------------------------------------------------*/
/*刪除測試資料*/
if exists(select * from sys.tables where name=N'BRANCH')
	drop table BRANCH

if exists(select * from sys.tables where name=N'NULLTEST')
	drop table NULLTEST

if exists(select * from sys.tables where name=N'NULLTESTJOIN')
	drop table NULLTESTJOIN

if exists(select * from sys.tables where name=N'FUNCTIONTEST')
	drop table FUNCTIONTEST