# Thread: procedures and statistical functions

1. Registered User
Join Date
Apr 2012
Posts
19

## 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. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
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?

3. Registered User
Join Date
Apr 2012
Posts
19
Originally Posted by anacedent
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 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 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 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. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
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?

5. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
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")

6. Registered User
Join Date
Apr 2012
Posts
19
Originally Posted by anacedent
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. Registered User
Join Date
Apr 2012
Posts
19
Originally Posted by anacedent
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. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
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```

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

10. Registered User
Join Date
Apr 2012
Posts
19
Originally Posted by anacedent
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. Registered User
Join Date
Apr 2012
Posts
19
Originally Posted by anacedent
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. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
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>```

13. Registered User
Join Date
Apr 2012
Posts
19
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. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
>now I would like to :

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

Please proceed to do it all.

15. Registered User
Join Date
Apr 2012
Posts
19
Originally Posted by anacedent
>now I would like to :

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

Please proceed to do it all.
LOL