Whiteship's Note

Chapter 14: Case Study: Building a Product Catalog

DB/SQL : 2008.01.08 16:23


참조 : 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%');
- 여러 컬럼에서 특정 문자열 검색할 때 꼼수로 모든 검색 대상 필드를 문자열로 변환하여 붙인다음에 검색.


top

TAG ,



: 1 : 2 : 3 : 4 : 5 : 6 : ··· : 15 :