SQL & PL/SQL :: How To Select Name And Department Name From Dept And Emp Table
Aug 1, 2013How to select Name and Department name from dept and emp table using parametrized cursor . Provide only Cursor declaration part.
View 12 Replies
ADVERTISEMENT
SQL & PL/SQL :: Retrieve All Information Of Dept Along With Two Employees Of Each Department
Jan 21, 2013I 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]......
Select Multiple Images Using Department Field In Spriden Table To Pull Needed Id Numbers
Jan 29, 2009I have an Oracle 10g database, on the App Serv I have an image file that has 20,000 .jpg files that has an id number as each image name.I have successfully queryed the image file and posted one image to my web page matching the image id number.
sample:
select substr(spriden_last_name,1,20)||', '||
substr(spriden_first_name,1,20)||' '||
substr(spriden_mi,1,1) stname,
'<img src = "/images/&1..JPG" width="400" height="400"/>' pic
from spriden
where spriden_id = '&1'
/
the &1 is the matching id number that is input from the user.My task now is to select multiple images using a department field in the spriden table to pull the needed id numbers.I have not been successful in the proper format to pass the id number to the <img src field.
SQL & PL/SQL :: Find Deptno Present In Dept Table?
Jun 23, 2013In dept table there is deptno=10,20,30,40
in emp table there is deptno=10,20,30
i want to find the deptno which present in dept table but not
present in emp table.
SQL & PL/SQL :: Identify Department VP In Employee Interface Table
Mar 12, 2010I 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.
Forms :: Display Department Name In Emp Table Along With Employees Details?
Mar 28, 2013I 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 RelatedSQL & PL/SQL :: Single Excel File For Emp Table Department Wise In Different Sheets Of Excel
Mar 30, 2012I need to generate a single excel file which each department details in seperate sheets of the excel file, each sheet should ne named with department no (ex :Dept_10, Dept_20,Dept_30) and the main excel should be named as Emp_details.
Header row and total row should be in bold.I have created 3 individual excels thru toad and moved them to single excel and renamed the sheets.
Can we programatically do this thru SQL or PL/SQL, does Oracle provide any build packages for excel.As excel files cannot be uploaded,I am unable to upload the excel file I generated manually.
select to_char(empno) empno,ename,job,mgr,hiredate,
nvl(sal,0) sal,nvl(comm,0) comm,nvl(sal,0) + nvl(comm,0) "Total Income" ,deptno
from emp
where deptno = 10
union all
select 'Total',null,null,null,null,sum(nvl(sal,0)),sum(nvl(comm,0)),sum(nvl(sal,0))+sum(nvl(comm,0)),null
from emp
where deptno = 10;
SQL & PL/SQL :: EMP And DEPT Tables
Dec 4, 2006What is the structure and the values of the default EMP and DEPT table in oracle 9i....i accidentally dropped those 2 tables .... and i don't remember the structure of it.
View -1 Replies View RelatedSQL & PL/SQL :: Finding First Two Employees Along With Dept Info
Jul 24, 2013I 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;
SQL & PL/SQL :: To Update The Names Of Employees By Concatenating With A For DEPT 20
Aug 21, 2012I 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
PL/SQL :: Update Names Of Employees By Concatenating With A For DEPT 20
Aug 21, 2012I 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.
SQL & PL/SQL :: Partition Keys Has To Be Changed To Dept From The Existing Version
Aug 9, 2011table was defined as below & indexes are also created on name /dept columns, data is also available :
create table test (
name varchar2(10),
version NUMBER(12),
dept varchar2(10)
[code].....
Now the requirement is that the parition keys has to be changed to 'dept' from the existing 'version' . How to accomplish this without any implication on the indexes and other constraints.
SQL & PL/SQL :: How To Get Department Wise Effective Rate
Oct 26, 20131) 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?
SQL & PL/SQL :: How To Show Total Number Of Department
Apr 19, 2013how to show total number of department with their department name assign to employee table.
View 2 Replies View RelatedSQL & PL/SQL :: Get The Highly Paid Two Employees From Each Department With All Details?
Mar 4, 2013Get the highly paid two employees from each department with all details?
View 3 Replies View RelatedSQL & PL/SQL :: Track Department Change - Update A Counter
Jun 22, 2011I 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]..........
Forms :: Show Manager / Employee And Department On The Tree
Nov 1, 2012i 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
SQL & PL/SQL :: Find All Employees Whose Salaries Greater Than Avg(salary) Of Department
Nov 26, 2010To 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
Write Function That Receives Department Name / Aggregation Operation
Sep 13, 2012I 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]...
PL/SQL :: Using A Subquery To Display Names And Max Salaries By Department / HR Schema
Mar 14, 2013Using 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?
Forms :: Create Hierarchical Tree With Department Number As Node?
Mar 25, 2010I would like to create a hierarchical tree with Department number as a node and all the employees (only employee names) under that department as shown below:
[-]Department - 10
KING
JAMES
-----
[-]Department - 20
MARY
|
|
and so on...
I have created a hierarchical tree 'HT_DEPTNO' under block 'BL_EMP'. I also created a Record Group 'RG_HTREE' with query as shown below:
SELECT 1, LEVEL, E.ENAME, D.DEPTNO||' - '||D.DNAME DEPARTMENT, D.DEPTNO
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
START WITH E.DEPTNO = 10
CONNECT BY PRIOR E.EMPNO=E.MGR
I am attaching the form for your reference.
PL/SQL :: Count Number Of Employees In Every Department - Group By Clause Not Working
Oct 31, 2012I 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.
SQL & PL/SQL :: Select Data From Test-1 Table Where ID Values In Table Exists In 2?
Aug 31, 2010I have the below data in table test_1.
select * from test_1
IDNameTotal
-----------
1A100
2B100
3C100
4D100
test_2 table contains the concatination of ID's with comma seperated. Actually in this table ID column is of datatype varchar2.
select * from test_2
ID
----
1,2,3
My requirement is to select the data from test_1 table where the id values in this table exists in test_2 table. I tried with the belowselect statement, but could not get any data.
SELECT * FROM test_1 WHERE to_char(id) IN (SELECT id FROM test_2)
create table test_1 (id number, name varchar2(100), total number)
create table test_2(id varchar2(100))
insert into test_1 values (1,'A',100)
insert into test_1 values (2,'B',100)
insert into test_1 values (3,'C',100)
insert into test_1 values (4,'D',100)
SQL & PL/SQL :: Table And MV Of Same Name - How To Select Table / MV Separately
Dec 11, 2011I have the same problem like in below link
[URL]
SQL & PL/SQL :: Select From Table
Sep 24, 2010Below is my table structure.
create table test_join (
user_name varchar2(20),
attribut varchar2(30),
attribute_val varchar2(30)
);
[code].......
I need to query an users name, manager,location and the manager's manager. Say the info for user A would be,
User : A
Location : Bangalore
Manager : B
Manager's Manager : C
I can get this in two step process.
select attribut,attribute_val from test_join where user_name = 'A';
select attribute_val from test_join where user_name =
(select attribute_val from test_join where user_name = 'A' and ATTRIBUT = 'manager') and
ATTRIBUT = 'manager';
But is there a way to get this in one single query ?
SQL & PL/SQL :: How To Select From A Table
Aug 23, 2005I have requirement where I need to use a regular select statement for a PL/SQL table. How can I do this? Here is a code example:
Create or replace PACKAGE cv IS
type comp_rec is RECORD
(deptno number,
[Code]....
In the function AAA, I am trying to return a ref cursor. I want to base the ref cursor on a PL/SQL table rather than a Oracle table. But unfortunately
open temp_cv for select * from temp_tbl;
is erroring out.
Can I do this?? that is, use a PL/SQL table in a regular select. If not, then, is there a workaround ?
How To Select Table From Known Column Name
Sep 2, 2009I want to find out all the tables that contains a known column name?
I know a column name called 'receiving_number', but don't what what table it's in.
SQL & PL/SQL :: Create Table As Select On 10.2.0.5?
Oct 26, 2011running on 10.2.0.5 and above satisfies the following:
having locally managed tablespace with assm enabled
create table t1 (n1 number) storage (next 1M);
insert into t1 values (1); commit;
create table t2 storage (next 2M) as select * from t1;
This will indeed set the storage option next to 2M;
The same situation in 10.2.0.4 will allways set next option for the ctas statement to the initial value of table t1.
Is this an impact of patch set 4 (10.2.0.5)? Why does it differ from 10.2.0.4 to newer releases?
SQL & PL/SQL :: SELECT From Dynamic Table Name?
Jul 12, 2013I do not know the table name but have a query that for sure returns the table name and I want to select a column value from the table into my PLSQL variable.
Here is the code :
DECLARE
v_VerTabName VARCHAR2(30);
v_Minor NUMBER(2);
[Code]....
But when I compile this I get ORA-00936 : Missing expression at the EXECUTE IMMEDIATE line.
SQL & PL/SQL :: Not To Select A Column From A Table
May 10, 2012If there are 100 colums or above in a table and we dont want select a single(any one) column from those 100 columns. is there any command or function to do this in oracle.
I can do it by writting names of all 99 columns but as this method is lenghty, so need some efficient way to do this.
like in SQL server we have a "except" keyword through which we can rule out the columns .
ex: select * except (column_name1,column_name_2)
from Table_name.