This page has 0 threads | Add post

Exercises

Finding our exercises useful? Please consider helping us to share more!

Software ==>	SQL (203 exercises)
Topic ==>	Scalar functions (9 exercises)
Level ==>	Relatively easy
Subject ==>	SQL training

Create a user-defined function in SQL Server to calculate album sales certifications.

This exercise is provided to allow potential course delegates to choose the correct Wise Owl Microsoft training course, and may not be reproduced in whole or in part in any format without the prior written consent of Wise Owl.

Before you can do this exercise, you'll need to download and unzip this file .

If you don't have a copy of the Music_01 database, follow the instructions at the following link to create it: www.wiseowl.co.uk/sql/exercises/standard/using-sql-server/5257/

Open the Album Certifications.sql file in the folder shown above. You'll find two queries showing information about albums and their sales certifications.

Your job is to create a function which you can use to replace each of the CASE expressions.

Calling a user defined scalar valued function in SQL Server.

Instead of a long CASE expression, we'd like to call a custom function.

In a new script, create a function called fn_Album_Certification which has a single FLOAT parameter called @Sales and returns NVARCHAR(14). Here's the basic outline of the code to help you:

CREATE FUNCTION Function_Name(@ParameterName DATATYPE)

RETURNS DATATYPE

BEGIN

END

Add a RETURN statement to the function so that it returns the correct album certification for the value passed in to the @Sales parameter. Feel free to copy and paste the CASE expression from the query to save time!

After creating the function, replace each case expression in the queries with a reference to the new function. Test that the queries still work.

Save and close the scripts.

You can unzip this file to see the answers to this exercise, although please remember this is for your personal use only.

You can find other training resources for the subject of this exercise here:

This page has 0 threads

Add a new post