Library-Data-Management-System/table_creations_additional.sql at main · riship1095/Library-Data-Management-System · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
Select t.author,
regexp_substr(t.author,'[^,]+',1,1) part1,
regexp_substr(t.author,'[^,]+',1,2) part2,
regexp_substr(t.author,'[^,]+',1,3) part3,
regexp_substr(t.author,'[^,]+',1,4) part4,
regexp_substr(t.author,'[^,]+',1,5) part5
from project1_books_load t;

create table temp1(nam varchar2(100));

insert into temp1
Select regexp_substr(t.author,'[^,]+',1,1) part1
from project1_books_load t
where regexp_substr(t.author,'[^,]+',1,1) is not null;

insert into project1_authors
select authors_seq.nextval, nam from(
select unique nam from temp1
order by 1);

-------------------------------------------------------------------------------------------------------------------------------------


create table temp2(isbn10 varchar2(20),
                   name varchar2(100));

insert into temp2
select isbn10,
regexp_substr(t.author,'[^,]+',1,1) part1
from project1_books_load t
where regexp_substr(t.author,'[^,]+',1,1) is not null;

insert into project1_book_authors
select unique a.author_id, t.isbn10
from project1_authors a, temp2 t
where a.name = t.name
order by 1;

-------------------------------------------------------------------------------------------------------------------------------------------