SQL Interview Questions and Answers

In this tutorial, I am going to share 48 useful SQL interview questions. It is the top collection of 2021. Even It will be helpful for beginners and experienced developers to get a job in the SQL  database field. So, you must prepare for this tutorial.

You will get SQL Interview Questions from basic to advance level. Here, You have the best option to easily learn it. Each question has the best example that will help you to quickly understand.

 SQL Interview Questions and Answers for Preparing in 2020

I have provided the top 48 SQL Interview Questions that can help you to raise your confidence in the SQL interview round. That’s why You must start to revise from beginning to end with full concentrate and Don’t skip any one of the given SQL questions that may be asked in the interview round.

Read Also

PHP Interview Questions with Answers

JavaScript Interview Questions with Answers

Most Important SQL Interview Questions

This is the collection of the most important SQL Interview Questions with answers and examples. It is more necessary for beginners as well as experienced developers.

Most of the company asked these types of SQL questions in a face to face or telephonic round. So, you must prepare these questions to qualify for the interview.

1. What is SQL?

SQL is a command language that stands for Structured Query Language which lets us handle and access data in the databases.

SQL performs the operations against databases like the following query.

•  Insert Data  INTO Database
• Retrieve Data From Database
• Update Data in Database
• Delete Data From Database

2. Why use DISTINCT Statement in SQL?

DISTINCT Statement is used to select different data from the duplicate data of the column. Even This statement always comes with the Select statement.

Suppose that a column of a table named “boy_name” contains Many duplicate Names. such as Mohan, Mohan, Radha, Mohan, Radha, Amit, Mohan, Mohan.

Table Name – boys
Column Name – boy_name

Now We Have to select different Names from the duplicates name through the following query.

SELECT DISTINCT boy_name from boys;

Then after executing this query, we get the output- Mohan, Radha, Amit.

3. What is the different type of SQL statements?

There are three types of statements are given below-

• DDl – It stands for Data Definition language used to make the structure of a database with create, alter & drop statement
• DML – It stands for Data Manipulation statement used to control data of tables with insert, update & delete statements.
• DCL – It stands for Data control language used to control data of tables for security and concurrent access with Grant and Revoke statements.
• TCL – It stands for Transaction Control Language used to deals with a transaction within the database with a commit, rollback, save point, and set transaction statements

4. What is the difference between VARCHAR and VHAR?

Both are the datatype of characters of a string.

VARCHAR

• VARCHAR is used to store the variable length of a string.
• For example, if we define datatype as varchar(10) then we can store Maximum 10 length of the string as 2,4,5,8,10 etc.

CHAR

• CHAR is used to store the fixed length of a string.
• For example, if we define datatype as char(10) then Wen can only 10 lengths of a string. if we try to store 2 or 4 or 5 or 8 etc then it will produce an error.

5. What is the difference between ORDER BY and GROUP BY?

ORDER BY

• ORDER BY is used to arrange columns data of a table in ascending or descending order.
• By default, It gives results in ascending order either we use ASC or not. But for descending order, We have to use order by DESC in the SQL query.

Example

See the below table named “employees” from which data is selected in descending order.

SELECT * FROM employees ORDER BY salary DESC;

GROUP BY

• GROUP BY is used to group the duplicate data of a column by making unique
• It calculates the result of their corresponding column data by using the following aggregate functions.

• SUM – To find some of data
• AVG – To find the average of data
• MIN – To find minim number of data
• MAX – To find the maximum number of data

Example –

See the below table named “students”. We have to find the total number of students in each city.

Query –

SELECT COUNT(student_id),city from students GROUP BY city

Output – See the output in below table

6. What is the difference between WHERE Clause and HAVING Clause?

Both Clauses are used to access those records that match given conditions. But the difference between where and having clause are given below

WHERE Clause- it can’t be used with aggregate functions

Query –

SELECT * FROM table_name WHERE condition;

HAVING Clause – It can be used with aggregate functions

Query –

SELECT COUNT(Column_name) FROM table_name GROUP BY column_name HAVING condition;

7. What is different between IN Operator and BETWEEN Operator?

IN Operator

IN Operator is used to define multiple values for multiple conditions with where clause.

Query –

SELECT * FROM table_name WHERE column_name IN( Value1, Value2.....);

BETWEEN Operator

BETWEEN Operator is used to defining a range between two values with where clause.

Query –

SELECT * FROM table_name WHERE column_name BETWEEN value1 AND value2;

8. What is an inner join?

Inner join is used to combine the field of two or more tables if Those tables have common fields and keep those rows that common fields data are exactly matched with other common fields.

• Example –
  We have two join tables named “boys” and boy_interest respectively using inner join.see the below table.

Table Name – boys

Table Name- interests

Query-

SELECT boy_id,boy_name,boy_interest from boys INNER JOIN interests  ON boy.boy_id=interests.boy_id;

Output- See the output below table.

9. What is a self join?

In the case of self join, We can join a table itself.

Example

We have a table named “boys” and We have to select those names that age is matched with Sunil age.

Table – boys

Query-

SELECT b2.name Name,b2.city City FROM boys b1 INNER JOIN boys b2 ON b1.age=b2.age AND b1.name="Sunil";

Output – See the output just below the table.

10. What is the Union?

The union is used to join two or more tables and returns into a single table but given conditions must be fulfilled such as-

In each select statement, the number of columns must be the same.

The data type of the corresponding column of each table must be the same.

By default, It can only select unique data and it ignores duplicate data.

Query-

SELECT name from table_name1 UNION SELECT name from table_name2;

11. What is Natural Join?

Natural join combine those tables have a common column with the same column name.

Query-

SELECT column_name FROM table1 NATURAL JOIN table2

12. Difference between unique key and primary key?

The unique and primary key is used to store unique data in a column of a table but the difference between them is a unique key can specify multiple times in a table but the primary key only one time.

13. What is the foreign key

The foreign key is defined to connect one table to another table. If a column of a table has the foreign key then the corresponding column of another table should have a primary key.

14. What is the Composite Key?

A composite key is used to define uniquely two primary key columns of a table. This means that A table has two primary key columns is known as a composite key.

15. What is SQL injection?

The SQL injection is a web hacking technique that can hack data from the table of a database and It can also destroy the database to insert hacking code in SQL query.

16. What is different between NULL, Zero, and BLANK?

NULL- null is neither a number nor a blank space.it only indicates the value is not available or known.
Zero – Zero is a number known as a value.
Blank- Blank is a space that may count as a character

Basic SQL Interview Questions for Beginners

This is the collection of the basic SQL Interview Questions with answers and examples. It is more necessary for beginners and experienced developers.

These types of SQL questions are asked in a face to face or telephonic round. So, you must prepare these questions to face the interview

1. What is Database?

The database is a collection of tables and it keeps the structure of the tables into the organized form to access, retrieve, and managing data in may ways.so it is known as a structured form of data.

Example- College Database, Hospital Database

2. What are the tables of a database, fields, and record?

Table – A table is a collection of data that is stored in columns and rows format. Columns are represented as vertical and rows horizontal direction.

Fields – A table contains the specified number of the column which is known fields.

Records – A table can contain any number of rows which is known as records.

 Example –

Table Name- Students

This table can have many records but above, only 2 records & 4 Fields are given to understand this example.

3. What is SQL alias?

Alias is a temporary name of column name and table name. It is mostly written in select SQL query with “ AS “ and makes table name & column name more readable.

4. How to Create a database?

We can create a new database by using the given query as-

CREATE DATABASE database_name;

5. How to drop a database?

We can remove an existing database by using the given query as-

DROP DATABASE database_name;

6.How to backup a database?

We can take full backup an existing database to the given location by using the given query as-

BACKUP DATABASE; database_name TO DISK =” file location”;

7. How to create a table?

We can create a new table in a database by using the given query as-

CREATE TABLE table_name (Column_name1 datatype,Column_name2 datatype,Column_name3 datatype);

8. How to Drop a table?

We can remove an existing table from the database by using the given query as-

DROP table tableName;

9. How to Alter a Table?

We can add, delete, or update a column of a table by using the given query as-

ALTER TABLE table_name ADD column_name datatype;

10. What is the difference between DROP and TRUNCATE?

In the case of DROP, A Table can be removed from the database.

In the case of TRUNCATE, All rows can be removed from the table.

11. How to insert data into a table in SQL?

INSERT INTO table_name (column_name1, column_name2,…) VALUES (‘column_data1’, ’column_data2’…..’);

12. How to update data into a table in SQL?

UPDATE table_name SET Column_name1 =’column_data1’, Column_name2 =’column_data2,....., WHERE condition;

13. How to delete data into a table in SQL?

DELETE FROM  table_name WHERE conditions;

14. How to select data from a table?

If Select all data from the table –

SELECT * FROM table_name;

if select two data from the table –

SELECT column_name1, column_name2 FROM table_name;

15. What are the constraints?

Constraints are used to apply conditions for the data in a table while creating that table.

CREATE TABLE tableName(Column_name1 datatype constraintColumn_name2 datatype constraint);

Advanced SQL Interview Questions for Experienced

This is the collection of the basic Advanced SQL Interview Questions with answers and examples. It is more necessary for experienced developers. But Beginner developers may prepare these types of questions.

1. What are the scalar functions?

There are six scalar functions that mostly used in SQL such that-

ROUNT() – It is used to convert the decimal value of a column into a number.

LEN() – It is used to find the total length of the content column.

UCASE() – It is used to convert data of columns into uppercase.

LCASE() – It is used to convert data of columns into lowercase.

MID- It is used to return by extracting data from the content field

Organization()- It is used to specify the arrange

2. What are the common Aggregate functions and their uses?

There are five common aggregate functions are given below

COUNT – It used to get the total number of records in a particular column of a table.

SUM – It used to get the Addition of records in a particular column of a table.

AVG – It used to find the average value of records in a particular column of a table.

MIN – It is used to find the lowest value of records in a particular column of a table

MAX – it is used to find the greatest value of records in a particular column of a table.

3. What is different between Like and Wildcard?

Wildcard- It is a character that is used to add one or more characters in a string that used with Like operator.
There are two common wildcards:-

Percentage ( % ) – it is used for zero, one, or multiple characters of a string.

Underscore ( _ ) – it is only used for a single character.

Example –

X% – It means any data that begin with X
%X – It means any data that end with X
X%X% – means any data that have X character is placed at any position of a string
_X  – It means any data that have X character is placed at the second position of a string.
X__% – It means any data begin with X and have at least 3 characters in the length of a string
X%Y – It means any data that begin with X and end with Y

Like – It is an operator that used to search data for a given pattern in a column with where condition.

Query-

SELECT * FROM table_name WHERE condition LIKE pattern

4. What is join & its different types?

Join is used to combine two or more tables either those have a common column or not

5. What is LEFT INNER JOIN?

Left inner join is used to combine the field of two or more tables. But a single column of those tables must be common.

It joins all the rows of the left table with the corresponding rows of the right table. But It has the following conditions

• If the left table has more rows than the right table rows, the remaining rows of the left table will be NULL.
• If the left table has fewer rows than the right table rows, the remaining rows of right will not be included.

Example –

We have the same table of the above Question no-20. Now, We have to join both tables by using the LEFT INNER JOIN.

Query-

SELECT boy_id,boy_name,boy_interest from boys LEFT JOIN interests  ON boy.boy_id=interests.boy_id;

Output – See the output just below table-

6. What is RIGHT INNER JOIN?

Right inner join is used to combine the field of two or more tables. But a single column of those tables must be common.

It joins all the rows of the right table with the corresponding rows of the left table. But It has the following conditions

• If the right table has more rows than the right table rows, the remaining rows of the right table will be NULL.
• If the right table has fewer rows than the right table rows, the remaining rows of the left table will not be included.

Example –

We have the same table of question no-20, Now, We have to join both tables by using RIGHT INNER JOIN.

Query- 

SELECT boy_id,boy_name,boy_interest from boys RIGHT JOIN interests  ON boy.boy_id=interests.boy_id;

Output- See the result just below the table

7. What is a full inner join?

Full inner join is a combination of left join and right join.

Right inner join is used to combine the field of two or more tables. But a single column of those tables must be common.

It joins all the rows of the right table with the corresponding rows of the left table & vice versa

• If the right table has more rows than the right table rows, the remaining rows of the right table will be NULL.
• If the right table has fewer rows than the right table rows, the remaining rows of the left table will be NULL

Example –

We have the same table of question no-20, Now, We have to join both tables by using RIGHT INNER JOIN.

Query-

SELECT boy_id,boy_name,boy_interest from boys FULL JOIN interests  ON boy.boy_id=interests.boy_id;

8. What is a cross join?

A cross join combines each row of a table with each row of another table.

SELECT *FROM table1 CROSS JOIN table2;

or

SELECT column1, column2 FROM table1 CROSS JOIN table2;

or

SELECT * FROM table1,table2;

Example

We have two tables named “boys” and “interests”. we have to join these tables by using a CROSS JOIN.

Table – boys

Table -interests

Query-

SELECT  name, interest from boys CROSS JOIN interests;

Output– See the output just below the table

9. What is normalization and explain different types of common normalization?

Normalization is a concept to minimize duplicate data of tables.

There are three common normalizations that mostly used as-

• First Normal Form-1NF
• Second Normal Form-2NF
• Third Normal Form- 3NF

1First Normal Form- 1NF

1NF stands for First Normal Form

It does not allow repeating of the same group of data inside a column.

Example –

This table is not in 1NF

Above the table is normalized into 1NF is given below two separated tables

Second Normal Form- 2NF

2NF stands for Second Normal Form

2Nf type of table must follow the given rule such as:-

•  The table must be in 1NF
• A table has a non-key column that must depend on its primary key column.

Example –Table-students

This table is not in 2NF

Above the table is normalized into 2NF is given below two separate tables.

Third Normal Form- 3NF

3Nf stands for the Third Normal form.

3NF type of table must follow the given rule such as

• The table must be in 1NF & 2NF.
  A table has non-key columns that must not depend on another non-key column.

Example –

Previous two tables of 2NF are normalized into 3NF is given below three separated tables

10. What is the table relationship?

In the case of table relationship, a table can relate to another table with a foreign key.

Suppose that – We have A boys table named boys

Second Boys Interest table named interests

Then we can say that the boy’s table and interest table are in the relationship because interest table data belongs to the boys’ table.

11. Explain One to one relationship

In the case of one relationship, one data of a table belongs to only one data of another table

Example- A person can have only one Aadhar card-

12. Explain One too many relationships

In the case of one to many relationships, one data of a table can belong to many data of another table

For example- A Mother can have many children

A customer can order any product

13. Explain Many to many relationships

In the case of many to many relationships, many data of a table can belong to many data of another table

For example- many friends can have many friends Such as Sumit & Radha are friends of Amit & Monika

14. Explain the different types of common constraints?

There are seven common constraints that are given below-

Primary Key- it is used to define uniquely column data of a table with NOT NULL and it may define only one time in a table

UNIQUE key– It is also used to define uniquely column data with NOT NULL and it may define many times in a table.

FOREIGN KEY –It is used to relate one table to another table when a column of a table holds the primary key and another foreign key.

NOT NULL– It doest not allow columns of a table to hold NULL data.

CHECK– It is used to check a given condition for the table data.

DEFAULT- It can hold default value for a column when no value is declared.

INDEX– It can hep to insert and select data from the table very fast.

15. What is the CASE statement in SQL?

A case statement can apply for table data with one or more specified conditions. If one condition is satisfied then stop the remaining conditions

SELECT name, age,
CASE
WHEN age>=30 THEN “this boy is above 30 years old”
WHEN age= 30 THEN “this boy is 30 years old”
ELSE “this boy is under 30”
END AS text FROM boys;

16. What is Stored Procedures?

It is a procedure to save SQL queries for using that query multiple times. When we save our query as stored procedures then we can call anywhere to execute.

Example-

CREATE PROCEDURE allBoysDetails AS SELECT * FROM CustomersGo;

To execute query-

EXEC allBoysDetails;

1. Find 10 records after 20 records from 50 records of a table?

SELECT * from boys limit 20, 10

17. Find the second-highest record from 50 records of a table?

SELECT * from boys order by DESC limit 1, 1

Suggestion

Dear developer, I have shared the top SQL interview Questions according to my working experience. If you will prepare the given questions, you will definitely qualify the interview.