T13 Soluciones a los ejercicios

Soluciones

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)
('yoyo@gmial.es','99888777R','FERNANDEZ FERNANDEZ','ROSA','04','1225')
¿Qué ha pasado? ¿Por qué?

insert into VusuAli (email,dni,apellidos,nombre,provincia,pueblo) values 
('yoyo@gmial.es','99888777R','FERNANDEZ FERNANDEZ','ROSA','04','1225'); -- esta orden fallará porque no tenemos permisos de inserción en la base de datos tiendaonline.

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;

--comprobamos que la consulta y la vista obtienen el mismo resultado
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 VusuAli 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 VusuAli 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 usuAlm LIKE tiendaonline.usuario

La orden anterior crea una nueva tabla (usuAlm) con las columnas de tiendaonline.usuario y la clave primaria, pero sin claves ajenas, si las hubiere. Si las necesitas, debes usar ALTER TABLE. La nueva tabla no tiene filas aún.

Cuando tengas la tabla anterior, 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');
-- hemos podido insertar porque la tabla base (usuAlm) está en nuestra base de datos, y tenemos permiso para ello

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)
('tutu@gmial.es','00999000T','LOPEZ DE LOPEZ','ANA','03','1225')
¿Qué ha pasado? ¿Por qué?

insert into VusuAlm (email,dni,apellidos,nombre,provincia,pueblo) values 
('tutu@gmial.es','00999000T','LOPEZ DE LOPEZ','ANA','03','1225');
-- Estamos intentando insertar una usuaria de Alicante ('03'), pero la vista no permite otra provincia que Almeria ('04') por el CHECK OPTION

T13.014- Vas a montar la delegación de Almería. En TIENDAONLINE no hay ningún usuario de esa provincia. Ya tienes 2 usuarios (ejercicio 12), inserta algunos pedidos para alguno de ellos.

La tabla usuAlm ya la tienes aunque sin clave ajena a localidad.

ALTER TABLE usuAlm 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é otras tablas necesitas en tu base de datos y cuáles solo vas a utilizar como referencia desde TIENDAONLINE —y no necesitas recrearlas—.

ALTER TABLE usuAlm ADD FOREIGN KEY (pueblo, provincia) REFERENCES tiendaonline.localidad (codm,provincia);

create table pedidosAlm like tiendaonline.pedido;
alter table pedidosAlm add foreign key (usuario) references usuAlm(email);

create table lineasAlm like tiendaonline.linped;
alter table lineasAlm add foreign key (numpedido) references pedidosAlm(numpedido);
alter table lineasAlm add foreign key (articulo) references tiendaonline.articulo(cod);

insert into pedidosAlm (numpedido, usuario, fecha) values (1,'rff20@gmial.es',now());
insert into lineasAlm (numpedido,linea,articulo,cantidad,importe) values
(1,1,'A0685',10,100),(1,2,'A1234',2,10);

-- comprobamos los datos
select * from usuAlm;
select * from pedidosAlm;
select * from lineasAlm;
select * from cestasAlm;

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

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- De tus datos almacenados en tu base de datos, crea una vista TApedidosAlm que muestre número de pedido, usuario, fecha, y total pagado por cada pedido. Serán los pedidos hechos en la delegación de Almería.

create or replace view TApedidosAlm as
select p.numpedido,sum(importe*cantidad) total,date(fecha),usuario  
from pedidosAlm p join lineasAlm l on p.numpedido=l.numpedido 
group by p.numpedido,date(fecha),usuario;

-- comprobando
select * from TApedidosAlm;

T13.017- Aprovecha VusuAlm y TApedidosAlm para mostrar a todos los usuarios de Almeria (email, nombre, y apellidos) y, si tienen pedidos, también número de pedido y el total pagado por cada uno de sus pedidos.

select email, nombre, apellidos, p.numpedido,total
from VusuAlm u 
left join TApedidosAlm p on u.email=p.usuario;