Soluciones

English version (when available)

T13.001- Crea una vista (VusuAli) de la delegación de Alicante que permite ver los usuarios de la provincia de Alicante (de código '03').

create or replace view VusuAli as select * from tiendaonline.usuario where provincia='03';
Como solo tienes permisos de lectura en TIENDAONLINE solo eso puedes hacer en esta vista, consultar.

T13.002- Con la vista anterior, lista a todos los usuarios de la provincia de Alicante ordenados por apellidos y nombre.

select * from VusuAli order by apellidos,nombre;


T13.003- Con la vista anterior, lista a todos los usuarios de la provincia de Alicante que se llamen Carolina o Iloveny, ordenados por apellidos y nombre.

select * from VusuAli where nombre in ('Carolina','Iloveny') order by apellidos,nombre;


T13.004- Usando la vista anterior elimina a los usuarios de la provincia de Alicante que se llamen Carolina o Iloveny.
¿Qué ha pasado? ¿Por qué?

delete from VusuAli where nombre in ('Carolina','Iloveny');
Esta orden falla.

T13.005- Intenta insertar mediante VusuAli (email,dni,apellidos,nombre,provincia,pueblo)
('rff20@gmial.es','11222333R','FERNANDEZ FERNANDEZ','ROSA','03','1225')
¿Qué ha pasado? ¿Por qué?

insert into VusuAli (email,dni,apellidos,nombre,provincia,pueblo) values 
('rff20@gmial.es','11222333R','FERNANDEZ FERNANDEZ','ROSA','03','1225');
Esta orden falla.

T13.006- Crea una vista que nos informe del total del importe a pagar por cada pedido en TIENDAONLINE y su fecha. Comprueba que nos da los datos correctos.

create or replace view Tpedidos as
select p.numpedido,sum(importe*cantidad) total,date(fecha)
from tiendaonline.pedido p, tiendaonline.linped l where p.numpedido=l.numpedido
group by p.numpedido,fecha;

select p.numpedido,sum(importe*cantidad) total,date(fecha)
from tiendaonline.pedido p, tiendaonline.linped l where p.numpedido=l.numpedido
group by p.numpedido,fecha;

select * from Tpedidos;


T13.007- Usando las vistas previas, lista todos los email, nombre y apellidos de usuario de Alicante, y si tienen pedidos también el total pagado por el usuario.

select u.email,u.apellidos,u.nombre,pp.pagado
from usuAli u
left join
(
    select usuario, sum(total) pagado
    from tiendaonline.pedido p -- necesitamos esta tabla porque la vista Tpedidos no incluye la información de usuario
    join Tpedidos t on p.numpedido=t.numpedido
    group by usuario
) pp
on pp.usuario=u.email
Si la vista no tiene toda la información necesaria, no hay problema en recurrir a las tablas del esquema de base de datos de trabajo.

T13.008- Crea otra vista que nos informe del total del importe a pagar por cada pedido hecho por usuarios de Alicante y su fecha (solo fecha).

create or replace view TApedidos as
select p.numpedido,sum(importe*cantidad) total,date(fecha),usuario  
from tiendaonline.pedido p join tiendaonline.linped l on p.numpedido=l.numpedido 
where usuario in (select email from tiendaonline.usuario where provincia='03')
group by p.numpedido,date(fecha),usuario;
En esta vista estamos limitando la información a únicamente pedidos hechos por usuarios de Alicante.

T13.009- Usando la nueva vista previa, modifica tu solución anterior y lista todos los email, nombre y apellidos de usuario, y si tienen pedidos también el total pagado por el usuario.

select u.email,u.apellidos,u.nombre,pp.total
from usuAli u
left join TApedidos pp
on pp.usuario=u.email;
El objetivo de estos ejercicios es que te des cuenta de lo que puedes añadir a una vista en función de la consulta que quieras diseñar y los requisitos de seguridad que pudieras querer imponer a los que usen esas vistas.

Fíjate que no hemos necesitado filtrar nada, las vistas ya trabajan únicamente con datos de usuarios de Alicante.


T13.010- Crea en tu base de datos una tabla con la misma estructura que ""TIENDAONLINE.USUARIO"". Tu tabla se llamará ""usuAlm"".
CREATE TABLE nombretabla LIKE otratabla crea una nueva tabla (nombretabla) con las columnas de otratabla. Lo único que no se crea en nombretabla son las claves ajenas.
Crea una vista ""VusuAlm"" que permita gestionar (altas, bajas, modificaciones y consultas) a los usuarios de Almería (código '04') y de ninguna otra.

create table usuAlm like tiendaonline.usuario; -- esto crea una tabla idéntica excepto en las claves ajenas que, para añadirlas, habría que usar ALTER TABLE
create view VusuAlm as select * from usuAlm where provincia='04' with check option; -- CHECK OPTION es necesario para evitar que se puedan insertar usuarios de otras provincias
Tanto la tabla base como la vista pertenecen a tu base de datos y, por tanto, tienes todos los permisos necesarios para trabajar con ellas.

Podemos pensar que el objetivo es permitir la gestión de nuestra tabla USUARIO mediante la vista, eso nos asegura que solo se podrán manejar datos de usuarios de Almería.


T13.011- Intenta insertar mediante VusuAlm (email,dni,apellidos,nombre,provincia,pueblo)
('rgg2@gmial.es','11222333R','GOMEZ GOMEZ','ROSA','04','1002')
¿Qué ha pasado? ¿Por qué?

insert into VusuAlm (email,dni,apellidos,nombre,provincia,pueblo) values 
('rgg2@gmial.es','11222333R','GOMEZ GOMEZ','ROSA','04','1002');


T13.012- Utilizando VusuAlm elimina todas las posibles filas de usuarios que tengas e inserta en tu tabla USUARIO a estos 2 usuarios (email,dni,apellidos,nombre,provincia,pueblo):
('rff20@gmial.es','11222333R','FERNANDEZ FERNANDEZ','ROSA','04','0530')
('jmm119@gmial.es','22333444T','MARTINEZ MARTINEZ','JULIA','04','1002')

delete from VusuAlm;

insert into VusuAlm (email,dni,apellidos,nombre,provincia,pueblo) values 
('rff20@gmial.es','11222333R','FERNANDEZ FERNANDEZ','ROSA','04','0530'),
('jmm119@gmial.es','22333444T','MARTINEZ MARTINEZ','JULIA','04','1002');


T13.013- Intenta insertar mediante VusuAlm (email,dni,apellidos,nombre,provincia,pueblo)
('rff20@gmial.es','11222333R','FERNANDEZ FERNANDEZ','ROSA','03','1225')
¿Qué ha pasado? ¿Por qué?

insert into VusuAlm (email,dni,apellidos,nombre,provincia,pueblo) values 
('rff20@gmial.es','11222333R','FERNANDEZ FERNANDEZ','ROSA','03','1225');


T13.014- Vas a montar la delegación de Almería. En TIENDAONLINE no hay ningún usuario de esa provincia. La tabla usuarios ya la tienes aunque sin clave ajena a localidad.
ALTER TABLE usuario ADD FOREIGN KEY (pueblo, provincia) REFERENCES tiendaonline.localidad (codm,provincia);
La orden anterior añade dicha clave ajena. Fíjate que hace referencia la tabla LOCALIDAD en TIENDAONLINE. Eso quiere decir que no necesitas esta tabla en tu base de datos, la vas a manejar mediante vistas.
Debes decidir qué tablas necesitas en tu base de datos y cuáles solo vas a utilizar como referencia desde TIENDAONLINE —y no necesitas recrearlas—. Las tablas que necesitas son todas aquellas que necesitas para añadir información de qué compran tus usuarios.
Ya tienes 2 usuarios, monta algunos pedidos y cestas para alguno de ellos.

No se proporciona solución.


T13.015- Tienes a tus usuarios de Almería y acceso al resto de usuarios de TIENDAONLINE. Lista el email de todos los usuarios, los de Almería y todos los demás, apellidos, nombre, y nombres de la localidad y provincia en la que viven.

select email,apellidos,u.nombre,p.nombre,l.pueblo
from tiendaonline.localidad l, tiendaonline.provincia p,
(
  select email,apellidos,nombre,provincia,pueblo from VusuAlm
  UNION
  select email,apellidos,nombre,provincia,pueblo from tiendaonline.usuario
) u
where u.provincia=l.provincia and u.pueblo=l.codm
  and l.provincia=p.codp;


T13.016- Sobre tu base de datos, redefine TApedidos y muestra todos los email, nombre y apellidos de usuario —en tu caso de Almería— y si tienen pedidos también el total pagado por el usuario.

create or replace view TApedidos as
select p.numpedido,sum(importe*cantidad) total,date(fecha),usuario  
from pedido p join linped l on p.numpedido=l.numpedido 
where usuario in (select email from VusuAlm)
group by p.numpedido,date(fecha),usuario;