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. Show
So, before starting let’s create and populate tblstudent and tblScholarship table using following script. Create table tblStudent ( StudentId int, FirstName varchar(50), LastName varchar(50), Admission_fee int, Admission_date datetime, Branch varchar(50), ) 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. Select * from tblstudent2) Get first name from the tblstudent using alias name “StudentName”. Select FirstName as StudentName from tblstudent3) Get first name, last name from the tblstudent table. Select FirstName, LastName from tblstudent4) Select student details whose name is “David” from tblstudent. Select * from tblstudent where FirstName = 'David'5) Get first name from tblstudent in upper case. Select upper(FirstName) from tblstudent6) Get last name from tblstudent in lower case. Select lower(FirstName) from tblstudent7) Get unique branch from tblstudent table. Select distinct(Branch) from tblstudentAlso check, Top C# interview questions and answers 8) Write a query to combine FirstName and LastName and display it as “Full Name”. Select FirstName +' '+ LastName As "Full Name" from tblstudent9) Get all student details from tblstudent whose “FirstName” contains ‘a’. Select * from tblstudent where FirstName like '%A%'10) Get all student details from tblstudent whose “FirstName” start with latter ‘d’. Select * from tblstudent where FirstName like 'D%'11) Get all student details from tblstudent whose “FirstName” end with ‘a’. Select * from tblstudent where FirstName like '%a'12) List all students whose first name start with ‘Ma’ or ‘Da’. 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. Select * from tblStudent order by FirstName asc14) Get all students details from the tblStudent table order by FirstName Descending Select * from tblStudent order by FirstName desc15) Get all students details from the tblStudent table order by LastName Ascending and Admission fees descending. 16) Get position of ‘v’ in name ‘David’ from tblstudent. Select CHARINDEX('v', FirstName,0) from tblstudent where FirstName = 'David'17) Select firstname from tblstudent with ‘Hello’ prefix. select 'Hello ' + firstname from tblStudent18) Get FirstName from tblstudent after removing white spaces from right side. Select RTRIM(FirstName) from tblstudent19) Get FirstName from tblstudent after removing white spaces from left side. Select LTRIM(FirstName) from tblstudent20) Get length of FirstName from tblstudent. Select len(FirstName) from tblstudent21) Get FristName from tblStudent table after replacing ‘a’ with ‘$’. Select REPLACE(FirstName,'a','$') from tblStudent22) Get all students details from tblStudent whose first name starts with ‘m’ and name contains 4 letters. 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. Select * from tblStudent where FirstName like '___a'24) Get fristname from tblstudent not start with any single character between a-p. 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. 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 tblStudent26) Get student details from tblStudent table whose admission year is “2015”. 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. Select * from tblStudent where Admission_date >'01/31/2016'28) Get student details from tblStudent table whose admission date is before January 31st. Select * from tblStudent where Admission_date <'01/31/2016'29) Get student details from tblStudent table whose admission month is “January”. Select * from tblStudent where SUBSTRING(convert(varchar,Admission_date,100),1,3)='Jan'30) Get admission date and time from tblStudent table. Select convert(varchar(19), admission_date,121) from tblStudent31) Get database date. select getdate()32) Get UTC date. select GETUTCDATE()33) Get only month part of admission date from tblStudent. 34) Get only year part of admission date from tblStudent. select DATEPART(YEAR, admission_date) from tblStudent35) Get all student details from tblStudent table whose admission date between ‘2015-01-01’ and ‘2016-01-01’. 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. select FirstName, LastName, GETDATE() as 'Current_date', Admission_date, DATEDIFF(DD, Admission_date, GETDATE()) As days from tblStudent37) Get the first name, last name, current date, admission date and difference between current date and admission date in month. select FirstName, LastName, GETDATE() as 'Current_date', Admission_date, DATEDIFF(MM, Admission_date, GETDATE()) As months from tblStudentAlso 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. select FirstName, LastName, GETDATE() as 'Current_date', Admission_date, DATEDIFF(YYYY, Admission_date, GETDATE()) As years from tblStudent39) Show “AdmissionDate” in “dd mmm yyyy” format, ex- “06 May 2016”. select CONVERT(varchar(30), admission_date, 106) from tblStudent40) Show “AdmissionDate” in “yyyy/mm/dd” format, ex- “2016/05/06”. select CONVERT(varchar(30), admission_date, 111) from tblStudent41) Show only time part of the “AdmissionDate” from tblStudent. Select CONVERT(varchar(20), admission_date, 108) from tblStudent42) Select no of students get admission with respect to year and month from tblStudent table. 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 select top 1 * from tblStudent44) Select second highest admission fees from “tblStudent” table. Select TOP 1 Admission_fee from (Select TOP 2 Admission_fee from tblStudent order by Admission_fee DESC) T Order By Admission_fee ASC45) Select TOP 2 Admission fees from tblStudent table select top 2 * from tblStudent order by Admission_fee desc46) Select Highest Admission fees from tblStudent table. Select Max(Admission_fee) from (select top 6 * from tblStudent) A47) Select Minimum Admission fees from tblStudent table. Select Min(Admission_fee) from (select top 6 * from tblStudent) A48) Select FirstName, LastName from tblStudent table in singal column. select FirstName from tblStudent union select LastName from tblStudent49) Get students details from “tblStudent” table whose admission fees is less than 15000. 50) Get students details from “tblStudent” table whose admission fees is greater than 15000. Select * from tblStudent where Admission_fee > 1500051) Get students details from “tblStudent” table whose admission fees in between 10000 and 15000. Select * from tblStudent where Admission_fee between 10000 and 1500052) 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. SELECT FirstName, CASE FirstName WHEN 'Sara' THEN Admission_fee * .10 WHEN 'Dora' THEN Admission_fee * .15 ELSE Admission_fee * .1553) Write a query to get how many students exist in tblstudent. select COUNT(*) from tblStudent54) Write the query to get the branch and branch wise total(sum) admission fees, display it in ascending order according to admission fees. Select Branch, SUM(admission_fee) as Total_admission_fees from tblStudent group by Branch order by SUM(admission_fee) ASC55) Write the query to get the branch and branch wise total(sum) admission fees, display it in desending order according to admission fees. Select Branch, SUM(admission_fee) as Total_admission_fees from tblStudent group by Branch order by SUM(admission_fee) DESC56) Get branch wise average admission fees from “tblStudent” table order by admission fees ascending order. Select Branch, AVG(admission_fee) as Average_admission_fees from tblStudent group by Branch order by SUM(admission_fee) ASC57) Get branch wise maximum admission fees from “tblStudent” table order by admission fees descending order. Select Branch, MAX(admission_fee) as Maxmum_admission_fees from tblStudent group by Branch order by SUM(admission_fee) DESC58) Get branch wise minimum admission fees from “tblStudent” table order by admission fees ascending order. Select Branch, MIN(admission_fee) as Minimum_admission_fees from tblStudent group by Branch order by SUM(admission_fee) ASC59) Get branch, no of students in a branch, total admission fees with respect to a branch from tblStudent table order by admission fees descending. Select Branch,count(FirstName),sum(admission_fee) Admission_fee from tblStudent group by Branch order by Admission_fee desc60) Select no of students joined with respect to year and month from tblStudent table. 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’ questions61) Select FirstName, Lastname, Scholarship amount from tblStudent and tblScholarship table for all students even if they didn’t get Scolarship. Select Firstname, Lastname, Scholarship_Amount from tblStudent A left join tblScholarship B on A.StudentId = B.Student_ref_id62) Select FirstName, Lastname, Scholarship amount from tblStudent and tblScolarship table for those students who got Scolarship amount. Select Firstname, Lastname, Scholarship_Amount from tblStudent A Inner join tblScholarship B on A.StudentId = B.Student_ref_id63) Select FirstName, Lastname, Scholarship amount from tblStudent and tblScholarship table for those students who got Scolarship amount greater than 1200. Select Firstname, Lastname, Scholarship_Amount from tblStudent A Inner join tblScholarship B on A.StudentId = B.Student_ref_id and Scholarship_Amount > 120064) Select FirstName, Lastname, Scholarship amount from tblStudent and tblScholarship table for those students who got Scolarship amount using right join. Select Firstname, Lastname, isnull(Scholarship_Amount,0) from tblStudent A right join tblScholarship B on A.StudentId = B.Student_ref_id65) 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. Select Firstname, Lastname, ISNULL(Scholarship_Amount,0) from tblStudent a left join tblScholarship B on A.StudentId = B.Student_ref_idAlso 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. SQL Server Advanced Questions – ‘Tricky Query’71) How to select random record form a tblstudent. Select top 1* from tblStudent order by NEWID()72) Write a query to create a clone of existing table without using Create Command. 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’. Select LEN('TECHSTUDY') - LEN (REPLACE('TECHSTUDY', 'T', ''))74) What will be the result of the query below? 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? Select Case when 10 = 10 then 'Tech Study' when 20 = 20 then 'techstudy.org' else 'techstudy the complete debuggin solution' end as nameAnswer: Tech Study 76) Write down the query to print first letter of a Name in Upper Case and all other letter in Lower Case. Select UPPER(SUBSTRING(Firstname, 1,1))+ LOWER(SUBSTRING(Firstname, 2, Len(FirstName)-1))As Firstname from tblStudentAlso 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”. Declare @name varchar(MAX) = '' Select @name = @name + FirstName + ', ' from [tblStudent] Select SUBSTRING(@name,1, LEN(@name)) As StudentListSQL Server Advanced Questions – ‘SQL DDL Questions’78) Write down the query to create tblstudent table with primary key (studentId) 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 Alter table tblstudent3 add primary key(StudnetId)80) How to drop Primary key(PK) using Alter command ALTER TABLE tblstudent DROP CONSTRAINT PK__tblstudentid81) Write a query to add new column in tblstudent ALTER TABLE tblstudent ADD Address varchar(50)82) Write a query to drop Address column in tblstudent ALTER TABLE tblstudent DROP Column AddressSQL Server Advanced Questions – ‘Small Tricky SQL SERVER Queries’85) What would be the output of following query? select 15a. 15 b. 1 c. 0 d. Could not find |