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


ADVERTISEMENT

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

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 :: EMP And DEPT Tables

Dec 4, 2006

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

SQL & PL/SQL :: Find Deptno Present In Dept Table?

Jun 23, 2013

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

View 4 Replies View Related

SQL & PL/SQL :: Partition Keys Has To Be Changed To Dept From The Existing Version

Aug 9, 2011

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

View 1 Replies View Related

SQL & PL/SQL :: How To Get Column Info From Ref Cursor

Feb 20, 2013

i know the procedure "DBMS_SQL.DESCRIBE_COLUMNS" and the example,the example uses the "dbms_sql.open_cursor",not the ref cursor. so how to get the information of ref cursor by using describe_columns? or how to get the information of ref cursor by other?

View 4 Replies View Related

SQL & PL/SQL :: Getting Info About A Table Through Query?

Mar 10, 2011

Is there a way to retrieve information about a table through an sql query? In particular I need to retrieve the size of some varchar columns.

View 3 Replies View Related

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

Query (Sub-selects) - Getting Info From Database

Feb 26, 2013

At my Workplace we have a large Orcle 11g Database with 30 different tables for production control issues.I try to get a couple of different information from the database, so i started with SQL Query's, but for this problem i was not able to write an working query.

In this case i have 2 tables:

Table 1:
ID ;ORDER_NR ;DESCRIPTION ;CREATE_DATE
1 ;A500236 ;CLEAN HOUSE ;02/20/2012
2 ;A623555 ;REPAIR CAR ;01/10/2012
3 ;A866944 ;MAINTAIN EQUIPMENT ;02/11/2012

Table 2:
ID;ORDER_NR;WO_STEP;STEP_DATE;EMPLOYEE
1;A500236 ;A;02/21/2012;W0010
2;A500239 ;F;02/21/2012;W0010
3;A500239 ;S;02/22/2012;W0027

[code]....

And the result of my Query should look like this:
ORDER_NR;DESCRIPTION ;CREATE_DATE;A_STAT_AGE;R_STAT_AGE;U_STAT_AGE
A500236;CLEAN HOUSE ;02/20/2012 ;5 ;3 ;1
A623555;REPAIR CAR ;01/10/2012 ;42 ;39 ;38
A866944;MAINTAIN EQUIPMENT ;02/11/2012 ;15 ;4 ;3

The age of my query result should be calculated from the Create date of the Order.I want to know 2 things, one is how old was the Order when they reached that status A, R and U.The second this ist, how long did the order remain on the stat A,R and U (and if possible all other status also)It could happend that not each order reaches each status, so it ca go directly from A to you in this case i want display a wildcard in this row/column

View 2 Replies View Related

Using Nested-query To Achieve Info

Apr 19, 2010

For each keeper, show the number of cages cleaned by the keeper, show the average number of animals in the cages cleaned by the keeper and the total cost of the cages cleaned by the keeper."

The data table is shown on the picture.i75.photobucket. com /albums/i297/lovebipbo/SIT103.jpg.. I can learn and do some similar task myself

View 4 Replies View Related

How To Obtain User Session Info

Jan 26, 2007

How do I get my list of user session info? I thought there was a user_session_parameter view or something? Basically, I did an ALTER SESSION ... and want to verify it was set correctly.

View 8 Replies View Related

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

SQL & PL/SQL :: Member Procedure Insert With Subtype Info?

Mar 2, 2012

Im trying to generate a member procedure that allows the user to manual enter the required information via substitution variables. The manual INSERT INTO statements work but I cant seem to get it to work within a procedure.

Here is the code for the type, table andstandard insert:

CREATE TYPE toy_typ AS OBJECT
(toy_id NUMBER ( 5),
toy_name VARCHAR2 (20),
toy_cost NUMBER ( 4),

[code]...

the procedure complies but i get a warning error. When i also try to execute the code with either set values or substitutions i get either not enough or to many values error.

View 39 Replies View Related

Enterprise Manager :: How To Extract MGMT_JOB Info

Aug 2, 2010

I want to extract all OEM Job schedule information so that we can recreate the jobs on a different OEM should the need arise; e.g. Our network A OEM grid control system fails completely.We decide to connect all databases on network A to an OEM grid control system on Network B. Once all agents are switched over to network B we need to recreate and schedule all the OEM jobs that were running from the OEM on A to the new OEM.

Any sql that will get the job info from sysman.mgmt_jobs (and related tables) which I can run on a regular basis OR how to merge the mgmt_job.. tables from one oem into a second oem without corrupting the data already in the second oem ?

View 5 Replies View Related

SQL & PL/SQL :: How To Add A CHECK CONSTRAINT To Compare Info In 2 Tables

Jan 27, 2011

I need to alter a table to check that the data in a column is contained in a similar column in another table.

I have a STORES table and a STORE_REP table. Both tables have a column REP_ID.

I need to add a CHECK constraint into the STORES table to make sure that the info entered into its REP_ID column matches an entry in the STORE_REP table.

Both have a NUMBER(5) data type.

Will it make any difference if the REP_ID column in the STORE_REP table was originally created with a VARCHAR2(5) data type and was later converted to NUMBER(5), while the REP_ID column in the STORES table was created as NUMBER(5) when that table was created?

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

Retrieving Info From Database And List Out In HTML Table?

Feb 21, 2011

I am having a little trouble retrieving info from my mock database.

here are my definitions:
response.setHeader("pragma", "no-cache");
response.setHeader("Cache-control", "no-cache, no-store, must-revalidate");
response.setHeader("Expires", "01 Apr 1995 01:10:10 GMT");
response.addHeader("Cache-Control", "post-check=0, pre-check=0");

[code]...

and here is my select statement.
ds = (DataSource) context.lookup(dSource);
conn = ds.getConnection();
stmt = conn.prepareStatement("select add_date, campus, name, leader from TABLENAME where id = ? order by xbbo_add_date");
stmt.setString(1, colleagueID);
result = stmt.executeQuery();

***basically I am trying to retrieve this info from my db, and list it out in a HTML table. I am writing this on a JSP page. Also, where i want the retrieved data listed, I am using for ex: <%=name%>

View 5 Replies View Related

Server Administration :: Undo Info Of Insert Statement

Jun 24, 2012

If we insert a row in a database table then the new row stays at database buffer cache in SGA (until commit), right?. The target table is not affected (before commit). The new row is saved after commit.

I saw a concepts at Sybex oracle 10g oca book (Page 406) as follows:

" INSERT statements use little space in an undo segment; only the pointer to the new row is stored in the undo tablespace. To undo an INSERT statement, the pointer locates the new row and deletes it from the table if the transaction is rolled back. "

My question is If the row is not saved at table before commit, if we issue rollback then how oracle delete from table? I think the new row is deleted from database buffer cache in SGA.

View 2 Replies View Related

Reports & Discoverer :: Find Report Header Info

Jul 2, 2012

I need to customize an oracle report and I am not able to find the header section when I click on the header section in the paper layout. The header info is not on the main section either. But when I run the oracle conc prog the header seciton gets printed. I verified the rdf file if it was correct- it is.

View 4 Replies View Related

SQL & PL/SQL :: Using Minus Operator To Get Changed Data Info Between Two Tables

Feb 14, 2013

I am using the below query to show the difference of data between two tables using minus operator.

teh result is correct, but is there a way can it show with a flag with new rows and updated rows something like "N" for new row and "U" for updated row.

select CURRENT_STG_GLACCTS.TABLE_NAME,
CURRENT_STG_GLACCTS.ACTIVE,
CURRENT_STG_GLACCTS.BUSINESSUNITID,
CURRENT_STG_GLACCTS.COST_CENTER,
CURRENT_STG_GLACCTS.GLACCT,
[code].......

View 7 Replies View Related

SQL & PL/SQL :: Retrieve All Data From Table And Print Out Each Customer Info One By One

Nov 15, 2010

How similar is PL/SQL to SQL? I'm trying to retrieve all data from a customer table and print out each customer information one by one:

DECLARE
CURSOR all_customer IS
SELECT *
FROM customer;
BEGIN
DBMS_OUTPUT.PUT_LINE(all_customer);
END;

but I really don't know the correct syntax. The Powerpoint slides the professor gave is not useful at all.

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

Server Administration :: Record Audit Info About Sql Statement Run By User

Jul 19, 2012

I am trying to record audit info about sql statement run by user (only one audit entry per specific type of operation such as create table, or insert table). Such as if a user create three tables, but database record only one entry of create table type per session.

I am giving you all the statement I issued...

SQL> create user saimon identified by abc1;

User created.

SQL> grant connect, resource to saimon;

Grant succeeded.

SQL> audit table, insert table by saimon by session;

Audit succeeded.

SQL> show parameter audit

NAME TYPE VALUE
-------------------- ----------- -------------
audit_file_dest string /u01/app/oracle/admin/orcl/adum
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DBSQL>

[oracle@DBTEST ~]$ sqlplus saimon/abc1

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 19 21:45:09 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL>

SQL> create table TB1 (id number, name varchar2(20));

Table created.

SQL> create table TB3 (id number, name varchar2(20));

Table created.

SQL> select USERNAME, TERMINAL, OS_USERNAME, USERHOST, ACTION, action_name, OBJ_NAMe
2 from user_audit_trail;

USERNAME TERMINAL OS_USERNAM USERHOST ACTION ACTION_NAME OBJ_NAME
---------- ---------- ---------- ----------------- ----------------------- ----------
SAIMON pts/4 oracle DBTEST 1 CREATE TABLE TB3
SAIMON pts/4 oracle DBTEST 1 CREATE TABLE TB1
conn / as sysdba

Now my question is I have enabled statement auditing for session not by access. So only one audit entry should have been recorded for two table creation. Why database is recording every create statement?

SQL> show user
USER is "SYS"

SQL> SELECT audit_option, failure, success, user_name
2 FROM dba_stmt_audit_opts;

AUDIT_OPTION FAILURE SUCCESS USER_NAME
----------------------------------- ---------- ---------- ------------------------------
TABLE BY SESSION BY SESSION SAIMON
INSERT TABLE BY SESSION BY SESSION SAIMON

View 2 Replies View Related







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