Mega Code Archive
Getting the Five Most Expensive Products
<![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 Name, Price FROM
2 (SELECT Name, Price
3 FROM Product
4 ORDER BY Price DESC, Name ASC)
5 WHERE ROWNUM<=5;
NAME PRICE
-------------------------------------------------- ----------
PC 49.99
Ruler 14.99
Mouse 9.99
Desk 5.99
Pen 5.99
5 rows selected.
SQL>
SQL> drop table product;
Table dropped.
SQL> drop sequence ProductIDSeq;
Sequence dropped.
]]>