/* - and # may not be a part of an identifier, date is not a valid id, since its a datatype.
note there is only a space seperator between the identifier and its datatype sepcification, no : */

/*1*/
create table person (driverid varchar(20) primary key,name varchar(20));

/*2*/
alter table person add address varchar(20);


/*3*/
create table car(regno varchar(20) primary key, model varchar(20), year int);
/* date is not a primary key, as shown in the question */
create table accident(reportnum int primary key, dat date,location varchar(20));
create table owns(driverid varchar(20),regno varchar(20), primary key(driverid,regno));
create table participated(driverid varchar(20),regno varchar(20), reportnum int,damageamt int,
primary key(driverid,regno,reportnum),
foreign key(driverid,regno) references owns(driverid,regno)
);




/* do not use the word constraint before foreign key, do not use person.driverid use person(driverid) 

use as : tablename(fieldname) here, but use tablename.fieldname in select queries etc.
*/
/*4*/
alter table owns add foreign key(driverid) references person(driverid);
alter table owns add foreign key(regno) references car(regno);
alter table participated add foreign key (driverid) references person(driverid);
alter table participated add foreign key (regno) references car(regno);
alter table participated add foreign key (reportnum) references accident(reportnum);


/*5*/
/* values must be in quotes, ' only, not " */
insert into person values('1','uknowhu','basweshwaranagar');
insert into person values('2','abhijit','rmv');
insert into person values('3','dilip','hebbal');
insert into person values('4','preetesh','navarang');
insert into person values('5','anoop','msr nagar');


insert into car values('ka04m123', 'maruti800', 2000);
insert into car values('ka04m124', 'maruti800', 2000);
insert into car values('ka04m125', 'zen', 2000);
insert into car values('ka04m126', 'maruti800', 2001);
insert into car values('ka04m127', 'maruti1000', 2002);
insert into car values('ka04m128', 'maruti1000', 2002);



/* note the (only) proper (default) date format */
insert into accident values(11,'01-feb-05','mathikere');
insert into accident values(12,'01-feb-02','xyzplace');
insert into accident values(13,'02-feb-02','xyzplace');
insert into accident values(15,'03-feb-02','ooooplace');
insert into accident values(14,'06-dec-04','mg');

insert into owns values('1','ka04m123');
insert into owns values('1','ka04m125');
insert into owns values('2','ka04m124');
insert into owns values('3','ka04m126');
insert into owns values('4','ka04m127');
insert into owns values('5','ka04m128');

insert into participated values('1','ka04m123',11,3124);
insert into participated values('1','ka04m123',14,100000);
insert into participated values('2','ka04m124',12,1000);
insert into participated values('3','ka04m126',12,1000);
insert into participated values('4','ka04m127',15,1000);
insert into participated values('5','ka04m128',11,1000);
commit;

/*6*/
select * from tab;

/*7*/
desc person;

/*8*/
select * from person;

/*9*/
select * from accident where reportnum=1;
/*10*/
select * from accident where reportnum>2;


/*11*/
/* note : 2 and 10 are inclusive */
select * from person where driverid between 2 and 10;

/*12*/
/* in('john' or 'anthony'); is not valid in oracle 8 */
/* select * from person where name like('john' or 'anthony'); */
select * from person where name like 'john' or name like 'anthony';

/*13*/
/* extract(year from dat) is not valid in oracle8 */
select count(*) from person where driverid in (select driverid from participated,accident where participated.reportnum=accident.reportnum  and accident.dat>='1-jan-2004' and accident.dat<='31-dec-2004');

/*14*/
update car set year=2004 where model='maruti800';

/*15*/
delete from car where regno='5';

/*16*/
delete from accident where reportnum=4;

/*17*/
select count(distinct regno) from participated;

/*18*/
as 13

/* don't think this is what is expected */
/*19*/
select name,model from person,car,owns where owns.driverid=person.driverid and owns.regno=car.regno order by name;

/*20*/
/* in the que : must be reportnum instead of regno */
select location from accident where reportnum=5;

/*21*/
select distinct regno from participated;

/*22*/
select car.regno,model from car,participated where car.regno = participated.regno and damageamt in (select max(damageamt) from participated);

/*23*/
select name,count(*) from owns,person where owns.driverid=person.driverid group by name;

/*24*/
select person.driverid,count(*) from person,participated where person.driverid=participated.driverid group by person.driverid; 

/*25*/
select count(*) from car,participated where car.model='maruti800' and car.regno=participated.regno;



/* clear the database of our tables */
drop table participated;
drop table accident;
drop table owns;
drop table car;
drop table person;


