SQL>
SQL> CREATE TABLE contract
2 (income INTEGER
3 ,overhead INTEGER);
Table created.
SQL>
SQL> INSERT INTO contract VALUES (1000,20);
1 row created.
SQL> INSERT INTO contract VALUES (2000,10);
1 row created.
SQL> INSERT INTO contract VALUES (1000,50);
1 row created.
SQL>
SQL> SELECT income,
2 overhead,
3 (income-income*overhead/100) AS residual,
4 0.20*(income-income*overhead/100) AS Est,
5 0.10*(income-income*overhead/100) AS Admin,
6 0.05*(income-income*overhead/100) AS Rsrv
7 FROM contract;
INCOME OVERHEAD RESIDUAL EST ADMIN RSRV
---------- ---------- ---------- ---------- ---------- ----------
1000 20 800 160 80 40
2000 10 1800 360 180 90
1000 50 500 100 50 25
SQL> SELECT income,
2 overhead,
3 residual,
4 0.20*residual AS Est,
5 0.10*residual AS Admin,
6 0.05*residual AS Rsrv
7 FROM
8 (SELECT income, overhead, (income-income*overhead/100) AS residual
9 FROM contract) subquery;
INCOME OVERHEAD RESIDUAL EST ADMIN RSRV
---------- ---------- ---------- ---------- ---------- ----------
1000 20 800 160 80 40
2000 10 1800 360 180 90
1000 50 500 100 50 25
SQL> CREATE VIEW residual1 AS
2 SELECT income, overhead, (income-income*overhead/100) AS residual
3 FROM contract;
View created.
SQL>
SQL> SELECT income,
2 overhead,
3 residual,
4 0.20*residual AS Est,
5 0.10*residual AS Admin,
6 0.05*residual AS Rsrv
7 FROM residual1;
INCOME OVERHEAD RESIDUAL EST ADMIN RSRV
---------- ---------- ---------- ---------- ---------- ----------
1000 20 800 160 80 40
2000 10 1800 360 180 90
1000 50 500 100 50 25
SQL> SELECT subquery.*,
2 0.20*residual AS Est,
3 0.10*residual AS Admin,
4 0.05*residual AS Rsrv
5 FROM
6 (SELECT contract.*, (income-income*overhead/100) AS residual
7 FROM contract) subquery;
INCOME OVERHEAD RESIDUAL EST ADMIN RSRV
---------- ---------- ---------- ---------- ---------- ----------
1000 20 800 160 80 40
2000 10 1800 360 180 90
1000 50 500 100 50 25
SQL>
SQL> DROP VIEW residual1;
View dropped.
SQL> DROP TABLE contract;
Table dropped.
SQL>