Solutions

Español

T02B.001- 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.002- DNI, email, name and surnames of the users of the Asturias province (code 33)

select dni,email,name,surnames 
from uuser
where province='33';

Alternative:

select dni,email,u.name,surnames 
from uuser u, province pv
where u.province=codp and pv.name='Asturias';

---

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

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

---

T02B.004- 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;

Alternative:

select distinct pv.* 
from uuser u, province pv
where u.province=codp
order by 2;

---

T02B.005- Email of the users of Murcia province that have no phone, with a message in the output like "They don't have phone".

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

---

T02B.006- Articles that have no brand

select * from article where brand is null

---

T02B.007- 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;

Alternative:

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

---

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

Alternative:

select cod,name from article 
where rpp = 415 or rpp = 129 or rpp = 1259 or rpp = 3995;

---

T02B.009- 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.010- 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.011- 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,user,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.012- 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.014- 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.user;

---

T02B.020- 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');

Alternative:

select codp,name from province 
where name != 'huelva' and name != 'sevilla' 
  and name != 'asturias'and name != 'barcelona';

---