Database Administrator Interview Questions and Answers
Database Interview Questions and Answers:
There is a list of top frequently asked Database Administrator Interview Questions and Answers are given below:
1. What is Primary Key?
Ans – The primary key uniquely identifies each record in a table and must never be the same for two records.
2. What are the roles of DBA?
Ans – The DBA controls the database structure and sets up the definition for the physical as well as the logical implementation of the database.
Roles of DBA:
(i) Schema Definition
(ii) Storage structure and access method definition
(iii) Schema and physical-organization modification
(iv) Granting of authorization for data access
(v) Routine maintenance
(vi) Maintaining the integrity
3. What is a Relationship? How many types of Relationships are in DBMS?
Ans – An association of several entities in an Entity Relation model, called a Relationship.
There are three types of relationships exist:
(i) One to One Relationship (1: 1)
(ii) One-to-Many Relationship (1: M)
(iii) Many to Many Relationship (M: M)
4. What is a Snapshot Log?
Ans – It holds the primary keys of rows that have been updated in the master table. It can also contain filter columns to support fast refreshes of snapshots with subqueries.
5. What are transactions and their controls?
Ans – In DBMS, Transaction States are a set of logically related operations.
Transaction States:
Active: It is the initial state, transaction stays the state while it is executing.
Partially Committed: In this state, the final statement of the transaction has been executed.
Failed: When the normal execution of a transaction can no longer proceed, it is failed.
Aborted: In this state, after the transaction has been rolled back and the database has been restored to be stated before starting the transaction.
Committed: In this state, the transaction has been completed.
6. What are the properties of the transaction?
Ans – A transaction is expected to contain certain properties, called ACID Properties of Transactions. There are four properties of ACID Properties of Transactions are listed below:
- Atomicity
- Consistency
- Isolation
- Durability
7. What are triggers?
Ans – In SQL, Triggers is a kind of stored procedure used to create a response to a specific action performed on the table such as INSERT, UPDATE or DELETE.
8. Define Horizontal Tool Bar, Vertical Tool Bar & Canvas Views.
Ans –
Horizontal Tool Bar: These Toolbars are displayed at the top of a window, just under its menu bar.
Vertical Tool Bar: These Tool bars are displayed along the left side of a window.
Canvas Views: It is used to create toolbars for individual windows.
9. What is Normalization? How many types of Normalization Form in DBMS?
Ans – Normalization is a process of simplifying the relationship among data elements in a record. There are mainly five types of Normalization Forms in DBMS.
1. First Normal Form (1NF)
2. Second Normal Form (2NF)
3. Third Normal Form (3NF)
4. Boyce Codd Normal Form (BCNF)
5. Fourth Normal Form (4NF)
10. What is a Cursor?
Ans – A cursor is a database object used to manipulate data in a row-to-row manner.
11. What is Schema?
Ans – The term schema indicates an overall structure of all data items including their record types stored in a database.
12. Define Cluster.
Ans – It refers to the ability of several servers or instances to connect to a single database.
13. What is Replication? How many types of Replication in DBMS?
Ans – It is the process of copying and maintaining database objects in multiple databases that make up a distributed database system. Replication provides the user with fast, local access to shared data and protects the availability of applications.
In DBMS, There are mainly 5 types of Data Replication –
i. Snapshot Replication
ii. Transactional Replication
iii. Merge Replication
iv. Heterogeneous Replication
v. Peer-to-Peer Transactional Replication
14. How do you add a column to a table?
Ans –
ALTER TABLE table_name ADD column_name data_type column_constraint;
15. Difference between TRUNCATE, DELETE and DROP commands.
Ans – TRUNCATE: It removes ALL rows from a table by de-allocating the memory pages, This operation cannot be rolled back.
DELETE: This command removes some or all rows from a table based on the condition, This operation can be rolled back.
DROP: This command removes a table from the database completely.
16. Which TCP/IP port does Oracle Database run?
Ans – 1521
17. How do you rename a column in SQL?
Ans – RENAME COLUMN Table_Name.Old_Column_Name TO New_Column_Name;
18. Define GRANT and REVOKE commands.
Ans – Grant Command: SQL is used in multiuser environments. The GRANT command used to permit users to access the database. It has the following syntax:
GRANT | ALL ON <object width="300" height="150"> TO <user|PUBLIC> [WITH GRANT OPTION];
Revoke Command: The REVOKE command used to cancel database privileges from users. It has the following syntax:
REVOKE | ALL ON object> FROM <user|PUBLIC>;
19. Write a SQL query for SQL Injection of a table.
Ans –
SELECT column_names FROM table_name WHERE condition;
20. Write a SQL query to show the details of all Employees from the Emp table whose name starts with ‘S’.
Ans –
SELECT * FROM Emp WHERE Employee_Name like ‘S%’;