DB4Breakfast

Lately, I start practicing some memory training exercises. I'll start posting memory exercises so everyone can try. I think training our brain will help us learning database. For all the exercises below: score 1 point for every digit in the correct order. If you list the first 3 digits correctly, but the 4th one incorrect, you'll get 3 points. You'll need a sheet of paper and pen to write down your answers. Also a clock for timer. Here's a link that has a timer. Exercise 1: You have 1 minutes to memory the order of these 10 binary digits. After that you have 1 minute to recall the digits in exact order. 0100011011 Exercise 2: You have 1 minutes to memory the order of these 10 binary digits. After that you have 1 minute to recall the digits in reversed order. 1100110101 Exercise 3: You have 3 minutes to memory the order of these 30 binary digits. After that you have 1 minute to recall the digits in exact order. 010001101100110101001101110110

Before leaning FOR XML PATH option and XQuery, today, we'll learn about XPath Expression. This post will go over some XPath terminologies. Next post we'll look at the XPath syntax and work through some examples. If you want to learn more about XPath, please read the W3C XPath documentation here. W3schools website also has a good tutorial here. Xpath (XML Path Language)     Is a standard for define path to element in XML fragments/documents.     Like a query language for XML     Expressed in hierarchical way (like path in UNIX file system)     Used in XQuery, XSLT, Xpointer, XML Schema     Level are delimited with / character     By default every column are element     Prefix the column alias with @ character to covert column to attribute-centric formatting. XPath Nodes:     Root     Element     Attribute     Text     Namespace     Comment...

I was working on a project at work, and came up with the 2 scripts below. Some people may find the scripts useful. The first sql script will replace a range of characters within a single string. The second script will replace a range of characters within a rowset. Real life use of these scripts example: Let's assume that we have an application(or a spreadsheet) tracking people annual income but the income column data type is varchar or text, and users may enter these: 100000.00 $100000 $100000 $100000/yr $ 100000/annual If we try to convert the values above(except the first one) to a decimal or money, the conversion will fail. Our goal is to remove all character accept character 0 to 9 and the period(.). After that we can convert it to a different data type such decimal or money. Notice that I use the same technique on the example in this old post, Replace Function with Recursive CTE --Script 1: replace a range of characters for a single string. DECLARE @original_s...

Today, we will explore how to transforms rows into XML elements when dealing with joining multiple tables. The examples below will demonstrate the important of the columns ordering when using join. -- Example 1. -- This query returns the first 2 customers' orders -- by INNER JOIN Sales.Customers and Sales.Orders table SELECT c.custid, c.contactname, o.orderid, o.orderdate FROM Sales.Customers AS c JOIN Sales.Orders AS o ON o.custid = c.custid WHERE c.custid <= 2; Result: The customers' orders are not ordered based on the custid. Without the ORDER BY clause, there's no guarantee of the rows ordering. -- Example 2 --In this query, ORDER BY clause is not specified and result is transformed to XML. SELECT c.custid, c.contactname, o.orderid, o.orderdate FROM Sales.Customers AS c JOIN Sales.Orders AS o ON o.custid = c.custid WHERE c.custid <= 2 FOR XML AUTO; Result: Notice just like, in term of ordering,  the result of this query is the same as previo...

This post we'll explore the XML AUTO mode. FOR XML AUTO     By default:         No nesting         Use attribute-centric presentation.         Element/row tag names = schema.table or the table alias.         It's not allowed to change the row/element tag names by specifying an argument for AUTO mode.         Note it's only allowed to specify an argument to RAW or PATH mode of FOR XML to change the element/row tag name.                 Example:                 SELECT custid                               ,contactname                 FROM Sales.Customers                 FOR XML RAW('Customers'); By adding the ELEMENT directive, the xml ...

Let's take a break from XML. : ) To build a foundation for other topics(indexing, insert, update, merge) today we'll learn how to create a table. This is part of chapter 8, lesson 1 in 70-461 training kit. You can read more about the syntax here . -- --Create customers and orders table on dbo schema of the AdventureWorks2012 sample database. USE AdventureWorks2012; GO CREATE TABLE dbo.Customers ( custid BIGINT NOT NULL PRIMARY KEY, lastname VARCHAR(30) NOT NULL, firstname VARCHAR(30) NOT NULL ); GO CREATE TABLE dbo.Orders ( orderid BIGINT NOT NULL PRIMARY KEY, orderdate DATE NOT NULL , totalamount DECIMAL(10,4) NOT NULL, returned CHAR(1) NOT NULL DEFAULT('N'), --indicate the order was returned by customer custid BIGINT NOT NULL FOREIGN KEY REFERENCES dbo.Customers(custid), CHECK (totalamount > 0.00), CHECK (returned IN ('Y','N')) ); GO Notice that the dbo.Customers table has a primary key constraint. The dbo.Or...

Previous 2 posts, we learned about XMLDATA and XMLSCHEMA directive of the FOR XML clause. These 2 directives returns the XDR(XML-Data Reduced) and XSD(XML Schema Definition) schema along with the XML data. In this post, we will learn how to get only the XDR or XSD schema without XML data. --This query returns the XML-Data Schema only without the XML data SELECT custid, contactname FROM Sales.Customers WHERE 1 <> 1 -- this condition always evaluated to false, thus empty set is returned. FOR XML RAW , XMLDATA; Result: notice that only the XDR schema is returned.   <Schema name="Schema1" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">   <ElementType name="row" content="empty" model="closed">     <AttributeType name="custid" dt:type="i4" />     <AttributeType name="contactname" dt:type="string" />    ...