Session 03-tabibitosan method

View All Public Scripts Login to Run Script

Script Name Session 03-tabibitosan method
Visibility Unlisted - anyone with the share link can access
Description Ranking database using tabibitosan method in oracle live sql-Anand prabhakar
Area SQL Analytics
Contributor anandprabhakar0507@gmail.com
Created Tuesday October 16, 2018

Statement 1
```
drop table LAB_SAMPLES
```
```
ORA-00942: table or view does not exist 
```
More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 2

drop sequence LAB_SAMPLES_SEQ

ORA-02289: sequence does not exist

More Details: https://docs.oracle.com/error-help/db/ora-02289

Statement 3

create sequence LAB_SAMPLES_SEQ

Sequence created.

Statement 4

create table LAB_SAMPLES
 ( sample_id          int default LAB_SAMPLES_SEQ.NEXTVAL,
   date_taken  date
 )

Table created.

Statement 5

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-01')

1 row(s) inserted.

Statement 6

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-02')

1 row(s) inserted.

Statement 7

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-03')

1 row(s) inserted.

Statement 8

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-04')

1 row(s) inserted.

Statement 9

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-07')

1 row(s) inserted.

Statement 10

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-08')

1 row(s) inserted.

Statement 11

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-09')

1 row(s) inserted.

Statement 12

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-10')

1 row(s) inserted.

Statement 13

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-14')

1 row(s) inserted.

Statement 14

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-15')

1 row(s) inserted.

Statement 15

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-16')

1 row(s) inserted.

Statement 16

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-19')

1 row(s) inserted.

Statement 17

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-20')

1 row(s) inserted.

Statement 18

select * from LAB_SAMPLES order by 2


SAMPLE_ID DATE_TAKEN
1 01-DEC-15
2 02-DEC-15
3 03-DEC-15
4 04-DEC-15
5 07-DEC-15
6 08-DEC-15
7 09-DEC-15
8 10-DEC-15
9 14-DEC-15
10 15-DEC-15
11 16-DEC-15
12 19-DEC-15
13 20-DEC-15

13 rows selected.

SAMPLE_ID	DATE_TAKEN
1	01-DEC-15
2	02-DEC-15
3	03-DEC-15
4	04-DEC-15
5	07-DEC-15
6	08-DEC-15
7	09-DEC-15
8	10-DEC-15
9	14-DEC-15
10	15-DEC-15
11	16-DEC-15
12	19-DEC-15
13	20-DEC-15

Statement 19

select date_taken,
       row_number() over(order by date_taken) as rn
       from LAB_SAMPLES
order by 1


DATE_TAKEN RN
01-DEC-15 1
02-DEC-15 2
03-DEC-15 3
04-DEC-15 4
07-DEC-15 5
08-DEC-15 6
09-DEC-15 7
10-DEC-15 8
14-DEC-15 9
15-DEC-15 10
16-DEC-15 11
19-DEC-15 12
20-DEC-15 13

13 rows selected.

DATE_TAKEN	RN
01-DEC-15	1
02-DEC-15	2
03-DEC-15	3
04-DEC-15	4
07-DEC-15	5
08-DEC-15	6
09-DEC-15	7
10-DEC-15	8
14-DEC-15	9
15-DEC-15	10
16-DEC-15	11
19-DEC-15	12
20-DEC-15	13

Statement 20

select date_taken,
       date_taken-row_number() over(order by date_taken) as delta
       from LAB_SAMPLES
order by 1


DATE_TAKEN DELTA
01-DEC-15 30-NOV-15
02-DEC-15 30-NOV-15
03-DEC-15 30-NOV-15
04-DEC-15 30-NOV-15
07-DEC-15 02-DEC-15
08-DEC-15 02-DEC-15
09-DEC-15 02-DEC-15
10-DEC-15 02-DEC-15
14-DEC-15 05-DEC-15
15-DEC-15 05-DEC-15
16-DEC-15 05-DEC-15
19-DEC-15 07-DEC-15
20-DEC-15 07-DEC-15

13 rows selected.

DATE_TAKEN	DELTA
01-DEC-15	30-NOV-15
02-DEC-15	30-NOV-15
03-DEC-15	30-NOV-15
04-DEC-15	30-NOV-15
07-DEC-15	02-DEC-15
08-DEC-15	02-DEC-15
09-DEC-15	02-DEC-15
10-DEC-15	02-DEC-15
14-DEC-15	05-DEC-15
15-DEC-15	05-DEC-15
16-DEC-15	05-DEC-15
19-DEC-15	07-DEC-15
20-DEC-15	07-DEC-15

Statement 21

select min(date_taken) date_from,
       max(date_taken) date_to,
       count(*) num_samples
from (
  select date_taken,
         date_taken-row_number() over(order by date_taken) as delta
         from LAB_SAMPLES
     )
group by delta
order by 1


DATE_FROM DATE_TO NUM_SAMPLES
01-DEC-15 04-DEC-15 4
07-DEC-15 10-DEC-15 4
14-DEC-15 16-DEC-15 3
19-DEC-15 20-DEC-15 2

4 rows selected.

Statement 22
```
drop table LAB_SAMPLES
```
```
Table dropped.
```

DATE_FROM	DATE_TO	NUM_SAMPLES
01-DEC-15	04-DEC-15	4
07-DEC-15	10-DEC-15	4
14-DEC-15	16-DEC-15	3
19-DEC-15	20-DEC-15	2

Statement 23

drop sequence LAB_SAMPLES_SEQ

Sequence dropped.

Statement 24

create sequence LAB_SAMPLES_SEQ

Sequence created.

Statement 25

create table LAB_SAMPLES
 ( sample_id          int default LAB_SAMPLES_SEQ.NEXTVAL,
   date_taken  date
 )

Table created.

Statement 26

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-01')

1 row(s) inserted.

Statement 27

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-02')

1 row(s) inserted.

Statement 28

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-03')

1 row(s) inserted.

Statement 29

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-04')

1 row(s) inserted.

Statement 30

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-07')

1 row(s) inserted.

Statement 31

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-08')

1 row(s) inserted.

Statement 32

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-09')

1 row(s) inserted.

Statement 33

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-10')

1 row(s) inserted.

Statement 34

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-14')

1 row(s) inserted.

Statement 35

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-15')

1 row(s) inserted.

Statement 36

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-16')

1 row(s) inserted.

Statement 37

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-19')

1 row(s) inserted.

Statement 38

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-20')

1 row(s) inserted.

Statement 39

select * from LAB_SAMPLES order by 2


SAMPLE_ID DATE_TAKEN
1 01-DEC-15
2 02-DEC-15
3 03-DEC-15
4 04-DEC-15
5 07-DEC-15
6 08-DEC-15
7 09-DEC-15
8 10-DEC-15
9 14-DEC-15
10 15-DEC-15
11 16-DEC-15
12 19-DEC-15
13 20-DEC-15

13 rows selected.

SAMPLE_ID	DATE_TAKEN
1	01-DEC-15
2	02-DEC-15
3	03-DEC-15
4	04-DEC-15
5	07-DEC-15
6	08-DEC-15
7	09-DEC-15
8	10-DEC-15
9	14-DEC-15
10	15-DEC-15
11	16-DEC-15
12	19-DEC-15
13	20-DEC-15

Statement 40

select date_taken,
       row_number() over(order by date_taken) as rn
       from LAB_SAMPLES
order by 1


DATE_TAKEN RN
01-DEC-15 1
02-DEC-15 2
03-DEC-15 3
04-DEC-15 4
07-DEC-15 5
08-DEC-15 6
09-DEC-15 7
10-DEC-15 8
14-DEC-15 9
15-DEC-15 10
16-DEC-15 11
19-DEC-15 12
20-DEC-15 13

13 rows selected.

DATE_TAKEN	RN
01-DEC-15	1
02-DEC-15	2
03-DEC-15	3
04-DEC-15	4
07-DEC-15	5
08-DEC-15	6
09-DEC-15	7
10-DEC-15	8
14-DEC-15	9
15-DEC-15	10
16-DEC-15	11
19-DEC-15	12
20-DEC-15	13

Statement 41

select date_taken,
       date_taken-row_number() over(order by date_taken) as delta
       from LAB_SAMPLES
order by 1


DATE_TAKEN DELTA
01-DEC-15 30-NOV-15
02-DEC-15 30-NOV-15
03-DEC-15 30-NOV-15
04-DEC-15 30-NOV-15
07-DEC-15 02-DEC-15
08-DEC-15 02-DEC-15
09-DEC-15 02-DEC-15
10-DEC-15 02-DEC-15
14-DEC-15 05-DEC-15
15-DEC-15 05-DEC-15
16-DEC-15 05-DEC-15
19-DEC-15 07-DEC-15
20-DEC-15 07-DEC-15

13 rows selected.

DATE_TAKEN	DELTA
01-DEC-15	30-NOV-15
02-DEC-15	30-NOV-15
03-DEC-15	30-NOV-15
04-DEC-15	30-NOV-15
07-DEC-15	02-DEC-15
08-DEC-15	02-DEC-15
09-DEC-15	02-DEC-15
10-DEC-15	02-DEC-15
14-DEC-15	05-DEC-15
15-DEC-15	05-DEC-15
16-DEC-15	05-DEC-15
19-DEC-15	07-DEC-15
20-DEC-15	07-DEC-15

Statement 42

select min(date_taken) date_from,
       max(date_taken) date_to,
       count(*) num_samples
from (
  select date_taken,
         date_taken-row_number() over(order by date_taken) as delta
         from LAB_SAMPLES
     )
group by delta
order by 1


DATE_FROM DATE_TO NUM_SAMPLES
01-DEC-15 04-DEC-15 4
07-DEC-15 10-DEC-15 4
14-DEC-15 16-DEC-15 3
19-DEC-15 20-DEC-15 2

4 rows selected.

DATE_FROM	DATE_TO	NUM_SAMPLES
01-DEC-15	04-DEC-15	4
07-DEC-15	10-DEC-15	4
14-DEC-15	16-DEC-15	3
19-DEC-15	20-DEC-15	2

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database