This is the instructor solution to Spring 2013 COP4709 Assignment 4

I am using the schema as shown;

1. (4 points) Create the first required function as a table valued function

CREATE FUNCTION [dbo].[func_GetArtistIDsForSong] 

(

  @SongName NVARCHAR(200)

)

RETURNS TABLE

AS

   RETURN (

   SELECT Albums.artistID FROM Albums, Songs

   WHERE Songs.name LIKE @SongName + '%'

   AND Songs.albumID = Albums.id)

GO

-- Sample of using the TABLE VALUE FUNCTION

SELECT * FROM func_GetArtistIDsForSong('C')

The SELECT Statement at the end demonstrates how to call am Inline TABLE Valued Function. Notice it has to be treated as a table since it returns a table.

2. (5 points) Next create the second function as a SCALAR Function

CREATE FUNCTION [dbo].[func_GetArtistNameForArtistID] 

(

  @ArtistID INT

)

RETURNS NVARCHAR(200)

AS

BEGIN

   RETURN ( 

     SELECT name 

     FROM Artists 

     WHERE id = @ArtistID)

END

GO

-- Sample of using the scalar function

SELECT dbo.func_GetArtistNameForArtistID(1) 

The final SELECT statement demonstrates calling a scalar function

3. (1 point) Simply put the 2 functions together. Note that regardless of your schema design - if your 2 functions work according to specifications - this call will always work.

SELECT dbo.func_GetArtistNameForArtistID(artistID) FROM func_GetArtistIDsForSong('F') 

This will return all the artists that have songs that start with the passed string.