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?
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]...
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
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?
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.
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;
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. .
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 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
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
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
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:
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.
we have 5 database servers in different locations,
1.main database 10g(production) ip-192.1.1.1 2.standby for production 10g ip- 192.1.1.2
both are linux
3.another database 10g windows 4. another database 9i windows
both are in same location
5.new database 10g in linux ip-192.1.1.3 some other location i m having a system windows xp installed i m monitoring, all 10g by oracle enterpric manager linux os monitor by Putty software
and i m monitoring below 10g database by sqlplus. now how could i monitor all the database by oracle enterprise manager grid control installed in my system that windows OS installed.
I am having a database running in my windows 2003 server and I want to configure Enterprise manager for that database to check the resource utilization during the peak hours and also the number of transactions...
i have already give the grant option cnnect,resource to scott but it is not connecting to enter prise manager console of Oracle database 11gR1. i am getting this error while connecting "The application requires more database privileges than you have currently been granted. Click on Help to get more version specific information"
when i am giving scott the dba rights then it is connecting. but i cann't give dba rights to scott.so, how/what is the specific command to give permission scott for connecting to enter prise manager console.
We have the Oracle Enterprise Manager Grid Control 10.2.0.5. We recently upgraded our Databases from 10.2.0.3 to 10.2.0.4 and since then we are having this problem.
Whenever i click an Agent on the Grid Control it gives me this error: "Communication between the Oracle Management Service host to the Agent host is unavailable. Any functions or displayed information requiring this communication link will be unavailable. For example: deleting/configuring/adding targets, uploading metric data, or displaying Agent home page information such as Agent to Management Service Response Time (ms)."
I get the same error when i try to configure a host or a database.
i have change my database name successful but after that when i open my enterprise manager i can open it but i cannot do anything in enterprise manager . i cannot show any tabs there.. i have attached my this enterprise manager login page here
