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


ADVERTISEMENT

PL/SQL :: Using A Subquery To Display Names And Max Salaries By Department / HR Schema

Mar 14, 2013

Using the HR schema, I'm trying to write a query which will display the names and salaries of those employees who earn the highest salaries in their respective departments.

I wrote the query:

SELECT first_name, last_name, salary
FROM employees
WHERE salary =
(SELECT department_id, MAX(salary) FROM employees GROUP BY department_id);

However, this query does not execute correctly but I'm not sure why?

View 7 Replies View Related

SQL & PL/SQL :: Get The Highly Paid Two Employees From Each Department With All Details?

Mar 4, 2013

Get the highly paid two employees from each department with all details?

View 3 Replies View Related

SQL & PL/SQL :: Retrieve All Information Of Dept Along With Two Employees Of Each Department

Jan 21, 2013

I WANT to RETRIEVE all the INFORMATION of DEPT ALONG with TWO EMPLOYEES of each DEPARTMENT.is any OTHER WAY to DO THIS due to performance in Oracle 10g


SELECT *
FROM DEPT D,
(SELECT DEPTNO,
MAX(DECODE(RN,1,ENAME,NULL)) ENAME1,
MAX(DECODE(RN,2,ENAME,NULL)) ENAME2
[code]......

View 6 Replies View Related

Forms :: Display Department Name In Emp Table Along With Employees Details?

Mar 28, 2013

I used Scott schema.I take 2 blocks ,one block having Deptno,Job and also 1st block is a non database block and Another Block Empno,ename,job,sal,deptno, is a database block,my question is when i enter Deptno,Job then Display Employees Details and also Department name

View 1 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

PL/SQL :: Count Number Of Employees In Every Department - Group By Clause Not Working

Oct 31, 2012

I have two columns Department and EmpName:

Department EmpName
____________________________________
Sales empname1
Sales empname2
Marketing empname3
Development empname4

Now I want to count the number of employees in every department. I want the output to be

Department Total
______________________
Sales 2
Marketing 1
Development 1

I am retrieving names of the department through a subquery

The query I am trying to execute is:

SELECT Department, Employee FROM
( SELECT ...query from other table) AS Department, count( A.EmpName) AS Employee
FROM Employer A, EmployeeInfo B
WHERE (A.EmpID = B.EmpID AND A.EmpCategory like 'Category2')
GROUP BY Department

I know that you cannot group by using aliases and hence a little work around, but still the query isn't working.

View 2 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

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 :: 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 View Related

SQL & PL/SQL :: Query To Find Out Employees Who Are All Joined Before Manager

Jun 27, 2011

Query to find out employees who are all joined before Manager.

For Example the Table may look like.

EMPIDDesignationDateJoined
E101Programmer10-Jan-04
E102Programmer22-Mar-04
E103Analyst 14-Jan-05
E104Designer20-Dec-06
E105Tester 20-Nov-07
E106Manager 11-Oct-08
E107Programmer20-Nov-09
E108Coordinator12-Dec-10
E109DB Admin10-Feb-07
E110DB Analyst10-Aug-05

The out put must be..

EMPIDDesignationDateJoined
E101Programmer10-Jan-04
E102Programmer22-Mar-04
E103Analyst 14-Jan-05
E104Designer20-Dec-06
E105Tester 20-Nov-07
E109DB Admin10-Feb-07

View 4 Replies View Related

SQL & PL/SQL :: How To Get Department Wise Effective Rate

Oct 26, 2013

1) First Table

Id , Name , Eff_date
10 I1 15-APR-2013
20 I2 30-APR-2013
30 I3 26-May-2013
20 I4 10-SEP-2013
40 I5 10-sep-2013
40 I6 10-Oct-2013

2) Second Table

Eff date Rate
15-APR-2013 900
30-APR-2013 500
16-Sep-2013 400
05-Oct-2013 200

Q. How to get department wise effective rate?

View 3 Replies View Related

SQL & PL/SQL :: How To Show Total Number Of Department

Apr 19, 2013

how to show total number of department with their department name assign to employee table.

View 2 Replies View Related

SQL & PL/SQL :: How To Select Name And Department Name From Dept And Emp Table

Aug 1, 2013

How to select Name and Department name from dept and emp table using parametrized cursor . Provide only Cursor declaration part.

View 12 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

SQL & PL/SQL :: Use Greater And Less Than In Left Join?

Apr 9, 2013

I am creating a query where I am trying to take phone call lengths and put them into buckets of length ranges 0:00 - 0:59, 1:00 - 1:59 etc. Even if there are no calls in the call table I need to return the range with a zero (hence the left join and nvl). When I do this the left join acts like an equal join, I suspect there is some reason left joins only work if there is an equal condition in the join (instead of >= and < that I use, or similarly I could use BETWEEN). I also have a question about performance (below).

The create table script for the lookup is like this:

CREATE TABLE DURATION_RANGES
(
RANGE_TEXT varchar2(20),
RANGE_LBOUND decimal(22),
RANGE_UBOUND decimal(22)
)

Sample inserts are:

INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('00:00 - 00:59',0,59);
INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('01:00 - 01:59',60,119);
etc.

The query is:
select
r.range_text as duration_range,
nvl(count(*),0) as calls,
nvl(SUM(call_duration),0) as total_duration
from

[code]...

As I say, it is not returning all ranges in the duration_ranges table, so acting like an inner join. I realize one solution would be to populate duration ranges with every value possible (instead of ranges) so join is an equal join, but that would make the duration_range table larger.

My questions:
1. Is it possible to get the left join to work with the duration range values as they currently are?
2. Even if 1 is possible, would it be better performance to have exact values (but a larger lookup table) and do an equals join instead of >=, < or BETWEEN? Performance now is not bad.

What I mean is (with only one time value and not lbound and ubound:

INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('00:00 - 00:59',0);
INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('00:00 - 00:59',1);
INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('00:00 - 00:59',2);

View 4 Replies View Related

SQL & PL/SQL :: Precision Greater Than Actual Value

Apr 6, 2012

When we are trying to create number data type column of a table with precision greater than actual value,it's accepting the definition of the table . But we are unable to insert any values into the table.how internally it stores the value

SQL> drop table precision_test;
Table dropped
SQL> create table precision_test(name number(2,5));
Table created
SQL> insert into precision_test values (1);
insert into precision_test values (1)
[code]....

View 5 Replies View Related

SQL & PL/SQL :: Records Greater Than Row Number?

Mar 9, 2011

I have two sql queries. They run the one after another.

Query 1:
select * from capital
where member_status = 'MEMBER' AND rownum <= 25
order by price desc

Query 2:
select * from capital
where member_status = 'MEMBER' AND rownum > 26
order by price desc

Question is, in the query 2 I want records greater than row number 25. In query 2, I don't want the records that were fetched in Query 1. Is there any way to do this without using rownum?

View 7 Replies View Related

SQL & PL/SQL :: Time Comparison Alone Like Less / Greater Than?

Dec 15, 2010

Select
to_char(to_date('10-02-2006 10:30:00 AM', 'DD-MM-YYYY HH:MI:SS AM'), 'HH:MI:SS AM') as a1,
to_char(to_date('10-02-2006 01:30:00 PM', 'DD-MM-YYYY HH:MI:SS AM'), 'HH:MI:SS AM') as a2,
Case
when to_char(to_date('10-02-2006 10:30:00 AM', 'DD-MM-YYYY HH:MI:SS AM'), 'HH:MI:SS AM') >

[code]...

from the above query i was expecting value '2' but its returning '1'. As I am using TO_CHAR its trying to compare characters. Is there a way, to compare times alone like less than, greater than?

View 3 Replies View Related

SQL & PL/SQL :: Identify Department VP In Employee Interface Table

Mar 12, 2010

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

View 13 Replies View Related

SQL & PL/SQL :: Track Department Change - Update A Counter

Jun 22, 2011

I am trying to track an employee's department changes by adding a Department change counter which is set to 1 whenever the department changes otherwise it is set to a 0. How can I accomplish this update of the counter? Below is the table creation sql and sample data for insert.

CREATE TABLE EMPL_DEPT
(EMPLID VARCHAR2(11) NOT NULL,
EFFDT DATE,
EFFSEQ SMALLINT NOT NULL,
EMPL_RCD SMALLINT NOT NULL,
DEPT_ID VARCHAR2(12) NOT NULL,
[code]..........

View 5 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

SQL & PL/SQL :: Selecting Data Greater Than Some Value Into One Column

Jul 15, 2013

I have table TEST_REP with below data

DA SUMA
---------------------- ----------------------
2011 2
2011 3
2011 5
2012 2
2012 7
2014 2
2014 10
2015 2
2016 33
2015 26
2017 21
2017 2
2018 23

13 rows selected

I have used following query to get the below output:

select
br_mat MAT_YEAR,
sum(br_par) TOTAL
from (
(select to_char(da) br_mat,suma br_par from test_rep)
UNION ALL
[code].......

Output :

MAT_YEAR TOTAL
---------------------------------------- ----------------------
2011 10
2012 9
2013 0
2014 12
2015 28
2016 33
2017 23
2018 23
2019 0
2020 0

10 rows selected

Expected Output :

MAT_YEAR TOTAL
---------------------------------------- ----------------------
2011 10
2012 9
2013 0
2014 12
2015 28
2016 33
2017 and Greater 46

View 6 Replies View Related

Forms :: Show Manager / Employee And Department On The Tree

Nov 1, 2012

i have 3 tables

table employee
employee_no
employee_name

table manager
manager_id
manager_name

table department
dept_id
dept_name

how can show on the tree all manager with employee with department

example
manager ali
employee Mohamed
department marketing

View 6 Replies View Related

Write Function That Receives Department Name / Aggregation Operation

Sep 13, 2012

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 3 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







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