• 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.


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)
   SELECT Albums.artistID FROM Albums, Songs
   WHERE Songs.name LIKE @SongName + '%'
   AND Songs.albumID = Albums.id)
-- 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
   RETURN ( 
     SELECT name 
     FROM Artists 
     WHERE id = @ArtistID)
-- 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.