T12 Soluciones a los ejercicios

Solutions

T12.001- Days past between the first and the last order.

select datediff(
   (select max(date) from orrder), 
   (select min(date) from orrder)
) dias;

Alternative:

select datediff(max(date), min(date)) dias from orrder;

T12.002- Calculate and show the amount of televisions, cameras and lenses stored in the database.

select
(select count(*) from tv) tv,
(select count(*) from camera) cameras,
(select count(*) from lens) lenss;

T12.003- Calculate and show the percentage of televisions, cameras and lenses over the total of the articles stored in the database.

select
(select count(*) from tv)/(select count(*) from article)*100 tvPC,
(select count(*) from camera)/(select count(*) from article)*100 camerasPC,
(select count(*) from lens)/(select count(*) from article)*100 lenssPC;

T12.004- Email, name and surnames of the users of the province 03, and if they have an order whose total cost is greater than 10000euros, show also the number of the order and this cost; descending sorting the output by the value of the order. Start resolving the number of the order, user and total cost of the orders greater than 10000 euros and use the result as a temporal table.

select email,name, surnames,numOrder, valor
from uuser u left join
  (select p.numOrder,user, sum(amount*price) valor
   from orrder p, linorder l
   where p.numOrder=l.numOrder
   group by numOrder,user
   having sum(amount*price)>10000) calculo
on (email=user)
where province='03'
order by valor desc

T12.005- From the users with some order without line and articles pending on requesting at any basket, show their email, name, surnames, number of order without lines, and total cost of the basket. Start resolving the orders without lines and the value of the basket by user and use the results as temporal tables.

select email,name, surnames,numOrder, pendiente
from uuser u,
  (select numOrder,uuser
   from orrder  
   where numOrder not in (select numOrder from linorder)
  ) orrders,
  (select user,sum(rrp) pendiente
   from basket c, article a
   where c.article=a.cod
   group by user
  ) baskets
where email=orrders.user and email=baskets.user;

T12.006- For those users with more than one order in 2010, get the table where each column correspond with one month of the year and show the amount of orders performed by that user that month. Each row starts with the email, name and surnames of the user.

select *
from
  (select email,name,surnames,
   (select count(*) from orrder where month(date)=1 and year(date)=2010 and uuser=email) January,
   (select count(*) from orrder where month(date)=2 and year(date)=2010 and uuser=email) February,
   (select count(*) from orrder where month(date)=3 and year(date)=2010 and uuser=email) March,
   (select count(*) from orrder where month(date)=4 and year(date)=2010 and uuser=email) April,
   (select count(*) from orrder where month(date)=5 and year(date)=2010 and uuser=email) May,
   (select count(*) from orrder where month(date)=6 and year(date)=2010 and uuser=email) June,
   (select count(*) from orrder where month(date)=7 and year(date)=2010 and uuser=email) July,
   (select count(*) from orrder where month(date)=8 and year(date)=2010 and uuser=email) August,
   (select count(*) from orrder where month(date)=9 and year(date)=2010 and uuser=email) September,
   (select count(*) from orrder where month(date)=10 and year(date)=2010 and uuser=email) October,
   (select count(*) from orrder where month(date)=11 and year(date)=2010 and uuser=email) November,
   (select count(*) from orrder where month(date)=12 and year(date)=2010 and uuser=email) December
   from uuser) pormeses
where January+February+March+April+May+June+July+August+September+October+November+December > 1;

Alternative:

select email,name,surnames,
   (select count(*) from orrder where month(date)=1 and year(date)=2010 and user=email) January
   (select count(*) from orrder where month(date)=2 and year(date)=2010 and user=email) February,
   (select count(*) from orrder where month(date)=3 and year(date)=2010 and user=email) March,
   (select count(*) from orrder where month(date)=4 and year(date)=2010 and user=email) April,
   (select count(*) from orrder where month(date)=5 and year(date)=2010 and user=email) May,
   (select count(*) from orrder where month(date)=6 and year(date)=2010 and user=email) June,
   (select count(*) from orrder where month(date)=7 and year(date)=2010 and user=email) July,
   (select count(*) from orrder where month(date)=8 and year(date)=2010 and user=email) August,
   (select count(*) from orrder where month(date)=9 and year(date)=2010 and user=email) September,
   (select count(*) from orrder where month(date)=10 and year(date)=2010 and user=email) October,
   (select count(*) from orrder where month(date)=11 and year(date)=2010 and user=email) November,
   (select count(*) from orrder where month(date)=12 and year(date)=2010 and user=email) December
from uuser
where email in (select user from orrder group by user having count(*) > 1);

T12.007- Provinces and number of orders served in them, for those provinces with an average total amount of their orders above € 2,500.

select province,count(*) orders
from uuser u, 
 (select user,p.numOrder,sum(amount*price) total
  from orrder p join linorder l on (p.numorder=l.numorder)
  group by user,numOrder) pd
where u.email=pd.user
group by province
having avg(total)>2500;

T12.008- Name of the locations of users who have made 2 orders or more. Eliminate duplicates.

select distinct l.town 
from uuser u, locality l 
where u.town = l.codm and u.province=l.province
  and email in (select user
                from orrder p
                group by user
                having count(*)>=2);

T12.009- Get the average value of "screen" for TVs with "1920 x 1080" resolution.

From SONY brand TVs, get all your data as a TV, but changing the screen to that pre-calculated average value.

select tv.cod,panel,(select round(avg(screen),0) from tv where resolution='1920 x 1080') screen, resolution, hdreadyfullhd, tdt
from tv, article a 
where tv.cod=a.cod and brand='SONY';

T12.010- Count the number of TVs, cameras and lenses we have in the database, but display those data in a single row.

select 
(select count(*) from tv) televisions,
(select count(*) from camera) cameras,
(select count(*) from lens) lenses;

T12.011- Show the amount of users from the province of Alicante ('03'), the amount that are from Agost (province '03', codm '0020'), and the percentage that the latter represent over the total of Alicante, rounded to 2 decimals.

Solutions

T12.001- Days past between the first and the last order.

T12.002- Calculate and show the amount of televisions, cameras and lenses stored in the database.

T12.003- Calculate and show the percentage of televisions, cameras and lenses over the total of the articles stored in the database.

T12.006- For those users with more than one order in 2010, get the table where each column correspond with one month of the year and show the amount of orders performed by that user that month. Each row starts with the email, name and surnames of the user.

T12.007- Provinces and number of orders served in them, for those provinces with an average total amount of their orders above € 2,500.

T12.008- Name of the locations of users who have made 2 orders or more. Eliminate duplicates.

T12.009- Get the average value of "screen" for TVs with "1920 x 1080" resolution. From SONY brand TVs, get all your data as a TV, but changing the screen to that pre-calculated average value.

T12.010- Count the number of TVs, cameras and lenses we have in the database, but display those data in a single row.

T12.011- Show the amount of users from the province of Alicante ('03'), the amount that are from Agost (province '03', codm '0020'), and the percentage that the latter represent over the total of Alicante, rounded to 2 decimals.

T12.009- Get the average value of "screen" for TVs with "1920 x 1080" resolution.

From SONY brand TVs, get all your data as a TV, but changing the screen to that pre-calculated average value.