SQL Interview Questions with Answers – An Ultimate Guide

Q1: What is the difference between a permanent table and a temporary table?

A temporary table is a database table that is used to store the temporary result set. The result set can be reused several times in a single session. But the temporary table is automatically removed when the session ends.

A permanent table is a database tables that retains the data permanently even after the session is terminated.

Q2: What is a View?

 

A view is a virtual table. A view is created using all or subset of result sets from one or multiple tables.

Q3: What are the advantages of using SQL views?

Some of the advantages of views are:

·        Views are virtual tables and hence take very little space to store.

·        Using views, we can join and simplify multiple tables into a single virtual table. It simplifies the complexity of the query.

·        Views are used to keep the sensitive information safe and display only the required data to the user

·        Views can hide the complexity of data. For example, we can create a view by querying the subset of data for a given condition and select required columns only. 

Q4: What is an index?

 

An index is a data structure that is used to improve the speed of data
retrieval from a table or a view.

Q5: What is a constraint? 

A constraint is a rule applied on a column in a table to validate the input data when inserted. For example, if a column has a UNIQUE constraint, then it means the system will not allow if a duplicate value is entered. 

Another example, if a column has a NOT NULL constraint, then it is mandatory to enter a value in that column and it can’t be left blank.

Q6: What are different constraints in SQL? 

Different types of constraints are:

·        Primary Key: Uniquely identifies every single record in a table

·        Foreign Key: Ensures referential integrity between the data in two tables.

·        Not Null: Restricts insertion of Null value into the table

·        Unique: Restricts the duplicate entries into the field

·        Check: Validates that inserted value satisfies the condition  

·        Default: Automatically assigns the default value in the field in case no value is assigned in the field

·        Index: Indexes a field to provide faster retrieval of records from the table

 

Q7: What is a primary key?

A primary key is a constraint on a single or on a combination of column to identify a record uniquely. A primary key column cannot have a NULL value and must contain unique values. There can be only one primary key in a tables.

 

Q8: What is a foreign key?

 A foreign key is a column (or combination of columns) that is used to establish relationship between two tables. The foreign key field of on table refers to the primary key field of another table.

Q9: What is a unique key?

 

Unique key is a constraint that is used to ensures that all values in a column are different or distinct.

Q10: How a primary key is different from unique key
and not null?

 Primary key constraint uniquely identifies every single record in a table. It cannot have a NULL value and must contain a unique value. There can be only one primary key in a table. 

Unique key restricts the duplicate entries into the field. There can be
more than one unique key in a table.

 

Not Null restricts insertion of Null value into the table. There can be more than on Not Null key in a table.

Q11: What is normalization?

 

 

Normalization is the process of decomposing (or organizing) tables to eliminate data redundancy. It is a multi-steps process to divide a large table into smaller ones.

Q12: What is the difference between BETWEEN and IN operator?

 

The BETWEEN operator is used to select a range of data between two
values while the IN operator used to specify multiple values.

Q13: How HAVING clause is different from WHERE clause?

WHERE clause is used to filter records before grouping (for aggregation)
while HAVING clause is used to filter values after they have been grouped.

Hence in SQL queries Where clause is used before Group by and Having is
used after Group by.

 

Having clause is used to apply filter on aggregated values. Where clause
cannot be used to apply filter on aggregated value.

Q14: What is a join?

A join is used to combine data from two or more tables based on a
related field between them.

 Q15: Explain different types of joins.

 Different types of joins are:

·        
Inner
Join : Returns records at the intersection of the two tables.

·        
Left
Outer Join : Returns all records from table A and any matching records from
table B provided A is on the left and B on the right.

·        
Right
Outer Join : Returns all records from table B and any matching records from
table A provided A is on the left and B on the right.

·        
Full Join
: Returns all records from both tables.

·        
Self Join
: Used to join a table to itself as if the table were two tables.

·        
Cross
Join : Cartesian product of the sets of records from the two or more joined
tables.

  

Q16: What is the difference between DELETE and
TRUNCATE commands?

DELETE command removes specified records based on given condition. If
condition is not mentioned with DELETE command then it deletes all records.

TRUNCATE command deletes entire data from the table.

DELETE command can have WHERE clause but TRUNCATE command does not have
WHERE clause.

 Q17: What is the difference between DROP and
TRUNCATE commands?

DROP command removes a table. It is also used to delete a database.

TRUNCATE command deletes entire data from the table but it does not
delete the table (structure).

 Q18: Explain wildcards.

In SQL, wildcard characters are used to find text data with matching
pattern. With wildcards, LIKE operator is used with WHERE clause. 

% (percent sign) and _ (underscore) are two wildcard characters used to
find text data with matching pattern.

 

Q19: What is a UNION operator? 

When we have two or more SELECT statements producing results having same
number of fields, in the same order then we use UNION operator to combine the
results. 

UNION combines the results by appending the output and removes duplicate records (if any).

Q20: What is the difference between UNION and UNION ALL?

UNION combines the results of two or more SELECT queries and eliminates
the duplicate records. While UNION ALL combines the results of two or more
SELECT queries without removing the duplicate records.

Q21: What is COALESCE function?

COALESCE() function is used for returning the first non-null value in a
list of expressions. If all the values in the list evaluate to NULL, then the COALESCE() function returns NULL.

For example: 

The query below will return ID, Name, Phone from
Emp table. The query will return MobileNo as Phone. If MobileNo is Null then it
will return DeskPhone as Phone. If MobileNo and DeskPhone both are Null then it
will return HomePhone as Phone. If all are non-null then all the numbers will
come in the result.

SELECT ID, Name, COALESCE(MobileNo, DeskPhone, HomePhone) As Phone from Emp;

Q22: What is DISTINCT keyword?

DISTINCT keyword is used with SELECT statement to fetch unique records.

 

 

Q23: What are commonly used aggregate functions?

 

The most commonly used aggregate functions are SUM(), COUNT(), MIN(), MAX(), AVG()