Top 100 SQL server queries Interview questions | SQL server interview questions

Introduction

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
)

SQL Server Basic Questions - 'Select', 'Where', 'Order By', 'Wild Card Search'.


1) Get all students details from the tblstudent table. 
1
 
Select * from tblstudent
2) Get first name from the tblstudent using alias name "StudentName”. 
1
 
Select FirstName as StudentName from tblstudent
     

3) Get first name, last name from the tblstudent table. 
1
 
Select FirstName, LastName from tblstudent
4) Select student details whose name is "David" from tblstudent. 
1
 
Select * from tblstudent where FirstName = 'David'
5) Get first name from tblstudent in upper case. 
1
 
 Select upper(FirstName) from tblstudent
6) Get last name from tblstudent in lower case. 
1
 
 Select lower(FirstName) from tblstudent
7) Get unique branch from tblstudent table. 
1
 
Select distinct(Branch) from tblstudent
Also check, Top C# interview questions and answers
8) Write a query to combine FirstName and LastName and display it as "Full Name". 
1
 
Select FirstName +' '+ LastName As "Full Name" from tblstudent
9) Get all student details from tblstudent whose "FirstName" contains 'a'. 
1
 
Select * from tblstudent where FirstName like '%A%'
10) Get all student details from tblstudent whose "FirstName" start with latter 'd'. 
1
 
Select * from tblstudent where FirstName like 'D%'
11) Get all student details from tblstudent whose "FirstName" end with 'a'. 
1
 
Select * from tblstudent where FirstName like '%a'
     


12) List all students whose first name start with 'Ma' or 'Da'. 
1
 
SELECT FirstName, LastName, Branch FROM tblStudent WHERE firstname LIKE '%Ma%' OR firstname LIKE '%Da%'
13) Get all students details from the tblStudent table order by FirstName Ascending. 
1
 
Select * from tblStudent order by FirstName asc
14) Get all students details from the tblStudent table order by FirstName Descending 
1
 
Select * from tblStudent order by FirstName desc
15) Get all students details from the tblStudent table order by LastName Ascending and Admission fees descending. 
1
 
Select * from tblStudent order by LastName asc, Admission_Fee desc
16) Get position of 'v' in name 'David' from tblstudent. 
1
 
Select CHARINDEX('v', FirstName,0) from tblstudent where FirstName = 'David'
17) Select firstname from tblstudent with 'Hello' prefix. 
1
 
select 'Hello ' + firstname from tblStudent
18) Get FirstName from tblstudent after removing white spaces from right side. 
1
 
Select RTRIM(FirstName) from tblstudent
19) Get FirstName from tblstudent after removing white spaces from left side. 
1
 
Select LTRIM(FirstName) from tblstudent
20) Get length of FirstName from tblstudent. 
1
 
Select len(FirstName) from tblstudent
21) Get FristName from tblStudent table after replacing 'a' with '$'. 
1
 
Select REPLACE(FirstName,'a','$') from tblStudent
22) Get all students details from tblStudent whose first name starts with 'm' and name contains 4 letters. 
1
 
Select * from tblStudent where FirstName like 'm___'
23) Get all students details from tblStudent whose first name ends with 'a' and name contains 4 letters. 
1
 
Select * from tblStudent where FirstName like '___a'
24) Get fristname from tblstudent not start with any single character between a-p. 
1
 
select * from tblStudent where FirstName like '[^a-p]%'

SQL Server Advanced Questions - 'DateTime'


25) Get first name, admission year, admission month and admission date from tblStudent table. 
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
26) Get student details from tblStudent table whose admission year is "2015". 
1
 
Select * from tblStudent where SUBSTRING(convert(varchar,Admission_date,103),7,4)='2015'
27) Get student details from tblStudent table whose admission date is after January 31st. 
1
 
Select * from tblStudent where Admission_date >'01/31/2016'
28) Get student details from tblStudent table whose admission date is before January 31st. 
1
 
Select * from tblStudent where Admission_date <'01/31/2016'
29) Get student details from tblStudent table whose admission month is "January". 
1
 
Select * from tblStudent where SUBSTRING(convert(varchar,Admission_date,100),1,3)='Jan'
30) Get admission date and time from tblStudent table. 
1
 
Select convert(varchar(19), admission_date,121) from tblStudent
31) Get database date. 
1
 
select getdate()
32) Get UTC date. 
1
 
select GETUTCDATE()
33) Get only month part of admission date from tblStudent. 
1
 
select DATEPART(MONTH, admission_date) from tblStudent
34) Get only year part of admission date from tblStudent. 
1
 
select DATEPART(YEAR, admission_date) from tblStudent
35) Get all student details from tblStudent table whose admission date between '2015-01-01' and '2016-01-01'. 
1
 
select * from tblStudent where admission_date between '2015-01-01' and '2016-01-01'
36) Get the first name, last name, current date, admission date and difference between current date and admission date in days. 
1
2
 
select FirstName, LastName, GETDATE() as 'Current_date', Admission_date,
DATEDIFF(DD, Admission_date, GETDATE()) As days from tblStudent
37) Get the first name, last name, current date, admission date and difference between current date and admission date in month. 
1
2
 
select FirstName, LastName, GETDATE() as 'Current_date', Admission_date,
DATEDIFF(MM, Admission_date, GETDATE()) As months from tblStudent
Also check, Top C# interview questions and answers
38) Get the first name, last name, current date, admission date and difference between current date and admission date in year. 
1
2
 
select FirstName, LastName, GETDATE() as 'Current_date', Admission_date,
DATEDIFF(YYYY, Admission_date, GETDATE()) As years from tblStudent
39) Show "AdmissionDate" in "dd mmm yyyy" format, ex- "06 May 2016". 
1
 
select CONVERT(varchar(30), admission_date, 106) from tblStudent
40) Show "AdmissionDate" in "yyyy/mm/dd" format, ex- "2016/05/06". 
1
 
select CONVERT(varchar(30), admission_date, 111) from tblStudent
41) Show only time part of the "AdmissionDate" from tblStudent. 
1
 
Select CONVERT(varchar(20), admission_date, 108) from tblStudent
42) Select no of students get admission with respect to year and month from tblStudent table. 
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)

SQL Server Advanced Questions - 'Top, Union, admission fees and Group by' questions


43) Select TOP Nth (any number) admission fees from tblStudent table 
1
 
select top 1 * from tblStudent
44) Select second highest admission fees from "tblStudent" table. 
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
45) Select TOP 2 Admission fees from tblStudent table 
1
 
select top 2 * from tblStudent order by Admission_fee desc
46) Select Highest Admission fees from tblStudent table. 
1
 
Select Max(Admission_fee) from (select top 6 * from tblStudent) A
47) Select Minimum Admission fees from tblStudent table. 
1
 
Select Min(Admission_fee) from (select top 6 * from tblStudent) A
48) Select FirstName, LastName from tblStudent table in singal column. 
1
 
select FirstName from tblStudent union select LastName from tblStudent
49) Get students details from "tblStudent" table whose admission fees is less than 15000. 
1
 
Select * from tblStudent where Admission_fee < 15000
50) Get students details from "tblStudent" table whose admission fees is greater than 15000. 
1
 
Select * from tblStudent where Admission_fee > 15000
51) Get students details from "tblStudent" table whose admission fees in between 10000 and 15000. 
1
 
Select * from tblStudent where Admission_fee between 10000 and 15000
52) Select 5 % of admission fees from sara , 10% of admission fees from Dora and for other 15 % of admission fees as 'Deducted_Admission_fee' from tblStudent table. 
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
53) Write a query to get how many students exist in tblstudent. 
1
 
select COUNT(*) from tblStudent
54) Write the query to get the branch and branch wise total(sum) admission fees, display it in ascending order according to admission fees. 
1
2
 
Select Branch, SUM(admission_fee) as Total_admission_fees from tblStudent
group by Branch order by SUM(admission_fee) ASC
55) Write the query to get the branch and branch wise total(sum) admission fees, display it in desending order according to admission fees. 
1
2
 
Select Branch, SUM(admission_fee) as Total_admission_fees from tblStudent
group by Branch order by SUM(admission_fee) DESC
56) Get branch wise average admission fees from "tblStudent" table order by admission fees ascending order. 
1
2
 
Select Branch, AVG(admission_fee) as Average_admission_fees from tblStudent
group by Branch order by SUM(admission_fee) ASC
57) Get branch wise maximum admission fees from "tblStudent" table order by admission fees descending order. 
1
2
 
Select Branch, MAX(admission_fee) as Maxmum_admission_fees from tblStudent
group by Branch order by SUM(admission_fee) DESC
58) Get branch wise minimum admission fees from "tblStudent" table order by admission fees ascending order. 
1
2
 
Select Branch, MIN(admission_fee) as Minimum_admission_fees from tblStudent
group by Branch order by SUM(admission_fee) ASC
59) Get branch, no of students in a branch, total admission fees with respect to a branch from tblStudent table order by admission fees descending. 
1
2
 
Select Branch,count(FirstName),sum(admission_fee) Admission_fee from tblStudent
group by Branch order by Admission_fee desc
60) Select no of students joined with respect to year and month from tblStudent table. 
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)

SQL Server Advanced Questions - 'Join' questions


61) Select FirstName, Lastname, Scholarship amount from tblStudent and tblScholarship table for all students even if they didn't get Scolarship. 
1
2
 
Select Firstname, Lastname, Scholarship_Amount from tblStudent A left join tblScholarship
 B on A.StudentId = B.Student_ref_id
62) Select FirstName, Lastname, Scholarship amount from tblStudent and tblScolarship table for those students who got Scolarship amount. 
1
2
 
Select Firstname, Lastname, Scholarship_Amount from tblStudent A Inner join tblScholarship 
B on A.StudentId = B.Student_ref_id
63) Select FirstName, Lastname, Scholarship amount from tblStudent and tblScholarship table for those students who got Scolarship amount greater than 1200. 
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
64) Select FirstName, Lastname, Scholarship amount from tblStudent and tblScholarship table for those students who got Scolarship amount using right join. 
1
2
 
Select Firstname, Lastname, isnull(Scholarship_Amount,0) from tblStudent A right join 
tblScholarship B on A.StudentId = B.Student_ref_id
65) Select FirstName, Lastname, Scholarship amount from tblStudent and tblScholarship table for all students even if they didn't get Scolarship amount and set Scolarship amount as 0 for those students who didn't get Scolarship amount. 
1
2
 
Select Firstname, Lastname, ISNULL(Scholarship_Amount,0) from tblStudent a left join
tblScholarship B on A.StudentId = B.Student_ref_id
Also check Top C# interview questions and answers
66) Write a query to find out the studentname who has not received any scholarship amount, and display 0 in front of his name. 
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

SQL Server Advanced Questions - 'Tricky Query'


71) How to select random record form a tblstudent. 
1
 
Select top 1* from tblStudent order by NEWID()
72) Write a query to create a clone of existing table without using Create Command. 
1
 
Select Top 6 * INTO tblStudent_clone From tblstudent

note- 'tblStudent_clone' will be create in sample database

73) Write a query to calculate number of T in string 'TECHSTUDY'. 
1
 
Select LEN('TECHSTUDY') - LEN (REPLACE('TECHSTUDY', 'T', ''))
74) What will be the result of the query below? 
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 '='.


75) What would be the output of the follwing query? 
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

76) Write down the query to print first letter of a Name in Upper Case and all other letter in Lower Case. 
1
2
 
Select UPPER(SUBSTRING(Firstname, 1,1))+ 
LOWER(SUBSTRING(Firstname, 2, Len(FirstName)-1))As Firstname from tblStudent
Also check Top C# interview questions and answers
77) Write down the query to display all student name in one cell seprated by ',' example:-"Sara, David, Dora, Jack, Vikram, Ross". 
1
2
3
 
Declare @name varchar(MAX) = ''
Select @name = @name + FirstName + ', ' from [tblStudent]
Select SUBSTRING(@name,1, LEN(@name)) As StudentList

SQL Server Advanced Questions - 'SQL DDL Questions'


78) Write down the query to create tblstudent table with primary key (studentId) 
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
)
79) How to set Primary key(PK) using Alter command 
1
 
Alter table tblstudent3 add primary key(StudnetId)
80) How to drop Primary key(PK) using Alter command 
1
2
 
ALTER TABLE tblstudent
DROP CONSTRAINT PK__tblstudentid
81) Write a query to add new column in tblstudent 
1
2
 
ALTER TABLE tblstudent
ADD Address varchar(50)
82) Write a query to drop Address column in tblstudent 
1
2
 
ALTER TABLE tblstudent
DROP Column Address

SQL Server Advanced Questions - 'Small Tricky SQL SERVER Queries'


85) What would be the output of following query? 
1
 
select 15

a. 15
b. 1
c. 0
d. Could not find

86) What would be the output of following query? 
1
 
select $

a. Could not find
b. 1
c. $
d. 0.00
87) What would be the output of following query? 
1
 
select 1 + '1'

a. 11
b. 1
c. 2
d. Could not find

88) What would be the output of following query? 
1
 
select '1' + 1

a. 11
b. 1
c. 2
d. Could not find

89) What would be the output of following query? 
1
 
select count('7')

a. 0
b. 1
c. 7
d. Could not find

90) What would be the output of following query?
1
 
select count(*)

a. 0
b. 1
c. 7
d. Could not find

91) What would be the output of following query?
1
 
select 'TechStudy' + 1

a. 'TechStudy'
b. TechStudy
c. TechStudy1
d. Conversion failed

92) What would be the output of following query?
1
 
select (SELECT 'TechStudy')

a. 'TechStudy'
b. TechStudy
c. TechStudy1
d. Could not find

93) What would be the output of following query?
1
 
select 'TechStudy' + '1'

a. 'TechStudy'
b. TechStudy
c. TechStudy1
d. Could not find

94) What would be the output of following query?
1
 
select * from 'City'

a. City
b. None
c. Select all data from city table
d. Incorrect syntax

95) What would be the output of following query?
1
 
select * from City, Student

a. Output will be inner join
b. Output will be only City table details
c. Output will cross join of both tables details
d. Could not find

96) What would be the output of following query?
1
 
select 'David' from student

a. Display David as many rows in student table
b. None
c. Display on time David
d. Could not find

97) What would be the output of following query?
1
 
select SUM(null)

a. 0
b. None
c. NULL
d. Operand data type void type is invalid for sum operator.

98) What would be the output of following query?
1
 
select 0/9

a. 0
b. Throw error(Divide by zero error encountered.)
c. NULL
d. 1

99) What would be the output of following query?
1
 
select 1 where null = null

a. 0
b. Nothing will return
c. NULL
d. 1

In case you are looking for the most common Technical Interview Questions, read along:

Top 100 C# interview questions and answers 2020

Top 30 ADO.NET Interview Questions and Answers

Top 45 C programming interview questions and answers

12 July 2019 18832     Written By: Rohit




Popular Posts

About Us

Tech Study passionately delivers stylish and innovative information to programmer and technology lovers. Tech Study is a young and innovative mind having the zeal to do something useful and creative. Our main goal is to offer step by step debugging solutions for programmers.

Read More

Subscribe

Subscribe and get a most valuable piece of content delivered to your inbox.

© 2020 Tech Study. All rights reserved | Developed by Tech Study| Privacy Policy | Sitemap