SQL Server Guide: SQL Challenge

Showing posts with label SQL Challenge. Show all posts

Learn T-SQL

SQL Basics? SQL Server Interview Questions And Answers

Create Database

Table

View

Stored Procedure

Function

Trigger

SQL TOP

SQL LIKE

SQL Wildcards

SQL IN

SQL BETWEEN

SQL Alias

SQL JOINs

SQL UNION

SQL UNION ALL

SQL SELECT INTO

SQL CONSTRAINTS

Data Definition Language (DDL)

Data Manipulation Language (DML)

SQL Server Data Types

SQL NULLs

SQL ISNULL

SQL NULLIF

SQL Date and Time Functions

T-SQL Control of Flow

CURSOR

T-SQL Operators

RAISERROR

Error Handling

SET Statements

Security Statements

BACKUP

RESTORE

TRANSACTIONS

SQL Server Interview Questions And Answers

How to Create Database using T-SQL command?

Explain Candidate Key, Alternate Key, and Composite Key

How to create Primary Key on existing table?

How to create Foreign Key on existing table?

How to create FOREIGN KEY constraint by using WITH NOCHECK

How to add a DEFAULT constraint to an existing column?

How to add CHECK CONSTRAINTS on existing table?

What's the difference between a primary key and a unique key constraints?

Explain SQL Server JOINs with examples?

What is the difference between TRUNCATE and DELETE?

Write T-SQL query to calculate Month End Date

How to get Month Number from Month Name

Write T-SQL Query to find Nth largest Number

Maximum Capacity Specifications for Database Objects in SQL Server 2008 R2

What is View in SQL Server?

What is Stored Procedure?

What is User Defined Functions (UDF)

What is User-Defined Function (UDF) in SQL Server?

Types of User Defined Functions (UDF) in SQL Server?

Difference between Function and Stored Procedure?

What is the difference between a local and a global temporary tables?

What is a NOLOCK?

What is lock escalation?

What is Log Shipping in SQL Server?

What is WITH TIES clause in SQL Server?

COUNT Number of Records for all the Tables in a Database?

Function to Split Multi-valued String

How to connect to SQL Server using RUNAS command from command prompt?

Regular Expression Problem in T-SQL

Concatenating Row Values using Transact-SQL

Which TCP/IP port does SQL Server run on? How can it be changed?

What is uniqueidentifier in SQL Server?

What is NEWSEQUENTIALID?

What is Trace Flag 610?

Sleep Command in T-SQL?

What is the use UPDATE_STATISTICS command?

How to Granting Execute permission on All the Stored Procedure?

How to get SQL Server Restore history using T-SQL?

Function to Convert Decimal Number into Binary, Ternary, and Octal

How to find all the IDENTITY columns in a database?

What will be output of below T-SQL code:

CREATE TABLE MyTable
(
   MyId [INT] IDENTITY (1,1),
   MyCity [NVARCHAR](50)
)

BEGIN TRANSACTION OuterTran
INSERT INTO MyTable VALUES ('Boston')
BEGIN TRANSACTION InnerTran
    INSERT INTO MyTable VALUES ('London')
ROLLBACK WORK
    IF (@@TRANCOUNT = 0)
    BEGIN
      PRINT 'All transactions were rolled back'
    END
    ELSE
    BEGIN
      PRINT 'Outer transaction is rolling back...'
      ROLLBACK WORK
    END
    DROP TABLE MyTable

Here are the options:
1.   All transactions were rolled back
2.   Outer transaction is rolling back...
3.   ERROR: Incorrect syntax near 'WORK'.

Correct answer: All transactions were rolled back
Explanation: Issuing a ROLLBACK WORK rolls back all the way to the outer BEGIN TRANSACTION

T-SQL Challenge

What will be the output of below T-SQL code:

CREATE TABLE #TestDate
(
   [ID] int IDENTITY(1,1)
   ,[FullDate] datetime DEFAULT (GETDATE()),
)
GO
INSERT INTO #TestDate VALUES
('01/07/2010'),('2010/07/01'),('07/01/2010')
GO
SELECT COUNT([FullDate]) FROM #TestDate
WHERE CAST([FullDate] as int) = 40358
GO
DROP TABLE #TestDate
GO

Answer this without cheating (without executing the Query). Here are the options:

A.   1
B.   2
C.   3
D.   Error
F.   None of the above

Correct Answer is B.
Reason: Dates 07/01/2010 and 2010/07/01 are inserted as 2010-07-01 00:00:00.000 in the database which is equal to 40358 of int type. So when the conversion into int and then taking ceiling of the values, these 2 records generate the same values. However, 01/07/2010 is saved as 2010-01-07 00:00:00.000 in the database which is equal to 40183 of type int.

Regular Expression Problem in T-SQL

I have one SQL Challenge for you:

A table has one column Code. Here is the sample data.

DECLARE @T TABLE(Code varchar(20))

INSERT @T VALUES

('STQ-309-A65'),('XYZ-999-A65'),

('AZZ-345-B66'),('CzA-123-C671'),

('GUP-999-C67'),('STQ-123-c67'),

('AtT-456-B66'),('ATT-000-B66'),

('AWT-101-A65'),('AUV-111-d68'),

('stq-007-c67'),('att-123-A97'),

('stq-777-c99'),('byz-789-d100'),

('stq-111-250'),('1at-p2a-149')

You need to filter the codes based on below conditions:

1. Code can be only 11 or 12 CHAR long.

2. First char must be a - s or A - S

3. Second char must be t - z to T - Z

4. Third char can be any char a - z or A - Z but not a digit.

5. Digit 4th and 8th must be "-"

6. Char 5th, 6th, and 7th must be a digit.

7. Char 5th should be non-zero digit.

8. Char 8th can be a - z but not a digit

9. Position 9th and 10th must be ASCCI value of 8th CHAR. If ASCII code is of three digit then 9th, 10th, and 11th position should be occupy by ASCII code.

Try to get the solution before checking my solution:

SELECT * FROM @T

WHERE Code LIKE '[A-S][T-Z][A-Z][-][1-9][0-9][0-9][-][A-Z]'+CAST(ASCII(SUBSTRING(Code,9,1)) as varchar(3))

Function to Split Multi-valued String

Can you write a query to split a comma seperated value?
Can you create a function to spilt a delimitted string into multipled rows? Delimiter can be any char like comma (,), @, &, ; etc.
How to use a multi valued parameter in a Stored Procedure to filter report data? I am sure you can't use a multi valued parameter directly in T-SQL code without splitting multiple values.

To find the answer of above questions create a user defined function using below T-SQL code:

/**********************************************
CREATED BY HARI
PURPOSE : To split comma seperated values
--------------------------------------------
Use this function to split any multivalued string
seperated by any delimiter into multiple rows
***********************************************/
CREATE FUNCTION [dbo].[SplitMultivaluedString]
(
   @DelimittedString [varchar](max),
   @Delimiter [varchar](1)
)
RETURNS @Table Table (Value [varchar](100))
BEGIN
   DECLARE @sTemp [varchar](max)
   SET @sTemp = ISNULL(@DelimittedString,'') + @Delimiter
   WHILE LEN(@sTemp) > 0
   BEGIN
      INSERT INTO @Table
      SELECT SubString(@sTemp,1,CharIndex(@Delimiter,@sTemp)-1)

      SET @sTemp = RIGHT(@sTemp,LEN(@sTemp)-CharIndex(@Delimiter,@sTemp))
   END
   RETURN
END
GO

/* How to use this function:
SELECT * FROM [dbo].[SplitMultivaluedString] ('1,2,3,4', ',')
SELECT * FROM [dbo].[SplitMultivaluedString] ('1;2;3;4', ';')
*/

T-SQL Query to Calculate Month End Date

How to calculate Month End Date in one liner query?

Here is the easiest way to calculate Month End Date for any given date:

SELECT DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 1, 0)-1 AS MonthEndDate

Example:
If you replace GetDate() with any date, above query will return the Month End Date for that particular month.
If GetDate() value is '2010-01-25' then Output will be '2010-01-31'
If GetDate() value is '2010-02-20' then Output will be '2010-02-28'

T-SQL Query to Find Nth Largest number

How to find Nth Highest number using SQL query?

This is very simple to achieve by using Ranking Functions. Below is the answer of this query:

-- PREPARE TEST DATA
DECLARE @T TABLE (Amount int)
INSERT INTO @T VALUES
(101),(120),(14),(110),(930),(310),
(12),(104),(330),(423),(110),(10)

DECLARE @N int
SET @N = 5 -- SET Nth Number

-- ACTUAL QUERY
SELECT [Rank],Amount FROM (
SELECT ROW_NUMBER() OVER (ORDER BY Amount DESC) [Rank]
,Amount FROM @T) AS Temp
WHERE [Rank] = @N

Pages