SQL & PL/SQL :: How To Get 2nd Highest Salary From Employee Table
Apr 29, 2011i was given a task to find the second highest employee sal from emp table.
View 5 Replies
ADVERTISEMENT
SQL & PL/SQL :: Employee Table To Assign Grade For Each Employee Based On His Salary
Feb 9, 2012based on the following information
grade lowsal highsal
------ ----- ------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
for the employee table to assign grade for each employee based on his salary the following plsql procedure is giving error:
-----------------------------------------------------------
CREATE OR REPLACE PROCEDURE GRADE(EID IN NUMBER,BONUS OUT NUMBER) IS
vGRADE NUMBER(8,2);
vSAL NUMBER(8,2);
BEGIN
vGRADE=1
SELECT SAL INTO vSAL FROM EMP WHERE EMPNO=EMPID;
IF vSAL<= 700 THEN
vGRADE:=1;
ELSEIF vSAL<= 1201 THEN
[code]....
SQL & PL/SQL :: Print Ename And Salary Of Employee Using Employee Number As Input
Aug 11, 2013/* Formatted on 2013/08/11 18:46 (Formatter Plus v4.8.8) */
CREATE PROCEDURE p_get_name (
p_empno IN OUT NUMBER,
p_name OUT VARCHAR2,
p_err OUT NUMBER
[code].......
Note:- I want to print ename and salary of emp using empno as a input but i dont want to declare extra variable for salary , i want to print salary using empno but when i execute this procedure. It gives value of empno in salary. Don't Know Why , how can i print salary of emp using empno as input without declaring extra variable for salary.
Find Nth Highest Salary?
Jul 18, 2011How does the following underlined query works to find the nth highest salary
SELECT DISTINCT(A.SALARY) FROM EMPLOYEE A
WHERE &SALARY = (SELECT COUNT(DISTINCT(B.SALARY)) FROM EMPLOYEE B
WHERE A.SALARY<=B.SALARY);
SQL & PL/SQL :: How To Find The Nth Highest Salary
Jun 30, 2010how to find the nth highest salary
View 11 Replies View RelatedQueries Will Fetch Second Highest Sal From Emp Table
Jan 19, 2012go through both the given queries...
SELECT * FROM EMP A WHERE 1=(SELECT COUNT(DISTINCT(SAL)) FROM EMP B WHERE B.SAL>A.SAL);
SELECT * FROM EMP WHERE SAL=(SELECT MIN(SAL) FROM (SELECT DISTINCT(SAL) FROM EMP ORDER BY SAL DESC) WHERE ROWNUM<=2);
both queries will fetch second highest sal from emp table.which sorting is used by oracle in order by and group by clause.
Forms :: How To Validate Salary Regarding To Its MIN And MAX Value From Another Table
Jun 16, 2012I have a created data block using employees table so am trying to validate item salary using max_salary and min_salary from table jobs how to do this kind of validation.
[How can i validate input number into a Field of type char in oracle form?]
SQL & PL/SQL :: Update Salary In Employees Table With Analytical Functions?
Feb 16, 2013i have employee table i want to update salary with all employee 5 percent
View 4 Replies View RelatedSQL & PL/SQL :: Update Of Employee Table
May 28, 2011I have to implement a functionality in my application. I have an employee table and each employee does a transaction which is stored in a transaction table.
The functionality that I have to implement is that if an employee does not perform any transaction for a period of 2 years then updated the employee and set him inactive.
SQL & PL/SQL :: Update In Employee Table?
Apr 4, 2010Employee Table
==============
create table empoyee (
empno number,
sal number)
insert into employee(empno,sal) values (1, 200);
[code]...
If we make any update in Employee table for his/her salary, before update, that record should be inserted into EmployeeHist table and history will continue to build. Employee Table should have only current salary.If we change sal for emplyee # 1 from 200 to 800 then original current record in employee table will be inserted into employeehist table like
empsno = 1
empno =1
sal=200
last_update=sysdate
Business Rules For Employee Table
Aug 19, 2013We need some transformation rules on Source employee table which comes from SAP and want to load in Target table.Basic idea is we need to calculate time when position was not held by sub owner so . . . Etc
Source looks like:
POSITION_TCD START_DT END_DT SUBTYPE POSI_HOLDER
Example 1:
10005822 10/16/2003 11/20/2008 A008 105404
10005822 10/16/2003 3/31/2009 A999 105404
10005822 6/23/2008 7/5/2009 A008 124530
10005822 11/21/2008 8/31/2009 A008 105404
10005822 8/31/2009 4/16/2010 A008 105323
10005822 4/19/2010 12/31/9999 A999 131995
10005822 7/5/2010 12/31/9999 A008 131995
Example 2:
10084408 5/3/2010 12/31/9999 A008 130591
10084408 3/21/2011 5/17/2011 A008 132725
10084408 5/3/2010 1/2/2011 A999 130591
After business rules data should look like:
Target
Example 1:
POSITION_TCD POSI_HOLDER SUBS_OWNER EMP_ASSI_TYPE START_DT END_DT
10005822 105404 105404 Hold=Subs 10/16/2003 11/20/2008
10005822 124530 105404 Hold<>Subs 6/23/2008 3/31/2009
10005822 124530 No Substantive No Substan 4/1/2009 7/5/2009
10005822 105404 No Substantive No Substan 11/21/2008 8/31/2009
10005822 105323 No Substantive No Substan 8/31/2009 4/16/2010
10005822 No Holder 131995 No Holder 4/19/2010 7/4/2010
10005822 131995 131995 Hold=Subs 7/5/2010 12/31/9999
Example 2:
10084408 130591 130591 Hold=Subs 5/3/2010 1/2/2011
10084408 130591 No Substantive No Substan 1/3/2011 12/31/9999
10084408 132725 No Substantive No Substan 3/21/2011 5/17/2011
PL/SQL :: Fetch Records From Employee Table
Oct 28, 2012I am having one table Employee. Employee table having 50 records. I want to fetch 5 records every timeone the query is executed. But it should be like below.
1-5 records
6-10 records
11-15 records
16-20 records
46-50 records
Any one can give the query.
PL/SQL :: Employee Table - Find Characters
Sep 24, 2013In the Employee table i want to display having 4 characters of Emplo_name ,that name's 3rd character must be 'R' How to get it .
View 9 Replies View RelatedSQL & PL/SQL :: Identify Department VP In Employee Interface Table
Mar 12, 2010I have employee interface table something like this.
emp_idemp_name Job_title supervisor_name
1AJ Engineer BJ
2CK Analyst ND
3BJ Manager TR
5TR VP IT JD
6ND S Manager MD
7MD VP Telecom SK
8SK VP Eng JR
I want to idenitfy the VP for each employee. The logic I have to apply is check for hte supervisor of each employee to see if the supervisor has designation starting with 'VP'. If no, I have check the supervisor of the supervisor and so on. I tried using a recursive query using connect_by_root but in the above example for employee ND it lists the VP as both MD and SK. I need it to show on MD who is the lower in the hierarchy.
I am a Java person but since my app uses the Oracle DB I am to do this task.
Create Trigger So That Whenever Record In Employee Table Deleted?
Dec 7, 2009I'm trying to create a trigger so that whenever a record in the Employee table is deleted, a trigger will automatically delete corresponding records in the Job History table, then the Employee record is archived to EmployeeArchive before it is deleted. It compiles but with warnings. Here's what I've got.
CREATE TABLE EmployeeArchive
(EmployeeID Int, FirstName Char, LastName Char,
EMail Char, PhoneNumber Int, HireDate Date, JobID Char, Salary Int,
Commission Int, ManagerID Int, DepartmentID Char);
[Code]....
SQL & PL/SQL :: Employee Table In Scott Schema Created By Default
Oct 3, 2011I am using Oracle 11g Release 11.2.0.1.0
OS: Windows
I am using the Employee table in Scott schema which created by default.
10,20,30,40,50 are the department ids.
I would like to have the output like below. I am having lots of values in dep id and lots of employees in each dept ids
Emp name Dep ID
John 20
Mike 40
Ram 10
Guru 50
Kumar 30
SQL & PL/SQL :: Display Total Number Of Employee Working Under President In Emp Table
Apr 27, 2012display the total number of employee working under president in emp table
View 5 Replies View RelatedSQL & PL/SQL :: How To Retrieve Each Employee Age From Employee DB
Nov 25, 2011I have a table like below,I want retrieve each employee age ,from db column
SQL> select * from dob;
ENAME DB
---------- ---------
vishal 12-MAR-90
jeya 30-MAR-73
vasanthi 17-APR-80
mangai 25-NOV-81
poorna 18-AUG-80
vinod 20-AUG-81
nanthini 01-JUN-86
karthick 20-MAR-88
SQL & PL/SQL :: Highest Value?
Feb 24, 2010I have an table named as rawdata.I need output comes
F_DATE MAGNITUDE
--------- ----------
11-JAN-10 9
create table rawdata(f_date date,magnitude number);
select * from rawdata;
F_DATE MAGNITUDE
--------- ----------
10-JUN-09 6
10-DEC-09 6
11-JAN-10 7
11-JAN-10 8
11-JAN-10 9
SQL & PL/SQL :: Highest Value In Column
Jun 21, 2011I have a column.
EMPNO
----------
E123
E256
E420
E1420
I need to get the highest value among these. how to get it?
Select Row With Highest Version
Aug 25, 2010My table holds info on documents:
|doc_id|auth|version|date_created|
so what I'm trying to do is select the latest version of each document record.
so say I have there records:
doc_uid|doc_id|auth|version|date_created|
|1 | 100 |al |0.1 |11/4/08 |
|1 | 101 |al |0.2 |11/4/08 |
|2 | 102 |al |0.4 |11/4/08 |
So i want to select only one copy of every doc_uid(unique id) with the highest version number, which would make my output:
doc_uid|doc_id|auth|version|date_created|
|1 | 101 |al |0.2 |11/4/08 |
|2 | 102 |al |0.4 |11/4/08 |
doc_id 100 was excluded because it has a higher version which was shown instead.
So how would i express this is oracle sql.
edit: Just thought i should say that I have been tring to crack this for 2 days now :P I have been messing around with MAX() but I cant make it do what i want.
SQL & PL/SQL :: Getting Highest Value From Varchar Column
Feb 12, 2011I've a large table on which I applying aggregate and group by functions to the the results.
Here are two of the columns in my table:
Name ==== Score
John ==== 200*
Zohaib ==== 299
Ali ==== 0*
John ==== 200
Maria ==== 150*
Ali ==== 0
Maria ==== absent
John ==== absent
Here astrick (*) means with distinction....
The "score" column is a varchar column I want to run a query on this table to show the highest score for each student and the output should be like this:
Name ==== Score
Zohaib ==== 299
John ==== 200*
Maria ==== 150*
Ali ==== 0*
Important note:
1. if there is a tie between two highest scores like for a student, incase of john 200 was made twice, but the score with asterick (*) will be the "maximum" becuase it is with distinction so the output should also show the the highest score with asterick.
2. the output should show the the 2nd column (score) in desc order of highest score by each student...again incase of a tie, the one with astrick will come first in the result..
I know with just mere numbers, that is pretty easy but in this case it is a varchar column and also i need the astrick along with the highest score.
I want the simplest and shortest query if possible to achieve this result
I hope I've been able to clearly explain my requirment. I am using 10G.
SQL & PL/SQL :: How To Calculate Highest Common Factor
Oct 5, 2005Is there any function avaialble in SQL that can return the highest common factor among a bunch of numbers. For example
10,20,25 have a highest common factor of 5.
Updating Rows To Highest Security Level Of Missions Of Same Type
Jan 25, 2009I am trying to update the security_level of a mission to the highest security level of missions of the same type.
Attributes of the missions table:
mission_id, code_name, mission_type_id, mission_date, security_level
The following is an intermediate output.
MISSION_ID MISSION_TYPE_ID SECURITY_LEVEL
318 3 6
329 3 2
286 5 6
521 5 3
281 6 4
396 7 3
331 8 4
14 9 4
230 9 0
486 10 2
The maximum output for each mission_type_id
MAX_LEVEL TYPE
6 3
6 5
4 6
3 7
4 8
4 9
2 10
According to this 3 records (329,521 and 230) should update.
But my code returns an error.
ERROR at line 4:
cannot update (......"SECURITY_LEVEL") to NULL
this is my code
UPDATE
AM_X_442_2 amx
SET
Amx.SECURITY_LEVEL =
(
select
max_level
[code].........
I have intended the query to make it legible but it removes all spaces.
Reports & Discoverer :: MATRIX Report / Bold Value With Highest Value In Numeric Field?
Nov 11, 2010I want to Bold the value with highest value in Numeric field. how can i do this.
View 12 Replies View RelatedPL/SQL :: Finding ID Max (salary)
Oct 31, 2012Select * from one:
ID SALARY
----------------------
10 1000
20 2000
30 3200
Select * from two:
ID SALARY
----------------------
10 1000
20 2000
40 4000
10 3000
20 4000
30 3000
When i try to find ID,MAX(SALARY) from these two tables, i am getting this output:
Select id,max(salary)
from
(select * from one
union
select * from two)
group by id
order by max(salary) desc;
OUTPUT:
ID MAX(SALARY)
--------------------------------------
20 4000
40 4000
30 3200
10 3000
I want OUTPUT to be like this:
ID MAX(SALARY)
--------------------------------------
20 4000
40 4000
SQL & PL/SQL :: Salary Hike Using Cursor
Sep 22, 2011A.Create object to get the salary hike of an employee on the basis of completion of years in an organization.
Conditions:-
Create two tables with name as "Employee_yourname" and "Employee_Hike_Yourname".
"Employee_yourname" can have columns like EMPID, NAME, SALARY, DATE_OF_JOIN.
"Employee_Hike_Yourname" will have 3 more columns for new salary, Current salary and completed period in organization.
Consider the employees who have completed minimum one year in organization.
Salary hike for employee with tenure >= 1yr and < 2yrs should be 10% of current salary.
If tenure is greater than or equal to two years and less than 3 years then salary hike should be 20% of current salary.
For employees having tenure more than 3 years hike should be 30% % of current salary
SQL & PL/SQL :: Display The Maximum Sum Of Salary?
Aug 14, 2010I want to display the maximum sum of salary among sum of salary of each department.
deptno sal
10 1000
10 500
10 100
20 2000
20 200
30 500
30 1500
30 2000
30 200
Sum of salary for each department.
10 1600
20 2200
30 4200
The output should be
30 4200
Because this the highest sum of salary compare to sum of salary of reaming departments.
SQL & PL/SQL :: Calling Function Which Calculate Sum Of Salary
May 8, 2010I created a PROCEDURE in that i am calling function which calculate sum of salary...I just want Output in format for that which function i need to use...?
Actual Output:::
DEPt_Name SALARY
ACCOUNTING 8750
RESEARCH 10875
SALES 11600
I want Output in well alignment column...i WANT Output IN column format but my output in not geting in that format...Is there any function to align output...I want Output in well alignment column
SQL & PL/SQL :: Function To View Maximum Salary
Dec 16, 2011I have to write function that receives department name and an aggregation operation (average, maximum, minimum) and apply the operation on the salary of employees working on the given department and return the result.
here is my select statement:
select distinct d.deptno, d.deptname, max(e.salary)
from employee e join department d
on e.deptno=d.deptno
where d.deptname=upper('finance')
group by d.deptno, d.deptname;
[code]...