SQL – Contest to Get The Date – Win USD 50 Amazon Gift Cards and Cool Gift

If you are a regular reader of this blog – you will find no issue at all in resolving this puzzle. This contest is based on my experience with NuoDB.

If you are not familiar with NuoDB, here are few pointers for you.

In today’s contest you have to answer following questions:

Q 1: Precision of NOW()

What is the precision of the NuoDB’s NOW() function, which returns current date time?

Hint: Run following script on NuoDB Console Explorer section:

SELECT NOW() AS CurrentTime
FROM dual;

Here is the image. I have masked the area where the time precision is displayed.

Q 2: Executing Date and Time Script

When I execute following script -

SELECT 'today' AS Today, 'tomorrow' AS Tomorrow, 'yesterday' AS Yesterday
FROM dual;

I will get the following result:

 

NOW – What will be the answer when we execute following script? and WHY?

SELECT CAST('today' AS DATE) AS Today, 
 CAST('tomorrow' AS DATE) AS Tomorrow, 
 CAST('yesterday'AS DATE) AS Yesterday
FROM dual;

HINT: Install NuoDB (it takes 90 seconds).

Prizes:

  • 2 Amazon Gifts
  • 2 Limited Edition Hoodies (US resident only)

 

Rules:

  • Please leave an answer in the comments section below.
  • You must answer both the questions together in a single comment.
  • US resident who wants to qualify to win NuoDB apparel please mention your country in the comment.
  • You can resubmit your answer multiple times, the latest entry will be considered valid.
  • Last day to participate in the puzzle is June 24, 2013.
  • All valid answer will be kept hidden till June 24, 2013.
  • The winner will be announced on June 25, 2013.
  • Two Winners will get USD 25 worth Amazon Gift Card. (Total Value = 25 x 2 = 50 USD)
  • The winner will be selected using a random algorithm from all the valid answers.
  • Anybody with a valid email address can take part in the contest.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Puzzle #1 – Querying Pattern Ranges and Wild Cards

Note: Read at the end of the blog post how you can get five Joes 2 Pros Book #1 and a surprise gift.

I have been blogging for almost 7 years and every other day I receive questions about Querying Pattern Ranges. The most common way to solve the problem is to use Wild Cards. However, not everyone knows how to use wild card properly.

SQL Queries 2012 Joes 2 Pros Volume 1 – The SQL Queries 2012 Hands-On Tutorial for Beginners Book On Amazon | Book On Flipkart

Learn SQL Server get all the five parts combo kit Kit on Amazon | Kit on Flipkart

Many people know wildcards are great for finding patterns in character data. There are also some special sequences with wildcards that can give you even more power. This series from SQL Queries 2012 Joes 2 Pros® Volume 1 will show you some of these cool tricks.

All supporting files are available with a free download from the www.Joes2Pros.com web site. This example is from the SQL 2012 series Volume 1 in the file SQLQueries2012Vol1Chapter2.2Setup.sql. If you need help setting up then look in the “Free Videos” section on Joes2Pros under “Getting Started” called “How to install your labs

Querying Pattern Ranges

The % wildcard character represents any number of characters of any length. Let’s find all first names that end in the letter ‘A’. By using the percentage ‘%’ sign with the letter ‘A’, we achieve this goal using the code sample below:

SELECT *
FROM Employee
WHERE FirstName LIKE '%A'

To find all FirstName values beginning with the letters ‘A’ or ‘B’ we can use two predicates in our WHERE clause, by separating them with the OR statement.

Finding names beginning with an ‘A’ or ‘B’ is easy and this works fine until we want a larger range of letters as in the example below for ‘A’ thru ‘K’:

SELECT *
FROM Employee
WHERE FirstName LIKE 'A%'
OR FirstName LIKE 'B%'
OR FirstName LIKE 'C%'
OR FirstName LIKE 'D%'
OR FirstName LIKE 'E%'
OR FirstName LIKE 'F%'
OR FirstName LIKE 'G%'
OR FirstName LIKE 'H%'
OR FirstName LIKE 'I%'
OR FirstName LIKE 'J%'
OR FirstName LIKE 'K%'

The previous query does find FirstName values beginning with the letters ‘A’ thru ‘K’. However, when a query requires a large range of letters, the LIKE operator has an even better option. Since the first letter of the FirstName field can be ‘A’, ‘B’, ‘C’, ‘D’, ‘E’, ‘F’, ‘G’, ‘H’, ‘I’, ‘J’ or ‘K’, simply list all these choices inside a set of square brackets followed by the ‘%’ wildcard, as in the example below:

SELECT *
FROM Employee
WHERE FirstName LIKE '[ABCDEFGHIJK]%'

A more elegant example of this technique recognizes that all these letters are in a continuous range, so we really only need to list the first and last letter of the range inside the square brackets, followed by the ‘%’ wildcard allowing for any number of characters after the first letter in the range.

Note: A predicate that uses a range will not work with the ‘=’ operator (equals sign). It will neither raise an error, nor produce a result set.

--Bad query (will not error or return any records)
 SELECT *
 FROM Employee
 WHERE FirstName = '[A-K]%'

Question: You want to find all first names that start with the letters A-M in your Customer table and end with the letter Z. Which SQL code would you use?

a. SELECT * FROM Customer
WHERE FirstName LIKE 'm%z'

b. SELECT * FROM Customer
WHERE FirstName LIKE 'a-m%z'

c. SELECT * FROM Customer
WHERE FirstName LIKE 'a-m%z'

d. SELECT * FROM Customer
WHERE FirstName LIKE '[a-m]%z'

e. SELECT * FROM Customer
WHERE FirstName LIKE '[a-m]z%'

f. SELECT * FROM Customer
WHERE FirstName LIKE '[a-m]%z'

g. SELECT * FROM Customer
WHERE FirstName LIKE '[a-m]z%'

Contest

  • Leave a valid answer before June 18, 2013 in the comment section.
  • 5 winners will be selected from all the valid answers and will receive Joes 2 Pros Book #1.
  • 1 Lucky person will get a surprise gift from Joes 2 Pros.
  • The contest is open for all the countries where Amazon ships the book (USA, UK, Canada, India and many others).

Special Note: Read all the options before you provide valid answer as there is a small trick hidden in answers.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER- Solution – SQL Puzzle of SET ANSI_NULL

Earlier I have posted a puzzle which received so many valid responses and got a fantastic explanation to the questions as well. I encourage all of you to read the original puzzle here.

First run following script:

SET ANSI_NULLS ON;
-- Query1
SELECT 'SQLAuthority' AS Statement11
WHERE 'Authority' IN ('S','Q', 'L', 'Authority', NULL);
-- Query 2
SELECT 'SQLAuthority' AS Statement12
WHERE 'Authority' NOT IN ('S','Q', 'L', NULL);

You will get following result:

You can clearly see that in the first case we are getting different results.

Question: Why do Query 1 return results but Query 2 does not return any result?

The answer is very simple and easy. Let us see the answer

Solution:

In case of the Query1
SELECT ‘SQLAuthority’ AS Statement11
WHERE ‘Authority’ IN (‘S’,’Q’, ‘L’, ‘Authority’, NULL);

Explanation:
IN Operator is equivalent to
Condition = ‘S’ OR Condition = ‘Q’ OR Condition = ‘L’ OR Condition = ‘Authority’ OR Condition = NULL

Above query represents in our case as

‘Authority’ = ‘S’ OR ‘Authority’ = ‘Q’ OR ‘Authority’ = ‘L’ OR ‘Authority’ = ‘Authority’ OR ‘Authority’ = NULL

Returns FALSE OR FALSE OR FALSE OR TRUE OR FALSE so the final result is TRUE.

In this case if ANY of the above condition returns true it the query will return value ‘SQLAuthority’

In case of the Query2
SELECT ‘SQLAuthority’ AS Statement12
WHERE ‘Authority’ NOT IN (‘S’,’Q’, ‘L’, NULL);

Explanation:
NOT IN Operator is equivalent to
Condition != ‘S’ AND Condition != ‘Q’ AND Condition != ‘L’ AND Condition != NULL

Above query represents in our case as

‘Authority’ != ‘S’ AND ‘Authority’ != ‘Q’ AND ‘Authority’ != ‘L’ AND ‘Authority’ != NULL

Returns TRUE AND TRUE AND TRUE AND FALSE so the final result is FALSE.

In this case if ALL of the above condition returns true it the query will return value ‘SQLAuthority’

I guess this solves the puzzle. However do not forget to note that in my query I have ANSI_NULL ON. When ANSI_NULLS is ON, any comparison operation with one of the operands is NULL will evaluate to UNKNOWN. For the result to include a record, all the operands for that record should evaluate to TRUE. We can absolutely get different results if we have ANSI_NULLS OFF.

We will continue the discussion in future blog posts. Additionally, the winners of the contests will be announced in the original blog posts.

Click to Download Scripts

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Solution to Puzzle – REPLICATE over 8000 Characters

Earlier this week, I asked a puzzle about how REPLICATE works with 8000 and over 8000 characters. I strongly suggest to read the original blog post where I have described the problem in detail SQL SERVER Puzzle – REPLICATE over 8000 Characters.

Just quick to summarize the puzzle. Here is the quick recap of the same.

Now let us run following script.

DECLARE @FirstString VARCHAR(MAX)
DECLARE @SecondString VARCHAR(MAX)
DECLARE @ThirdString VARCHAR(MAX)
SET @FirstString = REPLICATE('A',4000)
SELECT LEN(@FirstString) LenFirstString;
SET @SecondString = REPLICATE('B',8000)
SELECT LEN(@SecondString) LenSecondString;
SET @ThirdString = REPLICATE('C',11000)
SELECT LEN(@ThirdString) LenThirdString;

The script above will return following result:

Quiz 1:

Pay attention to the last resultant and you will notice that the length of the @ThirdString is only 8000. WHY?

Answer 1:

The reason for the same is that if the first parameter of the REPLICATE function is not of type varchar(max) or nvarchar(max), REPLICATE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, the first parameter must be explicitly cast to the appropriate varchar(max) or nvarchar(max).

Quiz 2:

What changes I should do in the REPLICATE function so it will give the appropriate result in the case of @ThirdString. In our case, it should return the value of 11000 and not 8000.

Answer 2:

To return the result as 11000, one has to just CAST or CONVERT the first parameters to VARCHAR(MAX) or NVARCHAR(MAX). Here is the example of the same.

DECLARE @ThirdString VARCHAR(MAX)
SET @ThirdString = REPLICATE(CONVERT(VARCHAR(MAX),'C'),11000)
SELECT LEN(@ThirdString) LenThirdString;

Now let us see the result set.

DB Optimizer

One last thing: Do not forget to download DB Optimizer XE3.5 Pro. It is my favorite tool for performance tuning.

If you notice that this was not very difficult puzzle but it was interesting for sure. There are so many valid answers that it will be not possible to name every single person. I strongly encourage all of you to go over the original blog post and read all the comments. Though all the comments are very similar there are so many new information there that I will say wealth of information just right there in the comments area.

Click to Download Scripts

Reference:  Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – SQL Puzzle of SET ANSI_NULL – Win USD 50 worth Amazon Gift Cards and Bubble Copter R/C

We all love puzzles and here is interesting puzzle which you can play with me and win Amazon Gift Cards and Bubble Copter R/C. The contest for Amazon Gift Card is open worldwide, however, Bubble Copter winner will be chosen from USA only.

First run following script:

SET ANSI_NULLS ON;
-- Query1
SELECT 'SQLAuthority' AS Statement11
WHERE 'Authority' IN ('S','Q', 'L', 'Authority', NULL);
-- Query 2
SELECT 'SQLAuthority' AS Statement12
WHERE 'Authority' NOT IN ('S','Q', 'L', NULL);

You will get following result:

You can clearly see that in the first case we are getting different results. Here are the questions you need to answer to win the Amazon Gift Cards and Bubble Copter R/C.

There are two steps to take participate in the contest:

Step 1: Answer the Question

Question: Why do Query 1 return results but Query 2 does not return any result?

Answer this question in comments area along with the question in Step2.

Step 2: Identify File Size

Question: What is the size of the DevArt Schema Compare installation file in KB? Please leave a note in the comment area.

Please note the size of the file should be KB and not in MB. You can download the file from here.

Giveaway:

  • 2 lucky winners will get USD 25 worth Amazon Gift Card (Open worldwide, Total giveaway value USD 50)
  • One lucky winner from USA will get  Bubble Copter R/C (Shipping from USA to other countries is not possible)

Rules and Conditions:

  • Contest open till May 25 12:00 GMT. 
  • Please leave your answer in the comment area in following format:
    • Answer to Q1:
    • Answer to Q2:
  • Please note that winner will be selected after May 25th by random selection and will be posted as a comment to this blog.
  • The answers will be kept hidden till the winner will be announced to have fair competition.

Click to Download Scripts

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – How to use xp_sscanf in Real World Scenario?

I need your help. I recently came across extended stored procedure xp_sscanf. After reading a lot about it and searching online, I could not figure out how and where in real world, I will use this function.

Microsoft documentations suggest that this extended stored procedure reads data from the string into the argument locations specified by each format argument. I still do not get it. I know it is very similar to C function but again I am not sure where in the real world I will use this function. Here is the demonstration of how this function works.

Following example is an enhanced version of the example provided on MSDN.

DECLARE @valueb VARCHAR(20), @filename VARCHAR(20), @message VARCHAR(20)
EXEC xp_sscanf 'sync -b123 -fauthors10.tmp -rrandom', 'sync -b%s -f%s -r%s',
@valueb OUTPUT, @filename OUTPUT, @message OUTPUT
SELECT @valueb, @filename, @message

The above query will return us following result:

-------------------- -------------------- --------------------
123                  authors10.tmp        random

Here is the result set.

You can see how xp_sscanf retrieves the parameters from the string and returns as an output. However, I am still not sure where I will use this feature in real world scenarios. Any insight and or guidance will be helpful.

Reference:  Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Puzzle and Answer – REPLICATE over 8000 Characters

It has been a long time since we have played a puzzle over this blog. This Monday, let us play a quick puzzle.

SQL Server have REPLICATE function which will replicate the string passed as many as times as the second parameter. For example execute following string.

SELECT 'Ha'+REPLICATE('ha',20)

The script above will return following result:

`

You can notice that it has returned a string ha about 20 times after first Ha.

Now let us run following script.

DECLARE @FirstString VARCHAR(MAX)
DECLARE @SecondString VARCHAR(MAX)
DECLARE @ThirdString VARCHAR(MAX)
SET @FirstString = REPLICATE('A',4000)
SELECT LEN(@FirstString) LenFirstString;
SET @SecondString = REPLICATE('B',8000)
SELECT LEN(@SecondString) LenSecondString;
SET @ThirdString = REPLICATE('C',11000)
SELECT LEN(@ThirdString) LenThirdString;

The script above will return following result:

Quiz 1:

Pay attention to the last resultant and you will notice that the length of the @ThirdString is only 8000. WHY?

Here is the second part of the Quiz.

Quiz 2:

What changes I should do in the REPLICATE function so it will give the appropriate result in the case of @ThirdString. In our case, it should return the value of 11000 and not 8000.

DB Optimizer

One last thing: Do not forget to download DB Optimizer XE3.5 Pro. It is my favorite tool for performance tuning.

Please leave a comment with your answer. I will publish the answer to this puzzle on coming Friday’s blog post and will mention the names of the all the valid users.

Reference:  Pinal Dave (http://blog.SQLAuthority.com)