These functions are commonly used for the same thing which is execute a piece of SQL code or stored procedure, most commonly used for dynamic SQL.
So lets see a usage example for both:
EXEC ( )
DECLARE @SQL varchar(max) DECLARE @Tablename varchar(20) SET @Tablename = 'Person.Address' SET @SQL = 'SELECT * FROM '+ @Tablename EXEC (@SQL)
DECLARE @SQL nvarchar(max) DECLARE @Tablename nvarchar(20) SET @Tablename = 'Person.Address' SET @SQL = 'SELECT * FROM '+ @Tablename exec sp_executesql @SQL
In the above examples there is just one slight difference, @SQL variable is varchar for EXEC and nvarchar for sp_executesql.
But what are the main differences between them?