T10 Soluciones a los ejercicios

Solutions

T10.001- Email, name and surnames of users, and name of the province and locality where he/she lives, ordered descendant by the total in euros buyed in our store.

select u.email,u.name,u.surnames,l.town,p.name
from uuser u join orrder pd on u.email=pd.uuser
join linorder lp on lp.numOrder = pd.numOrder
join locality l on u.town = l.codm and u.province = l.province
join province p on l.province = p.codp
group by u.email,u.name,u.surnames,l.town,p.name
order by sum(price*amount) desc;

T10.002- Email, name and surnames of users, and name of the province and locality where he/she lives, only the 5 users with that have ordered more in total euros in our store.

select u.email,u.name,u.surnames,l.town,p.name
from uuser u join orrder pd on u.email=pd.uuser
join linorder lp on lp.numOrder = pd.numOrder
join locality l on u.town = l.codm and u.province = l.province
join province p on l.province = p.codp
group by u.email,u.name,u.surnames,l.town,p.name
order by sum(price*amount) desc limit 5;

T10.003- Email, name and surnames of users, and name of the province and locality where he/she lives, only the five users that have ordered less in total euros in our store.

select u.email,u.name,u.surnames,l.town,p.name
from uuser u join orrder pd on u.email=pd.uuser
join linorder lp on lp.numOrder = pd.numOrder
join locality l on u.town = l.codm and u.province = l.province
join province p on l.province = p.codp
group by u.email,u.name,u.surnames,l.town,p.name
order by sum(price*amount) limit 5;

T10.004- Amount of orders from users with delivery address.

select count(*) amount
from deliveryaddress d, orrder p
where d.email=p.uuser

T10.005- Provinces and amount of orders served in those provinces, for those provinces with an average total price (price*amount) of their line orders greater that 1500 €.

select province,count(*) orrders
from uuser u, orrder p, linorder l
where u.email=p.uuser and p.numOrder=l.numOrder
group by province
having avg(price*amount)>=1500;

T10.006- Brands and amount of cameras with avalilability greater that 0 units, if this amount of cameras is less than 20.

select a.brand,count(*) amount
from article a, camera c, stock s
where a.cod=c.cod and c.cod=s.article
  and s.available>0
group by brand
having count(*)<20;

T10.007- Codes of province and localites of users that have made more than 1 order. Without duplicates.

select distinct u.province,u.town
from uuser u, orrder p
where p.uuser=u.email
group by u.email,u.province,u.town
having count(*)>1;

Alternative:

use tiendaonline;
select distinct province,town
from uuser
where email in (select uuser 
                from orrder 
                group by uuser
                having count(*)>1);

T10.008- Brands and average of the rrp of its articles, except from the packs, and only the brands with an amount of articles greater that 100, ordered descendant by average.

select brand,avg(rrp) promedio
from article
where cod not in (select cod from pack)
group by brand
having count(*) > 100
order by promedio desc;

T10.009- Code of the article and brand of the TV's whose average unit price in orders is greater in a 10% of the rrp.

select a.cod,a.brand
from article a, tv, linorder l
where a.cod=tv.cod and l.article=a.cod
group by a.cod,a.brand
having avg(l.price)>1.10*max(a.rrp);
Alternative:
select a.cod,a.brand
from article a, tv,
(select article,avg(price) mprice from linorder group by article) l
where a.cod=tv.cod and l.article=a.cod
  and l.mprice>1.10*a.rrp;
En el having hemos tenido que utilizar max(a.rrp) simplemente para que MySQL nos permitiera ejecutar. Date cuenta de que cada artículo de article tiene un único rrp y calcular el máximo obtiene ese mismo rrp.

La solución alternativa salve este inconveniente convirtiendo la consulta a linorder en una subconsulta o consulta temporal.

T10.010- Code of the article, brand and average price of the cameras that have been ordered in more that two line orders, ordered by brand and code of the article.

select a.cod,a.brand,avg(price) mprice
from article a, camera c, linorder l
where a.cod=c.cod and l.article=a.cod
group by a.cod,a.brand
having count(*)>2
order by a.brand,a.cod;
Utilizamos count(*) porque sabemos que habrá tantas filas como haya en linorder, justo las que queremos contar. Count(linea) valdría también, sabemos que no hay nulos en esta columna. Count(distinct linea) no, hay valores repetidos en la columna.

T10.011- Dates of the order and amount of orders performed that date, without considering the line orders with prices greater that 1000 €.

select date, count(distinct l.numOrder) cuantos
from orrder p, linorder l
where p.numOrder=l.numOrder
  and l.price <=1000
group by date;
Cuidado, aquí es obligatorio utilizar count(distinct numOrder). ¿Sabes por qué?

T10.012- Dates of order (only date) and total price paid in those orders, ordered by this total price.

select date(date), sum(l.amount*l.price) total
from orrder p, linorder l
where p.numOrder=l.numOrder
group by date
order by 2;
""Order by 2"" es ""ordena por la segunda columna"", y equivalente en este caso a ""order by sum(l.amount*l.immporte)"".

T10.013- Number of order and amount of line orders for each order, ordered descendant by this amount.

select numOrder, count(*) lineas
from linorder
group by numOrder
order by count(*) desc;

T10.014- Surnames of users if these surnames are repeated in users table.

select surnames
from uuser
group by surnames
having count(*) >= 2;

Alternative:

select distinct u1.surnames
from uuser u1, uuser u2
where u1.email != u2.email
  and u1.surnames = u2.surnames
order by u1.surnames;

T10.015- Surnames and names of users if these surnames and names together are repeated in users table.

select surnames, name
from uuser
group by surnames,name
having count(*) >= 2;

Alternative:

select distinct u1.surnames,u1.name
from uuser u1, uuser u2
where u1.email != u2.email
  and u1.surnames = u2.surnames
  and u1.name = u2.name
order by u1.surnames;

T10.016- Name of locality and amount of provinces in which this locality can be found, in case of towns with 2 o more provinces.

select town, count(*) provinces
from locality
group by town
having count(*) >= 2;

T10.017- Amount of articles without rrp.

select count(*) sinrrp
from article
where rrp is null;

T10.018- Amount of articles without rrp and Unlisted.

select count(*)  'Unlisted and sinrrp'
from article a, stock s
where a.cod=s.article
  and rrp is null
  and entrega='Unlisted';

T10.019- Brand and amount of articles whose name indicates that they are lenses with focals of ""18-200 mm"" or ""28-200 mm"", ordered descendant by this amount.

select brand,count(*) cuantos
from article
where name like '%18-200%' or name like '%28-200%'
group by brand
order by count(*) desc;

T10.020- Code and name of provinces, and average and standard deviation of the amount of articles ordered in those provinces.

select p.codp,p.name,avg(amount) media, std(amount) desvest
from province p, uuser u,orrder pd,linorder l
where p.codp=u.province 
  and u.email=pd.uuser
  and pd.numOrder=l.numOrder
group by p.codp,p.name;

T10.022- Number of order, date, email, surnames and name of users, and IVA (21 %, included in the prices, rounded to 2 decimals) to be applied in this invoice derived from orders, ordered by date and email.

select p.numOrder,p.date,u.email,u.surnames,u.name,
       round(sum(amount*price*.21),2) iva
from uuser u,orrder p,linorder l
where u.email=p.uuser
  and p.numOrder=l.numOrder
group by p.numOrder,p.date,u.email,u.surnames,u.name
order by p.date,u.email;

T10.023- Number of order, date, email, surnames and name of users, and taxable income (assume 21 % IVA) of each invoice derived from the orders, ordered by date and email. Calculated with a round of 2 decimals.

select p.numOrder,p.date,u.email,u.surnames,u.name,
       round(sum(amount*price*.79),2) base
from uuser u,orrder p,linorder l
where u.email=p.uuser
  and p.numOrder=l.numOrder
group by p.numOrder,p.date,u.email,u.surnames,u.name
order by p.date,u.email;

T10.024- Year, month and total of IVA (21 %, included in the prices) applied, ordered by year and month. Calculated with a round of 2 decimals.

select year(date) año, month(date) mes, round(sum(price*amount)*.21,2) IVA 
from orrder p join linorder l on (l.numOrder = p.numOrder)
group by year(date), month(date)
order by year(date), month(date);

T10.025- Year, month and total of taxable income (IVA 21 %, included in the prices) of their orders, ordered by year and month. Calculated with a round of 2 decimals.

select year(date) año, month(date) mes, round(sum(price*amount)/1.21,2) base 
from orrder p join linorder l on (l.numOrder = p.numOrder)
group by year(date), month(date)
order by year(date), month(date);

T10.026- By each year and month, taxable income, IVA (21 %, included in the prices) applied and total price of all their orders, ordered by year and month.Calculated with a round of 2 decimals.

select year(date) año, month(date) mes, 
 round(sum(price*amount)*.79,2) base, 
 round(sum(price*amount)*.21,2) IVA,
 round(sum(price*amount),2) total  
from orrder p join linorder l on (l.numOrder = p.numOrder)
group by year(date), month(date)
order by year(date), month(date);

T10.027- By each year and month, taxable income, IVA (21 %, included in the prices) applied and total price of all their orders, ordered by year and month. Only the months whose amount of orders is greater that 10. Calculated with a round of 2 decimals.

select year(date) año, month(date) mes, 
 round(sum(price*amount)*.79,2) base, 
 round(sum(price*amount)*.21,2) IVA,
 round(sum(price*amount),2) total  
from orrder p join linorder l on (l.numOrder = p.numOrder)
group by year(date), month(date)
having count(distinct l.numOrder) > 10
order by year(date), month(date);

T10.028- By each year and month, taxable income, IVA (21 %, included in the prices) applied and total price of all their orders, ordered by year and month. Only the months whose amount of orders is greater that 10 and eliminating the orders of the province 33 . Calculated with a round of 2 decimals.

select year(date) año, month(date) mes, 
 round(sum(price*amount)*.79,2) base, 
 round(sum(price*amount)*.21,2) IVA,
 round(sum(price*amount),2) total  
from orrder p join linorder l on (l.numOrder = p.numOrder)
join uuser u on (p.uuser = u.email)
where u.province != '33'
group by year(date), month(date)
having count(distinct l.numOrder) > 10
order by year(date), month(date);

T10.029- Dates of order and amount of orders done in those dates, except from the orders that contains lines with prices less than 1000 €.

select date, count(distinct l.numOrder)
from orrder p, linorder l
where p.numOrder=l.numOrder
  and p.numOrder not in (select numOrder from linorder where price<1000)
group by date;
Cuidado, aquí es obligatorio utilizar count(distinct numOrder). ¿Sabes por qué?