Correction de l’atelier 2-SGBDRO

Exercice 1 :

1.
SQL> create type tab_email as varray(5) of varchar2(20);
  2  /

Type créé.

2.
SQL> create table fournisseur(id number, nom varchar2(10), ville varchar2(10),
  2  liste_email tab_email);

Table créée.


3.

SQL> insert into fournisseur values(10,'ABC','rabat',tab_email('ab@gmail.com','ali@yahoo.fr','sd@menara.ma'));

1 ligne créée.

SQL> insert into fournisseur values(20,'SOS','casa',tab_email('sos@mail.ma','sos@hotmail.com'));

1 ligne créée.

4.

SQL> select  2   from fournisseur;

        ID NOM        VILLE  LISTE_EMAIL                                                                    
--------------------------------------------------------------------------------
        10 ABC        rabat TAB_EMAIL('ab@gmail.com', 'ali@yahoo.fr', 'sd@menara.ma')                      
                                                                               
        20 SOS        casa TAB_EMAIL('sos@mail.ma', 'sos@hotmail.com')                                    
                                                                               



SQL> desc fournisseur
 Nom                                       NULL ?   Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NOM                                                VARCHAR2(10)
 VILLE                                              VARCHAR2(10)
 LISTE_EMAIL                                        TAB_EMAIL

SQL> select nom, liste_email from fournisseur;

NOM      LISTE_EMAIL                                                                    
--------------------------------------------------------------------------------
ABC  TAB_EMAIL('ab@gmail.com', 'ali@yahoo.fr', 'sd@menara.ma')                      
                                                                               
SOS TAB_EMAIL('sos@mail.ma', 'sos@hotmail.com')                                    
                                                                               



SQL> select f.nom, v.column_value from fournisseur f, table(f.liste_email) v;

NOM        COLUMN_VALUE                                                        
---------- --------------------                                                
ABC        ab@gmail.com                                                        
ABC        ali@yahoo.fr                                                        
ABC        sd@menara.ma                                                        
SOS        sos@mail.ma                                                         
SOS        sos@hotmail.com                                                     

5.

SQL> select f.nom, v.column_value from fournisseur f, table(f.liste_email) v where f.nom like 'ABC';

NOM        COLUMN_VALUE                                                        
---------- --------------------                                                
ABC        ab@gmail.com                                                        
ABC        ali@yahoo.fr                                                        
ABC        sd@menara.ma                                                        

6.


SQL> drop type tab_email;
drop type tab_email
*
ERREUR à la ligne 1 :
ORA-02303: impossible de supprimer ou de remplacer un type dont dépendent des
types ou des tables


SUPPRESSION IMPOSSIBLE

Exercice 2

1.
SQL> create type t_prenom as varray(4) of varchar2(20);
  2  /

Type créé.



SQL> ed
écrit file afiedt.buf

  1  create  or replace type t_personne as object
  2  ( id number,
  3  nom varchar2(10),
  4* prenom t_prenom)
SQL> /

Type créé.

2.

SQL> create table personne of t_personne (constraint  pk_id primary key(id));

Table créée.

3.

SQL> desc t_personne
 Nom                                       NULL ?   Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NOM                                           VARCHAR2(10)
 PRENOM                                     T_PreNOM

SQL> desc personne
 Nom                                       NULL ?   Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 NOM                                                VARCHAR2(10)
 PRENOM                                             T_PreNOM

4.
SQL> insert into personne values(1,'kassimi',t_prenom('ali','ali mohamed'));

1 ligne créée.

SQL> insert into personne values(2,'hain',t_prenom('mustapha','mustafa'));

1 ligne créée.

SQL> commit;

Validation effectuée.

5.

SQL> select nom, prenom from personne;

NOM        PRENOM                                                                         
--------------------------------------------------------------------------------
kassimi    T_PreNOM('ali', 'ali mohamed')                                                 
                                                                               
hain       T_PreNOM('mustapha', 'mustafa')                                                
                                                                               

Exercice3


1.

create type t_etudiant as object(code varchar2(20), nom varchar2(20), prenom varchar2(20));

create type list_etudiant as table of t_etudiant;
create table classe(id varchar2(20),opt varchar2(20),etudiant list_etudiant)
nested table etudiant store as table_etudiants;


2-desc classe

3-insert into classe values('isi4','informatique',
                           list_etudiant(t_etudiant('ax10','toto','ali'),
                                             t_etudiant('ax11','tata','sami'),
                                             t_etudiant('ax12','titi','siham')));

insert into classe values('com','communication',
                                     list_etudiant(t_etudiant('ax22','tito','salma'),
                                     t_etudiant('ax23','tato','amine')));

4-select * from classe;
select * from classe c,TABLE(c.etudiant);

5-insert into the(select etudiant from classe where id='isi4') values(t_etudiant('ax14','tom','kamal'));
select * from classe;

6-update table (select etudiant from classe where id='isi4')
                             set nom='yassir'
                             where code='ax14';