domingo, 17 de abril de 2011

Soluciones T10

T10.001- ¿Hay alguna fila en la tabla marca?
select exists (select * from marca)

T10.002- Email y nombre de los usuarios que no han pedido ninguna cámara.
select u.email, u.nomb
from usuario u
where not exists (select 1 from camara c,linped l, pedido p
where p.numPedido=l.numPedido and c.cod=l.articulo and u.email=p.usuario);

T10.003- Email y nombre de los usuarios que, habiendo realizado algún pedido, no han pedido ninguna cámara.
select u.email, u.nombre
from usuario u
where exists (select 1 from pedido p where u.email=p.usuario) and
not exists (select 1 from camara c,linped l, pedido p
where p.numPedido=l.numPedido and c.cod=l.articulo and u.email=p.usuario);

T10.004- Código y nombre del artículo que ha sido incluído en todos los pedidos.
select a.cod, a.nombre
from articulo a
where not exists (select 1 from pedido p where
not exists (select 1 from linped l where
l.numpedido=p.numpedido and l.articulo=a.cod));

T10.005- Código y nombre de los artículos que han sido solicitados en todos los pedidos del usuario acm@colegas.com.
select a.cod, a.nombre
from articulo a
were not exists (select 1 from pedido p where p.usuario='acm@colegas.com'
and not exists ( select 1 from linped l where p.numPedido=l.numPedido
and l.articulo=a.cod));

T10.006- ¿Hay alguna fila en la tabla marca? Si la respuesta es positiva, que muestre la palabra "sí".
select 'sí' respuesta
from dual
where exists (select 1 from marca);

T10.007- ¿Hay alguna fila en la tabla memoria? Si la respuesta es negativa, que muestre la palabra "no".
select 'no' respuesta
from dual
 where not exists (select 1 from memoria);

T10.008- Pedidos que incluyen cámaras y televisiones.
select * from pedido p
where exists (select 1 from linped l where l.numpedido=p.numpedido
and articulo in (select cod from tv))
and exists (select 1 from linped l where l.numpedido=p.numpedido
and articulo in (select cod from camara));

T10.009- Pedidos que incluyen cámaras y objetivos.
select * from pedido p
where exists (select 1 from linped l where l.numpedido=p.numpedido
and articulo in (select cod from camara))
and exists (select 1 from linped l where l.numpedido=p.numpedido
and articulo in (select cod from objetivo));

Soluciones T09

T09.001- ¿Cuántos artículos de cada marca hay?
select marca, count(*)
from articulo
group by marca;

T09.002- ¿Cuáles son las marcas que tienen menos de 150 artículos?
select marca, count(*)
from articulo
group by marca
having count(*)<150;

T09.003- ¿Cuáles son las marcas que tienen menos de 150 artículos (eliminar las marcas que sean null)?
select marca, count(*)
from articulo
where marca is not null
group by marca
having count(*)<150;

T09.004- Número de cámaras que tienen sensor CMOS
select count(*)
from camara
where sensor like 'CMOS%';

T09.005- Dni, nombre, apellidos y email de los usuarios que han realizado más de un pedido.
select dni, nombre, apellidos, email
from usuario u, pedido p
where u.email = p.usuario
group by dni,nombre,apellidos,email
having count(*)>1;

T09.006- Pedidos (número de pedido y usuario) de importe mayor a 4000 euros.
select p.numpedido,p.usuario  from pedido p, linped l  where p.numpedido=l.numpedido
group by p.numpedido,p.usuario
having sum(cantidad*precio)>4000; 

T09.007- Pedidos (número de pedido y usuario) con más de 10 artículos, mostrando esta cantidad.
select p.numPedido, p.usuario, sum(cantidad) from pedido p, linped l
                where P.numPedido=L.numPedido
group by p.numPedido,p.usuario
having sum(cantidad)>10;

T09.008- Pedidos (número de pedido y usuario) que contengan más de cuatro artículos distintos.
select p.numPedido,p.usuario,
count(distinct articulo)  from pedido p, linped l
 where p.numPedido=l.numPedido
group by p.numPedido,p.usuario
having count(distinct articulo)>4;

 T09.009- ¿Hay dos provincias que se llamen igual (con nombre repetido)?
select nombre,count(*)
from provincia
group by nombrehaving count(*)>1;

T09.010- ¿Hay algún pueblo con nombre repetido?
select pueblo,count(*)
from localidad group by pueblo
having count(*)>1; 

T09.011- Obtener el código y nombre de las provincias que tengan más de 100 pueblos.
select P.codp,P.nombre,count(*)
from provincia P, localidad L WHERE P.codp=L.provincia
group by P.codp,P.nombre
having count(*)>100;

T09.012- Ha habido un error en Tiendaonline y se han colado varios artículos sin stock en la cesta. Averigua el código de esos artículos y las veces que aparecen en la cesta. 
select c.articulo, count(c.articulo) from cesta c, stock s
where c.articulo=s.articulo              
and disponible=0  group by c.articulo;

T09.013- Clientes que hayan adquirido (pedido) más de 2 tv
select p.usuario, sum(cantidad)
from pedido p, linped l,articulo a, tv t
where p.numpedido=l.numpedido
and l.articulo=a.cod           
and a.cod=t.cod group by p.usuario having sum(cantidad)>2;

T09.014- ¿Cuántas veces se ha pedido cada artículo?. Si hubiesen artículos que no se han incluido en pedido alguno también se mostrarán. Mostrar el código y nombre del artículo junto con las veces que ha sido incluido en un pedido (solo si ha sido incluido, no se trata de la "cantidad").
select cod, nombre, count(numpedido)
from articulo a left join linped l on (a.cod=l.articulo)
group by cod, nombre;

T09.015- Código y nombre de las provincias que tienen más de 50 usuarios (provincia del usuario no de la dirección de envío).
select p.codp, p.nombre from provincia p, usuario u
where p.codp=u.provincia
group by p.codp, p.nombre
having count(email)>50;

T09.016- Cantidad de artículos con stock 0
select count(*)
from stock where disponible=0;

T09.017- Cantidad de artículos que no son ni memoria, ni tv, ni objetivo, ni cámara ni pack.
select  count(*)
from articulo
where cod not in (select cod from camara) 
and cod not in (select cod from tv)                
and cod not in (select cod from memoria)   
and cod not in (select cod from objetivo)    
and cod not in (select cod from pack);

T09.018- Número de artículos pedidos por provincia (provincia del usuario no de la dirección de envío). Mostrar el código de la provincia, su nombre y la cantidad de veces que se ha pedido el artículo; si la provincia no tiene asociada esta cantidad, mostrar "0" en esa columna.
select pr.codp, pr.nombre,
IFNULL(sum(cantidad),0)
from provincia pr               
left join usuario u on (pr.codp=u.provincia)                 left join pedido p on (p.usuario=u.email)     
left join linped l on (p.numpedido=l.numpedido)group by pr.codp,pr.nombre;

Soluciones T08


T08.001- Obtener el precio total por línea para el pedido 1, en la salida aparecerá los campos numlinea, articulo y el campo calculado total. 
                select linea, articulo, (precio * cantidad)
                from linped
                where numPedido = 1;
T08.002- Obtener la cantidad de provincias distintas de las que tenemos conocimiento de algún usuario.
                select count(distinct provincia)
                from usuario;

T08.003-
 Cantidad de usuarios de nuestra BD.
                select count(*)
                from usuario;

T08.004- Número de articulos con precio de venta mayor de 200 euros.
                select count(*)
                from articulo
                where pvp > 200;

T08.005- Total en euros de la cesta del usuario "bmm@agwab.com".
select sum(ar.pvp)
 from cesta ce, articu
 where (ce.usuario = 'bmm@agwab.com')
and (ce.articulo = ar.cod);

T08.006- Tamaño máximo de pantalla para las televisiones.
                select MAX(pantalla)
                from tv;

T08.007- Media de precios de venta al público distintos de los articulos, redondeada a dos decimales.
                select round(avg(distinct pvp),2)
                from articulo;

T08.008- Nombre y precio de los articulos con el mínimo stock disponible.
select ar.nombre,ar.pvp
 from articulo ar, stock sto
 where (ar.cod = sto.articulo)
 and disponible = (select min(disponible) from stock);

T08.009- Número de pedido, fecha y nombre y apellidos del usuario de las lineas de pedido cuyo total en euros es el más alto.
                select p.numPedido,fecha,nombre,apellidos
                from pedido p,linped l,usuario u
                where p.usuario=email
                and p.numPedido=l.numPedido
                and (cantidad*precio)=(select max(cantidad*precio) from linped);

T08.010- Máximo, mínimo y media de precio de venta de los artículos.
                select MAX(pvp) max_Pvp, MIN(pvp) min_Pvp, AVG(pvp) media_Pvp
                from articulo; 

T08.011- Código, nombre, pvp y fecha de incorporación del artículo a la cesta más reciente.
                select cod, nombre, pvp
                from cesta, articulo
                where articulo=cod and fecha=(select MAX(fecha) from cesta);

T08.012- Cantidad de artículos que están descatalogados.
                select count(*) Num_Art_Desc
                from stock
                where entrega='Descatalogado'

T08.013-
 Precio máximo del artículo en stock que será entregado próximamente.
                select max(pvp)
                from articulo, stock
                where cod=articulo
                and entrega='Próximamente';

T08.014- Nombre, código y disponible en stock para todos los artículos cuyo código acabe en 3, siendo ese disponible el mínimo de toda la tabla.
select nombre, cod, disponible MinDisp
                from stock, articulo
                where articulo=cod and cod like '%3'
                and disponible=(select MIN(disponible) from stock);

T08.015- Precio máximo, mínimo y medio en los pedidos que incluyen el artículo “Bravia KDL-32EX402”
                select MAX(precio) Max_Prec, MIN(precio) Min_Prec, AVG(precio) Prec_Med
                from linped l, articulo a
                where a.cod=l.articulo and a.nombre='Bravia KDL-32EX402';

T08.016- Cantidad total que se ha pedido de los artículos cuyo nombre empieza por "UE22".
                select SUM(cantidad) cant_Tot
                from linped, articulo
                where articulo=cod and nombre like 'UE22%';

T08.017- Precio medio de los artículos incluidos en la línea de pedido número 4, redondeado a 3 decimales.
select round(avg(precio),3) PrecioMedio
from linped
where linea=4;

T08.018- Número de pedido, nombre, teléfono y email de usuario del pedido (o los pedidos) que contiene líneas de pedido cuyo precio sea igual al precio más alto de entre todas las segundas líneas de todos los pedidos.
select l.numPedido, nombre, telefono, email
from usuario u, pedido p, linped l
where l.numPedido=p.numPedido
                and p.usuario=u.email
                and precio=(select MAX(precio) from linped where linea=2);

T08.019- Diferencia entre el precio máximo y el precio mínimo del pedido número 30.
select (MAX(precio)-MIN(precio)) Diff
from linped
where numPedido=30;

T08.020- Código, nombre, precio de venta del artículo que más hay en stock.
select cod,nombre,pvp
from articulo,stock
where cod=articulo and disponible = (select MAX(disponible) from stock);

T08.021- Fecha de nacimiento del usuario más viejo.
                select MIN(nacido) from usuario;    


Soluciones T07

T07.001- Crea las siguientes tablas:
TA (a int, b int) CP(a) create table TA (a int, b int, primary key (a)) engine=innodb 
TB (c int, d int) CP(c) CAj(d) >> TA (borrados: propagar, modificaciones: propagar)
create table TB (c int, d int, primary key (c), foreign key (d) references TA (a) on delete cascade on update cascade ) engine=innodb
TC (e int, f int) CP(e) CAj(f) >> TB (borrados: propagar, modificaciones: propagar)
create table TC (e int, f int, primary key (e), foreign key (f) references TB (c) on delete cascade on update cascade)engine=innodb

T07.002- Inserta los siguientes datos
TA(1,10) insert into TA value ('1','10') 
TA(2,20) insert into TA value ('2','20')
TA(3,30) insert into TA value ('3','30')
TB(100,1) insert into TB value ('100','1')
TB(200,1) insert into TB value ('200','1')
TB(300,2) insert into TB value ('300','2')
TB(400,NULL) insert into TB value ('400',NULL)
TC(1000,100) insert into TC value ('1000','100')
TC(2000,100) insert into TC value ('2000','100')
TC(3000,NULL) insert into TC value ('3000',NULL)

T07.003- Borra TA(2,20) y comprueba los cambios que se han producido en las 3 tablas.
delete from TA where a=2 and b=20

T07.004- Modifica TA(1,10) a TA(15,10) y comprueba los cambios que se han producido en las 3 tablas.
update TA set a=15 where a=1
               
T07.005- Borra TC(2000,100) y comprueba los cambios que se han producido en las 3 tablas.
delete from TC where e=2000 and f=100

T07.006- Borra TA(3,30) y comprueba los cambios que se han producido en las 3 tablas.
delete from TA where a=3 and b=30

T07.007- Borra TB(100,15) y comprueba los cambios que se han producido en las 3 tablas.
delete from TB where c=100 and d=15

T07.008- Borra TC(3000,NULL) y comprueba los cambios que se han producido en las 3 tablas.
delete from TC where e=3000

T07.009- Borra TB(400,NULL) y comprueba los cambios que se han producido en las 3 tablas.
delete from TB where c=400

T07.010- Borra TA(15,10) y comprueba los cambios que se han producido en las 3 tablas: ¿Están vacias? Si, lo están.
delete from TA where a=15 and b=10

T07.011- Vuelve a crear las tablas:
TA (a int, b int) CP(a)
TB (c int, d int) CP(c) CAj(d) >> TA 
(borrados: anular, modificaciones: anular)
TC (e int, f int) CP(f) CAj(f) >> TB 
(borrados: anular, modificaciones: anular)
drop table TC
drop table TB
drop table TA
create table TA(a int, b int, primary key (a)) engine=innodb
create table TB(c int, d int, primary key (c), foreign key (d) references TA(a) on delete set null on update set null) engine=innodb
create table TC(e int, f int, primary key(e), foreign key (f) references TB(c) on delete set null on update set null) engine=innodb

T07.012- Vuelve a rellenar las tablas:
TA(1,10) insert into TA value ('1', '10')
TA(2,20) insert into TA value ('2', '20')
TA(3,30) insert into TA value ('3', '30')
TB(100,1) insert into TB value ('100', '1')
TB(200,1) insert into TB value ('200', '1')
TB(300,2) insert into TB value ('300', '2')
TB(400,NULL) insert into TB value ('400',NULL)
TC(1000,100) insert into TC value ('1000', ‘100’)
TC(2000,100) insert into TC value ('2000', ‘100’)
TC(3000,NULL) insert into TC value ('3000', NULL)

T07.013- Ejecuta las siguientes órdenes:
Borra TA(2,20)
delete from TA where a=2 and b=20
Modifica TA(1,10) a TA(15,10)
 update TA set a=15, b=10 where a=1 and b=10
Modifica TB(100,NULL) a TB(150,NULL)
update TB set c=150 where c=100
¿Queda algún valor de clave ajena distinto de NULL?
d y f.

T07.014- Vuelve a crear las tablas:
TA (a int, b int) CP(a)
TB (c int, d int) CP(c) CAj(d) >> TA (borrados: propagar)
TC (e int, f int) CP(f) CAj(f) >> TB (modificaciones: anular)
drop table TC
drop table TB
drop table TA
create table TA(a int, b int, primary key (a)) engine=innodb
create table TB(c int, d int, primary key (c), foreign key (d) references TA(a) on delete cascade) engine=innodb
create table TC(e int, f int, primary key (e) , foreign key (f) references TB(c) on update set null) engine=innodb 
TA(1,10) insert into TA value ('1', '10')
TA(2,20) insert into TA value ('2', '20')
TA(3,30) insert into TA value ('3', '30')
TB(100,1) insert into TB value ('100', '1')
TB(200,1) insert into TB value ('200', '1')
TB(300,2) insert into TB value ('300', '2')
TB(400,NULL) insert into TB value ('400',NULL)
TC(1000,100) insert into TC value ('1000', ‘100’)
TC(2000,100) insert into TC value ('2000', ‘100’)
TC(3000,NULL) insert into TC value ('3000', NULL)

T07.015- Borra TA(1,10): ¿qué ha pasado? No se borra porque a es una clave ajena de la tabla TB y tiene elementos que están relacionados con TA(1,10).
delete from TA where a=1 and b=10

T07.016- Borra TA(2,20): ¿qué ha pasado? No se borra porque a es una clave ajena de la tabla TB y tiene elementos que están relacionados con TA(2,20)
delete from TA where a=2 and b=20

T07.017- Modifica TA(1,10) a TA(17,10): ¿qué ha pasado? No se modifica porque a es una clave ajena de la que depende b, y b no tiene asignado que pasaría si borrara algo de lo que depende, por tanto, rechaza.
update TA set a=17, b=10 where a=1 and b=10

T07.018- Vuelve a intentar borrar TA(1,10): ¿por qué ahora sí?
delete from TA where a=1 and b=10

Soluciones T06

T06.001- Crea una tabla de nombre XX con 2 columnas, col1 de tipo integer, y col2 de tipo char(3), con col1 como clave primaria.
create table XX ( col1 integer, col2 char(3),
primary key (col1))
engine = innodb;

T06.002- Consulta la tabla.
select * from XX;

T06.003- Inserta en la tabla la fila (1,’AA’).
insert into XX values(1,'AA');

T06.004- inserta en la tabla la fila ('BB',2).
insert into XX(col2,col1) values('BB', 2)

T06.005- Inserta en la tabla la fila (2,’BB’).
insert into XX values(2,'BB');

T06.006- Consulta la tabla XX.
select * from XX;

T06.007- Cierra la sesión e identifícate de nuevo (“salte y vuelve a entrar” o "desconecta" y "conecta"). A continuación consulta de nuevo XX.
Podemos  comprobar que la tabla creada con su datos siguen ahí; es decir, cerrar la sesión no borra la tabla. Por ello solo lo podremos hacer con el “drop table”

T06.008- Borra la tabla XX
drop table XX;

T06.009- Crea una tabla YY con 3 columnas
col1(integer),
col2(char(2)) y
col3(varchar(10)),
y con clave primaria (col1, col2).
drop table if exists YY;
create table YY (col1 integer, col2 char(2), col3 varchar(10), primary key (col1, col2)) engine=innodb;

T06.010-  Inserta los siguientes datos y consulta la tabla para ver los datos almacenados
(1,'AA','primera')
(2,'AA','segunda')
(2,'BB','tercera')à No lo introduce porque se repite el ‘2’.
(1,'AA','cuarta')à  No lo introduce porque se repite el ‘1’
(NULL,NULL,'quinta')à No lo introduce porque col1 es inter.
(NULL,'CC','sexta')à Como antes.
(3,NULL,'séptima')à No lo introduce porque NULL es mayor que char (2).
(0,'','octava') --0, cadena vacía, 'octava' à
(3,'AA',NULL) 

T06.011- Ejecuta lo siguiente:
create table T1(a int,b int,c int,
primary key(a)) engine innodb;
create table T2(a int,d int,e int,
primary key(d),foreign key(a) references T1(a))
engine innodb;
y comprueba, buscando el porqué en caso de fallo, el resultado de cada
una de las órdenes de la siguiente secuencia:
a) insertar en T1(1,10,100).
Insert into T1 values (1,10,100);
b) insertar en T1(NULO,20,NULO).
Como es clave principal no puede ser nula
c) insertar en T1(2,20,NULO)
Error al introducir un char en un int;
d) insertar en T1(3,NULO,300)
error al introducir un char en un int;
e) insertar en T2(2,NULO,NULO)
Como la b.
f) insertar en T2(2,20,NULO)
Dará error al introducir un char en un int;
g) insertar en T1(1,20,200)
insert into T1 values (1, 20, 200);
h) insertar en T2(4,10,100)
insert into T2 values (4, 10, 100);
i) insertar en T2(2,30,230)
insert into T2 values (2, 30, 230);

T06.012- Continúa el anterior
j) modificar T1(1,10,100) a (2,10,100)
update T1 set a=2;
k) modificar T1(1,10,100) a (5,10,100)
update T1 set a=5;
l) modificar T2(2,20,NULO) a (2,20,220)
No puede ser NULO.
m) modificar T2(2,20,220) a (5,20,220)
update T2 set a=5;
n) modificar T2(5,20,220) a (2,10,100)
update T2 set a=2, b=10, c=100;
o) modificar T1(2,20,200) a (6,60,600)
update T1 set a=6, b=60, c=600;
p) modificar T1(3,NULO,300) a (7,70,700)
No puede ser NULO.
q) modificar T2(2,10,100) a (7,10,100)
update T2 set a=7;
r) modificar T2(2,30,230) a (7,30,230)
update T2 set a=7;
s) modificar T1(2,20,NULO) a (6,60,600)
No puede ser NULO.

T06.013- Continúa el anterior
Primero habría que crear las columnas o modificar la que tenemos.
t) borrar T2(7,30,230)
delete from T2 where a=7 and b=30 and c=230;
u) borrar T1(7,70,700)
delete from T2 where a=7 and b=30 and c=230;
v) borrar T1(5,10,100)
delete from T2 where a=7 and b=30 and c=230;
w) borrar T2(7,10,100)
delete from T2 where a=7 and b=10 and c=100;
x) borrar T1(7,70,700)
delete from T1 where a=7 and b=70 and c=700;
y) borrar T1(6,60,600)
delete from T1 where a=6 and b=60 and c=600;