T04 EnglishSol

T04.001- All the information of orders previous to October 2010

select * from orrder where date < '2010-10-01';

 

T04.002- All the information of orders later than August 2010.

select * from orrder where date > '2010-08-31';

T04.003- All the information of orders between August and October 2010.

select * from orrder where date > '2010-07-31' and date < '2010-11-01';

Solución alternativa: 

select * from orrder where date between '2010-08-01' and '2010-10-30';

 

T04.004- All the information of orders requested 3rd March or 27th October 2010

select * from orrder where date = '2010-03-03' or date = '2010-10-27';

 

T04.005- All the information of orders requested 3rd March or 27th October 2010, and requested by users of the domain "cazurren"

select * from orrder

where (date = '2010-03-03' or date = '2010-10-27')

       and user like '%@cazurren.%';

T04.006- Which is the date of today and the time?

select now();

 

T04.007- 21st February 2011 in format dd/mm/aaaa

select date_format('2011-02-21','%d/%m/%Y') ladate;

 

T04.008- 31st February 2011 in format dd/mm/aaaa

select date_format('2011-02-31','%d/%m/%Y') ladate;

 

T04.009- Orders requested at 13.9.2010 (this format is compulsory in the comparison)

select * from orrder 
where date = str_to_date('13.9.2010','%d.%c.%Y');

 

T04.010- Number and date of the orders requested at 13.9.2010 (this format is compulsory in the comparison and the output)

select numOrder, date_format(date,'%d.%c.%Y') date 
from orrder 
where date = str_to_date('13.9.2010','%d.%c.%Y');

 

T04.011- Number, date and email of clients of orders (format dd.mm.aa) descending sorted by date and ascending by client.

select numOrder, date_format(date,'%d.%m.%y') ladate, user
from orrder 
order by date desc, uuser;

 

T04.017- Information about the users younger than 25 years old.

select * from uuser 
where year(now())-year(birthday)<25;



T04.012- Codes of articles requested in 2010, avoiding duplicates and ascending sorted.

select distinct article

from linorder l, orrder p

where l.numOrder=p.numOrder

  and year(date)=2010 

order by article;

Solución alternativa: 

select distinct article

from linorder l, orrder p

where l.numOrder=p.numOrder

  and date between '2010-01-01' and '2010-12-31' 

order by article;

 

T04.013- Codes of articles requested in March 2010, avoiding duplicates and ascending sorted.

select distinct article

from linorder l, orrder p

where l.numOrder=p.numOrder

  and month(date)=3 and year(date)=2010 

order by article;

Solución alternativa: 

select distinct article

from linorder l, orrder p

where l.numOrder=p.numOrder

  and date between '2010-03-01' and '2010-03-31' 

order by article;

 

T04.015- Name, surnames and age (approx.) of the users of the domain "dlsi.ua.es", descending sorted by age.

select name, surnames, year(now())-year(birthday) age 
from uuser
where email like '%@dlsi.ua.es'
order by age desc; 

 

T04.016- Email and amount of days past from the orders requested by each user until the date of each own basket. Avoid duplicates.


select distinct c.uuser, datediff(c.date,p.date) days
from basket c, orrder p 
where c.uuser=p.uuser;

 

 

T04.014- Codes of the articles requested in orders of September 2010, and week of the year (the week starting on Monday) and year of the order, sorted by week.

select article, date_format(date,'%u') week, year(date) yyear

from linorder l, orrder p

where l.numOrder=p.numOrder

  and month(date)=9 and year(date)=2010 

order by week;

T04.018- Number of order, user and date (dd/mm/aaaa) for orders requested during the week of the 7th November 2010.

select numOrder, uuser, date_format(date,'%d/%m/%Y') cuando

from orrder

where date_format(date,'%u')=date_format('2010-11-07','%u');

Solución alternativa: 

select numOrder, uuser, date_format(date,'%d/%m/%Y') cuando

from orrder

where date_format(date,'%u')=date_format(str_to_date('10/11/07','%y/%m/%d'),'%u');

 

T04.019- Code, name, panel and screen of the televisions that have not been requested during this year and not in the last six month of last year.

select a.cod, name, panel, screen
from article a, tv 
where a.cod=tv.cod 
   and a.cod not in (select article 
                     from linorder l, orrder p
                     where l.numOrder=p.numOrder
                       and (
                            (year(date)=year(now())-1 
                             and month(date) between 7 and 12)
                           ) or year(date)=year(now())
                    );

 

T04.020- Email and amount of days past from the orders requested by each user until the date of each article in his/her basket at this moment. Avoid duplicates.

select distinct c.uuser, datediff(c.date,p.date) days

from basket c, orrder p, linorder l

where c.uuser=p.uuser

and l.numOrder=p.numOrder

and l.article = c.article;

Comments