SQL Tricky Questions

1) How to insert into a table with just one IDENTITY column?

INSERT INTO tbl_ID DEFAULT VALUES;

2) How to concatenate multiple rows into a single text string without using loop?

Suppose we have a table with the following data:

ClientID    ClientName

3           Sowrabh Malhotra

4           Saji Mon

6           Sajith Kumar

7           Vipin Job

8           Monoj Kumar

We need to concatenate the ClientName column like the following:

Sowrabh Malhotra, Saji Mon, Sajith Kumar, Vipin Job, Monoj Kumar

Answer

Method 1

SELECT ClientName + ', '

From ClientMaster

For XML PATH('')

Method 2

DECLARE @ClientNames VARCHAR(MAX);

SET @ClientNames = '';

SELECT @ClientNames = @ClientNames + IsNull(ClientName + ', ', '')

FROM ClientMaster

Select @ClientNames

select * from test

SELECT channel_cd + ', '

From test

For XML PATH('')

DECLARE @Ch_code VARCHAR(MAX);

SET @Ch_code = '';

SELECT @Ch_code = @Ch_code + IsNull(channel_cd + ', ', '')

FROM test

Select @Ch_code

3) How to use ORDER BY in VIEWS

Create View vClientMaster

As

Select TOP 100 PERCENT ClientID, ClientName FROM ClientMaster Order BY ClientID  DESC

But in the above example, the SQL server will not consider the [TOP 100

PERCENT] statement when executing the query. So we will not get the result in the order described in the view. But if we specify any number less than 100 PERCENT, SQL server will sort the result.

Note: It is not advisable to use ORDER BY in VIEWS. Use order by outside the view like the following:

Select ClientID, ClientName FROM vClientMaster Order BY ClientID  DESC

4) How to create a UNIQUE Key on a Column which is having multiple NULL values

In SQL Server, Unique Key Constraint will allow only one NULL value. But there are situations that are more than one null value in the column, but still have to maintain Uniqueness by ignoring all null values.

Answer

Method 1

Only works on SQL Server 2008 and above

Using Filter Index. Filtered index is used to Index a portion of rows in a table. While creating an index, we can specify conditional statements. The below SQL Query will create a Unique Index on the rows having non nullvalues:

CREATE UNIQUE INDEX IX_ClientMaster_ClientCode ON ClientMaster(ClientCode)

WHERE ClientCode IS NOT NULL

Method 2

Create a view having the unique fields and create a Unique Clustered Index on it:

Create View vClientMaster_forIndex

With SchemaBinding

As

Select ClientCode From dbo.ClientMaster Where ClientCode IS NOT NULL;

Go

CREATE Unique Clustered Index UK_vClientMaster_ForIndex

on vClientMaster_forIndex(ClientCode)

Method 3

Create a Computed Column like the following and create a UNIQUE KEY on that:

 [CMP_ClientCode]  AS (case when [ClientCode] IS NULL _

then CONVERT([varchar](10),[ClientID],0) else [ClientCode] end)

4) What is the default size of SQL Server Database

Answer

The default size of user database is based on system database model. When a user creates a new database, SQL server will take the copy of system model database and add user specified settings on it and create new database.

Some imp queries and its output :

select $  output : 0.00

select count(*) output: 1

select count(7) output :1

select 'vikas' + 1 output: Conversion failed when converting the varchar value 'vikas' to data type int

select 'vikas' + '1' output: vikas1

select (select 'vikas') output : vikas

select select 'vikas' output: Incorrect syntax near the keyword 'select'

select * from 'test' output : error

select * from test,test1 ouput : done the cross join

select count(*)+count(*) output :2

select 'vikas' from test output : vikas will repeated till no. of records

select max(1+2*3)  output: 7

select max(1,3,4) output : Error

select Count(select id from test) output : Error

select 1+'2' output :3

select '1'+2 output :3

select '1'+'2' output :12

select 1 +'c' output :Error

select 'c'+1 output :Error

select vikas output :Error

select count(*),(select count(*)) --output : two column 1,1

A column has some negative values and some positive values. It is required to find the sum of negative numbers and the sum of the positive numbers in two separate columns.

create table neg_pos(num number);

insert into neg_pos values(-1);

insert into neg_pos values(-2);

insert into neg_pos values(-3);

insert into neg_pos values(-4);

insert into neg_pos values(1);

insert into neg_pos values(2);

insert into neg_pos values(3);

insert into neg_pos values(4);

commit;

select * from neg_pos ;

num

-1

-2

-3

-4

1

2

3

4

Ans:

SELECT

SUM(CASE WHEN num < 0 THEN num ELSE 0 END) neg,

SUM(CASE WHEN num > 0 THEN num ELSE 0 END)pos

FROM neg_pos;

-- update 1,2,3 to 'a' and 4,5,6 to 'b'

id    name

1     a

2     a

3     a

4     b

5     b

6     b

update abc1 set name=case when id in(1,2,3) then 'b' else 'a' end

id	description
1	A
2	B
3	C

Item                                                                                                      Item1

id	description
1	A
2	B
3	C
NULL	D

select * from #item1  where id not in (select id from #item) – Output: No records

select max('AB') -- Output: AB

select count(*)  -- Output: 1

select $  -- Output: 0.00

Select 'NULL'+'1'-- Output:NULL1

select 'NULL'+1  -- Output:throw error

select 1+'2' -- Output:3

select '1'+2 -- Output: 3

select 1+'AB' --Output: throw error

select 'AB'+1 --Output: throw error

select 'AB'+'1' --Output: AB1

declare @a int

set @a=''

select @a  --Output: 0