Top 44 SQL Interview Questions And Answers

Question 1

What is `PRIMARY KEY`?

Accepted Answer

* A **PRIMARY KEY** constraint is a _unique identifier_ for a row within a database table. 
* Every table _should have_ a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. 
* The primary key constraints are used to enforce _entity integrity_.

Question 2

What is a `VIEW`?

Accepted Answer

A **view** is simply a virtual table that is made up of elements of multiple physical or “real” tables. Views are most commonly used to join multiple tables together, or control access to any tables existing in background server processes.

Question 3

Define a _Temp Table_

Accepted Answer

In a nutshell, a **temp table** is a _temporary storage structure_. Basically, you can use a temp table to store data _temporarily_ so you can manipulate and change it before it reaches its destination format.

Question 4

What is _Normalisation_?

Accepted Answer

**Normalization** is basically to design a database schema such that **duplicate and redundant data is avoided**. If the same information is repeated in multiple places in the database, there is the risk that it is updated in one place but not the other, leading to data corruption.

There is a number of normalization levels from 1. normal form through 5. normal form. Each normal form describes how to get rid of some specific problem.

By having a database with normalization errors, you open the risk of getting invalid or corrupt data into the database. Since data "lives forever" it is very hard to get rid of corrupt data when first it has entered the database.

Question 5

What is the difference between _Data Definition Language (DDL)_ and _Data Manipulation Language (DML)_?

Accepted Answer

* **Data definition language (DDL)** commands are the commands which are used to define the database. **CREATE**, **ALTER**, **DROP** and **TRUNCATE** are some common DDL commands.

* **Data manipulation language (DML)** commands are commands which are used for manipulation or modification of data. **INSERT**, **UPDATE** and **DELETE** are some common DML commands.

Question 6

What is the difference between `TRUNCATE` and `DELETE`?

Accepted Answer

* **DELETE** is a Data Manipulation Language(DML) command. It can be used for deleting some specified rows from a table. **DELETE** command can be used with **WHERE** clause.

* **TRUNCATE** is a Data Definition Language(DDL) command. It deletes all the records of a particular table. **TRUNCATE** command is faster in comparison to **DELETE**. While **DELETE** command can be rolled back, **TRUNCATE** can not be rolled back in MySQL.

Question 7

What is `FOREIGN KEY`?

Accepted Answer

* **FOREIGN KEY** constraint _prevents_ any actions that would destroy links between tables with the corresponding data values. 
* A foreign key in one table _points_ to a primary key in another table. 
* Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. 
* The foreign key constraints are used to **enforce referential integrity**.

Question 8

What is `DEFAULT`?

Accepted Answer

* **Default** allows to add values to the column _if the value of that column is not set_. 
* Default can be defined on number and datetime fields. 
* They cannot be defined on timestamp and `IDENTITY` columns.

Question 9

What is the difference between `INNER JOIN` and `OUTER JOIN`?

Accepted Answer

Assuming you're joining on columns with no duplicates, which is a very common case:

* An **INNER JOIN** of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram intersection.
* An **OUTER JOIN** of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.
* A **LEFT OUTER JOIN** will give all rows in A, plus any common rows in B.
* A **RIGHT OUTER JOIN** will give all rows in B, plus any common rows in A.
* A **FULL OUTER JOIN** will give you the **union** of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.

![](https://i.stack.imgur.com/1UKp7.png)

Question 10

Define ACID Properties

Accepted Answer

* **Atomicity**: It ensures all-or-none rule for database modifications.
* **Consistency**: Data values are consistent across the database.
* **Isolation**: Two transactions are said to be independent of one another.
* **Durability**: Data is not lost even at the time of server failure.

Top 44 SQL Interview Questions

Define a Temp Table

What is PRIMARY KEY?

What is a VIEW?

What is Normalisation?

What is the difference between Data Definition Language (DDL) and Data Manipulation Language (DML)?

What is the difference between TRUNCATE and DELETE?

What is DEFAULT?

What is FOREIGN KEY?

What is the difference between INNER JOIN, OUTER JOIN, FULL OUTER JOIN?

What is the difference between UNION and UNION ALL?

Describe the difference between truncate and delete

What is Denormalization?

What's the difference between a Primary Key and a Unique Key?

What are the difference between Clustered and a Non-clustered index?

How does a Hash index work?

How a database index can help performance?

What is the difference between WHERE clause and HAVING clause?

What is the difference between JOIN and UNION?

What is Collation?

What is the difference between INNER JOIN and OUTER JOIN?

How can VIEW be used to provide security layer for your app?

Discuss INNER JOIN ON vs WHERE clause (with multiple FROM tables)

Define ACID Properties

What’s the difference between Azure SQL Database and Azure SQL Managed Instance?

What is the cost of having a database index?

What is faster, one big query or many small queries?

What is the difference among UNION, MINUS and INTERSECT?

Explain the difference between Exclusive Lock and Update Lock

How does B-trees Index work?

Name some disadvantages of a Hash index

What are some other types of Indexes (vs B-Trees)?

How does database Indexing work?

What is Optimistic Locking and Pessimistic Locking?

What is the difference between B-Tree, R-Tree and Hash indexing?

What is Index Cardinality and why does it matter?

How to select first 5 records from a table?

Find duplicate values in a SQL table

What would happen without an Index?

Delete duplicate values in a SQL table

How can we transpose a table using SQL (changing rows to column or vice-versa)?

How to generate row number in SQL without ROWNUM

How does TRUNCATE and DELETE operations effect Identity?

How can I do an UPDATE statement with JOIN in SQL?

Select first row in each GROUP BY group (greatest-n-per-group problem)?

What is `PRIMARY KEY`?

What is a `VIEW`?

What is the difference between `TRUNCATE` and `DELETE`?

What is `DEFAULT`?

What is `FOREIGN KEY`?

What is the difference between `INNER JOIN`, `OUTER JOIN`, `FULL OUTER JOIN`?

What is the difference between `UNION` and `UNION ALL`?

Describe the difference between `truncate` and `delete`

What is the difference between `WHERE` clause and `HAVING` clause?

What is the difference between `JOIN` and `UNION`?

What is the difference between `INNER JOIN` and `OUTER JOIN`?

How can `VIEW` be used to provide security layer for your app?

Discuss `INNER JOIN ON` vs `WHERE` clause (with multiple `FROM` tables)

What is the difference among `UNION`, `MINUS` and `INTERSECT`?

How to select first `5` records from a table?

How to generate row number in SQL without `ROWNUM`

How does `TRUNCATE` and `DELETE` operations effect Identity?

How can I do an `UPDATE` statement with `JOIN` in SQL?

Select first row in each `GROUP BY` group (greatest-n-per-group problem)?