Chapter 14: Case Study: Building a Product Catalog
참조 : The Programmer's Guide to SQL
오라클에서 테이블 만들기
CREATE TABLE Category (
CategoryID INT NOT NULL PRIMARY KEY,
DepartmentID INT NOT NULL,
Name VARCHAR(50) NOT NULL,
Description VARCHAR (200) NULL,
FOREIGN KEY (DepartmentID) REFERENCES Department (DepartmentID));
CREATE SEQUENCE CategoryIDSeq;
CREATE OR REPLACE TRIGGER CategoryAutonumberTrigger
BEFORE INSERT ON Category
FOR EACH ROW
BEGIN
SELECT CategoryIDSeq.NEXTVAL
INTO :NEW.CategoryID FROM DUAL;
END;
/
- 시퀀스 만들고, 트리거 만들어서 자동 증가값처럼 사용하기.
CREATE TABLE ProductCategory (
ProductID INT NOT NULL,
CategoryID INT NOT NULL,
PRIMARY KEY (ProductID, CategoryID),
FOREIGN KEY (ProductID) REFERENCES Product (ProductID),
FOREIGN KEY (CategoryID) REFERENCES Category (CategoryID)
);
- Junction 테이블.
데이터 가져오기
SELECT Name, Price FROM
(SELECT Name, Price
FROM Product
ORDER BY Price DESC, Name ASC)
WHERE ROWNUM<=5;
- 상위 다섯개의 데이터 가져오기.
- id를 사용하지 않는다. id가 꼭 순서대로 매겨진다는 보장이 없으니까..
SELECT ProductID, Name FROM
(
SELECT RANK() OVER (ORDER BY ProductID) As Ranking, ProductID, Name
FROM Product
ORDER BY PRODUCTID
)
WHERE Ranking BETWEEN 6 AND 8;
- 특정 범위의 값 가져오기.
SELECT C.Name as "Category Name", P.Name as "Product Name"
FROM Product P
INNER JOIN ProductCategory PC ON P.ProductID = PC.ProductID
INNER JOIN Category C ON PC.CategoryID = C.CategoryID
ORDER BY C.Name, P.Name;
=> WHERE 절을 사용하도록 변경하면...
SELECT C.Name as "Category Name", P.Name as "Product Name"
FROM Product P, ProductCategory PC, Category C
WHERE P.ProductID = PC.ProductID AND PC.CategoryID = C.CategoryID
ORDER BY C.Name, P.Name;
=> 연관된 서브쿼리를 사용하도록 변경하면..
SELECT C.Name as "Category Name", Product.Name as "Product Name"
FROM Product, Category C
WHERE Product.ProductID IN
(SELECT ProductID FROM ProductCategory
WHERE ProductCategory.CategoryID = C.CategoryID)
ORDER BY C.Name, Product.Name;
데이터 검색
SELECT Name, Description
FROM Product
WHERE (Description || Name LIKE '%devil%')
AND (Description || Name LIKE '%mask%');
- 여러 컬럼에서 특정 문자열 검색할 때 꼼수로 모든 검색 대상 필드를 문자열로 변환하여 붙인다음에 검색.