D20 - EX06 (DECLARE , IF - ELSE, CASE - WHEN, WHILE- BREAK, GOTO, TRY - CATCH ,SQL SYSTEM FUNCTIONs)

-- 10/7일(교제 6장)
--Northwind 데이터베이스 컨텍스트로 변경
USE Northwind

--1. 변수 선언 및 할당

DECLARE @data int   --변수 선업 : DECLARE @변수이름 자료형
SET @data = 100 --값 할당 : SET @변수이름 = 값

PRINT @data

--2. 자료형
DECLARE @str char(10)
DECLARE @str2 varchar(10)

SET @str = 'hello'  -- 공백을 더한 10개 크기의 문자열
SET @str2 = 'hello' -- 5개 크기의 문자열

SELECT @str + 'next string', @str2 + 'nest string'

 

--3. IF - ELSE
DECLARE @myage int
SET @myage = 30

IF @myage >= 20
BEGIN -- 프로그램의 시작 중괄호
 PRINT N'환영합니다.'   -- 앞에 N 을 붙여줘야하다(유니코드 표시)
END   -- 프로그램의 종료 중괄호
ELSE
BEGIN
 PRINT N'미성년자 입니다.'
END

-- B
SELECT * FROM Employees
/*
DECLARE @hiredate datetime

--SET @hiredate =
--(
-- SELECT hiredate FROM Employees WHERE EmployeeID = 1
--)

SELECT @hiredate = hiredate FROM Employees WHERE EmployeeID = 1

SELECT @hiredate
*/
----------------
-- B. 특정 직원의 근속 일수 계산해서 10년 이상된 직원을

DECLARE @hiredate datetime
SELECT @hiredate = hiredate FROM Employees WHERE EmployeeID = 1
DECLARE @today datetime
SET @today = GETDATE() -- GETDATE() 현재시작을 반환하는 함수

DECLARE @duration int
SET @duration = DATEDIFF(DAY, @hiredate, @today) -- DATEDIFF 기분 일 월 차이 알아내는것
DECLARE @years int
SET @years = DATEDIFF(YEAR, @hiredate, @today)

PRINT N'근속일수는 ' + CONVERT(nvarchar, @duration) -- CONVERT 데이터 타입 변환
IF @years >= 10
BEGIN
 PRINT CONVERT(nvarchar, @years) + N'년 장기근속자 입니다.'
END
ELSE
BEGIN
 PRINT N'장기근속자가 아닙니다.'
END

--4. CASE - WHEN
--A
DECLARE @data4 int
SET @data4 = 85
DECLARE @grade char(1)

SET @grade =
CASE
 WHEN @data4 >= 90 THEN 'A'
 WHEN @data4 >= 80 THEN 'B'
 WHEN @data4 >= 70 THEN 'C'
 WHEN @data4 >= 60 THEN 'D'
 ELSE 'A'
END
SELECT @grade

--B
SELECT
 c.customerid, c.companyname,
 --o.OrderID, o.orderdate,
 --od.Quantity, od.UnitPrice
 SUM(od.Quantity * od.UnitPrice) 주문총액,
 CASE
  WHEN SUM(od.Quantity * od.UnitPrice) < 10000 THEN N'보통고객'
  WHEN SUM(od.Quantity * od.UnitPrice) < 100000 THEN N'우수고객'
  ELSE N'VIP'
 END 고객구분
FROM
 Customers c
 INNER JOIN Orders o
 ON c.CustomerID = o.CustomerID
 INNER JOIN [Order Details] od
 ON o.OrderID = od.OrderID
GROUP BY c.CustomerID, c.CompanyName
ORDER BY
 c.CustomerID

-- 5. WHILE, BREAK
-- A
DECLARE @sum int = 0
DECLARE @index int = 1

WHILE @index <= 100
BEGIN
 SET @sum = @sum + @index
 SET @index = @index + 1
 
 CONTINUE -- 반복문의 처음 위치로 이동
 
 IF @index = 50
 BEGIN
  BREAK -- 반복문을 중단
 END  
END

SELECT @sum

--6. GOTO

DECLARE @opt int = 1

IF( @opt = 1)
BEGIN
 GOTO first
END
ELSE
BEGIN
 GOTO second
END
 
first:
 PRINT N'첫번째 실행문'
 GOTO last
second:
 PRINT N'두번째 실행문'
last:
 PRINT N'마지막 실행문'
 
-- DOTNET6TH 데이터베이스 컨텍스트로 변경
-- 7. TRY - CATCH (SQL SERVER 2K5 or LATER ver 이상만 가능)
USE DOTNET6TH
sp_tables contact
sp_help contact

BEGIN TRY
 INSERT INTO contact VALUES (N'이상규', '010-1234-6978', 'lsk@ex.com')
 PRINT N'쿼리가 정상적으로 실행되었습니다.'
END TRY
BEGIN CATCH
 PRINT N'쿼리 실행중 오류가 발생했습니다.'
END CATCH

SELECT * FROM Contact

--8. SQL SYSTEM FUNCTIONs

-- DECLARE @result

--형변환
SELECT 10 + 10
SELECT CONVERT(VARCHAR, 10) + CAST(10 AS VARCHAR)

--날짜
SELECT GETDATE()
SELECT DATEADD(day, 365, GETDATE()) -- 오늘 기준으로 365일 이후
SELECT DATEDIFF(day, GETDATE(), '2010-12-25') -- 두 날짜간 차이(D-Day 기능)
SELECT YEAR(GETDATE()), MONTH(GETDATE()), DAY(GETDATE())

--수치
SELECT ROUND(12.34, 1)  -- 반올림
SELECT FLOOR(12.34)  -- 내림
SELECT CEILING(12.34) -- 올림

-- 문자열
SELECT CHARINDEX('R', 'TEST STRING') -- 특정 문자열의 위치(1부터 시작)
SELECT LEFT('TEST STRING', 3) --좌측의 지정된 갯수만큼의 문자열 반환
SELECT RIGHT('TEST STRING', 3) --우측의 지정된 갯수만큼의 문자열 반환
SELECT SUBSTRING('TEST STRING', 3, 5) -- 3번째부터 5개의 부분문자열 반환
SELECT LEN('TEST STRING')  -- 문자열의 길이
SELECT LOWER('aNcDeFgH') -- 소문자로 변환
SELECT UPPER('aNcDeFgH') -- 대문자로 변환
IF( 'ABCDE' = 'abcde')PRINT N'같습니다.'
ELSE
BEGIN PRINT '다릅니다.'

SELECT REPLACE('TEST STRING', 'TEST', N'테스트') -- 문자열 대체

--Pubs 데이터베이스 컨텍스트로 이동
--순위함수
USE pubs
-- 순위함수
SELECT ROW_NUMBER() OVER(ORDER BY ytd_sales ASC) 매출순위,
    ROW_NUMBER() OVER(PARTITION BY type ORDER BY ytd_sales ASC) 분류매출순위,
    title_id, title, type, price, ytd_sales
FROM titles
WHERE ytd_sales IS NOT NULL
--ORDER BY type
ORDER BY ytd_sales

--  DENSE_RANK() 중복순위  같게
SELECT DENSE_RANK() OVER(ORDER BY ytd_sales ASC) 매출순위,   
    title_id, title, type, price, ytd_sales
FROM titles
WHERE ytd_sales IS NOT NULL
ORDER BY ytd_sales

--  DENSE_RANK(),RANK() 중복순위  같게
SELECT DENSE_RANK() OVER(ORDER BY ytd_sales ASC) 매출순위,   
       RANK() OVER(ORDER BY ytd_sales ASC) 매출순위,   
    title_id, title, type, price, ytd_sales
FROM titles
WHERE ytd_sales IS NOT NULL
ORDER BY ytd_sales

--  NTILE(2) 2개의 불류로 나누기, NTILE(3) 3개의 분류로
SELECT NTILE(2) OVER(ORDER BY ytd_sales ASC) 매출별그룹,   
    title_id, title, type, price, ytd_sales
FROM titles
WHERE ytd_sales IS NOT NULL
ORDER BY ytd_sales