Skip to content

Instantly share code, notes, and snippets.

View niisar's full-sized avatar
🎯
Focusing

Mohammed Nisar Ansari niisar

🎯
Focusing
View GitHub Profile
@niisar
niisar / Lists objects in schema.sql
Created May 4, 2014 16:12
lists all objects being accessed in the schema
SET LINESIZE 255
SET VERIFY OFF
COLUMN object FORMAT A30
SELECT a.object,
a.type,
a.sid,
b.serial#,
b.username,
@niisar
niisar / ch7_1.sql
Created May 19, 2014 21:19
compute 4! using a recursive with clause
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
niisar / ch7_2.sql
Created May 19, 2014 21:24
spin thru family table starting at parent_key is null
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
niisar / sample_table_1.sql
Created May 19, 2014 21:26
sample table 1
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
niisar / Ch7_3.sql
Created May 19, 2014 21:30
spin thru company table starting at manager_id = 3. add the manager's name to the results.
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
niisar / Ch7_4.sql
Created May 19, 2014 21:33
spin thru the company table sorting breath first also in depth first.
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
niisar / Ch7_5.sql
Created May 19, 2014 21:34
determine if an employee is earning the same salary as his/her manager.
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
niisar / childstat table.sql
Created May 20, 2014 15:51
childstat table
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
niisar / Ch6_1.sql
Last active August 29, 2015 14:01
Ch6_1
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
niisar / Ch6_2.sql
Created May 20, 2014 15:57
with (allows database to treate query as inline view/temporary table
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