지난번 포스팅에서는 웹으로 간단하게 SQL 을 연습할 수 있는 온라인 사이트로서 


w3schools.com/sql 과 테이블을 쿼리해서 데이터 항목을 알아보았습니다. 


주문, 주문상세 테이블과 고객, 상품, 상품카테고리, 상품제공업체, 배송업체, 종업원의 기준정보 테이블이 있는 것으로 봐서 유통업체의 데이터임을 알 수 있습니다. 이벤트나 프로모션 정보 테이블, 온라인이나 모바일 등의 채널 이용 정보 테이블, 고객등급/고객세분화 정보 테이블,  결제수단 정보 테이블 등... 뭐, 유통업체라면 더 많은 테이블이 있어야 겠지만서도, SQL 연습하라고 만든 가상의 약식 데이터 DB 테이블이므로 '이 정도도 어디야'하고 감사하면 사용하면 좋겠습니다. 


ERD (Entity Relationship Diagram)이 없어서 테이블, 데이터 간의 관계를 한 눈에 파악하는 것이 어려웠는데요, 시간 좀 내서 아래처럼 ERD 그려보았습니다. 


[ 유통업체 ERD (Entity Relationship Diagram) ]


* https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all  에 있는 테이블별 칼럼을 보고 추측해서 ERD 그린 것임.  SQL 연습하려는 분은 이 ERD 참고해서 테이블 간 join 해서 분석하면 됨. 



테이블을 여러개 Join 해서 통계량 집계하고 정렬하는 예를 들어보겠습니다. 



 
[SQL Query 문제] 


"제품 카테고리 중 'Dairy Products', 'Grains/Cereals', 'Seafood', 'Condiments' 카테고리에 대해서 카테고리별로 판매가 일어난(주문이 발생한) 제품들의 가격의 합계, 총 주문 발생 회수, 제품들의 가격의 평균을 구하시오.  


단, 카테고리별 제품 가격의 합계가 1,100 이상인 경우만 집계 결과를 제시하되, 

가격의 합계를 기준으로 내림차순으로 정렬하여 제시하시오."


SELECT e.CategoryName AS CategoryName, 

        SUM(e.Price) AS Price_sum, 

        COUNT(*) AS Order_cnt, 

        AVG(e.Price) AS Price_avg

    FROM 

    (SELECT c.OrderID, c.ProductID, c.Price, c.CategoryID, d.CategoryName  -- sub query 2

     FROM (SELECT a.OrderID, a.ProductID, b.Price, b.CategoryID  -- sub query 1

           FROM OrderDetails a

           INNER JOIN Products b ON a.ProductID = b.ProductID) c

     LEFT JOIN Categories d ON c.CategoryID = d.CategoryID) e

     WHERE e.CategoryName IN ('Dairy Products', 'Grains/Cereals', 'Seafood', 'Condiments')

     GROUP BY e.CategoryName

     HAVING Price_sum > 1100

     ORDER BY Price_sum DESC;   

 

 

Number of Records: 3

====================================================

CategoryName        Price_sum        Order_cnt           Price_avg

====================================================

Dairy Products         2863.2                  100               28.63

Seafood                  1345.17                    67               20.07

Condiments           1121.5                    49               22.88




위의 문제가 너무 복잡하고, SQL Query도 SUM(), COUNT(), AVG() 등의 aggregation 함수, FROM 절에 Sub Query 랑 INNER JOIN, LEFT JOIN 이 들어가 있고, WHERE 조건절, GROUP BY, HAVING, ORDER BY 등 어지간한 SQL 기능이 망라되어 있어서 복잡하긴 합니다. 


Query가 잘 이해가 안되면 Sub Query를 하나씩 순차적으로 실행시켜보면서 결과를 확인해보면 한결 이해하기가 쉽습니다. 


예를 들어보자면, 위의 Query를 가장 안에 위치한 Sub Query 부터 하나씩 아래에 풀어보겠습니다. 


=========================================================================


[ sub query 1]


OrderDetails 테이블(a)Products 테이블(b)ProductID key를 기준으로 INNER JOIN으로 교집합을 구해서 Products 테이블에서 상품의 가격과 카테고리ID 데이터를 가져왔습니다. (테이블 구분하기 편하라고 a, b 라는 alias name 별명을 부여해서 변수 앞에 b.Price 처럼 붙여서 사용합니다)  상위 5개만 예시로 가져오겠습니다. 



SELECT a.OrderID, a.ProductID, b.Price, b.CategoryID

           FROM OrderDetails a

           INNER JOIN Products b ON a.ProductID = b.ProductID

           LIMIT 5;

 

 

OrderID   ProductID   Price   CategoryID

10248 11                 21         4

10248 42                 14         5

10248 72                 34.8         4

10249 14                 23.25 7

10249 51                 53         7





=========================================================================


[sub query 2]


위의 'sub query 1' 결과 테이블(c)에다가 CategoryID key를 기준으로 Categories 테이블(d)을 LEFT JOIN 하여 Categories 테이블에 있는 CategoryName 칼럼을 붙여서 가져왔습니다.  CategoryName 을 붙여 와야지 원래의 SQL Query 문제에 나와있는 'CategoryName별 판매상품 가격의 합계, 판매(주문)회수, 평균판매가격'을 구할 수 있겠지요?



SELECT c.OrderID, c.ProductID, c.Price, c.CategoryID, d.CategoryName

     FROM (SELECT a.OrderID, a.ProductID, b.Price, b.CategoryID

           FROM OrderDetails a

           INNER JOIN Products b ON a.ProductID = b.ProductID) c

     LEFT JOIN Categories d ON c.CategoryID = d.CategoryID

     LIMIT 5;

 


c.OrderID   c.ProductID  c.Price  c.CategoryID   d.CategoryName

10248 11                 21         4                 Dairy Products

10248 42                 14         5                 Grains/Cereals

10248 72                 34.8         4                 Dairy Products

10249 14                 23.25 7                 Produce

10249 51                 53         7                 Produce

 




========================================================================


아래 Query 는 CASE WHEN ~ THEN ... ELSE ... END 문으로 연속형 변수(continuous variable)를 범주형 변수(categorical variable) 로 변환하는 예제 Query 입니다.  아래처럼 '가격대(Price_grp)' 변수를 만든 후에 위에 'SQL Query' 문제에서 사용했던 Query 를 사용해서 다른 응용을 할 수 있습니다. 



SELECT a.OrderID, a.ProductID, b.Price, 

       CASE WHEN b.Price >= 40 THEN '1_over_40'

                WHEN b.Price < 40 AND b.Price >= 20 THEN '2_20_40'

                ELSE '3_under_20' END Price_grp

    FROM OrderDetails a

    INNER JOIN Products b ON a.ProductID = b.ProductID

    LIMIT 10;

 


OrderID ProductID Price Price_grp

10248 11       21         2_20_40

10248 42       14         3_under_20

10248 72       34.8         2_20_40

10249 14       23.25         2_20_40

10249 51       53         1_over_40

10250 41       9.65         3_under_20

10250 51       53         1_over_40

10250 65       21.05         2_20_40

10251 22       21         2_20_40

10251 57       19.5         3_under_20




많은 도움이 되었기를 바랍니다. 


이번 포스팅이 도움이 되었다면 아래의 '공감~'를 꾸욱 눌러주세요. ^^



728x90
반응형
Posted by Rfriend
,

집에서 개인 컴퓨터로 SQL 연습을 하고 싶은데 


- 상용 DBMS 평가판 혹은 오픈 소스 DBMS 설치하자니 힘들고

- DB, Table 생성하고, 데이터 파일을 구해서 import 하거나 

  혹은 건건이 insert 하기에 힘들고


할 때 아주 쉽고 빠르게, 간편하게 웹 상에서 SQL 연습할 수 있는 사이트가 있어서 소개합니다. 


w3schools.com 이라는 곳에서 다양한 언어의 튜토리얼을 제공하는데요, 그 중에서 SQL도 튜토리얼과 함께 연습할 수 있는 웹 환경도 제공하고 있습니다. MySQL, SQL Server, MS Access, Oracle, Sybase, Informix, Postgres 등의 다양한 DB에 대한 SQL 튜토리얼을 제공하니 이곳만 잘 이용해도 특정 DB를 염두에 두고 쓰여진 SQL 책보다 더 유용할 수도 있겠습니다. Data 도 준비가 다 되어있어서 그냥 웹에 접속해서 연습하면 됩니다. 


단, Chrome, Safari, FireFox 브라우저만 지원하고, Internet Explorer 는 지원하지 않습니다. 


접속할 주소는요, 


https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all 


이며, 아래와 같은 화면이 나타납니다. 


[ w3schools.comSQL 연습할 수 있는 초기 화면 ]




순서대로 살펴보면 

(1) 왼쪽 상단에 SQL 을 입력할 수 있는 'SQL Statement: ' 창이 있습니다. 

(2) 왼쭉 중간에 'Run SQL >' 이라는 네모 단추가 있는데요, 이를 커서로 클릭하면 SQL이 실행됩니다. 

(3) 왼쪽 하단에 'Reslut: ' 란에 SQL 실행 결과가 나타납니다. 

(4) 우측 상단에 보면 Database에 들어있는 Table 이름과 Record 수가 나옵니다. 


아래의 이름으로 총 8개의 Table에 있는 데이터를 SQL 연습하는데 사용할 수 있습니다! 


No.

Tablename

Records 

1

Customers

91 

 Categories

 Employees

10 

 OrderDetails

518 

Orders

196 

 Products

77 

 Shippers

 Suppliers

29 



ERD (Entity Relationship Diagram) 이 있으면 좋을 텐데요, 그게 없는지... 못 찾겠네요. 


각 Table 별로 상위 5개씩 Select 해서 조회를 해보면 아래와 같습니다. 

각 Table 이름을 봐도 그렇고, 상위 5개 records 조회를 해서 봐도 그렇고, 유통업체에서 사용하는 DB table 들을 예로 간단한 예제 DB를 제공한다고 보면 되겠습니다. 


SELECT

    FROM Customers

    LIMIT 5; 


CustomerID CustomerName ContactName Address City PostalCode Country

1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany

2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico

3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico

4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK

5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden



SELECT

    FROM Categories

    LIMIT 5;


 CategoryID CategoryName Description

1                 Beverage                 Soft drinks, coffees, teas, beers, and ales

2                 Condiments         Sweet and savory sauces, relishes, spreads, and seasonings

3                 Confections         Desserts, candies, and sweet breads

4                 Dairy Products    Cheeses

5                 Grains/Cereals         Breads, crackers, pasta, and cereal



SELECT

    FROM Employees

    LIMIT 1; 

 

EmployeeID LastName FirstName BirthDate Photo         Notes

1                 Davolio         Nancy         1968-12-08 EmpID1.pic Education includes a BA in psychology from Colorado State University. She also completed (The Art of the Cold Call). Nancy is a member of 'Toastmasters International'.



SELECT

    FROM OrderDetails

    LIMIT 5;


OrderDetailID OrderID ProductID Quantity

1                 10248 11                 12

2                 10248 42                 10

3                 10248 72                 5

4                 10249 14                 9

5                 10249 51                 40 



SELECT

    FROM Orders

    LIMIT 5;

 

OrderID CustomerID EmployeeID OrderDate ShipperID

10248 90                 5                 1996-07-04 3

10249 81                 6                 1996-07-05 1

10250 34                 4                 1996-07-08 2

10251 84                 3                 1996-07-08 1

10252 76                 4                 1996-07-09 2



SELECT

    FROM Products

    LIMIT 5;


ProductID ProductName SupplierID CategoryID Unit Price

1 Chais 1 1 10 boxes x 20 bags 18

2 Chang 1 1 24 - 12 oz bottles 19

3 Aniseed Syrup 1 2 12 - 550 ml bottles 10

4 Chef Anton's Cajun Seasoning 2 2 48 - 6 oz jars 22

5 Chef Anton's Gumbo Mix 2 2 36 boxes 21.35



SELECT

    FROM Shippers; 


ShipperID ShipperName         Phone

1                 Speedy Express (503) 555-9831

2                 United Package (503) 555-3199

3                 Federal Shipping (503) 555-9931

 


SELECT

    FROM Suppliers

    LIMIT 2; 

 

SupplierID SupplierName ContactName Address City PostalCode Country Phone

1 Exotic Liquid Charlotte Cooper 49 Gilbert St. Londona EC1 4SD UK (171) 555-2222

2 New Orleans Cajun Delights Shelley Burke P.O. Box 78934 New Orleans 70117 USA (100) 555-4822




다음번 포스팅에서는 ERD 한번 그려서 올려보겠습니다. 그리고 table 간 Join 도 해보고, aggregation 함수도 몇 개 예를 들어서 한번 더 포스팅해보겠습니다. 


SQL 집에서 간단하게 공부하시려는 분들에게 도움이 되었기를 바랍니다. 

이번 포스팅이 도움이 되셨다면 아래의 '공감~'를 꾸욱 눌러주세요. ^^



728x90
반응형
Posted by Rfriend
,