Inline Queries

------------------Inline View ---------------

SELECT E.Ename, E.Sal , E.Deptno, E1.SalAvg

FROM Emp E, (SELECT Deptno, AVG(Sal) SalAvg

            FROM Emp

            GROUP BY Deptno) E1

WHERE E.Deptno = E1.Deptno AND

E.Sal > E1.SalAvg;

SELECT E.Ename, E.Sal, E.Deptno,

ROUND(E1.SalAvg, 2) DeptAvgSal,

ROUND(E.Sal - E1.SalAvg) DiffSalAvg

FROM Emp E, (SELECT Deptno, AVG(Sal) SalAvg

            FROM Emp

            GROUP BY Deptno) E1

WHERE E.Deptno = E1.Deptno

ORDER BY Deptno;

SELECT E.Ename, E.Sal, E.Deptno, E1.SalSum

FROM Emp E, (SELECT Deptno, SUM(Sal) SalSUM

            FROM Emp

            GROUP BY Deptno) E1

WHERE E.Deptno = E1.Deptno

ORDER BY Deptno;

SELECT T1.Deptno, Dname, Staff

FROM Dept T1,

(SELECT Deptno, COUNT(*) AS Staff

FROM Emp

GROUP BY Deptno) T2

WHERE T1.Deptno = T2.Deptno

AND Staff >= 5;

SELECT Deptno, SUM(Sal),

SUM(Sal)/Tot_Sal * 100 "Salary%"

FROM Emp,

(SELECT SUM(Sal) Tot_Sal

 FROM Emp)

GROUP BY Deptno, Tot_Sal;

SELECT Job, SUM(Sal),

ROUND(SUM(Sal)/Tot_Sal * 100, 2) "Salary%"

FROM Emp, (SELECT SUM(Sal) Tot_Sal

FROM Emp)

GROUP BY Job, Tot_Sal;

SELECT TO_CHAR(HireDate, 'YYYY') Year,

SUM(Sal),

ROUND(SUM(Sal)/Tot_Sal * 100, 2) "Salary%"

FROM Emp, (SELECT SUM(Sal) Tot_Sal

FROM Emp)

GROUP BY TO_CHAR(HireDate, 'YYYY'), Tot_Sal;

SELECT

TO_CHAR(HireDate, 'YYYY') Year,

TO_CHAR(HireDate, 'Month') "Month",

SUM(Sal),

ROUND(SUM(Sal)/Tot_Sal * 100, 2) "Salary%"

FROM Emp, (SELECT SUM(Sal) Tot_Sal

FROM Emp)

WHERE TO_CHAR(HireDate, 'YYYY') = &GiveYear

GROUP BY TO_CHAR(HireDate, 'YYYY'), TO_CHAR(HireDate, 'Month'), Tot_Sal;

SELECT

TO_CHAR(HireDate, 'YYYY') Year,

Deptno,

TO_CHAR(HireDate, 'Month') "Month",

SUM(Sal),

ROUND(SUM(Sal)/Tot_Sal * 100, 2) "Salary%"

FROM Emp, (SELECT SUM(Sal) Tot_Sal

FROM Emp)

WHERE TO_CHAR(HireDate, 'YYYY') = &GiveYear

GROUP BY TO_CHAR(HireDate, 'YYYY'), TO_CHAR(HireDate, 'Month'), Tot_Sal, Deptno

ORDER BY Deptno;

SELECT TO_CHAR(HireDate, 'YYYY') Year,

Deptno, SUM(Sal),

ROUND(SUM(Sal)/Tot_Sal * 100, 2) "Salary%"

FROM Emp, (SELECT SUM(Sal) Tot_Sal

FROM Emp)

GROUP BY TO_CHAR(HireDate, 'YYYY'), Deptno, Tot_Sal

ORDER BY Year;

SELECT E.EmpCount, D.DeptCount

FROM 

(SELECT COUNT(*) EmpCount 

 FROM Emp) E,

(SELECT COUNT(*) DeptCount 

 FROM Dept) D;

 SELECT A.Deptno "Department Number",

(A.NumEmp / B.TotalCount ) * 100  "%Employees",

(A.SalSum / B.TotalSal ) * 100 "%Salary"

FROM

(SELECT Deptno, COUNT(*) NumEmp,

 SUM(Sal) SalSum

 FROM Emp

 GROUP BY Deptno) A,

(SELECT COUNT(*) TotalCount,

 SUM(Sal) TotalSal

 FROM Emp) B;