T03 EnglishSol

T03.001- Code and name of the articles with a price between 400 and 500 euros

select cod,name from article where rrp between 400 and 500;

Solución alternativa: 

select cod,name from article where rrp >= 400 and rrp <= 500;

 

T03.002- Code and name of the articles with price 415, 129, 1259 or 3995.

select cod,name from article

where rrp in (415, 129, 1259, 3995);

Solución alternativa: 

select cod,name from article

where rrp = 415 or rrp = 129 or rrp = 1259 or rrp = 3995;

 

T03.003- Code and name of the provinces that are not neither Huelva, Sevilla, Asturias nor Barcelona

select codp,name from province

where name not in ('huelva', 'sevilla', 'asturias', 'barcelona');

Solución alternativa: 

select codp,name from province

where name != 'huelva' and name != 'sevilla'

  and name != 'asturias'and name != 'barcelona';


 

T03.004- Code of Alicante province

select codp from province 
where name like 'Alicante%';

 

T03.005- Get the code, name and rrp of the articles whose brand starts from S

elect cod, name, rrp from article where brand like 'S%'

 

T03.006- Information about the users whose email is from eps.

select * from uuser where email like '%@eps.%'

 

T03.009- Email of the users whose postcode is not neither 02012, 02018 nor 02032.

select email from uuser

where postcode not in ('02012','02018','02032');


 

T03.021- Name of the articles whose name cointains the word EOS

select name from article where name like '%EOS%';

 

T03.022- Type and focal of the lenses that are used in a Canon Camera of any model.

select type, focaL from lens where frame like 'Canon%';

 

T03.023- Name of the articles whose price is greater than 100 but less or equal to 200.

select name from article where rrp>100 and rrp<=200;

 

T03.024- Name of the articles whose prices is greater or equal to 100 but less or equal to 300.

select name from article where rrp between 100 and 300;

 

T03.025- Name of the cameras whose brand is not starting by S.

select name from article a, camera c where a.cod=c.cod and brand not like 'S%';

 

T03.027- Code of the tvs that have an LCD or LED panel


select cod from tv where panel like '%LCD%' or panel like '%LED%';

 

T03.007- Code, name and resolution of the tvs whose screen is not between 22 and 42.

select a.cod, name, resolution 
from article a, tv 
where a.cod=tv.cod and screen not between 22 and 42;

 

T03.008- Code and name of the televisions whose panel is LED type and its price is not greater than 1000 euros

select t.cod, name from tv t, article a where t.cod=a.cod and panel like '%LED%' and rrp<=1000;

 

T03.010- Code and name of the packs whose belonging articles are known

select distinct cod, name 
from  article, part_of 
where pack=cod;

 

T03.011- Is there any article in basket that is out of stock?

select c.article,c.uuser,c.date,s.article stock,s.available,s.delivery 
from basket c, stock s 
where c.article=s.article 
  and delivery='Unlisted';

 

T03.012- Code, name and rrp of the compact type cameras?

select a.cod, name, rrp
from article a, camera c
where a.cod=c.cod and type like'%compact%';

 

T03.013- Code, name and difference between rrp and price of articles that have been requested at any order with price distinct to rrp.

select cod, name, rrp-price
from article, linorder
where cod=article and rrp<>price;

 

T03.014- Number of order, date and name and surnames of users that request the order, for all those orders requested by users with MARTINEZ surname

select numOrder, date, name, surnames 
from orrder, uuser 
where uuser=email and surnames like'%MARTINEZ%'

 

T03.019- Brands from which there are no televisions in our DB

select brand from brand

where brand not in (select brand

                    from article a, tv t

                    where a.cod=t.cod);

Solución alternativa: 

SELECT brand

FROM brand m

WHERE NOT EXISTS

  (SELECT 1 FROM article a, tv t

   where a.cod=t.cod and a.brand=m.brand);

 

T03.026- Postal address of the users whose dni ends with B, L or P.

select email from uuser where dni like '%B' or dni like '%L' or dni like '%P';

 

T03.034- Name of provinces in where users that have requested any order live, avoiding duplicates.

select distinct p.name 
from uuser u, province p, orrder pe 
where u.province=p.codp and u.email=pe.uuser;

 

T03.035- Name of the articles that have been selected at any basket with date between 01.11.2010 and 31.12.2010

select distinct name

from article a, basket c

where a.cod=c.article

   and c.date between '2010-11-01' and '2010-12-31';

 

T03.037- Identifying number of orders in which articles with a price lower than its rrp are included. No duplicates.

select distinct numOrder 
from linorder l, article a 
where l.article=a.cod and l.price < a.rrp;

 



T03.015- Code, name and brand of the most expensive article.

select cod, name, brand

from article

where rrp = (select max(rrp) from article);

Solución alternativa: 

select cod, name, brand

from article

where rrp >= all (select rrp from article);

La solución alternativa no funciona en versiones antiguas de MySQL por un "bug".

T03.016- Name, brand and resolution of cameras that have been never ordered.

select name, brand, resolution 
from article a, camera c
where a.cod=c.cod and 
c.cod not in (select article from linorder);

 

T03.017- Code, name, type and brand of the cameras with brand Nikon, LG or Sigma

select a.cod, name, type, brand 
from article a, camera c
where a.cod=c.cod and brand in ('NIKON','LG','SIGMA');

 

T03.018- 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%');

Solución alternativa: 

select a.cod, name, rrp

from article a, camera c

where a.cod=c.cod and type like'%réflex%'

and rrp >= all (select rrp from article a, camera c

                where a.cod=c.cod and type like'%réflex%');

 


 

T03.020- 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 hours'

  and available=(select min(available)

                   from stock

                   where delivery='24 hours');

Solución alternativa: 

select cod, name, available

from stock, article

where cod=article

and delivery ='24 hours'

and available <=all (select available

                      from stock

                      where delivery='24 hours');

 

T03.028- Number of order and article with the lowest price line order.

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

 

T03.029- Name of the televisions with a screen bigger that the one of television with code A0686.

select name 
from article a, tv t 
where a.cod=t.cod 
  and screen > (select screen from tv where cod ='A0686');

 

T03.030- 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 cantidad = (select max(cantidad) from linorder);

Solución alternativa: 

select line, numOrder

from linorder

where cantidad >= all (select cantidad from linorder);

 

T03.031- 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);

Solución alternativa: 

select distinct line, name

from article a, linorder l

where a.cod=l.article

   and price > any (select price from linorder);

 

T03.032- 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);

Solución alternativa: 

select name, rrp, brand

from article a, stock s

where a.cod=s.article

  and s.available >=all (select available from stock);

 

T03.033- 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);

Solución alternativa: 

select name, rrp, brand

from article a, stock s

where a.cod=s.article

   and s.available < any (select available from stock);

 

T03.036- Name of the articles that have been selected at any basket by users of the Valencia or Alicante provinces.

select distinct a.name 
from article a, basket c, uuser u 
where a.cod=c.article 
  and c.uuser=u.email 
  and u.province in 
     (select codp 
      from province 
      where name like 'Alicante%' or name like 'Valencia%');

 

 

Comments