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

  • Whenever you search in PBworks or on the Web, Dokkio Sidebar (from the makers of PBworks) will run the same search in your Drive, Dropbox, OneDrive, Gmail, Slack, and browsed web pages. Now you can find what you're looking for wherever it lives. Try Dokkio Sidebar for free.


Spring 2013 COP4709 Assignment 4 Solution

Page history last edited by Dr. Ron Eaglin 10 years, 1 month 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.