SQL Query assignment Help

Click Here to see questions

Solutions:

a)   select distinct(deck) as 'Deck',count(*) as ':total' from results group by deck;

b)   select c1,(t1.won+t2.won) 'won',(t1.loss+t2.loss) 'loses',   (t1.won+t2.won)+(t1.loss+t2.loss)  'total' from (select deckone c1,count(if(result=1,1,NULL)) as 'won',count(if(result=2,1,NULL)) as 'loss'  from matches where deckone!=decktwo group by deckone) t1,(select decktwo c2,count(if(result=2,1,NULL)) as 'won',count(if(result=1,1,NULL)) as 'loss'  from matches where deckone!=decktwo group by decktwo) t2 where t1.c1=t2.c2;

c)  SELECT t23.Deck,t23.x,(t12.win)/(t12.win+t12.loss) 'Global win',(t23.win)/(t23.win+t23.loss) 'Win vs X'  from       (select c1,(t1.won+t2.won) 'win',(t1.loss+t2.loss) 'loss', (t1.won+t2.won)+(t1.loss+t2.loss)  'total'       from       (select deckone c1,count(if(result=1,1,NULL)) as 'won',count(if(result=2,1,NULL)) as 'loss'  from matches where deckone!=decktwo group by deckone) t1,      (select decktwo c2,count(if(result=2,1,NULL)) as 'won',count(if(result=1,1,NULL)) as 'loss'  from matches where deckone!=decktwo group by decktwo) t2       where t1.c1=t2.c2 ) t12,      (select deckone Deck,decktwo 'x' ,count(if(result=1,1,NULL))/(count(if(result=1,1,NULL))+count(if(result=2,1,NULL))) 'global win' ,count(if(result=1,1,NULL)) as 'win',count(if(result=2,1,NULL)) as 'loss'             from matches where deckone!=decktwo group by deckone,decktwo       union      select decktwo Deck,deckone 'x',count(if(result=2,1,NULL))/(count(if(result=1,1,NULL))+count(if(result=2,1,NULL))) 'global win',count(if(result=2,1,NULL)) as 'win',count(if(result=1,1,NULL)) as 'loss'         from matches where deckone!=decktwo group by decktwo,deckone ) t23
where t12.c1=t23.Deck;

d)  SELECT ttt1.deck, ttt1.x, ttt1.Globalwin,ttt1.WinvsX,ttt2.TotalDecks  from       (SELECT t23.Deck Deck,t23.x x,(t12.win)/(t12.win+t12.loss) 'Globalwin',(t23.win)/(t23.win+t23.loss) 'WinvsX'         from       (select c1,(t1.won+t2.won) 'win',(t1.loss+t2.loss) 'loss', (t1.won+t2.won)+(t1.loss+t2.loss)  'total'       from       (select deckone c1,count(if(result=1,1,NULL)) as 'won',count(if(result=2,1,NULL)) as 'loss'  from matches where deckone!=decktwo group by deckone) t1,      (select decktwo c2,count(if(result=2,1,NULL)) as 'won',count(if(result=1,1,NULL)) as 'loss'  from matches where deckone!=decktwo group by decktwo) t2       where t1.c1=t2.c2 ) t12,      (select deckone Deck,decktwo 'x' ,count(if(result=1,1,NULL))/(count(if(result=1,1,NULL))+count(if(result=2,1,NULL))) 'global win' ,count(if(result=1,1,NULL)) as 'win',count(if(result=2,1,NULL)) as 'loss'             from matches where deckone!=decktwo group by deckone,decktwo       union      select decktwo Deck,deckone 'x',count(if(result=2,1,NULL))/(count(if(result=1,1,NULL))+count(if(result=2,1,NULL))) 'global win',count(if(result=2,1,NULL)) as 'win',count(if(result=1,1,NULL)) as 'loss'         from matches where deckone!=decktwo group by decktwo,deckone ) t23
   where t12.c1=t23.Deck      ) ttt1,                   ( select tt1.Deck Deck,count( tt1.Deck)  'TotalDecks' from                               (select deckone Deck,decktwo 'x' ,count(if(result=1,1,NULL))/(count(if(result=1,1,NULL))+count(if(result=2,1,NULL))) 'global win' ,count(if(result=1,1,NULL)) as 'win',count(if(result=2,1,NULL)) as 'loss'             from matches where deckone!=decktwo group by deckone,decktwo           union          select decktwo Deck,deckone 'x',count(if(result=2,1,NULL))/(count(if(result=1,1,NULL))+count(if(result=2,1,NULL))) 'global win',count(if(result=2,1,NULL)) as 'win',count(if(result=1,1,NULL)) as 'loss'             from matches where deckone!=decktwo group by decktwo,deckone) tt1 group by tt1.Deck                    ) ttt2  where ttt1.Deck=ttt2.Deck;