Mega Code Archive
Browse Products with three level nested queries
<![CDATA[
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> SELECT ProductID, Name FROM
2 (SELECT ProductID, Name, ROWNUM AS rn FROM
3 (SELECT ProductID, Name
4 FROM Product
5 ORDER BY ProductID)
6 ) inner
7 WHERE inner.rn BETWEEN 6 AND 10;
PRODUCTID NAME
---------- --------------------------------------------------
6 Keyboard
1 row selected.
SQL>
SQL> drop table product;
Table dropped.
SQL> drop sequence ProductIDSeq;
Sequence dropped.
]]>