[SQL Challenge] Unique movie names

Question

I have a table 'movies' with the following table data.

id name release_year
------------------------------------------
1 Titanic 1997
2 The Avengers 1998
3 The Avengers 2012
4 Twilight 1998
5 Twilight 2008

'id' is the primary key, names can be same. You can also assume (name, year) is unique.

I want to see the following output

name
--------
The Avengers (1998)
The Avengers (2012)
Twilight (1998)
Twilight (2008)
Titanic

If the title is unique, it should be displayed as it is, if there are multiple movies with same name but with different release years, they should have them in parenthesis.

Answer 1

Something like that works in MySQL:

SELECT name FROM movies GROUP BY name having COUNT(name) = 1
UNION
SELECT CONCAT(movies.name, ' (', movies.release_year, ')') FROM movies INNER JOIN (SELECT name FROM movies GROUP BY name HAVING COUNT(id) > 1) dup ON movies.name = dup.name

Answer 2

I also had a similar answer.

select concat(name,' (',release_year,')') as name from movies where name in (select name from movies
group by name
having count(id) > 1)
union
select name from movies where name in (select name from movies
group by name
having count(id) = 1);