These are all basics but when asked in interview we make mistake.


select * from #temp












select AVG(id) from #temp  [5/3 or 5/5]







select COUNT(*) from #temp [5 or 3]






select distinct(id) from #temp [1,2 or 1,2,null]

select * from EmployeeDetail

Id EmployeeName
1 Anurag Nayak
2  Ritika mohanty
3     Siba Dalai
4 Biswa Panda

Trick 1 ;with cte(id,name,finalIndex,startIndex)as
(
select id,
EmployeeName,
LEN(EmployeeName) as LengthName,
CHARINDEX(' ',EmployeeName) as StartIndex from dbo.EmployeeDetail
)
select id,name,SUBSTRING(name,startIndex+1,finalIndex) as final from cte 
order by final


id       name                               final
3 Siba Dalai                       Dalai
2 Ritika mohanty
1 Anurag Nayak                Nayak
4 Biswa Panda                   Panda



Trick 2

select EmployeeName
from employeedetail
order by REVERSE(substring(reverse(EmployeeName),1,CHARINDEX(' ',reverse(EmployeeName))))

select * from EmployeeSalary order by id














--o/p [Need employee id and the count that crossed more than 5000]
--employeeid and count(> 5000)
--1 1
--2 1
--3 2

select id,COUNT(EmployeeSalary) from EmployeeSalary
where EmployeeSalary > 5000
group by id





--Now i want those employees id who has more than 1 count [> 5000] salary.

select id,COUNT(EmployeeSalary) from EmployeeSalary
where EmployeeSalary > 5000
group by id
having COUNT(EmployeeSalary) >1








--> After Group by No Where clause we know that.

In NVARchar DataType column we can store both Single byte and Double byte data. Many a times we want to know how many records have Single byte or Double byte data. Let us understand this with an example.

--CREATE TABLE dbo.library
-- (libraryId int, username NVarchar(50))
--GO
--Populate Customer table with single byte and
--double byte CustomerName records
INSERT INTO dbo.library (libraryId, username)
VALUES (1, 'Anurag')

INSERT INTO dbo.library (libraryId, username)
VALUES (2, N'尊敬卿')

INSERT INTO dbo.library (libraryId, username)
VALUES (3, 'Siba')
GO


SELECT *
FROM dbo.library
WHERE username = CAST(username AS VARCHAR(50))








SELECT *
FROM dbo.library
WHERE username != CAST(username AS VARCHAR(50))






------------------------------------------------------------------------
Can we store double byte data type in varchar ?

Lets see
CREATE TABLE dbo.library1
 (library1Id int, username Varchar(50))
GO
--Populate Customer table with single byte and
--double byte CustomerName records
INSERT INTO dbo.library1 (library1Id, username)
VALUES (1, 'Anurag')

INSERT INTO dbo.library1 (library1Id, username)
VALUES (2, N'尊敬卿')

INSERT INTO dbo.library1 (library1Id, username)
VALUES (3, 'Siba')
GO


SELECT *
FROM dbo.library1

alter proc usp_find_word
as
begin

select 'anuragggggggggggg'
declare @varrrrrrr varchar(50)

end

-------------------------

SELECT OBJECT_NAME(object_id), OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%anuragggggggggggg%'

SELECT OBJECT_NAME(object_id), OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%@varrrrrrr%'








Try this too

SELECT *, OBJECT_DEFINITION(object_id) ObjectDefinition
FROM sys.objects
WHERE OBJECT_DEFINITION(object_id) LIKE '%anuragggggggggggg%'

create table #temp1
(
id int,
username varchar(50)
)

create table #temp2
(
  
  age int,
  sex varchar(1)

)

insert into #temp1
select 1,'anurag'
union all select 2,'abhi'
union all select 3,'binda'
union all select 4,'asu'

insert into #temp2
select 24,'m'
union all select 22,'m'
union all select 25,'m'

select * from #temp1
select * from #temp2

----------------Now we have to combine the two table ..lets try..-----------------

;with cte(id,username,row1)
as
(
select id,username,row_number() over (order by id)
from #temp1
),
cte1(age,sex,row2)
as
(
select age,sex,row_number() over (order by age)
from #temp2
),
cte2
as
(
select c1.id,c1.username,c2.age,c2.sex
from cte c1
left join cte1 c2 on c1.row1=c2.row2
)
select * from cte2
-----------------------------------------------------------------------------------