Results 1 to 15 of 15
  1. #1
    Join Date
    Apr 2012
    Posts
    19

    Unhappy Unanswered: procedures and statistical functions

    Hi,
    I want to Create a stored procedure which will print out the names, salaries and IDs of all employees who match the following:
    Their salary is within one standard deviation of two times the average salary of all employees : 2 * AVG - STDDEV <= EmpSalary <= 2 * Avg + STDDEV

    This is the code I have been using but it is not working :

    Declare fname VARCHAR2(50);
    lname VARCHAR2(50);
    sal Decimal(10);

    cursor e1 is Select firstname,lastname,salary from employee group by ID having 2*AVG(Salary)-STDDEV(Salary) <= salary <= 2*AVG(Salary)+STDDEV(Salary);
    Begin
    open e1;
    loop
    fetch e1 into fname,lname,sal;
    exit when e1%notfound;
    DBMS_OUTPUT.PUT_LINE('Employee'||' : '||fname||' '||lname||' '||' have a salary within one standard deviation of two times the average salary of all employees'||' '||sal);
    End loop;
    close e1;
    end;
    /

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    see this picture of my car.
    my car is not working.
    tell me how to make my car go.

    Realize that since we don't have your tables or data, we can not run, test or improve posted code.

    is COPY & PASTE broken for you?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2012
    Posts
    19
    Quote Originally Posted by anacedent View Post
    see this picture of my car.
    my car is not working.
    tell me how to make my car go.

    Realize that since we don't have your tables or data, we can not run, test or improve posted code.

    is COPY & PASTE broken for you?
    this is my table codes :

    --drop tables
    drop table Employee cascade constraints;
    drop table employeelog cascade constraints;
    drop table Customer cascade constraints;
    drop table Emp_Helps_Cust cascade constraints;

    --drop sequence
    drop sequence Employees_id_seq;
    drop sequence Customers_id_seq;

    --Table Employee
    CREATE TABLE Employee (
    ID INT NOT NULL ,
    FirstName VARCHAR2(50) NOT NULL ,
    LastName VARCHAR2(50) NOT NULL ,
    Salary DECIMAL(10) NOT NULL ,
    PRIMARY KEY (ID) );

    --Employee auto_increment
    create sequence Employees_id_seq start with 1 increment by 1;
    create or replace trigger Employees_insert
    before insert on Employee
    for each row
    begin
    select Employees_id_seq.nextval into :new.ID from dual;
    end;
    /

    --Table Employee Log
    CREATE TABLE EmployeeLog (
    LogID INT NOT NULL ,
    EmpID INT NOT NULL ,
    LogDate DATE NOT NULL ,
    PRIMARY KEY (LogID, EmpID) ,
    FOREIGN KEY (EmpID )
    REFERENCES Employee (ID));


    --Relative sequence Employeelog
    CREATE OR REPLACE TRIGGER relative
    BEFORE INSERT ON Employeelog
    FOR EACH ROW
    BEGIN
    SELECT NVL(MAX(LogID), 0)+1 INTO :new.LogID FROM employeelog;
    END;
    /

    --Table Customer
    CREATE TABLE Customer(
    CID INT NOT NULL ,
    FirstName VARCHAR2(50) NOT NULL ,
    LastName VARCHAR2(50) NOT NULL ,
    PRIMARY KEY (CID) );

    --Customer auto_increment
    create sequence Customers_id_seq start with 1 increment by 1;
    create or replace trigger Customers_insert
    before insert on Customer
    for each row
    begin
    select Customers_id_seq.nextval into :new.CID from dual;
    end;
    /

    --Table Emp_Helps_Cust
    CREATE TABLE Emp_Helps_Cust (
    CustID INT NOT NULL ,
    EmpID INT NOT NULL ,
    PRIMARY KEY (CustID, EmpID) ,
    FOREIGN KEY (CustID )
    REFERENCES Customer (CID),
    FOREIGN KEY (EmpID)
    REFERENCES Employee(ID));

    This is my data :

    INSERT INTO Employee (FirstName, LastName, Salary) VALUES ('Chris', 'Hayes', 20000);
    INSERT INTO Employee (FirstName, LastName, Salary) VALUES ('Luke', 'Parham', 20000);
    INSERT INTO Employee (FirstName, LastName, Salary) VALUES ('Joe', 'Guilliams', 60000);
    INSERT INTO Employee (FirstName, LastName, Salary) VALUES ('Jimmy', 'Smith', 150000);
    INSERT INTO Employee (FirstName, LastName, Salary) VALUES ('Sandy', 'Roberts', 125000);
    INSERT INTO Employee (FirstName, LastName, Salary) VALUES ('Robin', 'Scherwin', 35000);
    INSERT INTO Employee (FirstName, LastName, Salary) VALUES ('Ryan', 'Dickinson', 250000);
    INSERT INTO Employee (FirstName, LastName, Salary) VALUES ('Tyrone', 'Ward', 87500);

    INSERT INTO Customer (FirstName, LastName) VALUES ('Pherick', 'Ashton');
    INSERT INTO Customer (FirstName, LastName) VALUES ('Riccardo', 'Larus');
    INSERT INTO Customer (FirstName, LastName) VALUES ('Roy', 'Malakai');
    INSERT INTO Customer (FirstName, LastName) VALUES ('Fabijan', 'Desire');
    INSERT INTO Customer (FirstName, LastName) VALUES ('Severin', 'Ira');
    INSERT INTO Customer (FirstName, LastName) VALUES ('Ludoviko', 'Chin');
    INSERT INTO Customer (FirstName, LastName) VALUES ('Pipin', 'Shi');
    INSERT INTO Customer (FirstName, LastName) VALUES ('Topias', 'Hirshel');

    INSERT INTO Emp_Helps_Cust (EmpID, CustID) VALUES (1, 1);
    INSERT INTO Emp_Helps_Cust (EmpID, CustID) VALUES (1, 2);
    INSERT INTO Emp_Helps_Cust (EmpID, CustID) VALUES (1, 3);
    INSERT INTO Emp_Helps_Cust (EmpID, CustID) VALUES (1, 6);
    INSERT INTO Emp_Helps_Cust (EmpID, CustID) VALUES (1, 7);
    INSERT INTO Emp_Helps_Cust (EmpID, CustID) VALUES (2, 1);
    INSERT INTO Emp_Helps_Cust (EmpID, CustID) VALUES (2, 3);
    INSERT INTO Emp_Helps_Cust (EmpID, CustID) VALUES (3, 1);
    INSERT INTO Emp_Helps_Cust (EmpID, CustID) VALUES (3, 4);
    INSERT INTO Emp_Helps_Cust (EmpID, CustID) VALUES (4, 8);
    INSERT INTO Emp_Helps_Cust (EmpID, CustID) VALUES (5, 6);
    INSERT INTO Emp_Helps_Cust (EmpID, CustID) VALUES (7, 2);
    INSERT INTO Emp_Helps_Cust (EmpID, CustID) VALUES (7, 3);

    And this is the procedure that is not working :

    Declare fname VARCHAR2(50);
    lname VARCHAR2(50);
    sal Decimal(10);

    cursor e1 is Select firstname,lastname,Salary from employee group by ID having salary > 2*avg(Salary)-STDDEV(Salary) and salary < 2*avg(Salary)+STDDEV(Salary) ;
    Begin
    open e1;
    loop
    fetch e1 into fname,lname,sal;
    exit when e1%notfound;
    DBMS_OUTPUT.PUT_LINE('Employee'||' : '||fname||' '||lname||' '||' have a salary within one standard deviation of two times the average salary of all employees'||' '||sal);
    End loop;
    close e1;
    end;
    /

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    so much for actually testing code before posting.

    post results from following SQL
    Select firstname,lastname,Salary from employee group by ID having salary > 2*avg(Salary)-STDDEV(Salary) and salary < 2*avg(Salary)+STDDEV(Salary) ;

    What are expected/desired results from procedure & test data?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    18:19:35 SQL> INSERT INTO Employee (FirstName, LastName, Salary) VALUES ('Robin', 'Scherwin', 35000)
    *
    ERROR at line 1:
    ORA-01400: cannot insert NULL into ("USER2"."EMPLOYEE"."ID")


    18:19:35 SQL> INSERT INTO Employee (FirstName, LastName, Salary) VALUES ('Ryan', 'Dickinson', 250000)
    *
    ERROR at line 1:
    ORA-01400: cannot insert NULL into ("USER2"."EMPLOYEE"."ID")


    18:19:35 SQL> INSERT INTO Employee (FirstName, LastName, Salary) VALUES ('Tyrone', 'Ward', 87500)
    *
    ERROR at line 1:
    ORA-01400: cannot insert NULL into ("USER2"."EMPLOYEE"."ID")
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Apr 2012
    Posts
    19
    Quote Originally Posted by anacedent View Post
    18:19:35 SQL> INSERT INTO Employee (FirstName, LastName, Salary) VALUES ('Robin', 'Scherwin', 35000)
    *
    ERROR at line 1:
    ORA-01400: cannot insert NULL into ("USER2"."EMPLOYEE"."ID")


    18:19:35 SQL> INSERT INTO Employee (FirstName, LastName, Salary) VALUES ('Ryan', 'Dickinson', 250000)
    *
    ERROR at line 1:
    ORA-01400: cannot insert NULL into ("USER2"."EMPLOYEE"."ID")


    18:19:35 SQL> INSERT INTO Employee (FirstName, LastName, Salary) VALUES ('Tyrone', 'Ward', 87500)
    *
    ERROR at line 1:
    ORA-01400: cannot insert NULL into ("USER2"."EMPLOYEE"."ID")
    I don't get theses errors while inserting my data. It works for me with the codes I sent you.Because of referential integrity tables have to be populated in the right order ?

  7. #7
    Join Date
    Apr 2012
    Posts
    19
    Quote Originally Posted by anacedent View Post
    so much for actually testing code before posting.

    post results from following SQL
    Select firstname,lastname,Salary from employee group by ID having salary > 2*avg(Salary)-STDDEV(Salary) and salary < 2*avg(Salary)+STDDEV(Salary) ;

    What are expected/desired results from procedure & test data?
    I want to Create a stored procedure which will print out the names, salaries and IDs of all employees who match the following:
    Their salary is within one standard deviation of two times the average salary of all employees : 2 * AVG - STDDEV <= EmpSalary <= 2 * Avg + STDDEV

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    so which rows should be in the result set?

    Code:
      1  with grp as (
      2   select avg(salary) AVERAGE, stddev(salary) STD from employees)
      3  Select firstname,lastname,salary from employees  EE, GRP
      4* where  ee.salary between grp.average*2-grp.STD and grp.average*2+GRP.STD
    18:47:36 SQL> /
    
    no rows selected
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Apr 2012
    Posts
    19
    Thanks i just wanted a working function.

  10. #10
    Join Date
    Apr 2012
    Posts
    19

    Thumbs up

    Quote Originally Posted by anacedent View Post
    so which rows should be in the result set?

    Code:
      1  with grp as (
      2   select avg(salary) AVERAGE, stddev(salary) STD from employees)
      3  Select firstname,lastname,salary from employees  EE, GRP
      4* where  ee.salary between grp.average*2-grp.STD and grp.average*2+GRP.STD
    18:47:36 SQL> /
    
    no rows selected
    Thank you very much !
    it worked for me :

    with grp as (
    select avg(salary) AVERAGE, stddev(salary) STD from employee)
    Select firstname,lastname,salary from employee EE, GRP
    where ee.salary between grp.average*2-grp.STD and grp.average*2+GRP.STD;
    /

  11. #11
    Join Date
    Apr 2012
    Posts
    19
    Quote Originally Posted by anacedent View Post
    so which rows should be in the result set?

    Code:
      1  with grp as (
      2   select avg(salary) AVERAGE, stddev(salary) STD from employees)
      3  Select firstname,lastname,salary from employees  EE, GRP
      4* where  ee.salary between grp.average*2-grp.STD and grp.average*2+GRP.STD
    18:47:36 SQL> /
    
    no rows selected
    How can I put your code in this procedure :

    set serveroutput on

    Declare fname VARCHAR2(50);
    lname VARCHAR2(50);
    sal Decimal(10);

    cursor e1 is with grp as (
    select avg(salary) AVERAGE, stddev(salary) STD from employee)
    Select distinct firstname,lastname,salary from employee EE, GRP
    where ee.salary between grp.average*2-grp.STD and grp.average*2+GRP.STD;
    open e1;
    loop
    fetch e1 into fname,lname,sal;
    exit when e1%notfound;
    DBMS_OUTPUT.PUT_LINE('Employee'||' : '||fname||' '||lname||' '||' have a salary within one standard deviation of two times the average salary of all employees'||' '||sal);
    End loop;
    close e1;
    end;
    /

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    below worked for me.
    Code:
    rote file afiedt.buf
    
      1  Declare fname VARCHAR2(50);
      2  lname VARCHAR2(50);
      3  sal Decimal(10);
      4  cursor e1 is with grp as (
      5  select avg(salary) AVERAGE, stddev(salary) STD from employee)
      6  Select distinct firstname,lastname,salary from employee EE, GRP
      7  where ee.salary between grp.average*2-grp.STD and grp.average*2+GRP.STD;
      8  BEGIN
      9  open e1;
     10  loop
     11  fetch e1 into fname,lname,sal;
     12  exit when e1%notfound;
     13  DBMS_OUTPUT.PUT_LINE('Employee'||' : '||fname||' '||lname||' '||' have a salary within one standard deviation of two times the average salary of all employees'||' '||sal);
     14  End loop;
     15  close e1;
     16* end;
    07:39:24 SQL> /
    Employee : Sandy Roberts  have a salary within one standard deviation of two
    times the average salary of all employees 125000
    Employee : Jimmy Smith	have a salary within one standard deviation of two times
    the average salary of all employees 150000
    Employee : Ryan Dickinson  have a salary within one standard deviation of two
    times the average salary of all employees 250000
    
    PL/SQL procedure successfully completed.
    
    07:39:25 SQL>
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  13. #13
    Join Date
    Apr 2012
    Posts
    19

    Question

    Wow you really are an Oracle Guru !!!
    now I would like to :


    1-Create a stored procedure which takes as input the first and last name of an employee to look up and prints out only that employee’s information.
    2-Create a trigger which makes an entry with the current date and time to the EmployeeLog table each time an Employee is inserted.
    3-Create a trigger which, every time an Employee would be updated, the trigger takes the new value for the employee’s salary and reduces it by 5% - but only if the employee’s salary amount would be less than 50000 (before adjustment).

  14. #14
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >now I would like to :

    Nobody here objects or can prevent you from implementing the requirements.

    Please proceed to do it all.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  15. #15
    Join Date
    Apr 2012
    Posts
    19

    Thumbs up

    Quote Originally Posted by anacedent View Post
    >now I would like to :

    Nobody here objects or can prevent you from implementing the requirements.

    Please proceed to do it all.
    LOL

    Thanks for your help !!!
    have a good one.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •