Wednesday, October 23, 2013

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;

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect