Database Administrator Interview Questions and Answers

There is a list of top frequently asked Database 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 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 in DBMS?

Ans An association of several entities in an Entity Relation model is called a Relationship.
There are three types of relationships are 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 rollback 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 that are called ACID Properties of Transactions. There are four properties of ACID Properties of Transactions are listed as below:

  • Atomicity
  • Consistency
  • Isolation
  • Durability
  • 7. What are triggers?

    Ans In SQL, Triggers is a kind of stored procedures 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 Tool bars are display 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 Forms 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 that 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 is used to permit users access to the database. It has the following syntax:

    GRANT <privilege_name> | ALL ON <object> TO <user|PUBLIC> [WITH GRANT OPTION];
    

    Revoke Command: The REVOKE command is used to cancel database privileges from users. It has the following syntax:

    REVOKE <privilege_name> | 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 Employee from Emp table whose name starts with S.

    Ans

    SELECT * FROM Emp WHERE Employee_Name like ‘S%’;
    

    Leave a Reply

    Your email address will not be published. Required fields are marked *