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 3 

Prashant Surya  |   Oct 30, 2019  |  Views: 1303
pictures

Description:

1) What is the difference between DELETE and TRUNCATE commands?

 

SNO.

DELETE

TRUNCATE

1

DELETE command is used to remove one by one rows based on parameter which set in WHERE clause.

 

Ex : DELETE FROM EMP WHERE ID=101

 

TRUNCATE command is used to remove all rows at a time.

 

Ex : TRUNCATE TABLE EMP

 

 

2

DELETE can be rolled back because it has log operation.

TRUNCATE can not be rolled back because it has no log operation.

3

DELETE is DML.

TRUNCATE is DDL.

4

DELETE command can not reset Identity values because it can rolled back old values then error will be occur due to Identity constraint for column.

Ex : We have EMP Table along with ID column and applied primary key ,identity constraint for that.

By default values will be insert

1,2,3

If we run the command

DELETE FROM EMP

And want to again insert ID values,

Id will be 4.

TRUNCATE command can reset Identity values because it can not be rolled back old values

Ex : We have EMP Table along with ID column and applied primary key ,identity constraint for that.

By default values will be insert

1,2,3

If we run the command

TRUNCATE TABLE EMP

And want to again insert ID values,

Id will be 1.

5

We can create trigger for DELETE .

We can not create trigger for TRUNCATE .

 



2) What is difference between PROCEDURE and FUNCTION ?

 

SNO.

PROCEDURE

FUNCTION

1

We can use INSERT, DELETE, UPDATE, SELECT statements in Procedure.

We can use only SELECT statements in Function.

2

Procedure can return multiple value at a time.

Function can return Single value in Tabular or scalar format at a time

3

We can call function in procedure to return single value for any fields.

We can not call procedure in function.

4

Procedures are in pre-compilation form.

Functions are not in pre-compilation form.

5

Transaction are allowed in Procedure.

Transaction are not allowed in function.

6

EXAMPLE:

 

CREATE PROCEDURE P1 (@ID int) 

AS

BEGIN

SELECT * from Beneficiary where ID=@ID

END

 

EXAMPLE:

 

CREATE FUNCTION F1 (@EMPNO Varchar(10))

Return varchar(10)

AS

begin

 select salary into sal from emp where id=@EMPNO

 return sal;

end;

 

 



3) What is difference between CLUSTER and NON-CLUSTER Index?

 

SNO.

CLUSTER

NON-CLUSTER

1

When We create Primary key on column in table, CLUSTER Index auto created on that column.

We need to manually create NON-CLUSTER Index on column in table

2

We can create only one CLUSTER  index in a table

We can create multiple NON-CLUSTER index in a table.

3

CLUSTER Index is used to reorder physical order of table.

NON-CLUSTER index is used to create logiocal order of data.

4

Cluster index is slower than NON-Cluster Index

NON-Cluster Index is faster than Cluster index.

 



4) What is difference between UNION and UNION ALL?

 

SNO.

UNION

UNION ALL

1

UNION fetches distinct records from tables.

UNION ALL fetches all records included duplicates from tables.

2

UNION is slower than UNION ALL.

UNION ALL is faster than UNION.

3

Syntax :

 

Select Col1, Col2, Clo3 from Table1 

UNION 

Select Col1, Col2, Clo3 from Table2

 

Syntax :

 

Select * from Table1 

UNION ALL

 

Select * from Table2

 

 



5) What is difference between WHERE and HAVING Clause?

 

SNO.

WHERE

HAVING

1

Where clause is used to filter rows.

Having Clause is used to filter Groups.

2

Where clause can be used without GROUP BY

Having clause can not be used without GROUP BY

3

This clause used before GROUP BY

This clause used after GROUP BY

 



6) What is difference between PRIMARY and UNIQUE Key?

 

SNO.

PRIMARY Key

UNIQUE Key

1

Unique key + NOT NULL values

Unique + One NULL Values

2

There can be only one primary in table

We can create multiple unique key in table.

 



7) What is difference between VARCHAR and NVARCHAR datatype?

 

SNO.

VARCHAR

NVARCHAR

1

VARCHAR stores NON-UNICODE data .

NVARCHAR stores UNICODE data .

2

Varchar takes 1 bytes per character

 

Ex:

DECLARE @Name AS VARCHAR(50) = 'POOJA'

SELECT @Name AS FirstName,

DATALENGTH(@Name) AS

Length

 

O/p : 5

NVARCHAR takes 2 bytes per character

 

Ex :

DECLARE @Name AS NVARCHAR(50) = 'POOJA'

SELECT @Name AS FirstName,

DATALENGTH(@Name) AS

Length

 

O/p: 10

 



8) What is difference between CHAR and VARCHAR datatype?

 

SNO.

CHAR

VARCHAR

1

CHAR is fixed length .It takes spaces which declared in DataType

If CHAR(10) , length will be 10.

 

Ex :

DECLARE @Name AS CHAR(10) = 'POOJAAAA'

SELECT @Name AS FirstName,

DATALENGTH(@Name) AS

Length

 

O/p : 10

 

VARCHAR is variable length .It takes spaces

Based on string size.

 

 

EX :

DECLARE @Name AS Varchar(10) = 'POOJAAAA'

SELECT @Name AS FirstName,

DATALENGTH(@Name) AS

Length

 

O/p : 8

2

It accept Character values.

It can accept both Character and Numbers Values.

3

CHAR is faster than VARCHAR

VARCHAR is slower than CHAR

 



9) What is difference between DROP and TRUNCATE?

 

SNO.

DROP

TRUNCATE

1

DROP deletes the data included structure of table.

TRUNCATE deletes the data but structure of table will remain.

2

Syntax :

 

DROP TABLE EMP

 

Syntax :

 

 TRUNCATE TABLE EMP

 

 



10) What is the difference between SUBQUERY and CORRELATED SUBQUERY ?

 

SNO.

SUBQUERY

CORRELATED SUBQUERY

1

Inner query is not dependent on Outer Query.

Inner query and Outer Query is dependent on each other.

2

We can run inner query without using outer query

We cannot run inner query without using outer query

 




Interview Questions and Answers Videos


Types Of databases


DBMS Interview Questions and Answers


DBMS Interview Questions and Answers Part 2


Top 50 SQL Interview Questions and Answers


 


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: 1291

SQL Interview Questions | Part 3

Prashant Surya  |   Oct 30, 2019  |  Views: 1304

SQL interview questions | Part 2

Prashant Surya  |   Oct 24, 2019  |  Views: 1195

SQL interview questions | Part 1

Prashant Surya  |   Oct 22, 2019  |  Views: 2160

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