| 
  • If you are citizen of an European Union member nation, you may not use this service unless you are at least 16 years old.

  • Buried in cloud files? We can help with Spring cleaning!

    Whether you use Dropbox, Drive, G-Suite, OneDrive, Gmail, Slack, Notion, or all of the above, Dokkio will organize your files for you. Try Dokkio (from the makers of PBworks) for free today.

  • Dokkio (from the makers of PBworks) was #2 on Product Hunt! Check out what people are saying by clicking here.

View
 

Spring 2013 COP4709 Assignment 4 Solution

Page history last edited by Dr. Ron Eaglin 9 years, 4 months ago

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.

 

 

Comments (0)

You don't have permission to comment on this page.