What are top 30 SQL interview questions with answers?

In my previous articles I have already given the different SQL interview questions. The day by day the SQL interview questions becoming more advanced and difficult. The goal of our website is to help people to crack the SQL interviews with no difficulty. I have collected top 30 SQL interview questions which may ask in latest interviews. These SQL interview questions are important to crack all multinational companies. If you can read the SQL interview questions before interview and take its printout it will be really useful.

1. What is SQL ? (100% asked top 30 SQL interview questions )

Answer:

The SQL stands for structured query language which is the database management language especially used for different database management activities. It is specifically used for database creation, data manipulation, transaction control and so on. There are so many database management systems like Oracle, MySQL which are supporting SQL language.

2. What is DBMS ?

Answer :

DBMS stands for database management systems which is the core system to manage, control the data. The key tasks for database management systems are – create database, update database or manipulate the databases. The database management system is heart of application if it fails the application will not work.

3.What are multiple usages of DBMS?

Answer :

The database management system is used for different purposes :

1.Controlling the database redundancy : The data should not be duplicate and redundant.

2.Different database operations : The database management system is used for multiple database operations to create the database, manipulate the database, to delete the data from database or for controlling the transactions.

3.Database Security : The database management system gives you ability to encrypt and decrypt the data so it is useful by database security perspective.

4.Transaction Control : The DBMS is used for transaction controlling and it will give you ability to control transactions.

4.What are different types of Database Management System? (100% asked top 30 SQL interview questions )

Answer :

There are following types of database management systems :

• Data definition language (DDL): The data definition language also called as DDL which gives user ability to play with database structure like CREATE, ALTER, DROP, etc.
• Data manipulation language (DML): The data manipulation language (DML) will manipulate existing data in the database. The commands in this category are SELECT, UPDATE, INSERT, etc.
• Data control language (DCL): It controls access to the data stored in the database. The commands in this category include GRANT and REVOKE.
• Transaction Control Language (TCL): It is used to deal with the transaction operations in the database. The commands in this category are COMMIT, ROLLBACK, SET TRANSACTION, SAVEPOINT, etc.

5.What is SQL used for?

Answer :

SQL is used to perform following database transactions :

1. User can create a database and tables.
2. User can manipulate the data in the table
3. User can update or delete data from the table
4. User can control the database transactions
5. User can control the access of multiple users who needs to utilize the data.
6. SQL can be used in relational as well as multidimensional databases.

6.What are different database management systems?

Answer :

Database Management Systems	Name of SQL	Full Form of SQL name
Oracle	SQL	Structured Query Language
Oracle	PLSQL	Procedural Language Structured Query Language
IBM DB2	SQL PL	SQL Procedural Language (implements SQL/PSM)
IBM Informix	SPL	Stored Procedural Language
IBM Netezza	NZPLSQL[20]	(based on Postgres PL/pgSQL)
Invantive	PSQL[21]	Invantive Procedural SQL (implements SQL/PSM and PL/SQL)
Microsoft / Sybase	T-SQL	Transact-SQL
Mimer SQL	SQL/PSM	SQL/Persistent Stored Module (implements SQL/PSM)
MySQL	SQL/PSM	SQL/Persistent Stored Module (implements SQL/PSM)
MonetDB	SQL/PSM	SQL/Persistent Stored Module (implements SQL/PSM)
NuoDB	SSP	Starkey Stored Procedures
Oracle	PL/SQL	Procedural Language/SQL (based on Ada)
PostgreSQL	PL/pgSQL	PostgreSQL (implements SQL/PSM)
SAP R/3	ABAP	Advanced Business Application Programming
SAP HANA	SQLScript	SQLScript
Sybase	Watcom-SQL	SQL Anywhere Watcom-SQL Dialect
Teradata	SPL	Stored Procedural Language

7.What is mean by DML? give us an example. (90% asked top 30 SQL interview questions )

Answer :

Data manipulation language makes the user able to retrieve and manipulate data in a relational database management system. The Data Manipulation Language can only perform read-only operations on data. We can perform the following operations using DDL language:

• Insert data into the database through the INSERT command. Example : Insert into Student Values(1,’Amit’)
• Retrieve data from the database through the SELECT command. Example : Select * from Student;
• Update data in the database through the UPDATE command. Example : Update Student Set rollno=1;
• Delete data from the database through the DELETE command. Example : Delete from Student;

8.What is DDL? give us an example.

Answer :

DDL stands for Data definition language. It is the subset of a database that defines the data structure of the database when the database is created. 

Create table : The one of the DDL language statement is create statement.

Example : Create table Student( roll_no Number(10), Name varchar2(30));

Alter database object : The second DDL which works with database structure is alter statement. It will give you ability to alter database objects.

Example : Alter table student add column surname varchar2(30);

Delete Objects : The third DDL statement is delete which gives user ability to delete database objects.

Example : Delete table student;

9.Why do we need to use sql? ( Or Question may be WHY SQL?)

Answer :
 Allows users to access data in relational database management systems.
 Allows users to define the data in database and manipulate that data.
 Allows users to create and drop databases and tables.
 Allows users to create view, stored procedure, functions in a database.
 Allows users to set permissions on tables, procedures, and views.

10.Which are most important SQL commands?

Answer :

SELECT – Extracts data from a database
UPDATE – Updates data in a database
DELETE – Deletes data from a database
INSERT INTO – Inserts new data into a database
CREATE TABLE – Creates a new table
ALTER TABLE – Modifies a table
DROP TABLE – Deletes a table
CREATE INDEX – Creates an index (search key)
DROP INDEX – Deletes an index

11.What are different DCL commands?

Answer :

DCL stands for Data Control Language:

GRANT :Gives a privilege to user
REVOKE :Takes back privileges granted from user.

12.What is RDBMS? (80% asked top 30 SQL interview questions)

Answer :

RDBMS stands for R elational D ata B ase M anagement S ystem. RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access. A Relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd.

13.What is difference between Where clause and having clause with examples?

Answer:

• WHERE clause is used to choose records from the table which satisfies certain conditions and HAVING clause is used to choose records from groups or aggregated row which satisfy given conditions.

• WHERE clause can still function without GROUP BY clause and when used with GROUP BY, is used before it and HAVING clause always require GROUP BY clause and is used.

14.What is mean by database?

Answer:

1) It is a collection of Inter-Related data. Records the data in HDD (Permanent
Memory).
2) Inter-Related data means relation among data values
3) Objective of DB is to record data & save it for future use.

15.What is difference between data transformation and data transportation?

Answer :

Data Transformation: It converts one S/W Data Types into another S/W corresponding Data
Types.
Data Transportation: It carries instructions from front end (S/W application) & transfers to
back end (DB) and vice-versa.

16.What are types of views in SQL?

Answer :

The view in SQL is nothing but the snapshot which will give you data from multiple tables in single go. There are three types of views :

1.Simple View : The simple view is nothing but the view where user can pick the data from single table.

2.Complex view : The complex view is view or snapshot where user can collect the data from multiple tables using joining conditions or any other way.

3.Materialized view :

“Materialized views are also know as snapshots..”

Snapshots acts like a physical table because data from snapshots are storing in to physical memory. Snapshot retrieves data very fast. So for performance tuning Snapshots are used.

17.What is difference between View and Materialized view?

Answer :

View	Materialized Views(Snapshots)
1.View is nothing but the logical structure of the table which will retrieve data from 1 or more table.	1.Materialized views(Snapshots) are also logical structure but data is physically stored in database.
2.You need to have Create view privileges to create simple or complex view	2.You need to have create materialized view ‘s privileges to create Materialized views
3.Data  access is not as fast as materialized views	3.Data retrieval is fast as compare to simple view because data is accessed from directly physical location
4.There are 2 types of views:1.Simple View2.Complex view	4.There are following types of Materialized views:1.Refresh on Auto2.Refresh on demand
5.In Application level views are used to restrict data from database	5.Materialized Views are used in Data Warehousing.

18.What is difference between Union and Union all? (80% asked top 30 SQL interview questions)

Answer :

Union	Union ALL
1.Union Set operator is used to fetch the records from 2 different tables which eliminates the duplicate records	1.Union all Set operator is used to fetch the records from 2 different tables which does not eliminates the duplicate records
2.Syntax:Select col1,col2…from table1;UnionSelect col1,col2…from table2;	2.Syntax:Select col1,col2…from table1;Union allSelect col1,col2…from table2;
3.For Performance tuning Union operator is not preferable as it takes time to eliminate duplicate records	3.Union all is preferable operator in Performance tuning.

19.What is difference between decode and Case statement?

Answer :

Decode Statement	Case Statement
Decode is oracle system function	Case is oracle statement not a function
Decode can not work other than = Equal to operator	Case statement can work other than equal to operator
Decode Statement can not work with the Sub-queries and predicates	Case statement works with sub-queries and predicates
Decode can only be used in function inside SQL only.	Case statement can be used in PL SQL blocks
You can not use Decode function in stored procedures call	You can use case statement in procedure calls
Case statement Compiles ANSI SQL statement	Decode is nothing but the proprietary of oracle

20.What are different types of Joins in SQL? (100% asked top 30 SQL interview questions)

Answer :

1.Inner Join

2.Equi Join

3.Non-Equi join

4.Self Join

5.Left Outer Join

6.Right Outer Join

7.Full Outer Join

21.What is inner join with example? (100% asked top 30 SQL interview questions)

Answer :

When two or more tables connected with each other with specified condition to fetch common records is inner join.

Syntax of Inner Join :

Select Column_name1,Column_name2…Column_name’n’ from Table T1,Table1 T2

Where T1.Column_name=T2.Column_name;

Example of Inner Join :

I would like to give you the inner join real life examples.If user want to fetch the Employee_name and Department_name from Employee table and department table where Emp_no is the joining condition.Following are different examples of inner join:

Type 1 : Using Where Clause

Select E.Employee_name,D.Department_name

From Employee E,Department D

Where E.Emp_no=D.Emp_no;

Type 2 : Using Join Clause

Select E.Employee_name,D.Department_name from

Employee E Join Department D                      —Join Clause

on E.Emp_no=D.Emp_no;                                —On Condition

22.What is difference between Primary key and unique key?

Answer :

Primary Key	Unique Key
Primary Key is nothing but unique identifier for every record for the table	Unique key is also unique identifier for record in the table
Only One primary key is defined for one table	Multiple unique keys can be defined for one table.
Primary key cannot contain null values	Unique key constraint can contain null values
The Unique clustered index can be created once you create primary key	Selection of unique key creates non clustered index
Example : CREATE TABLE Student_table ( RollNo Number (10) PRIMARY KEY, FName Varchar2 (15), LName Varchar2 (15) );	Example : CREATE TABLE Student_table ( RollNo Number (10) UNIQUE, FName Varchar2 (15), LName Varchar2 (15) );
Null value can not be inserted in above table with primary key	Null value can be inserted in table with unique key

23.What is mean by Sequence in database?(80 % asked in Interview Questions)

Answer:

Use the CREATE SEQUENCE statement to create a sequence, which is a database object from which multiple users may generate unique integers. You can use sequences to automatically generate primary key values.

When a sequence number is generated, the sequence is incremented, independent of the transaction committing or rolling back.

Once a sequence is created, you can access its values in SQL statements with the CURRVAL Pseudo Column, which returns the current value of the sequence, or the NEXTVAL Pseudo Column, which increments the sequence and returns the new value.

24.What is SQL subqueries?

Answer :

Subquery is query within a query.There are 2 or more types of queries used to fetch output of the query.One is query used inside the query which is known as inner query and the output of query is given to the query which is used outside.The query used outside is called as outer query.Subquery is made up of combining two types of Queries the first type is outer query and other is inner query.

25.What are types of subqueries?

Answer :

1.Single Row Subquery

2.Multi row Subquery

3.Co-Related Subquery

4.Scalar Subquery

5.Nested Subquery.

26.What are different set operators in SQL?

Answer :

Set operators are nothing but the operators which are used to connect two tables and fetch the records from the two tables.We need to follow one condition that the table set 1 columns and table set 2 columns are same and its datatype must be same.SQL Set Operators combines the result of 2 queries or components on to the single result.

Following are Set Operators in SQL:

1. Union
2. Unionall
3. Intersect
4. Minus

27.What is mean by correlated subqueries?

Answer :

Correlated Query is nothing but the subquery whose output is depending on the inner query used in that query.Correlated query is the query which is executed after the outer query is executed.The outer query is always dependent on inner query.The approach of the correlated subquery is bit different than normal subqueries.In normal subqueries the inner queries are executed first and then the outer query is executed but in Correlated Subquery outer query is always dependent on inner query so first outer query is executed then inner query is executed.Correlated Subqueries always uses operator like Exist,Not Exist,IN,Not IN.

“Correlated Queries are also called as Synchronized queries…”

28. What is mean by Scalar Subqueries?

Answer :

Definition of Scalar Subquery:

A scalar sub-query expression is a sub-query that returns exactly one column value from one row. What if the oracle failed to return scalar sub-query?There are some specific conditions.

Usages of Scalar Query :

1.The scalar sub-queries are most used for removing the outer joins.

2.If user want to aggregate multiple tables then scalar sub-queries are useful.

3.Table insertion based on other table values.

29.What is mean by SQL index with its types?

Answer :

“SQL Indexes are nothing but optional structure associated with the table which may or may not improve the performance of Query”

Types of indexes :

1.Normal index

2.Unique Index

3.Bit Map Index

4.Composite Index

5.B-Tree Index(Oracle considered Normal indexes as B-Tree Indexes)

6.Function Based Index

7.Clustered Index

8.Non-Clustered Index.

30.What is ROWID & ROWNUM?(90 % asked in top 30 SQL Interview Questions )

Answer:

ROWID is nothing but the physical address given to that row which is in hexadecimal format. ROWNUM is nothing but the logical sequence given to the row of that column.

These are above 30 most important and top 30 SQL interview questions for 2022.