Activitats

Manipulació de dades de l'esquema Videoclub

L’objectiu d’aquesta activitat és practicar les sentències de manipulació de dades del llenguatge SQL deL MySQL.

Carregueu el següent script de base de dades en L’SGBDR MySQL i dissenyeu sentències SQL per respondre a les següents qüestions, que fan referència a l’esquema Videoclub:

videoclub.sql ( 1.1 KB )

1. Afegiu l’adreça “’Egara,106.Terrassa’” al client que té DNI “022222222R”.

2. Canvieu tots els noms dels clients per tal que tots els caràcters s’emmagatzemin amb lletres majúscules.

3. Afegiu una película amb codi 6 amb títol ‘FFFFFFFFF’ del gènere de ‘Drama’ que és la segona part de la película ‘DDDDDDD’ (de la qual coneixem el codi: 4) i l’actor principal de la qual és ‘YYYY’.

4. Afegim una còpia amb codi de còpia 1 de la película que té codi 6.

5. Creem una nova factura (amb codi de factura 4) per a ‘Pere Puig’ per a la data actual. L’import serà 0, de moment.

6. Afegim dos conceptes a la factura anterior (número 4):

1 unitat de Pel·li 1, de preu 4,50 €
2 unitats de Refresc, de preu 2,50 €

I modifiquem l’import total de la factura.

7. Afegiu un préstec amb data actual de la còpia existent (1) de la pel·lícula de codi 6 per part de ‘Pere Puig’.

8. Actualitzeu la taula INTERPRETADA per tal que reflecteixi que l’actor principal de la pel·lícula 6 interpreta aquesta pel·lícula.

9. Elimineu la pel·lícula de codi 6.

UPDATE CLIENT
    SET Adreca='Egara,106.Terrassa'
    WHERE DNI='022222222r';

update client
  set nom=upper(nom);

insert into pelicula 
  values (6, 'FFFFFFFFF', 
          (select codigenere from genere where descripcio='Drama'), 
          4, (select codiactor from actor where nom='YYYY'));

insert into copia 
  values (6,1);

insert into factura 
  values (4, sysdate(), 0,  (select dni from client where nom like 'Pere Puig%'));

Inicialment caldrà inserir dues línies al detall de la factura:

insert into detallfactura 
  values (4, 1, 'Peli 1', 4.50, 1);
insert into detallfactura 
  values (4, 2, 'Refresc', 2.50, 2);

Tot seguit caldrà recalcular l’import de la factura i modificar la taula de factures:

update factura
  set import=(select sum(preuunitari*numerounitats) from detallfactura where codifactura=4) 
  where codifactura=4;

insert into prestec
  values (6, 1, sysdate(), (select dni from client where nom like 'Pere Puig%'));

insert into interpretada
  values (6, (select codiactor from pelicula where codipeli=6));

delete from pelicula where codipeli=6;

Aquesta sentència provoca un error, ja que hi ha files que en depenen. El missatge d’error ens indica que la taula interpretada té un registre amb el codi de pel·lícula 6. Caldrà eliminar, doncs, abans aquest registre:

delete from interpretada where codipeli=6;

Si tornem a intentar executar la sentència inicial, el sistema encara ens indica un altre error: també tenim files relacionades en la taula COPIA. Procedim, doncs, a eliminar-les:

delete from copia where codipeli=6;

Després d’això podem executar amb èxit la sentència inicial:

delete from pelicula where codipeli=6;

Manipulació de dades de l'esquema Empresa

L’objectiu d’aquesta activitat és practicar amb les instruccions de manipulació de dades (inserció, actualització i eliminació) en una base de dades amb el llenguatge SQL.

Dissenyeu les instruccions SQL adequades per aconseguir donar resposta a les sol·licituds següents referents a l’esquema empresa:

Carregueu el següent script de base de dades en l’SGBDR MySQL que fa referència a l’esquema empresa:

empresa.sql ( 3.5 KB )

1. Donar d’alta aquests departaments:

Informàtica, a la població de Lleida, amb codi 50
Compres, a la població de Girona, amb codi 60
Personal, a la població de Tarragona, amb codi 70
Serveis mèdics, amb codi 80, pendent d’ubicar

2. Donar d’alta un empleat amb codi 7935, de cognom ‘GOTERA’, de professió ‘PSICÒLEG’, assignat al departament de ‘SERVEIS MÈDICS’ (obtenint-ne dinàmicament el codi) i dependent directament del ‘PRESIDENT’ de la companyia, que sabem que té codi d’empleat 7839.

S’ha de poder fer amb una única instrucció, sense poder esbrinar prèviament el codi del departament de ‘SERVEIS MÈDICS’.

3. Donar d’alta una ordre (capçalera i línies) en data d’avui, al client 100, calcada de la ordre 617, per ser tramesa d’aquí quinze dies i amb un increment del 10% en els preus.

Es pot fer utilitzant diverses instruccions SQL.

4. Ubicar el departament ‘SERVEIS MÈDICS’ a la ciutat de ‘VALÈNCIA’.

5. Elimineu la ordre que heu donat d’alta en l’apartat 3.

Les sentències són aquestes:

insert into dept (dept_no, dnom, loc) values (50,'INFORMÀTICA','LLEIDA');
 
insert into dept (dept_no, dnom, loc)  values (60,'COMPRES','GIRONA');
 
insert into dept (dept_no, dnom, loc)  values (70,'PERSONAL','TARRAGONA');
 
insert into dept values (80,'SERVEIS MÈDICS', null);
 
commit;

La instrucció COMMIT és necessària si no tenim activada la validació automàtica (autocommit=0) i volem validar els canvis de manera que qualsevol altre usuari hi tingui accés des d’una altra sessió.

També ens hauríem pogut estalviar escriure les columnes en què inserim els valors i, en aquest cas, l’ordre hauria d’haver coincidit amb el que mostra l’ordre describe:

 desc dept


 Nom                                        Nul?     Tipus
 -----------------------------------------  -------- ---------------
 DEPT_NO                                    not null  number(2)
 DNOM                                       not null  varchar2(14)
 LOC                                                  varchar2(14)

Fixem-nos que ha estat possible la inserció del departament 80 pendent d’ubicació indicant el valor null en la columna loc, ja que aquesta columna permet valors nuls.

Posteriorment, podem comprovar que les insercions s’han efectuat:

select * from dept order by 1;
 
   DEPT_NO DNOM           LOC
---------- -------------- --------------
        10 COMPTABILITAT  SEVILLA
        20 INVESTIGACIÓ   MADRID
        30 VENDES         BARCELONA
        40 PRODUCCIÓ      BILBAO
        50 INFORMÀTICA    LLEIDA
        60 COMPRES        GIRONA
        70 PERSONAL       TARRAGONA
        80 SERVEIS MÈDICS 
 
8 files seleccionades.

La sentència és aquesta:

insert into emp values ( 7935, 'GOTERA', 'PSICÒLEG', 7839, sysdate(),null,null,
         (select dept_no from dept where upper(dnom)='SERVEIS MÈDICS'));

on estem inserint els valors en l’ordre en què estan en la taula (segons l’ordre describe):

desc emp;
 
 Nom               Nul?     Tipus
 ----------------- -------- ------------
 EMP_NO            not null number(4)
 COGNOM            not null varchar2(10)
 OFICI                      varchar2(10)
 CAP                        number(4)
 DATA_ALTA                  DATE
 SALARI                     number(10)
 COMISSIÓ                   number(10)
 DEPT_NO           not null number(2)

Posteriorment, podem comprovar que la inserció ha estat correcta:

select * from emp order by emp_no;

EMP_NO COGNOM     OFICI         CAP DATA_ALT     SALARI   COMISSIO    DEPT_NO
------ ---------- ---------- ------ -------- ---------- ---------- ----------
  7369 SÀNCHEZ    EMPLEAT      7902 17/12/80     104000                    20
  7499 ARROYO     VENEDOR      7698 20/02/80     208000      39000         30
  7521 SALA       VENEDOR      7698 22/02/81     162500      65000         30
  7566 JIMÉNEZ    DIRECTOR     7839 02/04/81     386750                    20
  7654 MARTÍN     VENEDOR      7698 29/09/81     162500     182000         30
  7698 NEGRO      DIRECTOR     7839 01/05/81     370500                    30
  7782 CEREZO     DIRECTOR     7839 09/06/81     318500                    10
  7788 GIL        ANALISTA     7566 09/11/81     390000                    20
  7839 REY        PRESIDENT         17/11/81     650000                    10
  7844 TOVAR      VENEDOR      7698 08/09/81     195000          0         30
  7876 ALonSO     EMPLEAT      7788 23/09/81     143000                    20
  7900 JIMENO     EMPLEAT      7698 03/12/81     123500                    30
  7902 FERNÁNDEZ  ANALISTA     7566 03/12/81     390000                    20
  7934 MUÑOZ      EMPLEAT      7782 23/01/82     169000                    10
  7935 GOTERA     PSICÒLEG     7839 16/07/08                               80

15 files seleccionades.

És important adonar-se que es produiria un error si no hi hagués cap departament de nom ‘SERVEIS MÈDICS’, ja que no podríem introduir un valor nul en la columna CAP.

En primer lloc, esbrinem el següent número d’ordre:

select max(com_num)+1 from comanda;

I obtenim com a resultat la 622.

Tot seguit volem conèixer el codi del tipus de ordre que té al 617:

select com_tipus from comanda where com_num=617;

I observem que el seu valor és null. Ara creem la capçalera de la instrucció:

insert into comanda 
   values (622, sysdate(),null,100,adddate(sysdate(),15), null);

A continuació, inserim en la taula DETALL les línies que corresponen com a còpia de les línies de detall de la ordre 617, però amb l’augment del preu indicat i inserint el número de instrucció que acabem de veure en el pas anterior.

insert into detall (select 622, detall_num, prod_num, 
                              preu_venda*1.10,  quantitat,
                              preu_venda*1.10*quantitat
                      from detall 
                      where com_num=617
                     );
 
10 files creades. 
 </code >

Finalment, actualitzem l'import total de la nova instrucció amb el càlcul a partir de les línies de detall que acabem d'inserir en l'apartat anterior.

<code>
update comanda set total=(select sum(import) 
                            from detall 
                            where com_num=622)
                 where com_num=622;
 
1 fila actualitzada.

Comprovem l’existència de la ordre 622 (capçalera i línies) comparant-la amb la ordre 617.

SQL>  select * from comanda where com_num=617 or com_num=622;

   COM_NUM COM_DATA C CLIENT_COD DATA_TRA      TOTAL
----------  -------- - ---------- -------- ----------
       617 05/02/87          104  03/03/87      46370
       622 16/07/08          100  31/07/08      51007

SQL>  select * from detall where com_num=617 or com_num=622 
     order by com_num, detall_num;
 
   COM_NUM DETALL_NUM   PROD_NUM PREU_VENDA  QUANTITAT      IMPORT
---------- ---------- ---------- ---------- ---------- ----------
       617          1     100860         35         50       1750
       617          2     100861         45        100       4500
       617          3     100870        2,8        500       1400
       617          4     100871        5,6        500       2800
       617          5     100890         58        500      29000
       617          6     101860         24        100       2400
       617          7     101863       12,5        200       2500
       617          8     102130        3,4        100        340
       617          9     200376        2,4        200        480
       617         10     200380          4        300       1200
       622          1     100860       38,5         50       1925
       622          2     100861       49,5        100       4950
       622          3     100870       3,08        500       1540
       622          4     100871       6,16        500       3080
       622          5     100890       63,8        500      31900
       622          6     101860       26,4        100       2640
       622          7     101863      13,75        200       2750
       622          8     102130       3,74        100        374
       622          9     200376       2,64        200        528
       622         10     200380        4,4        300       1320

20 files seleccionades.

update dept set loc='VALÈNCIA'
  where  upper(dnom)='SERVEIS MÈDICS'

Podem comprovar la correcció de la instrucció:

select * from dept;

I n’obtenim la solució:

 
   DEPT_NO DNOM           LOC
---------- -------------- -------------
        50 INFORMÀTICA    LLEIDA
        60 COMPRES        GIRONA
        70 PERSONAL       TARRAGONA
        80 SERVEIS MÈDICS VALÈNCIA
        10 COMPTABILITAT  SEVILLA
        20 INVESTIGACIÓ   MADRID
        30 VENDES         BARCELONA
        40 PRODUCCIÓ      BILBAO
 
8 files seleccionades.

Executem la sentència que esborra la ordre número 622.

delete from comanda where com_num=622;

Però no pot ésser eliminada perquè la instrucció és una fila pare i té valors referenciats en una altra taula (clau forana).

És a dir, la taula DETALL té definida la restricció d’integritat referencial de la columna com_num respecte la taula COMANDA sense eliminació en cascada i, per tant, l’SGBD no ens deixa eliminar un registre de la taula COMANDA que té registres relacionats en la taula DETALL.

Per tant, primer haurem d’eliminar les files corresponents de la taula DETALL per, posteriorment, eliminar la capçalera.

delete from detall where com_num=622; 

delete from comanda where com_num=622;

Manipulació de dades de l'esquema Sanitat

L’objectiu d’aquesta activitat és practicar amb les instruccions de manipulació de dades (inserció, actualització i eliminació) en una base de dades amb el llenguatge SQL.

Dissenyeu les instruccions SQL adequades per aconseguir donar resposta a les sol·licituds següents referents a l’esquema:

Carregueu el següent script de base de dades en l’SGBDR MySQL que fa referència a l’esquema sanitat:

sanitat.sql ( 2 KB )

1. Creeu l’hospital ‘La Pau’ amb codi 50 i amb l’adreça ‘Catalana 2000’. Assigneu a l’hospital les sales següents:

Sala 1 de nom ‘Recuperació’ amb 10 llits
Sala 2 de nom ‘Maternitat’ amb 15 llits
Sala 3 de nom ‘Cures Intensives’ amb 15 llits
Sala 4 de nom ‘Cardiologia’ amb 20 llits
Sala 5 de nom ‘Psiquiàtric’ amb 30 llits

2. Traslladeu el doctor ‘Cabeza D.’ de l’hospital 22 a l’hospital 50.

3. Augmenteu el sou de tots els empleats sanitaris amb els percentatges següents:

5% a tots els empleats del torn de matí o tarda
7% als empleats del torn de nit

Intenteu fer-ho amb una única sentència SQL.

4. Doneu d’alta en el sistema sanitari el malalt ‘Gotera, P.’, amb adreça a ‘Percebe, 13’, el qual avui fa trenta anys, de sexe ‘H’, i ingresseu-lo a la sala ‘Recuperació’ de l’hospital 50.

5. Elimineu l’hospital 50.

insert  into hospital values (50, 'La Pau', 'Catalana 2000',null, 0);
 
insert  into sala values (50,1,'Recuperació',10);
 
insert  into sala values (50,2,'Maternitat',15);
 
insert  into sala values (50,3,'Cures Intensives',15);
 
insert  into sala values (50,4,'Cardiologia',20);
 
insert  into sala values (50,5,'Psiquiàtric',30);
 

update hospital set qtat_llits=(select sum(qtat_llits)
                                  from sala
                                  where hospital_cod=50)
                  where hospital_cod=50;

Podem comprovar la correcció de les instruccions:

select * from hospital;

Amb resultat:

 
HOSPITAL_COD NOM        DIRECCIO             TELEFON  QTAT_LLITS
------------ ---------- -------------------- -------- ----------
          13 Provincial O Donell 50          964-4264         88
          18 General    Atocha s/n           595-3111         63
          22 La Paz     Castellana 1000      923-5411        162
          45 San Carlos Ciudad Universitaria 597-1500         92
          50 La Pau     Catalana 2000        null             90

select * from sala where hospital_cod=50;

Amb resultat:

HOSPITAL_COD   SALA_COD NOM                  QTAT_LLITS
------------ ---------- -------------------- ----------
          50          1 Recuperació                  10
          50          2 Maternitat                   15
          50          3 Cures Intensives             15
          50          4 Cardiologia                  20
          50          5 Psiquiàtric                  30

Abans de començar, hauríem de verificar que només hi ha un doctor a l’hospital 22 que s’anomeni ‘Cabeza D.’, ja que si fem una actualització filtrant per aquest cognom i resulta que n’hi ha més d’un a l’hospital 22, tots seran traslladats.

Això, de fet, també ho podem veure amb el missatge que ens doni respecte al nombre de files actualitzades. Si veiem que el nombre és superior a 1, sempre podem executar un roll-back per desfer els canvis efectuats.

update doctor set hospital_cod=50
         where hospital_cod=22 and upper(cognom)='CABEZA D.'; 

commit;

Podem comprovar la correcció de la instrucció executada:

select * from doctor;

Que resulta:

HOSPITAL_COD  DOCTOR_NO COGNOM        ESPECIALITAT
------------ ---------- ------------- ----------------
          13        435 López A.      Cardiologia
          18        585 Miller G.     Ginecologia
          18        982 Cajal R.      Cardiologia
          22        453 Galo D.       Pediatria
          22        398 Best K.       Urologia
          50        386 Cabeza D.     Psiquiatria
          45        607 Nico P.       Pediatria
          45        522 Adams C.      Neurologia

Per comprovar la correcció de la instrucció, en primer lloc, ens convé quedar-nos amb els salaris actuals per després poder comprovar-ne els canvis:

select * from plantilla;

Amb el resultat:

HOSPITAL_COD   SALA_COD EMPLEAT_NO  COGNOM          FUNCIO    T     SALARI
------------ ---------- ---------- --------------- ---------- - ----------
          13          6       3754 Díaz B.         Infermera  T    2262000
          13          6       3106 Hernández J.    Infermer   T    2755000
          18          4       6357 Karplus W.      Intern     T    3379000
          22          6       1009 Higueras D.     Infermera  T    2005000
          22          6       8422 Bocina G.       Infermer   M    1638000
          22          2       9901 Adams C.        Intern     M    2210000
          22          1       6065 Rivera G.       Infermera  N    1626000
          22          1       7379 Carlos R.       Infermera  T    2119000
          45          4       1280 Amigó R.        Intern     N    2210000
          45          1       8526 Frank H.        Infermera  T    2522000

10 files seleccionades.

La sentència que permet efectuar les modificacions és aquesta:

update plantilla set salari = case
    when torn='T' then salari*1.05
    when torn='M' then salari*1.05
    when torn='N' then salari*1.07
  end;

Podem comprovar els resultats per comparar-los amb els salaris previs:

select * from plantilla;

Que resulta:

HOSPITAL_COD   SALA_COD EMPLEAT_NO  COGNOM          FUNCIO    T     SALARI
------------ ---------- ---------- --------------- ---------- - ----------
          13          6       3754 Díaz B.         Infermera  T    2375100
          13          6       3106 Hernández J.    Infermer   T    2892750
          18          4       6357 Karplus W.      Intern     T    3547950
          22          6       1009 Higueras D.     Infermera  T    2105250
          22          6       8422 Bocina G.       Infermer   M    1719900
          22          2       9901 Adams C.        Intern     M    2320500
          22          1       6065 Rivera G.       Infermera  N    1739820
          22          1       7379 Carlos R.       Infermera  T    2224950
          45          4       1280 Amigó R.        Intern     N    2364700
          45          1       8526 Frank H.        Infermera  T    2648100

10 files seleccionades.

Una possible sentència per inserir el nou malalt, assabentant-nos prèviament de les columnes que conté la taula MALALT i del següent codi de malant:

desc malalt<code>

Que permet obtenir:
<code>
 Nom                                        Nul?     Tipus
 -----------------------------------------  -------- -------------
 INSCRIPCIO                                not null  number(5)
 COGNOM                                    not null  varchar2(15)
 ADRECA                                            varchar2(20)
 DATA_NAIX                                           DATE
 SEXE                                      not null  varchar2(1)
 NSS                                                 number(9)

I select max(inscripcio)+1 from malalt; que ens dóna com a resultat 74836.

Aleshores:

insert into malalt 
  values (74836, 'Gotera, P.',
           'Percebe 13', str_to_date(concat(year(sysdate())-30,'-', month(sysdate()),'-', day(sysdate())),'%Y-%m-%d'),'H',null);

Fixem-nos en la instrucció per tirar, a partir d’avui, 30 anys enrere. Aquesta instrucció s’ha executat el dia 16 de juliol de 2008 i ha provocat l’alta següent:

select * from malalt where cognom='Gotera, P.';

Amb resultat:

 
INSCRIPCIO COGNOM           ADRECA            DATA_NAIX  S        NSS
---------- --------------- -------------------- ---------- - ----------
     74836 Gotera, P.      Percebe 13           16/07/1978 H

Ara ja estem en disposició d’efectuar l’ingrés:

desc ingressos

 Nom                                        Nul?     Tipus
 -----------------------------------------  -------- --------------------
 INSCRIPCIO                                 not null number(5)
 HOSPITAL_COD                               not null number(2)
 SALA_COD                                   not null number(2)
 LLIT                                                number(4)

Aleshores:

insert  into ingressos values (74836, 50, 1, null);

Si intentem eliminar l’hospital directament en la taula HOSPITAL, ens trobem amb un error de restricció per clau forana:

delete from hospital where hospital_cod=50;

És a dir, la taula SALA té definida la restricció d’integritat referencial de la columna hospital_cod respecte a la taula HOSPITAL sense eliminació en cascada i, per tant, l’SGBD no ens deixa eliminar un registre de la taula HOSPITAL que té registres relacionats en la taula SALA.

Per tant, primer haurem d’eliminar les files corresponents de la taula SALA per, posteriorment, eliminar l’hospital.

En intentar eliminar les sales de l’hospital 50, ens trobem també amb un error:

delete from sala where hospital_cod=50;

És a dir, la taula INGRESSOS té definida la restricció d’integritat referencial de les columnes hospital_cod – sala_cod respecte a la taula SALA sense eliminació en cascada i, per tant, l’SGBD no ens deixa eliminar un registre de la taula SALA que tingui registres relacionats en la taula INGRESSOS.

Per tant, primer haurem d’eliminar les files corresponents de la taula INGRESSOS per, posteriorment, eliminar les sales i, finalment, l’hospital.

delete from ingressos where hospital_cod=50;

delete from sala where hospital_cod=50;

delete from hospital where hospital_cod=50;

Ai… no recordàvem que havíem traslladat el doctor ‘Cabeza D.’ de l’hospital 22 al 50 i no podem eliminar l’hospital 50 si hi ha doctors que el tenen assignat. Procedim a retornar el doctor ‘Cabeza D.’ a l’hospital 22 (també el podríem eliminar) i ja podrem eliminar l’hospital 50.

update doctor set hospital_cod=22 
  where hospital_cod=50 and cognom='Cabeza D.';

delete from hospital where hospital_cod=50;