Oracle Sql with subqueries

Question

I'm using the same scott schema.the questiion goes like this. Display the empno,sal,TA,DA,HRA,Total allowances,Insurance,TDS,Netdeductions,Netsal with descriptive labels.

here Ta is 30% of sal, DA is 40% of sal,hra is 50% of sal, insurance is 5%sal and tds is 5%sal. Total allowances is ta + da+ hra. total deductions is ins + tds now netsal is (sal + allowances-total deductions)

I'm trying to achieve this using with statement

WITH T1 AS
 (SELECT (30 / 100 * SAL) AS TA FROM EMP),
T2 AS
 (SELECT (40 / 100 * SAL) AS DA FROM EMP),
T3 AS
 (SELECT (50 / 100 * SAL) AS HRA FROM EMP)
SELECT Empno, TA, DA, HRA, (ta + da + hra)
  FROM EMP, T1, T2, T3
 group by empno;

something like this.The logic is wrong I can understand that.

Why in subqueries and why are there no table joins? would select *, 30 / 100 * SAL) AS TA ,(40 / 100 * SAL) AS DA , (50 / 100 * SAL) AS HRA from emp give the same result? — Twelfth
– Twelfth, Commented Aug 1, 2014 at 21:55

Gordon Linoff · Accepted Answer · 2014-08-01 22:02:01Z

2

Just do this in a single query:

select e.*, (ta + da + hra) as total_allowances,
       (sal + (ta + da + hra) - (ins + tds)) as net_salary
from (select empno, (sal * 30/100) as ta, (sal * 40/100) as da, 
             (sal * 50/100) as hra,
             (sal * 0.05) as ins,
             (sal * 0.05) as tds
      from emp
     ) e;

I don't understand what the group by is for. You have no aggregation functions and with a table name like emp, I would expect each row to have a unique value for empno.

answered Aug 1, 2014 at 22:02

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Strafer Over a year ago

Works for me..though I was trying to do it using the "with" clause anywaz thanks!

Brian DeMilia · Accepted Answer · 2014-08-01 22:01:37Z

select empno,
       sal,
       .3 * sal as ta,
       .4 * sal as da,
       .5 * sal as hra,
       .05 * sal as insurance,
       .05 * sal as tds,
       (.3 * sal) + (.4 * sal) + (.5 * sal) as total_allowances,
       (.05 * sal) + (.05 * sal) as total_deductions,
       sal + ((.3 * sal) + (.4 * sal) + (.5 * sal)) - ((.05 * sal) + (.05 * sal)) as netsal
  from emp

I don't know what you're trying to achieve the WITH clause. Your t1/t2 queries are separately performing each calculation throughout the whole table. You can do each calculation in the same query. And it does not appear that any more than one table is involved, so you do not need any joins.

Collectives™ on Stack Overflow

Oracle Sql with subqueries

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related