First SQL Saturday

Currently  attending my first sqlsaturday in Madison Wisconsin... Will have a trip report at the end of the day, first session is the picture below... Excited! Wondering if I will grasp things today or if it all will go saling over my head... Cheers!

Learning new stuff while not forgetting old stuff

Well I posted the homework for chapters 6-8ish, and worked on it once or twice.  Then the other day I decided to go back and clean up a few posts in my blog as to not confuse anyone that might read the blog and in doing so I started to review some of the homework that I assigned myself.  When I read the questions and tried to construct a query that would answer the questions in SQL I had a feeling of writers block; like ok, how do I start this thing?  Do I need a CTE? What am I grouping?  What tables am I using?  How do I cross apply, and what does that do again?

I think it's virtually impossible to not relapse on the things you've learned without looking at them on a daily basis, but thankfully when I felt the writers block I was able to go back and review some notes to get back on track as to how I can construct a sufficient query.  None the less, it did take time for me to look up notes which I will not have the luxury of doing in a test environment.

Realizing that the initial things that I learned had gotten fuzzy I've been working through my homework sets from chapters 3, 4, 5.  Reading the questions slowly, and taking my time has helped me to refresh my memory of these queries.

Learning SQL for this exam is tough if you're new to SQL because there are many ways to go about learning it.  It's not as if you first learn the SELECT statement in chapter one, and the ORDER BY in the last chapter (if that makes sense).  You learn principle concepts that are simple at first, then shift, and morph to become increasingly more difficult.

So with that all being said, it can be frustrating trying to learn something new and not forgetting the old.  The best remedy I've found for blending the two is constant application.  I've already told myself that I'm not going to take the exam again until I can complete every homework question in my blog without looking at notes.  I then will know that I'm ready…

Cheers to those on the warpath with me...

Pop Quiz

Well since I've spent a lot of time recently on the middle chapters of the book, I decided to review a couple old topics, hence the Pop Quiz title.  Just two questions:


1)  In Chapter 5 I had a question like this in the homework that I used from stack overflow:

USE TESTDB GO
CREATE TABLE chapter5 ( ID INT, Name VARCHAR(10));
USE TestDB
INSERT INTO chapter5 (ID, Name) VALUES (1, 'Alpha'), (2, 'Beta'), (3, 'Beta'), (4, 'Beta'), (5, 'Charlie'), (6, 'Charlie')

Here's the query itself
USE TestDB
SELECT NAME, COUNT(ID) AS idcoount
FROM chapter5
WHERE ID IS NOT NULL
GROUP BY Name
UNION ALL
SELECT 'SUM', COUNT(ID)
FROM chapter5

--
Explain why this invokes a conversion error:
USE TestDB
SELECT COUNT(ID) AS idcoount, Name
FROM chapter5
WHERE ID IS NOT NULL
GROUP BY Name
UNION ALL
SELECT 'SUM', COUNT(ID)
FROM chapter5

2)  Write a query that finds locations (region, and city) that are common (belong in both) between hr.employees and sales.customers using TSQL2012

That's all for now

Homework Chapters 6-10

I'm going to attempt to create a homework set that covers things I've learned in chapters 6 through 10... keep in mind these are just exercises, it's important to know how to read and write this stuff, but it's also import to understand them deeper *cough* *cough* so that you're not lost in the multiple choice portion of the test.

Chapter 6
1. What are stopwords?
2. What is the difference between LIKE and CONTAINS in the WHERE clause?
3. What is a Stemmer, and what does it do?

Chapter 7 (The Big Dog)
4. What's the difference between FOR XML AUTO, FOR XML PATH, and FOR XML RAW?
5. Use humanresources.jobcandidate in AdventureWords2012 and display email address's of candidates (EMail)
6. Use humanresources.jobcandidate in AdventureWords2012 and display first name and last name of people who's education was in the state of Iowa (IA)... (ns:Resume/ns:Education/ns:Edu.Location/ns:Location/ns:Loc.State)
7. Use this code:
 DECLARE @X XML
 SET @X = '<root>
<production.Categories categoryid="1" categoryname="Beverages" description="Soft drinks, coffees, teas, beers, and ales" />
<production.Categories categoryid="2" categoryname="Condiments" description="Sweet and savory sauces, relishes, spreads, and seasonings" />
<production.Categories categoryid="3" categoryname="Confections" description="Desserts, candies, and sweet breads" />
<production.Categories categoryid="4" categoryname="Dairy Products" description="Cheeses" />
<production.Categories categoryid="5" categoryname="Grains/Cereals" description="Breads, crackers, pasta, and cereal" />
<production.Categories categoryid="6" categoryname="Meat/Poultry" description="Prepared meats" />
<production.Categories categoryid="7" categoryname="Produce" description="Dried fruit and bean curd" />
<production.Categories categoryid="8" categoryname="Seafood" description="Seaweed and fish" />
<production.Categories categoryid="9" categoryname="Beer" description="Budweiser" />
<production.Categories categoryid="10" categoryname="liquor" description="Captain Morgan" />
</root>'

Query @X to:
         A. Return the categoryid of categoryname "Beer"
         B. Return everything from @x
         C. Shread categoryname and description from categoryid 9 and 10
8. What kind of XML indexes can be created?  page 260
9. What's the difference between element centric XML, and attribute centric?

Chapter 8
10. Create a table called QuestionTen in TESTDB.  Column one called "PlayerID" should be an integer that is 5 digits, this column can't be NULL.  Column two called "FirstName" as a variable character up to 25 characters long, this can be NULL.
11. Alter table "QuestionTen" and add "LastName" as variable character 25, not null
12. Add primary key constraint to table "QuestionTen" on the PlayerID column.
13. Discuss what the difference is between a primary key constraint, and a unique key constraint
14. What is a default constraint, what's the syntax?
15. What is a check constraint, what's the syntax?

That should keep me busy, along with not forgetting stuff from chapters 3-5.... Cheers!

Chapter 9
COMING SOON

Chapter 10
COMING SOON

Chapters 6-10, Life things

Well since there are about 17 chapters in this book I am going to try to split up the homework section by chapters of 5.  Previously I wrote a homework assignment that incorporated chapters 1-5, I'm going to do the same thing in my next post about chapters 6 through 10...  Chapter 7 (XML) will be favored as I write out this homework, but creating views, Inline Functions, creating/altering/deleting objects, insert/update/deleting rows of data, and adding constraints, will be in there as well.  Look for this homework in the next few days.

Tonight I'm going to do a few homework assignments from chapter 3, 4, and 5... maybe one from each and then relax.  Things have been crazy since my setback/injury trying to play catch up and having little time to get on blogger.  I always try to read the chapter I'm working on once through first, then go back and look at the lessons individually to help me absorb the material which leads to less posts because the time that I have to blog is used for reading.  Also, these last few chapters have been different then chapters 3, 4, and 5 due to the fact that there is still a lot of content, but the ability to work out the content with hours in TSQL seems to be less specific... maybe I'm not being creative enough.

Anyways, I've gotta "keep on keeping on" to get through this book.... enough with any excuses that I have.  I mentioned in the forums that I would like to test out this Friday (3/28), but with roughly 6 chapters left to go that's a very tall task.  None the less I'll be inspired after SQL Saturday #287 next weekend.. I'll for sure make a post from there.

Cheers... stay on the war path!

UPDATE, here is the homework I did today, mixed in a few different homework assignments, I'm only posting the answers, because I want to hold my self accountable for doing the homework, and not just saying that I did it....:

SELECT DATEDIFF(MONTH,MIN(hiredate),MAX(hiredate))
FROM hr.Employees
--
WITH CTE AS (SELECT orderid, custid, shippeddate,
DATEDIFF(DAY,shippeddate,GETDATE()) AS diff
FROM sales.orders)
SELECT orderid, custid, shippeddate,
'since shipped' =
CASE
WHEN diff > 1 THEN diff
WHEN diff IS NULL THEN '-999999'
END
FROM CTE
--
SELECT custid, orderid,
AVG(val) OVER(PARTITION BY custid
ORDER BY orderdate, orderid
ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS lstfour
FROM sales.OrderValues
--
SELECT custid, orderdate, orderid, SUM(val) OVER(PARTITION BY custid
ORDER BY orderdate, orderid
ROWS UNBOUNDED PRECEDING) AS runtot
FROM sales.OrderValues
--
WITH CTE AS(
SELECT orderid, shipperid, freight,
ROW_NUMBER() OVER(PARTITION BY shipperid
ORDER BY freight DESC, orderid) AS rankpership
FROM sales.Orders)
SELECT *
FROM CTE
WHERE rankpership <= 3
--
SELECT o.custid, c.city, c.country, COUNT(*) AS Swedeorders
FROM Sales.orders AS o
INNER JOIN Sales.Customers AS c
ON o.custid = c.custid
WHERE c.country LIKE 'Sweden'
GROUP BY GROUPING SETS((o.custid, c.city, c.country), ())

Chapter 10

Hitting double digits in chapter #'s... Woot!

This chapter is about inserting, updating, and deleting. Topics that have many real world uses... I'll be paying keen attention to this chapter and examples.

-Cheers!

Chapter 9 Suggested Prac. 2... Synonyms!

So here is the code from Chapter 9's second suggested practice.

--First off I check to see if there are any procedures on my system to begin with
SELECT name
FROM sys.procedures
-- Then I create an insert procedure on production.categories like the book suggests
CREATE PROCEDURE Production.CategoriesINSERT
@categoryname nvarchar(15),
@description nvarchar(200)
AS
INSERT INTO production.categories VALUES
(@categoryname, @description)
--I check to see if it works
EXEC production.CategoriesINSERT 'Beer','Budweiser'
--It worked!  Then I create a Synonym for it
CREATE SYNONYM categoryinsert FOR Production.CategoriesINSERT
--Check to see if it worked again
EXEC categoryinsert 'liquor', 'Captain Morgan'
--Now run it without any values and observe the error
EXEC categoryinsert
--The error refers to the procedure, not the synonym because
--the synonym simply points to the procedure

Appropriate timing

I'm on the SQL server central daily email blast, today the topic was Views!

Here is a link to the page:

http://www.sqlservercentral.com/articles/Stairway+Series/107120/

I thought it was appropriate!

One other note, as I was going back and doing homework some of the homework I assigned myself I realized that Blogger was auto correcting my spelling of 'OrderID' to 'Ordered'... it's a small annoying thing that I though I should point out.. I'm correcting it when I see it.  Also I revised the answer on question five of chapter 5... it was wrong

Cheers

Chapter 9 Suggested Practice 1

This chapter dealt with Views, Inline Functions, and Synonyms

The first practice asks you to create a view with CHECK OPTION, then try to insert something into the view, but because the view was designed with CHECK OPTION it fails and is terminated.  Take the CHECK OPTION off and it works.

So just to be clear, I think of a view a lot like a CTE that you can always access.  There are a few options with a view though such as with schemabinding, with encryption, with metadata, and with check option.  It might not matter, but something to remember would be that you have to specify the "with check option" after the "where" clause in the Select statement.

So this is the code that I used in this practice:

IF OBJECT_ID(N'chnine','V') IS NOT NULL DROP VIEW chnine

GO

CREATE VIEW chnine

AS

SELECT * FROM hr.Employees

WHERE country = 'USA'

WITH CHECK OPTION;

--

INSERT INTO dbo.chnine (lastname,firstname,title,titleofcourtesy,birthdate,hiredate,

address,city, region, postalcode, country, phone) VALUES('smith','jim','ceo','mr.',

'19980216','20080201','1234elm','Dallas','TX','23201','Canada','2065511253')

--

SELECT * FROM chnine

--

DELETE FROM chnine

WHERE lastname = 'smith'

--

You shouldn't run all of this code at once, but highlight it and run it separately.  I'm working on the second piece of this chapter now, will post results for the Synonyms project next!

Cheers!

Sudoku & TSQL

Have spent some time today looking over some basic queries from chapter 3, 4, and 5.  My brain starts firing and thinking clearer the more problems that I try to work out.

I haven't written this before, but I'm a huge fan of the Sudoku puzzles (google it if you have never heard of it).  When you get in a groove in Sudoku you feel like you blast through the puzzle, seeing every angle, every line, knowing every missing number... I feel the same way when I begin doing some of my homework assignments.  Visualization becomes more clear, focus is sharper, and I'm able to link things in my mind faster.

Homework tonight, finishing chapter 9 tomorrow.

Cheers!

Set back to sailing forward.. BLITZ!!!

So I've had a sluggish week with the injuries and some other things going on in my life, but I'm ready to rededicate myself to finishing this book and getting through the exam.

Through my "born to learn" forum buddies I'm motivated to get going again.  First thing is first is to do more home work each day.  Even if it is one or two problems.  It's important to keep all of this stuff fresh in my head, because I can tell after a week that I'm not as sharp as I was when I was doing this everyday.

A buddy from the forums posted a question about subqueries and I had to get my book out and read a bit through chapter four to remember what they were.  I remember them as queries that could be ran on their own, but because I have been using so many CTE's I couldn't explain them as well as if I had been doing homework each night…

So I'm back on the war path, time to blitz the rest of this book and pass this exam!!!

Cheers!!!

Setback

I haven't written anything for a few days due to some health setbacks specifically muscle spasms in my upper back and neck area. Sitting down at a computer had been extraordinarily painful, as has been driving in a car, or sitting at a desk. Standing seems to be ok, and if I'm active I notice it less.

Today I had finally had enough and went to the dr to get a diagnosis thankfully it was only muscle spasms. I'll be recovering for the next few days then will write more.

I plan on taking in some sql you tube videos while I'm resting.

-Cheers

Chapter 9 lesson 1 Views

Chapter 9 Lesson 1 deals with views.  Creating and altering views along with rules associated with views.  A couple of the rules that should be known:

• Technically you can only have one SELECT statement in a view, but you can work around this by using a UNION clause
• The CREATE VIEW statements must be the first statement in a batch
• You can set the columns name of a view at the beginning of the statement instead of during the SELECT statement which looks like this:

CREATE VIEW Sales.salesrep (yearstarted, amountsold) WITH SCHEMABINDING
AS
SELECT
YEAR(A.hiredate),
SUM(B.sales)
FROM hr.employees AS A
  JOIN sales.metrics AS B
    ON a.empid = b.empid
GROUP BY YEAR(hiredate)

Instead of
CREATE VIEW Sales.salesrep WITH SCHEMABINDING
AS
SELECT
YEAR(A.hiredate) AS yearstarted,
SUM(B.sales) AS amountsold
FROM hr.employees AS A
  JOIN sales.metrics AS B
    ON a.empid = b.empid
GROUP BY YEAR(hiredate)

Small difference but worthing writing out and taking note of.

There are a few options when creating a VIEW as well.  These options can help with data integrity, and keeping things clean (a very technical way of putting it right?).

1. Option one is WITH SCHEMABINDING binds the view to the tables that it refers to.  "The view cannot have its schema definitions changed unless the view is dropped" according to the training kit
2. WITH ENCRYPTION makes it more difficult for users to see the SELECT text in the view
3. WITH VIEW_METADATA returns the metadata of the view which could be a part of the DB-Library, or API.. instead of the base tables metadata.
4. WITH CHECK OPTION is a filter that makes sure that when updating a view it restricts modifications that don't meet criteria that you set in the WHERE clause. 

I realized that I didn't really define what a view was… A view is an object that is similar to a table.  It holds data that is referenced to a base table.

To create view you write: CREATE VIEW abc.xyz WITH SCHEMABINDING AS.. (with schema binding optional)

To alter a view you write: ALTER VIEW abc.xyz WITH SCHEMABINDING AS… (with schema binding optional)

To drop a view you write: DROP VIEW abc.xyz;

Before creating a new view in place of an old one you should start with:

IF OBJECT_ID('abc.xyz', N'V') IS NOT NULL DROP VIEW abc.xyz

GO

CREATE VIEW abc.xyz…

Lastly, here is a way to check what views you currently have in your database:

USE (some DB)

GO

SELECT name

FROM sys.views;

That's all I've got for now…. more on Inline Functions later!!!

Cheers!

credit: querying microsoft sql server 2012; Itzik Ben-gan, Dejan Sarka, Ron Talmage

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!

Chapter 8, Creating and Altering Tables

I've dug into chapter 8 more heavily lately, mainly reading the chapter over once quickly, letting the ideas melt in my head, then twice to understand in more detail the objective of the chapter... and finally a third time to understand the exercises and try them myself.

Today I'll be completing the "suggested practice" at the end of chapter in which I'll need to know how to create tables, alter tables/columns, create constraints, and transfer columns (there are probably a few more things in there too).

I find it's almost more difficult to study chapters like 6 and 8 because working on them in TSQL is more difficult.  It's more like you need to understand the syntax and know how to write it, but more so WHY you would need to utilize these things.  I'll post some info on chapter 8 in my next post.  Hopefully I'll be completing the practice today at lunch, then again this evening when I get home.

Cheers!