[T-SQL] T-SQL_JOIN문 예제(sql 연습 문제3)

 

1. 서브 쿼리를 사용하여 각 제품의 비용 및 정가를 각 판매에 부과 된 단가와 비교하십시오.

- 정가가 판매 된 모든 제품의 평균 단가보다 높은 각 제품의 제품 ID, 이름 및 정가를 검색하십시오.

-- select the ProductID, Name, and ListPrice columns
SELECT ProductID,Name, ListPrice
FROM SalesLT.Product
-- filter based on ListPrice
WHERE  ListPrice>
-- get the average UnitPrice
(SELECT AVG(UnitPrice) FROM SalesLT.SalesOrderDetail)
ORDER BY ProductID;

 

 

​

2. AdventureWorks는 손실로 판매되는 제품을 찾는 데 관심이 있습니다.

- 정가가 100 이상이고 100 미만으로 판매 된 각 제품의 product ID, name, list price를 검색하십시오.

- 서브 쿼리의 ProductID는 SalesLT.SalesOrderDetail 테이블에 있습니다.

SELECT ProductID, Name, ListPrice
FROM SalesLT.Product
WHERE ProductID IN
  -- select ProductID from the appropriate table
  (SELECT ProductID FROM SalesLT.SalesOrderDetail
   WHERE UnitPrice < 100)
AND ListPrice >= 100
ORDER BY ProductID;

 

​

 

3. 정가보다 싸거나 또는 비싸게 판매되는 제품 수에 대한 아이디어를 얻기 위해 일부 집계 제품 데이터를 수집하려고합니다.

- 각 제품의 product ID, name, list price와 해당 제품이 판매 된 평균 unit price 를 검색하십시오.

SELECT ProductID, Name, StandardCost, ListPrice,
-- get the average UnitPrice
(SELECT AVG(UnitPrice)
 -- from the appropriate table, aliased as SOD
 FROM SalesLT.SalesOrderDetail AS SOD
 -- filter when the appropriate ProductIDs are equal
 WHERE P.ProductID = SOD.ProductID) AS AvgSellingPrice
FROM SalesLT.Product AS P
ORDER BY P.ProductID;

 

 

 

​

4. AdventureWorks는 평균 판매 가격보다 비싼 제품을 찾는 데 관심이 있습니다.

- 비용이 평균 판매 가격보다 높은 제품만 포함하도록 이전 실습에 대한 쿼리를 필터링합니다.

SELECT ProductID, Name, StandardCost, ListPrice,
(SELECT AVG(UnitPrice)
 FROM SalesLT.SalesOrderDetail AS SOD
 WHERE P.ProductID = SOD.ProductID) AS AvgSellingPrice
FROM SalesLT.Product AS P
-- filter based on StandardCost
WHERE StandardCost >
-- get the average UnitPrice
(SELECT AVG(UnitPrice)
 -- from the appropriate table aliased as SOD
 FROM SalesLT.SalesOrderDetail AS SOD
 -- filter when the appropriate ProductIDs are equal
 WHERE P.ProductID = SOD.ProductID)
ORDER BY P.ProductID;

 

 

​

5. AdventureWorksLT 데이터베이스에는 dbo.ufnGetCustomerInformation이라는 테이블 반환 사용자 정의 함수가 포함되어 있습니다. 이 기능을 사용하여 데이터베이스의 테이블에서 검색된 고객 ID 값을 기반으로 고객의 세부 사항을 검색하십시오.

- SalesLT.SalesOrderHeader 테이블 및 dbo.ufnGetCustomerInformation 함수에서 모든 판매 주문에 대한 order ID, customer ID, first name, last name, total due를 검색하십시오.

-- select SalesOrderID, CustomerID, FirstName, LastName, TotalDue from the appropriate tables
SELECT SalesOrderID, SOH.CustomerID, FirstName, LastName, TotalDue
FROM SalesLT.SalesOrderHeader AS SOH
-- cross apply as per the instructions
CROSS APPLY dbo.ufnGetCustomerInformation(SOH.CustomerID) AS CI
-- finish the clause
ORDER BY SOH.SalesOrderID;

 

 

 

 

6. 테이블 값 사용자 정의 함수 dbo.ufnGetCustomerInformation을 다시 사용하여 데이터베이스의 테이블에서 검색된 고객 ID 값을 기반으로 고객의 세부 사항을 검색하십시오.

- dbo.ufnGetCustomerInformation 함수를 사용하여 SalesLT.Address 및 SalesLT.CustomerAddress 테이블에서 모든 고객의 customer ID, first name, last name, address line 1, city를 검색하십시오. 제공된 별명과 다른 곳에서는 기본 열 이름을 사용해야합니다.

-- select the CustomerID, FirstName, LastName, Addressline1, and City columns from the appropriate tables
SELECT CA.CustomerID, FirstName, LastName, A.AddressLine1, A.City
FROM SalesLT.Address AS A
JOIN SalesLT.CustomerAddress AS CA
-- join based on AddressID
ON A.AddressID = CA.AddressID
-- cross apply as per instructions
CROSS APPLY dbo.ufnGetCustomerInformation(CA.CustomerID) AS CI
ORDER BY CA.CustomerID;

 

 

 

 

7. AdventureWorks는 동일한 제품 모델의 변형 모델을 많이 판매합니다. 이러한 제품에 대한 정보를 검색하는 쿼리를 작성해야합니다.

- SalesLT.Product 테이블 및 SalesLT.vProductModelCatalogDescription보기에서 각 제품의 product ID, product name, product model name, product model summary을 검색하십시오.

-- select the appropriate columns from the appropriate tables
SELECT ProductID, P.Name AS ProductName, PM.Name AS ProductModel, PM.Summary
FROM SalesLT.Product AS P
JOIN SalesLT.vProductModelCatalogDescription AS PM
-- join based on ProductModelID
ON P.ProductModelID = PM.ProductModelID
ORDER BY ProductID;

 

 

​

8. 데이터베이스에 색상이 나열된 제품에만 관심이 있습니다.

- 테이블 변수를 작성하고 SalesLT.Product 테이블에서 고유 한 색상 목록으로 채우십시오. 그런 다음 테이블 변수를 사용하여 SalesLT.Product 테이블에서 product ID, name, color을 리턴하는 쿼리를 필터링하여 테이블 변수에 나열된 색상의 제품 만 리턴하십시오. 솔루션에서 NVARCHAR (15)를 사용해야하며 제공된 별명을 사용해야합니다.

DECLARE @Colors AS TABLE (Color NVARCHAR(15));

INSERT INTO @Colors
SELECT DISTINCT Color FROM SalesLT.Product;

SELECT ProductID, Name, Color
FROM SalesLT.Product
WHERE Color IN (SELECT COlor FROM @Colors);

 

 

​

9. AdventureWorksLT 데이터베이스에는 제품 범주 (예 : 'Road Bikes') 및 상위 범주 (예 : 'Bikes')의 테이블을 반환하는 dbo.ufnGetAllCategories라는 테이블 반환 함수가 포함되어 있습니다.

- 이 기능을 사용하여 상위 카테고리 및 자체 카테고리를 포함한 모든 제품 목록을 리턴하는 조회를 작성하십시오.

- 아래 표는 dbo.ufnGetAllCategories 의 구조를 표현한 것이다.

ParentProductCategoryName	ProductCategoryName	ProductCategoryID
Accessories	Bike Racks	30
Accessories	Bike Stands	31

SELECT C.ParentProductCategoryName AS ParentCategory,
       C.ProductCategoryName AS Category,
       P.ProductID, P.Name AS ProductName
FROM SalesLT.Product AS P
JOIN dbo.ufnGetAllCategories() AS C
ON P.ProductCategoryID = C.ProductCategoryID
ORDER BY ParentCategory, Category, ProductName;

 

 

 

 

 

 

10. 각 AdventureWorks 고객은 연락처가 지정된 소매 회사입니다. 회사 및 고객 담당자 이름을 포함하여 각 고객의 총 수익을 리턴하는 조회를 작성해야합니다.

- 각 고객의 총 수익과 함께 회사 (연락처 이름) 형식의 고객 목록을 검색하십시오.

- 파생 테이블 또는 공통 테이블 식을 사용하여 각 판매 주문에 대한 세부 정보를 검색 한 다음 파생 테이블 또는 CTE를 쿼리하여 데이터를 집계하고 그룹화하십시오.

SELECT CompanyContact, SUM(SalesAmount) AS Revenue
FROM (SELECT CONCAT(c.CompanyName, CONCAT(' (' + c.FirstName + ' ', c.LastName + ')')), SOH.TotalDue
FROM SalesLT.SalesOrderHeader AS SOH
JOIN SalesLT.Customer AS c
ON SOH.CustomerID = c.CustomerID) AS CustomerSales(CompanyContact, SalesAmount)
GROUP BY CompanyContact
ORDER BY CompanyContact;

​

 

 

11. AdventureWorks는 전 세계 여러 국가 / 지역의 고객에게 제품을 판매합니다. 기존 보고서는 편집기에서 제공 한 쿼리를 사용하여 국가 / 지역 및 주 /도별로 그룹화 된 총 판매 수익을 반환합니다.

- state/province 소계 외에 모든 판매 수익에 대한 총합과 각 country/region의 소계를 포함하도록 쿼리를 수정하십시오.

SELECT a.CountryRegion, a.StateProvince, SUM(soh.TotalDue) AS Revenue
FROM SalesLT.Address AS a
JOIN SalesLT.CustomerAddress AS ca
ON a.AddressID = ca.AddressID
JOIN SalesLT.Customer AS c
ON ca.CustomerID = c.CustomerID
JOIN SalesLT.SalesOrderHeader as soh
ON c.CustomerID = soh.CustomerID
-- Modify GROUP BY to use ROLLUP
GROUP BY ROLLUP(a.CountryRegion, a.StateProvince)
ORDER BY a.CountryRegion, a.StateProvince;

 

 

 

 

 

12. 이전 연습에 대한 솔루션이 일부 추가되었습니다.

- 행의 매출 수치가 집계되는 total, country/region, and state/province 계층 구조의 레벨을 나타내는 Level이라는 열을 포함하도록 쿼리를 수정하십시오.

- 예를 들어 총 합계 행에는 값 'Total'이 포함되어야하고 미국의 소계를 표시하는 행에는 'United States Subtotal'값이 포함되어야 합니다. 캘리포니아의 소계를 표시하는 행에는 'California Subtotal'값이 포함되어야합니다.

SELECT a.CountryRegion, a.StateProvince,
IIF(GROUPING_ID(a.CountryRegion) = 1 AND GROUPING_ID(a.StateProvince) = 1, 'Total', IIF(GROUPING_ID(StateProvince) = 1, a.CountryRegion + ' Subtotal', a.StateProvince + ' Subtotal')) AS Level,
SUM(soh.TotalDue) AS Revenue
FROM SalesLT.Address AS a
JOIN SalesLT.CustomerAddress AS ca
ON a.AddressID = ca.AddressID
JOIN SalesLT.Customer AS c
ON ca.CustomerID = c.CustomerID
JOIN SalesLT.SalesOrderHeader as soh
ON c.CustomerID = soh.CustomerID
GROUP BY ROLLUP(a.CountryRegion, a.StateProvince)
ORDER BY a.CountryRegion, a.StateProvince;

 

 

​

13. 다시, 이전 연습에 대한 솔루션이 제공되었으므로 거기서 연습 할 수 있습니다!

- 개별 도시에 대한 그룹화를 포함하도록 쿼리를 확장하십시오.

SELECT a.CountryRegion, a.StateProvince, a.City,
CHOOSE (1 + GROUPING_ID(a.CountryRegion) + GROUPING_ID(a.StateProvince) + GROUPING_ID(a.City),
        a.City + ' Subtotal', a.StateProvince + ' Subtotal',
        a.CountryRegion + ' Subtotal', 'Total') AS Level,
SUM(soh.TotalDue) AS Revenue
FROM SalesLT.Address AS a
JOIN SalesLT.CustomerAddress AS ca
ON a.AddressID = ca.AddressID
JOIN SalesLT.Customer AS c
ON ca.CustomerID = c.CustomerID
JOIN SalesLT.SalesOrderHeader as soh
ON c.CustomerID = soh.CustomerID
GROUP BY ROLLUP(a.CountryRegion, a.StateProvince, a.City)
ORDER BY a.CountryRegion, a.StateProvince, a.City;

 

 

 

​

14. AdventureWorks 제품은 범주로 그룹화되며 상위 범주가 있습니다 (SalesLT.vGetAll Categories View에서 정의된다)

AdventureWorks 고객은 소매 회사이므로 모든 범주의 제품을 주문할 수 있습니다. 주문에서 각 제품의 수익은 SalesLT.SalesOrderDetail 테이블에 LineTotal 값으로 기록됩니다.

- Accessories, Bikes, Clothing, Components에서 각 상위 카테고리에 대한 총 수익과 함께 고객 회사 이름 목록을 검색하십시오.

SELECT * FROM
(SELECT cat.ParentProductCategoryName,sod.LineTotal, cust.CompanyName
 FROM SalesLT.SalesOrderDetail AS sod
 JOIN SalesLT.SalesOrderHeader AS soh ON sod.SalesOrderID = soh.SalesOrderID
 JOIN SalesLT.Customer AS cust ON soh.CustomerID = cust.CustomerID
 JOIN SalesLT.Product AS prod ON sod.ProductID  = prod.ProductID
 JOIN SalesLT.vGetAllCategories AS cat ON prod.ProductcategoryID = cat.ProductCategoryID) AS catsales
PIVOT (SUM(LineTotal) FOR ParentProductCategoryName
IN ([Accessories], [Bikes], [Clothing], [Components])) AS pivotedsales
ORDER BY CompanyName;