This page has 0 threads | Add post

Exercises

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

Software ==>	SQL (203 exercises)
Topic ==>	Table-valued functions (8 exercises)
Level ==>	Relatively easy
Subject ==>	SQL training

Create a table valued function in SQL Server to return statistics about concerts in a given year.

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 file called Show Me the Bunny.sql from the folder shown above. You'll find a query which returns statistics about concerts grouped by artists and tours.

Query results in SQL Server Management Studio.

Some sample results from the query.

Your job is to use the query to create a table-valued function which you can call in another query as shown below:

Calling a table-valued function in a SQL query.

You can pass a search string into the function to return artists with a matching name.

In the Show Me the Bunny.sql script, use the query to create a table-valued function called fn_Shows_by_Artist. Provide the function with a single parameter with a data type of NVARCHAR(255) and ensure that the function returns a table.

CREATE FUNCTION Function_Name(@Parameter_Name DATATYPE)

RETURNS TABLE

RETURN

Refer to the parameter in the WHERE clause of the query to filter the results for artists with matching names.

In a new script, write a query which calls the function and try passing different strings to it.