DBMS Interview Questions and Answers
Question - 61 : - What do you understand by Data Independence?
Answer - 61 : -
When you say an application has data independence, it implies that the application is independent of the storage structure and data access strategies of data.
Question - 62 : - What are the different integrity rules present in the DBMS?
Answer - 62 : -
The different integrity rules present in DBMS are as follows:
Entity Integrity: This rule states that the value of the primary key can never be NULL. So, all the tuples in the column identified as the primary key should have a value.
Referential Integrity: This rule states that either the value of the foreign key is NULL or it should be the primary key of any other relation.
Question - 63 : - What does Fill Factor concept mean with respect to indexes?
Answer - 63 : -
Fill Factor is used to mention the percentage of space left on every leaf-level page, which is packed with data. Usually, the default value is 100.
Question - 64 : - What is Index hunting and how does it help in improving query performance?
Answer - 64 : -
The process of boosting a collection of indexes is known as Index hunting. This is done as indexes improve the query performance and the speed at which they are processed.
It helps in improving query performance in the following way:
- The best queries are suggested using the query optimizer.
- Index, query distribution and their performance are used as metrics to check the effect
- Databases are tuned into a small collection of problem queries.
Question - 65 : - Explain what is a deadlock and mention how it can be resolved?
Answer - 65 : -
Deadlock is a situation which occurs when two transactions wait on a resource which is locked or other transaction holds. Deadlocks can be prevented by making all the transactions acquire all the locks at the same instance of time. So, once deadlock occurs, the only way to cure is to abort one of the transactions and remove the partially completed work.
Question - 66 : - What is SQL and where is it used?
Answer - 66 : -
SQL aka Structured Query Language is the core of the relational database which is used for accessing and managing the databases. This language is used to manipulate and retrieve data from a structured data format in the form of tables and holds relationships between those tables. So, in layman terms, you can use SQL to communicate with the database.
Question - 67 : - How can you perform pattern matching in SQL?
Answer - 67 : -
You can perform pattern matching in SQL by using the LIKE operator. With the LIKE operator, you can use the following symbols:
%(Percentage sign) – To match zero or more characters.
_ (Underscore) –To match exactly one character.
Example:
SELECT * FROM Customers WHERE CustomerName LIKE ‘s%’
SELECT * FROM Customers WHERE CustomerName like ‘xyz_’
Question - 68 : - Mention few case manipulation functions in SQL
Answer - 68 : -
There are three case manipulation functions in SQL, namely:
LOWER: This function returns the string in lowercase. It takes a string as an argument and returns it by converting it into lower case.
Syntax: LOWER(‘string’)
UPPER: This function returns the string in uppercase. It takes a string as an argument and returns it by converting it into uppercase.
Syntax: UPPER(‘string’)
INITCAP: This function returns the string with the first letter in uppercase and the rest of the letters in lowercase.
Syntax: INITCAP(‘string’)
Question - 69 : - What are joins in SQL and what are the different types of joins?
Answer - 69 : -
A JOIN clause is used to combine rows from two or more tables, based on a related column between them. It is used to merge two tables or retrieve data from there. There are 4 joins in SQL namely:
- Inner Join
- Right Join
- Left Join
- Full Join
Question - 70 : - What do you understand by the view and mention the steps to create, update and drop a view?
Answer - 70 : -
A view in SQL is a single table, which is derived from other tables. So, a view contains rows and columns similar to a real table and has fields from one or more table.
To create a view, use the following syntax:
- CREATE VIEW ViewName AS
- SELECT Column1, Column2, ..., ColumnN
- FROM TableName
- WHERE Condition;
To update a view, use the following syntax:
- CREATE VIEW OR REPLACE ViewName AS
- SELECT Column1, Column2, ..., ColumnN
- FROM TableName
- WHERE Condition;
To drop a view, use the following syntax: