M2 - Bases de dades / Solucio exemple prova UF2: DDL - DML

Exemple examen DDL-DML

Esquema base de dades metges

Aquí tens la descripció detallada de cada taula:

1. Crea les taules i les restriccions anteriors.

 CREATE TABLE POBLACIONS (
 NOM VARCHAR2(15) CONSTRAINT PK_NOM_POBLACIONS PRIMARY KEY,
 HABITANTS NUMBER(6) CONSTRAINT NN_HABITANTS_POBLACIONS NOT NULL,
 SUPERFICIE NUMBER(10),
 CONSTRAINT CK_HABITANTS_POBLACIONS CHECK(HABITANTS BETWEEN 1 AND 250000));

 CREATE TABLE MALALTS (
 NUM_MALALT NUMBER(4) CONSTRAINT PK_NUM_MALT_MALALTS PRIMARY KEY,
 NOM_MALALT VARCHAR2(30)CONSTRAINT NN_NOM_MALT_MALALTS NOT NULL,
 ADREÇA VARCHAR2(50),
 DNI VARCHAR2(9) CONSTRAINT UQ_DNI_MALALTS UNIQUE,
 POBLACIO VARCHAR2(15) CONSTRAINT FK_POBLACIO_MALALTS REFERENCES POBLACIONS(NOM) ON DELETE CASCADE);

 CREATE TABLE METGES(
 NUM_METGE NUMBER(3)CONSTRAINT PK_NUM_METGES PRIMARY KEY, 
 NOM_METGE VARCHAR2(30) CONSTRAINT UQ_NOM_METGES UNIQUE,
 ESPECIALITAT VARCHAR2(30)CONSTRAINT CK_ESPECIALITAT_METGES CHECK(ESPECIALITAT IN('PEDIATRE','MG','OCULISTA')),
 ADREÇA VARCHAR2(40),
 TELEFON VARCHAR2(9)CONSTRAINT NN_TELEFON_METGES NOT NULL , 
 SOU NUMBER(4,2)CONSTRAINT NN_SOU_METGES NOT NULL );

 CREATE TABLE VISITES( 
 NUM_METGE NUMBER(3) CONSTRAINT FK_METGE_VISITES REFERENCES METGES(NUM_METGE),
 NUM_MALALT NUMBER(4) CONSTRAINT FK_MALALT_VISITES REFERENCES MALALTS(NUM_MALALT),
 DATA_VISITA DATE,
 DATA_CONCERTACIO DATE DEFAULT SYSDATE,
 IMPORT NUMBER(3),
 CONSTRAINT PK_VISITES PRIMARY KEY(NUM_METGE, NUM_MALALT, DATA_VISITA));

 CREATE TABLE DETALL_VISITES(
 NUM_METGE NUMBER(3),
 NUM_MALALT NUMBER(4),
 DATA_VISITA DATE,
 NUM_LINIA NUMBER(2),
 TIPUS_VISITA VARCHAR2(10),
 SIMPTOMES VARCHAR2(80),
 CONSTRAINT PK_DETALL_VISITES PRIMARY KEY (NUM_METGE, NUM_MALALT, DATA_VISITA,NUM_LINIA),
 CONSTRAINT FK_DETALL_VISITES FOREIGN KEY (NUM_METGE, NUM_MALALT, DATA_VISITA)
 REFERENCES VISITES(NUM_METGE, NUM_MALALT, DATA_VISITA));

2. Crea una seqüència anomenada 'num_malalt_seq' amb les característiques següents. Valor inicial 1000, increment 1, valor màxim 9999.

 CREATE SEQUENCE NUM_MALALT_SEQ
 START WITH 1000
 INCREMENT BY 1
 MAXVALUE 9999

3. Crea un script per insertar les dades d’un malalt. Usa la seqüencia creada anteriorment.

 ACCEPT v_nom_malalt PROMPTINSERT INTO POBLACIONS

SELECT Nom_ciutat, Num_habitants, Superfície FROM CIUTATS; 'Introdueix el nom del malalt: '

 ACCEPT v_adreça PROMPT 'Introdueix la adreça del malalt: '
 ACCEPT v_dni PROMPT 'Introdueix el dni: '
 ACCEPT v_poblacio PROMPT 'Introdueix la poblacio: '
 
 INSERT INTO MALALTS 
 VALUES(NUM_MALALT_SEQ.NEXTVAL, '&v_nom_malalt','&v_adreça','&v_dni','&v_poblacio');

4. Modificar el contingut de la taula metges per a que el seu sou s'incrementi en un 10% ,però tan sols d’aquells metges que hagin fet alguna visita.

 UPDATE METGES
 SET SOU=SOU*1.1
 WHERE NUM_METGE IN (SELECT DISTINCT NUM_METGE FROM VISITES); 

5. Volem insertar dins de la taula POBLACIONS els registres que tenim en una altra taula anomenada CIUTATS formada pels camps ( Codi_ciutat, Nom_ciutat, Província, Num_Habitants, Superfície). Com ho podem fer tenint present si només ens interessen tres camps d'aquesta darrera taula?

 INSERT INTO POBLACIONS
 SELECT Nom_ciutat, Num_habitants, Superfície
 FROM CIUTATS;

6. Modifica les característiques del camp 'simptomatologia' de la taula Detall_visites a un màxim de 100 caràcters

 ALTER TABLE DETALL_VISITES
 MODIFY (simptomatologia VARCHAR2(100)); 
 

7. Esborra, per error, les dades dels metges de l'especialitat de Neurologia. Torna a recuperar-los.

 DELETE 
 FROM METGES
 WHERE ESPECIALITAT='NEUROLOGIA';

 ROLLBACK: 

8. Quines és la diferència entre aquestes sentències sobre una taula? DROP, DELETE i TRUNCATE .

  DELETE-> ELIMINA LAS FILAS DE LA TABLA. PODEMOS HACER ROLLBACK (deshacer)
  DROP -> BORRA TODO EL CONTENIDO Y LA DEFINICION DE LA TABLA. NO ROLLBACK.
  TRUNCATE-> BORRA TODAS LAS FILAS DE UNA TABLA PERO NO PERMITE ROLLBACK.