Are you looking for a job as a database administrator? Or are you thinking of leave your current position for a new job as a DBA with a new company?
If you answered yes to either of those questions, this article is for you and any of the described technologies and questions may be asked of you during the interview.
An Introduction with DBA’s roles and responsibilities:
A database administrator is responsible for the installation, configuration, upgrade, and migration installation of the database software on a given server. As new releases and patches are developed, it’s the DBA’s job to decide which are appropriate and to install them.
DBAs are also responsible for backup and recovery plans for the databases they manage. When failures do occur, the DBA needs to know how to use the backups to return the database to operational status as quickly as possible, without losing any transactions that were committed.
Databases are hot targets for hackers; the DBA must understand the particular security model that the database product uses and how to use it effectively to control access to the data. The three basic security tasks are authentication, authorization, and auditing (tracking who did what with the database).
The primary purpose of a database is to store and retrieve data, so planning how much disk storage will be required and monitoring available disk space are key DBA responsibilities. The DBA is also responsible for monitoring the database server on a regular basis to identify and rectify them. When database services go down, the DBA needs to know how to quickly troubleshoot and resolve the problem without losing data or making the situation worse.
Before facing any interview for a database administration position, make sure you have enough knowledge on the below technologies:
Required Key Skills for a DBA:
- Sound knowledge of the physical database design
- Knowledge of ALL backup and recovery policies
- Knowledge about the RDBMS e.g. Oracle Database, IBM DB2, Microsoft SQL Server etc.
- Knowledge of structured query language (SQL)
- Hands-on knowledge of code migration and database change management
- A sound knowledge of both database and system performance tuning
- General understanding of storage technologies, memory management, disk arrays, NAS/SAN and networking
- General understanding of routine maintenance, recovery and handling failover of a database
- A DBA should have the ability to handle multiple projects with deadlines
My Best Questions for an Interview of Database Administrator: All of the questions below are very common and must be prepared for before facing any interview for the database administration position.
Q: What is a database?
A: A database provides a single point mechanism for storing and retrieving information with the help of tables. These tables are made up of columns and rows where each column stores specific attributes and each row displays a value for the corresponding attribute.
Q: What are the different types of storage systems available and which one is used by Oracle?
A: Two types of storage systems are available: Relational Database Management System (RDBMS) and Hierarchical Storage Management System (HSM). Most databases use the RDBMS model. Oracle also uses the RDBMS model.
Q: What is normalization? What are the different forms of normalization?
A: Normalization is a process of organizing the fields and tables of a relational database to minimize redundancy and dependency. It saves storage space and ensures consistency of our data.
There are six different normal forms:
- First Normal Form – If all underlying domains contain atomic values only.
- Second Normal Form – If it is in first normal form and every non-key attribute is fully functionally dependent on primary key.
- Third Normal Form – If it is in 2nd normal form and every non-key attribute is non-transitively dependent on the primary key.
- Boyce Codd Normal Form – A relation R is in BCNF if and only every determinant is a candidate key.
- Fourth Normal Form – Multivalued dependency
- Fifth Normal Form – Join dependency
Q: What is the purpose of cluster?
A: Cluster is used for storing table data. It contains groups of tables that share the same data blocks. Clusters can’t be used for tables that are frequently fetched.
Q: What is the use of control file?
A: Control file is the backbone of databases. Control files have all the information about redo log files and log files.
A control file contains:
- The database name
- Names and locations of associated data files and redo log files
- The time stamp of the database creation
- The current log sequence number
- Checkpoint information
Q: What is the difference between view and materialized view?
A: A view is a simple select statement executed each time a user runs queries against it. A materialized view is a physical persistent object used for performance improvement of summary data or replication. View does not consume space and materialized view consume space
Q: What is a schema? Explain different types of schema.
A: Schema is a collection of components and database objects under the control of a given database user. The schema stores the components owned by the application and it can store the database objects on which the components are based.
Main types of schema are
- Star schema : A single fact table with N number of dimension
- Snowflake schema : One fact table that is connected to many dimension tables
- Multiple Star (galaxy) : A schema with more than one fact table
Q: What is rollback segment?
A: Rollback segment is a database object used to undo changes when the transaction is rolled back. It is a very useful tool to recover the database in case of failures.
Q: What is the limit of data files in an Oracle database?
A: A database can have up to 65,536 data files. So you can create maximum a of 65,536 data files.
Q: What are the common problems while taking backups? Explain some common issues.
A: Some common problems are:
- If you are taking backup from a no-archive log, then your database doesn’t bring down due to some processes are running. (WORD MISSING IN THIS SENTENCE?)
- If you are taking hot backup then maybe newly added data files were missed in your backup script.
- For a cold backup, shutdown Oracle first and then proceed with the cold (offline) back up.
- Disk space problem: we need to monitor backup disk space.
- A network problem is if we take backup on NAS.
Q: How do you see if specific table space is offline or online?
A: You can use the following commands to specify online/offline table space:
SELECT tablespace_name, online_status FROM dba_data_files;
SELECT tablespace_name,status FROM dba_tablespaces;
Q: What are the primary roles of a DBA?
A: A DBA has the authority to create new users, remove existing users or modify any of the environment variables or privileges assigned to other users.
- Manage database storage
- Administrate users and security
- Manage schema objects
- Monitor and manage database performance
- Perform backup and recovery
- Schedule and automate jobs
Q: What is an index in Oracle?
A: An index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.
Q: What is DDL?
A: The Data Definition Language is used to create, remove and alter the structure of database objects. The DDL is used for table definition and can be classified into four categories:
- Create table command
- Alter table command
- Truncate table command
- Drop table command
Q: What is DML?
A: Data Manipulation Commands are used to query and manipulate existing database objects. Following are some DML commands:
Q: What is primary key?
A: Primary key is used to uniquely identify each row of the table. A table can have only one primary key.
Q: What is unique and foreign key?
A: The unique key constraint ensures that information in the column(s) is unique or the value entered in a unique column must not be repeated across the column(s). A table may have more than one unique key.
Foreign key constraint is used to represent relationships between two or more tables.
Q: What is sub-query?
A: Query within a query is called a sub-query. The statement containing a sub-query is called the parent statement. Sub-queries are used to retrieve data from tables and the retrieved data depends on the value in the table itself.
Q: What is the difference between JOIN and UNION?
A: SQL JOIN allows us to “look up” records on other table based on the given conditions between two tables. UNION operation allows us to add two similar data sets to create resulting data set that contains all the data from the source data sets. Union does not require any condition for joining.
Q20. What is the difference between WHERE clause and HAVING clause?
A: WHERE and HAVING both filters out records based on one or more conditions. The difference is: a WHERE clause can only be applied on a static non-aggregated column whereas we will need to use HAVING for aggregated columns.
Q: What is the difference between truncate and delete?
A: The Delete command is used to delete data from a table. Whereas, truncate is a very dangerous command and should be used carefully as it deletes every row from a table. The syntax of it as follows:
TRUNCATE TABLE “table_name”
The questions above are very tricky and important from the standpoint of clearing any interview for a database administrator position. It is not possible for anyone to explain all kinds of questions, but you can get more frequently asked interview questions for Database Administrator Jobs from the download link posted here. If you find any difficulty in answering any questions, then you can write me in comment section.
Tips for Preparing for an Interview
- Study: Before an interview, take a quick recap of relevant technologies.
- Updated Resume: Read your resume through; don’t copy and paste your resume. You must be aware of your strengths and weaknesses.
- Professional Certifications: One of the best ways to prove the technical skills mentioned in your resume is through certifications. This gives a new employer an easy way to understand your knowledge level.
- Updated LinkedIn Profile: Update your LinkedIn profile regularly; make sure that your work experience, qualifications, and project details match with your resume.
Don’t forget to post your queries and feedback in the comment section. You can join our Facebook group, http://www.facebook.com/intenseschool, to get updates on new posts and technologies.