[SQL] Operator, Function

MIN() , MAX()

SELECT MIN(column_name)
FROM table_name
WHERE condition;

SELECT MAX(column_name)
FROM table_name
WHERE condition;

• 예시

SELECT MIN(Price) AS SmallestPrice
FROM Products;
//Products에서 가장 작은 Price 값을 SmallestPrice라는 이름으로 출력

SELECT MAX(Price) AS LargestPrice
FROM Products;
//Products에서 가장 큰 Price 값을 LargestPrice라는 이름으로 출력

COUNT(), AVG(), SUM()

• COUNT( ) : 조건에 맞는 row의 개수 출력
• AVG( ) : numeric colum의 평균 값 출력
• SUM( ) : numeric colum의 총합 출력

//COUNT() Syntax
SELECT COUNT(column_name)
FROM table_name
WHERE condition;

//AVG() Syntax
SELECT AVG(column_name)
FROM table_name
WHERE condition;

//SUM() Syntax
SELECT SUM(column_name)
FROM table_name
WHERE condition;

• 예시

SELECT COUNT(ProductID)
FROM Products;

SELECT AVG(Price)
FROM Products;

SELECT SUM(Quantity)
FROM OrderDetails;

LIKE ( % , _ )

• % : n개의 문자를 의미
• _ : 문자 1개를 의미

SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;

• 예시

// a로 시작하는 값 찾기
SELECT * FROM Customers WHERE CustomerName LIKE 'a%';
//a로 끝나는 값 찾기
SELECT * FROM Customers WHERE CustomerName LIKE '%a';
//or이 포함된 값 찾기
SELECT * FROM Customers WHERE CustomerName LIKE '%or%';
//두번째 값이 r인 값 찾기 
SELECT * FROM Customers WHERE CustomerName LIKE '_r%';
// a가 뒤에서 3번째에 있는 값 찾기
SELECT * FROM Customers WHERE CustomerName LIKE 'a__%';
// a로 시작하고 o로 끝나는 값 찾기
SELECT * FROM Customers WHERE ContactName LIKE 'a%o';
// a로 시작하지 않는 값 찾기
SELECT * FROM Customers WHERE CustomerName NOT LIKE 'a%';

ANY and ALL

• ANY : 어떤 조건 값이 맞으면 true
• ALL : 모든 값이 조건에 맞으면 true

SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
(SELECT column_name FROM table_name WHERE condition);

SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
(SELECT column_name FROM table_name WHERE condition);

• 예시

SELECT ProductName
FROM Products
WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);

SELECT ProductName
FROM Products
WHERE ProductID = ALL (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);

NULL Values

SELECT column_names
FROM table_name
WHERE column_name IS NULL;
//column)name이 NULL값인 것 찾아서 출력

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;
//column)name이 NULL값이 아닌 것 찾아서 출력