What are SQL interview questions for data analyst in 2021?

I have checked and studied lot of website and taken the opinion of experienced professionals and come up with Interview Questions for SQL.In Previous article i have explained the interview questions for Tech mahindra and interview questions for IBM.In website you will get different interview questions related to Performance Tuning of Database also. In this article i would like to explain simple SQL interview questions for data analyst in 2021. These are just basic questions and you can check the data analyst interview questions here.

CLICK HERE TO GET INTERVIEW QUESTIONS FOR TCS…

Following are some important Interview Questions for SQL :

1.What is SQL?Where SQL is used?

Answer:

SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in relational database.SQL can be used in different languages or database management systems like Oracle,SQL Server,Postgresql.

Click Here to Get Information of Oracle..
2.What are different versions of Oracle? What version you are using in your project?

Answer:

Following are different versions of Oracle:
In 1977, SEL (Software Development Laboratory) … V1
In 1979, RSI (Relational S/W Incorporation) …. V2
In 1983, Oracle Corporation à Oracle 3 [Developed Using ‘C’, which supports simple queries but does not support transactions]
In 1984, Oracle 4 à Supports Transactions [Commit/Rollback]
In 1985, Oracle 5 à Client-Server Architecture [Only install DB in Server, so that ‘N’ no of Clients can connect is known as Client-Server Architecture].
In 1989, Oracle 6 à PL/SQL
In 1992, Oracle 7 à Supports DWH [OLAP-Online Analytical Processing]
In 1997, Oracle 8 à ORDMBS
In 1999, Oracle 8i à ‘I’ means Internet & it has inbuilt JVM (JAVA Virtual Machine)
In 2001, Oracle 9i à with 400 New features, e.g. XML (Xtended Markup Language), RAC (Real Application Clusters) etc which provided high availability & performance.
In 2003, Oracle 10g à ‘g’ means grid (group of DB Servers)
In 2006, Oracle 11g àwe can add columns with values etc.

We are using the latest version of oracle ,which is Oracle 12 C in our project.

3.Tell me 2-3 Features added latest of Oracle 11G Version?

Answer:

Following are some features which are added in Oracle 11 G Version:

1.Alter table with Default values

2.New datatypes added ‘Simple Integer’, ‘Simple Float’ and ‘Simple Double’

3.Invisible Indexes

4.Read only Mode of Table

Click Here to get brief information about features of Oracle 11 G..

4.Explain 1 feature which you are using in project? (Mostly asked in Interview Questions For SQL)

Answer:

We have used listagg function and nth val function in our project which has following explaination:

Analytical Functions-NTH,LISTAGG:

Oracle 11 G specifies two new aggregate functions:

1.LISTAGG:
LISTAGG aggregates a column values in a single row format.

2.NTH_VALUE:

NTH_VALUE is an extended format of FIRST_VALUE and LAST_VALUE functions to get a random row from a grouped result set.

Syntax:

LISTAGG – LISTAGG (measure_expr [, ‘delimiter_expr’]) WITHIN GROUP (ORDER
BY clause) [OVER PARTITION BYclause]

Real Life Scenario:

Kindly consider following table.We need to find aggregate results departmentwise in form of rows.

Employee table:

Employee_num	Employee_name	Department ID	Salary
1	Amit	100	680000
2	Rohan	100	550000
3	Rohit	101	430000

Query used:

select Department_ID,listagg(Employee_name,’,’) within group(order by Employee_name) as Employee_name from Employee group by Department_id;

Output:

Department ID	Employee_name
100	Amit,Rohan
101	Rohit

So listagg function is very useful function for converting columns in to single row format.

5. What is index in SQL? (90 % asked in every Interview Questions For SQL)

Answer:

“Index is optional structure associated with the table which may or may not improve the performance of Query”

“In simple words suppose we want to search the topic in to book we go to index page of that book and search the topic which we want.Just like that to search the values from the table when indexing is there you need not use the full table scan.”

6.What are different types of indexes?(Mostly asked in Interview Questions For SQL)

Answer:

There are following types of indexes used while Indexing in SQL:

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.

Click here to get information for Indexes in SQL…

7.How do you find current date in oracle? Explain with example.

Answer:

To fetch the current date in oracle there is on function named ‘Sysdate’.

Example:

select sysdate fron dual;

8.How to fetch the date in “YYYY-MM-DD” format with its timestamp.

Answer:

SELECT TO_CHAR (SYSDATE, ‘YYYY-MM-DD HH24:MI:SS’) “Current_Date” FROM DUAL;

9.What is tablespace in Oracle?

Answer:

The oracle database have logical storage space is called as tablespace.A tablespace is a set of related logical structures. Actually a tablespace groups related logical structures together.

10.What is Oracle table?

Answer:

Table is nothing but Physical data object which has rows and columns.

Syntax:

Create table tablename

(columnname datatype1(size),

columnname datatype2(size));

Click Here to get information about different ways of creating table.

11.What is subquery?

Answer:

Subquery is query within query.The output of outer query is assigned to the column which is used in where condition of outer query.The subquery output is returning only one output value and based on that output value the outer query is executed.

12.What are different types of Subqueries?(Mostly asked in Interview Questions For SQL)

Answer:

Following are types of subqueries:

1.Single Row Subquery

2.Multirow Subquery

3.Correlated Subquery

4.Nested SubQueries

5.Scalar Subqueries

Click here to get information about Subqueries..

13.What is Correlated Subquery or synchronized Query?

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 subqueries are called as Synchronized query.

14.What is Invisible property of Index?

Answer:

In Oracle 11 G You can put the index to invisible property so that it can not show in Execution plan.Indexes can be created in Invisible mode in Oracle 11 G.The new parameter has been introduced named OPTIMIZER_USE_INVISIBLE_INDEXES and optimizer uses the same parameter if it sets to ‘TRUE’ or ‘FALSE’.This parameter sets to true or false at system level.

Syntax:

Create or replace index Index_name on Table_name(Column_name) Invisible;

Alter index:

ALTER Index Indexname set Invisible=[TRUE|FALSE];

15.What is Snapshot of Oracle Database?

Answer:

Snapshot is nothing but materialized view.

Materialized view in SQL is also a logical structure which is stored physically on the disc.Like a view in Materialized view we are using simple select statement to create it.You should have create materialized view privileges to create a materialized view.Definition of materialized view(called as MV) has been stored in databases.Materialized views are useful in Data-warehousing concepts.

Click here to get more information about Materialized view or snapshot..

16.What is difference between simple view and snapshot?(Mostly asked Interview Questions For SQL)

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 Dataware-housing.

17.How the select statement executes? Explain stepwise.

Answer:

Select Statement executes in following steps:

Step 1: From Clause Execution:

In Every select statement first step is execution of the statements in from clause.In above statement there is only one table so it directly takes the table.So in above statement first step optimizer or compiler points to table name Employee.

Step 2:Where Clause:

The second step is where clause.When there is no where clause in Select statement optimizer directly fetches all the results from specified table.Where clause filters the data in database.In above query there is no where condition so Optimizer directly takes all the records from the table.

Step 3:Group By clause

The Step 3 is Group by clause.If the query contains group by clause then the specified result of Query is partitioned in to different groups.One group is every combination of values given in to group by clause.In above query we are doing grouping the department id wise.

Step4:Having Clause

If your Query contains having clause then it will executes after the group by clase.After grouping if user wants to filter the records then having conditios comes in picture.In our query we are taking sum of records having department id=100.

Step 5:Aggregate functions and select columns:

After fetching all the records and executing the clauses the columns which are taken in select statement gets executed.The funnctions like max,min,count,sum is executed first and then all columns taken in select statement gets executed last.

CLICK HERE TO GET EXECUTION OF SELECT STATEMENT STEPWISE WITH EXAMPLE

18. What is difference between union and union all set operators?

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; Union Select col1,col2…from table2;	2.Syntax: Select col1,col2…from table1; Union Select 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.

Click here to get information of Union and Union all in detail..

19.What is difference between Intersect and Minus?

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.

Click here to get information on Intersect and Minus with Scenarios

20.What is Row id in SQL?

Answer:

ROWID is nothing but the physical memory location on which that data/row is stored.ROWID basically returns address of row.
ROWID uniquely identifies row in database.
ROWID is combination of data object number,data block in datafile,position of row and datafile in which row resides.
ROWID is 16 digit hexadecimal number whose datatype is also ROWID Or UROWID.
The fastest way to access a single row is ROWID
ROWID is unique identifier of the ROW.

Example:- select rowid from dual;
AAAAECAABAAAAgiAAA

Click here to get more information on RowID and Rownum in SQL

Hope you like this article on Interview Questions For SQL.If you like this article kindly comment here in comment section.If you want PDF of this article kindly comment.

HOME