8.2 Complex query

복잡한 쿼리 작성하기

데이터베이스 사용자는 여러 테이블에서 데이터를 조인하여 비즈니스 질문에 답하는 복잡한 SQL 쿼리를 자주 작성한다. 예를 들어, 서점은 "여름에 가장 많이 팔리는 책은 무엇인가?" 또는 "서부 해안 지역 고객들이 구매하는 책의 유형은 무엇인가?"와 같은 질문을 할 수 있다.

 

복잡한 쿼리를 작성하기 위해 데이터베이스 사용자는 다음과 같은 전략을 사용할 수 있다:

1. 테이블 다이어그램 또는 다른 데이터베이스 요약을 검토하여 테이블과 관계를 이해한다.
2. 질문에 답하기 위해 필요한 데이터를 포함하는 테이블을 식별한다.
3. 결과 테이블에 표시될 열을 결정한다.
4. 테이블의 기본 키와 외래 키를 사용하여 테이블을 조인하는 쿼리를 작성한다.
5. 문제를 단순한 쿼리로 나누어, 쿼리의 각 부분을 하나씩 작성한다.

Zion Bookstore는 2020년 2월에 Colorado 또는 Oklahoma 출신 고객들에게 단일 저자가 작성한 책 중에서 가장 많은 매출을 올린 책이 무엇인지 알고 싶어 한다. 이 질문에 답하기 위해 필요한 정보는 여러 테이블에 분산되어 있으므로 복잡한 쿼리가 필요하다.

 

아래 그림의 테이블 다이어그램은 Zion Bookstore 데이터베이스를 설명하며, 책, 고객 및 판매를 추적한다.

Figure 8.2.1: Table diagram for Zion Bookstore database

---

Joining tables

Zion Bookstore의 질문에 답하기 위해 결과 테이블에는 이전에 식별한 테이블의 열 또는 테이블에서 계산할 수 있는 열이 포함되어야 한다. 결과 테이블에는 다음 정보가 포함되어야 한다: 고객 주 (Customer.State), 책 ID (Sale.BookID), 책 제목 (Book.Title), 구매한 책 수량 (Sale.Quantity), 총 가격 (Sale.Quantity × Sale.UnitPrice).

---

participation activity | 8.2.2: Join the tables

Tables for the Zion Bookstore database are created and populated below. Run a query that joins the Sale, Customer, and Book tables:

CREATE TABLE Author (
  ID INT NOT NULL,
  FirstName VARCHAR(45) DEFAULT NULL,
  LastName VARCHAR(45) DEFAULT NULL,
  BirthDate DATE DEFAULT NULL,
  PRIMARY KEY (ID)
);

INSERT INTO Author VALUES
(1,'Jennifer','McCoy', '1980-05-01'),
(2,'Yuto','Takahashi', '1973-12-04'),
(3,'Jose','Martinez', NULL),
(4,'Jasmine','Baxter', NULL),
(5,'Xiu','Tao', '1992-11-13'),
(6,'Ethan','Lonestar', '1965-02-15'),
(7,'Amar','Agarwal', NULL),
(8,'Emilia','Russo', '1999-08-03');

CREATE TABLE Book (
  ID INT NOT NULL,
  Title VARCHAR(200) DEFAULT NULL,
  Publisher VARCHAR(45) DEFAULT NULL,
  Category VARCHAR(20) CHECK (Category IN ('adventure','drama','fantasy','humor','romance','scifi')),
  Price DECIMAL(6,2) DEFAULT NULL,
  PRIMARY KEY (ID)
);

INSERT INTO Book VALUES
(100,'The Black Box','Wright Pub','adventure',22.50),
(101,'Lost Time','Caster','scifi',19.99),
(102,'Which Way Home?','Light House','humor',8.99),
(103,'Grant Me Three Wishes','Caster','romance',10.75),
(104,'The Last Attempt','Longshot','scifi',15.99),
(105,'My Crazy Life','Light House','humor',9.67);


CREATE TABLE BookAuthor (
  AuthorID INT NOT NULL,
  BookID INT NOT NULL,
  PRIMARY KEY (AuthorID, BookID),
  FOREIGN KEY (AuthorID) REFERENCES Author (ID),
  FOREIGN KEY (BookID) REFERENCES Book (ID) 
);

INSERT INTO BookAuthor VALUES
(2,100),
(3,100),
(4,101),
(7,102),
(8,102),
(5,103),
(1,104),
(8,105);

CREATE TABLE Customer (
  ID INT NOT NULL,
  FirstName VARCHAR(45) DEFAULT NULL,
  LastName VARCHAR(45) DEFAULT NULL,
  Address VARCHAR(45) DEFAULT NULL,
  City VARCHAR(45) DEFAULT NULL,
  State CHAR(2) DEFAULT NULL,
  Zip INT DEFAULT NULL,
  PRIMARY KEY (ID)
);

INSERT INTO Customer VALUES
(1,'Jill','Halloway','101 Main St','Boulder','CO',80301),
(2,'Mario','Androsa','4545 N 1st St','Tulsa','OK',74008),
(3,'Erica','MacCallum','999 S Fir Ct','Breckenridge','CO',80424),
(4,'John','Patrick de la Cruz','501 N Main','Albuquerque','NM',87109),
(5,'Anna','Green','600 S Main','Los Alamos','NM',87544),
(6,'Mo','Yan','211 W Lipan','Edmond','OK',73013),
(7,'Alexis','Brookhart','9000 S Greenwood','Englewood','CO',80110),
(8,'Hala','Nassar','840 N Pine St','Oklahoma City','OK',73008);


CREATE TABLE Sale (
  ID INT AUTO_INCREMENT NOT NULL,
  CustID INT DEFAULT NULL,
  BookID INT DEFAULT NULL,
  UnitPrice DECIMAL(6,2) DEFAULT NULL,
  Quantity INT DEFAULT NULL,
  Date DATETIME DEFAULT NULL,
  PRIMARY KEY (ID),
  FOREIGN KEY (CustID) REFERENCES Customer (ID),
  FOREIGN KEY (BookID) REFERENCES Book (ID)
);

INSERT INTO Sale (CustID, BookID, UnitPrice, Quantity, Date) VALUES 
(1,103,10.99,1,'2020-02-14 12:30:00'),
(2,104,15.5,2,'2020-02-15 14:15:00'),
(3,101,21.99,1,'2020-02-15 19:10:00'),
(6,104,16.00,2,'2020-02-15 19:25:00'),
(4,102,9.75,1,'2020-02-16 11:05:00'),
(4,105,9.50,1,'2020-02-16 11:05:00'),
(2,102,9.75,1,'2020-02-16 11:30:00'),
(5,104,16.0,1,'2020-02-16 15:25:00'),
(6,100,22.3,2,'2020-02-16 16:00:00'),
(2,103,10.99,1,'2020-02-17 12:30:00'),
(7,103,14.99,3,'2020-02-21 14:25:00'),
(7,104,16.00,1,'2020-02-21 14:25:00'),
(7,105,10.00,1,'2020-02-21 14:25:00'),
(1,101,15.20,1,'2020-02-22 13:15:00'),
(2,105,9.50,1,'2020-03-05 10:18:00'),
(1,100,22.5,1,'2021-02-01 09:15:00'),
(2,103,10.99,1,'2021-02-01 13:15:00'),
(2,101,15.60,1,'2021-02-01 13:15:00'),
(3,103,14.99,1,'2021-02-01 13:15:00');

   
-- Write your query here:

SELECT S.CustID, C.State, S.BookID, B.Title, S.Quantity, S.UnitPrice * S.Quantity 
FROM Sale S
INNER JOIN Customer C ON C.ID = S.CustID
INNER JOIN Book AS B ON B.ID = S.BookID;

Grouping by state and book

결과 테이블에는 책과 주별 총 판매량이 표시되어야 한다.

participation activity | 8.2.4: Group by state and book

SELECT C.State, S.BookID, B.Title, SUM(S.Quantity) AS Quantity, SUM(S.UnitPrice * S.Quantity) AS TotalSales
FROM Sale S
INNER JOIN Customer C ON C.ID = S.CustID
INNER JOIN Book AS B ON B.ID = S.BookID 
GROUP BY C.State, S.BookID
ORDER BY TotalSales DESC;

Filtering states and dates

결과 테이블은 콜로라도와 오클라호마에서의 결과만 보여야 한다. 2020년 2월에 이루어진 구매만 고려해야 한다. 모든 필터링 기준은 쿼리의 WHERE 절에 명시해야 한다.

participation activity | 8.2.6: Filtering states and dates

Add a WHERE clause to restrict the result table to sales from Colorado and Oklahoma only. Use the MONTH() and YEAR() functions to select only sales in month 2 and year 2020.

SELECT C.State, S.BookID, B.Title, SUM(S.Quantity) AS Quantity, SUM(S.UnitPrice * S.Quantity) AS TotalSales
FROM Sale S
INNER JOIN Customer C ON C.ID = S.CustID
INNER JOIN Book AS B ON B.ID = S.BookID 
WHERE (C.State = 'CO' OR C.State = 'OK') AND MONTH(S.Date) = 2 AND YEAR(S.Date) = 2020
GROUP BY C.State, S.BookID
ORDER BY TotalSales DESC;

Books with single author

결과 테이블은 단일 저자가 쓴 책만 포함해야 한다. BookAuthor 테이블의 행은 한 저자가 한 책에 할당된 것을 나타낸다. 예를 들어, AuthorID = 5와 BookID = 103인 행은 저자 5가 책 103을 썼음을 의미한다. 여러 저자가 있는 책은 여러 행으로 표현되므로, 주어진 BookID에 대해 BookAuthor 테이블에서 행의 개수를 확인하는 서브쿼리를 사용하여 결과를 단일 저자의 책으로 제한할 수 있다.

participation activity | 8.2.8: Books with single author

Modify the WHERE clause to use a subquery. The subquery uses a HAVING clause with COUNT() to select only book IDs that appear in one row of BookAuthor.

SELECT C.State, S.BookID, B.Title, SUM(S.Quantity) AS Quantity, SUM(S.UnitPrice * S.Quantity) AS TotalSales
FROM Sale S
INNER JOIN Customer C ON C.ID = S.CustID
INNER JOIN Book AS B ON B.ID = S.BookID 
WHERE (C.State = 'CO' OR C.State = 'OK') AND MONTH(S.Date) = 2 AND YEAR(S.Date) = 2020 AND B.ID IN 
   (SELECT BookID 
   FROM BookAuthor
   GROUP BY BookID
   HAVING COUNT(*) = 1)
GROUP BY C.State, S.BookID
ORDER BY TotalSales DESC;