select EmployeeID id
,concat(len(replace(Notes,' ','')),'(',substring(Notes,1,10),')') info
,notes
from Employees;
select EmployeeID id
,LastName
,concat(FirstName,'(',charindex('German',Notes),')') German
,Notes
from Employees;
select supplierId
,suppliername
,contactName
,country
,phone
from suppliers
where (phone not like '([0-9][0-9][0-9])%') and country = 'Germany'
select supplierid
,suppliername
,contactname
,country
,phone
,iif(phone like '([0-9][0-9][0-9])%','Exact','Inexact') notes
from suppliers
where phone like '([0-9]%)%' and country = 'Germany'
select supplierid
,suppliername
,contactname
,country
,case country when 'canada' then 1 when 'france' then 2 else 3 end candidate
from suppliers
where country = 'canada' or country = 'france' or country = 'australia'
order by candidate, contactname
where Country in('canada', 'france', 'australia') 로 대체할 수 있다
select shipperid
,min(orderdate) date1
,max(orderdate) date2
,count(*) orderCount
,count(distinct customerid) customerCount
from orders
group by shipperID
select employeeid, count(*) jumun
from orders
group by employeeid
having count(*) >= 30
order by jumun desc
select top 1
customerID
,count(orderID) orderCount
,stuff((select concat(',',orderDate)
from orders
where customerID = a.customerID
for xml path('')),1,1,'') notes
from orders a
group by customerID
order by orderCount desc