REM Creation, insertion, interrogation 

create type Vacance as object
      (datdeb Date,
       datfin Date,
       lieu VARCHAR(15)
)
/

create type lvacances as table of Vacance
/

create type Person as object
      (nom  VARCHAR(15),
      prenom  VARCHAR(15),
      datenais Date,
      vacances lvacances)
/

create table LesPersons of Person
nested table vacances store as lv;

insert into LesPersons VALUES('Jospin','Lionel', '12-JUL-1937',lvacances());

insert into THE(select vacances from LesPersons where nom='Jospin') VALUES('13-MAY-2002','04-SEP-2002','Ile de Re');

select * from THE(select vacances from LesPersons where nom='Jospin');


REM Interrogation sous Oracle 8

create type Parti as object
     (nom VARCHAR(15),
      adresse VARCHAR(30))
/

create type Politicien as object
     (nom VARCHAR(15),
      nocarteparti NUMBER,
      membre REF Parti,
      dateint DATE,
      superieurdirect REF Politicien,
      fonction VARCHAR(15)
)
/

create table LesPartis of Parti;
create table LesPoliticiens of Politicien;
create table LesVotes
      (votant VARCHAR(15),
       pour REF Politicien,
       datevote DATE);

insert into LesPartis VALUES('Communiste','bidon');
insert into LesPartis VALUES('UMP','blabla');
insert into LesPartis VALUES('Socialiste','Solferino');
insert into LesPoliticiens SELECT 'Jospin',1,REF(m),'01-JAN-1957',NULL,'retraite' from LesPartis m where m.nom='Socialiste';
insert into LesPoliticiens SELECT 'Hollande',2,REF(m),'01-JAN-1957',REF(p),'1er sec' from LesPartis m, LesPoliticiens p where m.nom='Socialiste' and p.nom='Jospin';
insert into LesPoliticiens SELECT 'Hue',3,REF(m),'01-JAN-1957',NULL,'1er sec' from LesPartis m where m.nom='Communiste';
insert into LesPoliticiens SELECT 'Chirac',5,REF(m),'01-JAN-1952',NULL,'president' from LesPartis m where m.nom='UMP';

insert into lesvotes select 'moi',REF(p),'28-APR-2002' from Lespoliticiens p where p.nom='Jospin';

insert into lesvotes select 'al',REF(p),'28-APR-2002' from Lespoliticiens p where p.nom='Jospin';

insert into lesvotes select 'ig',REF(p),'28-APR-2002' from Lespoliticiens p where p.nom='Hue';

insert into lesvotes select 'lg',REF(p),'28-APR-2002' from Lespoliticiens p where p.nom='Hollande'
;

insert into lesvotes select 'moi',REF(p),'30-APR-2002' from Lespoliticiens p where p.nom='Jospin';


select nom, adresse from LesPartis;
NOM             ADRESSE
--------------- ------------------------------
Communiste      bidon
UMP             blabla
Socialiste      Solferino

3 rows selected.

select p.nom, p.membre.nom, p.dateint, p.superieurdirect.nom from LesPoliticiens p;
NOM             MEMBRE.NOM      DATEINT   SUPERIEURDIRECT
--------------- --------------- --------- ---------------
Jospin          Socialiste      01-JAN-57
Hollande        Socialiste      01-JAN-57 Jospin
Hue             Communiste      01-JAN-57

3 rows selected.

select Level,p.nom,p.fonction from LesPoliticiens p
where p.membre.nom='Socialiste'
start with p.superieurdirect is NULL
connect by p.superieurdirect=PRIOR REF(p);
     LEVEL NOM             FONCTION
---------- --------------- ---------------
         1 Jospin          retraite
         2 Hollande        premier secretaire

2 rows selected.


1 row selected.


select count(p.nocarteparti),p.membre.nom
from LesPoliticiens p
group by p.membre;
COUNT(P.NOCARTEPARTI) MEMBRE.NOM
--------------------- ---------------
                    1 Communiste
                    1 UMP
                    2 Socialiste

3 rows selected.

select count(v.datevote),p.nom
from LesVotes v, Lespoliticiens p
where (v.datevote='28-APR-2002' or v.datevote IS NULL)
and REF(p) = v.pour (+) 
group by REF(p),p.nom;

COUNT(V.DATEVOTE) NOM
----------------- ---------------
                2 Jospin
                1 Hollande
                1 Hue
                0 Chirac

4 rows selected.

