Getting the Five Most Expensive Products

    
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> 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.

Related examples in the same category

1.	Use rownum in select clause
2.	Use rownum in update set statement
3.	Use rownum in where clause to control the row count
4.	Use rownum column with order by
5.	Use rownum in where clause to limit the row count
6.	Limit the query to display only the top 3 highest paid employees.
7.	Use rownum to limit the subquery
8.	Use rownum = 1 and select into
9.	Retrieving the Top Five Students with ROWNUM

Getting the Five Most Expensive Products : ROWNUM « Select Query « Oracle PL / SQL

Related examples in the same category