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

 

1.Sale.Customer테이블에서CompanyName, SalesOrderId 및 SalesLT.SalesOrderHeader 테이블에서 TotalDue 를 반환하는 쿼리를 작성하십시오.

- JOIN을 사용하시오

SELECT CompanyName, SalesOrderId, TotalDue 
FROM SalesLT.Customer AS c 
JOIN SalesLT.SalesOrderHeader AS oh 
ON c.CustomerID = oh.CustomerID;

 

​

2. 고객에게 송장을 보내려면 고객의 주소가 필요합니다.

- 고객 주문 조회를 확장하여 전체 주소, 도시, 주 또는도, 우편 번호 및 국가 또는 지역을 포함한 각 고객의 본사 주소를 포함하십시오.

SELECT c.CompanyName, a.AddressLine1, ISNULL(a.AddressLine2, '') AS AddressLine2, a.City, a.StateProvince, a.PostalCode, a.CountryRegion, oh.SalesOrderID, oh.TotalDue 
FROM SalesLT.Customer AS c 
JOIN SalesLT.SalesOrderHeader AS oh 
ON oh.CustomerID = c.CustomerID 
	JOIN SalesLT.CustomerAddress AS ca 
	ON c.CustomerID = ca.CustomerID 
		AND AddressType = 'Main Office' 
			JOIN SalesLT.Address AS a 
			ON ca.AddressID = a.AddressID;

 

​

3. 영업 관리자는 모든 CompanyName, FirstName, LastName 의 목록을 원하며, 주문한 각 SalesOrderID and TotalDue를 표시합니다.

SELECT CompanyName, FirstName, LastName, SalesOrderID, TotalDue 
FROM SalesLT.Customer AS c LEFT 
JOIN SalesLT.SalesOrderHeader AS oh ON c.CustomerID = oh.CustomerID 
ORDER BY oh.SalesOrderID DESC;

 

 

​

4.영업 사원이 AdventureWorks에 모든 고객의 주소 정보가 있지 않은 것을 알게 되었습니다.

- 데이터베이스에 저장된 주소가없는 customer IDs, company names, contact names (first name and last name), phone numbers 목록을 반환하는 쿼리를 작성하십시오.

SELECT c.CompanyName, c.FirstName, c.LastName, c.Phone FROM SalesLT.Customer AS c LEFT 
JOIN SalesLT.CustomerAddress AS ca ON c.CustomerID = ca.CustomerID 
WHERE ca.AddressID IS NULL;

 

​

5. 일부 고객은 주문한 적이 없으며 일부 제품은 주문한 적이 없습니다.

- 주문한 적이없는 고객에 대한 customer ID 열과 주문한 적이없는 제품에 대한 product ID 열을 리턴하는 조회를 작성하십시오.

- customer ID가 있는 각 행은 고객이 제품을 주문한 적이 없기 때문에 product ID가 NULL이어야하고 product ID가 있는 각 행은 고객이 제품을 주문한 적이 없기 때문에 NULL customer ID를 가져야합니다.

SELECT CompanyName, FirstName, LastName, SalesOrderID, TotalDue 
FROM SalesLT.Customer AS c LEFT 
JOIN SalesLT.SalesOrderHeader AS oh ON c.CustomerID = oh.CustomerID 
ORDER BY oh.SalesOrderID DESC;

 

​

6. 고객은 본사 주소와 배송 주소라는 두 가지 종류의 주소(main office address, shipping address.)를 가질 수 있습니다.

- 계정 부서에서는 본사 주소가 항상 청구에 사용되도록하고 각 고객의 서로 다른 주소 유형을 명확하게 식별하는 쿼리를 작성하도록 요청했습니다.

- SalesLT.CustomerAddress 테이블의 주소 유형이 'MainOffice'인 company name, AddressLine1, city 및 AddressType이라는 열을 값이 'Billing'인 열을 검색하는 쿼리를 작성하십시오.

SELECT c.CompanyName, a.AddressLine1, a.City, 'Billing' AS AddressType 
FROM SalesLT.Customer AS c 
JOIN SalesLT.CustomerAddress AS ca 
ON c.CustomerID = ca.CustomerID 
	JOIN SalesLT.Address AS a 
	ON ca.AddressID = a.AddressID
		WHERE ca.AddressType = 'Main Office';

 

 

​

7. 문제 유형은 6번과 동일

- SalesLT.CustomerAddress 테이블의 주소 유형이 'Shipping'인 company name, AddressLine1, city 및 AddressType이라는 열을 검색하기 위해 값을 지정하십시오.

SELECT c.CompanyName, a.AddressLine1, a.City, 'Shipping' AS AddressType 
FROM SalesLT.Customer AS c 
JOIN SalesLT.CustomerAddress AS ca 
ON c.CustomerID = ca.CustomerID 
  JOIN SalesLT.Address AS a 
  ON ca.AddressID = a.AddressID 
  	WHERE ca.AddressType = 'Shipping'

 

 

​

8. 6번 코드와 7번 코드를 합친다.

- UNION ALL을 사용하여 두 쿼리에서 반환 된 결과를 결합하여 company name과 address 유형별로 정렬 된 모든 customer addresses 목록을 만듭니다.

SELECT c.CompanyName, a.AddressLine1, a.City, 'Billing' AS AddressType
FROM SalesLT.Customer AS c
JOIN SalesLT.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID
  JOIN SalesLT.Address AS a
  ON ca.AddressID = a.AddressID
  	WHERE ca.AddressType = 'Main Office'
UNION ALL
SELECT c.CompanyName, a.AddressLine1, a.City, 'Shipping' AS AddressType
FROM SalesLT.Customer AS c
JOIN SalesLT.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID
  JOIN SalesLT.Address AS a
  ON ca.AddressID = a.AddressID
  	WHERE ca.AddressType = 'Shipping'
 ORDER BY c.CompanyName, AddressType;

 

 

 

9. 모든 고객 주소의 마스터 목록을 작성했지만 이제는 본사 주소(mainoffice)만 있는 고객과 본사 및 운송 주소를 가진 고객을 표시하는 필터링 된 목록을 작성하라는 요청을 받았습니다.

- 'Main Office'주소를 가진 고객 테이블에 나타나지만 'Shipping'주소를 가진 고객 테이블에는 나타나지 않는 각 회사의 회사 이름을 반환하는 쿼리를 작성하십시오.

SELECT c.CompanyName
FROM SalesLT.Customer AS c
-- join the CustomerAddress table
JOIN SalesLT.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID
JOIN SalesLT.Address AS a
-- join based on AddressID
ON a.AddressID = ca.AddressID
WHERE ca.AddressType = 'Main Office'
EXCEPT
SELECT c.CompanyName
FROM SalesLT.Customer AS c
-- use the appropriate join to join the CustomerAddress table
JOIN SalesLT.CustomerAddress AS ca
-- join based on CustomerID
ON c.CustomerID = ca.CustomerID
-- use the appropriate join to join the Address table
JOIN SalesLT.Address AS a
ON ca.AddressID = a.AddressID
-- filter for the appropriate AddressType
WHERE ca.AddressType = 'Shipping'
ORDER BY c.CompanyName;

 

 

 

 

10. 이번 문제는 이전 문제에서 수행 한 작업을 기반으로합니다.

- 'Main Office'주소를 가진 고객 테이블과 'Shipping'주소를 가진 고객 테이블에 나타나는 각 회사의 회사 이름을 반환하는 쿼리를 작성하십시오.

-- select the CompanyName column
SELECT c.CompanyName
-- from the appropriate table
FROM SalesLT.Customer AS c
-- use the appropriate join with the appropriate table
JOIN SalesLT.CustomerAddress AS ca
-- join based on CustomerID
ON c.CustomerID = ca.CustomerID
-- use the appropriate join with the appropriate table
JOIN SalesLT.Address AS a
-- join based on AddressID
ON ca.AddressID = a.AddressID
-- filter based on AddressType
WHERE ca.AddressType = 'Main Office'
INTERSECT
-- select the CompanyName column
SELECT c.CompanyName
FROM SalesLT.Customer AS c
-- use the appropriate join with the appropriate table
JOIN SalesLT.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID
JOIN SalesLT.Address AS a
-- join based on AddressID
ON ca.AddressID = a.AddressID
-- filter based on AddressType
WHERE ca.AddressType = 'Shipping'
ORDER BY c.CompanyName;

 

 

​

11. 대문자로 형식화 된 제품 이름과 각 제품의 반올림 된 무게 ApproxWeight 열과 함께 각 제품의 product ID를 반환하는 쿼리를 작성하십시오.

SELECT ProductID, UPPER(Name) AS ProductName, ROUND(Weight, 0) AS ApproxWeight 
FROM SalesLT.Product;

 

​

 

12. AdventureWorks가 각 제품을 판매일을 아는 것은 중요합니다.

- AdventureWorks가 각 제품 판매를 시작한 연도와 월이 포함 된 SellStartYear 및 SellStartMonth라는 열을 포함하도록 쿼리를 확장합니다. 월은 월 이름으로 표시되어야합니다 (예 : 'January').

SELECT ProductID, UPPER(Name) AS ProductName, ROUND(Weight, 0) AS ApproxWeight, YEAR(SellStartDate) as SellStartYear, DATENAME(m, SellStartDate) as SellStartMonth 
FROM SalesLT.Product;

 

 

​

13. 각 제품의 유형을 아는 것도 유용합니다.

- 제품 번호에서 가장 왼쪽에있는 두 문자를 포함하는 ProductType 열을 포함하도록 쿼리를 확장하십시오.

SELECT ProductID, UPPER(Name) AS ProductName, ROUND(Weight, 0) AS ApproxWeight,
       YEAR(SellStartDate) as SellStartYear,
       DATENAME(m, SellStartDate) as SellStartMonth,
       -- use the appropriate function to extract substring from ProductNumber
       LEFT(ProductNumber, 2) AS ProductType
FROM SalesLT.Product;

 

 

​

14. 숫자 제품 크기 데이터 만 보려고합니다.

- 숫자 크기의 제품 만 포함되도록 반환 된 제품을 필터링하도록 쿼리를 확장하십시오.

SELECT ProductID, UPPER(Name) AS ProductName, ROUND(Weight, 0) AS ApproxWeight,
       YEAR(SellStartDate) as SellStartYear,
       DATENAME(m, SellStartDate) as SellStartMonth,
       LEFT(ProductNumber, 2) AS ProductType
FROM SalesLT.Product
-- filter for numeric product size data
WHERE ISNUMERIC(Size) = 1;

 

 

 

 

15. 영업 관리자는 영업별로 순위가 매겨진 고객 목록을 원합니다.

- SalesOrderHeader 테이블에서 TotalDue 값이 가장 높은 목록에서 순위가있는company name 목록을 반환하는 쿼리를 작성하십시오.

-- select CompanyName and TotalDue columns
SELECT CompanyName, TotalDue AS Revenue,
       -- get ranking and order by appropriate column
       RANK() OVER (ORDER BY TotalDue DESC) AS RankByRevenue
FROM SalesLT.SalesOrderHeader AS SOH
-- use appropriate join on appropriate table
JOIN SalesLT.Customer AS C
ON SOH.CustomerID = C.CustomerID;

 

 

​

16. 제품 관리자가 제품 판매에 대한 집계 된 정보를 원합니다.

- product name 목록과 SalesLT.SalesOrderDetail 테이블에서 LineTotal의 합계로 계산 된 총 수익 목록을 검색하는 쿼리를 작성하고 결과는 총 수익의 내림차순으로 정렬됩니다.

SELECT Name, SUM(LineTotal) AS TotalRevenue
FROM SalesLT.SalesOrderDetail AS SOD
JOIN SalesLT.Product AS P
ON SOD.ProductID = P.ProductID
GROUP BY P.Name
ORDER BY TotalRevenue DESC;

 

 

 

​

17. 제품 관리자가 제품 판매에 대한 집계 된 정보를 원합니다.

- ListPrice이 1000 이상인 제품의 TotalRevenue를 포함하도록 이전 쿼리를 수정하십시오.

SELECT Name, SUM(LineTotal) AS TotalRevenue
FROM SalesLT.SalesOrderDetail AS SOD
JOIN SalesLT.Product AS P
ON SOD.ProductID = P.ProductID
WHERE P.ListPrice > 1000
GROUP BY P.Name
ORDER BY TotalRevenue DESC;