niisar

SET LINESIZE 255
SET VERIFY OFF

COLUMN object FORMAT A30

SELECT a.object,
       a.type,
       a.sid,
       b.serial#,
       b.username,

niisar

WITH RSFC(ITERATION,RUNNING_FACTORIAL) AS
  ( SELECT NUM AS ITERATION, 1 AS RUNNING_FACTORIAL FROM NUMBERS WHERE NUM =1
  UNION ALL
  SELECT R.ITERATION +1,
    R.RUNNING_FACTORIAL * B.NUM
  FROM RSFC R
  INNER JOIN NUMBERS B
  ON (R.ITERATION+1) = B.NUM
  )
SELECT iteration,running_factorial FROM rsfc

niisar

WITH RSFC(CK,PK,LVL) AS
  ( SELECT CHILD_KEY,PARENT_KEY,0 AS LVL FROM FAMILY WHERE PARENT_KEY IS NULL
  UNION ALL
  SELECT CHILD_KEY,
    PARENT_KEY,
    LVL+1
  FROM RSFC R
  INNER JOIN FAMILY F
  ON R.CK = F.PARENT_KEY
  )

niisar

create table NUMBERS(
NUM integer
);
INSERT INTO NUMBERS VALUES (1);
INSERT INTO NUMBERS VALUES (2);
insert into NUMBERS values (3);
INSERT INTO NUMBERS VALUES (4);

create table FAMILY(
CHILD_KEY varchar(50),

niisar

WITH RSFC(EMPID,ENAME,MGRID,LVL) AS
  (SELECT EMPLOYEE_ID,
    EMPLOYEE_NAME,
    MANAGER_ID,
    0 AS LVL
  FROM COMPANY
  WHERE MANAGER_ID = 3
  UNION ALL
  SELECT EMPLOYEE_ID,
    EMPLOYEE_NAME,

niisar

WITH RSFC(CK,PK,LVL,HIER) AS
  (SELECT EMPLOYEE_ID,
    MANAGER_ID,
    0             AS LVL,
    EMPLOYEE_NAME AS HIER
  FROM COMPANY
  WHERE MANAGER_ID IS NULL
  UNION ALL
  SELECT EMPLOYEE_ID,
    MANAGER_ID,

niisar

WITH RSFC(EMPID,ENAME,MGRID,LVL,SAL) AS
  (SELECT EMPLOYEE_ID,
    EMPLOYEE_NAME,
    MANAGER_ID,
    0 AS LVL,
    SALARY
  FROM COMPANY
  WHERE MANAGER_ID IS NULL
  UNION ALL
  SELECT EMPLOYEE_ID,

niisar

CREATE TABLE CHILDSTAT" 
   (	"FIRSTNAME" VARCHAR2(50 BYTE), 
	"GENDER" VARCHAR2(1 BYTE), 
	"BIRTHDATE" DATE, 
	"HEIGHT" NUMBER(*,0), 
	"WEIGHT" NUMBER(*,0)
   ) ;

Insert into CHILDSTAT (FIRSTNAME,GENDER,BIRTHDATE,HEIGHT,WEIGHT) values ('lauren','f',to_date('10-JUN-00','DD-MON-RR'),54,876);
Insert into CHILDSTAT (FIRSTNAME,GENDER,BIRTHDATE,HEIGHT,WEIGHT) values ('rosemary','f',to_date('08-MAY-00','DD-MON-RR'),35,123);

niisar

WITH WT_M AS
  (SELECT FIRSTNAME,
    WEIGHT,
    ROW_NUMBER() over (order by WEIGHT DESC) AS WT_RANK_MALE
  FROM CHILDSTAT
  WHERE GENDER = 'm'
  ),
  WT_F AS
  (SELECT FIRSTNAME,
    WEIGHT,

niisar

WITH WT_AVG AS
  (SELECT AVG(WEIGHT) AS AVG_WT FROM CHILDSTAT
  )
SELECT a.FIRSTNAME,
  a.GENDER,
  a.HEIGHT,
  a.WEIGHT
FROM CHILDSTAT a
WHERE a.GENDER = 'm'
AND a.HEIGHT   > 50