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

  • You already know Dokkio is an AI-powered assistant to organize & manage your digital files & messages. Very soon, Dokkio will support Outlook as well as One Drive. Check it out today!


Spring 2013 COP4709 Assignment 4 Solution

Page history last edited by Dr. Ron Eaglin 11 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.