Re: SQL Question

Hope you get your homework answered.

On 12 Sep 2006, anil.gothal_at_gmail.com wrote:
> Please create following table into schema, I am trying to develop SQL
> query to retrieve following results
>
> ORG HOME T_QTY E_QTY
> ---- ---- ----- -----
> O14 O1 15 109
> O23 O2 27 200
>
> Following the logic involved:
>
> 1. Org and home has parent-child relationship 2. Find Org having max
> of t_qty and then by e_qty for each HOME. 2.1. If two records have
> same t_qty then choose the one with max of e_qty 3. Sql performed per
> HOME; infact where should have home as NOT NULL;
>
>
>
> CREATE TABLE TBL01
> (
> ORG VARCHAR2(10 BYTE) NULL,
> HOME VARCHAR2(10 BYTE) NULL,
> T_QTY INTEGER NULL,
> E_QTY INTEGER NULL
> );
>
>
> CREATE UNIQUE INDEX TBL01_PK ON TBL01
> (ORG)
> LOGGING
> NOPARALLEL;
>
>
> ALTER TABLE TBL01 ADD (
> CONSTRAINT TBL01_PK
> PRIMARY KEY
> (ORG));
>
>
> ALTER TABLE TBL01 ADD (
> CONSTRAINT TBL01_HOME_FK
> FOREIGN KEY (HOME)
> REFERENCES TBL01 (ORG));
>
>
> INSERT INTO TBL01 ( ORG, HOME, T_QTY, E_QTY ) VALUES (
> 'O1', NULL, 100, 3000);
> INSERT INTO TBL01 ( ORG, HOME, T_QTY, E_QTY ) VALUES (
> 'O10', 'O1', 10, 100);
> INSERT INTO TBL01 ( ORG, HOME, T_QTY, E_QTY ) VALUES (
> 'O11', 'O1', 11, 90);
> INSERT INTO TBL01 ( ORG, HOME, T_QTY, E_QTY ) VALUES (
> 'O12', 'O1', 12, 101);
> INSERT INTO TBL01 ( ORG, HOME, T_QTY, E_QTY ) VALUES (
> 'O13', 'O1', 12, 103);
> INSERT INTO TBL01 ( ORG, HOME, T_QTY, E_QTY ) VALUES (
> 'O14', 'O1', 15, 109);
> INSERT INTO TBL01 ( ORG, HOME, T_QTY, E_QTY ) VALUES (
> 'O15', 'O1', 12, 109);
> INSERT INTO TBL01 ( ORG, HOME, T_QTY, E_QTY ) VALUES (
> 'O2', NULL, 132, 1009);
> INSERT INTO TBL01 ( ORG, HOME, T_QTY, E_QTY ) VALUES (
> 'O20', 'O2', 11, 89);
> INSERT INTO TBL01 ( ORG, HOME, T_QTY, E_QTY ) VALUES (
> 'O21', 'O2', 12, 109);
> INSERT INTO TBL01 ( ORG, HOME, T_QTY, E_QTY ) VALUES (
> 'O22', 'O2', 24, 209);
> INSERT INTO TBL01 ( ORG, HOME, T_QTY, E_QTY ) VALUES (
> 'O23', 'O2', 27, 200);
> COMMIT;

-- 
Galen Boyer