.mode column
.header on
-- select max(length(hobbies) - length(replace(hobbies,',','')))+1
-- from student;

-- select count(*) 
-- from student
-- where hobbies is not null and hobbies <> '';

-- select count(*) 
-- from student
-- where length(hobbies) - length(replace(hobbies,',','')) > 0;

-- select count(*) 
-- from student
-- where length(hobbies) - length(replace(hobbies,',','')) > 1;

-- -- Q1

-- select id, hobbies from student where instr(hobbies,',') = 0
-- union 
-- select id, substr(hobbies,1,instr(hobbies,',')-1) 
-- from student where instr(hobbies,',') > 0;

-- -- select id,
-- --        substr(hobbies,1,
-- --        case 
-- --           when instr(hobbies,',') = 0 then length(hobbies) + 1
-- --           else instr(hobbies,',')
-- --        end-1)
-- -- from student
-- -- where hobbies is not null and hobbies <> '';

-- -- Q2

-- select id, substr(hobbies,instr(hobbies,',')+1) from student
-- where length(hobbies) - length(replace(hobbies,',','')) = 1
-- union 
-- select id, substr(substr(hobbies,instr(hobbies,',')+1),1,
--            instr(substr(hobbies,instr(hobbies,',')+1),',')-1) as h
-- from student
-- where length(hobbies) - length(replace(hobbies,',','')) = 2;

-- -- Q3

-- select id,
--        substr(substr(hobbies,instr(hobbies,',')+1),
--        instr(substr(hobbies,instr(hobbies,',')+1),',')+1) as h
-- from student
-- where length(hobbies) - length(replace(hobbies,',','')) = 2;

create table student_hobby(student_id int, hobby text);

insert into student_hobby

select id, hobbies from student where instr(hobbies,',') = 0
union 
select id, substr(hobbies,1,instr(hobbies,',')-1) 
from student where instr(hobbies,',') > 0
union
select id, substr(hobbies,instr(hobbies,',')+1) from student
where length(hobbies) - length(replace(hobbies,',','')) = 1
union 
select id, substr(substr(hobbies,instr(hobbies,',')+1),1,
           instr(substr(hobbies,instr(hobbies,',')+1),',')-1) as h
from student
where length(hobbies) - length(replace(hobbies,',','')) = 2
union
select id,
       substr(substr(hobbies,instr(hobbies,',')+1),
       instr(substr(hobbies,instr(hobbies,',')+1),',')+1) as h
from student
where length(hobbies) - length(replace(hobbies,',','')) = 2;


create index student_hobby_index on student_hobby(hobby);
