Actividad Final SQL Bases de Datos.

a)    Enunciado:

Una empresa de consultoría de aplicaciones móviles desea poder realizar un informe sobre las descargas de aplicaciones de “iphone” en sus dispositivos.
Para ello necesita saber, qué móvil se ha bajado algo (IMEI), qué modelo de dispositivo es, qué versión tiene, y de qué color es.
Además también quiere almacenar qué aplicación ha sido descargada (nombre), qué tamaño tiene y su coste.
Para finalizar, necesitaría conocer la fecha de descarga de la misma, la cual genera un número automático de orden de descarga.

b)    Diseño Entidad Relación:

c)    Diseño SQL:

create table Iphone(imei varchar(5), modelo varchar (2), version int, color varchar(6), primary key (imei))ENGINE=INNODB;

create table aplicacion(nombre varchar (10), size int, precio int, primary key (nombre))ENGINE=INNODB;

create table descarga(numero int not null auto_increment, imeimovil varchar(5), nombreap varchar (10), fecha date, primary key(numero), index(nombreap), foreign key (imeimovil) references iphone(imei), foreign key (nombreap) references aplicacion(nombre))ENGINE=INNODB;

d)    Pantallazo de las tablas con sus datos:
TABLA IPHONE:
Insert into Iphone values ('11111', '4', 6, 'BLANCO');
Insert into Iphone values ('22222', '4s', 5, 'NEGRO');
Insert into Iphone values ('33333', '4s', 6, 'BLANCO');
Insert into Iphone values ('44444', '5', 6, 'BLANCO');
Insert into Iphone values ('55555', '4s', 6, 'NEGRO');
Insert into Iphone values ('66666', '4', 5, 'NEGRO');
Insert into Iphone values ('77777', '4', 5, 'NEGRO');
Insert into Iphone values ('88888', '5', 6, 'NEGRO');
Insert into Iphone values ('99999', '5', 6, 'NEGRO');

TABLA APLICACION:

insert into aplicacion    values ("instagram", 1, 2);
insert into aplicacion    values ("twitter", 2, 1);
insert into aplicacion    values ("triviados", 5, 3);
insert into aplicacion    values ("candycrush", 6, 5);
insert into aplicacion    values ("shazam", 2, 2);
insert into aplicacion    values ("youtube", 4, 1);
insert into aplicacion    values ("runtastic", 9, 2);
insert into aplicacion    values ("facebook", 3, 1);

TABLA DESCARGA:
insert into descarga (imeimovil, nombreap, fecha) values ('33333', 'facebook', '20130301');
insert into descarga (imeimovil, nombreap, fecha) values ('11111', 'twitter', '20130301');
insert into descarga (imeimovil, nombreap, fecha) values ('55555', 'twitter', '20130305');
insert into descarga (imeimovil, nombreap, fecha) values ('66666', 'facebook', '20130305');
insert into descarga (imeimovil, nombreap, fecha) values ('66666', 'runtastic', '20130306');
insert into descarga (imeimovil, nombreap, fecha) values ('88888', 'runtastic', '20130307');
insert into descarga (imeimovil, nombreap, fecha) values ('88888', 'facebook', '20130308');
insert into descarga (imeimovil, nombreap, fecha) values ('77777', 'facebook', '20130309');
insert into descarga (imeimovil, nombreap, fecha) values ('44444', 'candycrush', '20130310');
insert into descarga (imeimovil, nombreap, fecha) values ('22222', 'youtube', '20130311');
insert into descarga (imeimovil, nombreap, fecha) values ('22222', 'twitter', '20130312');
insert into descarga (imeimovil, nombreap, fecha) values ('44444', 'runtastic', '20130313');
insert into descarga (imeimovil, nombreap, fecha) values ('44444', 'facebook', '20130314');
insert into descarga (imeimovil, nombreap, fecha) values ('44444', 'twitter', '20130315');
insert into descarga (imeimovil, nombreap, fecha) values ('44444', 'instagram', '20130317');
insert into descarga (imeimovil, nombreap, fecha) values ('55555', 'triviados', '20130317');
insert into descarga (imeimovil, nombreap, fecha) values ('55555', 'runtastic', '20130317');
insert into descarga (imeimovil, nombreap, fecha) values ('88888', 'twitter', '20130317');
insert into descarga (imeimovil, nombreap, fecha) values ('77777', 'twitter', '20130317');
insert into descarga (imeimovil, nombreap, fecha) values ('66666', 'twitter', '20130320');
insert into descarga (imeimovil, nombreap, fecha) values ('55555', 'youtube', '20130320');
insert into descarga (imeimovil, nombreap, fecha) values ('99999', 'twitter', '20130321');
insert into descarga (imeimovil, nombreap, fecha) values ('99999', 'shazam', '20130321');
insert into descarga (imeimovil, nombreap, fecha) values ('55555', 'instagram', '20130321');
insert into descarga (imeimovil, nombreap, fecha) values ('55555', 'shazam', '20130322');
insert into descarga (imeimovil, nombreap, fecha) values ('33333', 'twitter', '20130323');
insert into descarga (imeimovil, nombreap, fecha) values ('33333', 'instagram', '20130324');
insert into descarga (imeimovil, nombreap, fecha) values ('33333', 'runtastic', '20130330');
insert into descarga (imeimovil, nombreap, fecha) values ('33333', 'candycrush', '20130330');
insert into descarga (imeimovil, nombreap, fecha) values ('99999', 'youtube', '20130330');

e)     Consultas sobre la base de datos. Deberás utilizar y realizar pantallazos de:
I.-Muestra por pantalla las descargas realizadas por un terminal 4s:
SELECT *
FROM descarga
JOIN Iphone ON Iphone.imei = descarga.imeimovil
WHERE Iphone.modelo = '4s'

II.-Muestra todas las descargas ordenadas por imei:
SELECT *
FROM descarga
ORDER BY imeimovil

III.-Muestra la cantidad de veces que se haya descargado cada una de las aplicaciones:
select count(d.nombreap), (a.nombre) from descarga as d join aplicacion as a on d.nombreap=a.nombre group by a.nombre;

IV.- Cuenta  todas las descargas realizadas ordenadas por modelo de iphone:
select count(d.nombreap), (i.modelo) from descarga as d join Iphone as i on d.imeimovil=i.imei group by i.modelo;

V.-Muestra por pantalla la cantidad de descargas realizadas por cada terminal (por imei):
SELECT COUNT( d.nombreap ) , i.imei FROM descarga AS d JOIN Iphone AS i ON d.imeimovil = i.imei GROUP BY i.imei

VI.-Muestra por pantalla todas las descargas realizadas de cada aplicación mostrando además un mensaje si la aplicación ha sido un éxito (hay más de 4 descargas) .
select count(d.nombreap), (a.nombre), if (count(d.nombreap)>4, 'exito', ' ') as 'exito o no' from descarga as d  join aplicacion as a on d.nombreap=a.nombre group by a.nombre

VII.-Muestra por pantalla el total de dinero gastado por dispositivo.
SELECT d.imeimovil, sum(a.precio)
from aplicacion as a
join descarga as d
on a.nombre=d.nombreap
group by d.imeimovil

VIII.- Muestra por pantalla todas las aplicaciones que empiecen por 't':
select * from aplicacion where nombre like 't%'

IX.-Muestra por pantalla las aplicaciones que hayan sido descargadas mas de tres veces:
select count(nombreap),nombreap from descarga  group by nombreap  having count(nombreap)>3;

X.-Muestra por pantalla todos aquellos terminales que se hayan descargado 'runtastic' o 'facebook', con su fecha de descarga.
select fecha, imeimovil, nombreap from descarga where nombreap='facebook' or nombreap='runtastic'

XI.- Actualiza los precios de 'shazam' a 5.
UPDATE aplicacion SET precio =5 WHERE nombre = 'shazam' 

select * from aplicación


XII.-Muestra por pantalla todas las aplicaciones aunque no hayan sido descargadas ninguna vez:
Para realizar un right join necesito agregar una nueva línea en la tabla aplicación:
insert into aplicacion    values ("amazon", 10, 7);
SELECT *
FROM aplicacion AS a
LEFT JOIN descarga AS d ON a.nombre = d.nombreap