SQL & PL/SQL :: How To Find Minimum Salary Holders

Nov 7, 2012

in this query, i am stuck in this little query but cant get answer lets suppose

select * from emp;

EMPNO ENAME JOB SAL
---------------------- ---------- --------- ----------------------
7788 SCOTT ANALYST 3000
7902 FORD ANALYST 3000
7876 ADAMS CLERK 1100
7934 MILLER CLERK 1300
7900 JAMES CLERK 950

[Code]....

now if i want to see min salary takers group by job then i use

select x.job, min(x.sal), count(*) from emp x group by x.job;

JOB MIN(X.SAL) COUNT(*)
--------- ---------------------- ----------------------
CLERK1 800 1
SALESMAN 1000 5
CLERK 950 3
PRESIDENT 5000 1
MANAGER 2450 5
Developer 2975 1
ANALYST 3000 2

The above result give me minimum salary but total number of JOB holders, You can see only one SALEMAN getting 1000 but count show total number of SALESMAN. Similarly 3 MANAGERS are getting minimum and same salary but count show total number of MANAGERS.

My question is how can i get number of person on min salary?

Possibly my data should be like as this

JOB MIN(X.SAL) COUNT(*)
--------- ---------------------- ----------------------
CLERK1 800 1
SALESMAN 1000 1
CLERK 950 1
PRESIDENT 5000 1
MANAGER 2450 3
Developer 2975 1
ANALYST 3000 2

View 5 Replies


ADVERTISEMENT

Find Nth Highest Salary?

Jul 18, 2011

How 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);

View 3 Replies View Related

SQL & PL/SQL :: How To Find The Nth Highest Salary

Jun 30, 2010

how to find the nth highest salary

View 11 Replies View Related

SQL & PL/SQL :: Find All Employees Whose Salaries Greater Than Avg(salary) Of Department

Nov 26, 2010

To find all the employees whose salaries greater than avg(salary) of the department.

Quote
select empname,salary,deptid from salaries t1 where
salary > (select avg(salary) from salaries t2 where t1.deptid = t2.deptid);
Unquote

Its not diplaying all departments

View 14 Replies View Related

PL/SQL :: Create_Date Must Be Set To Minimum Of All?

Jun 29, 2013

I have a table structure as follows Student(Id,First_Name, Last_Name, email, Contact, Address1 ,Address2, City, Edit_Date,Create_Date,Archived) 

Now if there is more than one row with same email the one with the latest edit date should be updated with missing fields by using same field value other rows (if the field is present in more than one row, the one with the next latest edit date is to be considered) and

the archived status of all rows with same email except this master row must be set to 1.The Create_Date must be set to the minimum of all the create_date values of rows with same email value 

View 2 Replies View Related

Return Minimum Value Of Resultset

Jul 22, 2010

SELECT
REF.REFERRAL_TO,
REF.LOCAL_PATIENT_NUM,
REF.REFERRAL_REFERENCE_NUM,
TO_CHAR (REF.REFERRAL_DTE, 'DD/MM/YYYY') REFDATE,
TO_CHAR (OPC.CONTACT_DATE, 'DD/MM/YYYY') CONTACTDATE,
TO_DATE (OPC.CONTACT_DATE, 'DD/MM/YYYY') - TO_DATE (REF.REFERRAL_DTE, 'DD/MM/YYYY')
FROM
PAS.REFERRAL_DETAILS REF
FULL JOIN
PAS.OUTPATIENT_CONTACT OPC

[Code]...

This calculates the difference between a set date and a contact date, I only want it to return the minimum of the results set for each patient, how can this be achieved? At the moment it pulls off several records for each patient as there are several contacts.

View 2 Replies View Related

SQL & PL/SQL :: Getting Minimum Composite Key From Group By

Oct 11, 2011

I have the following case:

create table try_o
(pk1 NUMBER, pk2 NUMBER, fld1 VARCHAR2(10), fld2 NUMBER,
PRIMARY KEY (pk1, pk2));
insert ALL
into try_o values (1,1,'f1',5)
into try_o values (1,2,'f1',5)

[Code]....

I need to get the row that contains the least pk2 among the group of records with the same fld1 and fld2. but if I used

select min(pk1), min(pk2), fld1, fld2
from TRY_o
group by fld1, fld2;

I get a record that doesn't exists as the min(pk1) and min(pk2) are from different records. I need to get the min(pk1) and its corresponding pk2.

View 4 Replies View Related

Minimum Size For Db_recovery_file_dest_size

Apr 25, 2013

i am trying to enable flashback on in my database and i am getting the below error.

idle> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38708: not enough space for first flashback database log file

my db_recovery_file_dest_size is 1g. If i change it to 20g i am able to "alter database flashback on".

Why does a size of 1g give this proble? Any simple steps or recommendation that i am missing? Or is the size of the DB which impacts the db_recovery_file_dest_size also?

idle> select *from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

View 14 Replies View Related

ODP.NET :: Minimum Role For GetSchema

Apr 16, 2013

I am trying to find out what is the minimum Oracle User Role required for the GetSchema command to work using ODP.NET and the Oracle.DataAccess library.

Dim cn As New OracleConnection(ConnectionString) 'Oracle.DataAccess.Client.OracleConnection Return cn.GetSchema("Tables")

View 0 Replies View Related

SQL & PL/SQL :: Finding Nth Maximum Or Minimum From A Table?

Sep 17, 2011

how can i find nth maximum or minimum salary from a employee table? here i want only one row as output.

View 16 Replies View Related

Return Minimum Duplicate Values

Dec 16, 2009

I am trying write a script that will return all values (based on the minimum tarif) from the Germany table for any duplicate values. Duplicate values are any values with the same UFI, ZC,limitid,depot. The German table also contains the fields tarif, city, supplier, etc.

Below is the script I have previously used to sort out duplicates. I have tried 50 different ways get it to return just lines for the minimum tariff but haven't been successful.

select *
from Germany t
where (ufi,zc,limitid,depot) in (
select ufi,zc,limitid,depot from (
select ufi,zc,limitid,depot, count(*) n
from Germany t
group by ufi,zc,limitid,depot)
where n<>1
)

View 4 Replies View Related

SQL & PL/SQL :: Extract Minimum Report Time

Oct 1, 2013

I have the below scenario:

[u]Report_Time M I Ta[/u]
02-SEP-13X Y35167005
01-SEP-13X Y35931902
03-SEP-13X Y35931901

The output I am expecting from above is:

X Y 01-SEP-13 03-SEP-2013 35931902 35931901

II need to extract minimum report time , max report time and corresponding Ta.

View 11 Replies View Related

PL/SQL :: Finding ID Max (salary)

Oct 31, 2012

Select * 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

View 3 Replies View Related

Reports & Discoverer :: Select Query With Minimum Values

May 30, 2013

The prob is i want to display minimum intime and max outtime in idate against employee,report keep displaying multi inout records of an employees!

SELECT div.division,
DEP.department,
E.employeecode,
E.name empname,
DES.designation,
i.idate,
To_char (Min(i.intime), 'HH:MI:SS AM'),
To_char (Max(I.outtime), 'HH:MI:SS AM'),
Round(i.btime / 60),
e.shift
[code]....

View 7 Replies View Related

Backup & Recovery :: Minimum Files Required To Open DB

Dec 26, 2012

what are the minimum files which are required to open the db ? take it as this

I have oracle installed in c drive. I have placed 3 datafile related to one tablespace say test: two in D and one in E. I have place 3 datafile related to tablespace say orcl : two in D and one in E.

my undo is in E scenario is that my E drive crashed . db is in no archive mode . no backup is there. to be more worst db shuts down abnormally. I want my db open with data of c drive and D drive.

View 3 Replies View Related

SQL & PL/SQL :: Distinct And Individual Records In Single Or Minimum Pass(es)

Feb 21, 2012

I have following data

select rowid,object_name,object_type from do;

ROWID OBJECT_NAME OBJECT_TYPE
------------------ ------------------------------ ------------------
AAA/wuAAHAAAW73AAA CON$ TABLE
AAA/wuAAHAAAW73AAB I_COL2 INDEX
AAA/wuAAHAAAW73AAC I_USER# INDEX
AAA/wuAAHAAAW73AAD C_TS# CLUSTER
AAA/wuAAHAAAW73AAE I_OBJ# INDEX
AAA/wuAAHAAAW73AAF I_CON2 INDEX

6 rows selected.

I want it in the following manner

select rowid,object_name,object_type from do;

ROWID OBJECT_NAME OBJECT_TYPEGROUP
------------------ ------------------------------ ---------------------------
AAA/wuAAHAAAW73AAA CON$ TABLE2
AAA/wuAAHAAAW73AAB I_COL2 INDEX1
AAA/wuAAHAAAW73AAC I_USER# INDEX1
AAA/wuAAHAAAW73AAD C_TS# CLUSTER1
AAA/wuAAHAAAW73AAE I_OBJ# INDEX1
AAA/wuAAHAAAW73AAF I_CON2 INDEX1

6 rows selected.

Here the GROUP is changing when the data type is changing and thus for same data type the group shall remain the same As of now this is achieved by - first selecting distinct object_type, then it's mod(rownum,<input variable) and this result of 'mod' is doing the grouping which is the retrieved along with rowid of all individual record

Present query is as following and it is not much efficient

SELECT DO.ROWID RWID, RID
FROM DO,
(
SELECT OT,CASE MOD(ROWNUM,:v) WHEN 0 THEN :v ELSE MOD(ROWNUM,:v) end as RID
FROM(
(SELECT DISTINCT OBJECT_TYPE OT

[code]....

I tried using sequence with cycle but it gave different results. Even I tried following but it did not gave satisfactory results

select d.rowid,d.object_type,x.x1 from do d,(select distinct object_type,mod(rownum,:v) x1 from do where
created>'01-jan-2008')x where d.object_type=x.object_type and created>'01-jan-2008';

In short the query needs Distinct with mod(rownum) and individual records in a single pass The mod(rownum) i.e. group shall change when the object_type changes but then shall remain constant through out the particular object_type.

View 9 Replies View Related

SQL & PL/SQL :: Salary Hike Using Cursor

Sep 22, 2011

A.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

View 11 Replies View Related

SQL & PL/SQL :: Display The Maximum Sum Of Salary?

Aug 14, 2010

I 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.

View 4 Replies View Related

Minimum Extent Error Creating Tables In Empty Tablespaces

Dec 30, 2010

I am trying to restore to a backup instance on a backup server. When I try to recreate the tables I keep getting ORA-01659: unable to allocate MINEXTENTS. The tablespaces and datafiles on both servers show as the same size in OEM.

I have dropped all tables and OEM shows tablespaces are empty. Then I run a script to recreate all tables. Most of the tables don't get created because their TS is full. After the script to recreate all tables runs, the main tablespaces are full, more full than on the production machine. I have also tried ALTER TABLESPACE xxx COALESCE; on each tablespace right after dropping all tables and before recreating them to reclaim free space. Why is it full? I've only dropped and created the tables, there shouldn't be any data in them yet.

ORA-01659: unable to allocate MINEXTENTS beyond 2 in tablespace PLUS_T...The backup instance was already there, all I did was drop the tables. Here's what I ran on prod to build a script to recreate the tables on backup server. Got it off Burleson somewhere.

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name) ||'; '
FROM USER_TABLES u;

View 4 Replies View Related

SQL & PL/SQL :: How To Get 2nd Highest Salary From Employee Table

Apr 29, 2011

i was given a task to find the second highest employee sal from emp table.

View 5 Replies View Related

SQL & PL/SQL :: Calling Function Which Calculate Sum Of Salary

May 8, 2010

I 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

View 4 Replies View Related

Forms :: How To Validate Salary Regarding To Its MIN And MAX Value From Another Table

Jun 16, 2012

I 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?]

View 1 Replies View Related

SQL & PL/SQL :: Function To View Maximum Salary

Dec 16, 2011

I 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]...

View 9 Replies View Related

PL/SQL :: Latest Salary (last 2 Updated Records)

Jun 18, 2012

I wanted to know the latest salary(last 2 updated records ) getting by emp- id 252 based on below mentioned information

source desti sal emp id MGR
1-Jan 1-Feb 1000 252 venkat
2-Jan 2-Feb 4000 252 venkat
2-Feb 2-Feb 5000 252 venkat

View 4 Replies View Related

Server Administration :: Minimum Datafile Size Required For Creating Non-system Tablespace?

May 21, 2007

what is the Minimum datafile size required for creating a non-system tablespace?

I am trying to create a tablespace by giving the syntax like,

SQL> create tablespace t1
2 datafile '/home/data/t1.dbf' size 72k;
create tablespace t1
*
ERROR at line 1:
ORA-03214: File Size specified is smaller than minimum required

SQL> create tablespace t1
2 datafile '/home/data/t1.dbf' size 73k;

Tablespace created.

The blocksize for my database is 4096, as i have heard that the minimum size of the datafile is decided by blocksize, but i want to know that how it is calculated as by giving the above syntax the other values will be default. I am trying the syntax in oracle 9.2.0.1.0 version.

View 13 Replies View Related

SQL & PL/SQL :: Write Query To Show Monthly Salary?

Jul 12, 2012

i have table structure (emp_no, emp_sal,emp_startwork)

i want query to show the monthly salary such as jan month salary Feb month salary

View 2 Replies View Related

SQL & PL/SQL :: Write A Query To See How Many (no) Employees Getting Salary 3000?

Dec 21, 2010

write a query to see how many(no) employees getting salary 3000 & what are their names respectively.

View 2 Replies View Related

SQL & PL/SQL :: Obtain Top 2 Departments In Terms Of Total Salary?

Aug 21, 2010

I need to obtain the top 2 departments in terms of total salary.

I have the following tables (Dno corresponds to DeptNo). I've found tutorials for obtaining the salaries of the top three employees, and summing the salaries for each dept. is easy, but I can't seem to combine the two.

DNo Fname Salary
2 Tom 10000
3 Mike 20000
2 Harry 30000

DeptNo DeptName
1 Administration
2 Special
3 Finance

View 6 Replies View Related

SQL & PL/SQL :: Update Salary In Employees Table With Analytical Functions?

Feb 16, 2013

i have employee table i want to update salary with all employee 5 percent

View 4 Replies View Related

PL/SQL :: Verify If Salary In Numbers / Decimals Is Converted Into Words?

Aug 30, 2013

How do i check in Table B whether it is converted correctly into words taking input or reference from table A

Consider below example:-

Table A Table B
$125 Dollar One Hundred twenty Five only
$45,542 Dollar Forty Five Thousand Five Forty Two Only
$145.56 Dollar One Forty Five and fifty six cents Only
$145,253
$35,256.65
$560,250.67

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved