/*EJERCICIO 1*/
/*xxxxxxxxxxxxx
SELECT
drivername
FROM
 driver
UNION
select codrivername
from codriver
order by drivername */


/*EJERCICIO 2 ++++++++++++++++++++*/

/*SELECT
drivername
FROM
 driver
WHERE drivername like 'C%'
order by drivername */


/*EJERCICIO 3+++++++*/

/*Select distinct RUNS.drivername
from CAR NATURAL JOIN runs
where car.engine > 1600*/


/*Ejercicio 4 ++++++++++++++++++++*/

/*Select distinct runs.drivername, runs.codrivername
select * from (
select distinct drivername, sum(Penalty) as tPenalty
from runs natural join car
where Rallyname like '60 Rally Sweden'
group by drivername) as selection 
where tpenalty > '0:0:10'*/

/*EJERCICIO 5 +++++++++++++++++++*/
/*select distinct rallyname, startdate
from runs natural join (select rallyname, startdate from rally) as rallies natural join car
where car.manufacturer like 'Citroen' */


/*EJERCICIO 6++++++++++++++++++++*/

/*select *
from rally 
where RLocation ='Norway' OR RLocation ='Monaco'  AND ENDDATE < ('2012/12/31');*/


/*Ejercicio 7*/
/*select  runs.RallyName, runs.DriverName, runs.Stagename
from rally natural join runs natural join RallyStages
where RALLY.ENDDATE BETWEEN ('2012/08/01') AND ('2012/11/30') AND penalty < '0:0:20' AND runs.stagename = rallystages.stagename*/

/*select  runs.RallyName, runs.DriverName, runs.Stagename, SUM(PENALTY) AS length
from rally natural join runs natural join RallyStages
where RALLY.ENDDATE BETWEEN ('2012/01/01') AND ('2012/11/30') AND runs.stagename = rallystages.stagename
group by runs.Rallyname, runs.DriverName, runs.Stagename
having SUM(PENALTY) < '0:0:20'
order by Rallyname*/



/*Ejercicio 8 +++++++++++++++*/
/*
select distinct drivername
from driver
except
(select distinct drivername
from runs
where RallyName like '60 Rally Sweden')*/


/*9. */
select distinct drivername
from (select drivername from runs 
natural join rally WHERE RALLY.ENDDATE BETWEEN ('2012/01/01') AND ('2012/12/31') ) as participates natural join 
(select * from championship where WRCyear = 2012) as xYear 
natural join car



/*EJERCICIO 10 ++++++++++++++++++*/
/*
select distinct runs.drivername, runs.codrivername
from runs*/



/*EJERCICIO 11++++++++++++++++++++++++++++++*/

/*(select distinct drivername
from runs
where RallyName like '60 Rally Sweden')
except
(select distinct drivername
from runs
where RallyName like '68 Wales Rally GB')*/ /* add participans to this rally's stages*/

/*EJERCICIO 12 ++++++++++++++++++++++++++++*/
/*select distinct rallyname, stagenum
from rally */



/*EJERCICIO 13+++++++++++++++++*/
/*

select * from (
select distinct drivername, sum(Penalty) as tPenalty
from runs natural join car
where Rallyname like '60 Rally Sweden' and car.Manufacturer ='Citroen'
group by drivername) as selection 
where tpenalty > '0:0:10'
*/



/*ejercicio 14*/



/*EJERCICIO 15*/

/*Eso del total time habría que echarle un pequeño vistazo ++++++++++++++++++++++++++++++++++++++++*/
/*select distinct RallyName, runs.drivername, runs.codrivername, tiempo, Stagename
from runs natural join RallyStages
order by Drivername*/

/*
SELECT * (SELECT DISTINCT drivername, sum (tiempo) as TotalTime
from runs
GROUP BY drivername)*/


/*16*/
/*select distinct rallyname, SUM(length) AS length, avg(length)
from (select distinct Rallyname from Rally) as Rallies natural join Stage natural join RallyStages
group by Rallyname
having SUM(length) > 2
order by Rallyname
*/

/*17*/
/*SELECT DISTINCT drivername, count(stagename) as completed, SUM (length) as TotalLength , sum (tiempo) as TotalTime
from runs natural join stage
where runs.rallyname='80 Rallye de Monte-Carlo'
GROUP BY drivername
order by totallength desc*/


/*18*/
/*select rallyname, stagenum
from rally
where stagenum>2
*/

/*19*/
/*select distinct drivername
from runs natural join stage
where length>30
*/

/*Ejercicio 20*/

/*select runs.Rallyname, runs.Drivername, Bloodtype
from runs natural join Driver
where Bloodtype = 'A+'*/