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 2 

Prashant Surya  |   Oct 24, 2019  |  Views: 1191
pictures

Description:

1) What are advantages and Disadvantages of Indexes?


Advantages

  • Increase speed to fast search of data like Index page in Books.
  • Cluster Indexes only apply for Unique Columns so It help to fetch unique rows.
  • Helps to sort records in ascending or descending order.
  • Multiple Non-Cluster Indexes can be create on columns in a single table so that it could help to fast execution of query.



Disadvantages

  • Indexes take more disk space so So DMLs like Insert, Delete, and Update statement becomes very slow.
  • Indexes slows down the query performances.

 


2) What will be output of below Query for the following tables?


Table: A                             Table: B

 

ID

 1

1

2

2

3

3

3

ID

1

1

2

2

3

3

3

 

 

Query : 

SELECT * FROM A INNER JOIN B ON A.id=B.ID

Answer : Output of above query will be shown in below screenshot.

 


 

 


3) What are the Magic Tables?

 

 Magic Tables are those tables which are shown as Inserted and Deleted Tables in Trigger.

 

l  If you insert data in table to implement Trigger , That Inserted Tables will be as  Inserted Magic Tables.

l  If you delete data from table to implement Trigger , That Deleted Tables will be as  Deleted Magic Tables.

 

Create Trigger T1
on EMP
for Insert
AS
 BEGIN
Select * from INSERTED
end

 

In above query INSERTED table will be Magic Table Just like DELETED Tables.

 



4) What is the ACID Properties?

 

Answer: ACID stands for Atomicity Consistency Isolation Durability

 

 

Atomicity: All transactions should be complete or none (Rolled back)

 

Ex : If we are running two query . one is correct and other is wrong , then both will be rolled back.

If both query are correct , both will succeeded

 

Consistency : Data should be in consistent state

 

Ex : If we are updating data by using one query suddenly there is a system failure (right before the insertion into sales or in the middle).

In this situation system will rollback the updates Otherwise, you can’t trace out the stock information.

 

Isolation: Transaction are independent

 

Ex : If we begin one transaction to update data in single statement and Other query also executed after in other window then it will not return data .

 

One transaction can’t access the result of other transaction until the transaction is completed.

 

Durability: Committed data is never lost

 

Once the transaction is successfully completed, then the changes it has made to the database will be permanent.

Even if there is a system failure, or any abnormal changes also, this SQL acid property will safeguard the committed data.




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

SQL Interview Questions | Part 4

Prashant Surya  |   Nov 2, 2019  |  Views: 1290

SQL Interview Questions | Part 3

Prashant Surya  |   Oct 30, 2019  |  Views: 1301

SQL interview questions | Part 2

Prashant Surya  |   Oct 24, 2019  |  Views: 1192

SQL interview questions | Part 1

Prashant Surya  |   Oct 22, 2019  |  Views: 2149

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