T03 Soluciones a los ejercicios

Solutions

T03.001- Code of the articles belonging to a pack.

select article
from part_of;

T03.002- Numer of pack, name and price of it.

select p.cod,name,rrp 
from article a, pack p
where a.cod = p.cod

T03.003- Code, name and brand of the articles that belong to any pack.

select article,name,brand
from article, part_of
where cod = article

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

select 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.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.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.010- Code and name of the packs whose belonging articles are known, without duplicates.

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 user=email and surnames like'%MARTINEZ%'

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

Alternative:

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.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.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.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.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.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.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.user=u.email 
  and u.province in 
     (select codp 
      from province 
      where name like 'Alicante%' or name like 'Valencia%');

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.038- Number of pack, name and price of it, and code, name and brand of the articles of that pack.

select a1.cod, a1.name, a1.rrp, a2.cod art2, a2.name name2, a2.brand
from article a1, part_of pp, article a2
where pp.pack = a1.cod
and pp.article = a2.cod