Best Online Tutorials

SQL Tutorial

Free SQL Tutorial

SQL Interview Questions

SQL Practice Questions

SQL Tips & Tricks

SQL Errors

SQL Books

Angular Tutorial

Free Angular Tutorial

Angular Interview Questions

Angular Practice Questions

Angular Tips & Tricks

Angular Errors

Angular Books

Node.js Tutorial

Free Node.js Tutorial

Node.js Interview Questions

Node.js Practice Questions

Node.js Tips & Tricks

Node.js Errors

Node.js Books

React.js Tutorial

Free React.js Tutorial

React.js Interview Questions

React.js Practice Questions

React.js Tips & Tricks

React.js Errors

React.js Books

SSIS Tutorial

Free SSIS Tutorial

SSIS Interview Questions

SSIS Practice Questions

SSIS Tips & Tricks

SSIS Errors

SSIS Books

SEO Tutorial

Free SEO Tutorial

SEO Interview Questions

SEO Practice Questions

SEO Tips & Tricks

SEO Errors

SEO Books

Javascript Tutorial

Free Javascript Tutorial

Javascript Interview Questions

Javascript Practice Questions

Javascript Tips & Tricks

Javascript Errors

Javascript Books

MongoDB Tutorial

Free MongoDB Tutorial

MongoDB Interview Questions

MongoDB Practice Questions

MongoDB Tips & Tricks

MongoDB Errors

MongoDB Books

Next.js Tutorial

Free Next.js Tutorial

Next.js Interview Questions

Next.js Practice Questions

Next.js Tips & Tricks

Next.js Errors

Next.js Books

SQL interview questions | Part 1 

Prashant Surya  |   Oct 22, 2019  |  Views: 2170
pictures

Description:



1.  What are the difference between Rank , dense rank and Row_Number() ?

Row_number(): 

Started from 1 for the first Row and no repeating or skipping numbers .

SELECT*,Row_number() over( orderby student_score)as RowNoRank from [MMM_HealthCare].[dbo].[StudentScore]
SELECT*,Row_number()over(partitionby student_score  orderby student_score)as RowNoRank from [MMM_HealthCare].[dbo].[StudentScore]

 


RANK ():

Started from 1 for the first Row and repeating numbers for duplicate values But leaving gaps between rank .

Ex : In below Screen shot ,Repeating numbers for Rank 5 and leaving gaps for rank 6

SELECT*,RANK()OVER(ORDERBY Student_Score)AS RankRank FROM [MMM_HealthCare].[dbo].[StudentScore]

SELECT*,RANK()over(partitionby student_score  orderby student_score)as RowNoRank from [MMM_HealthCare].[dbo].[StudentScore]


 


DENSE_RANK() :

Started from 1 for the first Row and repeating numbers for duplicate values but No leaving gaps between rank .

Ex : In below Screen shot ,Repeating numbers for Rank 5 and No leaving gaps for rank 6

SELECT*,DENSE_RANK()OVER(ORDERBY Student_Score)AS denseRank FROM [MMM_HealthCare].[dbo].[StudentScore]
SELECT*,DENSE_RANK()over(partitionby student_score  orderby student_score)as RowNoRank from [MMM_HealthCare].[dbo].[StudentScore]






2.  How to get Total of salary along with new row ?

Example: 
SELECT Name, Desgination, Department ,Sum(Salary)As Salary  from [MMM_HealthCare].[dbo].[EmplyeeExample] groupby Desgination, Department ,Name
Union
SELECT'Total',NULL,NULL,Sum(Salary)As Salary from [MMM_HealthCare].[dbo].[EmplyeeExample] 





3.  How to merge two rows into single row in below Table ?

 Example: 
 
Actual Result:




Expected Result:

select id ,min(name)As name ,min(Typed) As Typed from [MMM_HealthCare].[dbo].[T]  groupby id





4.  What is the difference Between Is Null and Coalesce Function ?

ISNULL() Function :

This function contains only two arguments.It returns First Non-Null Value.

Ex : SELECT IS NULL('H',NULL)

Coalesce() Function :

This function contains multiple arguments.It returns First Non-Null Value.

Ex : SELECT COALESCE(NULL,NULL,NULL,'N','H',NULL,NULL,'A')
 





5. How to use SUBSTRING, REPLACE, CHARINDEX, STUFF, Reverse Function ?

SUBSTRING:

SELECT SUBSTRING('abstriodd',3,5)   --return substring from string start from given postion 1 and until given 5

--o/p : strio


REPLACE:

SELECT REPLACE('sdgfjsgfjdfgk','f','mm')    --replace substring with other substring in a string

--o/p: sdgmmjsgmmjdmmgk


CHARINDEX:

SELECT CHARINDEX('j','sjsdf')    -- return postion of substring in a string
 
--o/p : 2


STUFF:

SELECT STUFF('Sql server', 1,3,'new')  --deletes a part of a string and then inserts another part into the string, starting at a specified position.

--o/p : new server

Reverse :

SELECT Reverse('Sql server')     --reverses a string
--o/p: revres lqS




6.  What is the difference b/w Aggregate and Analytical Function ?

Aggregate Function : It returns single result row based on grouping of rows.




Analytical Function : It returns result row for each partition.

 






Comments:
Content Contributor
Prashant Surya

Prashant Surya

Software developer at Uniserve Data Technologies Pvt. Ltd.  |   Noida, Uttar Pradesh, India

I am a software developer, freelancer and trainer working on technologies likes. MEAN stack, MERN Stack, ASP.NET full stack, WebRTC, socket.io ,PWA, SQL, JavaScript, jQuery, HTML, CSS, SEO, responsive UI/UX designing... having 7 years of experience in development in multiple domains like, eCommerce, RTA, capital markets, education, real estate...


Read related articles

SQL Interview Questions | Part 5

Prashant Surya  |   Jan 5, 2020  |  Views: 1597

SQL Interview Questions | Part 4

Prashant Surya  |   Nov 2, 2019  |  Views: 1296

SQL Interview Questions | Part 3

Prashant Surya  |   Oct 30, 2019  |  Views: 1310

SQL interview questions | Part 2

Prashant Surya  |   Oct 24, 2019  |  Views: 1197

SQL interview questions | Part 1

Prashant Surya  |   Oct 22, 2019  |  Views: 2171

Want to sell your software ?-

If you want to sell your readymade software to the genuine clients or businessman, list your software with details and demo links.

  • Sell ERP
  • Sell Website Clones
  • Sell App Clones
  • Sell Readymade Portals
  • Sell Readymade Websites
  • Sell Mobile Apps
  • Sell Window Apps

Clients will find it using our advanced search filter and will contact you directly.

No any charge for the product lsiting.

Do you own a Company ?+

Property.sale

Buy or Sell Commercial Property+

Are you a freelancer ?+

Looking for a job ?+

Are you a Service Provider ?+

Are you a blogger ?+

Do you own an IT Institute ?+



Property.sale