T02B EnglishSol

T02B.006- Code, name and retail price of the articles of less than 100€; the output must be code, name,"has a price of",rrp

select cod,name,'has the price of',rrp from article where rrp < 100

 

T02B.007- DNI, email, name and surnames of the users of the Asturias province (code 33)

select dni,email,name,surnames

from uuser

where province='33';

Solución alternativa: 

select dni,email,u.name,surnames

from uuser u, province pv

where u.province=codp and pv.name='Asturias';

The code of locality and provinces is text not numbers.

T02B.008- All the information (code and name) of the provinces that have users.

select pv.* 
from uuser u, province pv
where u.province=codp;

 

T02B.009- All the information (code and name) of the provinces that have users, avoiding duplicates and sorted by name.

select distinct pv.*

from uuser u, province pv

where u.province=codp

order by pv.name;

Solución alternativa: 

select distinct pv.*

from uuser u, province pv

where u.province=codp

order by 2;

 

T02B.010- Email of the users of Murcia province that have no phone, with a message in the output like "No phone".

select email,'No phone' 
from uuser u, province pv
where u.province=codp and pv.name = 'Murcia'
and telephone is null;

 

T02B.012- Articles that have no brand

select * from article where brand is null

T02B.014- Numer of pack, name and price of it.

select p.cod,name,rrp

from article a, pack p

where a.cod = p.cod

T02B.015- Code, name and brand of the articles that belong to any pack.

select article,name,brand

from article, part_of

where cod = article

T02B.017- Code, name, brand, rrp and price of the articles requested in order number 1.

select article,name,brand,rrp,price
from linorder l, article a
where numOrder=1
and a.cod=l.article

 

T02B.022- Code, sensor and screen of the cameras, if "screen" has a value, descending sorted by code;

select cod,sensor,screen

from camera

where screen is not null order by cod desc;




T02B.018- Code, name, brand, rrp and price of the articles requested in order number 1, that are tv

select article,name,brand,rrp,price
from linorder l, article a, tv t
where numOrder=1
and a.cod=l.article
and a.cod=t.cod

 

T02B.019- Date and user of the order, code, name, brand, rrp and price of the articles requested in order number 1 that are tv

select date,uuser,article,name,brand,rrp,price

from linorder l, article a, tv t, orrder p

where l.numOrder=1 and l.numOrder = p.numOrder

and a.cod=l.article

and a.cod=t.cod

T02B.025- Number of pack, name and price of it, and code, name and rrp of the articles of that pack.

select a1.cod, a1.name, a1.rrp, a2.cod art2, a2.name name2, a2.rrp rrp2

from article a1, part_of pp, article a2

where pp.pack = a1.cod

and pp.article = a2.cod

Comments