editat perDarrera edicióDiferènciesRestaurar Revisió
daniel herrera29/01/2018 19:48
@@ -8,13 +8,13 @@
     jornada( numeroJornada (pk), dataOrientativa )
     partit( numeroJornada (pk, fk1 a jornada), campPrevist (pk, fk2 a camp) )
     equipJugaPartit( numeroJornada (pk, fk1 a partit), 
-                     campPrevist (pk, fk1 a partit), equip (pk, fk2 a equip), 
+                     campPrevist (pk, fk1 a partit), equip ( fk2 a equip), 
                      golsMarcats, esEquipLocal (pk) )
 
 1. Fés el MCD per poder tenir una visió global de l'estructura de la base de dades
 1. Fes els create tables. Amb claus primàries i foranes.
 2. Inserta dades. Inserta al menys `Mestalla` i el `CampNou`
-2. Mitjana de gols marcats a Mestalla. (Join o subconsulta per saber id de 'Mestalla')
+2. Total de gols marcats a Mestalla. (Join o subconsulta per saber id de 'Mestalla')
 3. Total de gols marcats per Equip.
 3. Total de gols marcats a cada camp.
 4. Llistat d'equips que han jugat al 'CampNou'
Restaurar
daniel herrera29/01/2018 18:49
@@ -16,6 +16,7 @@
 2. Inserta dades. Inserta al menys `Mestalla` i el `CampNou`
 2. Mitjana de gols marcats a Mestalla. (Join o subconsulta per saber id de 'Mestalla')
 3. Total de gols marcats per Equip.
+3. Total de gols marcats a cada camp.
 4. Llistat d'equips que han jugat al 'CampNou'
 5. Llistat de partits disputats amb nom dels equips i resultat final.
 6. Mostra els equips que han marcat més de 3 gols (en tota la temporada)
Restaurar
daniel herrera29/01/2018 18:21
@@ -14,7 +14,7 @@
 1. Fés el MCD per poder tenir una visió global de l'estructura de la base de dades
 1. Fes els create tables. Amb claus primàries i foranes.
 2. Inserta dades. Inserta al menys `Mestalla` i el `CampNou`
-2. Mitjana de gols marcats a Mestalla. (Subconsulta per saber id de 'Mestalla')
+2. Mitjana de gols marcats a Mestalla. (Join o subconsulta per saber id de 'Mestalla')
 3. Mitjana de gols marcats per Equip.
 4. Llistat d'equips que han jugat al 'CampNou'
 5. Llistat de partits disputats amb nom dels equips i resultat final.
Restaurar
daniel herrera29/01/2018 16:34
@@ -1,6 +1,6 @@
 
 Llenguatges SQL: DML i DDL
-Selects: where i group by. Partits de futbol
+SQL Exercicis 5.3: Selects: where i group by. Partits de futbol
 Observa aquestes taules:
     
     equip ( id (pk), nom )
Restaurar
daniel herrera26/01/2018 17:23
@@ -21,153 +21,6 @@
 7. Mostra els partits amb més de 3 gols ( nivell de dificultat alta )
 8. Mostra els equips que han marcat més gols que la mitjana dels equips calculdada com la suma de tots els gols dividit pel número de partits ( nivell de dificultat alta )
 9. Mostra els camps on s'han marcat més gols de un 70% la mitjana de gols que es marquen per camp (calculada com suma de gols per camp dividit per número de camps )
-
-**Solució by Power Designer**
-
-![Imgur](http://i.imgur.com/C2sQbxI.png)
-
-**SQL**
-
-    :::sql
- create table equip(
- id int,
- nom varchar(100),
- constraint equip_pk 
- primary key (id)
- );
- create table camp(
- id int ,
- nom varchar(30),
- constraint camp_pk 
- primary key (id)
- );
- create table jornada(
- numeroJornada int,
- dataOrientativa date,
- constraint jornada_pk 
- primary key (numeroJornada)
- );
- create table partit(
- numero_jornada int,
- camp_previst int
- constraint partit_pk 
-    primary key(numero_jornada,camp_previst),
- constraint partit_a_jornada_fk 
-    foreign key(numero_jornada) 
-    references jornada(numeroJornada),
-     constraint partit_a_camp_fk 
-    foreign key(camp_previst) 
-    references camp(id)
- );
- create table equipJugaPartit(
- NumeroJornada int,
- campPrevist int,
- equip int,
- golsmarcats int,
- esEquipLocal bit,
- constraint jugapartit_pk 
-     primary key (numeroJornada,campPrevist,esEquipLocal),
- constraint jugapartit_a_partit_fk 
- foreign key (NumeroJornada,campPrevist) 
- references partit(numero_jornada,camp_previst),
- constraint jugapartit_a_equip_fk  
- foreign key (equip) 
- references equip(id)
- );
-
-
-**Inserts**
-
-    :::sql
-    insert into equip values
-    ( 1,'Barça' ),
-    ( 2,'Madrid' ),
-    ( 3,'Espanyol' ),
-    ( 4,'Betis' );
-    
-    Insert into camp values
-    ( 100, 'Mestalla'),
-    ( 200, 'Camp Nou');
-    
-    Insert into jornada values
-    ( 10, '2017-01-01'),
-    ( 11, '2017-01-07'),
-    ( 12, '2017-01-14');
-    
-    Insert into partit 
-    (numero_jornada, camp_previst )
-    values
-    ( 10, 100 ),  --jornada 10 a Mestalla
-    ( 10, 200 ),  --jornada 10 al Camp Nou
-    ( 11, 100 ),
-    ( 11, 200 );
-    
-    Insert into equipJugaPartit
-    (NumeroJornada, campPrevist, equip, golsmarcats, esEquipLocal )
-    values
-    (10, 200, 1, 5, 1 ),  --Jornada 10 Barça-Madrid al Camp Nou
-    (10, 200, 2, 0, 0 ),
-    (10, 100, 3, 5, 0 ),  --Jornada 10 Espanyol-Bestis a Mestalla
-    (10, 100, 4, 0, 1 ),
-    (11, 100, 3, 0, 0 ),  --Jornada 11 Espanyol-Madrid a Mestalla
-    (11, 100, 4, 10, 1 );
-
-
-**Consultes**
-
-    :::sql
-    --Mitjana de gols marcats a Mestalla. (Subconsulta per saber id de 'Mestalla')
-    --amb subconsulta
-    select avg( CAST( j.golsmarcats as float) )
-    from equipJugaPartit j
-    where j.campPrevist = ( select id from camp where nom = 'Mestalla'  ) 
-    
-    --Mitjana de gols marcats a Mestalla. (Subconsulta per saber id de 'Mestalla')
-    --amb inner join
-    select avg( CAST( j.golsmarcats as float) )
-    from equipJugaPartit j 
-    inner join camp c on c.id = j.campPrevist
-    where c.nom= 'Mestalla'
-    
-    
-    --Mitjana de gols marcats per Equip.
-    select  e.nom, avg( CAST( j.golsmarcats as float) ) as 'Mitjana de gols'
-    from equipJugaPartit J
-    inner join equip e 
-       on j.equip = e.id
-    group by e.nom
-    
-    --Llistat d'equips que han jugat al 'CampNou'
-    select distinct e.nom
-    from equip e
-    inner join equipJugaPartit j
-       on e.id = j.equip
-    inner join camp c
-       on c.id = j.campPrevist
-    where 
-       c.nom = 'Camp Nou'
-    
-    --Llistat de partits disputats amb nom dels equips i resultat final.
-    select p.numero_jornada as 'Jornada',
-                c.nom as 'camp', 
-     e_local.nom as 'equip local', 
-     e_visitant.nom as 'equip visitant', 
-     j_local.golsmarcats, j_visitant.golsmarcats
-    from partit p
-    inner join equipJugaPartit j_local
-        on p.camp_previst = j_local.campPrevist 
-        and p.numero_jornada = j_local.NumeroJornada
-        and j_local.esEquipLocal  = 1
-    inner join equipJugaPartit j_visitant
-        on p.camp_previst = j_visitant.campPrevist 
-        and p.numero_jornada = j_visitant.NumeroJornada
-        and j_visitant.esEquipLocal  = 0
-    inner join equip e_local 
-        on j_local.equip = e_local.id
-    inner join equip e_visitant 
-        on j_visitant.equip = e_visitant.id
-    inner join camp c
-        on p.camp_previst = c.id
 Continguts: 
 
 Resultats d'aprenentatge: 
Restaurar
daniel herrera06/02/2017 20:03
@@ -19,7 +19,7 @@
 5. Llistat de partits disputats amb nom dels equips i resultat final.
 6. Mostra els equips que han marcat més de 3 gols (en tota la temporada)
 7. Mostra els partits amb més de 3 gols.
-8. Mostra els equips que han marcat més gols que la mitjana dels equips.
+8. Mostra els equips que han marcat més gols que la mitjana dels equips calculdada com la suma de tots els gols dividit pel número de partits.
 9. Mostra els camps on s'han marcat més gols de un 70% la mitjana de gols que es marquen per camp.
 
 **Solució by Power Designer**
Restaurar
daniel herrera06/02/2017 19:44
@@ -17,7 +17,7 @@
 3. Mitjana de gols marcats per Equip.
 4. Llistat d'equips que han jugat al 'CampNou'
 5. Llistat de partits disputats amb nom dels equips i resultat final.
-6. Mostra els equips que han marcat més de 3 gols.
+6. Mostra els equips que han marcat més de 3 gols (en tota la temporada)
 7. Mostra els partits amb més de 3 gols.
 8. Mostra els equips que han marcat més gols que la mitjana dels equips.
 9. Mostra els camps on s'han marcat més gols de un 70% la mitjana de gols que es marquen per camp.
Restaurar
daniel herrera06/02/2017 19:39
@@ -17,6 +17,10 @@
 3. Mitjana de gols marcats per Equip.
 4. Llistat d'equips que han jugat al 'CampNou'
 5. Llistat de partits disputats amb nom dels equips i resultat final.
+6. Mostra els equips que han marcat més de 3 gols.
+7. Mostra els partits amb més de 3 gols.
+8. Mostra els equips que han marcat més gols que la mitjana dels equips.
+9. Mostra els camps on s'han marcat més gols de un 70% la mitjana de gols que es marquen per camp.
 
 **Solució by Power Designer**
 
Restaurar
daniel herrera31/01/2017 19:48
@@ -107,6 +107,63 @@
     (10, 100, 4, 0, 1 ),
     (11, 100, 3, 0, 0 ),  --Jornada 11 Espanyol-Madrid a Mestalla
     (11, 100, 4, 10, 1 );
+
+
+**Consultes**
+
+    :::sql
+    --Mitjana de gols marcats a Mestalla. (Subconsulta per saber id de 'Mestalla')
+    --amb subconsulta
+    select avg( CAST( j.golsmarcats as float) )
+    from equipJugaPartit j
+    where j.campPrevist = ( select id from camp where nom = 'Mestalla'  ) 
+    
+    --Mitjana de gols marcats a Mestalla. (Subconsulta per saber id de 'Mestalla')
+    --amb inner join
+    select avg( CAST( j.golsmarcats as float) )
+    from equipJugaPartit j 
+    inner join camp c on c.id = j.campPrevist
+    where c.nom= 'Mestalla'
+    
+    
+    --Mitjana de gols marcats per Equip.
+    select  e.nom, avg( CAST( j.golsmarcats as float) ) as 'Mitjana de gols'
+    from equipJugaPartit J
+    inner join equip e 
+       on j.equip = e.id
+    group by e.nom
+    
+    --Llistat d'equips que han jugat al 'CampNou'
+    select distinct e.nom
+    from equip e
+    inner join equipJugaPartit j
+       on e.id = j.equip
+    inner join camp c
+       on c.id = j.campPrevist
+    where 
+       c.nom = 'Camp Nou'
+    
+    --Llistat de partits disputats amb nom dels equips i resultat final.
+    select p.numero_jornada as 'Jornada',
+                c.nom as 'camp', 
+     e_local.nom as 'equip local', 
+     e_visitant.nom as 'equip visitant', 
+     j_local.golsmarcats, j_visitant.golsmarcats
+    from partit p
+    inner join equipJugaPartit j_local
+        on p.camp_previst = j_local.campPrevist 
+        and p.numero_jornada = j_local.NumeroJornada
+        and j_local.esEquipLocal  = 1
+    inner join equipJugaPartit j_visitant
+        on p.camp_previst = j_visitant.campPrevist 
+        and p.numero_jornada = j_visitant.NumeroJornada
+        and j_visitant.esEquipLocal  = 0
+    inner join equip e_local 
+        on j_local.equip = e_local.id
+    inner join equip e_visitant 
+        on j_visitant.equip = e_visitant.id
+    inner join camp c
+        on p.camp_previst = c.id
 Continguts: 
 
 Resultats d'aprenentatge: 
Restaurar
daniel herrera31/01/2017 19:16
@@ -101,9 +101,9 @@
     Insert into equipJugaPartit
     (NumeroJornada, campPrevist, equip, golsmarcats, esEquipLocal )
     values
-    (10, 200, 1, 5, 1 ),  --Jornada 11 Barça-Madrid al Camp Nou
+    (10, 200, 1, 5, 1 ),  --Jornada 10 Barça-Madrid al Camp Nou
     (10, 200, 2, 0, 0 ),
-    (10, 100, 3, 5, 0 ),  --Jornada 11 Espanyol-Bestis a Mestalla
+    (10, 100, 3, 5, 0 ),  --Jornada 10 Espanyol-Bestis a Mestalla
     (10, 100, 4, 0, 1 ),
     (11, 100, 3, 0, 0 ),  --Jornada 11 Espanyol-Madrid a Mestalla
     (11, 100, 4, 10, 1 );
Restaurar
Consulteu nota legal i condicions