Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> SQL Question
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:
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;
Received on Tue Sep 12 2006 - 22:47:04 CDT
![]() |
![]() |