Weslati Baha GR A TP1 [PDF]

Zitiervorschau

TP1 Création d’un utilisateur : 2) SQL> CREATE USER TPsgbd IDENTIFIED BY 123456 ; 3) SQL> GRANT DBA TO TPsgbd ; 4) SQL> CREATE TABLE Adherent (noadh NUMERIC(7) NOT NULL CONSTRAINT adh_noad_pk PRIMARY KEY, nomadh VARCHAR(25)NOT NULL, prenomadh VARCHAR(25)NOT NULL, tel NUMERIC(8), statut VARCHAR(25)NOT NULL CHECK (statut IN ('Président','SGeneral', 'Porte parole','Actif', 'Passif')), adremail VARCHAR(25), cin NUMERIC(8) UNIQUE) ; SQL> CREATE TABLE Famille (nofam NUMERIC(7) NOT NULL CONSTRAINT fam_nofam_pk PRIMARY KEY, nomfam VARCHAR(25) NOT NULL, nbrpers NUMERIC(2) NOT NULL, remarque VARCHAR(60), tel NUMERIC(8) NOT NULL, adr VARCHAR(30), ville VARCHAR(15) NOT NULL); SQL> CREATE TABLE MembreF (nomem NUMERIC(7) NOT NULL CONSTRAINT memb_nomem_pk PRIMARY KEY, prenom VARCHAR(25)NOT NULL, daten DATE NOT NULL, cin NUMERIC(8) UNIQUE, sexe varchar(1) NOT NULL CHECK (sexe IN ('F','M')), ecole varchar(3) NOT NULL CHECK (ecole IN ('Oui','Non')), handicap varchar(3) NOT NULL CHECK (handicap IN ('Oui','Non')), remarque VARCHAR(60), nofam NUMERIC(7),

CONSTRAINT membre_nofam_fk FOREIGN KEY (nofam) REFERENCES Famille(nofam));

SQL> CREATE TABLE TypeAct (notyp NUMERIC(7) NOT NULL CONSTRAINT typ_notyp_pk PRIMARY KEY, lib VARCHAR(25)NOT NULL, descrit VARCHAR(100)) ; SQL> CREATE TABLE Action (noact VARCHAR(10) NOT NULL CONSTRAINT act_noact_pk PRIMARY KEY, libact VARCHAR(30) NOT NULL, dated DATE DEFAULT CURRENT_DATE, datef DATE DEFAULT CURRENT_DATE, description VARCHAR(100), typeact NUMERIC(7) NOT NULL, CONSTRAINT act_typeact_fk FOREIGN KEY (typeact) REFERENCES Typeact(notyp)) ; SQL> CREATE TABLE Donation (nodonat NUMERIC(7) NOT NULL CONSTRAINT donat_nodonat_pk PRIMARY KEY, typedonat VARCHAR(10)NOT NULL CHECK (typedonat IN ('espèce','chèque','matériel')), Datedont DATE, descr VARCHAR(30), montant NUMERIC(8,3) , nodonateur NUMERIC(7), noact VARCHAR(10), CONSTRAINT donat_noact_fk FOREIGN KEY (noact) REFERENCES action(noact), CONSTRAINT donat_nodon_fk FOREIGN KEY (nodonateur) REFERENCES Adherent(noadh));

SQL> DESC Donation

5) SQL> ALTER TABLE MembreF ADD statut VARCHAR2(5) Not NULL ; 6.1) SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, SEARCH_CONDITION, INDEX_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME LIKE UPPER('MembreF') ;

6.2) SQL> ALTER TABLE MembreF ADD (CONSTRAINT statut_famille_ck CHECK (statut IN (‘fille’, ‘fils’, ‘père’, ‘mère’, ‘autre’))) ; 7) SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, SEARCH_CONDITION, INDEX_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME LIKE UPPER('MembreF') ;

8) SQL> INSERT INTO Famille VALUES (1, 'Dridi', 3, 'le père est décédé', 93456787, '12 rue yyyyy', 'Ariana') ; SQL> INSERT INTO MembreF (nomem,prenom,statut,daten,cin,ecole,endicap, remarque,nofam)VALUES (2,'Rabia','tata','04/23/1972',76543219,'Non','Non', 'sans emploi', 1) ;

9) SQL>ALTER TABLE MembreF DISABLE CONSTRAINT statut_famille_ck CASCADE ; 10) SQL> INSERT INTO MembreF (nomem,prenom,statut,daten,cin,sexe,ecole,handicap, remarque,nofam) VALUES (2, 'Rabia', 'tata','04/09/1972',76543219,'F','Non','Non', 'sans emploi', 1) ;

11) SQL> ALTER TABLE MembreF DROP CONSTRAINT statut_famille_ck CASCADE ; 12) SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, SEARCH_CONDITION, INDEX_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME LIKE UPPER('MembreF') ;

13) SQL> INSERT INTO typeact VALUES (100, 'Scolaire', 'Fournitures, Frais d’inscription, loyer...') ; SQL> INSERT INTO typeact VALUES (101, 'Acte médical', 'Médicaments,Soins spécifiques,Frais de déplacements'); SQL> INSERT INTO typeact VALUES (102, 'Lunette', 'collecte de montures, prise en charge des lunettes de vue') ; SQL> INSERT INTO typeact VALUES (103, 'Collecte solidaire', 'collecte de vêtements, de nourriture') ; COMMIT ; INSERT INTO Adherent VALUES (104, 'Ben Yahya', 'Amine', 54778899, 'Actif', '[email protected]', 56743817) ; INSERT INTO Adherent VALUES (105, 'Bensaid', 'sondes', 92058391, ' Porte parole','[email protected]', 90123897) ; INSERT INTO Adherent VALUES (106, 'Benahmed', 'amir', 96098764, 'Actif', '[email protected]', 90125697) ; COMMIT ; INSERT INTO Famille (nofam, nomfam, nbrpers, tel, adr, ville) VALUES (4, 'Hmiden', 5, 58954321, '40 rue zzzzz', 'Tela'); INSERT INTO MembreF (nomem,prenom,statut,sexe,daten,ecole,handicap,nofam) VALUES (3, 'Sana', 'fille','F','12/09/2001','Non','Non', 1); INSERT INTO MembreF (nomem,prenom,statut,sexe,daten,ecole,handicap,remarque,nofam) VALUES

(5,'Amir','fils','M','08/07/2008','Oui', 'Non', 'suit un traitement suite à des brulures au visage',4); INSERT INTO Action (noact, libact, dated, datef,typeact) VALUES ('Act1','Rentrée scolaire 2015', '08/09/2014','09/10/2014',100); INSERT INTO Action (noact, libact, dated, datef,typeact) VALUES ('Act2','Rentrée scolaire 2016','08/10/2015','09/10/2015',100); INSERT INTO Action VALUES ('Act3','Lunette de vue', '10/04/2017', '10/11/2017', 'collecte de fond pour couvrir les frais de lunettes de vue d’un montant de 200dt', 102) ; INSERT INTO Action (noact, libact, dated, datef,typeact) VALUES ('Act4','Rentrée scolaire 2017','08/11/2016','09/11/2016',100); INSERT INTO Action VALUES ('Act5','Lunette de vue', '02/01/2017', '02/09/2017', 'collecte de fond pour couvrir les frais de lunettes de vue d’un montant de 60dt', 102) ; INSERT INTO Donation VALUES (1,'espèce', '02/01/2016', 'donation libre', 250.0, 106, NULL) ; INSERT INTO Donation VALUES (2,'espèce', '06/08/2016', 'donation libre', 100.0, 106, NULL) ; INSERT INTO Donation VALUES (3,'chèque', '01/08/2017', 'donation libre', 80.0, NULL, NULL) ; INSERT INTO Donation VALUES (4,'chèque', '01/10/2017', 'donation libre', 500.0, NULL, NULL) ; 14) DELETE FROM Adherent WHERE noadh = 106 ;

15) SELECT noadh, nomadh, prenomadh, tel FROM Adherent ;

16)

SQL> SELECT nomem, prenom, nomfam, daten, cin , sexe , ecole ,handicap , m.remarque, statut, m.nofam FROM membref m , famille f WHERE m.nofam = f.nofam AND nomfam = 'Hmiden' ;

17) SQL> SELECT typeact, COUNT(noact) FROM action WHERE EXTRACT (YEAR FROM dated) = 2017 GROUP BY typeact ;

18) SQL> SELECT typedonat, SUM(montant) AS montantTotal FROM donation GROUP BY typedonat ;