/**********************************************************************************************
-- Title : 다양한 표현식 사용 예(데이터형, 함수, 기타)
-- Reference : 웹검색
-- Key word : float, datetime, sql_varient, text, ...
**********************************************************************************************/

/*
** SQL 부동 소수점을 사용 시 원하지 않는 결과 
*/
Create Table #TestFloat
(xNumeric Numeric(7,3)
,xDecimal Decimal(7,3)
,xFloat   Float
,xReal    Real)
GO

INSERT #TestFloat VALUES(3,3,3,3)
INSERT #TestFloat VALUES(12.3,12.3,12.3,12.3)

SELECT * FROM #TestFloat
GO 

UPDATE #TestFloat 
SET xNumeric = xNumeric/3, xDecimal = xDecimal/3, xFloat   = xFloat/3,   xReal = xReal/3

SELECT * FROM #TestFloat
GO
 

/*
--SQL 날짜 및 시간 데이터형 사용
*/
Create Table #TestDateType
(Date1 smalldatetime
,Date2 datetime
,Date3 datetime)

INSERT #TestDateType VALUES('1999'     ,'1999 SEP'   ,'1999-09-09')
INSERT #TestDateType VALUES('9/10/1998','1999.09.10' ,'September  10, 1999')
INSERT #TestDateType VALUES('19990911' ,'SEP 1999 11','19990911   14:14:14:140')
INSERT #TestDateType VALUES('19990912' ,'09-12-1999' ,'1999-09-12 15:15:15:150')
INSERT #TestDateType VALUES(GetDate()  ,GetDate()+1  ,DateAdd(month,1,GetDate()))

SELECT * FROM #TestDateType
GO

-- 위의 결과를 보면 년도만 대입한 경우는 1월 1일로 대입이 되며 년월만 대입한 경우는 
-- 1일로 기록된다.
-- 또, GetDate() 함수를 사용하여 현재 시간을 대입하고 date 데이트형에 +, - 연산을 수행하면 
-- 일 단위를 기준으로 계산하게 된다.
 

/*
** SQL Sql_variant 데이터형의 사용
*/
CREATE TABLE Objects
    (ObjectID      int PRIMARY KEY IDENTITY,
    ObjectName     nvarchar(80)
    )

CREATE TABLE ObjectsProperty
    (ObjectID      int REFERENCES Objects(ObjectID),
    PropertyName   nvarchar(100),
    PropertyValue  sql_variant,
    CONSTRAINT PK_ObjectsProperty
                    PRIMARY KEY(ObjectID, PropertyName)
    )

DECLARE @ObjectID int

INSERT Objects VALUES('책') 
SET @ObjectID = @@IDENTITY
INSERT ObjectsProperty VALUES(@ObjectID,'저자','우철웅')
INSERT ObjectsProperty VALUES(@ObjectID,'출판사','대림')
INSERT ObjectsProperty VALUES(@ObjectID,'판매가',15000) 

INSERT Objects VALUES('필기구') 
SET @ObjectID = @@IDENTITY
INSERT ObjectsProperty VALUES(@ObjectID,'구분','수성')
INSERT ObjectsProperty VALUES(@ObjectID,'색상','파랑')
INSERT ObjectsProperty VALUES(@ObjectID,'굵기',0.5) 

SELECT * FROM Objects, ObjectsProperty WHERE Objects.ObjectID = ObjectsProperty.ObjectID
 

/*
** SQL table 데이터형의 사용
*/
DECLARE @TableVar TABLE
  (ID int PRIMARY KEY,
   Name char(10))

INSERT INTO @TableVar VALUES (1, '권미숙')
INSERT INTO @TableVar VALUES (2, '권영아')

SELECT * FROM @TableVar

UPDATE @TableVar SET Name = '권미달' WHERE ID =1
SELECT * FROM @TableVar

DELETE @TableVar WHERE ID = 1
SELECT * FROM @TableVar
GO
 

/*
** SQL Uniqueidentifier 데이터형의 사용
*/
CREATE TABLE MyUniqueTable    
(UniqueID UNIQUEIDENTIFIER DEFAULT NEWID(),    Characters char(10))
GO

INSERT INTO MyUniqueTable(Characters) VALUES ('abc')
INSERT INTO MyUniqueTable VALUES (NEWID(), 'def')
GO
 

/*
** SQL Timestamp 데이터형의 사용
*/
Create Table TestTimeStamp1
(ID          int PRIMARY KEY IDENTITY,
TimeStamp    Timestamp)

Create Table TestTimeStamp2
(ID          int PRIMARY KEY IDENTITY,
WorkUser     Char(10),
WorkDatetime Datetime DEFAULT GETDATE(),
TimeStamp    Timestamp)

INSERT TestTimeStamp2(WorkUser) Values('우철웅')
INSERT TestTimeStamp2(WorkUser) SELECT WorkUser FROM TestTimeStamp2
INSERT TestTimeStamp2(WorkUser) SELECT WorkUser FROM TestTimeStamp2

INSERT TestTimeStamp1 DEFAULT VALUES

INSERT TestTimeStamp2(WorkUser) SELECT WorkUser FROM TestTimeStamp2

SELECT * FROM TestTimeStamp1
SELECT * FROM TestTimeStamp2
GO
 

/*
** SQL text 데이터형에서 지원되지 않는 일부 문자열 함수와 SUBSTRING 함수 사용 시 문제
*/
CREATE TABLE TestText1 (xChar varchar(500), xText text)

INSERT TestText1 VALUES('우리는 민족 중흥의','우리는 민족 중흥의')

SELECT LEFT(xChar,4) FROM TestText1
GO

SELECT LEFT(xText,4) FROM TestText1
GO

SELECT SUBSTRING(xChar,1,1), SUBSTRING(xChar,2,2) FROM TestText1
SELECT SUBSTRING(xText,1,1), SUBSTRING(xText,2,2) FROM TestText1
GO
 

/*
** SQL varchar와 text 데이터형의 메모리 공간 차지의 비교 
*/
USE TempDB
GO

CREATE TABLE TestVarchar(aa varchar(8000))
CREATE TABLE TestText(aa text)
GO

DECLARE @i int
SET @i =0
WHILE @i <10000
     BEGIN
 INSERT TestText VALUES(replicate('A',100))
 INSERT TestVarchar VALUES(replicate('A',100))
 SET @i = @i +1
     END
GO

SELECT * FROM TestText
SELECT * FROM TestVarchar
GO

SELECT DB_ID(), OBJECT_ID('TestVarchar'),OBJECT_ID('TestText')
GO

DBCC MEMUSAGE  -- 2000에서 더이상 지원하지 않고 성능 모니터 카운터에 대한 참조로 변경 요함.
GO
 

 
/*
** SQL 물리적인 공간의 사용을 살펴보면 text 데이터형이 2배 정도 많이 사용하는 것을 알 수 있다.
*/
exec sp_spaceused TestVarchar
exec sp_spaceused TestText
GO
 

/*
** SQL 숫자형의 묵시적인 형 변환
*/
SELECT 4/3,  4/3.0
GO
 

/*
** SQL 숫자형 문자와 숫자의 연산에서 숫자형 문자는 숫자로 형 변환되며
** & 는 Bitwise AND 연산자이므로 비트연산 외에는 사용하지 않아야 한다.
*/
SELECT '1' + 2,    '1' & 2,     '1' + '2'
GO

-- SQL NULL과의 연산은 항상 NULL이다.
SELECT '1' + NULL, 1 + NULL, 'A' + NULL
GO
 

/*
** SQL 문자열이 숫자 문자열이 아닌 경우 에러 발생
*/
SELECT 'A' + 1
GO
 

/*
** SQL 문자형 문자와 숫자의 연산에서는 숫자를 먼저 문자로 바꾸는 작업을 해야 한다.
*/
SELECT 'A' + CONVERT(varchar(10),1) , 'A' + STR(1)
GO
 

/*
** SQL CAST와 CONVERT 함수의 사용
*/
SELECT CAST(11.11   AS char(20)), CONVERT(varchar(20), 11.11)
      ,CAST(GetDate() AS char(20)), CONVERT(varchar(20), GetDate())
      ,CAST('1234'    AS int      ), CONVERT(smallint   , '1234')
GO
 

/*
** SQL CONVERT 함수의 사용
*/
SELECT CONVERT(varchar(8), GetDate(),112), CONVERT(varchar(10), GetDate(),101)
      ,CONVERT(smalldatetime, '20000505'), CONVERT(smalldatetime, '05/05/2000',101)
GO
 

/*
** SQL 지정한 자릿수를 넘는 변환에 대해서는 * 문자로 표현된다.
*/
SELECT STR(3), STR(123.45, 6, 1), STR(1234567.89, 6, 2)
GO
 

/*
** SQL CAST와 CONVERT의 사용
*/
USE pubs
GO

SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales
FROM titles
WHERE CAST(ytd_sales AS char(20)) LIKE '3%'
GO

SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales
FROM titles
WHERE CONVERT(char(20), ytd_sales) LIKE '3%'
GO
 

/*
** SQL CAST의 산술 연산
*/
SELECT CAST(ROUND(ytd_sales/price, 0) AS int) AS 'Copies'
FROM titles WHERE CAST(ROUND(ytd_sales/price, 0) AS int)>300
GO
 

/*
** SQL CAST로 문자형 바꾼 후 문자연결
*/
SELECT 'The price is ' + CAST(price AS varchar(12))
FROM titles
WHERE price > 20.00
GO
 

/*
** SQL 절상, 절삭에 대한 사용 예
*/
SELECT CEILING(11.55), CEILING(11.11), FLOOR(11.11), FLOOR(11.55)
GO
 

/*
** SQL 지정된 길이로 반올림 
*/
SELECT ROUND(11.22, 1), ROUND(1111.2, -2), SQUARE(5), SQRT(25)
GO
 

/*
** SQL 0 과 100 사이의 난수표 발생
*/
SELECT CONVERT(int, (RAND() *100))
GO

 
 
/*
** SQL LOWER, UPPER, REPLICATE, SPACE 함수 사용
*/
SELECT LOWER('Test'), UPPER('Test'), REPLICATE('OK',4), '대한' + SPACE(5) + '민국'
GO
 

/*
** SQL 한글, 영문 구분 없이 문자수로 계산함으로 주의할 필요가 있다.
*/
SELECT LEFT(LTRIM('  Korea  '), 2), SUBSTRING('Korea', 3, 1), RIGHT(RTRIM('  Korea  '), 2)
GO
 

/*
** SQL 대부분의 문자 함수는 한글, 영문 구분 없이 문자수로 계산함으로 주의할 필요가 있다.
*/
SELECT LEFT('한국_Korea', 4), SUBSTRING('한국_Korea', 2, 3), RIGHT('Korea_한국', 4)
GO
 

/*
** SQL 문자 열 대치
*/
SELECT REPLACE('대한민국만세', '민국', '국민' ), STUFF('대한민XX국만세', 3, 4, '국민')
GO
 

/*
* SQL LEN은 문자 수를 DATALENGTH는 바이트 수를 반환한다.
*/
SELECT LEN('korea'), LEN('대한'), DATALENGTH('대한'), DATALENGTH('Korea대한')
GO
 

/*
** SQL SOUNDEX, DIFFERENCE는 한글에 대해 처리하지 못한다. SOUNDEX는 SOUNDEX 코드 반환 
*/
SELECT SOUNDEX('mouth'),SOUNDEX('입')
GO
 

/*
** SQL DIFFERENCE의 사용, 일치도가 높으면 4, 낮으면 0, 한글에 대해 지원 안됨
*/
SELECT DIFFERENCE('can','con'), DIFFERENCE('can','can'), DIFFERENCE('형상','문자')
GO
 

/*
** SQL CHARINDEX와 PATINDEX 사용
*/
USE pubs 
GO
SELECT CHARINDEX('com', title),PATINDEX('%com%',title),title FROM titles
WHERE title_id LIKE 'B%' 
Go

/*
** SQL PATINDEX의 패턴 구문 사용 
*/
SELECT PATINDEX('%c[a-k]n%',notes),LEFT(notes,60) FROM titles
WHERE title_id LIKE 'B%' 
GO
 

/*
** SQL 날짜를 더한다. 0.5면 12시간으로 환산된다.
*/
SELECT GETDATE(), GETDATE()-1, GETDATE()+0.5
GO
 

/*
** SQL @@NOCOUNT 함수로 마지막 적용된 행 수를 파악하여 다음 구문에서 처리한다.
*/
UPDATE Pubs.dbo.authors SET au_lname = 'Jones'
WHERE au_id = '999-888-7777'

IF @@ROWCOUNT = 0
   RAISERROR ('수정에 해당하는 자료가 없습니다.',16,1)
GO
 

/*
** SQL @@Error로 마지막 작업의 에러 번호를 조회하여 다음 구문에서 사용한다.
*/
UPDATE Pubs.dbo.authors SET au_id = '172 32 1176'
WHERE au_id = '172-32-1176'
IF @@Error <> 0 
SELECT au_id, au_lname, au_fname   FROM Pubs.dbo.authors
GO
 

/*
** SQL @@TRANCOUNT 함수로 중첩 트랜잭션의 수를 파악하여 작업한다.
*/
BEGIN TRANSACTION
    UPDATE Pubs.dbo.authors SET au_lname = UPPER(au_lname) 
    WHERE au_lname = 'White'
SELECT @@TranCount

BEGIN TRANSACTION
    UPDATE Pubs.dbo.authors SET au_lname = LOWER(au_lname) 
    WHERE au_lname = 'White'
SELECT @@TranCount
 

/*
** SQL ISDATE, ISNUMERIC 함수의 사용
*/
SELECT ISDATE('1999-9-9'), ISDATE('2000-20-22') , ISDATE('20001001')
SELECT ISNUMERIC(55), ISNUMERIC('55'), ISNUMERIC('5A')
GO
 

/*
-- SQL ISNULL 함수의 사용, 가능한 Null이 입력되지 않도록 기본값을 설정하며, 
-- 이미 들어간 경우에 대치하여 조회할 수 있다.  
-- Null 데이터는 클라이언트 프로그램 구현 시 매번 Null인지 확인해야 하기 때문이다. 
*/
SELECT ISNULL(NULL,'A'),ISNULL(3,'A')
SELECT title_id,ISNULL(price,0),ISNULL(notes,'No Comment') FROM Pubs.dbo.titles
GO
 

/*
** SQL NULLIF 함수, 비교 두 값이 같으면 NULL을 반환, 아니면 첫번째 값을 반환 
*/
SELECT NULLIF(10,10), NULLIF('AA','AA'), NULLIF(10, 20)
GO
 

/*
-- SQL CURRENT_TIMESTAMP와 GETDATE()는 같으며 @@DBTS는 timestamp 데이터형에 사용한 마지막 값이다.  
-- USER_NAME(1)은 1번 프로세스 ID의 사용자명이다.
*/
SELECT APP_NAME(), CURRENT_TIMESTAMP, GETDATE(), @@DBTS
SELECT HOST_ID(), SESSION_USER, USER_NAME(1), SYSTEM_USER
GO
 

/*
-- SQL 데이터와 데이터 상세 관계에서 Identity 속성의 사용
-- 주의점은 @@Identity에 대한 참조는 Identity 값이 부여된 다음에 다른 작업이 수행되면 사라지기 
-- 때문에 한번의 사용에 대해서는 직접 @@Identity를 사용하여도 되지만 여러 번 사용할 경우에는 
-- 변수에 대입하여 사용하여야 한다.
*/
Use TempDB
GO
CREATE TABLE InHeads
    (SlipNo     int PRIMARY KEY IDENTITY(1000,1),
    Date        smalldatetime,
    CustomerID  int
    )
CREATE TABLE InDetails
    (SlipNo     int REFERENCES InHeads(SlipNo),
    Seq         int,
    ProductID   char(5),
    Price       money,
    Qty         int,
    CONSTRAINT PK_InDetials
                PRIMARY KEY(SlipNO, Seq)
    )
GO

DECLARE @SlipNo int
INSERT InHeads VALUES(Convert(char(8),GETDATE(),112),5) 
SET @SlipNo = @@IDENTITY
INSERT InDetails VALUES(@SlipNo,1,'A0017',500,6)
INSERT InDetails VALUES(@SlipNo,2,'B0019',200,12)
INSERT InDetails VALUES(@SlipNo,3,'A0027',1500,1)
GO

DECLARE @SlipNo int
INSERT InHeads VALUES(Convert(char(8),GETDATE(),112),4) 
SET @SlipNo = @@IDENTITY
INSERT InDetails VALUES(@SlipNo,1,'C0017',1500,6)

GO
SELECT * FROM InDetails
 

/*
** SQL Identity Property에 강제로 데이터 넣기, SET IDENTITY_INSERT table_name ON 구문 사용
*/
CREATE TABLE Products (
ProductID int IDENTITY(10000, 1) Primary Key,
ProductName CHAR(20) )
GO

INSERT Books VALUES('장길산')
INSERT Books(BookName) VALUES('동의보감')
GO

--SET IDENTITY_INSERT ~ ON으로 새로운 로우의 삽입시는 반드시 컬럼명을 명시해 주어야 한다. 
SET IDENTITY_INSERT Books ON           
INSERT Books(BookID, BookName) VALUES(9999, '태백산맥')
 

/*
-- SQL IDENTITY 함수를 이용한 Identity 컬럼의 생성, 반드시 INTO 구문으로 수행하여야 한다.
-- IDENTITY 함수는 임시 테이블이나 실 테이블에 적용할 수 있다.
*/
SELECT IDENTITY(int,1,1) as Seq,stor_id,qty INTO #Sales FROM Pubs.dbo.Sales
SELECT * FROM #Sales
GO
 

/*
** SQL CASE 함수의 사용
*/
SELECT stor_id, ord_num, ord_date, qty, 
    Class = Case When (Qty<20) then 'Small' 
                 When (Qty>40) then 'Large'
                 Else 'Middle' End
FROM Pubs.dbo.Sales
GO
 

/*
** SQL CAST와 CONVERT의 사용
*/
SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales
FROM Pubs.dbo.titles
WHERE CAST(ytd_sales AS char(20)) LIKE '3%'
GO 

SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales
FROM Pubs.dbo.titles
WHERE CONVERT(char(20), ytd_sales) LIKE '3%'
GO
 

/*
** SQL Coalesce 시스템 함수 사용 
*/
Use Tempdb
GO
CREATE TABLE wages
(
   emp_id      tinyint    identity,
   hourly_wage   decimal   NULL,
   salary      decimal    NULL,
   commission   decimal   NULL,
   num_sales   tinyint   NULL
)
GO

INSERT wages VALUES(10.00, NULL, NULL, NULL)
INSERT wages VALUES(20.00, NULL, NULL, NULL)
INSERT wages VALUES(30.00, NULL, NULL, NULL)
INSERT wages VALUES(NULL, 10000.00, NULL, NULL)
INSERT wages VALUES(NULL, 20000.00, NULL, NULL)
INSERT wages VALUES(NULL, 30000.00, NULL, NULL)
INSERT wages VALUES(NULL, NULL, 15000, 3)
INSERT wages VALUES(NULL, NULL, 25000, 2)
INSERT wages VALUES(NULL, NULL, 14000, 4)
GO

SET NOCOUNT OFF
GO

SELECT emp_id, CAST(COALESCE(hourly_wage * 40 * 52, 
   salary, 
   commission * num_sales) AS money) AS 'Total Salary' 
FROM wages
GO
 

/*
** SQL 파라메터를 가진 시스템 메시지의 추가와 FORMATMESSAGE 함수 사용
*/
sp_addmessage 50006, 16, '%s 테이블에 ID는 %1d이 삭제 되었습니다.'  

DECLARE @var1 VARCHAR(100)

SELECT @var1 = FORMATMESSAGE(50006, 'Sales', 55)
SELECT @var1
GO
 

/*
-- SQL GETANSINULL 함수를 통하여 지정한 데이터베이스에 ANSINULL이 활성화 되어 있는지 확인.
-- ANSI NULL DEFAULT가 설정되어 있다면 1 이 반환되고, 설정되어 있지 않다면 0 이 반환된다.
*/
SET ANSI_NULL_DFLT_ON  OFF 
SELECT GETANSINULL('pubs')
GO

SET ANSI_NULL_DFLT_ON ON
SELECT GETANSINULL('pubs')
GO
 

/*
** SQL NEWID 함수의 사용법, uniqueidentifier 데이터형에 기본값으로 설정
*/
SELECT NEWID()
SELECT NEWID()
GO

CREATE TABLE TestNewID
(id int, GUID uniqueidentifier DEFAULT NEWID())
GO

INSERT TestNewID(id) Values(1)
INSERT TestNewID(id) Values(2)

SELECT * FROM TestNewID
GO
 

/*
** SQL PARSENAME 함수로 오브젝트명의 지정한 부분의 오브젝트명을 조회 
*/
SELECT  PARSENAME('pubs.dbo.authors', 1) AS 'Object Name' ,
        PARSENAME('pubs.dbo.authors', 2) AS 'Owner Name' , 
        PARSENAME('pubs.dbo.authors', 3) AS 'Database Name' , 
        PARSENAME('pubs.dbo.authors', 4) AS 'Server Name'
Go
 

/*
** SQL DBCC PINTABLE 구문의 파라메터를 위해 @OBJECT_ID와 @DB_ID 함수를 이용
*/
DECLARE @db_id int, @tbl_id int
USE pubs
SET @db_id = DB_ID('pubs')
SET @tbl_id = OBJECT_ID('pubs..authors')
DBCC PINTABLE (@db_id, @tbl_id)
 

/*
-- SQL pubs 데이터베이스의 pr_info 테이블의 텍스트 데이터를 시작 포인터를 binary16자리 
-- 얻어 UPDATETEXT 구문으로 일부 위치의 데이터를 수정한다. 
-- UPDATETEXT pub_info.pr_info @ptrval 10 2 '__AA__' 구문은
-- 받은 포인트로부터 10 번째 바이트의 두자리를 __AA__ 로 대치하는 작업을 한다. 
*/
USE pubs
GO

EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'
GO

SELECT pr_info 
   FROM pub_info WHERE pub_id = '0877'

DECLARE @ptrval binary(16)

SELECT @ptrval = TEXTPTR(pr_info) 
   FROM pub_info WHERE pub_id = '0877'

UPDATETEXT pub_info.pr_info @ptrval 10 2 '__AA__'

SELECT pr_info 
   FROM pub_info WHERE pub_id = '0877'
GO
 

/*
-- SQL 변수 @MyCounter에 값을 대입하는 방법은 2가지가 있다.  
-- 첫번째는 SET 문을 사용하여 변수에 값을 대입할 수 있다.  
-- 둘째는 SELECT 문을 사용하여 변수에 값을 대입 할 수 있다.
*/
DECLARE @MyCounter INT
SET @MyCounter = 1

-- 또는
DECLARE @MyCounter INT
SELECT  @MyCounter = 1
 

/*
** ANY의 사용
*/
CREATE TABLE #A (ID int)
INSERT #A VALUES(1)
INSERT #A VALUES(7)
INSERT #A VALUES(9)

CREATE TABLE #T (ID int)
INSERT #T VALUES(4)
INSERT #T VALUES(6)
INSERT #T VALUES(8)
 
SELECT * FROM #A WHERE ID > ANY ( SELECT ID FROM #T)