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
ADVERTISEMENT
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
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
Feb 26, 2013
Refer the table below and provide me the query to find given employee is manager or not.
Refer the table below, emp#100 is manager, emp#300 is not a manager.
<emp> <emp_name> < manager >
100Smith null
200Ram 500
300Shankar 500
400Madhur 200
500Suman 100
600Sundar 100
View 2 Replies
View Related
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
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
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
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
Aug 15, 2013
I have install oracle 11g database enterprise edition But i cannot find the Oracle Enterprise Manager after the installation. i want the gui based Oracle Enterprise Manager i have access the the web base Oracle Enterprise Manager but not the gui based.how to use and download the GUI Based Oracle Enterprise Manager ....
View 1 Replies
View Related
Apr 25, 2013
I use Oracle 9i and I created DBMS Job for run Procedure. when job run by scheduled I found Failure notify but I can not find where the detail of it. How do I find path or detail of Failure notify? I use oracle enterprise manager console.
View 1 Replies
View Related
Jun 17, 2008
I have a schema whereby a table is not joined with other tables.
the info on that table can be gotten manually (by doing a query) and then using that info in another query. so is there a way of getting info from that table?
View 8 Replies
View Related
Jul 22, 2010
I have the following, simplified, query involving 3 tables:
Table 1
ID---Student
1----Terry
2----Gilbert
3 ---Egbert
4 ---Norris
Table 2
ID--Base_ID--Base_Name
1 --F100 ------St. Vincents
3 --F102 ------AB Junior High
4 --F103 ------CD Middle School
Table 3
ID--Choice_order_ID--Secondary_Base_Choice
1---Co1-------------School A
1---Co2-------------School B
1---Co3-------------School C
2---Co1-------------School B
2---Co2-------------School A
2---Co3-------------(null)
3---Co1-------------School C
3---Co2-------------School A
3---Co3-------------School B
I use the following query:
select table1.student, table2.base_name
from table 1, table2,
where table1.id = table2.id (+)
which returns the student and the base name they attend, i.e.
Student---Base_Name
Terry-----St. Vincents
Gilbert----(null)
However, I need to also list their school Secondary_Base_Choices horizontally, in the following format,
I.e.
Student---Base_Name----Base_Choice_1---Base_Choice_2---Base_Choice_3
Terry-----St. Vincents----School A----------School B-----------School C
Gilbert----(NULL)---------'NO DATA'--------'NO DATA'----------'NO DATA'
Egbert----AB Junior H----School B----------School A-----------'NO DATA'
View 3 Replies
View Related
Jun 18, 2010
Does EM have a utility that I can get to scan a set of host machines and their disks and tell me what oracle products are installed? Sort of like that common inventory tool used on windows desktops, but just for Oracle products.
If not EM then any third party tool or some other Oracle tool?
View 2 Replies
View Related
Sep 25, 2012
query to get the name and size of all the databases associated to grid..
I need to find this to do capacity planning in our environment.
View 6 Replies
View Related
Apr 29, 2013
I am using Oracle database 10g R2.
suppose I have this structure
EMP_ID EMP_NAME MANAGER_ID
1 KING
2 STEVE 1
3 FRANK 2
What I want is when I query for any EMP_ID, I want to get all his manager first, and the last record is the employee in leaf of the hierarchy in the query.
For example, when I query for EMP_ID=2 ,I want to get this result
EMP_ID EMP_NAME MANAGER_ID
1 KING
2 STEVE 1
and when I query for EMP_ID=3 ,I want to get this result:
EMP_ID EMP_NAME MANAGER_ID
1 KING
2 STEVE 1
3 FRANK 2
How to write this query?
View 3 Replies
View Related
Apr 16, 2010
from the below string i want to find the count of the "<-" using a single query.
string is "aaaa<-bbbb<-ccccc<-ddddd<-eeeeee<-ffffff<-"
If not the query, pointer on using SQL functions.
View 3 Replies
View Related
Nov 5, 2010
building SQL query to get the result as shown below.
Create Table Temp
CREATE TABLE TEMP
(
CASEID NUMBER,
SATUS VARCHAR2(1 BYTE),
TRANS_DATE DATE
)
Insert data
[Code]...
I want to build a query which should give output as shown below. Basically i want to select those rows which having minimum trans_date for a given CASEID & Status.
OUTPUT:
CASEIDSATUSTRANS_DATE
100A11/02/2010
100B11/07/2010
100A11/12/2010
200A11/02/2010
200B11/07/2010
View 6 Replies
View Related
Oct 23, 2012
the below requirement.
I have 2 tables, first one MAIN_T, which contains, user_id and username.
The second table is a relationship table which contains the referal details.
The requirement is to convert the user_id and frnd_id from RELATION_T to their respctive names using MAIN_T.
-- Test Case
CREATE TABLE main_t (user_id NUMBER, username VARCHAR2(10));
CREATE TABLE relation_t(user_id NUMBER, frnd_id NUMBER);
INSERT INTO main_t VALUES(1, 'u1');
INSERT INTO main_t VALUES(2, 'u2');
INSERT INTO main_t VALUES(3, 'u3');
INSERT INTO main_t VALUES(4, 'u4');
INSERT INTO main_t VALUES(5, 'u5');
[Code]....
I have written this query, which gives me the expected output.
SELECT membr.username member_name , frnd.username friend_name
FROM
main_t membr,
main_t frnd,
relation_t rltn
WHERE
rltn.user_id=membr.user_id
AND rltn.frnd_id=frnd.user_id
/
MEMBER_NAM FRIEND_NAM
---------- ----------
u1 u2
u2 u3
u5 u4
u1 u5
4 rows selected.
a better way for this requirement.
View 2 Replies
View Related
Oct 5, 2012
i have a requirement where i have to get the id who are only subscribed to only one course based on the below provided data.
ID Course
103812 CFH
102968 REP
103812 DFH
102968 DFH
103071 DFH
102968 CFH
View 5 Replies
View Related
Aug 3, 2010
I have a table with thirty column for attendance like Day_A01,Day_A02......Day_A31.
I need a query that should return the column which is empty but its next and previous column is not empty.
View 13 Replies
View Related
Aug 23, 2010
SELECT HISTORY_ID ,SUM(MISSED_SCHOOL) AS MISSED_SCHOOL,SUM(MISSED_SCHOOL_LAST) AS MISSED_SCHOOL_LAST
FROM EMRASTHAMAHISTORYDETAILS
WHERE ------
GROUP BY HISTORY_ID
There is no date column in table using sysdate alone need to retrieve last 6 month records
how to use in where condition
View 13 Replies
View Related
Apr 20, 2013
I need to find out that assume i have a table having 2 column
Num Name
1 Adam
2 Akanksha
1 barren
2 bosli
3 Benergee
4 Bhawna
3 anjani
I want a query as if A is there 2 times then there should be 1 then 2 then there is b coming in 4 places then it should be 1 2 3 4 and again there is anjani so 3 should be there as 1 and 2 in first 2 places and the num should be automatically generated number based on the count of the alphabets
View 6 Replies
View Related
Jan 16, 2013
i need a SQL query which should return me relevant table names. i.e. if there is table 'EMP' , then query should give table names with below result:
EMP
EMP_1
EMP_2
EMP_3
EMP_4
i.e. All tables which is starting with EMP (No Hardcoding of table, It should be dynamic way).I know we can achieve through SELECT * FROM USER_ OBJECTS WHERE OBJECT_NAME LIKE 'EMP%'. But here object_name i will passing dynamically.
View 10 Replies
View Related
Oct 24, 2013
I am working on a form, whose one block is query_find, so on clicking find button it will open another block.the find block contain 4 column and second block contain around 10 column.but the column present in find block are associated to standard tables and the column present in block-2 are from custom tables.there is a query which link those column.
I am not sure where to use that query, so that it will take input from the find block and then populate the result into the block-2.
View 13 Replies
View Related
Jul 4, 2011
I want to know how I can find which query is taking more time , for example some query's are run from unix, java and from toad,sqlplus. and one query is taking much more time to execute, so how i can get that query and all the details.
View 2 Replies
View Related
Jul 2, 2013
My table has two date columns EFF_DT which is the start date and TERM_DT is the end date. The EFF_DT of the next record should be the next date of the TERM_DT record.
My table looks like this.
Input Table:
-----------
CK_IDPI_IDEFF_DT TERM_DT
Mem1ABC1-Jan-1331-Mar-13
Mem1ABC1-Apr-1331-May-13
Mem1ABC1-Jun-1330-Sep-13
Mem1ABC15-Oct-1331-Dec-13
Mem1ABC1-Jan-1431-Mar-14
Mem1XYZ1-Apr-1430-Jun-14
Mem1XYZ1-Jul-1431-Dec-14
Expected Output:
----------------
CK_IDPI_IDEFF_DT TERM_DT
Mem1ABC1-Jan-1330-Sep-13
Mem1ABC15-Oct-1331-Mar-14
Mem1XYZ1-Apr-1431-Dec-14
In the fourth record, the effective date should be 1-Oct-13 which is the next date to the last TERM_DT 30-Sep-13.As the is the break in the date, the output should show 15-Oct-13 sa the second start date.
Note: Refer to the PI_ID columns, there is a break in the date for the sale PI_ID 'ABC'.
Here I am trying to generate a pseudo column, so that the table with the pseudo column looks like as shown below. and I can use first_value and LAST_value by partitioning on the pseudo column to get the desired output.
1) CNT_VAL is the pseudo column:
-----------------------------
CK_IDPI_IDEFF_DT TERM_DT CNT_VAL
Mem1ABC1-Jan-1331-Mar-131
Mem1ABC1-Apr-1331-May-131
Mem1ABC1-Jun-1330-Sep-131
[code].....
My Query :
----------
I not getting the desired output here as the value in pseudo column is 3.
select CK_ID, PI_ID,EFF_DT,TERM_DT,
(case
when case_CONT - LAG(case_CONT,1) over (ORDER BY EFF_DT) = 0 then to_char(case_CONT)
when case_CONT - LAG(case_CONT,1) over (ORDER BY EFF_DT) <> 0 then to_char(LAG(case_CONT,1) over (ORDER BY EFF_DT) + 1)
else to_char(nvl(case_CONT,0))
[code].....
Scripts:
--------------------
Create table lead_test(
CK_ID varchar2(10),
PI_IDvarchar2(10),
EFF_DTDate,
TERM_DT date);
[code].....
View 1 Replies
View Related
Aug 21, 2010
I need to query a table to get the time difference based on different dates.
SAMPLE DATA:
JOB ID start_time end_time
1 201008190056 19/08/2010 01:23:12
2 201008190123 19/08/2010 01:50:12
2 201008200045 19/08/2010 01:50:12
3 201008070345 7/08/2010 04:50:12
3 201008070345 7/08/2010 04:50:12
1 201008070003 7/08/2010 00:30:12
in the above, for job 1 ,on 19/08/2010 the time difference should be calculated as
01:23:12-00:56:00 and the difference should be in minutes.
for end_time. i can't take the substring as the length of the date varies for 19 and 7. In case of start date, the time has to be in format, hh:mm:ss, to calculate the difference.
View 2 Replies
View Related
Jul 29, 2011
find the recent SQL history from Oracle 11. could the following SQL retrieve all SQL history happened in last 5 mins?
SELECT h.user_id,
s2.username,
h.sample_time,
s1.sql_text
FROM v$active_session_history h,
v$sqlarea s1,
[code]....
View 3 Replies
View Related
Feb 23, 2013
I have a table as follows:
customers
------------------------------------------------------------------------------------------
custid credit amt month
--------------------------------------------------------------------------------------------
001 C 2000 Jan-2012
001 D 5000 Feb-2012
001 C 3000 Mar-2012
001 C 3000 Apr-2012
001 D 7000 May-2012
I Have to write a single query to calculate the sum of credit and sum of debit value separately.
View 8 Replies
View Related
Sep 3, 2013
I google to find the Table Name and Column Name by having a value(Number/String). And my where clauses are
where owner NOT IN ('SYS','SYSTEM') and data_type IN ('CHAR','VARCHAR2','NUMBER')
My query as follows
select a.owner, c.column_name, c.data_type, c.owner, c.table_namefrom dba_objects a, all_tab_cols c where a.owner NOT IN ('SYS','SYSTEM') and where c.owner NOT IN ('SYS','SYSTEM') and where c.data_type IN ('CHAR','VARCHAR2')order by a.owner
View 3 Replies
View Related
