In this article, you will find basic to most puzzled interview queries questions with an example. This article is very useful for those who are preparing for an interview in IT company. Whether you are experienced or fresher, this article will cover all Microsoft SQL server interview questions from basic to advanced level.
So, before starting let's create and populate tblstudent and tblScholarship table using following script.
1 2 3 4 5 6 7 8 9 |
Create table tblStudent ( StudentId int, FirstName varchar(50), LastName varchar(50), Admission_fee int, Admission_date datetime, Branch varchar(50), ) |
1 2 3 4 5 6 |
Create table tblScholarship ( Student_ref_id int, Scholarship_Date date, Scholarship_Amount int ) |
1 |
Select * from tblstudent |
1 |
Select FirstName as StudentName from tblstudent |
1 |
Select FirstName, LastName from tblstudent |
1 |
Select * from tblstudent where FirstName = 'David' |
1 |
Select upper(FirstName) from tblstudent |
1 |
Select lower(FirstName) from tblstudent |
1 |
Select distinct(Branch) from tblstudent |
1 |
Select FirstName +' '+ LastName As "Full Name" from tblstudent |
1 |
Select * from tblstudent where FirstName like '%A%' |
1 |
Select * from tblstudent where FirstName like 'D%' |
1 |
Select * from tblstudent where FirstName like '%a' |
1 |
SELECT FirstName, LastName, Branch FROM tblStudent WHERE firstname LIKE '%Ma%' OR firstname LIKE '%Da%' |
1 |
Select * from tblStudent order by FirstName asc |
1 |
Select * from tblStudent order by FirstName desc |
1 |
Select * from tblStudent order by LastName asc, Admission_Fee desc |
1 |
Select CHARINDEX('v', FirstName,0) from tblstudent where FirstName = 'David' |
1 |
select 'Hello ' + firstname from tblStudent |
1 |
Select RTRIM(FirstName) from tblstudent |
1 |
Select LTRIM(FirstName) from tblstudent |
1 |
Select len(FirstName) from tblstudent |
1 |
Select REPLACE(FirstName,'a','$') from tblStudent |
1 |
Select * from tblStudent where FirstName like 'm___' |
1 |
Select * from tblStudent where FirstName like '___a' |
1 |
select * from tblStudent where FirstName like '[^a-p]%' |
1 2 3 4 |
select SUBSTRING (convert(varchar,admission_date,103),7,4) as Year, SUBSTRING (convert(varchar,admission_date,100),1,3) as Month, SUBSTRING (convert(varchar,admission_date,100),5,2) as Date from tblStudent |
1 |
Select * from tblStudent where SUBSTRING(convert(varchar,Admission_date,103),7,4)='2015' |
1 |
Select * from tblStudent where Admission_date >'01/31/2016' |
1 |
Select * from tblStudent where Admission_date <'01/31/2016' |
1 |
Select * from tblStudent where SUBSTRING(convert(varchar,Admission_date,100),1,3)='Jan' |
1 |
Select convert(varchar(19), admission_date,121) from tblStudent |
1 |
select getdate()
|
1 |
select GETUTCDATE()
|
1 |
select DATEPART(MONTH, admission_date) from tblStudent |
1 |
select DATEPART(YEAR, admission_date) from tblStudent |
1 |
select * from tblStudent where admission_date between '2015-01-01' and '2016-01-01' |
1 2 |
select FirstName, LastName, GETDATE() as 'Current_date', Admission_date, DATEDIFF(DD, Admission_date, GETDATE()) As days from tblStudent |
1 2 |
select FirstName, LastName, GETDATE() as 'Current_date', Admission_date, DATEDIFF(MM, Admission_date, GETDATE()) As months from tblStudent |
1 2 |
select FirstName, LastName, GETDATE() as 'Current_date', Admission_date, DATEDIFF(YYYY, Admission_date, GETDATE()) As years from tblStudent |
1 |
select CONVERT(varchar(30), admission_date, 106) from tblStudent |
1 |
select CONVERT(varchar(30), admission_date, 111) from tblStudent |
1 |
Select CONVERT(varchar(20), admission_date, 108) from tblStudent |
1 2 3 |
select datepart (YYYY,Admission_date) Admission_Year, datepart (MM,Admission_date) Admission_Month,count(*) Total_Student from tblStudent group by datepart(YYYY,Admission_date), datepart(MM,Admission_date) |
1 |
select top 1 * from tblStudent |
1 2 |
Select TOP 1 Admission_fee from (Select TOP 2 Admission_fee from tblStudent order by Admission_fee DESC) T Order By Admission_fee ASC |
1 |
select top 2 * from tblStudent order by Admission_fee desc |
1 |
Select Max(Admission_fee) from (select top 6 * from tblStudent) A |
1 |
Select Min(Admission_fee) from (select top 6 * from tblStudent) A |
1 |
select FirstName from tblStudent union select LastName from tblStudent |
1 |
Select * from tblStudent where Admission_fee < 15000 |
1 |
Select * from tblStudent where Admission_fee > 15000 |
1 |
Select * from tblStudent where Admission_fee between 10000 and 15000 |
1 2 |
SELECT FirstName, CASE FirstName WHEN 'Sara' THEN Admission_fee * .10 WHEN 'Dora' THEN Admission_fee * .15 ELSE Admission_fee * .15 END "Deducted_Admission_fee" FROM tblStudent |
1 |
select COUNT(*) from tblStudent |
1 2 |
Select Branch, SUM(admission_fee) as Total_admission_fees from tblStudent group by Branch order by SUM(admission_fee) ASC |
1 2 |
Select Branch, SUM(admission_fee) as Total_admission_fees from tblStudent group by Branch order by SUM(admission_fee) DESC |
1 2 |
Select Branch, AVG(admission_fee) as Average_admission_fees from tblStudent group by Branch order by SUM(admission_fee) ASC |
1 2 |
Select Branch, MAX(admission_fee) as Maxmum_admission_fees from tblStudent group by Branch order by SUM(admission_fee) DESC |
1 2 |
Select Branch, MIN(admission_fee) as Minimum_admission_fees from tblStudent group by Branch order by SUM(admission_fee) ASC |
1 2 |
Select Branch,count(FirstName),sum(admission_fee) Admission_fee from tblStudent group by Branch order by Admission_fee desc |
1 2 |
select datepart (YYYY,Admission_date) Admission_year,datepart (MM,Admission_date) Admission_month, count(*) Total_student from tblStudent group by datepart(YYYY,Admission_date), datepart(MM,Admission_date) |
1 2 |
Select Firstname, Lastname, Scholarship_Amount from tblStudent A left join tblScholarship B on A.StudentId = B.Student_ref_id |
1 2 |
Select Firstname, Lastname, Scholarship_Amount from tblStudent A Inner join tblScholarship B on A.StudentId = B.Student_ref_id |
1 2 |
Select Firstname, Lastname, Scholarship_Amount from tblStudent A Inner join tblScholarship B on A.StudentId = B.Student_ref_id and Scholarship_Amount > 1200 |
1 2 |
Select Firstname, Lastname, isnull(Scholarship_Amount,0) from tblStudent A right join tblScholarship B on A.StudentId = B.Student_ref_id |
1 2 |
Select Firstname, Lastname, ISNULL(Scholarship_Amount,0) from tblStudent a left join tblScholarship B on A.StudentId = B.Student_ref_id |
1 2 3 |
Select Firstname, ISNULL(Scholarship_Amount, 0) As [Scholarship Amount] from tblStudent A Left Outer Join [tblScholarship] B on A.StudentId = B.Student_ref_id where Scholarship_Amount is null |
1 |
Select top 1* from tblStudent order by NEWID() |
1 |
Select Top 6 * INTO tblStudent_clone From tblstudent |
note- 'tblStudent_clone' will be create in sample database
1 |
Select LEN('TECHSTUDY') - LEN (REPLACE('TECHSTUDY', 'T', '')) |
1 |
select case when null = null then 'True' else 'False' end as Result; |
Answer : False ! The reason is the proper way to compare a value in SQL server is using 'IS' operator and not using '='.
1 2 3 |
Select Case when 10 = 10 then 'Tech Study' when 20 = 20 then 'techstudy.org' else 'techstudy the complete debuggin solution' end as name |
Answer: Tech Study
1 2 |
Select UPPER(SUBSTRING(Firstname, 1,1))+ LOWER(SUBSTRING(Firstname, 2, Len(FirstName)-1))As Firstname from tblStudent |
1 2 3 |
Declare @name varchar(MAX) = '' Select @name = @name + FirstName + ', ' from [tblStudent] Select SUBSTRING(@name,1, LEN(@name)) As StudentList |
1 2 3 4 5 6 7 8 |
Create table tblstudent( StudnetId int identity(1,1) Not null primary key, Firstname varchar(50), LastName varchar(50), Admission_fee int, Admission_date date, Branch date ) |
1 |
Alter table tblstudent3 add primary key(StudnetId) |
1 2 |
ALTER TABLE tblstudent DROP CONSTRAINT PK__tblstudentid |
1 2 |
ALTER TABLE tblstudent ADD Address varchar(50) |
1 2 |
ALTER TABLE tblstudent DROP Column Address |
1 |
select 15 |
1 |
select $ |
1 |
select 1 + '1' |
1 |
select '1' + 1 |
1 |
select count('7') |
1 |
select count(*) |
1 |
select 'TechStudy' + 1 |
1 |
select (SELECT 'TechStudy') |
1 |
select 'TechStudy' + '1' |
1 |
select * from 'City' |
1 |
select * from City, Student |
1 |
select 'David' from student |
1 |
select SUM(null) |
1 |
select 0/9 |
1 |
select 1 where null = null |
In case you are looking for the most common Technical Interview Questions, read along:
12 July 2019 18354 Written By: Rohit
© 2020 Tech Study. All rights reserved | Developed by Tech Study| Privacy Policy | Sitemap