M2 - Bases de dades / 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.