Answers 6-10

Here's what I've got so far, working on the rest of Q's and A's

1) In my own words, stopwords are just noisy nothing words that don't need to be searched, and can be looked over.  If I had a sentence like this (which I hear from way to many people), "Um, what time are the, um, Bears, um playing Sunday?" and I had to search that sentence on a computer, I could save myself a lot of room by telling my code, every time you see "um" skip it!.. At least that's my understanding of it...

2)  LIKE and CONTAINS are similar but against large text scans CONTAINS works better (faster).  LIKE can't perform scans on formatted binary data, LIKE performs a pattern scan of each table, CONTAINS "appl(ies) specific transformations at index and query time", it's smarter and goes faster for big stuff.

3)  Stemming is a fancy way of saying a word has a different tense... Past tense, future tense, etc (are there any others?)  So a word like "drink" would be the Stemmer for, drank, drunk, drinking, drinks.  This goes hand in hand with the last question about CONTAINTS() because CONTAINS can find the inflection of drink (meaning CONTAINS can find drank, drunk, drinking, drinks when you just tell it to find "drink").  Interestingly enough CONTAINS can also find a synonym for another word, meaning if I type in the word car, it could find the word "auto" or "automobile" if those words are in the Thesaurus that it uses (and you can edit the Thesaurus if you want).

Again with the XML....

4)  Ok, so the main differences in the different types are that they each get laid out differently... recognizing how the nodes look, and their attributes is important, and something that I should be able to visualize... in case I can't here is some data that can help me:

IF OBJECT_ID('XMLForHomework') IS NOT NULL DROP TABLE XMLForHomework
CREATE TABLE XMLForHomework
(
ID INT PRIMARY KEY IDENTITY (1,1),
FirstName varchar(20),
LastName varchar(20),
FavoriteTeam varchar(10)
)
-- Add some stuff
INSERT INTO XMLForHomework VALUES
('Ben', 'Jamin', 'Tribe'),
('Chris', 'Smith', 'Sox'),
('Beth', 'Smith', 'Cubs'),
('Your', 'Mom', 'Yanks'),
('Your', 'Dad', 'Mets')
--
SELECT * FROM XMLForHomework FOR XML AUTO
SELECT * FROM XMLForHomework FOR XML RAW
SELECT * FROM XMLForHomework FOR XML RAW, ELEMENTS
SELECT * FROM XMLForHomework FOR XML PATH('Path')
--
DROP TABLE XMLForHomework

After looking at the different outcomes of the XML I should be able to tell the differences between them.  My understanding is that you may need different forms of XML in different situations, and for different applications.

5)  So, check this out, I kept on getting frustrated with this question because I wrote the first batch of code, and kept on getting an error, then fixed my "bugs" with the second line of code.. can you find the 3 differences between them?  The correct answer is the bottom query.

USE AdventureWorks2012
WITH XMLNAMESPACE(
'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume' AS ns)
SELECT [Resume].value('(/ns:Resume/ns:EMail)', 'nvarchar(50)') AS emal
FROM HumanResources.JobCandidate;
--
WITH XMLNAMESPACES(
'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume' AS ns)
SELECT [Resume].value('(/ns:Resume/ns:EMail)[1]','varchar(50)') AS emal
FROM HumanResources.JobCandidate;

6)  WITH XMLNAMESPACES(
'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume' AS ns)
SELECT [Resume].value('(/ns:Resume/ns:Name/ns:Name.First)[1]', 'varchar(50)') AS firstname,
[Resume].value('(/ns:Resume/ns:Name/ns:Name.Last)[1]', 'varchar(50)') AS lastname
FROM HumanResources.JobCandidate
WHERE
[Resume].exist('/ns:Resume/ns:Education/ns:Edu.Location/ns:Location/ns:Loc.State[.="IA "]')= 1

7)
a. So a note on this one, I got really confused when I started working with attribute centric XML because most of the work I had done up to this point had been in element centric.  I actually had to post this question on SSC to obtain a hint on the answer.

SELECT
@x.value('(root/production.Categories[@categoryname="Beer"]/@categoryid)[1]','INT') AS catid,
@x.value('(root/production.Categories[@categoryname="Beer"]/@categoryname)[1]', 'NVARCHAR(50)') AS catname

b. This is another tough one because it's important how you define "everything" and I wasn't very specific... So I meant it as how do you return the entire XML document? Which would be this:

SELECT @x.query('*') AS everything

c. The key to problem c is to shred everything and make a big table first with all of the data in it (a cte) then query that table, here's what that would look like:

WITH CTE AS(

SELECT 

categoryid = Foo.value('(@categoryid)','NVARCHAR(250)'),

descrip = Foo.value('(@description)','NVARCHAR(250)'),

categoryname = Foo.value('(@categoryname)','NVARCHAR(250)')

FROM @x.nodes('/root/production.Categories') AS Tbl(Foo)

)

SELECT * FROM CTE

WHERE categoryid BETWEEN 9 AND 10

8) There are two types of indexes for XML a primary index, then after you have a primary index you can have a secondary index that has three different flavors: Path, Value, and Property.  The primary index "contains a shredded persisted representation of the XML values"... basically for each node a row is created, then you can search the rows.  Path in the secondary index speeds up queries that use exist() or value().  Value helps if you use queries that are value based or have wildcards.  Property is useful for queries that "retrieve one or more values from individual XML instances by using the value() method."

9) The differences between element centric and attribute centric XML in my head are first off the way that they look... element centric uses a lot of nodes, which are things that look like this <root> value</root>, and attribute centric looks like this root = "value"... or something to that affect.  Either are fine, however with element centric XML you may want to describe a namespace.

10-12) /*USE TestDB
IF OBJECT_ID('QuestionTen') IS NOT NULL DROP TABLE QuestionTen
--
CREATE TABLE QuestionTen(
PlayerID INT NOT NULL,
FirstName VARCHAR(25) NULL)
--
ALTER TABLE QuestionTen
ADD Lastname VARCHAR(25) NOT NULL
--
ALTER TABLE QuestionTen
ADD CONSTRAINT PK_PlayerID PRIMARY KEY(PlayerID)*/

13) Well, with a primary key you can only have one per table, the primary key can not have a NULL value, and it must be unique.  A unique key is just like it sounds, it is unique but it can be NULL ( I think there can only be one null unique key per table though, because then it would still be unique).

14) Sticking with table "ChapterTen" here is what adding a default constraint would look like:

ALTER TABLE QuestionTen
ADD AddedDate date DEFAULT GETDATE()

15) Again, sticking with table "ChapterTen" here is what adding a check constraint would look like:

ALTER TABLE QuestionTen
ADD CONSTRAINT Chk_FirstName CHECK (Firstname <> 'Bob')
--This Insert will error out because FirstName = Bob
INSERT INTO dbo.QuestionTen (PlayerID, FirstName, LastName)
VALUES (1, 'Bob', 'Smith')