Explore topic-wise InterviewSolutions in .

A simple example of Pivot table in SQL Server(Column into Row)?
Below is the example to CREATE a OUTPUT in form of Pivot Table or column into row. First i have create a below table.




And i need a output like below:-





Now below is the QUERY to get the output as above by using pivot table.

Write a SQL in built function which will RETURN the COUNT of active transactions?
Below is the SYNTAX which will return the NUMBER of active transactions.
select TRANCOUNT

How to set QUERY to apply paging in SQL Query to get records?
We can use SQL Query which helps us to apply paging without WORRY about the frontend setting. Below is the query which helps you to apply paging



In above query we use "OFFSET 10 ROWS" to START records from 10 ROW. And "FETCH NEXT 10 ROWS ONLY" will return 10 next records. We can increase "OFFSET 10 Rows" to jump to next 10 records.

We have define NCHAR(3) and CHAR(3) what is difference?
We have created a Table and define two column CountryName and CountryName1 in it.


But both different DATA types one have nchar(3) and another have char(3). Below is the difference

SQL Query to disable or UNABLE foreign KEY constraints temporarily by using TSQL?
There are certain conditions when we disable or unable foreign key or any other constraints on any table. After doing our operations just ROLLBACK the contraints on any of the SQL table:-

(1)Query to disable all table constraints

ALTER TABLE table_name NOCHECK CONSTRAINT ALL


(2)Query to enable all table constraints

ALTER TABLE table_name WITH CHECK CHECK CONSTRAINT ALL


(3)Query to disable single constraint

ALTER TABLE table_name NOCHECK CONSTRAINT constraint_name


(4)Query to enable single constraint

ALTER TABLE table_name WITH CHECK CHECK CONSTRAINT constraint_name

Get last 4 CHARCTERS of string in SQL SERVER query?
Below are the two sql query to get the last 4 CHARACTERS from string.
(1)SUBSTR(column, LENGTH(column) - 3, 4)
(2)select RIGHT('abcdefghijkl',4)

Different SQL QUERY to get details of every column in table with details?
Below are the 3 MAIN query to find all details of every column of table
(1)SELECT * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='tbl_name'

(2)SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('dbo.tbl_name')

(3)SELECT o.Name, C.Name FROM sys.columns c
JOIN sys.objects o ON o.object_id = c.object_id
WHERE o.type = 'U'
ORDER BY o.Name, c.Name

Query to repeat ROWS N TIMES as PER the COLUMN Value in SQL Server?
To Repeat Rows n times ACCORDING to column value we will write some query. To understand this first we will create a table as per below given.



Output of above query is given below:-



Now we will write below query to get the row repeated.



Now the final output as per the row repeated.

What ACTION plan you create when SQL Server is not responding?
Once SQL Server is not responding we will create below action to get the reason:-
(1)In First we will think about the connection. To do this we will CONNECT using DAC via CMD or by SSMS.
(2)Once we decided to connect via CMD. We will run below query to enter.
(3)SQLCMD -A U adminlogin P password -SMyServer dmaster
(4)Once we connect with master after above command.
(5)We will now run the diagnostic queries to FIND the problem.
(6)Now will get the error reason CORRECT the issue and after that restart the server.

How to retrieve UNSAVED SQL query script or files?
There are MANY situation when SQL server MANAGEMENT Studio or some query window accidently closed before saving script. To RETRIVE unsaved SQL query script we use below query:-

USE Database_Name
SELECT execquery.last_execution_time AS [Date Time],execsql.TEXT AS [Script] FROM sys.dm_exec_query_stats AS execquery
CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
ORDER BY execquery.last_execution_time DESC

Can store procedure be recursive if YES then its level?
A stored procedure can be recursive and referencing itself directly or INDIRECTLY. And the level of recursive in stored procedure is 32. Means store procedure can call itself up TP maximum 32 TIMES.

Write a query to GET all even and odd number if we have some id COLUMN?
Below is the query to get all even number of RECORDS from SQL table.

SELECT * FROM TABLE_NAME WHERE ID % 2 = 0

And BELOS is the query to get all odd number of records from table.

SELECT * FROM TABLE_NAME WHERE ID % 2 != 0

How to USE xp_cmdshell in SQL?
We use xp_cmdshell in SQL to run DOS command. But before use of this we need to enable xp_cmdshell by below commands. And to unable this we use below command to check it is enable or not.

use master
GO
Exec sp_configure 'show ADVANCED option'

By just using above command we get output like below.



No we need to enable this config_value to 1 and to do that we use below command

USE master;
GO
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE WITH OVERRIDE;

Once above command will run we need to enable xp_cmdshell command too by below syntax


EXEC sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE;


Now we are able to run xp_cmdshell command below is ONE of the example of of one xp_cmdshell command.

xp_cmdshell 'copy c: backup c: otherfoldername;

Can we EXECUTE DOS COMMAND from SQL.
YES we can execute DOS command by using xp_cmdshell. And to execute this we USE below command or syntax to execute this.
exec xp_cmdshell "c: *.exe"

How to change schema NAME of the Table in SQL Like dbo to crackuser?
TO change schema name of the table we need only alter command as given below

ALTER SCHEMA crackuser TRANSFER dbo.table_name

There are some situation where we are not ABLE to find the schema name in SQL. SO to creat that we use below query to CREATE schema.


IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'crackuser'))
BEGIN
EXEC ('CREATE SCHEMA [crackuser] AUTHORIZATION [dbo]')
END

Use of try catch and in store PROCEDURE and display error too in details?
We can use try catch in SQL query and store proecedure which will product the exact SYSTEM generated error and there details which WORKS as a LOGS for our APPLICATIONS.

Can you differentiate between LEN() and DATALENGTH() in SQL?
LEN():- LEN() FUNCTION will RETURNS the length in characters which will EXCLUDE trailing spaces.
DATALENGTH():- DATALENGTH() function will only returns length of string in bytes and which will include trailing spaces below example will clarify you the details.

Write down a SQL QUERY to CONVERT FIRST LETTER in UPPER case and Rest in lower case in Name?
To write down this query i have created a #temptable and then write query to convert firstname in proper case

Write down a SQL QUERY to find the missing number in AUTO increment column?
There are certain SITUATION when some number are missed in auto increment column to GET that number we use below query:-


select l.id + 1 as start
from tblname as l
left outer join tblname as r on l.id + 1 = r.id
where r.id is null;

How to insert missing SQL Server IDENTITY column values?
There are certain condition when some values are missing in any identity column. As per below given image one VALUE in ID primary column is missing.



But if we are TRYING insert some missing value we will get below error we will get some identity error.





But to over come this issue we need to run some query or COMMANDS to insert value in missing place.

Write down a Dynamic SQL Query with syntax in SQL?
Below is the query which is dynamic here in query we fetch some dynamic RECORD from different VARIABLES and concat them to one string.

DECLARE SQLCOMMAND varchar(1000)
DECLARE columnList varchar(75)
DECLARE city varchar(75)

Set columnList = 'FirstColumn1,SecondColumn,ThirdColumn'
Set whereValue = '''Value'''
Set sqlCommand = 'SELECT ' + columnList + ' FROM tableName where Columnvalue= ' + whereValue

EXEC(sqlCommand)

Define different FUNCTION to GET the current USERS DETAILS?
Below are the different function to get current user detals with in-build function as given below:-
USER_ID(),USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID(), HOST_NAME()

And syntax is given below

select USER_ID(),USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID(), HOST_NAME()

How to GET the table count in LAST used query from CACHE by SENDING tablename?
Below is the query to get the number of time query execute from cache and we need to PUT the table name.

SELECT dest.text
, deqs.execution_count
FROM sys.dm_exec_query_stats deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) dest
WHERE dest.text LIKE '%table_name%'
GO

Sql Interview Latest Query questions asked on 30 dec 2020
Interviewer has asked below five query and ASK for there OUTPUT from the CANDIDATE
(1)Find the output of below query
select count(*)

(2)Find the output of below query
select Null+10

(3)Find the output of below query
if(null=null)
print 'True'
else
print 'False'

(4)Find the output of below query
Select case when 1=1 then 'crackyourinterview'
when 2=2 then 'dotnet'
else 'javaquestions' end as TestResult


(5)Find the output of below query


Answer of above query
(1)1
(2)null
(3)False
(4)crackyourinterview
(5)?

Hi PUT NUMBER in the place of nth

SELECT TOP 1 number
FROM (SELECT DISTINCT TOP 'nth' number
FROM EMPLOYEE ORDER BY number DESC) a
ORDER BY number ASC

Remove LAST or first character from STRING in SQL Query?
Below is the CODE to remove first or last chaacter from string in SQL Query



(1)SQL remove first character
(i)By using Right keyword
Declare EmpName as varchar(20)='Crackyourinterview.com'
Select right(EmpName,len(EmpName)-1) as FirstCharacterRemove
Output:-
rackyourinterview.com

(ii)By using substring keyword
Declare EmpName as varchar(20)='Crackyourinterview.com'
Select substring(EmpName,2,len(EmpName)-1) as FirstCharacterRemove
Output:-
rackyourinterview.com

(2)SQL remove last Character
(i)by using Left keyword
Declare EmpName as varchar(20)='Crackyourinterview.com'
Select left(EmpName,len(EmpName)-1) as LastCharacterRemove
Output:-
Crackyourinterview.co

(ii)By using substring keyword
Declare EmpName as varchar(20)='Crackyourinterview.com'
Select substring(EmpName,1,len(EmpName)-1) as FirstCharacterRemove
Output:-
Crackyourinterview.co

Can we blocks execution of batch, store PROCEDURE or transaction in SQL for certain time?
Yes we can do that and to do that we use WAITFOR keyword for this. We can do this for sepcified time or time interval. Below is the syntax and one EXAMPLE for that.




Query Example 1:-
(1)Below will run query after 5 seconds
BEGIN
WAITFOR DELAY '00:00:05';
select top 10 * from tbl_name
END;

(2)Below will run on certain time(This will run on 1PM in after noon)
BEGIN
WAITFOR TIME '13:00:00';
select top 10 * from tbladsadded
END;

Please take care of ERROR time elapased and check the SESSION for time out error