Untitled

    CREATE TYPE OR REPLACE adresse_type AS object(num_rue NUMBER(3), nom_rue VARCHAR(40), ville VARCHAR(30))
     
    CREATE TYPE personne_type AS object(nom VARCHAR(20), prenom VARCHAR(20), adresse adresse_type)
     
    CREATE TYPE  OR REPLACE activites_type AS object (niveau NUMBER(1), nom VARCHAR(20), equipe VARCHAR(20))
     
    CREATE TYPE OR REPLACE cours_type AS object (num_cours NUMBER(2), nom VARCHAR(20), nbheures NUMBER(2), annee NUMBER(1))
     
    ---- creation de table
     
    DROP TABLE personne;
    DROP TABLE cours ;
    DROP TABLE activites;
     
    CREATE TABLE personne OF personne_type;
    CREATE TABLE cours OF  cours_type (CONSTRAINT pk_cours PRIMARY KEY (num_cours), CHECK(nom IS NOT NULL ));
    CREATE TABLE activites OF  activites_type (CONSTRAINT pk_activites PRIMARY KEY (niveau, nom));
     
    --- insertion dans personne
     INSERT INTO personne VALUES (personne_type('Mohammed', 'Gueye', adresse_type(1, 'rue uno', 'Uno')));
     INSERT INTO personne VALUES (personne_type('Hamza', 'Nahi', adresse_type(2, 'rue dos', 'Dos')));
     INSERT INTO personne VALUES (personne_type('Rammohan', 'Ramsanjeevan', adresse_type(3, 'rue tres', 'Tres')));
     
    --- insertion dans cours
     
     INSERT INTO cours VALUES (1, 'Réseau', 15, 1);
     INSERT INTO cours VALUES (2, 'Sgbd', 30, 1) ;
     INSERT INTO cours VALUES (3, 'Programmation', 15,1) ;
     INSERT INTO cours VALUES (4, 'Sgbd', 30,2 ) ;
     INSERT INTO cours VALUES (5, 'Analyse', 60,2) ;
     
    --- insertion dans activites
     
     INSERT INTO ACTIVITES VALUES(3,'Basket','Ace Club') ;
     INSERT INTO ACTIVITES VALUES(1,'Volley ball', 'Avs80') ;
     INSERT INTO ACTIVITES VALUES(2,'Mini foot', 'Les as du ballon') ;
     INSERT INTO ACTIVITES VALUES(2,'Volley ball', 'smash') ;
     
     
    --- 1.2
    SELECT * FROM cours
    SELECT activites.nom, COUNT(activites.equipe) AS nb_equipe FROM activites GROUP BY activites.nom
    SELECT nom, nbheures FROM cours WHERE nbheures > 24
    INSERT INTO ACTIVITES VALUES(1,'Ski','Ace Club') ;