Exercise: Modifying Data

Time to change some real data. In this exercise you'll open a new line of business for Northwind Traders: a Sporting Goods category with its own products. You'll hire yourself, take a customer's first order, raise some prices, and finally close the category down again, in nine SQL problems that build on each other.

Get the exercise files

Open this lesson's GitHub repository, github.com/ask-codey/exercise-modifying-data, also linked from the Exercise box beside the lesson. Click Code, then Download ZIP, and unzip it into your bootcamp folder.

Start fresh: run Northwind.sql from the course's sample databases first, so the examples you tried in the lessons (including their Sporting Goods category) are gone.

Each .sql file holds one problem as a comment. Open it in SSMS (File > Open > File...), choose Northwind in the database drop-down, and write your SQL under the problem. Then select it (with the DECLARE lines at the top, in problems 5, 7 and 9) and press F5.

Check your work

There are no tests for SQL here: you check your own work, the way you would on the job.

• Each problem says what to expect, such as (5 rows affected). Compare it with the Messages tab.
• After each change, run a SELECT and look at the data.
• Before every UPDATE and DELETE, run a SELECT with the same WHERE first.

Hints

• Look things up; don't type ids. SELECT @CategoryId = CategoryId FROM Categories WHERE CategoryName = 'Sporting Goods'; puts a row's value into a variable.
• An INSERT can take its rows from a SELECT. INSERT INTO OrderDetails (...) SELECT ... FROM Products WHERE ... inserts one row for each product the SELECT finds. It's handy in problem 5.
• Dates: GETDATE() is now, and DATEADD(day, -14, GETDATE()) is two weeks ago.
• Read the error in problem 9. It tells you exactly which table still points at the category.

Starting over

If something goes wrong, run Northwind.sql from the course's sample databases again. It creates Northwind fresh, and you can start from problem 1.

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-modifying-data), 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-modifying-data.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-modifying-data.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-modifying-data, 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

Insert, update and delete, with variables, lookups and transactions, is the work behind every Save button. You've just done it by hand; soon your C# code will do it for you.

Key terms

• INSERT
• UPDATE
• DELETE
• Transaction

Related

• Inserting Data
• Updating Data
• Deleting Data
• Transactions