T08 Soluciones a los ejercicios

Solutions

T08.001- Get the total price per line for the order number 1, in the output we will obtain the columns line, article and the calculated field total.

select line, article, (price*amount) total 
from linorder 
where numPedido=1;

T08.002- Get the amount of distinct provinces of users that we know.

select count(distinct province) provinces from uuser;

T08.003- Amount of users of our database.

select count(*) users from uuser;

T08.004- Number of articles with RPP greater than 200 euros.

select count(*) from article where rrp>200;

T08.005- Total in euros of the basket of the user "bmm@agwab.com"

select sum(rrp) total 
from basket, article 
where user='bmm@agwab.com' and article=cod;

T08.006- Maximum screen size for televisions.

select MAX(screen) maxPantalla from tv;

T08.007- Average distinct retail prices for the articles, rounded to 2 decimals.

select round(avg(distinct rrp),2) mediaventa from article;

T08.008- Name and price of the articles with the minimum available stock.

select name,rrp 
from article,stock 
where cod=article 
  and available = (select min(available) from stock);

T08.009- Number of order, date and name and surnames of the users of the order lines whose total in euros is the highest.

select p.numPedido,date,name,surnames 
from orrder p,linorder l,uuser u 
where p.user=email 
  and p.numPedido=l.numPedido 
  and (amount*price)=(select max(amount*price) from linorder);

T08.010- Maximum, minumum and average of price of the articles.

select MAX(rrp) maxPvp, MIN(rrp) minPvp, AVG(rrp) mediaPvp from article;

T08.011- Code, name, rrp and date of incorporation of the article to the most recent basket.

select cod, name, rrp, date
from basket, article 
where article=cod 
  and date=(select MAX(date) from basket);

T08.012- Amount of articles that are unlisted.

select count(*) NumArtUnlisteds 
from stock 
where delivery='Unlisted'

T08.013- Maximum prices of the article in stock that will be delivered shortly.

select max(rrp) 
from article, stock 
where cod=article 
  and delivery='Shortly';

T08.014- Name, code and availability in stock for all the articles whose code finish with 3, beind this availability the minimum of all the table.

select name, cod, available MinDisponible 
from stock, article 
where article=cod 
  and cod like '%3' 
  and available=(select MIN(available) from stock);

T08.015- Maximum price, minimum and average of all the order lines including the article "Bravia KDL-32EX402"

select MAX(price) MaxPrecio, MIN(price) MinPrecio, AVG(price) PrecioMedio 
from linorder l, article a 
where a.cod=l.article and a.name='Bravia KDL-32EX402';

T08.016- Total amount that has been requested of the articles starting with "UE22"

select SUM(amount) amountTotal 
from linorder, article 
where article=cod and name like 'UE22%';

T08.017- Average price of the articles included in the order line number 4, rounded to 3 decimals.

select round(avg(price),3) PrecioMedio 
from linorder 
where line=4;

T08.018- Number of order, name, telephone and email of the user of order (or orders) that
contains order line which unitary price is equal to the highest price per
article among all the second lines of all the orders.

select l.numPedido, name, telephone, email 
from uuser u, orrder p, linorder l 
where l.numPedido=p.numPedido 
  and p.user=u.email 
  and price=(select MAX(price) from linorder where line=2);

T08.019- Difference between the maximum and the minimum price of order number 30.

select (MAX(price)-MIN(price)) DiferenciaPrecios 
from linorder 
where numPedido=30;

T08.020- Code, name, and RRP of the article with the most amount in stock.

select cod,name,rrp 
from article,stock 
where cod=article 
  and available = (select MAX(available) from stock);

T08.021- Birthday of the oldest user.

select MIN(birthday) from uuser;

T08.022- Get in a single query, how meny rows are there in the article table, and how many of them has value in the brand column and how many distinct brands are there in the table.

select count(*) filas, count(brand) conbrand, count(distinct brand) brands from article;

T08.023- Code, name and brand of the most expensive article.

select cod, name, brand 
from article 
where rrp = (select max(rrp) from article);

Alternative:

select cod, name, brand 
from article 
where rpp >= all (select rpp from article);

T08.024- Code, name and rrp of the most expensive camera between the reflex type cameras

select a.cod, name, rrp
from article a, camera c
where a.cod=c.cod
and type like '%reflex%'
and rrp=(
    select max(rrp) 
    from article a, camera c 
    where a.cod=c.cod and type like '%reflex%');

Alternative:

select a.cod, name, rpp
from article a, camera c
where a.cod=c.cod and type like'%réflex%'
and rpp >= all (select rpp from article a, camera c
                where a.cod=c.cod and type like'%réflex%');

T08.025- Code, name and availability of the articles with less availability between the ones that are available in 24 hours.

select cod,name,available 
from stock, article 
where cod=article 
  and delivery='24 horas'
  and available=(select min(available) 
                   from stock 
                   where delivery='24 horas');

Alternative:

select cod, name, available
from stock, article 
where cod=article 
and delivery ='24 horas'
and available <=all (select available
                      from stock
                      where delivery='24 horas');

T08.026- Number of order and article with the lowest price line order.

select numOrder, article 
from linorder 
where price = (select min(price) from linorder);

T08.027- Lines of order and name of the articles of those lines, including a greater amount of articles of the rest of them.

select line, numOrder 
from linorder 
where amount = (select max(amount) from linorder);

Alternative:

select linea, numOrder 
from linorder 
where amount >= all (select amount from linorder);

T08.028- Lines of order and name of the articles of those lines, if the prices in those lines is not less than all the known lines.

select distinct line, name 
from article a, linorder l 
where a.cod=l.article 
   and price > (select min(price) from linorder);

Alternative:

select distinct linea, name 
from article a, linorder l 
where a.cod=l.article 
   and importe > any (select importe from linorder);

T08.029- Name, price and brand of the articles with the most availability of stock

select name, rrp, brand 
from article a, stock s 
where a.cod=s.article 
   and s.available = (select max(available) from stock);

Alternative:

select name, rpp, brand 
from article a, stock s 
where a.cod=s.article 
  and s.available >=all (select available from stock);

T08.030- Name, price and brand of the articles that they do not have the most availability of stock

select name, rrp, brand 
from article a, stock s 
where a.cod=s.article 
   and s.available <> (select max(available) from stock);

Alternative:

select name, rpp, brand 
from article a, stock s 
where a.cod=s.article 
   and s.available < any (select available from stock);

Solutions

T08.001- Get the total price per line for the order number 1, in the output we will obtain the columns line, article and the calculated field total.

T08.002- Get the amount of distinct provinces of users that we know.

T08.003- Amount of users of our database.

T08.004- Number of articles with RPP greater than 200 euros.

T08.005- Total in euros of the basket of the user "bmm@agwab.com"

T08.006- Maximum screen size for televisions.

T08.007- Average distinct retail prices for the articles, rounded to 2 decimals.

T08.008- Name and price of the articles with the minimum available stock.

T08.009- Number of order, date and name and surnames of the users of the order lines whose total in euros is the highest.

T08.010- Maximum, minumum and average of price of the articles.

T08.011- Code, name, rrp and date of incorporation of the article to the most recent basket.

T08.012- Amount of articles that are unlisted.

T08.013- Maximum prices of the article in stock that will be delivered shortly.

T08.014- Name, code and availability in stock for all the articles whose code finish with 3, beind this availability the minimum of all the table.

T08.015- Maximum price, minimum and average of all the order lines including the article "Bravia KDL-32EX402"

T08.016- Total amount that has been requested of the articles starting with "UE22"

T08.017- Average price of the articles included in the order line number 4, rounded to 3 decimals.

T08.018- Number of order, name, telephone and email of the user of order (or orders) that contains order line which unitary price is equal to the highest price per article among all the second lines of all the orders.

T08.019- Difference between the maximum and the minimum price of order number 30.

T08.020- Code, name, and RRP of the article with the most amount in stock.

T08.021- Birthday of the oldest user.

T08.022- Get in a single query, how meny rows are there in the article table, and how many of them has value in the brand column and how many distinct brands are there in the table.

T08.023- Code, name and brand of the most expensive article.

T08.024- Code, name and rrp of the most expensive camera between the reflex type cameras

T08.025- Code, name and availability of the articles with less availability between the ones that are available in 24 hours.

T08.026- Number of order and article with the lowest price line order.

T08.027- Lines of order and name of the articles of those lines, including a greater amount of articles of the rest of them.

T08.028- Lines of order and name of the articles of those lines, if the prices in those lines is not less than all the known lines.

T08.029- Name, price and brand of the articles with the most availability of stock

T08.030- Name, price and brand of the articles that they do not have the most availability of stock

T08.018- Number of order, name, telephone and email of the user of order (or orders) that
contains order line which unitary price is equal to the highest price per
article among all the second lines of all the orders.