2018-10-02 DB作業.sql

-- 6-5-1
insert into Product(pNo, pName, unitPrice, category)
	values('b10000', '總統之路', 300, 'Book' );
insert into Author(pNo, name)
	values('b10000', '陳水扁');
insert into Member(mid, pid, name)
	values('a00001', 'c1000000000', 'Mary');
insert into Browse(mid, pNo, browseTime)
	values('a00001', 'b10000', to_date('2003-04-05:09:00:08', 'YYYY-MM-DD:HH24:MI:SS'));

-- 6-5-2
update Member
	set name='Jenny Pai'
	where mid='a0910001';

-- 6-5-3
delete Browse(mid, pNo, browseTime)
	where mid='a00001' 
		and pNo='b10000' 
		and browseTime=to_date('2003-04-05:09:00:08', 'YYYY-MM-DD:HH24:MI:SS');



-- E3-5 A
select m.mName, m.place, r.tName
	from Participant p, Marathon m, Register r
	where p.pId=r.pId and r.mName=m.mName and p.name='馬英九';
	
-- E3-5 F
update Track t
	set description='已額滿'
 	where (mName, tName) in (
 		select r.mName as, r.tName
			from Register r, Track t
 			group by r.mName, r.tName
 			having count(pId)>=t.number
 	);