DATABASE_7

DATABASE TEST7

BASIC OPERATIONS

CREATE INDEX index_name
	ON table_name(column_name)
	
DROP INDEX index_name

7-1

CREATE TABLE test7_01 AS
SELECT sid,name,birthday FROM pub.student

CREATE INDEX index_name
	ON test7_01(substr(name,1,1))

select * from 
(select sid,name,birthday,
(select count(*) from test7_01 where substr(name,1,1)=substr(t1.name,1,1)) samefirstname 
from pub.student_testindex t1)
where samefirstname=7

7-2

CREATE TABLE test7_02 AS
	SELECT sid,name,birthday FROM pub.student
	
Update test7_02 set 
	birthday=to_date('19881018','yyyymmdd') where
    substr(sid,12,1)='0'
    
CREATE INDEX birth_name ON
	test7_02(birthday,name)
    
select * from
(select sid,name,birthday,
 (select count(*) from test7_02 where name=t1.name and birthday=t1.birthday) samenamebirthday,
(select count(*) from test7_02 where birthday=t1.birthday) samebirthday
 from pub.student_testindex t1)  

7-3

CREATE VIEW test7_03 AS
select * from
(select sid,name,birthday,
(select count(*) from pub.student
where name like CONCAT(substr(t1.name,1,1),'%')
) samefirstname 
from pub.student_testindex t1) where samefirstname=7

7-4

CREATE VIEW test7_04 AS
select * from 
(select sid,name,birthday,
(select count(*) from pub.student 
where birthday <= LAST_DAY(t1.birthday) AND
 	birthday >= TRUNC(t1.birthday,'mm')
) sameyearmonth,
(select count(*) from pub.student 
where birthday >= TRUNC(t1.birthday,'yyyy') AND
 	birthday < TRUNC(ADD_MONTHS(t1.birthday,12),'yyyy')
) sameyear
 from pub.student_testindex t1) where sameyearmonth=35

7-5

CREATE VIEW test7_05 AS
select * from 
(select sid,name,birthday,
(select count(*) from pub.student 
where birthday=t1.birthday + 1
) nextbirthday
from pub.student_testindex t1) where nextbirthday=7