11/23/16

SQL Oracle Interview Questions and Answers

Write a query to find the duplicate record?
The below is the employee table, it has 2 records with the name Bala. Now will write SQL query to return the only record where id=4.

idNameCountryDOB
1BalaIndia25-JAN-1987
2KrishnanIndia01-NOV-1992
3TestIndia14-JUL-1989
4BalaIndia25-JAN-1987
Using GROUP BY
SELECT name, count(*)
FROM employee
GROUP BY name
HAVING count(*)>1;
Using Rank()
SELECT *
FROM (
SELECT name, RANK() over(PARTITION BY name ORDER BY id) as rankn
FROM employee)
WHERE rankn>1;
Using Rownum()
SELECT *
FROM (
SELECT name, rownum() over(PARTITION BY name ORDER BY id) as rowno
FROM employee)
WHERE rowno >1;
Write a query to delete duplicate records?
Using GROUP BY
DELETE FROM employee
WHERE rowid IN (
SELECT rowid
FROM employee
GROUP BY name
HAVING COUNT(*)>1);
Using Rank()
DELETE FROM employee
WHERE rowid IN (
SELECT rowid FROM(
SELECT rowid,name, RANK() OVER(PARTITION BY name ORDER BY id) as rankn
FROM employee)a
WHERE rankn>1);
What is the difference between Drop vs truncate vs delete?
DeleteTruncate
Delete the specific rows using WHEREThe command deletes all records, but table structure exist
Can be rollbackCannot be rollback
DML commandDML command
Performance is slow since it deletes data one by one. Will take more time when volume is highPerformance is faster
DeleteDrop
Delete the specific rows using WHERERemove the table itself from database
Can be rollbackCannot be rollback
DML commandDDL command
Performance is slow since it deletes data one by one. Will take more time when volume is highPerformance is faster since it removes table irrespective if data volume
What are constraints?
  • Primary key – does not allow NULL and duplicate values. It acts as a reference in other tables.
  • Foreign key – will have reference with main table, main table column can be accessed with joins.
  • Unique – it does not allow duplicate values
  • Not NULL – does not allow NULL values
  • Check – it will take action based on the condition mentioned
  • Default – it will update the default value mentioned
Unique key vs Primary key
Primary KeyUnique Key
Only one key can be created for a tableMultiple unique keys can be created for a table
Won’t allow NULL valueALlows one NULL value
Won’t allow duplicating valueWon’t allow duplicating value
Primary key is a combination of Unique and NOT NULL constraints.
Union and union all
Union – to select unique records from multiple records
Select column1, column2, column3 from table1
UNION
Select column1, column2, column3 from table1
Union ALL– to select all records from multiple records including duplicate entries
Select column1, column2, column3 from table1
UNION ALL
Select column1, column2, column3 from table1
Primary Condition  for using UNION and UNION ALL would be,
The number of columns and data type of each column should be same.
Write a query to find cumulative sum at month level.
EmpIdMonthSalary
1001110000
1002212000
1003310000
1004412000
Select
empid, month, salary, sum(salary) over (order by empid, month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cum_salary
From Salary
Group by empid, month, salary
Order by empid, month
EmpIdMonthSalarycum_salary
100111000010000
100221200022000
100331000032000
100441200044000

EmpId     Month     Salary      cum_salary
1001           1             10000        10000
1002          2             12000        22000
1003          3             10000        32000
1004         4             12000       44000
  
Write a query to find nth highest value?
Select
id,name,dept,salary,
DENSE_RANK () over(PARTITION BY dept ORDER BY salary desc) as rankorder
from employee
order by dept,rankorder
The difference between Rank vs Dense_rank?
Rank – It will skip the rank numbers if it finds multiple records for the same rank
Dense_rank – It will assign the sequential numbers despite it finds multiple records for the same rank
In below table both 1005 and 1008 are having same salary, hence same rank number will be assigned.
idnamedeptsalary
1009ITECH900000
1006FTECH800000
1005EQA700000
1008HQA700000
1010JQA600000
Outcome of Rank and Dense_rank query,
idnamedeptsalaryRankDense_rank
1009ITECH90000011
1006FTECH80000022
1005EQA70000033
1008HQA70000033
1010JQA60000054
For query refer the previous question.
What is a correlated subquery?
The same column might be used in both outer query and inner query condition. It is called correlated subquery since the values of inner query have dependency with outer query values.
Select * from table1 where col1 in(select col3 from table2 where col2=col1);
The difference between Exists and In?
IN – we should have a column in where condition
Exists – does not require to have a column in where condition
Primary key vs Foreign key?
Primary KeyForeign Key
Only one primary key can be defined for a tableMultiple keys can be defined for a table
Won’t allow NULL value to insert/updateAllows NULL values unless the column explicitly marked as NOT NULLABLE
Does not allow duplicate valueAllows duplicate value
Subquery vs Join?
-The columns of subquery cannot be used in select statement, whereas joins will allow to use join table columns in select statement
Performance is faster in JOINS compare to subquery
Complexity will be high in subquery
-Only 8 subquery is allowable in a single select statement
View vs Materialized view?
View – only SQL query will be there and data will not be stored. When running the view query, the select statement of view will be executed. Extra memory is not required.
Materialized View – it stores the data in raw table like normal tables. Data refresh is required to update the data. Extra memory is required.
Explain about different types of joins?
Inner Join – returns data if join condition matches between two tables
Right Outer Join – right side table will be considered as base table, the data’s are in right table will be returned. If join condition matches it will return left table value else NULL value will be returned
Left Outer Join – left side table will be considered as base table, the data’s are in left table will be returned. If join condition matches it will return right table value else NULL value will be returned
Full Outer Join – combination of Right and Left outer join
Self Join – any join type used for the same table

What is the use of NVL command?
In select query we may want to set the default value for a column where it has NULL values. To replace the NULL value with some default value NVL can be used in Oracle.
Select NVL(col,’000’) from table;
What would be the order of commands Having, Group by, where, order by?
Select col1,count(*) from table1
Where col1=’value’
Group by col1
Having count(*) > 1000
Order by col1
DML vs DDL vs DCL
DDL – Data Definition Language
Create, Alter, Drop, Truncate
DML – Data Manipulation Language
Select, Insert, Update, Delete
DCL – Data Control Language
Commit, Rollback, Grant, Revoke
How do you fetch the unique records from a table?
UNION – apply this command for the same table
Select col1, col2, col3 from table1
UNION
Select col1,col2,col3 from table1
Questions on SQL functions (Min, max, sum, count, to_date, to_char etc..)
Clustered index vs Non-clustered index?
What is a normalization process?

Google Q&A Forum