SQL Developer Interview Questions :

SQL Interview Book DOWNLOAD FOR FREE

In my previous articles I have already explain  number of interview questions which are equally important to SQL Developer. This article will give you dedicated SQL Developer Interview Questions in 2021. SQL Developer is responsible for all the database related tasks in SQL.SQL Developer Interview Questions are set of common SQL questions which are asked most frequently in interviews. SQL developer is not only responsible for SQL task but also he/she should have knowledge of PL SQL also. In this article i will try to give brief idea about SQL Developer Interview Questions which are not only SQL but also PL SQL questions. Following are set of SQL Developer Interview Questions which are commonly asked during the interviews :

1.What is SQL?[100 % asked  SQL Developer Interview Questions ]

Answer: 

SQL Stands for Structured Query Language which is specially designed to communicate with databases.SQL pronounced as Sequel is very widely used language in most of the database management systems like Oracle,MySQL,PostgreSQL etc.SQL provides us  a simple and efficient way of reading,writing,executing the data from the system.this is one of the SQL Interview Question ever asked in interviews.

2.What is Normalization?(100% asked SQL Developer Interview Questions )

Answer :

Database Normalization is organizing non structured data in to structured data.Database normalization is nothing but organizing the tables and columns of the tables in such way that it should reduce the data redundancy and complexity of data and improves the integrity of data.

Database Normalization is nothing but technique of designing the database in structured way to reduce redundancy and improve data integrity.

3.What is purpose of Normalization?

Answer :

Normalization is used for following purpose:

• To Eliminate the redundant or useless data
• To Reduce the complexity of the data
• To Ensure the relationship between tables as well as data in the tables
• To Ensure data dependencies and data is logically stored.

4.What is Null in SQL?

Answer:

A NULL value in a table is a value in a field that appears to be blank, which means a field with a NULL value is a field with no value.

It is very important to understand that a NULL value is different than a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation.

5. What is not null constraint?

Answer:

By default, a table column can hold NULL values. The NOT NULL constraint enforces a column to NOT accept NULL values.

The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field.

6..What is the output of following Query?

select case when null=null then ‘Rohit’ Else ‘Ayan’ from dual;

Answer:

In SQL null value is not equal to itself.So null=null is false and the output of above query is ‘Ayan’.

7.What is Self join?

Answer :

Self join is nothing but the table joins with itself. There are lot of tables which contains more than one functionality at that time the concept of self join comes to the picture.

Syntax:

SELECT a.column_name, b.column_name…
FROM table1 a, table1 b
WHERE a.common_field = b.common_field;

Real Example:

If one table contains Employee and its position.If we need to fetch the Employees and its managers then self join is used.

8.How to find that selected column is indexed or not?

Answer:

To find out the selected column is indexed or not there are following 2 ways

1.Selecting Explain plan:

Select that specific table and check the explain plan of the table.But if that select statement is not using the index then it will not show the column is indexed or not.

2.Using System tables:

You can use the system table all_ind_columns and all_indexes table to check that column is indexed or not.This is the best way to find out column is indexed or not.

Select * from all_ind_columns where table_name=’Name of the table’ and column_name=’Name of column’;

If the column is indexed then you will get the output.

9.What is mean by TCL? Explain with examples.

Answer:

A transaction can be defined as the sequence task that is performed on databases in a logical manner to gain certain results. Operations performed like Creating, updating, deleting records in the database comes from transactions.

In simple word, we can say that a transaction means a group of SQL queries executed on database records.

There are 4 transaction controls such as

• COMMIT: It is used to save all changes made through the transaction
• ROLLBACK: It is used to roll back the transaction such as all changes made by the transaction are reverted back and database remains as before
• SET TRANSACTION: Set the name of transaction
• SAVEPOINT: It is used to set the point from where the transaction is to be rolled back

10.How many types of privileges available in SQL? Explain.

Answer:

There are two types of privileges available in SQL. One is system privilege other is User privilege.

• System Privilege: System privileges deal with an object of a particular type and specifies the right to perform one or more actions on it which include Admin allows a user to perform administrative tasks, ALTER ANY INDEX, ALTER ANY CACHE GROUP CREATE/ALTER/DELETE TABLE, CREATE/ALTER/DELETE VIEW etc.
• Object Privilege: This allows to perform actions on an object or object of another user(s) viz. table, view, indexes etc. Some of the object privileges are EXECUTE, INSERT, UPDATE, DELETE, SELECT, FLUSH, LOAD, INDEX, REFERENCES etc.

11.Does SQL support programming language features ? If yes ?How?

Answer :
Yes SQL language supports the programming language. But SQL is Database management system rather than the actual programming language.SQL has its extension language which is PL SQL language which supports conditional looping statement,If ..else statements. SWe do not have conditional statements in SQL like for loops or if..else, we only have commands which we can use to query, update , delete etc. data in the database. SQL allows us to manipulate data in a database.

12.What are different types of indexes?( 80 % asked in SQL Developer Interview Questions )

Answer:

There are following types of indexes:

1.Normal Indexes

2.Bit map indexes

3.B-tree Indexes

4.Unique Indexes

5.Function Based Indexes.

13.How to find count of duplicate rows? (95% asked in SQL Developer Interview Questions )

Answer:

Following Query will give you the count of duplicate rows in SQL Statement :

Select rollno, count (rollno) from Student

Group by rollno

Having count (rollno)>1

Order by count (rollno) desc;

14.How to display following using query?

*

**

***

Answer:

We cannot use dual table to display output given above. To display output use any table. I am using Student table.

SELECT lpad (‘*’, ROWNUM,’*’) FROM Student WHERE ROWNUM <4;

15.What is difference between NVL,NVL2 and Nullif?( 80 % asked in SQL Developer Interview Questions )

Answer:

1.NVL :

NVL function substitutes a value when a null value is encountered.

2.NVL2 :

NVL2 substitutes a value when a null value is encountered as well as when a non-null value is encountered.

3.NULLIF:

NULLIF function compares expr1 and expr2. If expr1 and expr2 are equal, the NULLIF function returns NULL. Otherwise, it returns expr1.

16.What is query to calculate second highest salary of the employee using analytical function?

Answer:

We need to use Dense_Rank function to calculate second highest salary:

select * from (Select Dense_Rank() over ( order by  salary desc) as Rnk,E.* from Employee E) where Rnk=2;

17.How to get number of Weekends of current month?

Answer:

SELECT count (*) AS Weekends FROM

(SELECT TRUNC (SYSDATE,’mm’) +LEVEL-1 Current_dt

FROM Dual

CONNECT BY LEVEL <= last_day (SYSDATE) – TRUNC (SYSDATE,’mm’) +1

)

Where TO_CHAR (Current_dt,’dy’) IN (‘sat’,’sun’);

Let us Fragment the Query for Understanding,

Step 1:  Try running internal query

SELECT TRUNC (SYSDATE,’mm’) +LEVEL-1 Current_dt

FROM Dual

CONNECT BY LEVEL <= last_day (SYSDATE) – TRUNC (SYSDATE,’mm’) +1;

The query will give the all the dates from first to last of current date.

Step 2: To count the weekends.

From all the month, we need to calculate the weekends. Weekends means the Saturdays and Sundays from the month. So here, we need to use To_char function and ‘dy’ attribute of that function to calculate days. Therefore, we have used Where TO_CHAR (Current_dt,’dy’) IN (‘sat’,’sun’); condition.

Therefore, Final Query will be,

SELECT count(*) AS Weekends FROM

(SELECT TRUNC (SYSDATE,’mm’) +LEVEL-1 Current_dt

FROM Dual

CONNECT BY LEVEL <= last_day (SYSDATE) – TRUNC (SYSDATE,’mm’) +1

)

Where TO_CHAR (Current_dt,’dy’) IN (‘sat’,’sun’);

18.How to convert seconds in to time format?( 80 % asked in SQL Developer Interview Questions )

Answer:

SELECT

TO_CHAR (TRUNC (2700/3600),’FM9900′) || ‘:’ ||

TO_CHAR (TRUNC (MOD (2700, 3600)/60),’FM00′) || ‘:’ ||

TO_CHAR (MOD (2700, 60),’FM00′)

FROM DUAL;

Where 2700 is seconds.

Output:

00:45:00

19.What is mean by Aggregate functions in SQL? Tell me with examples.

( 90% asked in SQL Developer Interview Questions )

Answer :

SQL aggregate functions are the functions where user can give multiple values as input but function always returns single value as output.

Aggregate Functions are the Functions where the value of input is grouped together and fetches the output as a single value

Following is single line Explanation of the Aggregate functions:

• AVG() – Returns the average value
• COUNT() – Returns the number of rows
• MAX() – Returns the largest value
• MIN() – Returns the smallest value
• SUM() – Returns the sum

20.What is difference between ‘Between’ operator and ‘In’ operator?( 80 % asked in SQL Developer Interview Questions )

Answer: 

BETWEEN Operator :
The BETWEEN operator is used to fetch rows based on a range of values.

Example :

SELECT * FROM Students WHERE ROLL_NO BETWEEN 20 AND 30;

This query will select all those rows from the table Students where the value of the field ROLL_NO lies between 20 and 30.
IN Operator :
The IN operator is used to check for values contained in specific sets.

Example :

SELECT * FROM Students WHERE ROLL_NO IN (20,21,23);

This query will select all those rows from the table Students where the value of the field ROLL_NO is either 20 or 21 or 23.

21.What is difference Between Intersect and Minus set operators?

Answer:

Intersect	Minus
1.Union Set operator is used to fetch the common records from 2 different tables .	1.Minus Operator is used to fetch the records from first table which eliminates common records.
2.Syntax:Select col1,col2…from table1; Intersect Select col1,col2…from table2;	2.Syntax:Select col1,col2…from table1; Minus Select col1,col2…from table2;
3.For Performance tuning Intersect operator is not preferable as it takes time to fetch duplicate records	3.Minus operator is preferable operator in Performance tuning.

22.What is the difference between SQL and MySQL?( 80 % asked in SQL Developer Interview Questions )

Answer :

SQL is a standard language which stands for Structured Query Language based on the English language whereas MySQL is a database management system. SQL is the core of relational database which is used for accessing and managing database, MySQL is an RDMS (Relational Database Management System) such as SQL Server, Informix etc.

23.Explain the difference between Clustered and non clustered indexes?

Answer:

• One table can have only one clustered index but multiple nonclustered indexes.
• Clustered indexes can be read rapidly rather than non-clustered indexes.
• Clustered indexes store data physically in the table or view and non-clustered indexes do not store data in table as it has separate structure from data row

24.What is difference between view vs materialized view?(100% asked Interview Questions for Deloitte)

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 privilges 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 fom directly physical location
4.There are 2 types of views:1.Simple View 2.Complex view	4.There are following types of Materialized views:1.Refresh on Auto 2.Refresh on demand
5.In Application level views are used to restrict data from database	5.Materialized Views are used in Data Warehousing.

25.What is inner join? Explain with Business use?( 80 % asked in SQL Developer Interview Questions )

Answer:

When 2 tables are connected such that it should retrieve only the matching records in both tables.Inner join select only the matching records between 2 tables.You can use Equal to(=) operator or Inner join keyword to apply inner join.This join is most widely used joins in real life applications,reporting,webapps,android apps.

Inner join is nothing but fetching common records from two or more tables.

Click Here to get information about Joins..

These are above some important SQL Developer Interview Questions which are useful for SQL developers.Hope you like this article on SQL Developer Interview Questions.If you have any suggestions kindly comment in to comment section.