| YEAR | GOLD | SILVER | BRONZE |
| 2000 | Alex | Dhruv | Shyam |
| 2001 | Dev | Duggu | Chandan |
| 2002 | Saksham Arora | Dev | Lovekush |
| 2003 | Ayush | Ram | santosh |
| 2004 | Gaurav | marry | Chandan |
| 2005 | Dev | Gaurav | Chandan |
| 2006 | Saksham Arora | Pyush | Lovekush |
| 2007 | Nazim | Chandan | Vijay |
| 2008 | Nazim | Pyush | santosh |
| 2009 | Saksham Arora | Pyush | Lovekush |
| 2010 | Anuskha | Dhruv | Sanjeev |
| 2011 | Nazim | Steven | Vijay |
Here we are having a table players having the years and players name.
So, we need to write a query to find number of players who won only the gold medals
(Hint: The players who won others medals too with gold medals should be excluded)
You can create the table at your end using the statements below:
create table players(YEAR int, GOLD varchar(50), SILVER varchar(50), BRONZE varchar(50));
insert into players values(2000, 'Alex', 'Dhruv', 'Shyam');
insert into players values(2001, 'Dev', 'Duggu', 'Chandan');
insert into players values(2002, 'Saksham Arora', 'Dev', 'Lovekush');
insert into players values(2003, 'Ayush', 'Ram', 'santosh');
insert into players values(2004, 'Gaurav', 'marry', 'Chandan');
insert into players values(2005, 'Dev', 'Gaurav', 'Chandan');
insert into players values(2006, 'Saksham Arora', 'Pyush', 'Lovekush');
insert into players values(2007, 'Nazim', 'Chandan', 'Vijay');
insert into players values(2008, 'Nazim', 'Pyush', 'santosh');
insert into players values(2009, 'Saksham Arora', 'Pyush', 'Lovekush');
insert into players values(2010, 'Anuskha', 'Dhruv', 'Sanjeev');
insert into players values(2011, 'Nazim', 'Steven', 'Vijay');Solution:
Method 1 (Using subquery):
SELECT Gold AS PlayerName, count(*) AS count FROM players
WHERE GOLD NOT IN (SELECT SILVER FROM players UNION SELECT BRONZE FROM players)
GROUP BY GOLD;
OUTPUT:
| PlayerName | count |
| Alex | 1 |
| Anuskha | 1 |
| Ayush | 1 |
| Nazim | 5 |
| Saksham Arora | 3 |
Method 2 (Using CTE):
WITH CTE AS
(
SELECT Gold As Player_name, 'Gold' AS medal_type FROM players
UNION all
SELECT Silver AS Player_name, 'Silver' AS medal_type FROM players
UNION all
SELECT Bronze as Player_name, 'Bronze' AS medal_type FROM players
)
SELECT player_name,count(1) AS no_of_medals, medal_type
FROM CTE
GROUP BY player_name
HAVING count(DISTINCT medal_type) =1 AND medal_type = 'Gold';OUTPUT:
| player_name | no_of_medals | medal_type |
| Alex | 1 | Gold |
| Anuskha | 1 | Gold |
| Ayush | 1 | Gold |
| Nazim | 3 | Gold |
| Saksham Arora | 3 | Gold |