Top 10 Database Interview Questions with Answers

0
625


Database knowledge is a must to have even if you have Java or .net as your technology. Here I am going to list down the TOP 10 DATABASE INTERVIEW QUESTIONS and ANSWERS which get surely covered in any interview for Database position or a Java or .NET Position.

Question 1: In what sequence these clauses execute : ‘Where’, ‘Having’, ‘Order By’ and ‘Group By’ ? How they are different with each other?

ORDER BY clause must be placed after the GROUP BY and HAVING clauses. HAVING clause is used in combination with the GROUP BY clause. It can be used in a SELECT statement to filter the records that a GROUP BY returns. ORDER BY is used just to decide the order of result set.
Here is an example of usage with these three clauses:

[symple_box color=”gray” fade_in=”false” float=”center” text_align=”left” width=”60″]

SELECT          city, COUNT(Orders) AS Orders_Count
FROM             Members
WHERE          domain = ‘goyalsbit.com’
GROUP BY   city
HAVING        Orders_Count > 10
ORDER BY   Orders_Count DESC ;

[/symple_box]

The above query would group the records based on the CITY of members and select the data only for members where number of Orders (Orders_Count) are more than 10. The result set would then be displayed in descending order of count.

Question 2: What is the difference between ‘Primary Key’, ‘Candidate Key’, ‘Composite Key’ and ‘Alternate Key’ ? Explain all of these.

Primary Key – is always unique (does not repeat) and has UNIQUE and NOT NULL constraints. It should not be a sensitive data to reveal or to be used in queries. For ex: SSN no. cant not be a primary key but employee id is a good field to be a primary key.

Candidate Key – A unique key that can be used as a primary key is called a candidate key. For ex: EmpId, emailId and SSN – all will be always unique for an employee and will be called candidate keys. Among these, one is chosen as primary key.

Alternate Key – Any candidate key which has not been chose as primary key is called alternate key.

Composite Key – A combination of more than one columns to identify a unique row in a table is called Composite key. For ex:

EMPLOYEE TABLE                             PROJECT TABLE
————————                              ————————
EmployeeId (PK)                                    ProjectId (PK)
EmailId                                                         ProjectName
SSN
Duration

PROJECTION_HOURS TABLE

EmpId + ProjectId (Composite Key)

Combination of EmpId and ProjectId called as composite key because the comobination acts as primary key in PROJECTION_HOURS table.

Question 3: What are the aggregate functions?

SQL aggregate functions return a single value, calculated from values in a column of the table. For Example :

• AVG() – Returns the average value of column values in select statment.
• COUNT() – Returns the count of number of rows in a SELECT statement.
• MAX() – Returns the largest value among all the values.
• MIN() – Returns the smallest value among all the values.
• SUM() – Returns the sum of all values in select statement.

Question 4: How to count the number of records in a table?

[symple_box color=”gray” fade_in=”false” float=”center” text_align=”left” width=”60″]
Select COUNT(*) as Number_of_Memebers
From goyalsbit_members
Where membership_days > 100;
[/symple_box]

Question 5: How to get the record of an employee with highest salary?

To select the maximum salary of an employee use this query:

[symple_box color=”gray” fade_in=”false” float=”center” text_align=”left” width=”60″]
Select max(salary)
from employee;
[/symple_box]

To select the complete record of the employee having maximum salary:

[symple_box color=”gray” fade_in=”false” float=”center” text_align=”left” width=”60″]
Select * from Employee
Where salary = (select max(salary) from Employee);
[/symple_box]

Question 6: How to get the record of an employee with second or nth highest salary?

There are two ways to get the nth highest salary :

[symple_box color=”gray” fade_in=”false” float=”center” text_align=”left” width=”60″]
SELECT * from (
Select DENSE_RANK() over(order by salary desc) as RankId, * from Employee
) innerQuery
Where innerQuery.RankId = N;
[/symple_box]

OR Use the below Query:

[symple_box color=”gray” fade_in=”false” float=”center” text_align=”left” width=”60″]
SELECT * from Employee e1
WHERE (N-1) = (
Select count(Distinct(e2.salary)) from Employee e2 where e2.salary > e1.salary
);
[/symple_box]

Question 7: How to get duplicate records out from all the records of a table?

To get the duplicate records from a table:

[symple_box color=”gray” fade_in=”false” float=”center” text_align=”left” width=”60″]
SELECT MemberId
FROM Goyalsbit
GROUP BY MemberId
HAVING count(*) > 1;
[/symple_box]

Question 8: What is the difference between ‘LEFT Outer Join’, ‘RIGHT Outer Join’ and ‘FULL Outer Join’ ?

Left Outer Join: This join returns all records from the LEFT-hand table specified in the ON condition and only those records from the other table where the joined fields are equal i.e. the join condition is met.

Right Outer Join: This join returns all records from the RIGHT-hand table specified in the ON condition and only those records from the other table where the joined fields are equal i.e. the join condition is met.

Full Outer Join: This join returns all rows from the LEFT-hand table and RIGHT-hand table with nulls in place where the join condition is not met.

Question 9: How to delete duplicate rows in a table?

To delete the duplicate records from the table:

[symple_box color=”gray” fade_in=”false” float=”center” text_align=”left” width=”60″]
DELETE from Goyalsbit
WHERE MemberId IN (select MemberId from Goyalsbit Group By MemberId having count(*) > 1);
[/symple_box]

Question 10: What are the built-in functions? Give examples for ‘DECODE’, ‘NVL’, ‘TO_CHAR’ etc.

DECODE : This function has the functionality of if-then-else.

[symple_box color=”gray” fade_in=”false” float=”center” text_align=”left” width=”60″]
SELECT website_name,
DECODE (website_name, 100, ‘GOYALSBIT’,
101, ‘GOYALSBIT.COM’,
‘http://goyalsbit.com’) result
FROM Websites;
[/symple_box]

NVL : Lets you substitute a value when a null value is encountered.

[symple_box color=”gray” fade_in=”false” float=”center” text_align=”left” width=”60″]
SELECT NVL(city_name, ‘N/A’) From Employee;
[/symple_box]

So when city would be null, it will be replaced with ‘N/A’.

TO_CHAR : converts a number or date to a string.
Example :

a) Number to String :
TO_CHAR(1210.73, ‘£9,999.00’) returns ‘£1,210.73’

b) Convert to String
TO_CHAR(sysdate, ‘yyyy/mm/dd’) returns ‘2014/12/17’

Hope you enjoyed the post. If there are any more DB interview questions which you think are useful to add, feel free to mention in your comments and they will be added here. Till then Happy Reading DB.

Previous articleSpring Interview Q n A
Next articleLogging in Java – Log4J
I have spent almost 10 years playing around Java and related technologies. I love to write on different topics and would be very much willing to hear back your feedback/suggestions on them. This site is a medium to share my knowledge with the Java folks and grow further. My other interests include traveling, driving, swimming and dance. But yes, my web site has become my passion over the time :) I live in Scotland and travel to India often, my roots being there.

NO COMMENTS

LEAVE A REPLY