-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathQueries_Part2.txt
1431 lines (1406 loc) · 75.4 KB
/
Queries_Part2.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
mysql> /*
/*> 3. Execute each query and display its results
/*> 1)Print the name, club, and country name of all midfielder players whose country is 'USA'.
/*> */
mysql>
mysql> SELECT
-> Name, Club, Country
-> FROM
-> PLAYERS
-> WHERE
-> Position = 'Midfielder'
-> AND
-> Country = 'USA';
+------------------+----------------------+---------+
| Name | Club | Country |
+------------------+----------------------+---------+
| KYLE BECKERMAN | Real Salt Lake | USA |
| BRAD DAVIS | Houston Dynamo | USA |
| JERMAINE JONES | Besiktas JK | USA |
| MICHAEL BRADLEY | Toronto FC | USA |
| ALEJANDRO BEDOYA | FC Nantes | USA |
| MIX DISKERUD | Rosenborg BK | USA |
| GRAHAM ZUSI | Sporting Kansas City | USA |
| JULIAN GREEN | FC Bayern Muenchen | USA |
+------------------+----------------------+---------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql> /*
/*> 2)Print the name, club, country and age of captains of each country participating in the 2014 world cup (this database)
/*> */
mysql>
mysql> SELECT
-> Name, Club, Country, TIMESTAMPDIFF(YEAR, DOB, CURDATE()) AS Age
-> FROM
-> PLAYERS
-> WHERE
-> Is_captain is true;
+---------------------+-------------------------+----------------------+------+
| Name | Club | Country | Age |
+---------------------+-------------------------+----------------------+------+
| MARIO YEPES | Atalanta Bergamo | Columbia | 46 |
| GIANLUIGI BUFFON | Juventus FC | Italy | 44 |
| GEORGIOS KARAGOUNIS | Fulham FC | Greece | 45 |
| SAMUEL ETOO | Chelsea FC | Cameroon | 40 |
| IKER CASILLAS | Real Madrid CF | Spain | 40 |
| RAFAEL MARQUEZ | Club Leon | Mexico | 43 |
| NOEL VALLADARES | CD Olimpia | Honduras | 44 |
| JAVAD NEKOUNAM | Kuwait SC | Iran | 41 |
| VINCENT ENYEAMA | Lille OSC | Nigeria | 39 |
| PHILIPP LAHM | FC Bayern Muenchen | Germany | 38 |
| MILE JEDINAK | Crystal Palace FC | Australia | 37 |
| CRISTIANO RONALDO | Real Madrid CF | Portugal | 37 |
| ANTONIO VALENCIA | Manchester United FC | Ecuador | 36 |
| CLAUDIO BRAVO | Real Sociedad | Chile | 38 |
| CLINT DEMPSEY | Seattle Sounders FC | USA | 38 |
| ASAMOAH GYAN | Al Ain FC | Ghana | 36 |
| DARIJO SRNA | Shakhtar Donetsk | Croatia | 39 |
| DIDIER DROGBA | Galatasaray SK | Ivory Coast | 43 |
| BRYAN RUIZ | PSV Eindhoven | Costa Rica | 36 |
| STEVEN GERRARD | Liverpool FC | England | 41 |
| MADJID BOUGUERRA | Lekhwiya SC | Algeria | 39 |
| VINCENT KOMPANY | Manchester City FC | Belgium | 35 |
| EMIR SPAHIC | Bayer 04 Leverkusen | Bosnia & Herzegovina | 41 |
| ROBIN VAN PERSIE | Manchester United FC | Netherlands | 38 |
| LIONEL MESSI | FC Barcelona | Argentina | 34 |
| DIEGO LUGANO | West Bromwich Albion FC | Uruguay | 41 |
| GOKHAN INLER | SSC Napoli | Switzerland | 37 |
| MAKOTO HASEBE | 1. FC Nuernberg | Japan | 38 |
| THIAGO SILVA | Paris Saint-Germain FC | Brazil | 37 |
| KOO JACHEOL | FSV Mainz 05 | South Korea | 33 |
| HUGO LLORIS | Tottenham Hotspur FC | France | 35 |
| ROMAN SHIROKOV | FC Krasnodar | Russia | 40 |
+---------------------+-------------------------+----------------------+------+
32 rows in set (0.00 sec)
mysql>
mysql> /*
/*> 3)Retrieve the names of countries participating in the 2014 world cup (this database) that have won the world cup more than twice.
/*> */
mysql>
mysql> SELECT
-> Country_Name
-> FROM
-> COUNTRY
-> WHERE
-> No_of_Worldcup_won > 2;
+--------------+
| Country_Name |
+--------------+
| Brazil |
| Germany |
| Italy |
+--------------+
3 rows in set (0.00 sec)
mysql>
mysql> /*
/*> 4)Retrieve the names of countries participating in the 2014 world cup (this database) that have never won the world cup.
/*> */
mysql>
mysql> SELECT
-> Country_Name, No_of_Worldcup_won AS 'World Cups Won'
-> FROM
-> COUNTRY
-> WHERE
-> No_of_Worldcup_won < 1;
+----------------------+----------------+
| Country_Name | World Cups Won |
+----------------------+----------------+
| Algeria | 0 |
| Australia | 0 |
| Belgium | 0 |
| Bosnia & Herzegovina | 0 |
| Cameroon | 0 |
| Chile | 0 |
| Columbia | 0 |
| Costa Rica | 0 |
| Croatia | 0 |
| Ecuador | 0 |
| Ghana | 0 |
| Greece | 0 |
| Honduras | 0 |
| Iran | 0 |
| Ivory Coast | 0 |
| Japan | 0 |
| Mexico | 0 |
| Netherlands | 0 |
| Nigeria | 0 |
| Portugal | 0 |
| Russia | 0 |
| South Korea | 0 |
| Switzerland | 0 |
| USA | 0 |
+----------------------+----------------+
24 rows in set (0.00 sec)
mysql>
mysql> /*
/*> 5)Retrieve the name and country of the player who had neither red cards nor yellow cards in the 2014 world cup.
/*> */
mysql>
mysql> SELECT
-> Name, Country
-> FROM
-> PLAYERS PL
-> LEFT JOIN
-> PLAYER_CARDS PC
-> ON
-> PL.Player_id = PC.Player_id
-> WHERE
-> PC.Player_id IS NULL;
+----------------------------+----------------------+
| Name | Country |
+----------------------------+----------------------+
| FARYD MONDRAGON | Columbia |
| GIANLUIGI BUFFON | Italy |
| GEORGIOS KARAGOUNIS | Greece |
| SAMUEL ETOO | Cameroon |
| PEPE REINA | Spain |
| DAMARCUS BEASLEY | USA |
| MICHAEL ESSIEN | Ghana |
| ALFREDO TALAVERA | Mexico |
| MARTIN SILVA | Uruguay |
| THIAGO MOTTA | Italy |
| YASUHITO ENDO | Japan |
| XAVI HERNANDEZ | Spain |
| ANDREA PIRLO | Italy |
| ANTONIO CASSANO | Italy |
| NOEL VALLADARES | Honduras |
| MARK BRESCIANO | Australia |
| JOSEPH YOBO | Nigeria |
| TIM HOWARD | USA |
| JOHNNY HERRERA | Chile |
| EDISON MENDEZ | Ecuador |
| BOUBACAR BARRY | Ivory Coast |
| KOLO TOURE | Ivory Coast |
| JULIO CESAR | Brazil |
| STIPE PLETIKOSA | Croatia |
| YOSHITO OKUBO | Japan |
| MAICON SISENANDO | Brazil |
| ALDO RAMIREZ | Columbia |
| JORGE GUAGUA | Ecuador |
| BRAD DAVIS | USA |
| MICKAEL LANDREAU | France |
| MIROSLAV KLOSE | Germany |
| PABLO ZABALETA | Argentina |
| CHIGOZIE AGBIM | Nigeria |
| ALEX WILKINSON | Australia |
| CHRISTIAN BOLANOS | Costa Rica |
| ROY MILLER | Costa Rica |
| ANDRES INIESTA | Spain |
| FERNANDO TORRES | Spain |
| HASSAN YEBDA | Algeria |
| MAXI RODRIGUEZ | Argentina |
| JEFFERSON GALVAO | Brazil |
| WALTER AYOVI | Ecuador |
| SEGUNDO CASTILLO | Ecuador |
| AUSTINE EJIDE | Nigeria |
| NICK RIMANDO | USA |
| DIEGO PEREZ | Uruguay |
| FRANK LAMPARD | England |
| SHOLA AMEOBI | Nigeria |
| SERGEY IGNASHEVICH | Russia |
| VASILY BEREZUTSKIY | Russia |
| ALEKSANDR KERZHAKOV | Russia |
| HELDER POSTIGA | Portugal |
| IVICA OLIC | Croatia |
| DIEGO FORLAN | Uruguay |
| VINCENT ENYEAMA | Nigeria |
| ROMAN WEIDENFELLER | Germany |
| JOSE CORONA | Mexico |
| STEPHAN LICHTSTEINER | Switzerland |
| STEVE VON BERGEN | Switzerland |
| DIEGO BENAGLIO | Switzerland |
| LUKAS PODOLSKI | Germany |
| ASHKAN DEJAGAH | Iran |
| WAYNE ROONEY | England |
| KEVIN PRINCE BOATENG | Ghana |
| MATT MCKAY | Australia |
| GLEN JOHNSON | England |
| YAYA TOURE | Ivory Coast |
| EIJI KAWASHIMA | Japan |
| ABEL AGUILAR | Columbia |
| ARTHUR BOKA | Ivory Coast |
| JUNG SUNGRYONG | South Korea |
| DANIEL CAMBRONERO | Costa Rica |
| JEAN MAKOUN | Cameroon |
| EZEQUIEL GARAY | Argentina |
| DAVID SILVA | Spain |
| CESC FABREGAS | Spain |
| MIGUEL VELOSO | Portugal |
| VIEIRINHA DE FREITAS | Portugal |
| CRISTIAN ZAPATA | Columbia |
| LANDRY NGUEMO | Cameroon |
| ADRIAN RAMOS | Columbia |
| FERNANDO GAGO | Argentina |
| CRISTIANO RONALDO | Portugal |
| LUIS SARITAMA | Ecuador |
| CLAUDIO BRAVO | Chile |
| JORGE VALDIVIA | Chile |
| PARK CHUYOUNG | South Korea |
| MAXWELL ANDRADE | Brazil |
| CLINT DEMPSEY | USA |
| PER MERTESACKER | Germany |
| JUANFRAN BELEN | Spain |
| JAMES MILNER | England |
| ASAMOAH GYAN | Ghana |
| DANIELE DE ROSSI | Italy |
| EUGENE GALEKOVIC | Australia |
| JEAN BEAUSEJOUR | Chile |
| FRANCISCO RODRIGUEZ | Mexico |
| TRANQUILLO BARNETTA | Switzerland |
| PHILIPPE SENDEROS | Switzerland |
| RICARDO COSTA | Portugal |
| BRUNO ALVES | Portugal |
| RAUL MEIRELES | Portugal |
| DANIJEL PRANJIC | Croatia |
| DARIJO SRNA | Croatia |
| KWAK TAEHWI | South Korea |
| AMIRHOSSEIN SADEGHI | Iran |
| DONIS ESCOBER | Honduras |
| PIERRE WEBO | Cameroon |
| PETER ODEMWINGIE | Nigeria |
| ANDREA BARZAGLI | Italy |
| BETO BASTOS | Portugal |
| HUGO ALMEIDA | Portugal |
| RIO MAVUBA | France |
| SHUSAKU NISHIKAWA | Japan |
| PATRICK PEMBERTON | Costa Rica |
| WESLEY SNEIJDER | Netherlands |
| IGOR AKINFEEV | Russia |
| GIORGIO CHIELLINI | Italy |
| GUILLERMO OCHOA | Mexico |
| VANGELIS MORAS | Greece |
| LOUKAS VYNTRA | Greece |
| ALBERTO AQUILANI | Italy |
| ANTHONY VANDEN BORRE | Belgium |
| ZVJEZDAN MISIMOVIC | Bosnia & Herzegovina |
| YURY ZHIRKOV | Russia |
| ALEXANDER SAMEDOV | Russia |
| IGOR DENISOV | Russia |
| CRISTIAN RODRIGUEZ | Uruguay |
| VALON BEHRAMI | Switzerland |
| RETO ZIEGLER | Switzerland |
| BLERIM DZEMAILI | Switzerland |
| RON VLAAR | Netherlands |
| RAUL ALBIOL | Spain |
| NICOLAS LOMBAERTS | Belgium |
| THOMAS VERMAELEN | Belgium |
| NIGEL DE JONG | Netherlands |
| GERARD PIQUE | Spain |
| CHARLES ITANDJE | Cameroon |
| CARLOS VALDES | Columbia |
| DIRK KUYT | Netherlands |
| CARLOS SALCIDO | Mexico |
| MARCELO VIEIRA | Brazil |
| WILLIAN DA SILVA | Brazil |
| MARVIN CHAVEZ | Honduras |
| ORIBE PERALTA | Mexico |
| ORESTIS KARNEZIS | Greece |
| ALEXANDROS TZIOLIS | Greece |
| JAVIER HERNANDEZ | Mexico |
| MAURICIO ISLA | Chile |
| CRISTOPHER TOSELLI | Chile |
| ALEXIS SANCHEZ | Chile |
| DAVID OSPINA | Columbia |
| ALEXANDER MEJIA | Columbia |
| FELIPE CAICEDO | Ecuador |
| MANUEL NEUER | Germany |
| IGNAZIO ABATE | Italy |
| ALESSIO CERCI | Italy |
| EZEQUIEL LAVEZZI | Argentina |
| LIONEL MESSI | Argentina |
| CARLOS CARMONA | Chile |
| GONZALO JARA | Chile |
| JOSE FUENZALIDA | Chile |
| JUAN ZUNIGA | Columbia |
| GABRIEL ACHILIER | Ecuador |
| ALVARO PEREIRA | Uruguay |
| CRISTHIAN STUANI | Uruguay |
| DAVID VILLA | Spain |
| JORGE CLAROS | Honduras |
| EMILIO IZAGUIRRE | Honduras |
| ASMIR BEGOVIC | Bosnia & Herzegovina |
| THEOFANIS GEKAS | Greece |
| RODRIGO PALACIO | Argentina |
| TIM KRUL | Netherlands |
| GABRIEL PALETTA | Italy |
| MASAHIKO INOHA | Japan |
| KEISUKE HONDA | Japan |
| MARCELO DIAZ | Chile |
| MICHAEL BRADLEY | USA |
| FRED CHAVES GUEDES | Brazil |
| GIOVANI DOS SANTOS | Mexico |
| MUBARAK WAKASO | Ghana |
| CELSO BORGES | Costa Rica |
| RANDALL BRENES | Costa Rica |
| JOZY ALTIDORE | USA |
| SAMUEL INKOOM | Ghana |
| CARL MEDJANI | Algeria |
| HERNANES VIANA LIMA | Brazil |
| CONSTANT DJAKPA | Ivory Coast |
| LUKA MODRIC | Croatia |
| JOSE ROJAS | Chile |
| FRANCK RIBERY | France |
| PEJMAN MONTAZERI | Iran |
| BEN FOSTER | England |
| MICHEL VORM | Netherlands |
| PAUL VERHAEGH | Netherlands |
| BACARY SAGNA | France |
| VARELA SILVESTRE | Portugal |
| NANI DA CUNHA | Portugal |
| GOKHAN INLER | Switzerland |
| SERGIO ROMERO | Argentina |
| MEHRDAD POOLADI | Iran |
| GARY MEDEL | Chile |
| UWA ECHIEJILE | Nigeria |
| JUAN MATA | Spain |
| EDINSON CAVANI | Uruguay |
| LEE KEUNHO | South Korea |
| PARK JOOHO | South Korea |
| LEE CHUNGYONG | South Korea |
| ATSUTO UCHIDA | Japan |
| SHINJI KAGAWA | Japan |
| GIANCARLO GONZALEZ | Costa Rica |
| TOSHIHIRO AOYAMA | Japan |
| DANIEL OPARE | Ghana |
| RUI PATRICIO | Portugal |
| FABIO COENTRAO | Portugal |
| DAVID DE GEA | Spain |
| MARCOS URENA | Costa Rica |
| EDUARDO VARGAS | Chile |
| LUIS GARRIDO | Honduras |
| JAVI MARTINEZ | Spain |
| LUIS SUAREZ | Uruguay |
| CESAR AZPILICUETA | Spain |
| DAVID LUIZ | Brazil |
| MICHAEL BARRANTES | Costa Rica |
| CARLO COSTLY | Honduras |
| GONZALO HIGUAIN | Argentina |
| EFE AMBROSE | Nigeria |
| DANNY WELBECK | England |
| VICTOR MOSES | Nigeria |
| MAMADOU SAKHO | France |
| KIM SEUNGGYU | South Korea |
| YUN SUKYOUNG | South Korea |
| AGUSTIN ORION | Argentina |
| EMMANUEL AGYEMANG BADU | Ghana |
| YOICHIRO KAKITANI | Japan |
| MANABU SAITO | Japan |
| YUYA OSAKO | Japan |
| TONI KROOS | Germany |
| OSCAR BAGUI | Ecuador |
| JORGE FUCILE | Uruguay |
| BRAD GUZAN | USA |
| JALAL HOSSEINI | Iran |
| KIM CHANGSOO | South Korea |
| AURELIEN CHEDJOU | Cameroon |
| CRISTHIAN NOBOA | Ecuador |
| FABIAN ORELLANA | Chile |
| ALVARO GONZALEZ | Uruguay |
| SHINJI OKAZAKI | Japan |
| MATTEO DARMIAN | Italy |
| EHSAN HAJI SAFI | Iran |
| GERVINHO YAO KOUASSI | Ivory Coast |
| JAMES TROISI | Australia |
| DARIO VIDOSIC | Australia |
| LAURENT CIMAN | Belgium |
| MAROUANE FELLAINI | Belgium |
| KEVIN MIRALLAS | Belgium |
| NICOLAS NKOULOU | Cameroon |
| OUSMANE DIARRASSOUBA | Ivory Coast |
| YUTO NAGATOMO | Japan |
| KIM BOKYUNG | South Korea |
| PARK JONGWOO | South Korea |
| KWADWO ASAMOAH | Ghana |
| ANDRE AYEW | Ghana |
| JAMES HOLLAND | Australia |
| AHMAD ALNAMEH | Iran |
| HASHEM BEIKZADEH | Iran |
| BENJAMIN MOUKANDJO | Cameroon |
| ALEJANDRO BEDOYA | USA |
| MARIO MARTINEZ | Honduras |
| JUAN CARLOS GARCIA | Honduras |
| EDDER DELGADO | Honduras |
| EL ARABI SOUDANI | Algeria |
| KARIM BENZEMA | France |
| HUGO LLORIS | France |
| MATHIEU VALBUENA | France |
| OSMAN CHAVEZ | Honduras |
| VALENTIN STOCKER | Switzerland |
| OGNJEN VUKOJEVIC | Croatia |
| ROMAN SHIROKOV | Russia |
| SANTI CAZORLA | Spain |
| KONAN YA | Ivory Coast |
| RAHMAN AHMADI | Iran |
| JOHN BOYE | Ghana |
| HENRIQUE ADRIANO BUSS | Brazil |
| SALVATORE SIRIGU | Italy |
| ANTONIO CANDREVA | Italy |
| HA DAESUNG | South Korea |
| JEREMAIN LENS | Netherlands |
| SIMON MIGNOLET | Belgium |
| AUGUSTO FERNANDEZ | Argentina |
| RUBEN AMORIM | Portugal |
| EDUARDO CARVALHO | Portugal |
| LAZAROS CHRISTODOULOPOULOS | Greece |
| MIRALEM PJANIC | Bosnia & Herzegovina |
| JEROME BOATENG | Germany |
| LOIC REMY | France |
| DANIJEL SUBASIC | Croatia |
| DOMAGOJ VIDA | Croatia |
| MENSUR MUJDZA | Bosnia & Herzegovina |
| JOE HART | England |
| PHIL JAGIELKA | England |
| VICTOR FAYZULIN | Russia |
| SENIJAD IBRICIC | Bosnia & Herzegovina |
| SEJAD SALIHOVIC | Bosnia & Herzegovina |
| EDIN DZEKO | Bosnia & Herzegovina |
| RON-ROBERT ZIELER | Germany |
| ALAN DZAGOEV | Russia |
| MAXIMO BANGUERA | Ecuador |
| SERGIO BUSQUETS | Spain |
| CARLOS PENA | Mexico |
| ALEXANDER DOMINGUEZ | Ecuador |
| ROGER ESPINOZA | Honduras |
| MESUT OEZIL | Germany |
| ABEL HERNANDEZ | Uruguay |
| NICOLAS LODEIRO | Uruguay |
| SEBASTIAN COATES | Uruguay |
| MOHAMED ZEMMAMOUCHE | Algeria |
| GARY CAHILL | England |
| LEIGHTON BAINES | England |
| JOAO ROJAS | Ecuador |
| MARIANO ANDUJAR | Argentina |
| BENOIT ASSOU EKOTTO | Cameroon |
| KHOSRO HEYDARI | Iran |
| JASON DAVIDSON | Australia |
| RYAN MCGOWAN | Australia |
| TOMMY OAR | Australia |
| DANIEL STURRIDGE | England |
| JONATHAN MENSAH | Ghana |
| CIRO IMMOBILE | Italy |
| LEE BUMYOUNG | South Korea |
| OKECHUKWU UCHEBO | Nigeria |
| JORDI ALBA | Spain |
| CRISTIAN GAMBOA | Costa Rica |
| JOEL CAMPBELL | Costa Rica |
| DIEGO REYES | Mexico |
| MARIO GOETZE | Germany |
| MATTIA PERIN | Italy |
| VICTOR LEANDRO BAGY | Brazil |
| JUAN CUADRADO | Columbia |
| TEOFILO GUTIERREZ | Columbia |
| MATS HUMMELS | Germany |
| FABIAN JOHNSON | USA |
| HARIS SEFEROVIC | Switzerland |
| MICHAEL BABATUNDE | Nigeria |
| GASTON RAMIREZ | Uruguay |
| OLIVER BOZANIC | Australia |
| ERIC CHOUPO MOTING | Cameroon |
| MIX DISKERUD | USA |
| JOEL VELTMAN | Netherlands |
| KOKE MERODIO | Spain |
| RICARDO RODRIGUEZ | Switzerland |
| HEINER MORA | Costa Rica |
| JACKSON MARTINEZ | Columbia |
| ESTEBAN PAREDES | Chile |
| KENNETH OMERUO | Nigeria |
| OGENYI ONAZI | Nigeria |
| RAMON AZEEZ | Nigeria |
| SANTIAGO ARIAS | Columbia |
| NIKICA JELAVIC | Croatia |
| ENZO PEREZ | Argentina |
| MOUSSA SISSOKO | France |
| HENRI BEDIMO | Cameroon |
| FEDERICO FERNANDEZ | Argentina |
| PEDRO RODRIGUEZ | Spain |
| KONSTANTINOS MITROGLOU | Greece |
| GEORGIOS TZAVELAS | Greece |
| MARCO FABIAN | Mexico |
| ELIAQUIM MANGALA | France |
| SOFIANE FEGHOULI | Algeria |
| PANAGIOTIS TACHTSIDIS | Greece |
| DARYL JANMAAT | Netherlands |
| LEROY FER | Netherlands |
| GEORGINIO WIJNALDUM | Netherlands |
| YANN SOMMER | Switzerland |
| MARIO GAVRANOVIC | Switzerland |
| RODRIGO MUNOZ | Uruguay |
| JOEL MATIP | Cameroon |
| LEONARDO BONUCCI | Italy |
| XHERDAN SHAQIRI | Switzerland |
| MEDHI LACEN | Algeria |
| MATHEW LECKIE | Australia |
| THOMAS MUELLER | Germany |
| IOANNIS MANIATIS | Greece |
| SHUICHI GONDA | Japan |
| GOTOKU SAKAI | Japan |
| KIM SHINWOOK | South Korea |
| RAIS MBOLHI | Algeria |
| STEPHEN ADAMS | Ghana |
| VINCENT ABOUBAKAR | Cameroon |
| HECTOR HERRERA | Mexico |
| GHASEM HADADIFAR | Iran |
| HOSSEIN MAHINI | Iran |
| JACK WILSHERE | England |
| JI DONGWON | South Korea |
| ANDY NAJAR | Honduras |
| GIOVANNI SIO | Ivory Coast |
| CARLOS GRUEZO | Ecuador |
| TERENCE KONGOLO | Netherlands |
| MEMPHIS DEPAY | Netherlands |
| CLEMENT GRENIER | France |
| ANTOINE GRIEZMANN | France |
| LUKE SHAW | England |
| SIME VRSALJKO | Croatia |
| ALAN PULIDO | Mexico |
| DIEGO CALVO | Costa Rica |
| ALEX IBARRA | Ecuador |
| UCHE NWOFOR | Nigeria |
| MASSIMO LUONGO | Australia |
| MATY RYAN | Australia |
| JOHNNY ACOSTA | Costa Rica |
| CHRIS WONDOLOWSKI | USA |
| BRAYAN BECKELES | Honduras |
| MATEO KOVACIC | Croatia |
| AHMED MUSA | Nigeria |
| BAKHTIAR RAHMANI | Iran |
| ALIREZA HAGHIGHI | Iran |
| HIROSHI KIYOTAKE | Japan |
| CARLOS BACCA | Columbia |
| VICTOR IBARBO | Columbia |
| ADRIAN BONE | Ecuador |
| JAIMEN AYOVI | Ecuador |
| MICHAEL ARROYO | Ecuador |
| FELIPE GUTIERREZ | Chile |
| RICARDO ALVAREZ | Argentina |
| JOSE MARIA BASANTA | Argentina |
| HIROKI SAKAI | Japan |
| JAVIER AQUINO | Mexico |
| MIGUEL PONCE | Mexico |
| MARCO REUS | Germany |
| WILFRIED BONY | Ivory Coast |
| MAX GRADEL | Ivory Coast |
| ADAM KWARASEY | Ghana |
| REZA HAGHIGHI | Iran |
| ISLAM SLIMANI | Algeria |
| MEHDI MOSTEFA | Algeria |
| ABDELMOUMENE DJABOU | Algeria |
| ALLAN NYOM | Cameroon |
| LOIC FEUDJOU | Cameroon |
| CHRISTIAN ATSU | Ghana |
| ESSEID BELKALEM | Algeria |
| CEDRIC SI MOHAMMED | Algeria |
| JORDAN HENDERSON | England |
| ALEX OXLADE CHAMBERLAIN | England |
| IZET HAJROVIC | Bosnia & Herzegovina |
| MICHAEL LANG | Switzerland |
| ADMIR MEHMEDI | Switzerland |
| FABIAN SCHAER | Switzerland |
| GODFREY OBOABONA | Nigeria |
| HOTARU YAMAGUCHI | Japan |
| HWANG SEOKHO | South Korea |
| HUGO CAMPAGNARO | Argentina |
| BERNARD DUARTE | Brazil |
| ROMAN BUERKI | Switzerland |
| JUAN QUINTERO | Columbia |
| RUBEN GABRIEL | Nigeria |
| AZUBUIKE EGWUEKWE | Nigeria |
| RAUL JIMENEZ | Mexico |
| CHRIS SMALLING | England |
| MITCH LANGERAK | Australia |
| GORDON SCHILDENFELD | Croatia |
| PHIL JONES | England |
| MATHIEU DEBUCHY | France |
| LAURENT KOSCIELNY | France |
| OLIVIER GIROUD | France |
| JOSE CHOLEVAS | Greece |
| IOANNIS FETFATZIDIS | Greece |
| VLADIMIR GRANAT | Russia |
| ALEXANDER KOKORIN | Russia |
| ASMIR AVDUKIC | Bosnia & Herzegovina |
| SENAD LULIC | Bosnia & Herzegovina |
| THIBAUT COURTOIS | Belgium |
| NACER CHADLI | Belgium |
| ROMELU LUKAKU | Belgium |
| DRIES MERTENS | Belgium |
| KEVIN DE BRUYNE | Belgium |
| JULIAN DRAXLER | Germany |
| KEVIN GROSSKREUTZ | Germany |
| JORDY CLASIE | Netherlands |
| JASPER CILLESSEN | Netherlands |
| GEORGY SHCHENNIKOV | Russia |
| ALEXEY IONOV | Russia |
| MAKSIM KANUNNIKOV | Russia |
| ANDREY ESHCHENKO | Russia |
| MARCO VERRATTI | Italy |
| LORENZO INSIGNE | Italy |
| SAMMIR CRUZ CAMPOS | Croatia |
| IVAN PERISIC | Croatia |
| LUIS NETO | Portugal |
| EDER LOPES | Portugal |
| RAPHAEL VARANE | France |
| STEPHANE RUFFIER | France |
| TONI SUNJIC | Bosnia & Herzegovina |
| OGNJEN VRANJES | Bosnia & Herzegovina |
| AVDIJA VRSAJEVIC | Bosnia & Herzegovina |
| PANAGIOTIS KONE | Greece |
| PANAGIOTIS GLYKOS | Greece |
| CAMILO VARGAS | Columbia |
| GRAHAM ZUSI | USA |
| REZA GHOOCHANNEJAD | Iran |
| STEFANOS KAPINO | Greece |
| PAULINHO MACIEL JR. | Brazil |
| FRASER FORSTER | England |
| FAOUZI GHOULAM | Algeria |
| ALBERT ADOMAH | Ghana |
| EMMANUEL EMENIKE | Nigeria |
| JUAN MONTES | Honduras |
| TIMMY CHANDLER | USA |
| EDIN VISCA | Bosnia & Herzegovina |
| YACINE BRAHIMI | Algeria |
| SAPHIR TAIDER | Algeria |
| SERGEY RYZHIKOV | Russia |
| YURY LODYGIN | Russia |
| ARON JOHANNSSON | USA |
| FABRICE OLINGA | Cameroon |
| JASMIN FEJZIC | Bosnia & Herzegovina |
| MATT BESLER | USA |
| JEAN DANIEL AKPA | Ivory Coast |
| SYLVAIN GBOHOUO | Ivory Coast |
| DIEGO COSTA | Spain |
| EBENEZER ODUNLAMI | Nigeria |
| RAFA SILVA | Portugal |
| LUCAS DIGNE | France |
| ADAM TAGGART | Australia |
| DEANDRE YEDLIN | USA |
| OLIVER ZELENIKA | Croatia |
| IVAN MOCINIC | Croatia |
| JULIAN GREEN | USA |
| EDER BALANTA | Columbia |
| RONY MARTINEZ | Honduras |
| IVAN FRANJIC | Australia |
| SAMMY NDJOCK | Cameroon |
| RASHID SUMAILA | Ghana |
| SERGE AURIER | Ivory Coast |
| MATHIS BOLLY | Ivory Coast |
| ISAAC BRIZUELA | Mexico |
| MIGUEL LAYUN | Mexico |
| ERMIN BICAKCIC | Bosnia & Herzegovina |
| RICKIE LAMBERT | England |
| JOHN BROOKS | USA |
| ANEL HADZIC | Bosnia & Herzegovina |
| ANDRE ALMEIDA | Portugal |
| FIDEL MARTINEZ | Ecuador |
| MARIO MANDZUKIC | Croatia |
| DANIEL VAN BUYTEN | Belgium |
| KIM YOUNGGWON | South Korea |
| FATAWU DAUDA | Ghana |
| SEAD KOLASINAC | Bosnia & Herzegovina |
| AISSA MANDI | Algeria |
| WILLIAM CARVALHO | Portugal |
| ISMAEL DIOMANDE | Ivory Coast |
| MARCO PAROLO | Italy |
| BEN HALLORAN | Australia |
| BAILEY WRIGHT | Australia |
| MATTHIAS GINTER | Germany |
| TINO SVEN SUSIC | Bosnia & Herzegovina |
| ALIREZA JAHAN BAKHSH | Iran |
| DANIEL DAVARI | Iran |
| STEVEN BEITASHOUR | Iran |
| MIIKO ALBORNOZ | Chile |
| ADNAN JANUZAJ | Belgium |
| DIVOCK ORIGI | Belgium |
| RIYAD MAHREZ | Algeria |
| ERIK DURM | Germany |
| ACQUAH AFRIYIE | Ghana |
| WAYLON FRANCIS | Costa Rica |
| ANDREY SEMENOV | Russia |
| MARCELO BROZOVIC | Croatia |
| SAYOUBA MANDE | Ivory Coast |
| LUIS LOPEZ | Honduras |
| CEDRIC DJEUGOUE | Cameroon |
| CHRISTOPH KRAMER | Germany |
| SAMMY BOSSUT | Belgium |
+----------------------------+----------------------+
572 rows in set (0.00 sec)
mysql>
mysql> /*
/*> 6)Retrieve the name and country of the players with the most Red cards in the 2014 world cup.
/*> */
mysql>
mysql> SELECT
-> Name, Country
-> FROM
-> PLAYERS PL
-> INNER JOIN
-> PLAYER_CARDS PC
-> ON
-> PL.Player_id = PC.Player_id
-> WHERE
-> PC.Red_Cards IN (SELECT
-> MAX(Red_Cards)
-> FROM
-> PLAYER_CARDS);
+--------------------------+------------+
| Name | Country |
+--------------------------+------------+
| ALEXANDRE SONG | Cameroon |
| ANTONIO VALENCIA | Ecuador |
| WILSON PALACIOS | Honduras |
| KONSTANTINOS KATSOURANIS | Greece |
| PEPE FERREIRA | Portugal |
| MAXIMILIANO PEREIRA | Uruguay |
| STEVEN DEFOUR | Belgium |
| CLAUDIO MARCHISIO | Italy |
| OSCAR DUARTE | Costa Rica |
| ANTE REBIC | Croatia |
+--------------------------+------------+
10 rows in set (0.00 sec)
mysql>
mysql> /*
/*> 7)For each Host city, retrieve the HostCity and the total number of games played in that city.
/*> */
mysql>
mysql> SELECT
-> Host_city, COUNT(1) AS 'Games Played'
-> FROM
-> MATCH_RESULTS
-> GROUP BY
-> Host_city;
+----------------+--------------+
| Host_city | Games Played |
+----------------+--------------+
| Sao Paulo | 6 |
| Natal | 4 |
| Salvador | 6 |
| Cuiaba | 4 |
| Belo Horizonte | 6 |
| Recife | 5 |
| Fortaleza | 6 |
| Manaus | 4 |
| Brasilia | 7 |
| Porto Alegre | 5 |
| Rio De Janerio | 7 |
| Curitiba | 4 |
+----------------+--------------+
12 rows in set (0.00 sec)
mysql>
mysql> /*
/*> 8)Retrieve the names of host city with the most games played in that city.
/*> */
mysql>
mysql> SELECT
-> Host_city, COUNT(1) AS 'Most Games Played'
-> FROM
-> MATCH_RESULTS
-> GROUP BY
-> Host_city
-> HAVING COUNT(1) = (
-> SELECT
-> MAX(y.num)
-> FROM
-> (
-> SELECT
-> Host_city, COUNT(1) AS num
-> FROM
-> MATCH_RESULTS x
-> GROUP BY
-> Host_city
-> ) y
-> );
+----------------+-------------------+
| Host_city | Most Games Played |
+----------------+-------------------+
| Brasilia | 7 |
| Rio De Janerio | 7 |
+----------------+-------------------+
2 rows in set (0.01 sec)
mysql>
mysql>
mysql> /*
/*> 9)For each country, retrieve the country name and the number of games they played as Team1 in the MATCH_RESULTS table, and the total goals scored (SUM of Team1_score) and the goals against (SUM of Team2_score).
/*> */
mysql>
mysql> Select
-> Team1 AS 'Country Name',
-> Count(Match_id) AS 'Games Played As Team 1',
-> SUM(Team1_score) AS 'Total Goals Scored',
-> SUM(Team2_score) AS 'Total Goals Against'
-> FROM
-> MATCH_RESULTS
-> Group By
-> Team1;
+----------------------+------------------------+--------------------+---------------------+
| Country Name | Games Played As Team 1 | Total Goals Scored | Total Goals Against |
+----------------------+------------------------+--------------------+---------------------+
| Algeria | 1 | 1 | 1 |
| Argentina | 4 | 5 | 1 |
| Australia | 2 | 2 | 6 |
| Belgium | 3 | 5 | 2 |
| Bosnia & Herzegovina | 1 | 3 | 1 |
| Brazil | 6 | 7 | 13 |
| Cameroon | 2 | 1 | 8 |
| Chile | 1 | 3 | 1 |
| Columbia | 3 | 7 | 1 |
| Costa Rica | 2 | 1 | 1 |
| Croatia | 1 | 1 | 3 |
| Ecuador | 1 | 0 | 0 |
| England | 1 | 1 | 2 |
| France | 3 | 5 | 1 |
| Germany | 4 | 9 | 3 |
| Ghana | 1 | 1 | 2 |
| Greece | 1 | 2 | 1 |
| Honduras | 2 | 1 | 5 |
| Iran | 1 | 0 | 0 |
| Italy | 2 | 0 | 2 |
| Ivory Coast | 1 | 2 | 1 |
| Japan | 2 | 1 | 4 |
| Mexico | 1 | 1 | 0 |
| Netherlands | 4 | 4 | 1 |
| Nigeria | 2 | 3 | 3 |
| Portugal | 1 | 2 | 1 |
| Russia | 1 | 1 | 1 |
| South Korea | 2 | 2 | 5 |
| Spain | 2 | 1 | 7 |
| Switzerland | 2 | 4 | 6 |
| Uruguay | 2 | 3 | 4 |
| USA | 2 | 2 | 3 |
+----------------------+------------------------+--------------------+---------------------+
32 rows in set (0.00 sec)
mysql>
mysql> /*
/*> 10)For each country, retrieve the country name and the number of games they played as Team2 in the MATCH_RESULTS table, and the total goals scored (SUM of Team12_score) and the goals against (SUM of Team1_score)
/*> */
mysql>
mysql> Select
-> Team2 AS 'Country Name',
-> Count(Match_id) AS 'Games Played As Team 2',
-> SUM(Team2_score) AS 'Total Goals Scored',
-> SUM(Team1_score) AS 'Total Goals Against'
-> FROM
-> MATCH_RESULTS
-> Group By
-> Team2;
+----------------------+------------------------+--------------------+---------------------+
| Country Name | Games Played As Team 2 | Total Goals Scored | Total Goals Against |
+----------------------+------------------------+--------------------+---------------------+
| Algeria | 3 | 6 | 6 |
| Argentina | 3 | 3 | 3 |
| Australia | 1 | 1 | 3 |
| Belgium | 2 | 1 | 1 |
| Bosnia & Herzegovina | 2 | 1 | 3 |
| Brazil | 1 | 4 | 1 |
| Cameroon | 1 | 0 | 1 |
| Chile | 3 | 3 | 3 |
| Columbia | 2 | 5 | 3 |
| Costa Rica | 3 | 4 | 1 |
| Croatia | 2 | 5 | 3 |
| Ecuador | 2 | 3 | 3 |
| England | 2 | 1 | 2 |
| France | 2 | 5 | 2 |
| Germany | 3 | 9 | 1 |
| Ghana | 2 | 3 | 4 |
| Greece | 3 | 1 | 4 |
| Honduras | 1 | 0 | 3 |
| Iran | 2 | 1 | 4 |
| Italy | 1 | 2 | 1 |
| Ivory Coast | 2 | 2 | 4 |
| Japan | 1 | 1 | 2 |
| Mexico | 3 | 4 | 3 |
| Netherlands | 3 | 11 | 3 |
| Nigeria | 2 | 0 | 2 |
| Portugal | 2 | 2 | 6 |
| Russia | 2 | 1 | 2 |
| South Korea | 1 | 1 | 1 |
| Spain | 1 | 3 | 0 |
| Switzerland | 2 | 3 | 1 |
| Uruguay | 2 | 1 | 2 |
| USA | 2 | 3 | 3 |
+----------------------+------------------------+--------------------+---------------------+
32 rows in set (0.00 sec)
mysql>
mysql> /*
/*> 11)Write a query that combines the results of the queries in 9. and 10. To get the total number of games each country has played (either as Team1 or as Team2), their total goals scored and their total goals against. Create a view TEAM_SUMMARY that has the following data attributes to hold the result of the combined query: CountryName, NoOfGames, TotalGoalsFor, TotalGoalsAgainst. Order in descending order of number of games played.
/*> */
mysql>
mysql> CREATE OR REPLACE VIEW TEAMS_SUMMARY AS
-> SELECT
-> t.Team1,
-> SUM(t.matches) as 'Games Played',
-> SUM(t.total_scored) as 'Total Goals For',
-> SUM(t.total_against) as 'Total Goals Against'
-> FROM
-> (
-> SELECT
-> Team1,
-> COUNT(1) as matches,
-> SUM(Team1_score) as total_scored,
-> SUM(Team2_score) as total_against
-> FROM
-> MATCH_RESULTS
-> GROUP BY
-> Team1
->
-> UNION
->
-> SELECT
-> Team2,
-> COUNT(1) as matches,
-> SUM(Team2_score) as total_scored,
-> SUM(Team1_score) as total_against
-> FROM
-> MATCH_RESULTS
-> GROUP BY
-> Team2
-> ) t
-> GROUP BY
-> t.Team1
-> ORDER BY
-> SUM(t.matches) DESC;
Query OK, 0 rows affected (0.55 sec)
mysql>
mysql> SELECT * FROM TEAMS_SUMMARY;
+----------------------+--------------+-----------------+---------------------+
| Team1 | Games Played | Total Goals For | Total Goals Against |
+----------------------+--------------+-----------------+---------------------+
| Argentina | 7 | 8 | 4 |
| Brazil | 7 | 11 | 14 |
| Netherlands | 7 | 15 | 4 |
| Germany | 7 | 18 | 4 |
| Belgium | 5 | 6 | 3 |
| Columbia | 5 | 12 | 4 |
| Costa Rica | 5 | 5 | 2 |
| France | 5 | 10 | 3 |
| USA | 4 | 5 | 6 |
| Algeria | 4 | 7 | 7 |
| Uruguay | 4 | 4 | 6 |
| Switzerland | 4 | 7 | 7 |
| Nigeria | 4 | 3 | 5 |
| Mexico | 4 | 5 | 3 |
| Greece | 4 | 3 | 5 |
| Chile | 4 | 6 | 4 |
| Croatia | 3 | 6 | 6 |
| Australia | 3 | 3 | 9 |
| Bosnia & Herzegovina | 3 | 4 | 4 |
| Cameroon | 3 | 1 | 9 |
| Spain | 3 | 4 | 7 |
| South Korea | 3 | 3 | 6 |
| Russia | 3 | 2 | 3 |