T11 Soluciones a los ejercicios

Solutions

T11.001- List the codes of the Samsung articles being ordered.

SELECT a.cod 
FROM article a 
WHERE brand = 'Samsung' AND cod IN
	(SELECT article FROM linorder);

Alternative:

SELECT a.cod 
FROM article a 
WHERE brand = 'Samsung' AND EXISTS
	(SELECT 1 FROM linorder l WHERE a.cod = l.article);

-- otra solución
SELECT DISTINCT a.cod 
FROM article a, linorder l 
WHERE a.cod = article AND brand = 'Samsung';

T11.002- Using the set operators get the name of the articles that are compact cameras with electronic sight or CRT televisions.

SELECT name FROM camera c, article a 
	WHERE c.cod = a.cod and type LIKE '%compact%visor%electrónico%' 
UNION 
SELECT name FROM tv t,article a 
	WHERE t.cod = a.cod and panel LIKE '%televisor%CRT%';

Alternative:

SELECT name FROM camera c 
	JOIN article a ON (c.cod = a.cod) 
	WHERE type LIKE '%compacta%visor%electrónico%' 
UNION 
SELECT name FROM tv t 
	JOIN article a ON (t.cod = a.cod) 
	WHERE panel LIKE '%televisor%CRT%';

T11.003- Using the set operators get the name of the users, the town and province of the users whose town contains 'San Vicente' or the province is 'Valencia'

SELECT u.name, p.name, l.town 
   FROM uuser u, locality l, province p 
	WHERE u.town = l.codm and u.province = l.province and l.province = p.codp and l.town LIKE '%San Vicente%' 
UNION 
SELECT u.name, p.name, l.town 
   FROM uuser u, locality l, province p
	WHERE u.town = l.codm AND u.province = l.province and l.province = p.codp and p.name LIKE '%Valencia%';

Alternative:

SELECT u.name, p.name, l.town from uuser u 
	JOIN locality l ON (u.town = l.codm AND u.province = l.province) 
	JOIN province p ON l.province = p.codp 
	WHERE l.town LIKE '%San Vicente%' 
UNION 
SELECT u.name, p.name, l.town from uuser u 
	JOIN locality l ON (u.town = l.codm AND u.province = l.province)
	JOIN province p ON l.province = p.codp
	WHERE p.name LIKE '%Valencia%';

T11.004- Name and email of the users from Asturias that have the same devilery address as the postal address (without delivery address).

SELECT u.name, email 
FROM uuser u, province p
WHERE u.province = codp 
  AND p.name = 'Asturias' 
  AND email NOT IN (SELECT email FROM deliveryaddress)

Alternative:

SELECT u.name, email 
from uuser u 
JOIN province p ON (u.province = codp)
WHERE p.name = 'Asturias' 
  AND email NOT IN (SELECT email FROM deliveryaddress);

T11.005- Code, name and brand of the lenses with focals of 500 or 600 mm for the brands with no ordered articles in the month of November 2010.

SELECT a.cod, name, brand 
FROM lens o, article a 
WHERE a.cod = o.cod AND (focal = '500 mm' OR focal = '600 mm') 
	AND brand NOT IN 
		(SELECT brand 
		FROM orrder p, linorder l, article 
		where p.numOrder=l.numOrder and article= cod 
		      and year(date)=2010 and month(date)=11)

T11.006- Code and rrp of the 'Samsung' articles that have rrp and no orders.

SELECT a.cod, rrp 
	FROM article a 
	WHERE brand = 'Samsung' AND rrp IS NOT NULL AND a.cod NOT IN 
		(select article from linorder);

T11.007- Using the set operator, show the name of the articles that are in a pack.

SELECT name 
FROM article 
WHERE cod IN 
	(SELECT article FROM part_of);

Alternative:

SELECT name 
FROM article 
WHERE EXISTS 
	(SELECT 1 FROM ptienea WHERE cod = article);

T11.008- Using the cartesian product, get the name of the towns with 2 or more users (without using group by).

SELECT distinct l.town 
FROM uuser u1, uuser u2, locality l 
WHERE u1.email != u2.email 
  AND u1.town = u2.town AND u1.province = u2.province 
  AND u1.town=l.codm AND u1.province=l.province;

Alternative:

Compare with a solution using group by-having.

SELECT l.town 
FROM locality l, uuser u 
WHERE u.town = l.codm AND u.province=l.province
GROUP BY l.codm, l.province, l.town 
HAVING COUNT(*) >= 2;

T11.009- The codes of the articles in stock, in the basket and being ordered.

SELECT DISTINCT s.article 
FROM stock s, basket c, linorder l 
WHERE s.article = c.article AND s.article = l.article;

T11.010- Code and name of the articles, even being repeated, that appear in a pack or a basket.

SELECT p.article, a.name 
FROM part_of p, article a 
WHERE p.article = a.cod 
UNION ALL 
SELECT c.article, a.name 
FROM basket c, article a 
WHERE c.article = a.cod;

Alternative:

SELECT p.article, a.name 
FROM ptienea p 
	JOIN article a ON (p.article = a.cod) 
UNION ALL 
SELECT c.article, a.name 
FROM basket c 
	JOIN article a ON (c.article = a.cod);

T11.011- Code of the articles that are in any basket or appear in any line order.

select  article from basket
union
select  article from linorder;

Alternative:

select cod 
from article
where cod in (select article from basket) 
   or cod in (select article from linorder);

select cod 
from article
where exists (select 1 from basket where article=cod) 
   or exists (select 1 from linorder where article=cod);

T11.012- Email and name of users that haven't done any order or only one.

select email, name
from uuser
where email not in (select uuser from orrder)
union
select email, name
from uuser, orrder
where email=user
group by email, name
having count(*)=1;

T11.013- Surnames that are repeated in more than one user (without using group by).

select distinct u1.surnames
from uuser u1, uuser u2
where u1.email <> u2.email and u1.surnames= u2.surnames;

Alternative:

Compare with a solution using group by-having.

select surnames
from uuser
group by surnames
having count(*) > 1;

T11.014- Pairs of province names that have any town with the same name, together with the name of the town.

select p1.name, p2.name, l1.town
from province p1, province p2, locality l1, locality l2
where p1.codp<>p2.codp 
  and p1.codp=l1.province 
  and p2.codp=l2.province 
  and l1.town=l2.town;

T11.015- Code and name of the articles that in stock appear as "Unlisted" or they have not been requested in any order.

select cod, name
from article
where cod in(
  select article from stock where delivery='Unlisted'
  union
  select cod
  from article where cod not in (select article from linorder))

T11.016- Email, name and surnames of the users that have ordered televisions but never cameras.

select email, name, surnames
from uuser
where email in
  (select user from orrder p, linorder l, tv 
   where p.numOrder=l.numOrder and article=cod)
 and email not in 
  (select user from orrder p, linorder l, camera 
   where p.numOrder=l.numOrder and article=cod);

T11.017- Users that have requested orders with a total cost greater than 10000 (by order) or that have requested more than 5 distinct articles between all their orders.

select user
from linorder l, orrder p where l.numOrder=p.numOrder
group by p.numOrder, user
having sum(amount*price)>10000
union 
select user
from linorder l, orrder p where l.numOrder=p.numOrder
group by user
having count(distinct article)>5;

T11.018- Get a listing in which appear for all the users: their email, name and surnames together with the sentence in which it is shown the following: -for users with a total cost between all their orders greater than 10000 we show GREAT CLIENT; -for users which total cost is between 6000 and 10000 we show MEDIEM CLIENT; - for the ones with total cost less than 6000 we show LITTLE BUYING;-for the ones that never requested an order we show **NEVER BUY ANYTHING. The listing will be sorted by surnames.

select email, name, surnames, '  GRAN CLIENTE'
from uuser, linorder l, orrder p where l.numOrder=p.numOrder and p.user=email
group by email, name, surnames
having sum(amount*price)>10000
UNION
select email, name, surnames, '  CLIENTE MEDIO'
from uuser, linorder l, orrder p where l.numOrder=p.numOrder and p.user=email
group by email, name, surnames
having sum(amount*price) between 6000 and 10000
UNION
select email, name, surnames, '  COMPRA POCO'
from uuser, linorder l, orrder p where l.numOrder=p.numOrder and p.user=email
group by email, name, surnames
having sum(amount*price)<6000
UNION
select email, name, surnames, '  ** NO HA COMPRADO NUNCA'
from uuser
where email not in (select user from orrder)
order by 3;

T11.019- Is there any row in the brand table?

select exists (select * from brand)

T11.020- Email and name of the users that never requested cameras.

select email, name
from uuser u
where email not in 
  (select user 
   from linorder l, orrder p, camera c
   where l.numOrder=p.numOrder and l.article = c.cod);

Alternative:

select email, name
from uuser u
where not exists 
 (select 1 
  from linorder l, orrder p, camera c
  where email=p.user 
    and l.numOrder=p.numOrder and c.cod=l.article);

T11.021- Email and name of the users that, being requested any order, they never request a camera.

select email, name
from uuser u
where email in (select uuser from orrder) 
and email not in 
  (select user 
   from linorder l, orrder p, camera c
   where l.numOrder=p.numOrder and l.article = c.cod);

Alternative:

select email, name
from uuser u 
where email in (select user from orrder) 
and not exists 
 (select 1 
  from linorder l, orrder p, camera c
  where email=p.user 
    and l.numOrder=p.numOrder and c.cod=l.article);

T11.022- Code and name of the articles that have been included in all the orders.

select cod,name
from article a 
where not exists 
  (select 1 
   from orrder p
   where not exists 
      (select 1 
       from linorder l
       where l.numOrder=p.numOrder
         and l.article=a.cod))

Alternative:

select article
from linorder l
group by article
having count(distinct numOrder) = 
  (select count(*) from orrder);

T11.024- Is there any row in the brand table? Is the answer is yes, show the word "yes"

select 'sí' respuesta
from dual
where exists (select 1 from brand);

T11.025- Is there any row in the memory table? If the answer is negative, show the word "no".

select 'no' respuesta
from dual
where not exists (select 1 from memory);

T11.026- Orders (without duplicates) that include cameras and televisions

select * from orrder p
where numOrder in 
 (select numOrder from linorder l, camera c 
  where l.article=c.cod)
and numOrder in 
 (select numOrder from linorder l, tv  
  where l.article=tv.cod);

Alternative:

select * from orrder p
where exists (select 1 from linorder l where l.numOrder=p.numOrder
                                       and article in (select cod from tv))
  and exists (select 1 from linorder l where l.numOrder=p.numOrder
                                       and article in (select cod from camera));

T11.027- Orders (without duplicates) than include cameras and lenses

select * from orrder p
where numOrder in 
 (select numOrder from linorder l, camera c 
  where l.article=c.cod)
and numOrder in 
 (select numOrder from linorder l, lens o  
  where l.article=o.cod);

Alternative:

select * from orrder p
where exists (select 1 from linorder l where l.numOrder=p.numOrder
                                       and article in (select cod from camera))
  and exists (select 1 from linorder l where l.numOrder=p.numOrder
                                       and article in (select cod from lens));

T11.028- Natural join of articles and memories

select * from article natural join memory;

T11.029- Code of the article, name, rrp, brand and type of the natural join between articles and memories.

select cod,name,rrp,brand,type from article natural join memory;

T11.030- Code of the article, name, rrp, brand and type of the natural join between articles and memories, if the type is "Compact Flash".

select cod,name,rrp,brand,type 
from article natural join memory
where type='Compact Flash';

Alternative:

select a.cod,a.name,a.rpp,a.brand,m.type 
from article a, memory m
where a.cod=m.cod and type='Compact Flash';

T11.031- Natural join of order and line orders, sorted by the date of the order.

select * 
from orrder natural join linorder
order by date;

Alternative:

select * 
from orrder natural join linorder
order by date;

T11.032- Check that the natural join between basket and pack produces a cartesian product.

select * 
from basket natural join pack;

Alternative:
select *
from basket, pack;

T11.034- Why the natural join between users and delivery address results in an empty table?

select * 
from uuser natural join deliveryaddress;