Mega Code Archive
Order by a calculated column
<![CDATA[
SQL>
SQL> CREATE TABLE titles(
2 title_id CHAR(3) NOT NULL,
3 title_name VARCHAR(40) NOT NULL,
4 type VARCHAR(10) NULL ,
5 pub_id CHAR(3) NOT NULL,
6 pages INTEGER NULL ,
7 price DECIMAL(5,2) NULL ,
8 sales INTEGER NULL ,
9 pubdate DATE NULL ,
10 contract SMALLINT NOT NULL
11 );
Table created.
SQL>
SQL> INSERT INTO titles VALUES('T01','Java','history','P01',111,21.99,566,DATE '2000-08-01',1);
1 row created.
SQL> INSERT INTO titles VALUES('T02','Oracle','history','P03', 114,19.95,9566,DATE '1998-04-01',1);
1 row created.
SQL> INSERT INTO titles VALUES('T03','SQL','computer','P02', 122,39.95,25667,DATE '2000-09-01',1);
1 row created.
SQL> INSERT INTO titles VALUES('T04','C++','psychology','P04', 511,12.99,13001,DATE '1999-05-31',1);
1 row created.
SQL> INSERT INTO titles VALUES('T05','Python','psychology','P04', 101,6.95,201440,DATE '2001-01-01',1);
1 row created.
SQL> INSERT INTO titles VALUES('T06','JavaScript','biography','P01', 173,19.95,11320,DATE '2000-07-31',1);
1 row created.
SQL> INSERT INTO titles VALUES('T07','LINQ','biography','P03', 331,23.95,1500200,DATE '1999-10-01',1);
1 row created.
SQL> INSERT INTO titles VALUES('T08','C#','children','P04', 861,10.00,4095,DATE '2001-06-01',1);
1 row created.
SQL> INSERT INTO titles VALUES('T09','SQL Server','children','P04', 212,13.95,5000,DATE '2002-05-31',1);
1 row created.
SQL> INSERT INTO titles VALUES('T10','AJAX','biography','P01', NULL,NULL,NULL,NULL,0);
1 row created.
SQL> INSERT INTO titles VALUES('T11','VB','psychology','P04', 821,7.99,94123,DATE '2000-11-30',1);
1 row created.
SQL> INSERT INTO titles VALUES('T12','Office','biography','P01', 507,12.99,100001,DATE '2000-08-31',1);
1 row created.
SQL> INSERT INTO titles VALUES('T13','VBA','history','P03', 812,29.99,10467,DATE '1999-05-31',1);
1 row created.
SQL>
SQL> SELECT title_id,
2 price,
3 sales,
4 price * sales AS "Revenue"
5 FROM titles
6 ORDER BY "Revenue" DESC;
TIT PRICE SALES Revenue
--- ---------- ---------- ----------
T10
T07 23.95 1500200 35929790
T05 6.95 201440 1400008
T12 12.99 100001 1299012.99
T03 39.95 25667 1025396.65
T11 7.99 94123 752042.77
T13 29.99 10467 313905.33
T06 19.95 11320 225834
T02 19.95 9566 190841.7
T04 12.99 13001 168882.99
T09 13.95 5000 69750
TIT PRICE SALES Revenue
--- ---------- ---------- ----------
T08 10 4095 40950
T01 21.99 566 12446.34
13 rows selected.
SQL>
SQL> drop table titles;
Table dropped.
SQL>
]]>