In my previous articles I have given set of different SQL interview Questions with answers for freshers as well as experienced people. In this article I will try to give the most common Interview SQL Questions which are useful to all developers ,Data Analyst ,Database admins and testers as well. The article explains about different Interview SQL Questions useful for interviews in MNC. I have already given lot of interview SQL questions in my previous articles. But in this article i will try to give you mixed Interview SQL Questions so that it will useful for everyone. In this article we will give you important interview SQL questions in detail.
Interview SQL Questions :
Following is the set of useful Interview SQL Questions :
1.What is SQL?[100 % asked Interview SQL Questions ]
Answer:
SQL Stands for Structured Query Language which is specially desinged 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 correlated Subquery?(80 % asked in Interview Questions for Tech Mahindra)
Answer:
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. (Source-click here)
Example:
Select * from Employee E where Not exist
(Select Department_no From Department D where E.Employee_id=D.Employee_ID);
Execution of query:
Step 1:
Select * from Employee E ;
It will fetch the all employees
Step 2:
The First Record of the Employee second query is executed and output is given to first query.
(Select Department_no From Department D where E.Employee_id=D.Employee_ID);
Step 3:
Step 2 is repeated until and unless all output is been fetched. (Source-Click here)
3.What is faster. In Operator in SQL or Exist in SQL?
Answer:
In Operator is slower than Exist operator.In Operator scans full table and checks the each and every value of the table.Exist operator only checks whether the value is present in the table or not.
4.What is Union Operator?
Answer:
Union Operator combines the result of 2 or more tables and fetches the results of two select statements.Union operator eliminates the duplicates from the table and fetches the result.For each duplicate row in table only one row is displayed in the result.By considering the performance of SQL using union is not preferable option but if there is situation where user wants to remove the duplicate data from two or more table the use of Union is preferable.
Example:
Select Employee_Num,Employee_name,Department,Salary from Employee_OBIEE;
Union
Select Employee_Num,Employee_name,Department,Salary from Employee_COGNOS;
5.What are advantages of Indexes?
Answer:
Indexes are memory objects which are used to improve the performance of queries which allows faster retrieval of records.
Following are advantages of Indexes:
1.It allows faster retrieval of data
2.It avoids the Full table scan so that the performance of retrieving data from the table is faster.
3.It avoids the table access alltogether
4.Indexes always speeds up the select statement.
5.Indexes used to improve the Execution plan of the database
6.What is parser?
Answer:
When SQL Statement has been written and generated then first step is parsing of that SQL Statement.Parsing is nothing but checking the syntaxes of SQL query.All the syntax of Query is correct or not is checked by SQL Parser.
There are 2 functions of parser:
1.Syntax analysis
2.Semantic analysis
Click Here to know more about Parser….
7.How to remove duplicate rows from table?[100 % asked Interview SQL Questions ]
Answer:
First Step: Selecting Duplicate rows from table
Tip: Use concept of max (rowid) of table. Click here to get concept of rowid.
Select rollno FROM Student WHERE ROWID <>
(Select max (rowid) from Student b where rollno=b.rollno);
Step 2: Delete duplicate rows
Delete FROM Student WHERE ROWID <>
(Select max (rowid) from Student b where rollno=b.rollno);
8.How to find Third highest salary in Employee table using self-join?(90% asked Complex SQL Queries for Interviews )
Answer:
Select * from Employee a Where 3 = (Select Count (distinct Salary) from Employee where a.salary<=b.salary;
9.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;
10.What is Query to drop all user tables from Oracle?
Answer:
To Drop all tables user needs to write simple PLSQL block
Begin
For I In
(Select * from Tabs) —Tabs is system table in which user get the different user defined table names.
Loop
Execute immediate (‘Drop Table ‘||i.table_name||’cascade constraints’);
End loop;
End;
11.How to Find table name and its owner?
Answer:
Make sure that the database user have logged in with SYS user.
Select table_name,Owner from All_tables order by table_name,owner;
12.How to find all details about Constraints?
Answer:
SELECT * From User_Constraints;
SELECT * FROM User_Cons_Columns;
13.What are advantages of Oracle 18c?(Latest Interview SQL Question )
Answer :
Oracle CTO has announced that new database version of Oracle will come till December 2017 which is Oracle 18c.Oracle 18c is worlds first autonomous database which will do lot of tasks will be automated using Artificial Intelligence Engine.
Following are some advantages of Oracle 18c :
- Oracle 18c uses adaptive machine learning which will reduce the human errors.
- It reduces the complexity of database
- It is highly relible and secure database
- It reduces the operational cost
- Self Driving database
- Self Tuning database
- Most efficient consumption of resources
- Less human intervention
14.What are different database Environments used in any project?(90% asked in Interview SQL Questions )
Answer:
The Project to project database environment varies.But the following is basic environment structure used for projects.
1.Development Environment:
In Development Environment all developer works and development work is been done on development environment.
2.Test Environment:
Developers does not have access of test environment.After development is done the code is migrated to Test Environment.Testing team is working on Test environment and execute black box as well as white box test cases on this Environment.Sometimes System Integration Testing (SIT) is also done on this Environment.
3.UAT Environment:
UAT stands for User Acceptance Testing.On this Environment the Customer side testers tests the software and executes User Acceptance Test Cases.
4.Performance Testing Environment:
On this environment the performance tester tests all performance related issues on this environment. This environment contains very huge data and performance tester will try to break the system using that big data.
5.Production Environment:
On this Environment actual user works and uses the software.
15.What is difference between unique and distinct?(90% asked in Interview SQL Questions )
Answer :
There is no difference between unique and distinct keywords apart from one difference.unique is applied before insertion and retrival.It consists of non duplicate values.if unique constraint is given it does not take duplicate values.distinct is used in retrieval it gives the suppressed row(ex if two rows are same it will show single row and non duplicate row) therefore distinct is the combination of suppressed duplicate and non duplicate rows.Specify DISTINCT or UNIQUE if you want Oracle to return only one copy of each set of duplicate rows selected (these two keywords are synonymous). Duplicate rows are those with matching values for each expression in the select list.
So there is no functional difference between Unique and distinct both have same functionalities.
16.What is Normalization?(90% asked in Interview SQL 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.
17.What is purpose of Normalization?(90% asked in Interview SQL Questions )
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.
18.What is difference between view vs materialized view?(90% asked in Interview SQL Questions )
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 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. |
19.Explain Rank function in SQL.
Answer :
Rank function is used as analytical function in SQL/PLSQL/SQL server which is used to give the rank to the specific record in the table.Rank function is giving you ranking in ordered partitions.Means Ties are assigned to the same values after using the order by clause.So Rank function is not useful where same data is repeated again and again.It is useful in Unique data where user can make partition and order the data properly.
Syntax of Rank:
RANK () OVER (PARTITION BY expression ORDER BY expression)
Example:
SELECT Employee_Name,Department_No,Salary,RANK() OVER (PARTITION BY Department_No ORDER BY Salary) “Rank” FROM EMPLOYEE;
CLICK HERE TO CHECK RANK AND DENSE RANK FUNCTION DIFFERENCE
20.How Can i create table with same structure of Employee table?
Answer:
Create table Employee_1 as Select * from Employee where 1=2;
21.What is composite index?
Answer:
When 2 or more columns are related to each other in the table and the same columns are used in where condition of the query then user can create index on both columns.These indexes are known as composite indexes.
Example:
Create index CI_Employee on Employee(Eno,Deptno);
22.What is mean by Clustered index?
Answer:
1.The clustered indexes are indexes which are physically stored in order means it stores in ascending or descending order in Database.
2.Clustered indexes are created once for each table.When primary key is created then clustered index has been automatically created in the table.
3.If table is under heavy data modifications the clustered indexes are preferable to use.
In this article i have explained 25 most important Interview Questions for Deloitte.Hope you like this article.Don’t forget to share this article.
23.What is raw datatype?
Answer:
Raw datatype is used to store values in binary data format.
There are 2 types of RAW datatype:
1.Raw
2.Long Raw.
Long raw datatype is used to store graphics,sound documents.
Raw datatype is variable length datatype like varchar2 but basically it only stores data in 1 ‘s and 0’s means binary data format.
24.Explain different Joins in SQL?
Answer:
Join is nothing but connecting 2 tables to fetch the records from 2 or more different tables.There are following types of joins in SQL:
1.Inner join:
Inner join retreives the records which are common between 2 or more tables.
2.Outer join:
Outer join retrieves the common records from the table as well as uncommon records from Left or right table.
2.1.Left outer join:
When user needs to fetch all data from left table and common records from left and right table then the join is called as left outer join.
2.2.Left outer join:
When user needs to fetch all data from right table and common records from left and right table then the join is called as right outer join.
2.3.Full Outer Join:
When user needs to fetch the data from both the tables and common records from both of the tables.
3.Cross join/Cartesian join:
When each and every record is connected to each and every record from other table then it is called as cross join or Cartesian join.
25.What is the result of following query?
select case when null=null then ‘Amit’ else ‘Rahul’ end from dual;
Answer:
The null=null is always false.so the Answer of this query is Rahul.
26.What are Explicit Indexes?
Answer:
The indexes which is created by user are called as explicit indexes.You can say the indexes which are created by ‘Create Index’ statement are called as Explicit indexes.
Syntax:
create index indexname on tablename(columnname);
Example:
Create index IND_Employee_ID on Employee(Employee_ID);
27.What is Bit-map index?Explain with Example.(90 % asked in Interview SQL Questions )
Answer:
1.If Table contains the distinct values which are not more than 20 distinct values then user should go for Bit map indexes.
2.User should avoid the indexing on each and every row and do the indexing only on distinct records of the table column.You should able to check drastic change in query cost after changing the normal index to Bit map index.
3.The bit map indexes are very much useful in dataware housing where there are low level of concurrent transactions.Bit map index stores row_id as associated key value with bitmap and did the indexing only distinct values.
4.Means If in 1 million records only 20 distinct values are there so Bitmap index only stores 20 values as bitmap and fetches the records from that 20 values only.
Syntax:
Create bitmap index Index_name on Table_name(Columns which have distinct values);
Example:
CREATE BITMAP index BM_DEPT_NAME on DEPT(Department_name);
28.What is visible/invisible property of index?
Answer:
User can make the indexes visible and invisible by altering the indexes.Following statement is used to make indexes visible and invisible.
ALTER INDEX index_name VISIBLE;
29.What will be the output of following query?
Select * from (select ‘a’ union all select ‘b’) Q;
Answer:
It will throw error because no values are selected in Sub-query.
Error code-ORA-00923 from keyword not found expected values.
30.What is query to display first 50% records from table?(90% asked in Interview SQL Questions )
Answer:
Select rownum,E.* from Employee E
minus
Select rownum,E.* from Employee E where rownum<=(Select count(*/2) from Employee);
31.What is Rownum in Oracle?
Answer:
- ROWNUM is magical column in Oracle which assigns the sequence number to the rows retreives in the table.
- To limit the values in the table you can use rownum pseudocolumn
- ROWNUM is nothing but logical sequence number given to the rows fetched from the table.
- ROWNUM is logical number assigned temporarily to the physical location of the row.
- You can limit the values in the table using rownum
- ROWNUM is also unique temparary sequence number assigned to that row.
Click Here to get information on RowID and Rownum..
32.What is Rank function as aggregate function?
Answer:
Rank function is used as aggregate function to return the rank of rows in the table within group of rows.If someone needs to find out the rank of specific row in the table then we will use the rank function.
Rank Function Syntax:
RANK( expr1 [, expr2, … expr_n ] ) WITHIN GROUP ( ORDER BY expr1 [, expr_2, … expr_n ] );
Click here to get Real life Example of Rank Function…
These are some important SQL interview questions with answers for freshers as well as experienced people. Hope you like this article on Interview SQL Questions with its answers. Please comment in to comment section if you like this article or if any suggestions and concerns.