DB Question Ans
DB Question Ans
DB Question Ans
Given below is a list of most popular Database interview questions and answers for
your reference.
Q #19) What are the advantages and disadvantages of views in the database?
Answer: Advantages of Views:
As there is no physical location where the data in the view is stored, it
generates output without wasting resources.
Data access is restricted as it does not allow commands like insertion,
updation, and deletion.
Disadvantages of Views:
The view becomes irrelevant if we drop a table related to that view.
Much memory space is occupied when the view is created for large tables.
Q #20) What do you understand by Functional dependency?
Answer: A relation is said to be in functional dependency when one attribute
uniquely defines another attribute.
For Example, R is a Relation, X and Y are two attributes. T1 and T2 are two
tuples. Then,
T1[X]=T2[X] and T1[Y]=T2[Y]
employee_info table:
a) Inner JOIN: Inner JOIN is also known as a simple JOIN. This SQL query
returns results from both the tables having a common value in rows.
SQL Query:
1 SELECT * from employee, employee_info WHERE employee.EmpID = employee_info.EmpID ;
Result:
EmpID EmpName EmpID Address
c) Cross JOIN: Cross JOIN returns the result as all the records where each row
from the first table is combined with each row of the second table.
SQL Query:
1 SELECT * from employee CROSS JOIN employee_info;
Result:
Let us make some modifications in the above tables to understand Right
JOIN, Left JOIN, and Full JOIN.
employee table:
employee_info table:
a) Right JOIN: Right JOIN is also known as Right Outer JOIN. This returns all
the rows as a result from the right table even if the JOIN condition does not match
any records in the left table.
SQL Query:
1 SELECT * from employee RIGHT OUTER JOIN employee_info on (employee.EmpID = employee_in
Result:
b) Left JOIN: Left JOIN is also known as Left Outer JOIN. This returns all the
rows as a result of the left table even if the JOIN condition does not match any
records in the right table. This is exactly the opposite of Right JOIN.
SQL Query:
1 SELECT * from employee LEFT OUTER JOIN employee_info on (employee.EmpID = employee_inf
Result:
c) Outer/Full JOIN: Full JOIN return results in combining the result of both the
Left JOIN and Right JOIN.
SQL Query:
1 SELECT * from employee FULL OUTER JOIN employee_info on (employee.EmpID = employee_inf
Result:
Q #38) What do you understand by ‘Atomicity’ and ‘Aggregation’?
Answer: Atomicity is the condition where either all the actions of the transaction
are performed or none. This means, when there is an incomplete transaction, the
database management system itself will undo the effects done by the incomplete
transaction.
Aggregation is the concept of expressing the relationship with the collection of
entities and their relationships.
Q #39) Define Phantom deadlock.
Answer: Phantom deadlock detection is the condition where the deadlock does not
actually exist but due to a delay in propagating local information, deadlock
detection algorithms identify the deadlocks.
Q #40) Define checkpoint.
Answer: Checkpoint declares a point before which all the logs are stored
permanently in the storage disk and is the inconsistent state. In the case of crashes,
the amount of work and time is saved as the system can restart from the
checkpoint.
Q #41) What is Database partitioning?
Answer: Database partitioning is the process of partitioning tables, indexes into
smaller pieces in order to manage and access the data at a finer level.
This process of partitioning reduces the cost of storing a large amount of data as
well as enhances the performance and manageability.
b) Write the SELECT command to display all the records of table Employees.
SQL Query:
1 SELECT * from Employee;
Result:
c) Write the SELECT command to display all the records of the employee whose
name starts with the character ‘R’.
SQL Query:
1 SELECT * from Employee WHERE empName LIKE ‘R%’;
Result:
d) Write a SELECT command to display id, age and name of the employees with
their age in both ascending and descending order.
SQL Query:
1 SELECT empId, empName, Age from Employee  ORDER BY Age;
Result:
SQL Query:
1 SELECT empName, SUM(Salary) from Emp GROUP BY empName;
Result:
Conclusion
These are the set of Database interview questions and answers which are mostly
asked in the interview.
Mostly the basics of every subject are questioned in the interviews. It is a well-
known fact to everyone that, if your basics are clear, you can reach top heights.
However, there may be some more tricky questions. Just be confident and face
each question with clarity in your subject knowledge.