Yesterday was my last day of MBA classes and needless to say I was out partying hard last night. I woke up early for some strange reason and decided to get a jump on my book chapters for the upcoming Programming SQL Server 2008 from Microsoft Press.

My VPC died on me so I decided to give SQL Server 2008 RC0 an install on a spare machine (what better to do on a Sunday morning with a hangover). I remember reading that if you have SQL Server 2005 Express edition installed you will have trouble installing RC0. So I went in and uninstalled everything on a spare machine, too hungover to reformat. I figured I am an MBA now, I should make myself more efficient.

Well, even with nothing left of SQL Server 2005, I still got a very mean error message (well after I entered in all the account, mixed mode, information):

"The SQL Server 2005 Express Tools are installed.  To continue, remove the SQL Server 2005 Express Tools."

I was not happy since I had already removed it. So I started removing Visual Studio 2005 too. That did not work. So I decided the next thing to do was Google. Someone else had to have had this pain already. Found this post from Jason Follas. He said that if you have Red Gate SQL Prompt software installed, as I did, it leaves a bad registry key and showed how to fix it. Read the blog post here.

I can't live without Red Gate SQL Compare and SQL Data Compare so I did not mind losing some time. They save me day and days of time over the year. The good news is that Red Gate left a note on Jason's blog that the issue has been resolved.

I guess the MBA was lost on me. Oh well, at least I can write TSQL and google for install problems...

Unless you have been living under a rock, you should already know about the controversy over the nHybernate mafia's Microsoft Entity Framework's Vote of No Confidence.  The manifesto says that the upcoming Entity Framework (to be released soon via .NET 3.5 SP1) is just not usable. As far as ORMs go, there are some valid points working against EF- in order of (my) importance:

• Lack of Lazy Loading (The EF team said that they had "explicit" lazy loading, but that is just playing with words and the team has since retracted from that.)
• Lack of persistence ignorance (at best this is inelegant design, at worst it will cripple the performance of your application and make it a burden to maintain)
• Too many merge conflicts when using a model in a source control environment
• Bad design for TDD development (and in my opinion, CI as well)

The manifesto also brings up some Domain Driven Design issues that I feel are less important than the above four bullets. To their credit the normally reserved EF team has responded quickly and nicely to the manifesto. On the heels of last week's transparent-design process announcement, Tim Mallalieu gave a detailed reply which has started a good discussion and wiki. They are listening and that is good.  Since the Entity Framework is starting work this week on v.next, they have also put together an advisory council including Eric Evans, Martin Fowler, Paul Hruby, Jimmy Nilsson, and myself.

To some degree the vote of no confidence worked, they got Tim's attention. I think that the manifesto has some very valid points, but I also think it lacks professionalism. Anyone can say something sucks, it is more important to give valid feedback, suggest solutions, and engage in a dialog. (The vote of no confidence was just too in your face-and a dialog was stated only because of the professionalism of the EF team.) In addition there are some mafia style attacks on anyone who does not agree with them, most recently against the always honest and open Julie Lerman.

So this blog post sounds like an endorsement of the Entity Framework and ORMs in general, right?

Wrong.

My first problem with ORMs in general is that they force you into a "objects first" box. Design your application and then click a button and magically all the data modeling and data access code will work itself out. This is wrong because it makes you very application centric and a lot of times a database model is going to support far more than your application. In addition an SOA environment will also conflict with ORM.

I prefer to build the application's object model and the data model at about the same time, with a "whiteboarding" approach that outlines the flows of data and functionality across the business process and problem set. Maybe it is the MBA talking but I tend to be "business and customers first" when I design a system. (Those of you that know me know that I have designed some very large and scalable systems in my day.) I usually like to "follow the money" as Ron Rose of Priceline taught me 10 years ago.  In addition, I prefer a very agile design process where you are constantly making changes to your design in the early sprints, the database and object model are both part of this process.

My second major problem with ORMs is that ORMs are a solution to a problem that should not be solved. Developers who write object oriented and procedural code like C# and Java have trouble learning the set-based mathematics theory that govern the SQL language. Developers are just plain old lazy and don't want to code SQL since it is too "hard." That is why you see bad T-SQL: developers try to solve it their way, not in a set-based way.

The premise of EF, LINQ, nHybernate, and Luca Bolongese’s original premise with ObjectSpaces, is that set-based theory causes an “impedance mismatch” between data access and all the other (more procedural) coding we do.  And it’s ORMs to the rescue to resolve the impedance mismatch.

So ORMs are trying to solve the issue of data access in a way that C# and VB developers can understand: objects, procedural, etc.  That is why they are doomed to fail. The further you abstract the developer from thinking in a set-based way and have them write in a procedural way and have the computer (ORM) convert it to a set-based way, the worse we will be off over time.

What I am saying (and have been saying for a long time) is that we should accept, no, embrace the impedance mismatch!  While others are saying we should eradicate it, I say embrace it.

ORM tools should evolve to get closer to the database, not further away.

One of the biggest hassles I see with LINQ to SQL is the typical many-to-many problem. If I have a table of Ocean Liners, vessels,  and ports, I’ll typically have a relational linking table to connect the vessels and ports via a sailing. (Can you tell I am working with Ocean Freight at the moment?) The last thing I want at the object layer is three tables! (And then another table to look up the Ocean Liner that operates the vessel.) Unfortunately, this is what most tools give me. Actually I don't even want one table, I want to hook object functionality to underlying stored procedures. I really want a port object with a vessel collection that also contains the ocean liner information. At least the Entity Framework does this however I have major concerns about the performance of the code it produces.

The ironic thing I’m now seeing is developers who are lazy and don't want to learn SQL using tools that will produce SQL for them. The SQL is bad, and now those same anti-SQL lazy developers are struggling to read pages of generated and near-unreadable SQL trying to solve performance problems. They’re dealing with SQL that’s more verbose and orders of magnitude harder to understand than what was needed in the first place!

So where do we go from here? We can't just keep pretending that this mismatch problem can be solved and keep throwing good money after bad. As Ted Neward said that is the Vietnam of Computer Science.

I say that developers should start embracing the impedance mismatch. I also say that Microsoft and other ORM vendors need to realize that embracing the mismatch is a good thing and design ORM tools that allow this. (This is the advice I will give at the EF council.) I am not anti-ORM (even tho I did trash LINQ on stage at TechEd last year), but I am pro using the right tool/technology for the right job. ORM is real good for CRUD and real bad at other things. Let's start from there. To quote Ted, "Developers [should] simply accept that there is no way to efficiently and easily close the loop on the O/R mismatch, and use an O/R-M to solve 80% (or 50% or 95%, or whatever percentage seems appropriate) of the problem and make use of SQL and relational-based access (such as "raw" JDBC or ADO.NET) to carry them past those areas where an O/R-M would create problems."

Hopefully "cooler heads will prevail."

Update 6/29/08

My fellow RD Greg Low brought to my attention that my discussion on Linq to SQL above is very similar to an email he sent not to long ago. At Triton Works (the ocean containers) we used his example in our own entity model debate and I realized after he pointed it out to me that the words above are just too close to his, so I am giving him the credit above. (Though he did not call programmers lazy and anti-SQL, I did.)

FYI, we went with POAN or Plain Old ADO.NET, ocean containers are old school.

The inspiration for this post came from not only the public debate on the EF vote of no confidence but also a private email discussion on nHybernate, EF and Linq to SQL between:  Greg, Andrew Brust, Rocky Lhotka, Sten Sundbland, Joel Semeniuk, Barry Gervin, and myself. I won't tell you who was on what side. If you want to know, you will have to ask them. Hint, hint, Canadians are lazy and anti-SQL. :)

Yesterday we discussed the changes to XQuery by allowing LET statements. Today the last XML enhancement is an "enhancement" to XML DML.

XML Data Manipulation Language (DML) is specific to MS SQL Server 2005 and 2008. It is a very useful feature that allows you to insert nodes to an existing XML instance, delete an element, or replace the value of (sort of an edit.) It works like this:

First let's create a table with an XML data type and insert a piece of XML into it:

Create table XMLTest1
    (
    Speaker_ID int primary key,
    SpeakerXML xml not null
    )

GO

Insert into XMLTest1
Values
(
    1,
    '
    <classes>
        <class name="Writing Secure Code for ASP .NET " />
        <class name="Using XQuery in SQL Server 2008" />
        <class name="SQL Server and Oracle Working Together" />
    </classes>
    '
    )

Now to insert an element into this table it is pretty easy, just use the XML modify method of the XML data type. Since our XML instance is in a SQL Server table we have to use the standard SQL UPDATE and SET syntax with a WHERE clause to get to the piece of XML in our table we are concerned with. You can see how easy it is to add an element to the table here:

Update XMLTest1
Set SpeakerXML.modify(
'insert
<class name="Using Linq to SQL" />
into /classes[1]'
)
Where Speaker_ID=1

If we select * from XMLTest1 WHERE Speaker_ID=1 the XML column looks like this now:

<classes>
  <class name="Writing Secure Code for ASP .NET " />
  <class name="Using XQuery in SQL Server 2008" />
  <class name="SQL Server and Oracle Working Together" />
  <class name="Using Linq to SQL" />
</classes>

SQL Server 2008 has an enhancement to this. You can substitute the XML element with a variable. For example let's create a variable here:

DECLARE @newElement xml
Set @newElement='<class name="ASP.NET Scalability"/>'

Now let's do the same insert as before, however, we will use the @newElement variable. This will enable you to select XML from other areas and stick it into a variable and then insert it into another XML instance. Here is the final piece:

Update XMLTest1
Set SpeakerXML.modify(
'insert
sql:variable("@newElement") 
into /classes[1]'
)
Where Speaker_ID=1

If we select * from XMLTest1 WHERE Speaker_ID=1 the XML column looks like this now:

<classes>
  <class name="Writing Secure Code for ASP .NET " />
  <class name="Using XQuery in SQL Server 2008" />
  <class name="SQL Server and Oracle Working Together" />
  <class name="Using Linq to SQL" />
  <class name="ASP.NET Scalability" />
</classes>

Not the greatest of new features, but a handy dandy feature to say the least.

Continuing our discussion of XML in SQL Server 2008, SQL Server 2005 alowed us to use XQuery to query XML data in an XML data type.  You can use the FLWOR statements of for, where, order by, and retun, but not LET. SQL Server 2008 now has support for LET, which is used to assign values to variables in an XQuery expression. For example we are going to take the XML below and count the number of sessions per speaker using the count XQuery expression and assign it to a variable via LET.

declare @xml xml
set @xml=
'
<Speakers>
    <Speaker name="Stephen Forte">
        <classes>
            <class name="Writing Secure Code for ASP .NET "/>
            <class name="Using XQuery in SQL Server 2008"/>
            <class name="SQL Server and Oracle Working Together"/>
        </classes>
    </Speaker>
    <Speaker name="Richard Campbell">
        <classes>
            <class name="SQL Server Profiler"/>
            <class name="Advanced SQL Querying Techniques"/>
            <class name="SQL Server and Oracle Working Together"/>
            <class name="From 1 Server to 2"/>
        </classes>
    </Speaker>
</Speakers>
'
SELECT @xml.query(
'<Speakers>
{
for $Speaker in /Speakers/Speaker
let $count :=count($Speaker/classes/class)
order by $count descending
return
<Speaker>
{$Speaker/@name}
{$Speaker/count}
<SessionCount>{$count}</SessionCount>
</Speaker>
}
</Speakers>')

The result is shown here, notice that Richard sorts on top since we used the descending order to our order by and ordered by the expression we created via the LET statement. This is very helpful if you are manipulating simple XML and need to do a mini-transform via XQuery. I used to use the RETURN statement all the time to make slight manipulations to my XML, so I was waiting for LET. While this is the only addition to XQuery, it is a much needed and welcome one.

<Speakers>
  <Speaker name="Richard Campbell">
    <SessionCount>4</SessionCount>
  </Speaker>
  <Speaker name="Stephen Forte">
    <SessionCount>3</SessionCount>
  </Speaker>
</Speakers>

Continuing our discussion from yesterday on SQL Server 2008 XML Enhancements, let's take a look at the XSD enhancement of Union and List types. In SQL Server 2005 you could define a simple type of xsd:list with an enumeration to require items in a list for any element that inherits that type. For example our XSD below will allow FedEx, DHL, and UPS for a shipping.

create XML SCHEMA COLLECTION dbo.order_xsd
AS
'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   <xsd:simpleType name="orderAmt" >
     <xsd:restriction base="xsd:int" >
       <xsd:maxInclusive value="5000" />
       <xsd:minExclusive value="1" />
     </xsd:restriction>
   </xsd:simpleType>
   <xsd:simpleType name="shiptypeList">
    <xsd:list>
        <xsd:simpleType>
          <xsd:restriction base="xsd:string">
            <xsd:enumeration value="FexEx"/>
            <xsd:enumeration value="DHL"/>
            <xsd:enumeration value="UPS"/>
          </xsd:restriction>
        </xsd:simpleType>
    </xsd:list>
    </xsd:simpleType>
   <xsd:element name="Order">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="CustomerName" type="xsd:string" />
        <xsd:element name="OrderDate" type="xsd:dateTime"/>
        <xsd:element name="OrderAmt" type="orderAmt"/>
        <xsd:element name="ShipType" type="shiptypeList"/>   
      </xsd:sequence>
    </xsd:complexType>
   </xsd:element> 
</xsd:schema>'
GO

DECLARE @xml XML(dbo.order_xsd)
SET @xml =
'<Order>  
    <CustomerName>Bill Gates</CustomerName>
    <OrderDate>2008-10-10T14:22:27.25-05:00</OrderDate>
    <OrderAmt>100</OrderAmt>
    <ShipType>DHL</ShipType>
</Order>'
GO

So this is cool, gives you some database lookup functionality, kind of third normal form Clemens Vasters style. With the new support for union of lists in SQL Server 2008 with xsd:union, you can combine multiple lists to one simple type. For example in our XSD below we allow the same strings as FedEx, etc, but also numerical values.

My example below is pretty basic, but this is useful if you have more than one way to describe something and need two lists to do so. One thing that comes to mind is units of measurement, metric and English measurements, so shoe sizes, US and Italian, etc.  This is very useful when you need to restrict items and are writing them from a database, etc.

Take a look at the demo for SQL 2008 below, notice that I changed the data to 1 from FedEx:

create XML SCHEMA COLLECTION dbo.order_xsd
AS
'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   <xsd:simpleType name="orderAmt" >
     <xsd:restriction base="xsd:int" >
       <xsd:maxInclusive value="5000" />
       <xsd:minExclusive value="1" />
     </xsd:restriction>
   </xsd:simpleType>
    <xsd:simpleType name="shiptypeList">
        <xsd:union>
            <xsd:simpleType>
              <xsd:list>
                <xsd:simpleType>
                  <xsd:restriction base="xsd:integer">
                    <xsd:enumeration value="1"/>
                    <xsd:enumeration value="2"/>
                    <xsd:enumeration value="3"/>
                  </xsd:restriction>
                </xsd:simpleType>
              </xsd:list>
            </xsd:simpleType>
            <xsd:simpleType>
              <xsd:list>
                <xsd:simpleType>
                  <xsd:restriction base="xsd:string">
                    <xsd:enumeration value="FedEx"/>
                    <xsd:enumeration value="DHL"/>
                    <xsd:enumeration value="UPS"/>
                  </xsd:restriction>
                </xsd:simpleType>
              </xsd:list>
            </xsd:simpleType>
        </xsd:union>
    </xsd:simpleType>

   <xsd:element name="Order">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="CustomerName" type="xsd:string" />
        <xsd:element name="OrderDate" type="xsd:dateTime"/>
        <xsd:element name="OrderAmt" type="orderAmt"/>   
        <xsd:element name="ShipType" type="shiptypeList"/>
        <xsd:any namespace="##other" processContents="lax"/>   
      </xsd:sequence>
    </xsd:complexType>
   </xsd:element> 
</xsd:schema>'
GO

DECLARE @xml XML(dbo.order_xsd)
SET @xml =
'<Order>  
    <CustomerName>Bill Gates</CustomerName>
    <OrderDate>2008-10-10T14:22:27.25-05:00</OrderDate>
    <OrderAmt>100</OrderAmt>
    <ShipType>1</ShipType>
    <Notes xmlns="sf">Steve Test 1</Notes>
</Order>'
GO

The SQL Server 2008 February CTP is now installed. I did it on my daily machine to force me to use it in a more realistic environment. It installed a-ok with my default SQL 2005 instance. As you can see below you can use the SQL 2008 IDE to manage and work with both SQL Server instances on your computer at the same time.

I am playing with the new XML features of SQL Server. For those of you that know my old company, Corzen, used the XML data type to do some really cool stuff. I'm now investigating the XML features for the update to the Programming SQL Server 2008 book from MS Press.

I guess that XLINQ is pretty cool and well accepted Microsoft did not change all that much in the XML world in SQL Server 2008. The new XML features of SQL Server 2008 are:

• Lax validation support in XSD-giving you some more flexibility with your required areas of the XSD
• Full support of the XSD: xs:dateTime, basically the preservation of time zones
• Union and List types in XSDs (I have not played with this yet so can't speak to how useful it will be yet, give me a day)
• Support of the LET FLOWR expression in XQuery (yay!)
• XML DML "enhancements." I put that in quotes for two reasons, there is only one true enhancement, allowing you to insert data from a SQL column or a SQL variable as part of your insert. This is a very minor enhancement, I was hoping for a new syntax for specifying position, etc. Also XML DML is SQL Server specific, so it is hard to get very excited about this feature.

Now let's take a look at some of the XSD enhancements. SQL Server 2005 added the ability put XML data into an intrinsic XML data type. This was a vast improvement over SQL Server 2000 where we had to put data in text fields. In addition, SQL Server 2005 allowed us to restrict this column with an XSD. Consider this XSD:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   <xsd:simpleType name="orderAmt" >
     <xsd:restriction base="xsd:int" >
       <xsd:maxInclusive value="5000" />
       <xsd:minExclusive value="1" />
     </xsd:restriction>
   </xsd:simpleType>
   <xsd:element name="Order">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="CustomerName" type="xsd:string" />
        <xsd:element name="OrderDate" type="xsd:dateTime"/>
        <xsd:element name="OrderAmt" type="orderAmt"/>
      </xsd:sequence>
    </xsd:complexType>
   </xsd:element> 
</xsd:schema>

We have defined a few elements, given them a sequence and data types. We also say that you can only have an order amount between 1 and 5000 (work with me, I like simple demos) since the OrderAmt element inherits the simple type orderAmt. This works well, for example if we create an XSD and a table with an XML column bound to that XSD in SQL 2005:

--Valid SQL Server 2005 Schema
CREATE XML SCHEMA COLLECTION dbo.order_xsd
AS
'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   <xsd:simpleType name="orderAmt" >
     <xsd:restriction base="xsd:int" >
       <xsd:maxInclusive value="5000" />
       <xsd:minExclusive value="1" />
     </xsd:restriction>
   </xsd:simpleType>
   <xsd:element name="Order">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="CustomerName" type="xsd:string" />
        <xsd:element name="OrderDate" type="xsd:dateTime"/>
        <xsd:element name="OrderAmt" type="orderAmt"/>
      </xsd:sequence>
    </xsd:complexType>
   </xsd:element> 
</xsd:schema>'
GO

CREATE TABLE xml_schematest (
   Order_ID int primary key,
   Order_XML XML(order_xsd) --XML Schema Name
)

Now let's insert some XML in there, the XML will be like this:

Insert into xml_schematest
VALUES
(1,
'<Order>  
    <CustomerName>Bill Gates</CustomerName>
    <OrderDate>2008-10-10T14:22:27.25-05:00</OrderDate>
    <OrderAmt>100</OrderAmt>
</Order>
')

Here is the result, notice the Z for UTC as well as the new time (more on that below):

<Order>
  <CustomerName>Bill Gates</CustomerName>
  <OrderDate>2008-10-10T19:22:27.250Z</OrderDate>
  <OrderAmt>100</OrderAmt>
</Order>

This XML will fail since it has an order over 5000.

Insert into xml_schematest
VALUES
(2,
'<Order>  
    <CustomerName>Bill Gates</CustomerName>
    <OrderDate>2008-10-10T14:22:27.25-05:00</OrderDate>
    <OrderAmt>10000</OrderAmt>
</Order>
')

So life is good. We have a nice XSD and it restricts our content. SQL Server 2008 has added two new features of an XSD that we will look at today. The first is something called Lax validation. Let's say that you wanted to add an additional element after <OrderAmt> that is not part of the same schema. In SQL Server 2005, schemas can use processContents values of skip and strict for any and anyAttribute declarations as a wildcard. If it was set to skip, SQL will skip completely the validation of the additional element, if it is set to strict, SQL will require that it has an element or namespace defined in the current schema. SQL Server 2008 adds support for an additional validation option: lax. By setting the processContents attribute for this wildcard section to lax, you can enforce validation for any elements that have a schema associated with them, but ignore any elements that are not defined in the schema. Pretty cool, no?

Also the xsd:dateTime XML data type is now timezone aware. SQL Server 2005 you had to provide a time zone for dateTime, time and date data (did you really ever do that? If you needed the flexibility, trust me, this was a pain). What was not cool was that SQL Server 2005 did not preserve the time zone information for your data for dateTime or time,  it normalizes it to UTC (so for example 2008-10-10T08:00:00:000-5:00 is normalized to 2008-10-10T13:00:00:000Z. Notice the Z for UTC. Quite annoying.) In SQL Server 2008, this has been removed! Take a look at this code:

CREATE XML SCHEMA COLLECTION dbo.order_xsd
AS
'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   <xsd:simpleType name="orderAmt" >
     <xsd:restriction base="xsd:int" >
       <xsd:maxInclusive value="5000" />
       <xsd:minExclusive value="1" />
     </xsd:restriction>
   </xsd:simpleType>
   <xsd:element name="Order">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="CustomerName" type="xsd:string" />
        <xsd:element name="OrderDate" type="xsd:dateTime"/>
        <xsd:element name="OrderAmt" type="orderAmt"/>   
        <xsd:any namespace="##other" processContents="lax"/>   
      </xsd:sequence>
    </xsd:complexType>
   </xsd:element> 
</xsd:schema>'
GO
CREATE TABLE xml_schematest (
   Order_ID int primary key,
   Order_XML XML(order_xsd) --XML Schema Name
)
GO

Now this will work and even preserve the time zone of -5 (New York) and notice the random <Notes> element due to lax validation. Here is how to insert the XML:

Insert into xml_schematest
VALUES
(1,
'<Order>  
    <CustomerName>Bill Gates</CustomerName>
    <OrderDate>2008-10-10T14:22:27.25-05:00</OrderDate>
    <OrderAmt>100</OrderAmt>
    <Notes xmlns="sf">Steve Test 1</Notes>
</Order>
')

Here are the results in the database with the original time zone:

<Order>
  <CustomerName>Bill Gates</CustomerName>
  <OrderDate>2008-10-10T14:22:27.25-05:00</OrderDate>
  <OrderAmt>100</OrderAmt>
  <Notes xmlns="sf">Steve Test 1</Notes>
</Order>

Tomorrow I will look at Union and List types.

Take a look at Andrew's and my book in French, Polish and Italian!

Enjoy!

The MDC is here!

The code and slides for SQL CLR In Action session are here. The code for Advanced TSQL is here.

Code for T_SQL kullanımını kolaylaştıran ve hızlandıran, bilinmeyen teknikler is here. Enjoy!

We are hiring a DBA at my company Corzen and instead of looking at resumes/cvs we are looking at how people solve particular SQL problems-not just the solution but the approach they take. (Do you use temp tables, (nolock), CTEs, table alias, etc.)

Here are the five test questions we ask. (If you want to come work here, just email me the answers.)

Here are three TSQL problems that can judge how you approach a SQL problem. There are no real wrong answers, but the more efficient and bulletproof the query is the better. No cheating and no cursors! Don't cheat and don't google the answers (we'll know.)

Step 1: run SetupTSQLProblems.sql in any database.

Problem 1.

Finding Duplicate Products in the Product table. Write a query to list only the duplicated products in the Products table. Assume that the dupe is the product with the highest Product_ID. The results of your SQL statement should be:

Dupe_Product_ID	Dupe_Product_Name	Dupe_Price	Parent_Product_ID	Parent_Product_Name
7	Widgets	88	1	Widgets

Problem 2.

Assign rooms to classes based on capacity using the Classes and Rooms tables. Rules: each class should have a room (and NULL if a room is not available). No class can be in a room where there are more students than capacity. No room can be used twice.

The results of your SQL statement should be:

class	students	room	capacity
Advanced SQL Queries	65	225	70
Introduction to C#	52	224	60
XQuery Deep Dive	35	222	40

Problem 3.

Make the following static pivot dynamic in AdventureWorks, assume that the orderyear can increment to 2005 and 2006. Assume that order years can skip.

SELECT *

FROM (SELECT CustomerID, YEAR(OrderDate) AS orderyear, TotalDue

      FROM Sales.SalesOrderHeader) as Header

PIVOT(SUM(TotalDue) FOR orderyear IN([2002],[2003],[2004])) as Piv

Problem 4.

You have a piece of XML:

<office id="NYC">

  <Employees>

    <employee num="1">Steveemployee>

    <employee num="2">Alemployee>

    <employee num="3">Mikeemployee>

    <employee num="4">Malishaemployee>

  Employees>

office>

<office id="Cairo">

  <Employees>

    <employee num="1">Remonemployee>

    <employee num="2">Lameesemployee>

    <employee num="3">Bassmaemployee>

  Employees>

office>

<office id="Pune">

  <Employees>

    <employee num="1">Vinayemployee>

    <employee num="2">Pradeepemployee>

    <employee num="3">Rashmiemployee>

    <employee num="4">Sujataemployee>

    <employee num="5">Ajitemployee>

  Employees>

office>

<office id="Karachi">

  <Employees>

    <employee num="1">Amratemployee>

  Employees>

office>

A. Using the XML datatype, write an XQuery to return the 3^rd employee in the NYC office. Your results should look like:

EmployeeName

Mike

B. Using the XML datatype, write an XQuery to return the 3^rd employee in each office. Your results should look like:

EmployeeName

Mike

Bassma

Rashmi

Problem 5.

Using adventureworks, rank each salesperson by total sales in 2004 with the rank starting over for each country. Do not use temp tables, self joins, or cursors.

CountryName	SalesPersonID	TotalSales	SalesRank
Australia	290