Saturday, 31 March 2012
CURSOR (SIMPLE LOOP)
============================================================
PL/SQL TABLE
============================================================
DECLARE
V_Empno Emp.Empno%TYPE := &EmpID;
Employee Emp%ROWTYPE;
BEGIN
IF V_Empno IS NOT NULL THEN
SELECT *
INTO Employee
FROM Emp
WHERE Empno=V_Empno;
IF Employee.Empno IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('Details of the employee that yoy want to delete ');
DBMS_OUTPUT.PUT_LINE('Name : ' || Employee.EName);
DBMS_OUTPUT.PUT_LINE('Desgnation : ' || Employee.Job);
DBMS_OUTPUT.PUT_LINE(' Deptno : ' || Employee.Deptno);
DBMS_OUTPUT.PUT_LINE(' Sal : ' || Employee.Sal);
DBMS_OUTPUT.PUT_LINE(' Comm : ' || NVL(TO_CHAR(Employee.Comm),'Can''t Applied'));
DBMS_OUTPUT.PUT_LINE(' MgeNo. : ' || NVL(TO_CHAR(Employee.Mgr),'Can''t Controled'));
DBMS_OUTPUT.PUT_LINE(' HireDate : ' || Employee.HireDate);
INSERT INTO EmpAudit
VALUES (Employee.Empno,Employee.EName,Employee.Job, Employee.Mgr,Employee.HireDate,
Employee.Sal,Employee.Comm, Employee.Deptno,USER,USERENV('TERMINAL'),SYSDATE );
DBMS_OUTPUT.PUT_LINE('Data Deletion Start ');
DELETE FROM Emp
WHERE Empno=V_Empno;
DBMS_OUTPUT.PUT_LINE('Employee recode is deleted successfully.'|| CHR(10)||' Thank you for using our product.. ');
ELSE
DBMS_OUTPUT.PUT_LINE('Recode is not found or some problm in deletion. Sorry Try Again ..'|| CHR(10)||' Thank you for using our product.. ');
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('Fatal Error ! NULL Encouted can''t proceed further. '|| CHR(10) ||' Thank you for using our product.. ');
END IF;
END;
DECLARE
V_Empno Emp.Empno%TYPE := &EmpID;
Employee Emp%ROWTYPE;
BEGIN
SELECT *
INTO Employee
FROM Emp
WHERE Empno=V_Empno;
IF V_Empno IS NOT NULL THEN
IF Employee.Empno IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('Details of the employee that yoy want to delete ');
DBMS_OUTPUT.PUT_LINE('Name : ' || Employee.EName);
DBMS_OUTPUT.PUT_LINE('Desgnation : ' || Employee.Job);
DBMS_OUTPUT.PUT_LINE(' Deptno : ' || Employee.Deptno);
DBMS_OUTPUT.PUT_LINE(' Sal : ' || Employee.Sal);
DBMS_OUTPUT.PUT_LINE(' Comm : ' || NVL(TO_CHAR(Employee.Comm),'Can''t Applied'));
DBMS_OUTPUT.PUT_LINE(' MgeNo. : ' || NVL(TO_CHAR(Employee.Mgr),'Can''t Controled'));
DBMS_OUTPUT.PUT_LINE(' HireDate : ' || Employee.HireDate);
DBMS_OUTPUT.PUT_LINE('Data Deletion Start ');
DELETE FROM Emp
WHERE Empno=V_Empno;
DBMS_OUTPUT.PUT_LINE('Employee recode is deleted successfully.'|| CHR(10)||' Thank you for using our product.. ');
ELSE
DBMS_OUTPUT.PUT_LINE('Recode is not found or some problm in deletion. Sorry Try Again ..'|| CHR(10)||' Thank you for using our product.. ');
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('Fatal Error ! NULL Encouted can''t proceed further. '|| CHR(10)||' Thank you for using our product.. ');
END IF;
END;
DECLARE
V_EmpRCount NUMBER(2);
MyIndex NUMBER(2) :=1 ;
V_RowID NUMBER(2) := 65 ;
TYPE Emp_Table_Type IS TABLE OF Emp%ROWTYPE
INDEX BY BINARY_INTEGER;
Employees Emp_Table_Type;
BEGIN
SELECT COUNT(*)
INTO V_EmpRCount
FROM Emp;
LOOP
EXIT WHEN MyIndex > V_EmpRCount ;
SELECT *
INTO Employees(MyIndex)
FROM Emp
WHERE SUBSTR(ROWID,18,1) =CHR(V_RowID);
V_RowID := V_RowID+1;
MyIndex := MyIndex + 1;
END LOOP;
MyIndex :=1;
DBMS_OUTPUT.PUT_LINE('=================Employee informations are shown below ===================');
DBMS_OUTPUT.PUT_LINE(CHR(9) || CHR(9) ||' ------------------------------------');
DBMS_OUTPUT.PUT_LINE('EmpNo EName Job Deptno HireDate MgrNo Salary Comm ');
DBMS_OUTPUT.PUT_LINE('================================================================= =========');
LOOP
EXIT WHEN NOT Employees.EXISTS(MyIndex);
DBMS_OUTPUT.PUT_LINE( LPAD(Employees(MyIndex).Empno,6) || CHR(9) ||
RPAD(Employees(MyIndex).EName,8)||' '||
RPAD(Employees(MyIndex).Job,9) || ' ' ||
LPAD(Employees(MyIndex).Deptno,3)|| ' ' ||
RPAD(Employees(MyIndex).HireDate,10) || ' ' ||
LPAD(NVL(TO_CHAR(Employees(MyIndex).Mgr),'NC'),6) || ' ' ||
LPAD(Employees(MyIndex).Sal,6)|| ' ' || LPAD(NVL(TO_CHAR(Employees(MyIndex).Comm),'NA'),6));
MyIndex := MyIndex + 1;
END LOOP;
END;
DECLARE
V_Index NUMBER(2) :=1;
TYPE Dept_TableType IS TABLE OF Dept%ROWTYPE
INDEX BY BINARY_INTEGER;
CURSOR DeptCursor
IS
SELECT * FROM Dept;
DeptTable Dept_TableType;
BEGIN
OPEN DeptCursor;
LOOP
FETCH DeptCursor INTO DeptTable(V_Index);
V_Index :=V_Index + 1;
EXIT WHEN DeptCursor%NOTFOUND;
END LOOP;
CLOSE DeptCursor;
END;
DECLARE
V_Index NUMBER(2) :=1;
TYPE Dept_TableType IS TABLE OF Dept%ROWTYPE
INDEX BY BINARY_INTEGER;
CURSOR DeptCursor
IS
SELECT * FROM Dept;
DeptTable Dept_TableType;
BEGIN
OPEN DeptCursor;
LOOP
FETCH DeptCursor INTO DeptTable(V_Index);
EXIT WHEN DeptCursor%NOTFOUND;
V_Index :=V_Index + 1;
END LOOP;
CLOSE DeptCursor;
FOR MyIndex IN 1..V_Index-1
LOOP
DBMS_OUTPUT.PUT_LINE(DeptTable(MyIndex).DName);
END LOOP;
END;
===============================================================
CURSOR (Simple LOOP)
===============================================================
DECLARE
V_EmpRCount NUMBER(2);
MyIndex NUMBER(2) :=1 ;
TYPE Emp_Table_Type IS TABLE OF Emp%ROWTYPE
INDEX BY BINARY_INTEGER;
Employees Emp_Table_Type;
CURSOR EmpCursor IS
SELECT * FROM Emp;
BEGIN
OPEN EmpCursor;
LOOP
EXIT WHEN EmpCursor%NOTFOUND ;
FETCH EmpCursor INTO Employees(MyIndex);
MyIndex := MyIndex + 1;
END LOOP;
CLOSE EmpCursor;
MyIndex :=1;
DBMS_OUTPUT.PUT_LINE('====================Employee informations are shown below ======================');
DBMS_OUTPUT.PUT_LINE(CHR(9) || CHR(9) ||RPAD('-',45,'-'));
DBMS_OUTPUT.PUT_LINE('EmpNo EName Job Deptno HireDate MgrNo Salary Comm ');
DBMS_OUTPUT.PUT_LINE(RPAD('=',80,'='));
LOOP
EXIT WHEN NOT Employees.EXISTS(MyIndex);
DBMS_OUTPUT.PUT_LINE( LPAD(Employees(MyIndex).Empno,6) || CHR(9) ||
RPAD(Employees(MyIndex).EName,8)||' '||
RPAD(Employees(MyIndex).Job,9) || ' ' ||
LPAD(Employees(MyIndex).Deptno,3)|| ' ' ||
RPAD(Employees(MyIndex).HireDate,10) || ' ' ||
LPAD(NVL(TO_CHAR(Employees(MyIndex).Mgr),'NC'),6) || ' ' ||
LPAD(Employees(MyIndex).Sal,6)|| ' ' || LPAD(NVL(TO_CHAR(Employees(MyIndex).Comm),'NA'),6));
MyIndex := MyIndex + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(CHR(10) || Employees.COUNT || ' recode is displayd on Screen....');
END;
================================================================= Employee salary summery using procedure
=================================================================
DECLARE
V_EmpRCount NUMBER(2);
V_TotalSal NUMBER := 0 ;
V_Dept10Sal NUMBER := 0 ;
V_Dept20Sal NUMBER := 0 ;
V_Dept30Sal NUMBER := 0 ;
MyIndex NUMBER(2) :=1 ;
TYPE Emp_Table_Type IS TABLE OF Emp%ROWTYPE
INDEX BY BINARY_INTEGER;
Employees Emp_Table_Type;
CURSOR EmpCursor IS
SELECT * FROM Emp;
BEGIN
OPEN EmpCursor;
LOOP
EXIT WHEN EmpCursor%NOTFOUND ;
FETCH EmpCursor INTO Employees(MyIndex);
MyIndex := MyIndex + 1;
END LOOP;
CLOSE EmpCursor;
MyIndex :=1;
DBMS_OUTPUT.PUT_LINE(RPAD('==================Employee informations are shown below ',80,'='));
DBMS_OUTPUT.PUT_LINE(CHR(9) || CHR(9) ||RPAD('-',45,'-'));
DBMS_OUTPUT.PUT_LINE(' EName Job Deptno Salary ');
DBMS_OUTPUT.PUT_LINE(RPAD('=',40,'='));
LOOP
EXIT WHEN NOT Employees.EXISTS(MyIndex);
DBMS_OUTPUT.PUT_LINE( RPAD(Employees(MyIndex).EName,8)||' '||
RPAD(Employees(MyIndex).Job,9) || ' ' ||
LPAD(Employees(MyIndex).Deptno,3)|| ' ' ||
LPAD(Employees(MyIndex).Sal,6));
V_TotalSal :=V_TotalSal + Employees(MyIndex).Sal;
IF Employees(MyIndex).Deptno =10 THEN
V_Dept10Sal :=V_Dept10Sal + Employees(MyIndex).Sal;
ELSIF Employees(MyIndex).Deptno =20 THEN
V_Dept20Sal :=V_Dept20Sal + Employees(MyIndex).Sal;
ELSIF Employees(MyIndex).Deptno =30 THEN
V_Dept30Sal :=V_Dept30Sal + Employees(MyIndex).Sal;
END IF;
MyIndex := MyIndex + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(CHR(10) || Employees.COUNT || ' recode is displayd on Screen....');
DBMS_OUTPUT.PUT_LINE(CHR(10) || 'Employee salary Summary ' || CHR(10) );
DBMS_OUTPUT.PUT_LINE('Total Investment of the company (Monthly) for Department 10 is ' || TO_CHAR(V_Dept10Sal,'99G999D99'));
DBMS_OUTPUT.PUT_LINE('Total Investment of the company (Yearly) for Department 10 is ' || TO_CHAR(V_Dept10Sal*12,'9G99G999D99'));
DBMS_OUTPUT.PUT_LINE('Total Investment of the company (Monthly) for Department 20 is ' || TO_CHAR(V_Dept20Sal,'99G999D99'));
DBMS_OUTPUT.PUT_LINE('Total Investment of the company (Yearly) for Department 20 is ' || TO_CHAR(V_Dept20Sal*12,'9G99G999D99'));
DBMS_OUTPUT.PUT_LINE('Total Investment of the company (Monthly) for Department 30 is ' || TO_CHAR(V_Dept30Sal,'99G999D99'));
DBMS_OUTPUT.PUT_LINE('Total Investment of the company (Yearly) for Department 30 is ' || TO_CHAR(V_Dept30Sal*12,'9G99G999D99'));
DBMS_OUTPUT.PUT_LINE('Total Investment of the company (Monthly) ' || TO_CHAR(V_TotalSal,'99G999D99'));
DBMS_OUTPUT.PUT_LINE('Total Investment of the company (Yearly) ' || TO_CHAR(V_TotalSal*12,'9G99G999D99'));
END;
=================================================================
Department wise employee data
=================================================================DECLARE
V_EmpRCount NUMBER(2);
V_Deptno NUMBER(2) := &GDeptno;
MyCounter NUMBER(2) :=0;
MyIndex NUMBER(2) :=1 ;
TYPE Emp_Table_Type IS TABLE OF Emp%ROWTYPE
INDEX BY BINARY_INTEGER;
Employees Emp_Table_Type;
CURSOR EmpCursor IS
SELECT * FROM Emp;
BEGIN
OPEN EmpCursor;
LOOP
EXIT WHEN EmpCursor%NOTFOUND ;
FETCH EmpCursor INTO Employees(MyIndex);
MyIndex := MyIndex + 1;
END LOOP;
CLOSE EmpCursor;
MyIndex :=1;
DBMS_OUTPUT.PUT_LINE('====================Employee informations are shown below ======================');
DBMS_OUTPUT.PUT_LINE(CHR(9) || CHR(9) ||RPAD('-',45,'-'));
DBMS_OUTPUT.PUT_LINE('EmpNo EName Job Deptno HireDate MgrNo Salary Comm ');
DBMS_OUTPUT.PUT_LINE(RPAD('=',80,'='));
LOOP
EXIT WHEN NOT Employees.EXISTS(MyIndex);
IF Employees(MyIndex).Deptno=V_Deptno THEN
DBMS_OUTPUT.PUT_LINE( LPAD(Employees(MyIndex).Empno,6) || CHR(9) ||
RPAD(Employees(MyIndex).EName,8)||' '||
RPAD(Employees(MyIndex).Job,9) || ' ' ||
LPAD(Employees(MyIndex).Deptno,3)|| ' ' ||
RPAD(Employees(MyIndex).HireDate,10) || ' ' ||
LPAD(NVL(TO_CHAR(Employees(MyIndex).Mgr),'NC'),6) || ' ' ||
LPAD(Employees(MyIndex).Sal,6)|| ' ' || LPAD(NVL(TO_CHAR(Employees(MyIndex).Comm),'NA'),6));
MyCounter :=MyCounter +1;
END IF;
MyIndex := MyIndex + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(CHR(10) || MyCounter || ' recode is displayd on Screen....');
END;
=================================================================
Job wise employee data
================================================================= DECLARE
V_EmpRCount NUMBER(2);
V_Job Emp.Job%TYPE := UPPER('&GJob');MyCounter NUMBER(2) :=0;
MyIndex NUMBER(2) :=1 ;
TYPE Emp_Table_Type IS TABLE OF Emp%ROWTYPE
INDEX BY BINARY_INTEGER;
Employees Emp_Table_Type;
CURSOR EmpCursor IS
SELECT * FROM Emp;
BEGIN
OPEN EmpCursor;
LOOP
EXIT WHEN EmpCursor%NOTFOUND ;
FETCH EmpCursor INTO Employees(MyIndex);
MyIndex := MyIndex + 1;
END LOOP;
CLOSE EmpCursor;
MyIndex :=1;
DBMS_OUTPUT.PUT_LINE('====================Employee informations are shown below ======================');
DBMS_OUTPUT.PUT_LINE(CHR(9) || CHR(9) ||RPAD('-',45,'-'));
DBMS_OUTPUT.PUT_LINE('EmpNo EName Job Deptno HireDate MgrNo Salary Comm ');
DBMS_OUTPUT.PUT_LINE(RPAD('=',80,'='));
LOOP
EXIT WHEN NOT Employees.EXISTS(MyIndex);
IF Employees(MyIndex).Job=V_Job THEN
DBMS_OUTPUT.PUT_LINE( LPAD(Employees(MyIndex).Empno,6) || CHR(9) ||
RPAD(Employees(MyIndex).EName,8)||' '||
RPAD(Employees(MyIndex).Job,9) || ' ' ||
LPAD(Employees(MyIndex).Deptno,3)|| ' ' ||
RPAD(Employees(MyIndex).HireDate,10) || ' ' ||
LPAD(NVL(TO_CHAR(Employees(MyIndex).Mgr),'NC'),6) || ' ' ||
LPAD(Employees(MyIndex).Sal,6)|| ' ' || LPAD(NVL(TO_CHAR(Employees(MyIndex).Comm),'NA'),6));
MyCounter :=MyCounter +1;
END IF;
MyIndex := MyIndex + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(CHR(10) || MyCounter || ' recode is displayd on Screen....');
END;
=================================================================
Top N Employee Details using procedure
=================================================================DECLARE
V_EmpRCount NUMBER(2);
V_Top NUMBER(2) := >opEmp;
PrvTopSal Emp.Sal%TYPE :=0;
MyIndex NUMBER(2) :=1 ;
TYPE Emp_Table_Type IS TABLE OF Emp%ROWTYPE
INDEX BY BINARY_INTEGER;
Employees Emp_Table_Type;
CURSOR EmpCursor IS
SELECT * FROM Emp
ORDER BY Sal DESC;
BEGIN
OPEN EmpCursor;
LOOP
EXIT WHEN EmpCursor%NOTFOUND ;
FETCH EmpCursor INTO Employees(MyIndex);
MyIndex := MyIndex + 1;
END LOOP;
CLOSE EmpCursor;
MyIndex :=1;
DBMS_OUTPUT.PUT_LINE(RPAD('================Top ' || V_Top ||' Employee informations are shown below ',80,'='));
DBMS_OUTPUT.PUT_LINE(CHR(9) || CHR(9) ||RPAD('-',45,'-'));
DBMS_OUTPUT.PUT_LINE('EmpNo EName Job Deptno HireDate MgrNo Salary Comm ');
DBMS_OUTPUT.PUT_LINE(RPAD('=',80,'='));
LOOP
EXIT WHEN NOT Employees.EXISTS(MyIndex) OR MyIndex > V_Top;
DBMS_OUTPUT.PUT_LINE( LPAD(Employees(MyIndex).Empno,6) || CHR(9) ||
RPAD(Employees(MyIndex).EName,8)||' '||
RPAD(Employees(MyIndex).Job,9) || ' ' ||
LPAD(Employees(MyIndex).Deptno,3)|| ' ' ||
RPAD(Employees(MyIndex).HireDate,10) || ' ' ||
LPAD(NVL(TO_CHAR(Employees(MyIndex).Mgr),'NC'),6) || ' ' ||
LPAD(Employees(MyIndex).Sal,6)|| ' ' || LPAD(NVL(TO_CHAR(Employees(MyIndex).Comm),'NA'),6));
IF PrvTopSal = Employees(MyIndex).Sal THEN
V_Top := V_Top+1 ;
PrvTopSal := Employees(MyIndex).Sal;
ELSE
PrvTopSal := Employees(MyIndex).Sal;
END IF;
MyIndex := MyIndex + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(CHR(10) || (MyIndex - 1 ) || ' recode is displayd on Screen....');
END;
=================================================================
Top N Employee Details using OLAP
=================================================================
DECLARE
V_EmpRCount NUMBER(2);
V_Top NUMBER(2) := >opEmp;
MyIndex NUMBER(2) :=1 ;
CURSOR EmpCursor IS
SELECT Empno,EName,Job,Deptno,Sal
,DENSE_RANK()
OVER(ORDER BY Sal DESC) SalRank
FROM Emp;
TYPE Emp_Table_Type IS TABLE OF EmpCursor%ROWTYPE
INDEX BY BINARY_INTEGER;
Employees Emp_Table_Type;
BEGIN
OPEN EmpCursor;
LOOP
EXIT WHEN EmpCursor%NOTFOUND ;
FETCH EmpCursor INTO Employees(MyIndex);
MyIndex := MyIndex + 1;
END LOOP;
CLOSE EmpCursor;
MyIndex :=1;
DBMS_OUTPUT.PUT_LINE(RPAD('================Top ' || V_Top ||' Employee informations are shown below ',80,'='));
DBMS_OUTPUT.PUT_LINE(CHR(9) || CHR(9) ||RPAD('-',45,'-'));
DBMS_OUTPUT.PUT_LINE('EmpNo EName Job Deptno Salary ');
DBMS_OUTPUT.PUT_LINE(RPAD('=',50,'='));
LOOP
EXIT WHEN NOT Employees.EXISTS(MyIndex) OR Employees(MyIndex).SalRank > V_Top;
DBMS_OUTPUT.PUT_LINE( LPAD(Employees(MyIndex).Empno,6) || CHR(9) ||
RPAD(Employees(MyIndex).EName,8)||' '||
RPAD(Employees(MyIndex).Job,9) || ' ' ||
LPAD(Employees(MyIndex).Deptno,3)|| ' ' ||
LPAD(Employees(MyIndex).Sal,6));
MyIndex := MyIndex + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(CHR(10) || (MyIndex-1) || ' recode is displayd on Screen....');
END;
=================================================================
Employe manager info wuth out contacting database
=================================================================
DECLARE
V_EmpRCount NUMBER(2);
MyIndex NUMBER(2) :=1 ;
InnerIndex NUMBER(2) :=1;
CURSOR EmpCursor IS
SELECT *
FROM Emp;
TYPE Emp_Table_Type IS TABLE OF EmpCursor%ROWTYPE
INDEX BY BINARY_INTEGER;
Employees Emp_Table_Type;
V_MgrName Emp.Ename%TYPE;
BEGIN
OPEN EmpCursor;
LOOP
EXIT WHEN EmpCursor%NOTFOUND ;
FETCH EmpCursor INTO Employees(MyIndex);
MyIndex := MyIndex + 1;
END LOOP;
CLOSE EmpCursor;
MyIndex :=1;
DBMS_OUTPUT.PUT_LINE(RPAD('================Employee informations are shown below ',80,'='));
DBMS_OUTPUT.PUT_LINE(CHR(9) || CHR(9) ||RPAD('-',45,'-'));
DBMS_OUTPUT.PUT_LINE('EmpNo EName Job Deptno Salary MgrName');
DBMS_OUTPUT.PUT_LINE(RPAD('=',60,'='));
LOOP
EXIT WHEN NOT Employees.EXISTS(MyIndex) ;
IF Employees(MyIndex).Mgr IS NOT NULL THEN
LOOP EXIT WHEN NOT Employees.EXISTS(InnerIndex)
OR Employees(InnerIndex).Empno = Employees(MyIndex).Mgr ;
InnerIndex := InnerIndex +1;
END LOOP;
V_MgrName :=Employees(InnerIndex).EName;
ELSE
V_MgrName :='NC';
END IF;
InnerIndex :=1;
DBMS_OUTPUT.PUT_LINE( LPAD(Employees(MyIndex).Empno,6) || CHR(9) ||
RPAD(Employees(MyIndex).EName,8)||' '||
RPAD(Employees(MyIndex).Job,9) || ' ' ||
LPAD(Employees(MyIndex).Deptno,3)|| ' ' ||
LPAD(Employees(MyIndex).Sal,6) || ' ' ||V_MgrName );
MyIndex := MyIndex + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(CHR(10) || (MyIndex-1) || ' recode is displayd on Screen....');
END;
Important Query for Beginners PART (3)
201.Department where no employee is working ?
SELECT D.Deptno || ' has no employee' DeptNo
FROM Emp E,Dept D
WHERE E.Deptno(+)=D.Deptno
AND
E.Deptno IS NULL
202.Dislpay Department with highest annual remuneration bill as compensation ?
SELECT Deptno,SalSum
FROM ( SELECT DeptNo,SUM(Comm) SalSum
FROM Emp
GROUP BY Deptno
ORDER BY SalSum )
WHERE ROWNUM<=1;
203.Department wise average salary ?
SELECT DeptNo,TRUNC(AVG(Sal)) DeptAvgSalary
FROM Emp
GROUP BY Deptno
ORDER BY 1
204.Employees salary more than lowest salary of dept 10 ?
SELECT EName,Deptno,Sal Salary
FROM Emp
WHERE Sal > (SELECT MIN(Sal) Salary
FROM Emp
WHERE Deptno=10)
205.Deptname,Deptno,Sum of salary ?
SELECT D.Deptno,DName,DeptSalSum
FROM Dept D,(SELECT Deptno,SUM(Sal) DeptSalSum
FROM Emp
GROUP BY Deptno) E
WHERE D.Deptno=E.Deptno
ORDER BY 1
206.Depatrment having more than 3 employee ?
SELECT Deptno,COUNT(*) No_Of_Employee
FROM Emp
GROUP BY Deptno
HAVING COUNT(*) >3
207.List Lowest paid employee of a group excluding any group where minimum salary less than 1000 sort in order of salary ?
SELECT EName,Deptno,Sal
FROM (SELECT EName,Deptno
,ROW_NUMBER()
OVER(PARTITION BY Deptno
ORDER BY Sal) DeptPos
,Sal
FROM Emp )
WHERE DeptPos=1
AND
Sal > 1000
208.Delete the recode where department has no worker ?
DELETE FROM Dept
WHERE Deptno= (SELECT D.Deptno
FROM Emp E,Dept D
WHERE E.Deptno(+)=D.Deptno
AND
E.Deptno IS NULL)
209.Employes going to retired those have experience more than 30 year ?
SELECT Ename
FROM Emp
WHERE TRUNC(MONTHS_BETWEEN(SYSDATE,HireDate)/12) >30
210.Emlpoyees those salary is odd ?
SELECT * FROM Emp
WHERE MOD(Sal,2) <> 0;
211.Emploees salary contain at least 3 digit ?
SELECT * FROM Emp
WHERE LENGTH(Sal) > 3;
212.Employees joined on month of December ?
SELECT * FROM Emp
WHERE TO_CHAR(HireDate,’MON’)=’DEC’;
213.Employees name contain ‘a’ ?
SELECT * FROM Emp
WHERE UPPER(EName) LIKE(‘%A%’);
214.Employees deptno available in salary ?
SELECT * FROM Emp
WHERE Sal LIKE(‘%’||Deptno||’%’);
215.Employees 10% salary equals to year of join ?
SELECT * FROM Emp
WHERE TO_CHAR(Hiredate,’YY’)=Sal*.1;
216.Employees joined before 15th of the month ?
SELECT * FROM Emp
WHERE TO_CHAR(Hiredate,’DD’) > 15
217.Employees joined 10 year back?
SELECT * FROM Emp
WHERE TRUNC(MONTHS_BETWEEN(SYSDATE,HireDate)/12) > 10
218.Employes working as manager?
SELECT EName FROM Emp E
WHERE EXISTS (SELECT EName FROM Emp M
WHERE E.Empno=M.mgr)
219.Display unique job in emp ?
SELECT DISTINCT Deptno FROM Emp;
220.Employees name concatenated with job separated by a comma and a space and employees as title ?
SELECT Ename||’, ‘||job Employees FROM Emp;
221.Display all data of table separatd by a comma and title as output ?
SELECT Empno ||’,’|| EName ||’,’|| Job ||’,’|| Deptno ||’,’|| Mgr||’,’|| HireDate ||’,’|| Sal||’,’|| Comm OutPut
FROM Emp;
222.Display as the format Employee <name>works in department <number> and appointed on <date> ?
SELECT ‘Employee ‘|| Ename || ‘ works in department ’ || Deptno || ‘ and appointed on ’ || Hiredate EmpInfo
FROM Emp;
Subscribe to:
Posts (Atom)