Database/MSSQL work
AdventureWorks 연습
웨이칭
2020. 11. 11. 13:54
시험
1.다음의 내용을 출력
● 고객의 주문번호, 주문상세번호, 주문일, 주문수량,
주문상품명, 판매지역명, 판매직원(first middle last),
선적회사명
● 주문번호 오름차순 정렬
select S.SalesOrderNumber as [주문번호]
, SD.SalesOrderDetailID as [주문상세번호]
, S.OrderDate as [주문일]
, SD.OrderQty as [주문수량]
, PD.Name as [주문상품명]
, ST.Name [판매지역명]
, CONCAT(PP.FirstName, ' ', PP.MiddleName, ' ', PP.LastName) as [판매직원]
, SH.Name as [선적회사명]
from Sales.SalesOrderHeader S
join Sales.SalesOrderDetail SD
on S.SalesOrderID = SD.SalesOrderID
join Production.Product PD
on SD.ProductID = PD.ProductID
join Sales.SalesTerritory ST
on S.TerritoryID = ST.TerritoryID
join Sales.SalesPerson SP
on S.SalesPersonID = SP.BusinessEntityID
join Person.Person PP
on SP.BusinessEntityID = PP.BusinessEntityID
join Purchasing.ShipMethod SH
on S.ShipMethodID = SH.ShipMethodID
order by S.SalesOrderNumber
2. 직원의 FirstName, LastName과 JobTitle, 입사일(HireDate)을
해당 jobTitle을 가진 직원수와 함께 출력하려 함
<출력 예시>
FirstName LastName JobTitle HireDate CountOfTitle
-------------------------------------------------- ----------------------
Barbara Moreland Accountant 2009-02-18 2
Mike Seamans Accountant 2009-03-08 2
2-1. Derived Table을 사용하여 작성
SELECT FirstName, LastName, e.JobTitle, HireDate, CountOfTitle
FROM HumanResources.Employee AS e
JOIN Person.Person AS p
ON e.BusinessEntityID = p.BusinessEntityID
JOIN ( SELECT COUNT(*) AS CountOfTitle, JobTitle
FROM HumanResources.Employee
GROUP BY JobTitle) AS j
ON e.JobTitle = j.JobTitle
2-2. CTE 구문으로 작성
WITH j AS (SELECT COUNT(*) AS CountOfTitle, JobTitle
FROM HumanResources.Employee
GROUP BY JobTitle)
SELECT FirstName, LastName, e.JobTitle, HireDate, CountOfTitle
FROM HumanResources.Employee AS e
JOIN Person.Person AS p
ON e.BusinessEntityID = p.BusinessEntityID
JOIN j ON e.JobTitle = j.JobTitle
2-3. OVER 구문을 사용하여 작성
SELECT FirstName, LastName, e.JobTitle, HireDate,
COUNT(*) OVER(PARTITION BY JobTitle) AS CountOfTitle
FROM HumanResources.Employee AS e
JOIN Person.Person AS p
ON e.BusinessEntityID = p.BusinessEntityID
3. 최소 5건 이상 주문했던 고객의 ID, SalesOrderID, OrderDate를 출력
3-1. Subquery를 이용하여 작성
SELECT CustomerID, SalesOrderID, OrderDate
FROM Sales.SalesOrderHeader
WHERE CustomerID IN
(SELECT CustomerID
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
HAVING COUNT(*) > 4)
order by CustomerID
3-2. CTE를 이용하여 작성
WITH c AS (
SELECT CustomerID
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
HAVING COUNT(*) > 4)
SELECT c.CustomerID, SalesOrderID, OrderDate
FROM Sales.SalesOrderHeader AS SOH
JOIN c ON SOH.CustomerID = c.CustomerID
order by c.CustomerID
3-3. Derived table을 이용하여 작성
SELECT c.CustomerID, SalesOrderID, OrderDate
FROM Sales.SalesOrderHeader AS SOH
JOIN ( SELECT CustomerID
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
HAVING COUNT(*) > 4) AS c
ON SOH.CustomerID = c.CustomerID
order by c.CustomerID
시험전 워밍업
-- 구매팀의 주문일 '2014-01-01'부터 '2014-01-31'까지 주문내역을 아래와 같이 출력
-- 주문번호(PurchaseOrderID), 주문상세번호(PurchaseOrderDetailID), 주문일(yyyy-mm-dd),상품단가(Unitprice),
-- 주문수량(OrderQty), 주문금액(SubTotal), 납기일(DueDate)
select POD.PurchaseOrderID, PurchaseOrderDetailID, CONVERT(date, OrderDate)as orderDate,UnitPrice, OrderQty,SubTotal,DueDate, Prod.Name, V.Name
from Purchasing.PurchaseOrderDetail POD
join Purchasing.PurchaseOrderHeader POH ON POD.PurchaseOrderID = POH.PurchaseOrderID
join Production.Product Prod ON Prod.ProductID = POD.ProductID
join Purchasing.Vendor V ON V.BusinessEntityID = POH.VendorID
where OrderDate between '2014-01-01' and '2014-01-31'
exec sp_help "purchasing.purchaseorderheader"
exec sp_help "purchasing.vendor"
3번째
-- 주문번호(SalesOrderID), 영업사원ID, SalesYTD, 영업사원의 FirstName, MiddleName, LastName을 출력
select H.SalesOrderID, P.BusinessEntityID, T.SalesYTD, p.FirstName, p.MiddleName, p.LastName
from Person.Person P
join Sales.Customer C ON p.BusinessEntityID = C.PersonID
join Sales.SalesTerritory T ON C.TerritoryID = T.TerritoryID
join Sales.SalesOrderHeader H ON C.CustomerID = H.CustomerID
order by H.SalesOrderID, p.BusinessEntityID
-- 강사님 답
select SOH.SalesOrderID, SOH.SalesPersonID, SP.SalesYTD, p.FirstName, p.MiddleName, p.LastName
from Sales.SalesPerson SP
join Sales.SalesOrderHeader SOH ON SP.BusinessEntityID = SOH.SalesPersonID
join Person.Person P ON P.BusinessEntityID = SP.BusinessEntityID
order by SOH.SalesOrderID, p.BusinessEntityID
go
exec sp_help "sales.salesOrderHeader"
exec sp_help "person.person"
exec sp_help "sales.salesterritory"
exec sp_help "sales.salesOrderdetail"
-- 각 주문건 별 환율ID(CurrencyRateID), 평균환율(AverageRate), 기본선적료(ShipBase), 주문번호(SalesOrderID)를 출력
select h.CurrencyRateID, r.AverageRate, m.ShipBase, h.SalesOrderID
from Sales.Customer C
right join Sales.SalesOrderHeader H ON c.CustomerID = h.CustomerID
join Purchasing.ShipMethod M ON h.ShipMethodID = m.ShipMethodID
join Sales.CurrencyRate R ON h.CurrencyRateID = r.CurrencyRateID
go
-- '2011-07-01'부터 '2011-07-31' 사이의 상품 주문 정보를 조회하라
-- 조회 할 컬럼 정보는 다음과 같다.
-- 주문날짜 (yyyy-MM-dd), 상품번호 (ProductID), 주문수량(OrderQty), 상품명(Name)
-- 주문 날짜 및 주문 수량의 오름차순으로 정렬하라
select s.OrderDate, p.ProductID, d.OrderQty, p.Name
from Sales.SalesOrderHeader S
join Sales.SalesOrderDetail D ON s.SalesOrderID = d.SalesOrderID
join Production.Product P ON d.ProductID = p.ProductID
where s.orderdate between '2011-07-01' and '2011-07-31'
order by s.OrderDate,d.OrderQty
-- 강사님답
select convert(date, s.OrderDate), p.ProductID, d.OrderQty, p.Name
from Sales.SalesOrderHeader S
join Sales.SalesOrderDetail D ON s.SalesOrderID = d.SalesOrderID
join Production.Product P ON d.ProductID = p.ProductID
where s.orderdate between '2011-07-01' and '2011-07-31'
order by s.OrderDate, d.OrderQty
go
2번째
use AdventureWorks2019
go
--각 판매건별 주문번호(SalesOrderID), 판매량(SalesQuota), 보너스(Bonus),
--영업사원의 FisrtName, MiddleName, LastName을 출력
select sh.SalesOrderID, sp.SalesQuota, sp.Bonus, pp.FirstName, pp.MiddleName, pp.LastName
from Sales.SalesOrderHeader SH
join Sales.SalesPerson SP ON sp.BusinessEntityID = sh.SalesPersonID
inner join Person.Person PP ON sp.BusinessEntityID = pp.BusinessEntityID
exec sp_help"person.person"
exec sp_help"sales.SalesPerson"
exec sp_help"sales.salesOrderHeader"
--고객의 FirstName, MiddleName, LastName과 해당 고객이 구입한 제품의 이름을 출력
/*
select p.FirstName, p.MiddleName, p.LastName, prod.Name
from Person.Person P
join Production.Document PD ON p.BusinessEntityID = pd.Owner
join Production.ProductDocument PP ON pd.DocumentNode = pp.DocumentNode
join Production.Product Prod ON prod.ProductID = pp.ProductID
*/
/*
select S.Name, p.FirstName
from Sales.Customer C
full join Person.Person P ON C.PersonID = P.BusinessEntityID
full join Sales.Store S ON C.PersonID = S.BusinessEntityID
*/
select distinct Per.FirstName, Per.MiddleName, Per.LastName, Prod.Name, H.OrderDate
from Sales.Customer C
join Person.Person Per ON C.PersonID = Per.BusinessEntityID
join Sales.SalesOrderHeader H ON C.CustomerID = H.CustomerID
join Sales.SalesOrderDetail D ON H.SalesOrderID = D.SalesOrderID
--join Sales.SpecialOfferProduct S ON S.ProductID = D.ProductID
join Production.Product Prod ON Prod.ProductID = D.ProductID
exec sp_help"person.person"
exec sp_help"Production.Document"
exec sp_help"sales.salesOrderHeader"
exec sp_help"sales.salesTerritory"
exec sp_help"sales.customer"
exec sp_help"production.product"
exec sp_help"purchasing.purchaseorderheader"
--주문번호와 상품ID, 상품명을 출력하되, 주문내역이 없는 상품도 출력함
select distinct sd.SalesOrderID, sd.ProductID, Prod.Name
from Sales.SalesOrderDetail SD
--join Sales.SpecialOfferProduct SP ON sd.ProductID = sp.ProductID
right join Production.Product Prod ON sd.ProductID = Prod.ProductID
select * from Production.Product
1번째
USE AdventureWorks2019
GO
select * from Person.Person
select * from Sales.Customer
GO
--직원의 job title, birth date, first name, last name을 출력
select E.JobTitle, E.BirthDate, P.FirstName, P.LastName
from HumanResources.Employee E, Person.Person P
where E.BusinessEntityID = P.BusinessEntityID
select E.JobTitle, E.BirthDate, P.FirstName, P.LastName
from HumanResources.Employee E
join Person.Person P
ON E.BusinessEntityID = P.BusinessEntityID
-- 고객의 CustomerID, StoreID, TerritoryID,
-- FirstName, MiddleName, LastName을 출력
select C.CustomerID, C.StoreID, C.TerritoryID, P.FirstName, P.MiddleName, P.LastName
from Sales.Customer C
join Person.Person P ON C.PersonID = P.BusinessEntityID
exec sp_help"person.person"
exec sp_help"sales.customer"
exec sp_help"sales.salesOrderHeader"
-- 위 쿼리에 고객의 주문번호 (SalesOrderID)를 추가
select C.CustomerID, C.StoreID, C.TerritoryID, P.FirstName, P.MiddleName, P.LastName, s.SalesOrderID
from Sales.Customer C
join Person.Person P ON C.PersonID = P.BusinessEntityID
join Sales.SalesOrderHeader S ON s.CustomerID = c.CustomerID
test2
update customersTbl
set
customersTbl.grade = S.grade
from customersTbl as C,
(
select C.custId,
case when sum(sales) >= 10000 then 'A'
when sum(sales) >= 5000 then 'B'
when sum(sales) >= 10 then 'C'
else 'D'
end as grade
from customersTbl C
left join salesTbl S ON C.custId = S.custId
group by c.custId
)
as S
where c.custId = s.custId
test1
use AdventureWorks2019
select CreditCardID from sales.CreditCard
where CardType='Vista'
order by ExpYear, ExpMonth;
-- 위와 동일~ TOP 사용하여 상위 10개만 조회
select top(10) CreditCardID from sales.CreditCard
where CardType='Vista'
order by ExpYear, ExpMonth;
-- TOP에 수식하기
select top(select count(*)/100 from Sales.CreditCard) CreditCardID from sales.CreditCard
where CardType='Vista'
order by ExpYear, ExpMonth;
select top(10) * from Sales.CreditCard
where ExpYear=2008
order by ModifiedDate, ExpMonth
-- 0.1퍼센트
select top(0.1) percent CreditCardID, ExpYear, ExpMonth
from sales.CreditCard
where CardType='Vista'
order by ExpYear, ExpMonth;
select *
from Sales.SalesOrderDetail
TABLESAMPLE (5 PERCENT);
select count(*) from Sales.SalesOrderDetail;
go
docs.microsoft.com/ko-kr/learn/certifications/help
Microsoft Certification help
Microsoft Certification help
docs.microsoft.com