VontineDev

WHERE 절에서 지정가능한 Predicate and Operators

UnderScore _ 를 사용해 조건을 만들수도 있다.

A__Z   >> A[아무거나][아무거나]Z

DEMOS

SalesLT.Product테이블에서 ProductModelID가 6인 row를 찾아서 Name, Color, Size를 출력한다.

--List information about product model 6
SELECT Name, Color, Size FROM SalesLT.Product WHERE ProductModelID = 6;

LIKE

ProductNumber가 FR로 시작하는 것을 찾는다. 

LIKE 'FR%'

--List information about products that have a product number beginning FR
SELECT productnumber,Name, ListPrice FROM SalesLT.Product WHERE ProductNumber LIKE 'FR%';

LIKE

LIKE 'FR-_[0-9][0-9]_-[0-9][0-9]'

FR-[아무거나][0~9까지숫자][0~9까지숫자][아무거나]-[0~9까지숫자][0~9까지숫자] 를 찾는다.

--Filter the previous query to ensure that the product number contains two sets of two didgets
SELECT Name, ListPrice, ProductNumber FROM SalesLT.Product WHERE ProductNumber LIKE 'FR-_[0-9][0-9]_-[0-9][0-9]';

NULL의 취급

SellEndDate가 NULL이 아닌 경우만 출력된다.

--Find products that have no sell end date
SELECT Name, SellEndDate FROM SalesLT.Product WHERE SellEndDate IS NOT NULL;

BETWEEN AND

BETWEEN AND를 사용해서 범위를 지정할 수 있다.

--Find products that have a sell end date in 2006
SELECT Name FROM SalesLT.Product WHERE SellEndDate BETWEEN '2006/1/1' AND '2006/12/31';

IN

IN에는 다양한 값을 한꺼번에 지정가능하다. ProductCategoryID가 5,6,7인 row가 모두 출력된다.

--Find products that have a category ID of 5, 6, or 7.
SELECT ProductCategoryID, Name, ListPrice FROM SalesLT.Product WHERE ProductCategoryID IN (5, 6, 7);

AND

ProductCategoryID가 5,6,7이면서 SellEndDate가 NULL인 것들을 출력한다.

--Find products that have a category ID of 5, 6, or 7 and have a sell end date
SELECT ProductCategoryID, Name, ListPrice, SellEndDate FROM SalesLT.Product WHERE ProductCategoryID IN (5, 6, 7) AND SellEndDate IS NULL;

OR

ProductNumber가 FR로 시작하거나 ProductCategoryID가 5,6,7인 것들이 모두 출력된다.

--Select products that have a category ID of 5, 6, or 7 and a product number that begins FR
SELECT Name, ProductCategoryID, ProductNumber FROM SalesLT.Product WHERE ProductNumber LIKE 'FR%' OR ProductCategoryID IN (5,6,7);

Removing Duplicates 

• SELECT ALL(중복을 포함)

select Color from SalesLT.Product

• SELECT DISTINCT(중복이 제거됨)

select distinct Color from SalesLT.Product

Sorting Results

select Name, ProductCategoryID
from SalesLT.Product
order by ProductCategoryID;

Limiting Sorted Results

TOP (N) | TOP (N) Percent를 사용해서 필요한 것만 볼 수 있다.

WITH TIES를 사용하면 동률인 것도 포함된다.

select top 10 Name, ProductCategoryID, ListPrice
from SalesLT.Product
order by ProductCategoryID, ListPrice desc;

Paging Throught Results

offset를 사용해서 10개의 row를 스킵하고

fetch를 통해 다음 10개의 row를 보여준다.

select Name, ProductCategoryID, ListPrice
from SalesLT.Product
order by ProductCategoryID, ListPrice desc
offset 10 row
fetch first 10 row only;

DEMOS

--Display a list of product colors
SELECT Color FROM SalesLT.Product;

--Display a list of product colors with the word 'None' if the value is null
SELECT DISTINCT ISNULL(Color, 'None') AS Color FROM SalesLT.Product;

--Display a list of product colors with the word 'None' if the value is null sorted by color
SELECT DISTINCT ISNULL(Color, 'None') AS Color FROM SalesLT.Product ORDER BY Color;

--Display a list of product colors with the word 'None' if the value is null and a dash if the size is null sorted by color
SELECT DISTINCT ISNULL(Color, 'None') AS Color, ISNULL(Size, '-') AS Size FROM SalesLT.Product ORDER BY Color;


--Display the top 100 products by list price
SELECT TOP 100 Name, ListPrice FROM SalesLT.Product ORDER BY ListPrice DESC;

--Display the first ten products by product number
SELECT Name, ListPrice FROM SalesLT.Product ORDER BY ProductNumber OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY; 

--Display the next ten products by product number
SELECT Name, ListPrice FROM SalesLT.Product ORDER BY ProductNumber OFFSET 10 ROWS FETCH FIRST 10 ROW ONLY;

NULL은 빠진 값이나 알려지지 않은 값이다.

2 + NULL = NULL

'MyString' + NULL = NULL

NULL = NULL returns false

NULL IS NULL returns true

데이터베이스에서는 NULL을 어떻게 처리하는지가 중요하다.

NULL Functions

• ISNULL(column/variable, value)
  
  • 컬럼/변수가 NULL이면 value를 리턴한다
• NULLIF(column/variable, value)
  • 컬럼/변수가 value이면 NULL을 리턴한다
• COALESCE(column/variable1, column/variable2, ...)
  • 맨 처음 만나는 NULL이 아닌 컬럼/변수를 리턴한다.

Size를 정수로 TRY_CAST로 변환하고, 성공하면 그 값으로, 실패하면 NULL을 ISNULL함수를 통해 0 으로 바꾼다.

SELECT Name, ISNULL(TRY_CAST(Size AS Integer),0) AS NumericSize
FROM SalesLT.Product;

비슷하게 Color나 Size가 NULL이라면 '' (blank string)을 리턴한다.

SELECT ProductNumber, ISNULL(Color, '') + ', ' + ISNULL(Size, '') AS ProductDetails
FROM SalesLT.Product;

Color가 Multi이면 NULL을 리턴한다.

SELECT Name, NULLIF(Color, 'Multi') AS SingleColor
FROM SalesLT.Product;

DiscontinuedDate, SellEndDate, SellStartDate중 처음으로 NULL이 아닌 값을 리턴한다.

SELECT Name, COALESCE(DiscontinuedDate, SellEndDate, SellStartDate) AS FirstNonNullDate
FROM SalesLT.Product;

CASE는 프로그래밍 언어에서 if나 switch와 비슷하게 사용할 수 있다.

SellSellEndDate가 NULL이라면 SalesStatus 컬럼에 On Sale로 표시하고

                        NULL이 아니라면                   Discontinued로 표시한다.

--Searched case
SELECT Name,
		CASE
			WHEN SellEndDate IS NULL THEN 'On Sale'
			ELSE 'Discontinued'
		END AS SalesStatus
FROM SalesLT.Product;

WHEN THEN을 여러개 쓸 수 있다.

--Simple case
SELECT Name,
		CASE Size
			WHEN 'S' THEN 'Small'
			WHEN 'M' THEN 'Medium'
			WHEN 'L' THEN 'Large'
			WHEN 'XL' THEN 'Extra-Large'
			ELSE ISNULL(Size, 'n/a')
		END AS ProductSize
FROM SalesLT.Product;