|
SQL>
SQL>
SQL> CREATE TABLE Product (
2 ProductID INT NOT NULL PRIMARY KEY,
3 Name VARCHAR(50) NOT NULL,
4 Description VARCHAR(1000) NOT NULL,
5 Price NUMBER NULL,
6 ImagePath VARCHAR(50) NULL,
7 soldout NUMBER(1,0) NULL,
8 Promotion NUMBER(1,0) NULL);
Table created.
SQL>
SQL> CREATE SEQUENCE ProductIDSeq;
Sequence created.
SQL>
SQL> CREATE OR REPLACE TRIGGER ProductAutonumberTrigger
2 BEFORE INSERT ON Product
3 FOR EACH ROW
4 BEGIN
5 SELECT ProductIDSeq.NEXTVAL
6 INTO :NEW.ProductID FROM DUAL;
7 END;
8 /
Trigger created.
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath,soldout, Promotion)
2 VALUES ('Pen', 'Ball Pen',5.99, 'pen.jpg', 1, 0);
1 row created.
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ('Ruler', 'Long',14.99, 'ruler.jpg', 0, 0);
1 row created.
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ('Desk', 'Computer Desk',5.99, 'desk.jpg', 0, 1);
1 row created.
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ('PC', 'Notebook',49.99, 'pc.jpg', 0, 1);
1 row created.
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ('Mouse', 'Wireless',9.99, 'mouse.jpg', 1, 0);
1 row created.
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ('Keyboard', 'keyboard',3.75, 'keyboard.jpg', 0, 0);
1 row created.
SQL>
SQL> CREATE TABLE ProductCategory (
2 ProductID INT NOT NULL,
3 CategoryID INT NOT NULL,
4 PRIMARY KEY (ProductID, CategoryID)
5 );
Table created.
SQL>
SQL>
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (1,3);
1 row created.
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (2,1);
1 row created.
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (2,3);
1 row created.
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (3,3);
1 row created.
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (4,1);
1 row created.
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (5,2);
1 row created.
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (6,3);
1 row created.
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (6,4);
1 row created.
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (7,4);
1 row created.
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (8,5);
1 row created.
SQL>
SQL>
SQL> CREATE TABLE Category (
2 CategoryID INT NOT NULL PRIMARY KEY,
3 DepartmentID INT NOT NULL,
4 Name VARCHAR(50) NOT NULL,
5 Description VARCHAR (200) NULL);
Table created.
SQL>
SQL> CREATE SEQUENCE CategoryIDSeq;
Sequence created.
SQL>
SQL> CREATE OR REPLACE TRIGGER CategoryAutonumberTrigger
2 BEFORE INSERT ON Category
3 FOR EACH ROW
4 BEGIN
5 SELECT CategoryIDSeq.NEXTVAL
6 INTO :NEW.CategoryID FROM DUAL;
7 END;
8 /
Trigger created.
SQL> INSERT INTO Category (DepartmentID, Name, Description) VALUES (1, 'Local', 'In town');
1 row created.
SQL> INSERT INTO Category (DepartmentID, Name, Description) VALUES (1, 'Remote', 'Telecommute');
1 row created.
SQL> INSERT INTO Category (DepartmentID, Name, Description) VALUES (2, 'Masks', 'By bits');
1 row created.
SQL> INSERT INTO Category (DepartmentID, Name, Description) VALUES (3, 'Wireless', 'Not connected');
1 row created.
SQL> INSERT INTO Category (DepartmentID, Name, Description) VALUES (3, 'Wired', 'Connected');
1 row created.
SQL>
SQL> SELECT CategoryName, ProductName, ProductPrice
2 FROM
3 (SELECT C.Name as CategoryName,
4 P.Name as ProductName,
5 P.Price as ProductPrice,
6 RANK() OVER (PARTITION BY C.Name ORDER BY P.Price DESC)
7 AS rank
8 FROM Product P
9 JOIN ProductCategory PC ON P.ProductID = PC.ProductID
10 JOIN Category C ON C.CategoryID = PC.CategoryID)
11 WHERE rank <= 2
12 ORDER BY CategoryName;
CATEGORYNAME
--------------------------------------------------
PRODUCTNAME PRODUCTPRICE
-------------------------------------------------- ------------
Local
PC 49.99
Local
Ruler 14.99
Masks
Ruler 14.99
Masks
Pen 5.99
Masks
Desk 5.99
Remote
Mouse 9.99
Wireless
Keyboard 3.75
7 rows selected.
SQL>
SQL>
SQL> drop table Product;
Table dropped.
SQL> drop table ProductCategory;
Table dropped.
SQL> drop table Category;
Table dropped.
SQL> drop sequence CategoryIDSeq;
Sequence dropped.
SQL> drop sequence ProductIDSeq;
Sequence dropped.
|