Database Analyst Interview Questions & Answers

Download PDF

Having identified the business problem, a data analyst has to go through the data provided by the client to analyse the root cause of the problem.

The three elements include the entities for which someone is seeking information, the attributes of those entities, and the relationships between the entities.

This is the most crucial step of the data analysis process wherein any data anomalies (like missing values or detecting outliers) with the data have to be modelled in the right direction.

Thinking mathematically, it is the number of elements in a set. Thinking in the database world, cardinality has to do with the counts in a relationship, one-to-one, one-to-many, or many-to-many.

Database candidates should be familiar with most if not all of these without needing to lookup definitions. Some of the other normalization forms are less commonly known/used, but could theoretically be asked. Knowing the differences between second and third is probably a good idea.

A measure of the dispersion of data that is shown in a box plot is referred to as the interquartile range. It is the difference between the upper and the lower quartile.

A transaction is a single logical (atomic) unit of work, in which a sequence of operations (or none) must be executed. A transaction has a defined beginning and end. You can commit or roll back a transaction.

Logic Regression can be defined as:

This is a statistical method of examining a dataset having one or more variables that are independent defining an outcome.

Data Validation is performed in 2 different steps-

☛ Data Screening – In this step various algorithms are used to screen the entire data to find any erroneous or questionable values. Such values need to be examined and should be handled.

☛ Data Verification- In this step each suspect value is evaluated on case by case basis and a decision is to be made if the values have to be accepted as valid or if the values have to be rejected as invalid or if they have to be replaced with some redundant values.

A LEFT JOIN returns all records from the left table, even when they do not match in the right table. Missing values become NULL. In a similar manner, a RIGHT JOIN returns all records from the right table, even when they do not match those in the left table. Missing values become NULL.

Typical data analysis involves the collection and organization of data. Then, finding correlations between that analyzed data and the rest of the company's and industry's data. It also entails the ability to spot problems and initiate preventative measures or problem solve creatively.

The modelling step begins once the data has been prepared. Modelling is an iterative process wherein the model is run repeatedly for improvements. Data modelling ensures that the best possible result is found for a given business problem.

☛ The developed model should have predictable performance.
☛ A good data model can adapt easily to any changes in business requirements.
☛ Any major data changes in a good data model should be scalable.
☛ A good data model is one that can be easily consumed for actionable results.

The primary key is the column or set of columns used to uniquely identify the items in a table. A foreign key is used to uniquely identify the items in a different table, allowing join operations to happen.

Self JOIN is a query that joins a table with itself. This is used to compare the values of a particular column with other values in the same column of the same table. Self JOIN uses aliases to name the original and duplicate tables.

A subquery is, as the name suggests, a query within another query. The outer query is called the main query, while the inner query is the subquery. You can think of this in terms of composition of functions. A subquery is always executed first, and its result is then passed on to the main query.

A good data analyst is the one who understands how changing business dynamics will affect the efficiency of a predictive model. You must be a valuable consultant who can use analytical skills and business acumen to find the root cause of business problems.

The best way to answer this question would be to say that you would work with the client to define a time period in advance. However, I would refresh or retrain a model when the company enters a new market, consummate an acquisition or is facing emerging competition. As a data analyst, I would retrain the model as quick as possible to adjust with the changing behaviour of customers or change in market conditions.

When answering this question, you should know that the definition of data cleansing is:

Data cleansing (also known as data cleaning) involves a data analyst discovering and eliminating errors and irregularities from the database to enhance data quality.

Data Profiling, also referred to as Data Archeology is the process of assessing the data values in a given dataset for uniqueness, consistency and logic. Data profiling cannot identify any incorrect or inaccurate data but can detect only business rules violations or anomalies. The main purpose of data profiling is to find out if the existing data can be used for various other purposes.

Data Mining refers to the analysis of datasets to find relationships that have not been discovered earlier. It focusses on sequenced discoveries or identifying dependencies, bulk analysis, finding various types of attributes, etc.

With a question like this, you glean insight into how candidates approach and solve problems. It also gives you a better idea of the type of work they have done.

What to look for in an answer:

☛ Explanation of how challenge(s) were overcome
☛ Lack of blaming others
☛ Discussion of why the project was difficult

INNER JOIN is the most common type of join. It returns all rows that are shared by two tables. Visually, it’s analogous to identifying the overlap, or intersection, between two sets of data.

To answer this question, you need to know that such responsibilities include:

☛ Interpret data and analyze results by using techniques of statistics and give reports.
☛ Look out for new areas or processes to improve opportunities.
☛ Get data from various sources (primary and secondary) and keep the systems running.
☛ Filter data from various sources and go through computer reports.
☛ Make sure all data analysis gets support and makes sure customers and staff relate well

☛ Separate data depending on their attributes
☛ In the case of massive datasets, do a stepwise cleansing and improve on the data on every step until the data quality is good.
☛ For common data cleansing, you need to generate a set of scripts which include blanking out every value not matching a regex.
☛ Do analysis on the statistic for every column.
☛ Stay up to date with all cleaning operations, so changes could make when necessary.

GROUP BY is a statement that divides the result for a particular query into groups of rows. It is often used with aggregate functions such as SUM, AVG, MAX, MIN, and COUNT, which calculate information about each group. The SELECT statement returns one row for each group.

A view is a virtual table that consists of a subset of data from a table. The content of a view is defined by the query. A view takes up little space because it doesn’t copy all data from the given table but only a subset of data as defined by the view. Note that a view can also display a combination of data from one or more tables. Views allow you to hide the complexity of large data and instead narrow in on areas of interest.