Solutions

Español

T09.001- How many articles of each brand are there?

select brand,count(*) 
from article 
group by brand;

---

T09.004- Amount of cameras with sensor CMOS.

select count(*) from camera where sensor like 'CMOS%'

---

T09.012- There were an error in Tiendaonline and we introduce some articles without stock, with zero units available, in the basket. Obtain the code of those articles and how many times they appear in the basket.

select c.article, count(c.article) 
from basket c, stock s 
where c.article=s.article
  and available=0  
group by c.article;

---

T09.016- Amount of articles with stock 0

select count(*) from stock where available=0;

---

T09.017- Amount of articles that are not a memory, nor a tv, nor a lens nor a pack.

select  count(*) 
from article 
where cod not in (select cod from camera) 
  and cod not in (select cod from tv) 
  and cod not in (select cod from memory) 
  and cod not in (select cod from lens) 
  and cod not in (select cod from pack);

Alternative:

select  count(*) 
from article 
where cod not in (select cod from camera 
            union select cod from tv 
            union select cod from memory 
            union select cod from lens 
            union select cod from pack);

---

T09.021- Number of lines per order.

select numOrder, count(*)
from linorder
group by numOrder;

---

T09.022- Quantity of lines for each order, eliminating those rows with requested quantities of articles less than 3.

select numOrder, count(*)
from linorder
where amount >= 3
group by numOrder;

---

T09.023- For orders prior to September 2010, number of lines per order.

select l.numOrder, count(*)
from linorder l, orrder p
where l.numOrder=p.numOrder 
  and date < '2010-09-01'
group by numOrder;

---

T09.024- For each item ordered, total number of units ordered.

select article, sum(amount)
from linorder
group by article;

---

T09.025- For each article ordered, total number of ordered units and average unit amount.

select article, sum(amount) amount, avg(price) impmedio
from linorder
group by article;

---

T09.026- For ordered cameras, article code, total number of units ordered and average unit amount.

select article, sum(amount) amount, avg(price) impmedio
from linorder
where article in (select cod from camera)
group by article;

---

T09.027- For the ordered cameras, code, name, brand of the article, and total number of ordered units and average unit amount.

select l.article, a.name, a.brand, sum(amount) amount, avg(price) impmedio
from linorder l, article a
where l.article=a.cod
  and article in (select cod from camera)
group by l.article, a.name, a.brand;

---

T09.028- For all users whose first surname is Martínez, check if there is another user with the same last name and first name.

select surnames,name,count(*)
from uuser
where surnames like 'Martínez%'
group by surnames,name;

---

T09.029- For each month and year, number of orders placed.

select month(date) mes, year(date) año, count(*)
from orrder
group by month(date), year(date);

---

T09.030- For articles 'A0233' and 'A1085', article code and number of different orders in which it has been requested.

select article, count(distinct numOrder)
from linorder
where article in ('A0233','A1085')
group by article;

---

T09.031- How many times have each article being ordered? If there are articles that have not been included in any order they will also appear in the output. Show the code and the name of the article together with the times it has been included in an order (only if it has been included, it is not the "amount").

select cod, name, count(numOrder) 
from article a 
left join linorder l on (a.cod=l.article) 
group by cod, name;

---

T09.032- For each user, email and total retail price accumulated in their current baskets.

select user,sum(rrp)
from basket c, article a
where c.article=a.cod
group by user;

---

T09.033- For each user, email and total retail price accumulated in their current baskets, previously eliminating the items without PVP, and ordering from highest to lowest for that total.

select user,sum(rrp) total
from basket c, article a
where c.article=a.cod and rrp is not null
group by user
order by total;

---