Solutions

Español

T13.001- Create a view (VusuAli) of the delegation of Alicante that allows users to see the province of Alicante (code '03').

create or replace view VusuAli as select * from OnlineStore.uuser where province='03';

T13.002- With the previous view, list all users of the province of Alicante sorted by last name and name.

select * from VusuAli order by surnames,name;

T13.003- With the previous view, list all users in the province of Alicante who are called Carolina or Iloveny, sorted by last name and name.

select * from VusuAli where name in ('Carolina','Iloveny') order by surnames,name;

T13.004- Using the previous view eliminates users from the province of Alicante who are called Carolina or Iloveny.
What happened? Why?

delete from VusuAli where name in ('Carolina','Iloveny');

T13.005- Try inserting via VusuAli (email, name, surname, first name, province, town)
('yoyo@gmial.es','99888777R', 'FERNANDEZ FERNANDEZ', 'ROSA', '04', '1225')
What happened? Why?

insert into VusuAli (email,dni,surnames,name,province,town) values 
('yoyo@gmial.es','99888777R','FERNANDEZ FERNANDEZ','ROSA','04','1225'); -- this order fails because we don't have insert permission on OnlineStore

T13.006- Create a view that informs us of the total amount to pay for each order in OnlineStore and its date. Check that you give us the correct data.

create or replace view Tpedidos as
select p.numOrder,sum(price*amount) total,date(date)
from OnlineStore.orrder p, OnlineStore.linorder l where p.numOrder=l.numOrder
group by p.numOrder,date;

select p.numOrder,sum(price*amount) total,date(date)
from OnlineStore.orrder p, OnlineStore.linorder l where p.numOrder=l.numOrder
group by p.numOrder,date;

select * from Tpedidos;

T13.007- Using the previews, list all emails, first and last names of the user of Alicante, and if they have orders also the total paid by the user.

select u.email,u.surnames,u.name,pp.paid
from VusuAli u
left join
(
    select user, sum(total) paid
    from OnlineStore.orrder p -- we need this table because Tpedidos view does not include user information
    join Tpedidos t on p.numOrder=t.numOrder
    group by user
) pp
on pp.user=u.email

T13.008- Create another view that informs us of the total amount to be paid for each order made by Alicante users and their date (date only).

create or replace view TApedidos as
select p.numOrder,sum(price*amount) total,date(date),user  
from OnlineStore.orrder p join OnlineStore.linorder l on p.numOrder=l.numOrder 
where user in (select email from OnlineStore.uuser where province='03')
group by p.numOrder,date(date),user;

T13.009- Using the new preview, modify your previous solution and list all emails, user's first and last names, and if they have orders also the total paid by the user.

select u.email,u.surnames,u.name,pp.total
from VusuAli u
left join TApedidos pp
on pp.user=u.email;

T13.010- Create in your database a table with the same structure as OnlineStore.uuser. Your table will be called "usuAlm".

CREATE TABLE usuAlm LIKE OnlineStore.uuser

creates a new table (usuAlm) with OnlineStore.uuser columns and primary key, but not foreign keys, if there is any. Those, if you need them, must be defined with ALTER TABLE. The table is empty, it has not rows.

Create a "VusuAlm" view that allows you to manage (registrations, deletions, modifications and queries) Almeria users (code '04') and no other.

create table usuAlm like OnlineStore.uuser; -- creates an identical table except for the foreign keys, which, to add them, you would have to use ALTER TABLE

create view VusuAlm as select * from usuAlm where province='04' with check option; -- CHECK OPTION is necessary to avoid inserting users from other provinces

T13.011- Try to insert using VusuAlm (email, ID, surname, name, province, town)
('rgg2@gmial.es','11222333R','GOMEZ GOMEZ','ROSA','04','1002')
What happened? Why?

insert into VusuAlm (email,dni,surnames,name,province,town) values 
('rgg2@gmial.es','11222333R','GOMEZ GOMEZ','ROSA','04','1002');
-- we can insert because de base table (usuAlm) is in our own database, and we have permissions to do that

T13.012- Using VusuAlm removes all the possible rows of users that you have and inserts these 2 users into your USER table (email, ID, surname, name, province, town):
('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,surnames,name,province,town) values 
('rff20@gmial.es','11222333R','FERNANDEZ FERNANDEZ','ROSA','04','0530'),
('jmm119@gmial.es','22333444T','MARTINEZ MARTINEZ','JULIA','04','1002');

T13.013- Try to insert using VusuAlm (email, ID, surname, name, province, town)
('tutu@gmial.es','00999000T','LOPEZ DE LOPEZ','ANA','03','1225')
What happened? Why?

insert into VusuAlm (email,dni,surnames,name,province,town) values 
('tutu@gmial.es','00999000T','LOPEZ DE LOPEZ','ANA','03','1225');
-- We are trying to insert a user from Alicante ('03'), but the view does not allow other province that Almeria ('04') because the CHECK OPTION modifier

T13.014- You are going to build the delegation of Almeria. You already have 2 users (exercise 12), insert some orders for some of them.

In OnlineStore there is no user from that province. The usuAlm table already has it but without a foreign key.

ALTER TABLE usuAlm ADD FOREIGN KEY (town, province) REFERENCES OnlineStore.town (codm, province);

The previous order adds that foreign key. Note that the TOWN table is referenced in OnlineStore. That means that you do not need this table in your database, you will manage it through views.

The other tables you need are all those about what your users buy. You must decide which tables you need in your database and which ones you will only use as a reference from OnlineStore -and you do not need to recreate them-. 

ALTER TABLE usuAlm ADD FOREIGN KEY (town, province) REFERENCES Online.Store (codm,province);

create table pedidosAlm like OnlineStore.orrder;
alter table pedidosAlm add foreign key (user) references usuAlm(email);

create table lineasAlm like OnlineStore.linorder;
alter table lineasAlm add foreign key (numorder) references pedidosAlm(numorder);
alter table lineasAlm add foreign key (article) references OnlineStore.article(cod);

insert into pedidosAlm (numorder, user, date) values (1,'rff20@gmial.es',now());
insert into lineasAlm (numorder,line,article,amount,price) values
(1,1,'A0685',10,100),(1,2,'A1234',2,10);

-- checking data
select * from usuAlm;
select * from pedidosAlm;
select * from lineasAlm;
select * from cestasAlm;

T13.015- You have your Almeria users and access to the other OnlineStore users. Of your users, those of Almeria, and all others in OnlineStore, list email, surnames, name, and names of the town and province.

select email,surnames,u.name,p.name,l.town
from OnlineStore.town l, OnlineStore.province p,
(
  select email,surnames,name,province,town from VusuAlm
  UNION
  select email,surnames,name,province,town from OnlineStore.uuser
) u
where u.province=l.province and u.town=l.codm
  and l.province=p.codp;

T13.016- On your database, create a view TApedidosAlm showing order number, user, date and total money paid for each order. These will be the orders from Almería delegation.

create or replace view TApedidosAlm as
select p.numorder,sum(price*amount) total,date(date),user  
from pedidosAlm p join lineasAlm l on p.numporder=l.numorder 
group by p.numporder,date(date),user;

-- comprobando
select * from TApedidosAlm;

T13.017- Use VusuAlm and TApedidosAlm to show all users of Almeria (email, name, and surnames) and, if they have orders, order number and total paid for each one of them also.

select email, name, surnames, p.numorder,total
from VusuAlm u 
left join TApedidosAlm p on u.email=p.user;