Blog - Our findings and learning
Procedure to display the departments, which are having total salary greater than?
Procedure without Parameters: CREATE OR REPLACE PROCEDURE dept_sal_pISCURSOR C1 ISselect d.department_name,e.tot_sal from(SELECT department_id,sum(salary) tot_salFROM employeesGROUP BY department_idHAVING SUM(salary) > 50000) e,departments dwhere e.department_id=d.department_id;v_id departments.department_name%TYPE;v_sal employees.salary%type;BEGINOPEN C1;DBMS_OUTPUT.PUT_LINE(‘Departments having salary greater than
Write a SQL query to display alternate records from a table?
For even rows: SELECT *FROM (SELECT e.*,MOD(ROWNUM,2) rn FROM employees E)WHERE rn =0; For odd rows: SELECT *FROM (SELECT e.*,MOD(ROWNUM,2) rn FROM employees E)WHERE rn =1;
Write a query to display manager and employees under them?
select manager,count(employee), rtrim(xmlagg(xmlelement(e,employee||’,’)).extract(‘//text()’),’,’) as “Employees under the Manager” from (select e2.first_name manager,e1.first_name employee from employee e1,employee e2 where e1.reporting_to=e2.employeeid) group by manager;