create table olympic_medal_winners (
olympic_year int,
sport varchar2( 30 ),
gender varchar2( 1 ),
event varchar2( 128 ),
medal varchar2( 10 ),
noc varchar2( 3 ),
athlete varchar2( 128 )
)
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Archery','M','Men''s Individual','Gold','KOR','KU Bonchan')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Archery','M','Men''s Individual','Silver','FRA','VALLADONT Jean-Charles')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Archery','M','Men''s Individual','Bronze','USA','ELLISON Brady')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Archery','M','Men''s Team','Gold','KOR','Republic of Korea')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Archery','M','Men''s Team','Bronze','AUS','Australia')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Archery','M','Men''s Team','Silver','USA','United States')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Artistic Gymnastics','M','Men''s Floor Exercise','Gold','GBR','WHITLOCK Max')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Artistic Gymnastics','M','Men''s Floor Exercise','Bronze','BRA','MARIANO Arthur')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Artistic Gymnastics','M','Men''s Floor Exercise','Silver','BRA','HYPOLITO Diego')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Artistic Gymnastics','M','Men''s Horizontal Bar','Gold','GER','HAMBUECHEN Fabian')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Artistic Gymnastics','M','Men''s Horizontal Bar','Bronze','GBR','WILSON Nile')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Artistic Gymnastics','M','Men''s Horizontal Bar','Silver','USA','LEYVA Danell')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Athletics','M','Men''s 10,000m','Gold','GBR','FARAH Mohamed')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Athletics','M','Men''s 10,000m','Bronze','ETH','TOLA Tamirat')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Athletics','M','Men''s 10,000m','Silver','KEN','TANUI Paul Kipngetich')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Athletics','M','Men''s 100m','Gold','JAM','BOLT Usain')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Athletics','M','Men''s 100m','Silver','USA','GATLIN Justin')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Athletics','M','Men''s 100m','Bronze','CAN','DE GRASSE Andre')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Badminton','M','Men''s Doubles','Gold','CHN','Zhang')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Badminton','M','Men''s Doubles','Bronze','GBR','Langridge')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Badminton','M','Men''s Doubles','Bronze','GBR','Ellis')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Badminton','M','Men''s Doubles','Silver','MAS','Tan')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Badminton','M','Men''s Doubles','Silver','MAS','Goh')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Badminton','M','Men''s Doubles','Gold','CHN','Fu')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Beach Volleyball','M','Men','Gold','BRA','Cerutti')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Beach Volleyball','M','Men','Gold','BRA','Oscar Schmidt')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Beach Volleyball','M','Men','Silver','ITA','Nicolai')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Beach Volleyball','M','Men','Silver','ITA','Lupo')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Beach Volleyball','M','Men','Bronze','NED','Meeuwsen')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Beach Volleyball','M','Men','Bronze','NED','Brouwer')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Boxing','M','Men''s Bantam (56kg)','Gold','CUB','RAMIREZ Robeisy')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Boxing','M','Men''s Bantam (56kg)','Bronze','UZB','AKHMADALIEV Murodjon')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Boxing','M','Men''s Bantam (56kg)','Bronze','RUS','NIKITIN Vladimir')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Boxing','M','Men''s Bantam (56kg)','Silver','USA','STEVENSON Shakur')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Boxing','M','Men''s Fly (52kg)','Gold','UZB','ZOIROV Shakhobidin')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Boxing','M','Men''s Fly (52kg)','Bronze','CHN','HU Jianguan')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Canoe Slalom','M','Canoe Double (C2) Men','Gold','SVK','PETER Skantar')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Canoe Slalom','M','Canoe Double (C2) Men','Bronze','FRA','GAUTHIER Klauss')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Canoe Slalom','M','Canoe Double (C2) Men','Bronze','FRA','MATTHIEU Peche')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Canoe Slalom','M','Canoe Double (C2) Men','Silver','GBR','RICHARD Hounslow')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Canoe Slalom','M','Canoe Double (C2) Men','Silver','GBR','DAVID Florence')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Canoe Slalom','M','Canoe Double (C2) Men','Gold','SVK','LADISLAV Skantar')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Canoe Sprint','M','Men''s Canoe Double 1000m','Gold','GER','Brendel')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Canoe Sprint','M','Men''s Canoe Double 1000m','Bronze','UKR','Mishchuk')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Canoe Sprint','M','Men''s Canoe Double 1000m','Bronze','UKR','Ianchuk')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Canoe Sprint','M','Men''s Canoe Double 1000m','Silver','BRA','Queiroz dos Santos')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Canoe Sprint','M','Men''s Canoe Double 1000m','Silver','BRA','de Souza Silva')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Canoe Sprint','M','Men''s Canoe Double 1000m','Gold','GER','Vandrey')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Cycling Road','M','Men''s Individual Time Trial','Gold','SUI','CANCELLARA Fabian')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Cycling Road','M','Men''s Individual Time Trial','Bronze','GBR','FROOME Christopher')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Cycling Road','M','Men''s Individual Time Trial','Silver','NED','DUMOULIN Tom')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Cycling Road','M','Men''s Road Race','Gold','BEL','VAN AVERMAET Greg')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Cycling Road','M','Men''s Road Race','Silver','DEN','FUGLSANG Jakob')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Cycling Road','M','Men''s Road Race','Bronze','POL','MAJKA Rafal')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Cycling Track','M','Men''s Keirin','Gold','GBR','KENNY Jason')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Cycling Track','M','Men''s Keirin','Bronze','MAS','AWANG Azizulhasni')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Cycling Track','M','Men''s Keirin','Silver','NED','BUCHLI Matthijs')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Cycling Track','M','Men''s Omnium','Gold','ITA','VIVIANI Elia')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Cycling Track','M','Men''s Omnium','Bronze','DEN','HANSEN Lasse Norman')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Cycling Track','M','Men''s Omnium','Silver','GBR','CAVENDISH Mark')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Diving','M','Men''s 10m Platform','Gold','CHN','CHEN Aisen')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Diving','M','Men''s 10m Platform','Bronze','USA','BOUDIA David')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Diving','M','Men''s 10m Platform','Silver','MEX','SANCHEZ German')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Diving','M','Men''s 3m Springboard','Gold','CHN','CAO Yuan')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Diving','M','Men''s 3m Springboard','Silver','GBR','LAUGHER Jack')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Diving','M','Men''s 3m Springboard','Bronze','GER','HAUSDING Patrick')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Equestrian','X','Dressage Individual','Gold','GBR','DUJARDIN Charlotte')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Equestrian','X','Dressage Individual','Bronze','GER','BRORING-SPREHE Kristina')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Equestrian','X','Dressage Individual','Silver','GER','WERTH Isabell')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Equestrian','X','Dressage Team','Gold','GER','Germany')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Equestrian','X','Dressage Team','Bronze','USA','United States')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Equestrian','X','Dressage Team','Silver','GBR','Great Britain')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Fencing','M','Men''s Foil Individual','Gold','ITA','GAROZZO Daniele')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Fencing','M','Men''s Foil Individual','Silver','USA','MASSIALAS Alexander')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Fencing','M','Men''s Foil Individual','Bronze','RUS','SAFIN Timur')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Fencing','M','Men''s Foil Team','Gold','RUS','Russian Federation')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Fencing','M','Men''s Foil Team','Bronze','USA','United States')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Fencing','M','Men''s Foil Team','Silver','FRA','France')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Handball','M','Men','Gold','DEN','Denmark')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Handball','M','Men','Silver','FRA','France')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Handball','M','Men','Bronze','GER','Germany')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Handball','W','Women','Gold','RUS','Russian Federation')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Handball','W','Women','Silver','FRA','France')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Handball','W','Women','Bronze','NOR','Norway')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Hockey','M','Men','Gold','ARG','Argentina')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Hockey','M','Men','Silver','BEL','Belgium')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Hockey','M','Men','Bronze','GER','Germany')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Hockey','W','Women','Gold','GBR','Great Britain')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Hockey','W','Women','Silver','NED','Netherlands')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Hockey','W','Women','Bronze','GER','Germany')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Judo','M','Men +100 kg','Gold','FRA','RINER Teddy')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Judo','M','Men +100 kg','Bronze','BRA','SILVA Rafael')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Judo','M','Men +100 kg','Bronze','ISR','SASSON Or')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Judo','M','Men +100 kg','Silver','JPN','HARASAWA Hisayoshi')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Judo','M','Men -100 kg','Gold','CZE','KRPALEK Lukas')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Judo','M','Men -100 kg','Bronze','FRA','MARET Cyrille')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Modern Pentathlon','M','Men''s Individual','Gold','RUS','LESUN Alexander')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Modern Pentathlon','M','Men''s Individual','Silver','UKR','TYMOSHCHENKO Pavlo')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Modern Pentathlon','M','Men''s Individual','Bronze','MEX','HERNANDEZ USCANGA Ismael Marcelo')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Modern Pentathlon','W','Women''s Individual','Gold','AUS','ESPOSITO Chloe')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Modern Pentathlon','W','Women''s Individual','Silver','FRA','CLOUVEL Elodie')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Modern Pentathlon','W','Women''s Individual','Bronze','POL','NOWACKA Oktawia')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Rhythmic Gymnastics','W','Group All-Around','Gold','RUS','Russian Federation')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Rhythmic Gymnastics','W','Group All-Around','Bronze','BUL','Bulgaria')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Rhythmic Gymnastics','W','Group All-Around','Silver','ESP','Spain')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Rhythmic Gymnastics','W','Individual All-Around','Gold','RUS','MAMUN Margarita')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Rhythmic Gymnastics','W','Individual All-Around','Silver','RUS','KUDRYAVTSEVA Yana')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Rhythmic Gymnastics','W','Individual All-Around','Bronze','UKR','RIZATDINOVA Ganna')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Rowing','M','Lightweight Men''s Double Sculls','Gold','FRA','Azou')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Rowing','M','Lightweight Men''s Double Sculls','Bronze','NOR','Brun')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Rowing','M','Lightweight Men''s Double Sculls','Bronze','NOR','Strandli')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Rowing','M','Lightweight Men''s Double Sculls','Silver','IRL','O''Donovan')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Rowing','M','Lightweight Men''s Double Sculls','Silver','IRL','O''Donovan')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Rowing','M','Lightweight Men''s Double Sculls','Gold','FRA','Houin')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Sailing','M','470 Men','Gold','CRO','Fantela')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Sailing','M','470 Men','Bronze','GRE','Kagialis')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Sailing','M','470 Men','Bronze','GRE','Mantis')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Sailing','M','470 Men','Silver','AUS','Ryan')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Sailing','M','470 Men','Silver','AUS','Belcher')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Sailing','M','470 Men','Gold','CRO','Marenic')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Swimming','M','Men''s 100m Backstroke','Gold','USA','MURPHY Ryan')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Swimming','M','Men''s 100m Backstroke','Bronze','USA','PLUMMER David')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Swimming','M','Men''s 100m Backstroke','Silver','CHN','XU Jiayu')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Swimming','M','Men''s 100m Breaststroke','Gold','GBR','PEATY Adam')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Swimming','M','Men''s 100m Breaststroke','Bronze','USA','MILLER Cody')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Swimming','M','Men''s 100m Breaststroke','Silver','RSA','VAN DER BURGH Cameron')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Table Tennis','M','Men''s Singles','Gold','CHN','MA Long')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Table Tennis','M','Men''s Singles','Bronze','JPN','MIZUTANI Jun')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Table Tennis','M','Men''s Singles','Silver','CHN','ZHANG Jike')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Table Tennis','M','Men''s Team','Gold','CHN','China')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Table Tennis','M','Men''s Team','Bronze','GER','Germany')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Table Tennis','M','Men''s Team','Silver','JPN','Japan')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Taekwondo','M','Men +80kg','Gold','AZE','ISAEV Radik')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Taekwondo','M','Men +80kg','Bronze','KOR','CHA Dongmin')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Taekwondo','M','Men +80kg','Bronze','BRA','SIQUEIRA Maicon')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Taekwondo','M','Men +80kg','Silver','NIG','ISSOUFOU ALFAGA Abdoulrazak')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Taekwondo','M','Men -58kg','Gold','CHN','ZHAO Shuai')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Taekwondo','M','Men -58kg','Silver','THA','HANPRAB Tawin')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Tennis','M','Men''s Doubles','Gold','ESP','Lopez')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Tennis','M','Men''s Doubles','Bronze','USA','Johnson')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Tennis','M','Men''s Doubles','Bronze','USA','Sock')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Tennis','M','Men''s Doubles','Silver','ROU','Tecau')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Tennis','M','Men''s Doubles','Silver','ROU','Mergea')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Tennis','M','Men''s Doubles','Gold','ESP','Nadal')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Trampoline Gymnastics','M','Men','Gold','BLR','HANCHAROU Uladzislau')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Trampoline Gymnastics','M','Men','Silver','CHN','DONG Dong')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Trampoline Gymnastics','M','Men','Bronze','CHN','GAO Lei')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Trampoline Gymnastics','W','Women','Gold','CAN','MACLENNAN Rosannagh')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Trampoline Gymnastics','W','Women','Silver','GBR','PAGE Bryony')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Trampoline Gymnastics','W','Women','Bronze','CHN','LI Dan')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Triathlon','M','Men','Gold','GBR','BROWNLEE Alistair')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Triathlon','M','Men','Silver','GBR','BROWNLEE Jonathan')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Triathlon','M','Men','Bronze','RSA','SCHOEMAN Henri')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Triathlon','W','Women','Gold','USA','JORGENSEN Gwen')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Triathlon','W','Women','Silver','SUI','SPIRIG HUG Nicola')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Triathlon','W','Women','Bronze','GBR','HOLLAND Vicky')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Volleyball','M','Men','Gold','BRA','Brazil')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Volleyball','M','Men','Silver','ITA','Italy')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Volleyball','M','Men','Bronze','USA','United States')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Volleyball','W','Women','Gold','CHN','China')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Volleyball','W','Women','Silver','SRB','Serbia')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Volleyball','W','Women','Bronze','USA','United States')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Water Polo','M','Men','Gold','SRB','Serbia')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Water Polo','M','Men','Silver','CRO','Croatia')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Water Polo','M','Men','Bronze','ITA','Italy')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Water Polo','W','Women','Gold','USA','United States')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Water Polo','W','Women','Silver','ITA','Italy')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Water Polo','W','Women','Bronze','RUS','Russian Federation')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Weightlifting','M','Men''s +105kg','Gold','GEO','TALAKHADZE Lasha')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Weightlifting','M','Men''s +105kg','Bronze','GEO','TURMANIDZE Irakli')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Weightlifting','M','Men''s +105kg','Silver','ARM','MINASYAN Gor')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Weightlifting','M','Men''s 105kg','Gold','UZB','NURUDINOV Ruslan')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Weightlifting','M','Men''s 105kg','Bronze','KAZ','ZAICHIKOV Alexandr')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Weightlifting','M','Men''s 105kg','Silver','ARM','MARTIROSYAN Simon')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Wrestling','M','Men''s Freestyle 125 kg','Gold','TUR','AKGUL Taha')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Wrestling','M','Men''s Freestyle 125 kg','Bronze','BLR','SAIDAU Ibrahim')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Wrestling','M','Men''s Freestyle 125 kg','Bronze','GEO','PETRIASHVILI Geno')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Wrestling','M','Men''s Freestyle 125 kg','Silver','IRI','GHASEMI Komeil Nemat')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Wrestling','M','Men''s Freestyle 57 kg','Gold','GEO','KHINCHEGASHVILI Vladimer')
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Wrestling','M','Men''s Freestyle 57 kg','Bronze','AZE','ALIYEV Haji')
select * from olympic_medal_winners
pivot ( count(*) for medal in (
'Gold' gold, 'Silver' silver, 'Bronze' bronze
))
order by noc
fetch first 6 rows only
OLYMPIC_YEAR | SPORT | GENDER | EVENT | NOC | ATHLETE | GOLD | SILVER | BRONZE | 2016 | Hockey | M | Men | ARG | Argentina | 1 | 0 | 0 | 2016 | Weightlifting | M | Men's +105kg | ARM | MINASYAN Gor | 0 | 1 | 0 | 2016 | Weightlifting | M | Men's 105kg | ARM | MARTIROSYAN Simon | 0 | 1 | 0 | 2016 | Archery | M | Men's Team | AUS | Australia | 0 | 0 | 1 | 2016 | Modern Pentathlon | W | Women's Individual | AUS | ESPOSITO Chloe | 1 | 0 | 0 | 2016 | Sailing | M | 470 Men | AUS | Belcher | 0 | 1 | 0 |
---|
select * from (
select noc, medal from olympic_medal_winners
)
pivot ( count(*) for medal in (
'Gold' gold, 'Silver' silver, 'Bronze' bronze
))
order by 2 desc, 3 desc, 4 desc
fetch first 5 rows only
NOC | GOLD | SILVER | BRONZE | CHN | 8 | 3 | 3 | GBR | 7 | 7 | 5 | RUS | 5 | 1 | 3 | GER | 4 | 1 | 6 | USA | 3 | 5 | 10 |
---|
select * from (
select noc, medal, sport, event, gender
from olympic_medal_winners
)
pivot ( count(distinct sport ||'#'|| event ||'#'||gender ) for medal in (
'Gold' gold, 'Silver' silver, 'Bronze' bronze
))
order by 2 desc, 3 desc, 4 desc
fetch first 5 rows only
NOC | GOLD | SILVER | BRONZE | GBR | 7 | 6 | 4 | CHN | 7 | 3 | 3 | RUS | 5 | 1 | 3 | USA | 3 | 5 | 9 | GER | 3 | 1 | 6 |
---|
select * from (
select noc, medal, sport, event, gender, athlete
from olympic_medal_winners
)
pivot (
count( distinct sport ||'#'|| event ||'#'|| gender ) medals,
count( distinct sport ) sports,
listagg( athlete, ',') within group (order by athlete) athletes
for medal in ( 'Gold' gold )
)
where gold_medals > 1
order by gold_medals, gold_sports, noc
fetch first 5 rows only
NOC | GOLD_MEDALS | GOLD_SPORTS | GOLD_ATHLETES | KOR | 2 | 1 | KU Bonchan,Republic of Korea | BRA | 2 | 2 | Brazil,Cerutti,Oscar Schmidt | FRA | 2 | 2 | Azou,Houin,RINER Teddy | GEO | 2 | 2 | KHINCHEGASHVILI Vladimer,TALAKHADZE Lasha | ITA | 2 | 2 | GAROZZO Daniele,VIVIANI Elia |
---|
select * from (
select noc, medal, sport, event, gender, athlete
from olympic_medal_winners
)
pivot (
count( distinct sport ||'#'|| event ||'#'|| gender ) medals,
count( distinct sport ) sports,
listagg( athlete, ',') within group (order by athlete) athletes
for medal in ( 'Gold' gold )
)
where noc like 'D%'
order by gold_medals
NOC | GOLD_MEDALS | GOLD_SPORTS | GOLD_ATHLETES | DEN | 1 | 1 | Denmark |
---|
select * from (
select noc, sport
from olympic_medal_winners
)
pivot (min('X') for sport in (
'Archery' as arc, 'Athletics' as ath, 'Hockey' as hoc,
'Judo' as jud, 'Sailing' as sai, 'Wrestling' as wre
)
)
order by noc
fetch first 7 rows only
NOC | ARC | ATH | HOC | JUD | SAI | WRE | ARG | - | - | X | - | - | - | ARM | - | - | - | - | - | - | AUS | X | - | - | - | X | - | AZE | - | - | - | - | - | X | BEL | - | - | X | - | - | - | BLR | - | - | - | - | - | X | BRA | - | - | - | X | - | - |
---|
select noc,
count ( case when medal = 'Gold' then 1 end ) gold_medals,
count ( case when medal = 'Silver' then 1 end ) silver_medals,
count ( case when medal = 'Bronze' then 1 end ) bronze_medals
from olympic_medal_winners
group by noc
order by 2 desc, 3 desc, 4 desc
fetch first 5 rows only
NOC | GOLD_MEDALS | SILVER_MEDALS | BRONZE_MEDALS | CHN | 8 | 3 | 3 | GBR | 7 | 7 | 5 | RUS | 5 | 1 | 3 | GER | 4 | 1 | 6 | USA | 3 | 5 | 10 |
---|
declare
sql_stmt clob;
pivot_clause clob;
begin
select listagg('''' || sport || ''' as "' || sport || '"', ',') within group (order by sport)
into pivot_clause
from (select distinct sport from olympic_medal_winners);
sql_stmt := 'select * from (select noc, sport from olympic_medal_winners)
pivot (count(*) for sport in (' || pivot_clause || '))';
dbms_output.put_line( sql_stmt );
execute immediate sql_stmt;
end;
select * from (
select noc, sport
from olympic_medal_winners
)
pivot xml (count(*) medal_winners for sport in (
select sport
from olympic_medal_winners
where sport like 'A%')
)
where rownum = 1
NOC | SPORT_XML | ARG | <PivotSet><item><column name = "SPORT">Archery</column><column name = "MEDAL_WINNERS">1</column></item><item><column name = "SPORT">Artistic Gymnastics</column><column name = "MEDAL_WINNERS">1</column></item><item><column name = "SPORT">Athletics</column><column name = "MEDAL_WINNERS">1</column></item></PivotSet> |
---|
select * from (
select noc, sport, athlete
from olympic_medal_winners
)
pivot xml (count(athlete) medal_winners for sport in (
select sport
from olympic_medal_winners
where sport like 'A%')
)
where rownum = 1
NOC | SPORT_XML | ARG | <PivotSet><item><column name = "SPORT">Archery</column><column name = "MEDAL_WINNERS">0</column></item><item><column name = "SPORT">Artistic Gymnastics</column><column name = "MEDAL_WINNERS">0</column></item><item><column name = "SPORT">Athletics</column><column name = "MEDAL_WINNERS">0</column></item></PivotSet> |
---|
create table olympic_medal_tables as
select * from (
select noc, medal, sport, event, gender
from olympic_medal_winners
)
pivot ( count(distinct sport ||'#'|| event ||'#'||gender ) for medal in (
'Gold' gold_medals, 'Silver' silver_medals, 'Bronze' bronze_medals
))
order by 2 desc, 3 desc, 4 desc
select * from olympic_medal_tables
unpivot (medal_count for medal_colour in (
gold_medals as 'GOLD',
silver_medals as 'SILVER',
bronze_medals as 'BRONZE'
))
order by noc
fetch first 6 rows only
NOC | MEDAL_COLOUR | MEDAL_COUNT | ARG | GOLD | 1 | ARG | BRONZE | 0 | ARG | SILVER | 0 | ARM | GOLD | 0 | ARM | BRONZE | 0 | ARM | SILVER | 2 |
---|
drop table olympic_medal_tables purge
create table olympic_medal_tables as
select * from (
select noc, medal, sport, event, gender
from olympic_medal_winners
)
pivot ( count(distinct sport ||'#'|| event ||'#'||gender ) medals,
count(distinct sport) sports
for medal in (
'Gold' gold, 'Silver' silver, 'Bronze' bronze
))
order by 2 desc, 4 desc, 6 desc
select * from olympic_medal_tables
unpivot ((medal_count, sport_count) for medal_colour in (
(gold_medals, gold_sports) as 'GOLD',
(silver_medals, silver_sports) as 'SILVER',
(bronze_medals, bronze_sports) as 'BRONZE'
))
fetch first 9 rows only
NOC | MEDAL_COLOUR | MEDAL_COUNT | SPORT_COUNT | GBR | GOLD | 7 | 7 | GBR | SILVER | 6 | 6 | GBR | BRONZE | 4 | 4 | CHN | GOLD | 7 | 5 | CHN | SILVER | 3 | 3 | CHN | BRONZE | 3 | 2 | RUS | GOLD | 5 | 4 | RUS | SILVER | 1 | 1 | RUS | BRONZE | 3 | 3 |
---|
drop table olympic_medal_tables purge
create table olympic_medal_tables as
select * from (
select noc, medal, sport, event, gender, athlete
from olympic_medal_winners
)
pivot ( count(distinct sport ||'#'|| event ||'#'||gender ) medals,
listagg(athlete, ',') within group (order by athlete) athletes
for medal in (
'Gold' gold, 'Silver' silver, 'Bronze' bronze
))
order by 2 desc, 4 desc, 6 desc
select * from olympic_medal_tables
unpivot ((medal_count, athletes) for medal_colour in (
(gold_medals, gold_athletes) as 'GOLD',
(silver_medals, silver_athletes) as 'SILVER',
(bronze_medals, bronze_athletes) as 'BRONZE'
))
where medal_colour = 'GOLD'
and medal_count = 2
order by noc
fetch first 3 rows only
NOC | MEDAL_COLOUR | MEDAL_COUNT | ATHLETES | BRA | GOLD | 2 | Brazil,Cerutti,Oscar Schmidt | FRA | GOLD | 2 | Azou,Houin,RINER Teddy | GEO | GOLD | 2 | KHINCHEGASHVILI Vladimer,TALAKHADZE Lasha |
---|
with rws as (
select * from olympic_medal_tables
unpivot ((medal_count, athletes) for medal_colour in (
(gold_medals, gold_athletes) as 'GOLD',
(silver_medals, silver_athletes) as 'SILVER',
(bronze_medals, bronze_athletes) as 'BRONZE'
))
where medal_colour = 'GOLD'
and medal_count = 2
)
select noc, athlete
from rws, xmltable (
'if (contains($X,",")) then ora:tokenize($X,"\,") else $X'
passing athletes as X
columns athlete varchar2(4000) path '.'
)
order by 1, 2
fetch first 6 rows only
NOC | ATHLETE | BRA | Brazil | BRA | Cerutti | BRA | Oscar Schmidt | FRA | Azou | FRA | Houin | FRA | RINER Teddy |
---|
create table olympic_country_sport_medals as
select * from (
select noc, sport
from olympic_medal_winners
)
pivot (count(sport) for sport in (
'Athletics' as ath, 'Artistic Gymnastics' as gym, 'Cycling Track' as cyc,
'Boxing' as box, 'Sailing' as sai
)
)
order by 1
select * from olympic_country_sport_medals
pivot (
sum(ath) ath, sum(box) box, sum(gym) gym, sum(sai) sai, sum(cyc) cyc
for noc in ('BRA' BRA, 'CHN' CHN, 'DEN' DEN, 'ESP' ESP, 'ETH' ETH, 'GRE' GRE )
)
unpivot (
(BRA, CHN, DEN, ESP, ETH, GRE ) for sport in (
(BRA_ATH, CHN_ATH, DEN_ATH, ESP_ATH, ETH_ATH, GRE_ATH) as 'Athletics',
(BRA_GYM, CHN_GYM, DEN_GYM, ESP_GYM, ETH_GYM, GRE_GYM) as 'Artistic Gym',
(BRA_BOX, CHN_BOX, DEN_BOX, ESP_BOX, ETH_BOX, GRE_BOX) as 'Boxing',
(BRA_SAI, CHN_SAI, DEN_SAI, ESP_SAI, ETH_SAI, GRE_SAI) as 'Sailing',
(BRA_CYC, CHN_CYC, DEN_CYC, ESP_CYC, ETH_CYC, GRE_CYC) as 'Track Cycling'
)
)
SPORT | BRA | CHN | DEN | ESP | ETH | GRE | Athletics | 0 | 0 | 0 | 0 | 1 | 0 | Artistic Gym | 2 | 0 | 0 | 0 | 0 | 0 | Boxing | 0 | 1 | 0 | 0 | 0 | 0 | Sailing | 0 | 0 | 0 | 0 | 0 | 2 | Track Cycling | 0 | 0 | 1 | 0 | 0 | 0 |
---|
select * from olympic_country_sport_medals
unpivot (
(medals) for sport in ( ath, box, gym, sai, cyc )
)
pivot (
sum(medals) for noc in (
'BRA' BRA, 'CHN' CHN, 'DEN' DEN, 'ESP' ESP, 'ETH' ETH, 'GRE' GRE
)
)
SPORT | BRA | CHN | DEN | ESP | ETH | GRE | CYC | 0 | 0 | 1 | 0 | 0 | 0 | BOX | 0 | 1 | 0 | 0 | 0 | 0 | GYM | 2 | 0 | 0 | 0 | 0 | 0 | SAI | 0 | 0 | 0 | 0 | 0 | 2 | ATH | 0 | 0 | 0 | 0 | 1 | 0 |
---|