DBMS Interview Questions - Part 4
Click Here - DBMS Interview Questions - Part 3
Click Here - DBMS Interview Questions - Part 2
Click Here - For DBMS Interview Questions - Part 1
What is a Query ?
Query is a statement that is used for the extraction of data from
database.
For example – select * from table1 is a query.
What is Subquery ?
Subquery is a query within query.
For example – select * from students where marks = ( select max(marks)
from students);
What are Joins ?
Join clause are used to combine rows from two or more tables, depending upon the columns between them.
What are the different types of Joins ?
Different types of Joins are :
1. INNER JOIN : It returns all records that are common in both tables.
2. LEFT OUTER JOIN : It returns all records from the left table, and
matched records from right table.
3. RIGHT OUTER JOIN : It returns all records from the right table, and
matched records from left table.
4. FULL OUTER JOIN : It returns all records when there is a match in
either left or right table.
Give brief information about entity, entity type, entity set ?
Entity is any real world object about which data can be stored in a
database.
Example – Book, Person.
Entity Type is a collection of the entities which have the same attributes.
Example – Employee table is an entity type containing ‘n’ rows, in which
each row defines different data for different entities
Entity Set is a collection of the entities of the same type.
Example – A collection of the employees of a company.
What are Stored Procedures ?
Stored Procedure refers to the set of Structured Query Language(SQL)
statements stored in a relational database management system as a group.
It can further be reused and shared by multiple programs. It provides a
layer of security between a user interface and database.
Can you create a table without using create command ?
Yes, we can create table with the help of SELECT INTO statement. It
copies content of one table to another table. However, there should be
atleast one table from where we can copy content.
Example : Copying all columns : select * into new_table from old_table where
condition
Copying specific column : select col1,col2 into new_table from old_table
where condition
Creating new empty table : select * into new_table from old_table where 1 = 0
What is Normalization ?
Normalization refers to the decomposition of relation. It is required to
remove data anomalies, data redundancy and data inconsistency.
Normalization of a database increases more restrictions on it.
Most commonly used normal forms are :
First Normal Form
Second Normal Form
Third Normal Form
Boyce & Codd Normal Form
What are the rules for 1 NF ?
1. Each table cell should contain a single value.
2. Each record needs to be unique.
What is 2 NF ?
A relation is said to be in 2 NF, if it satisfies following rules :
1. It is in 1 NF.
2. Every non-prime attribute is fully functionally dependent on the primary
key.
What is 3 NF ?
A relation is said to be in 3 NF, if it satisfies following rules :
1. It is in 2 NF.
2. There is no transitive functional dependency.
Explain BCNF ?
BCNF is Boyce-Codd Normal Form. It is considered to be the advanced
version of 3 NF. Hence it is also refered to as 3.5 NF. A relation is said to
be in BCNF, if it satisfies following rules :
1. It is in 3NF.
2. For every functional dependency P->Q, P should be the super key of the
table.
What is Denormalization ?
It is the reverse process of Normalization. It is the process of trying to
improve the readability of the database by grouping data. Denormalization
is also used for speeding up the performance.
