SQL To Get Top 10 Employees

Jun 17, 2011

I have Employees and Salary columns in emp table. So i have nearly 200 employees.

I need to show top 10 employees who has maximum salary.

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: How To Get All Employees Under Same Manager

Sep 27, 2010

For table employees with

EMPLOYEES
__________________________________________
EMPID NAME MANAGERID
------------------------------------------
34 Amy
17 Ben 34
5 Chris 34
10 Don 5
...

How can we use SQL to get all employees under one manager , either direct or indirect

I only can think the following SQL to get the first direct employee.

select e1.empid
from employees e1, employees e2
where e1.managerid = e2.empid
and e2.empid = '34';

Perhaps we could put it in PL/SQL procedure to do recursive call?

View 1 Replies View Related

Extract All Employees Who Have 2 Functions

Feb 24, 2013

I have a table:

create table employee_function
(
id_emloyee number,
id_function number
);
with clients and their functions.

I want to extract all employes who has 2 functions (ex:id_function = 1 and id_function=2)

View 3 Replies View Related

SQL & PL/SQL :: Oracle 10g - Select Top 2 From Employees

Apr 28, 2010

Select top 2 * from employees;

TOP command or function is suitable for oracle 10g SQL

if not then for top analysis what should need to use..is this query work in Oracle SQL 10g?

View 4 Replies View Related

SQL & PL/SQL :: Count Of Hired Employees

Sep 11, 2012

I borrowed some code fragments from other posts and put together a sql query. I think there is a better method in other posts but I couldn't get them working.

I'm trying to get the count of hired employee(s) using the EMP table. I want from the first hire to the last and ALL in between. If there were no hires in that window, I want 0.

WITH minmax AS
(SELECT MIN(last_day(hiredate)) fmonth, MAX(last_day(hiredate)) lmonth
FROM emp),
cal AS
(SELECT add_months(fmonth, LEVEL - 1) mnth
FROM minmax
CONNECT BY LEVEL <= months_between(lmonth, fmonth) + 1),
vals AS
(SELECT extract(YEAR FROM mnth) YEAR, extract(MONTH FROM mnth) MONTH
FROM cal),
data AS
(SELECT extract(YEAR FROM hiredate) YEAR,
extract(MONTH FROM hiredate) MONTH,
COUNT(*) hired_cnt
[code]...

View 2 Replies View Related

SQL & PL/SQL :: Employees Joined In Each Month

Jan 28, 2011

getting the following kind of output.

I would like to get Each year and the number of employees joined in the corresponding month for jan, feb, mar and april from emp table.

A sample output looks like below.

YEAR JAN FEB MAR APRIL
2000 4 2 1 2
2001 2 1 1 5
2002 2 4 2 6
2004 2 4 1 4

View 7 Replies View Related

SQL & PL/SQL :: Update Job-id Of Employees Table Of HR Schema?

Jan 8, 2012

I am trying to update of job_id column of employees table for employee number 205 two times one after another. First time job_id column of employees table for employee number 205 is updated with new job_id. But second time job_id column of employees for employee number 205 table can not be updated. Oracle returns the following errors

HR:orcl > update employees set JOB_ID='AC_MGR' where employee_id=205;
update employees set JOB_ID='AC_MGR' where employee_id=205
*
ERROR at line 1:
ORA-00001: unique constraint (HR.JHIST_EMP_ID_ST_DATE_PK) violated
ORA-06512: at "HR.ADD_JOB_HISTORY", line 10
ORA-06512: at "HR.UPDATE_JOB_HISTORY", line 2
ORA-04088: error during execution of trigger 'HR.UPDATE_JOB_HISTORY'

As there is composite primary key using employee_id and start_date column.how to update same employee job_id twice.AS we can see from job_history table, the record for employee 200 is as follow

EMPLOYEE_ID START_DATE END_DATE JOB_ID DEPARTMENT_ID
----------- ----------- ---------- ---------- -------------
200 17-SEP-1987 17-JUN-1993 AD_ASST 90
200 01-JUL-1994 31-DEC-1998 AC_ACCOUNT 90

So how can i do the same for employee 205 without changing hire_date after first job_id update. Since for every update of job_id fires trigger. To insert row in job_history table employee id and start_date must be unique each time. HERE hire_date of employees table is used as start_date of job_id table. how it was possible for employee id 200 to change job_id twice?

View 4 Replies View Related

Forms :: Assign Employees To Jobs

Oct 12, 2011

Assign employees to their jobs in consideration the maximum number of employees to each jobs is 5 employee plus each job has own the maximum number of employees

we need the maximum number of employees for each job 5 to be variable when need to change this maximum for certain job , change this number from database (form the from of job ) not form code )

tables
emp
emp_no
name
manager
hiredate
salary

job
job_no
job

you can add tables or attributes to tables to complete you business.

View 10 Replies View Related

SQL & PL/SQL :: Finding First Two Employees Along With Dept Info

Jul 24, 2013

I want TO find out FIRST two employee joined IN A particular department WITH department information.THE relation IS basically FROM THE scott SCHEMA.I tried LIKE AS follows. IS there ANY other way FOR best PERFORMANCE.

SELECT deptno,dname,loc,
Max(Decode(rn, 1, hiredate))hiredate1,
Max(Decode(rn, 1, ename)) employee1,
Max(Decode(rn, 2, hiredate))hiredate2,
Max(Decode(rn, 2, ename)) employee2
FROM (SELECT d.deptno,dname,loc,hiredate,ename,Row_number() over(PARTITION BY e.deptno ORDER BY hiredate) rn
FROM dept d, emp e
WHERE d.deptno = e.deptno(+))
GROUP BY deptno,dname,loc;

View 15 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 :: Group Functions - Number Of Employees Joined

Aug 21, 2013

I am having a table employees with columns

1.employee_id
2.department_id
3.hire_date

Display department ID, year, and Number of employees joined?

View 10 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 :: Display Count Of Employees That Belongs To Different Categories?

Sep 21, 2010

I have to display count of employees that belongs to different categories.

is the situatio There is a category table CATEGORY with three columns (PK,NAME,TREEPOSITION) and we have categories A, B, C these three categories can further have sub-categories so the treeposition for the sub categories will be followed by their root category with _ 'symbol'

Now I have table for the employees with 3 columns (pk,name,category_id), where employees.category_id=category.pk So I want to calculate the number of employees in each category or sub-category.

since the number of categories will be large and each will be having different names so going through names will be bad option left is grouping through the treepostion
the problem is I cant use like using IN for the TREEPOSITION. .

TABLE EMPLOYEES ( PK,NAME,CATEGORY_ID)
TBALE CATEGORIES (PK,NAME,TREEPOSITION)

View 2 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 :: To Update The Names Of Employees By Concatenating With A For DEPT 20

Aug 21, 2012

I want to update the names of employees by concatenating with A for DEPT 20. For that I have written the following PL/SQL block. But instead of one A the ename are concatenating with five AAAAA s.

DECLARE
TYPE lt_emp_arr IS TABLE OF t_emp.deptno%TYPE;
l_emp_arr lt_emp_arr;
BEGIN
SELECT deptno
BULK COLLECT INTO l_emp_arr
[code].......

I got the following out put.

EMPNOENAME SAL DEPTNO

7566JONESAAAAA200020
7788SCOTTAAAAA300020
7902FORDAAAAA300020
7369SMITHAAAAA800020
7876ADAMSAAAAA 130020

But I want the output like this

EMPNOENAMESAL DEPTNO

7566JONESA200020
7788SCOTTA300020
7902FORDA300020
7369SMITHA800020
7876ADAMSA 130020

View 1 Replies View Related

SQL & PL/SQL :: Fetch All Emails Of Employees And Separate Them By Semicolon

Aug 17, 2010

Need pl/sql block to fetch all the emails of all the employees from the database and separate them by semicolon....

View 4 Replies View Related

PL/SQL :: Create Query To Display Total No Of Employees?

Aug 22, 2012

How can I create a query to display the total no of employees and, of that total, the number of employees hired in 1995,1996,1997,1998.

I am using Oracle® Database Express Edition 10g Release 2 (10.2)

View 3 Replies View Related

PL/SQL :: Update Names Of Employees By Concatenating With A For DEPT 20

Aug 21, 2012

I want to update the names of employees by concatnating with A for DEPT 20. For that I have writteh the following PL/SQL block. But instead of one A the ename are concatinating with five AAAAA s.

DECLARE
   TYPE lt_emp_arr IS TABLE OF t_emp.deptno%TYPE;
   l_emp_arr   lt_emp_arr;
BEGIN
       SELECT   deptno
 
[Code]...

/I got the following out put.

EMPNO     ENAME              SAL   DEPTNO

7566     JONESAAAAA     2000     20     
7788     SCOTTAAAAA     3000     20     
7902     FORDAAAAA     3000     20     
7369     SMITHAAAAA     8000     20     
7876     ADAMSAAAAA      1300     20    

 But I want the output like this

EMPNO     ENAME     SAL   DEPTNO

7566     JONESA     2000     20     
7788     SCOTTA     3000     20     
7902     FORDA     3000     20     
7369     SMITHA     8000     20     
7876     ADAMSA  1300     20

how to achieve this.

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

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

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 :: 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 :: Right SQL To Show Employees Detail Attendance According To Number Of Days

Jun 12, 2012

There is a attendance table having structure(empid number,signtime datetime)It has data of attendance of employees:

What is the right sql to show employees detail attendance according to the no of days attendance . i.e.(According to the maximum no of attendance first and so on)

suppose: 3 employees abc,bbc,cca abc has 20 days of attendance ,bbc has 21 days,cca has 19 days..The report like this:

bbc 1/5/12 10:30
abc .....

then
cca
according to the no of attendannce

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







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