Exercise: Views, Procedures and Functions

Ten problems that create views, stored procedures and functions in Northwind and then use them, each with the result to expect.

Journeyman · T-SQL · SQL · Updated

Time to build things that live in the database: views that save a join, procedures that take parameters, and functions you can use inside queries.

Get the exercise code

Open this lesson's GitHub repository, github.com/ask-codey/exercise-views-procedures-and-functions, also linked from the Exercise box beside the lesson. Click Code, then Download ZIP, and unzip it into your bootcamp folder. Or clone it with Git.

There's one .sql file per problem, numbered in order. Each holds the problem as a comment, and an Expect line with the right answer.

What to do

Open each file in SSMS with Northwind chosen. Write the CREATE, then GO on a line of its own, then the query that uses what you made. Run the whole file with F5, and compare with the Expect line.

Problems Practice
1–4 Creating views, and querying them (problem 4 uses the course's CustomerOrders view: open Northwind_Views.sql, choose Northwind, and run it first)
5–7 Stored procedures with parameters, a default, and one that adds a row
8–10 A scalar function, a table-valued function, and a function used inside an aggregate

Hints

  • Write CREATE OR ALTER, so you can run a file again after fixing it.
  • End every CREATE with GO. A view, procedure or function must be alone in its batch.
  • Procedures run with EXEC; functions go inside a SELECT. A scalar function needs dbo. in front of its name.
  • Problem 7 changes data. To undo it, run Northwind.sql again; it rebuilds Northwind as it was.

When you're done

Every result matches its Expect line, and Object Explorer shows your objects under Views and Programmability. Try calling GetOrdersForCustomer from a C# program, as in Calling a Stored Procedure from C#.

Submit your exercise

The exercise is how you finish this lesson. When your work is done, put it on GitHub in a repo of your own, then submit that repo's address here.

  1. On GitHub, click +, then New repository. Name it (for example exercise-views-procedures-and-functions), choose Public, and click Create repository. Leave out the README: your folder already has one.

  2. In a terminal in your exercise folder (Git Bash on Windows), send your work to your new repo. If you downloaded the ZIP:

    git init -b main
git add .
git commit -m "My solution"
git remote add origin https://github.com/your-name/exercise-views-procedures-and-functions.git
git push -u origin main

    If you cloned the starter instead, point your copy at your own repo, then commit and push:

    git remote set-url origin https://github.com/your-name/exercise-views-procedures-and-functions.git
git add .
git commit -m "My solution"
git push -u origin main

  3. Paste your repo's address, such as https://github.com/your-name/exercise-views-procedures-and-functions, into Submit your exercise on this page. The site checks that the repo is public and holds your commits, and then the lesson is complete.

Changed something after you submitted? Push it, and submit again: your latest submission is the one that counts.

So what

Views, procedures and functions let the database carry part of the work: named, reusable and shared by every app that connects. You'll recognize them in every real database you meet.

Key terms

Related