The tables we have

SHOW TABLES
Tables_in_test
0 nobel 
-- format
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

The laureate who won more than one nobel prize

SELECT full_name, 
       COUNT(year) AS prize_count 
FROM   nobel 
GROUP  BY full_name 
HAVING prize_count > 1 
ORDER  BY prize_count DESC
full_name prize_count
0 Jack W. Szostak 3
1 Comité international de la Croix Rouge (Intern... 3
2 Adolf Friedrich Johann Butenandt 2
3 Fritz Albert Lipmann 2
4 Office of the United Nations High Commissioner... 2
5 Kurt Wüthrich 2
6 Barry J. Marshall 2
7 Thomas C. Südhof 2
8 Abdus Salam 2
9 Finn E. Kydland 2
10 Thomas A. Steitz 2
11 Dale T. Mortensen 2
12 Tomas Lindahl 2
13 Marie Curie, née Sklodowska 2
14 Carl Bosch 2
15 Walther Bothe 2
16 Rudolf Ludwig Mössbauer 2
17 Ilya Prigogine 2
18 Georges Charpak 2
19 Edward C. Prescott 2
20 Albert Fert 2
21 Charles Kuen Kao 2
22 Saul Perlmutter 2
23 Paul Modrich 2
24 Friedrich Bergius 2
25 Richard Kuhn 2
26 Linus Carl Pauling 2
27 Elinor Ostrom 2
28 Shinya Yamanaka 2
29 Antonio Caetano de Abreu Freire Egas Moniz 2
... ... ...
33 Roderick MacKinnon 2
34 Osamu Shimomura 2
35 Bruce A. Beutler 2
36 Adam G. Riess 2
37 Paul Ehrlich 2
38 Petrus (Peter) Josephus Wilhelmus Debye 2
39 Hermann Staudinger 2
40 John Franklin Enders 2
41 Claude Cohen-Tannoudji 2
42 John L. Hall 2
43 Mario R. Capecchi 2
44 Robert J. Lefkowitz 2
45 Serge Haroche 2
46 Stefan W. Hell 2
47 William Parry Murphy 2
48 Hideki Yukawa 2
49 Igor Yevgenyevich Tamm 2
50 Günter Blobel 2
51 Theodor W. Hänsch 2
52 Roger Y. Tsien 2
53 David J. Wineland 2
54 Isamu Akasaki 2
55 John Bardeen 2
56 Frederick Sanger 2
57 Il´ja Mikhailovich Frank 2
58 Christian de Duve 2
59 Toshihide Maskawa 2
60 Alvin E. Roth 2
61 Martin Karplus 2
62 Randy W. Schekman 2

63 rows × 2 columns

Comité international de la Croix Rouge (International Committee of the Red Cross) has won three nobel.

But wikipedia page of Jack W. Szostak says he won nobel once in 2009 and not three times. So do we have duplicates in our table.

Duplicate rows in table

-- for each name from inner query print info 
SELECT year, 
       category, 
       laureate_id, 
       full_name, 
       birth_date 
FROM   nobel 
WHERE  full_name IN ( 
                    -- gives list of names for which duplicates are present 
                    SELECT full_name 
                     FROM   nobel 
                     GROUP  BY year, 
                               full_name 
                     HAVING Count(*) > 1)
year category laureate_id full_name birth_date
0 1908 Medicine 302 Paul Ehrlich 1854-03-14
1 1908 Medicine 302 Paul Ehrlich 1854-03-14
2 1931 Chemistry 189 Carl Bosch 1874-08-27
3 1931 Chemistry 189 Carl Bosch 1874-08-27
4 1931 Chemistry 190 Friedrich Bergius 1884-10-11
5 1931 Chemistry 190 Friedrich Bergius 1884-10-11
6 1934 Medicine 328 William Parry Murphy 1892-02-06
7 1934 Medicine 328 William Parry Murphy 1892-02-06
8 1936 Chemistry 195 Petrus (Peter) Josephus Wilhelmus Debye 1884-03-24
9 1936 Chemistry 195 Petrus (Peter) Josephus Wilhelmus Debye 1884-03-24
10 1938 Chemistry 198 Richard Kuhn 1900-12-03
11 1938 Chemistry 198 Richard Kuhn 1900-12-03
12 1939 Chemistry 199 Adolf Friedrich Johann Butenandt 1903-03-24
13 1939 Chemistry 199 Adolf Friedrich Johann Butenandt 1903-03-24
14 1949 Medicine 348 Antonio Caetano de Abreu Freire Egas Moniz 1874-11-29
15 1949 Medicine 348 Antonio Caetano de Abreu Freire Egas Moniz 1874-11-29
16 1949 Physics 54 Hideki Yukawa 1907-01-23
17 1949 Physics 54 Hideki Yukawa 1907-01-23
18 1953 Chemistry 216 Hermann Staudinger 1881-03-23
19 1953 Chemistry 216 Hermann Staudinger 1881-03-23
20 1953 Medicine 355 Fritz Albert Lipmann 1899-06-12
21 1953 Medicine 355 Fritz Albert Lipmann 1899-06-12
22 1954 Medicine 356 John Franklin Enders 1897-02-10
23 1954 Medicine 356 John Franklin Enders 1897-02-10
24 1954 Physics 62 Walther Bothe 1891-01-08
25 1954 Physics 62 Walther Bothe 1891-01-08
26 1956 Medicine 361 Werner Forssmann 1904-08-29
27 1956 Medicine 361 Werner Forssmann 1904-08-29
28 1958 Physics 71 Igor Yevgenyevich Tamm 1895-07-08
29 1958 Physics 71 Igor Yevgenyevich Tamm 1895-07-08
... ... ... ... ... ...
85 2011 Medicine 861 Bruce A. Beutler 1957-12-29
86 2011 Medicine 861 Bruce A. Beutler 1957-12-29
87 2011 Physics 864 Saul Perlmutter None
88 2011 Physics 864 Saul Perlmutter None
89 2011 Physics 866 Adam G. Riess 1969-12-16
90 2011 Physics 866 Adam G. Riess 1969-12-16
91 2012 Chemistry 878 Robert J. Lefkowitz 1943-04-15
92 2012 Chemistry 878 Robert J. Lefkowitz 1943-04-15
93 2012 Economics 882 Alvin E. Roth 1951-12-18
94 2012 Economics 882 Alvin E. Roth 1951-12-18
95 2012 Medicine 875 Shinya Yamanaka 1962-09-04
96 2012 Medicine 875 Shinya Yamanaka 1962-09-04
97 2012 Physics 876 Serge Haroche 1944-09-11
98 2012 Physics 876 Serge Haroche 1944-09-11
99 2012 Physics 877 David J. Wineland 1944-02-24
100 2012 Physics 877 David J. Wineland 1944-02-24
101 2013 Chemistry 889 Martin Karplus 1930-03-15
102 2013 Chemistry 889 Martin Karplus 1930-03-15
103 2013 Medicine 885 Randy W. Schekman 1948-12-30
104 2013 Medicine 885 Randy W. Schekman 1948-12-30
105 2013 Medicine 886 Thomas C. Südhof 1955-12-22
106 2013 Medicine 886 Thomas C. Südhof 1955-12-22
107 2014 Chemistry 910 Stefan W. Hell 1962-12-23
108 2014 Chemistry 910 Stefan W. Hell 1962-12-23
109 2014 Physics 906 Isamu Akasaki 1929-01-30
110 2014 Physics 906 Isamu Akasaki 1929-01-30
111 2015 Chemistry 921 Tomas Lindahl 1938-01-28
112 2015 Chemistry 921 Tomas Lindahl 1938-01-28
113 2015 Chemistry 922 Paul Modrich 1946-06-13
114 2015 Chemistry 922 Paul Modrich 1946-06-13

115 rows × 5 columns

Yes we have many duplicate entries.

References