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" />    ...

Today, we'll explore the XMLSCHEMA directive of the FOR XML clause. XMLSCHEMA Directive      Return XSD (XML Schema Definition) schema along with XML data of XML document/fragment.      Can only use with RAW and AUTO mode. SELECT custid, contactname, fax FROM Sales.Customers WHERE custid <= 3 FOR XML RAW('Customer') , ELEMENTS XSINIL , ROOT('Customers') , XMLSCHEMA; Result: Notice the targetNamespce contains an Uniform Resource Name(URN) with format as: schemas-microsoft-com:sql:SqlRowSetn. Every time we execute the same query above in the same query window, n is incremented by 1. The sqltypes namespace is the link to SQL Server datatype to XSD type mapping. <Customers xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <xsd:schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft....

Today we'll learn more about XMLDATA directive for the FOR XML clause.  XMLDATA Directive        Return XML-Data schema (XML-Data Reduced schema aka XDR schema ) of XML document/fragment.        Cannot be use with ROOT directive.        Namespace identifier = Schema+n where n is an integer. Everytime we execute the same query, n is incremented by 1.         Will be deprecated on future release of SQL Server. Recommendation: use XMLSCHEMA directive to return XSD schema instead.        --This query returns the XML-Data Schema of the XML result. SELECT custid, contactname FROM Sales.Customers WHERE custid <= 2 FOR XML RAW , XMLDATA; -- --Result: Notice, if the same query above is executed one more time in the same query window, -- the schema's name will change from Schema1 to Schema2. <Schema name="Schema1" xmlns="urn:schemas-microsoft-com:xml-data" ...

This post is for those that are brand new to SQL Server. I will guide you through how to set up a test environment for your database learning. After completing this tutorials you will have a SQL Server 2012 instance on your computer along with a sample database. Step 1: You can download a 180 day trial version of SQL Server 2012 here , or spent $40 to get the developer version with all the feature here . Step 2: Follow this instruction from Andy Leonard to install a default SQL Server Instance on your computer. Note make sure you follow this instruction exactly or else instruction on step 5 won't work. Step 3: Download a copy of the AdventureWorks database here . This database will be used for your learning. Step 4: Read the instruction below to launch SQL Server Management Studio(SSMS):   Windows 7: go to Start menu > All Programs > Go to "Microsoft SQL Server 2102" folder > launch SQL Server Management Studio   Windows 8: on your keyboard press this...

In this post we'll learn how to transforms rows from a table or queries results to XML using the FOR XML RAW. FOR XML RAW By default: No root element Attribute-centric presentation (all data is display as attributes) All row tag name = row Exclude NULL mark       Use  ELEMENTS XSINIL directive to include NULL marks. Doesn't include nesting (XML document/fragment is format flat). We'll go through a series of examples and explore the FOR XML RAW. -- -- When no directive is specified after the RAW keyword, this query uses -- default setting above for the result: -- * use attribute-centric presentation(row data is stored inside row tag) -- * no nesting -- * no root node -- * all XML elements' name = row -- * NULL marks are excluded. USE TSQL2012; GO SELECT c.custid ,c.contactname FROM Sales.Customers AS c WHERE c.custid <= 2 FOR XML RAW; Result: Notice that the result is flat - no nesting. <row cu...

More XML terminologies. Element-centric presentation          Data is stored between start tag and end tag of XML elements.          Recommendation to store data. Example: <Customers>     <Customer>  <custid>1</custid>  <contactname>Allen, Michael</contactname>     </Customer>     <Customer>  <custid>2</custid>  <contactname>Hassall, Mark</contactname>     </Customer> </Customers> Attribute-centric presentation          Data is stored inside of element tags.          Recommendation to store metadata Example: <Customers>     <Customer custid="1" contactname="Allen, Michael" />     <Customer custid="2" contactname="Hassall, Mark" /> <...

I hope the example below will help you understand more about NULL. This 4 letters word can be troublesome and fun. After reading about null of this document , and this blog post  by Hugo Kornelis, I now conclude NULL is not unknown and update my explanation below. Definition of NULL    NULL is not a value    NULL is a mark for a missing value - not a value itself.    NULL represent missing data    NULL is not unknown    NULL is not applicable    NULL isn't empty    NULL doesn't equal zero or an empty string    NULL is not the value of string 'NULL'    NULL doesn't equal to NULL (just like saying something is missing can't equal to another missing object. I'll explain more on this below.)    Also SQL uses 3-valued predicate logic: True, False, Unknown.  March 27, 2014 SSC question of the day : G uess what this query returns? Select 1 Where 'hello' = Null Union ...

This post we summarize the XML terminologies. This is the foundation for working with XML; Next few posts we'll explore how to convert SQL table to XML format and vice versa. A simple XML document: <?xml version="1.0" encoding="UTF-8"?> <Customers>     <Customer custid="1" contactname="Allen, Michael" />     <Customer custid="2" contactname="Hassall, Mark" /> </Customers> XML(eXtensible Markup Language) Document Is not relational Is Case sensitive Is ordered (the position of elements is important) Is well-formed when every begin tag has a matching  end tag, and if tags are nested properly such as no nested element. Example: this is not a well formed because the elements are not nested. <?xml version="1.0" encoding="UTF-8"?>  <Customers>        <Customer>            <custid>1     ...

Data Type: Is a constraint Encapsulates behavior Relational Model's Physical Data Independence Let the storage engine take care of the internal storage format Exact Numeric: TINYINT (1 byte) SMALLINT (2 bytes) INT (4 bytes) BIGINT (8 bytes) NUMBERIC (size varies depend on precision) DECIMAL (size varies depend on precision) SMALLMONEY(4 bytes) MONEY(8 bytes) Approximate Numeric REAL (4 bytes) FLOAT Character non-Unicode (CHAR, VARCHAR) support only one language based on collation properties 1 byte/character Use single quotation marks, 'xyz', to indicate regular character strings (non-Unicode) Character Unicode (NCHAR, NVARCHAR) Unicode, support multiple language 2 bytes/character Use N'xyz', to indicate Unicode character string. Binary Strings: BINARY VARBINARY Image Date and Time: DATE (3 bytes) TIME (3 to 5 bytes) DATETIME2 (6 to 8 bytes) SMALLDATETIME (4 bytes) DATETIME (8 bytes) ...

CASE Expression:      Is an expression      Return values      2 types: simple and search Expression Functions:      COALESCE (standard)      NULLIF  (standard)           Syntax: NULLIF( expression, expression)           Return a NULL mark if two expressions are equal.      ISNULL (TSQL specific)    Logical Functions (available in SQL Server 2012)      CHOOSE           Syntax: CHOOSE( index, val_1, val_2, [, val_n])           Return a value at specified index           Index is based 1      IIF          Syntax: IIF( boolean_expression, true_value, false_value) Date and Time Functions:     GETDATE (TSQL specific)     CURRENT_TIMESTAMP (standard) ...