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
ADVERTISEMENT
SQL & 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 :: 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 View RelatedSQL & 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
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.
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 :: 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]......
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.
Server Utilities :: Append Tables Content To Existing Tables?
Nov 9, 2010problem on oracle 11gR2 where i have to import data from a source database to an existing table without truncate or drop the target table in the target database.
we have found something called table_exist_action=append in impdp.
Maintain Large Tables / Cleanup Data From Our Tables
May 18, 2011I have to cleanup data from our tables (Production Environment) that contain millions of rows. The question is apart from the solution of the partitioned tables what alternative recommended solution suggests Oracle?
To delete these tables by using a cursor PL/SQL block or to import all the database and in the tables that we want to remove the old rows to use the QUERY option of the data pump utility.
I have used both ways and i have to admit that datapump solution is much much faster than the deletion that suffers from I/O disk.The question again is which method from these two is more reliable and less risky for the health of the database.
Implementation Where Data From DB2 Tables Are Moved To Oracle Tables
Sep 3, 2012I came across an implementation where data from DB2 tables are moved to Oracle tables, for BI solutioning, using some oracle procedures called from MS SQL DTS packages which are scheduled jobs.Just being curious, can this be done using OWB or ODI rather than the above detour. I suppose there are some changes being done in those procedures before the data is being loaded into Oracle tables, can't this be done using OWB/ODI? Can it be scheduled too as jobs using OWB/ODI?
View 1 Replies View RelatedSQL & PL/SQL :: Audit Tables For Most Of Production Tables
May 29, 2013In our schema we have corresponding audit tables for most of the production tables
Ex Table name Audit Table
EMP EMP_AU
DEPT DEPT_AU
Audit tables will have all the columns of production table along with audit columns AUDIT_DATE , AUDIT_OPERATION There are few production tables which are not having audit tables.I need to write a script to identify
1) Production tables where corresponding audit table is missing
2) Where there is column difference (In case any column missing in audit table) between Production table and Audit table
SQL & PL/SQL :: Differences Among Records Tables And Tables Of Records
Sep 16, 2010In Oracle database pl/sql 11.2: describe the differences between :
records,
tables, and
tables of records.
SQL & PL/SQL :: Same Key For Few Tables
Aug 25, 2011I've got table tab1 with sequence id column.
Also I've got tables tab2 and tab3 with indicatorid column in each table.
My need is to prevent inserting values into indicatorid column in tables tab2 and tab3 which are absent in id column in table tab1.
For example, at this moment id column in table tab1 has two values, 1 and 2. I can insert value 3 into indicatorid column in tables tab2 and tab3. I need to prevent from inserting value 3 into indicatorid column in tables tab2 and tab3 while it is absent in id column of tab1 table.
How do I perform it?
Calling Tables From PL / SQL
Jan 22, 2013I have the Table name Location and synonym named Location in my DB. I am trying to create the proc where I am tryting to call the table. But its not working.
Example:
CREATE PROCEDURE TESTPROC
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('testing');
select count(*) from LOCATION;
END;
Compile error:
Error(5,22): PL/SQL: ORA-00942: table or view does not exist
Joining Two Tables
May 19, 2011can we link a table that contains a foreign key with a sequence table? i have a table that has a sequence bookid and i want to link this table to another table that contains a foreign key of bookid.can i link this two table and how?
View 1 Replies View RelatedShrink Tables - HWM?
Aug 22, 2012if the command is successful:>alter table my_table shrink;The segment will be defragmented and the High Water Mark will be moved.But what is the importance of the HWM?
Whats the difference between commands?
>alter table my_table shrink; -- move HWM
>alter table my_table shrink compact; -- not move HWV
SQL & PL/SQL :: Tables In Different Database?
Nov 19, 2010I have one table in a particular database and will have to create another table in a different database. I will have to join these two tables from two different DB's.
How should I go about doing this? Is this a good practice? or is it always better to use a single DB?
What are the disadvantages of joining two tables from two different DB's?
SQL & PL/SQL :: Migration Of Three Tables
Aug 29, 2013Data migration for three tables. I have three table which are
1.npi_p_mig contain four fields (pr_id,mi_id,qty,sl_dt,fac_code)
2.np_detail(pr_id,mil_id,qty,sl_dt,facility)
3.np_ref_tab(facility,fac_code),
I need to migrated the data from based on two tables (np_detail,np_ref_tab) to new table npi_P_mig(pr_id,mi_id,qty,sl_dt,fac_code) table. i need sql script to migrate above two table to new table (npi_P_mig) .
SQL & PL/SQL :: How To Sum Over Multiple Tables
Feb 21, 2010I'm trying to do a sum over 2 different tables but can't get it to work...This is the idea:I have a table A with client ID, time-id (per day), purchase amount and segment code.
In another table (let call it B) I have a lot of client ID's and also their purchase amount, time-id and segment code. I want to sum the purchase amount for every client from table A and B for clients with certain segment code from table B.
This is what I have now:
select client_id, purchase_amountA+ purchase_amountB from tableA, tableB where
A.client_id = B.client_id
and time_id between 20090101 and 20091001
and B.segment_code = 'A'
This does the job, but it selects only client_id's which are in both tables. I want to select all client_id from table B with segment_code 'A' and add the purchase_amount from table A to their purchase amount from table B, at least, if they have any purchase amount in table A.
SQL & PL/SQL :: Join On Two Tables
Jul 29, 2013I have 2 tables SEC_MASTER_HISTA and SEC_MASTER_HISTB.
Now, I need to compare the data of the two tables column-wise.
Ideally the 2 tables should have the same security_alias values but in my case they do not as the two tables belong to 2 diff client models. There is however a main SECURITY_MASTERA and SECURITY_MASTERB tables which have the security_alias recorded and a primary_asset_id column value which can act as a link between SEC_MASTER_HISTA and SEC_MASTER_HISTB. But, I have not been able to figure out the exact query which will be ideal.
Attached are the table structures and the data it contains.
Note: I need to compare the Coupon and Freq column values of SEC_MASTER_HISTA and SEC_MASTER_HISTB.
SQL & PL/SQL :: Multiple Tables
Sep 30, 2010Video Rental Shop
Each customer has a video card , When Customer rent a CD , Shopkeeper register an issue date and a Return Date . If customer return CD after Return Date Then There will be a fine of 2 Dollor .
After every 6 Months The shop Keeper review each customer Account , and Send Gifts to those customer whose Total Amount is More than 50 Dollar .and also send letters to those whose Fines Are More than 20 Dollor .
Now I am unable to understand that how many table i need to create for this .
What i have created so far is given below ,
When Customer Rent a CD then Shopkeeper will submit Following Information .
Customer_id 101
Issue DateDATE
Expected_return_dateDATE
Original_return_date-
Fine -
Total_Amount -
And at the time of return , he will Put these information .
Customer_id 101
Issue DateDATE
Expected_return_dateDATE
Original_return_date DATE
Fine 2
Total_Amount5
But Do i need to create another table for each customer also ? That will store customer total amount , total Fines ,and shopkeeper will view it after every six months. Which type oo table i need to create ?
SQL & PL/SQL :: Joining Two Tables
Apr 26, 2013I have a table (promo_custom) with following colimns (id, id_promo, button_promo, button_submit, css, user, date) and other table (promo_buttons) with following columns (id, id_design, name, url, username, date) .
Columns promo_custom.button_promo and promo_custom.button_submit are foreign keys (references promo_buttons.id).
I want to create a view showing table promo_custom but instead of columns button_promo and button_submit I would like to show the column url of them.
SQL & PL/SQL :: Relation Between Two Tables?
Dec 28, 2010oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"
I have a main table : CLAIMS_MAIN, in which claim_id is a primary key I have another table : CLAIMS_TRANS , in which claim_id is not a unique key, I mean, there can be more than one records with the same claim_id.. but the column transaction_id is unique, but transaction_id column doesnt exist in the main claims table.
It is required to join both these tables on claim_id...
SQL & PL/SQL :: Getting Result From Three Tables
Sep 24, 2012CREATE TABLE FLIGHTS(
FLIGHT_NUM NUMBER PRIMARY KEY
)
[Code]...
how to get the name of all passengers who have bookings in all flight_num?
o/p is 555
SQL & PL/SQL :: How To Add Tables Users
Jun 3, 2011I have 4 tables as follows.
1.Deploybase(DeployId)
2.LicenseHistory(DeployId,UserId)
3.Users(UserId,User_Detail_id)
4.UserDetails(User_Detail_id)
Tables are joined using below joins.
Deploybase.DeployId=LicenseHistory.DeployId
LicenseHistory.User_id=Users.User_id
Users.User_Detail_id=UserDetails.User_Detail_id
When I join LicenseHistory,Users,UserDetails, all records are unique.
But the issues is: LicenseHistory table has duplicate records for DeployId column which is used to join table Deploybase. As a result when I join it to Deploybase, it gives me repeating entries which I don't want
I want Comments from LicenseHistory,Login from Users and User_name from UserDetails. There are multiple columns from 1st table i.e. Deploybase which I need.After reading on Exists operator, I made following query.
Select DeployId,comment from (select comments as comment,deploy_id as DeployId from LicenseHistory slh where exists (select deploy_id from symdb.symdb_deploybase sdb) and slh.comments is not null)
Above query gives me results from LicenseHistory table only. But I don't know how to add tables Users,UserDetails
SQL & PL/SQL :: How To Compare The Four Tables
Jan 5, 2013I Have Four Tables
1) Sal_master
structure is voc_no varchar2(7),voc_date date
2) sal_detail
structure is voc_no varchar2(7),item_code varchar2(10),quantity number(10,2)
3). delivery_master
structure is voc_no varchar2(7),voc_date date;
4) delivery_detail
structure is voc_no varchar2(7),item_code varchar2(10),quantity number(10,2)
I want to compare these four tables i have insert 10 rows in sal_master and sal_detail tables and 5 transaction in delivery tables how to compares 10 records of sal_master,detail with delivery_master and detail if not exist in delivery_master and detail tables then display only sal_master,detail records for example
Voc_no Sale Qty Deliver Qty Remaining Qty
S000075 10 5 5 if data not found from delivery master and detail then answer must be
S000075 10 0 10
SQL & PL/SQL :: Field From Two Tables?
Sep 10, 2012I have two master tables , one is supplier master and the other customer, the requirement is , i will pass a code as IN parameter and the result it should bring the appropriate name of that code , for example if i pass a code which exists in supplier master , it should get me the supplier name for that code , if i pass code which exists in customer ,it should bring customer name.
CREATE TABLE OM_CUST (CUST_CODE VARCHAR2(30),CUST_NAME VARCHAR2(240));
INSERT INTO OM_CUST VALUES ('1001','CUST1');
INSERT INTO OM_CUST VALUES ('1002','CUST2');
CREATE TABLE OM_SUPP (SUPP_CODE VARCHAR2(30),SUPP_NAME VARCHAR2(240));
INSERT INTO OM_SUPP VALUES ('2001','SUPP1');
INSERT INTO OM_SUPP VALUES ('2002','SUPP2');
SQL & PL/SQL :: Union Of Two Tables?
Jul 1, 2010how can i get a union of two pl/sql tables. ??
View 2 Replies View RelatedSQL & PL/SQL :: DESC All Tables
Oct 22, 2010I am a newbie to Oracle and trying to write something to basically loop through all tables and do a DESC for each table,
I have the following code
set serveroutput on
begin
FOR cursor1 IN (SELECT * FROM ALL_TABLES where OWNER='ABC' ORDER BY TABLE_NAME)
LOOP
DBMS_OUTPUT.PUT_LINE(cursor1.TABLE_NAME);
FOR cursor2 IN (select * from cols where TABLE_NAME=cursor1.TABLE_NAME ORDER BY COLUMN_NAME)
LOOP
DBMS_OUTPUT.PUT_LINE(cursor2.COLUMN_NAME);
END LOOP;
END LOOP;
end;
Although the above code works, I would like to have the DESC TABLE_NAME instead of just displaying the column name so I can see the properties of the column.
I couldn't get the DESC part to work