Crossword Puzzle for Functions1

Try to solve this crossword puzzle. The key is answers are the function names.

Across:

1. Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.
5. Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate.
8. Returns the smallest integer greater than, or equal to, the specified numeric expression.
9. This function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
10. Determines whether a datetime or smalldatetime input expression is a valid date or time value.


Down:
2. Returns a null value if the two specified expressions are equal.
3. Returns the first nonnull expression among its arguments.
4. Repeats a string value a specified number of times.
6. Determines whether an expression is a valid numeric type.
7. Returns the number of rows affected by the last statement.


Answer:

Crossword puzzle for Functions

Try to solve this crossword puzzle for functions. The key is only names of the functions are the answers for this croosword puzzle.




















Across


1. Replaces NULL with the specified replacement value.
3. Returns a character expression after it removes leading blanks.
6. Returns a character expression with lowercase character data converted to uppercase.
7. Returns a numeric value, rounded to the specified length or precision.
8. Returns the average of the values in a group. Null values are ignored.
9. Returns the current system date and time.
10. provides a rowset view over an XML document.



Down
2. Returns part of a character, binary, text, or image expression.
4. Replaces all occurrences of a specified string value with another string value.
5. Stored procedures cannot be executed with in this object.



Answer:

Simple questions on XML

XQuery Examples:

If the following XML fragment is stored in a column with XML datatype,

<EMPLOYEE EMP_ID="AAA013" ADDRESS1="83 Railway Colony" ADDRESS2="Perambur" CITY="Chennai" STATE="TN" />

this select statement uses the value() method to retrive the attribute values.

SELECT 
txtxml.value('(//@EMP_ID)[1]','varchar(500)') EMP_ID,
txtxml.value('(//@ADDRESS1)[1]','varchar(500)') ADDRESS1,
txtxml.value('(//@ADDRESS2)[1]','varchar(500)') ADDRESS2,
txtxml.value('(//@CITY)[1]','varchar(500)') CITY,
txtxml.value('(//@STATE)[1]','varchar(500)') STATE
FROM Employee WHERE dept_code='AAA013'

The output of the above query will be as follows:

EMP_ID	ADDRESS1	ADDRESS2	CITY	STATE
AAA013	83 Railway Colony	Perambur	Chennai	TN

---

1. If you need to store XML document and manipulations are done only at client side, which one of the following datatype would you recommend?

(a) varbinary(max)
(b) xml
(c) Text
(d) char

2. The following query

SELECT EmpID, Emp_XML_Data.query('/Employee/Name') AS Name

will return the following output:

EmpID	Name
1	<Name> Sudhakar</Name>
2	<Name> Vignesh</Name>
3	<Name> Arivu</Name>

What modification to the SELECT will give the output without XML tags as follows?

EmpID	Name
1	Sudhakar
2	Vignesh
3	Arivu

(a) SELECT EmpID, Emp_XML_Data.Value('/Employee/Name') AS Name
(b) SELECT EmpID, Emp_XML_Data.Nodes('/Employee/Name') AS Name
(c) By using REPLACE function to remove the XML tags.
(d) We cannot remove the XML tags from the output.


Show Answers:

1. (a) [ - Because no XML manipulation is done at server side, Varbinary(max) datatype is sufficient. ]
2. (a) [ - Value() method returns the text node or value between the elements.

Hide Answers (X).

Basic questions on using XML in SQL Server 2005

The XML Basics:

• XML stands for EXtensible Markup Language.
• XML is a markup language similar to HTML.
• XML was designed to store data and not for display the data.
• An XML document contains XML Elements.
• Anyone can define their own tags.
• XML elements can have attributes, just like HTML.
• XPath uses path expressions to navigate in XML documents.
• XPath contains a library of standard functions.
• XQuery was designed to query XML data.

The XML data type:

• XML Data is one of the new data types introduced in SQL Server 2005.
• You can create columns, variables with data type XML.
• XML Data type allows you to store XML documents and fragments in SQL Server.
• You can index the XML column.

XML schema collection:

• Optionally XML schema collection can be associated with a column, or a variable which is of the xml data type.
• The XML schema collections are used to validate XML and the XML is said to be typed.
• If the XML is not associated with schema but well-formed then the XML is called untyped.
• SELECT * FROM sys.XML_Schema_collections returns the information about the registered schemas.

Retrieve XML data:

• In SQL Server 2005, you can store XML data in rowset or XML column.
• You can execute SQL queries to return results as XML instead of standard rowsets.
• The FOR XML clause converts the result sets from a query into an XML structure, and it provides different modes of formatting:

           *  FOR XML RAW
               Returns XML with each row representing an XML element.
               Each column value is mapped to an attribute with the same name as column name.

           *  FOR XML AUTO
               Returns Query results as nested XML elements.

           *  FOR XML PATH
               Returns specific values by indicating the column names for which you need to retrive the data.

           *  FOR XML EXPLICIT
               Returns XML that have format as given in the SELECT statement.

Methods:

The following are the available methods to query and manipulate XML data.

Method	- Description
Value	- Retrives a single value.
Nodes	- Shreds an XMl column into relational data.
Query	- Query an XML column.
Modify	- Specifies XML data manipulation statements.
Exist	- Checks if XML query returns data.

---

Questions:

1. Which of the following are well formed XML? Choose all that apply.
(a) <Question/>
(b) <Question>
(c) <Question>NULL</question>
(d) 'Question'


2. If an XML data is asociated with Schema collections then it is said to be typed.
(a) True
(b) False


3. If you execute the following query what will be your output. Choose any one answer.


USE AdventureWorks;
GO
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID=122 or ProductModelID=119
FOR XML AUTO, ELEMENTS;
GO

(a)
<row ProductModelID="122" Name="All-Purpose Bike Stand" />
<row ProductModelID="119" Name="Bike Wash" />

(b)
<row>
<ProductModelID>122</ProductModelID>
<Name>All-Purpose Bike Stand</Name>
</row>
<row>
<ProductModelID>119</ProductModelID>
<Name>Bike Wash</Name>
</row>

(c)
<ProductModel>
<ProductModelID>122</ProductModelID>
<Name>All-Purpose Bike Stand</Name>
</ProductModel>
<ProductModel>
<ProductModelID>119</ProductModelID>
<Name>Bike Wash</Name>
</ProductModel>

(d)
<ProductModel ProductModelID="122" Name="All-Purpose Bike Stand"/>
<ProductModel ProductModelID="119" Name="Bike Wash"/>


Show Answers:

1. (a), (d)
2. (a)
3. (c)

Hide Answers (X).

Simple Questions on Transactions

A transaction is a group of statements that are combined into a single logical unit. All statements succeed as a whole, or if any one of the statement fails, they all fail as a whole.

BEGIN TRAN --&gt; Marks the starting point of a transaction.
SAVE TRAN Save_point --&gt; Sets a new save point with in a transaction.
COMMIT TRAN --&gt; Marks the end of a transaction and makes the changes permanent.
ROLLBACK [TRAN] [Save_point] --&gt; Rolls back a transaction to the starting point or to the specific save point.



1. The following script if executed, what will be the output if you have only 200 Rupees?

IF Cash &gt; 200
   'Buy Fruits'
   'Buy Snacks'

(a) Buy Fruits, Buy Snacks.
(b) Buy Snacks.
(c) None of the statements will execute.


2. The following script if executed, what will be the output you have only 200 Rupees?

IF Cash &gt; 200
    BEGIN
        'Buy Fruits'
        'Buy Snacks'
    END

(a) Buy Fruits, Buy Snacks.
(b) Buy Snacks.
(c) None of the statements will execute.


3. Which one of the following function returns the open transaction count?

(a) @@ERROR
(b) @@FETCH_STATUS
(c) @@TRANCOUNT
(d) @@ROWCOUNT


4. Consider using nested transactions, if any one transaction rollbacks what will happen to other transactions with in the nesting?

(a) All other transactions will be committed except the one which rollback.
(b) All the transactions will be rollback regardless of the nesting level.
(c) None of the transactions will execute.
(d) Creates open transactions.


5. An open transaction prevents others from viewing the data that is modified, which command can be used to make the modifications permanent?

(a) UPDATE TRAN
(b) DELETE TRAN
(c) SELECT TRAN
(d) COMMIT TRAN


6. When using a TRY…CATCH block, which function helps to find out the description of the error?

(a) ERROR_SEVERITY
(b) ERROR_STATE
(c) ERROR_MESSAGE
(d) ERROR_NUMBER


Show Answers:

1. (b)
2. (c)
3. (c)
4. (b)
5. (d)
6. (c)

Hide Answers (X).

Simple questions about system databases

1. Identify system databases from the following:
a. Resources
b. AdventureWorks
c. Master
d. RTSDev
e. Temp
f. Model
g. Msdb
h. HRMS

2. Which one of the following databases used by SQL Server Agent
a. Master
b. Temp
c. Model
d. MSDB
e. Resource

3. Which database records all server specific configuration information including users, Remote servers, databases, logon accounts and end points.
a. Master
b. Temp
c. Model
d. MSDB
e. Resource

4. Which one of the following databases is recreated every time the SQL Server Starts?
a. Master
b. Temp
c. Model
d. MSDB
e. Resource

5. Which one of the following databases, if unavailable, the SQL server database engine will not be started.
a. Master
b. Temp
c. Model
d. MSDB
e. Resource

6. Which one of the following databases contains the initialization information of the SQL Server?
a. Master
b. Temp
c. Model
d. MSDB
e. Resource

7. Which one of the following databases that holds all temporary tables and SPs
a. Master
b. Temp
c. Model
d. MSDB
e. Resource

8. Which one of the following databases contains the path(location) of all other databases?
a. Master
b. Temp
c. Model
d. MSDB
e. Resource

9. Which one of the following databases act as a template or prototype for the new databases
a. Master
b. Temp
c. Model
d. MSDB
e. Resource

10. If you want a particular database object contained in all new databases created, you need to add the object to a system database. Choose one.
a. Master
b. Temp
c. Model
d. MSDB
e. Resource

11. Which one of the following databases automatically used by the server to resolve large or nested queries or to sorting the data
a. Master
b. Temp
c. Model
d. MSDB
e. Resource

12. Which one of the following databases that supports activities including Task scheduling, Alerts, Exception handling etc.
a. Master
b. Temp
c. Model
d. MSDB
e. Resource

13. Which one of the following databases is read only db and does not contain user data
a. Master
b. Temp
c. Model
d. MSDB
e. Resource

Show Answers:

1. a, c, e, f, g
2. d
3. a
4. b
5. a
6. a
7. b
8. a
9. c
10. c
11. b
12. d
13. e


Hide Answers (X).

Reference: SQL Server 2005 Books Online (System Databases)