Chapter 8 "Suggested Practice" Answers

Here are some of the answers to the books "suggested practices":

Question 1

ALTER TABLE production.categoriestest

ADD Categorystatus NVARCHAR(15) NOT NULL DEFAULT ''

--

Question 2

SELECT *

FROM production.products

--

INSERT INTO production.products (productid, productname, supplierid, categoryid, unitprice, discontinued)

VALUES (78, N'product HAMMERS', 1, 1, -10, 0)

--

ALTER TABLE production.products

DROP CONSTRAINT [CHK_Products_unitprice]

--

ALTER TABLE production.products WITH CHECK

ADD CONSTRAINT CHK_Products_unitprice CHECK(unitprice > 0)

--

ALTER TABLE production.products

SET IDENTITY_INSERT ON

--

SET IDENTITY_INSERT [Production].[Products] ON

INSERT INTO production.products (productid, productname, supplierid, categoryid, unitprice, discontinued)

VALUES (78, N'product HAMMERS', 1, 1, -10, 0)

--

ALTER TABLE production.products

DROP CONSTRAINT [CHK_Products_unitprice]

--Altering the column

UPDATE Production.Products

SET unitprice='168'

WHERE unitprice='-10';

So just a quick over view of what's going on here.  The first question asks you to create a column or add a row, but it errors out because the field is not allowed to be NULL, so you have to change your "alter table" code to make NULL values default to being blank which is written as '' (two single quotation marks next to each other).

The second question asks you add a row that contains a negative number for unitprice but because unitprice has a constraint in which it can't be a negative number this also fails.  You have to drop the [CHK_Products_unitprice] constraint then add the row.  Now you have a negative number in the column, try to add the constraint again, and it fails, basically telling you that you can't do that because there is a negative value in the column.  So you have to go back and alter the column which was the last bit of code I wrote, then reapply the constraint.  Fairly simple stuff of how to work with data, and making sure that your data has integrity.

Onto Chapter 9 tonight, I have no idea what chapter nine is about, but it will be exciting to learn something new!

Cheers!